mysql中的表锁的优化

标签: mysql | 发表时间:2013-08-20 09:11 | 作者:admin
出处:http://blog.haohtml.com

一、获取锁等待情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:
mysql> show status like 'Table%';
+----------------------------+----------+
| Variable_name       | Value |
+----------------------------+----------+
| Table_locks_immediate   | 105       |
| Table_locks_waited  | 3     |
+----------------------------+----------+
2 rows in set (0.00 sec)

Table_locks_immediate 表示立即释放MySQL表锁数,
Table_locks_waited 表示需要等待的MySQL表锁数
如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。这时,需要我们对应用做进一步的检查,来确定问题所在。

可以通过检查Innodb_row_lock状态变量来分析系统上的行锁的争夺情况:
mysql> show status like 'innodb_row_lock%';
+----------------------------------------+----------+
| Variable_name               | Value |
+----------------------------------------+----------+
| Innodb_row_lock_current_waits   | 0     |
| Innodb_row_lock_time            | 2001  |
| Innodb_row_lock_time_avg        | 667       |
| Innodb_row_lock_time_max    | 845       |
| Innodb_row_lock_waits       | 3     |
+----------------------------------------+----------+
5 rows in set (0.00 sec)

另外,针对Innodb类型的表,如果需要察看当前的锁等待情况,可以设置InnoDB Monitors,然后通过Show innodb status察看,设置的方式是:
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
监视器可以通过发出下列语句来被停止:
DROP TABLE innodb_monitor;
设置监视器后,在show innodb status的显示内容中,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等等,便于进行进一步的分析和问题的确定。打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以我们在确认问题原因之后,要记得删除监控表以关闭监视器。或者通过使用--console选项来启动服务器以关闭写日志文件。

二、什么情况下使用表锁

表级锁在下列几种情况下比行级锁更优越:
1.很多操作都是读表。
2.在严格条件的索引上读取和更新,当更新或者删除可以用单独的索引来读取得到时:
3.UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
4.DELETE FROM tbl_name WHERE unique_key_col=key_value;
5.SELECT 和 INSERT 语句并发的执行,但是只有很少的 UPDATE 和 DELETE 语句。
6.很多的扫描表和对全表的 GROUP BY 操作,但是没有任何写表。

三、什么情况下使用行锁

行级锁定的优点:
1.当在许多线程中访问不同的行时只存在少量锁定冲突。
2.回滚时只有少量的更改。
3.可以长时间锁定单一的行。

行级锁定的缺点:
1.比页级或表级锁定占用更多的内存。
2.当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
3.如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
4.用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

四、insert …select …带来的问题

当使用insert...select...进行记录的插入时,如果select的表是innodb类型的,不论insert的表是什么类型的表,都会对select的表的纪录进行锁定。

对于那些从oracle迁移过来的应用,需要特别的注意,因为oracle并不存在类似的问题,所以在oracle的应用中insert...select...操作非常的常见。例如:有时候会对比较多的纪录进行统计分析,然后将统计的中间结果插入到另外一个表,这样的操作因为进行的非常少,所以可能并没有设置相应的索引。如果迁移到mysql数据库后不进行相应的调整,那么在进行这个操作期间,对需要select的表实际上是进行的全表扫描导致的所有记录的锁定,将会对应用的其他操作造成非常严重的影响。

究其主要原因,是因为mysql在实现复制的机制时和oracle是不同的,如果不进行select表的锁定,则可能造成从数据库在恢复期间插入结果集的不同,造成主从数据的不一致。如果不采用主从复制,关闭binlog并不能避免对select纪录的锁定,某些文档中提到可以通过设置innodb_locks_unsafe_for_binlog来避免这个现象,当这个参数设置为true的时候,将不会对select的结果集加锁,但是这样的设置将可能带来非常严重的隐患。如果使用这个binlog进行从数据库的恢复,或者进行主数据库的灾难恢复,都将可能和主数据库的执行效果不同。

因此,我们并不推荐通过设置这个参数来避免insert...select...导致的锁,如果需要进行可能会扫描大量数据的insert...select操作,我们推荐使用select...into outfile和load data infile的组合来实现,这样是不会对纪录进行锁定的。

五、next-key锁对并发插入的影响

在行级锁定中,InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定:当它搜索或扫描表的索引之时,它对遇到的索引记录设置共享或独占锁定。因此,行级锁定事实上是索引记录锁定。

InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定,另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。

可以用next-key锁定在你的应用程序上实现一个唯一性检查:如果你以共享模式读数据,并且没有看到你将要插入的行的重复,则你可以安全地插入你的行,并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此,the next-key锁定允许你锁住在你的表中并不存在的一些东西。

六、隔离级别对并发插入的影响

REPEATABLE READ是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录,而不锁定记录前的间隙。用其它搜索条件,这些操作采用next-key锁定,用next-key锁定或者间隙锁定锁住搜索的索引范围,并且阻止其它用户的新插入。

在持续读中,有一个与READ COMMITTED隔离级别重要的差别:在这个级别,在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句,这些SELECT语句对相互之间也是持续的。

READ COMMITTED隔离级别是一个有些象Oracle的隔离级别。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD语句仅锁定索引记录,而不锁定记录前的间隙,因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录,而不包括记录前的间隙。

在范围类型UPDATE和DELETE语句,InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的,因为要让MySQL复制和恢复起作用,“幽灵行”必须被阻止掉。

如果应用是从基于ORACLE的应用迁移到MYSQL数据库的,那么建议使用该隔离级别提供数据库服务,因为该隔离级别是最接近ORACLE的默认隔离级别的,迁移可能遇到的锁问题最小。

七、如何减少锁冲突

1.对Myisam类型的表:
1)Myisam类型的表可以考虑通过改成Innodb类型的表来减少锁冲突。

2)根据应用的情况,尝试横向拆分成多个表或者改成Myisam分区对减少锁冲突也会有一定的帮助。

2.对Innodb类型的表:
1) 首先要确认,在对表获取行锁的时候,要尽量的使用索引检索纪录,如果没有使用索引访问,那么即便你只是要更新其中的一行纪录,也是全表锁定的。要确保sql是使用索引来访问纪录的,必要的时候,请使用explain检查sql的执行计划,判断是否按照预期使用了索引。

2)由于mysql的行锁是针对索引加的锁,不是针对纪录加的锁,所以虽然是访问不同行的纪录,但是如果是相同的索引键,是会被加锁的。应用设计的时候也要注意,这里和Oracle有比较大的不同。

3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,当表有主键或者唯一索引的时候,不是必须使用主键或者唯一索引锁定纪录,其他普通索引同样可以用来检索纪录,并只锁定符合条件的行。

4)用SHOW INNODB STATUS来确定最后一个死锁的原因。查询的结果中,包括死锁的事务的详细信息,包括执行的SQL语句的内容,每个线程已经获得了什么锁,在等待什么锁,以及最后是哪个线程被回滚。详细的分析死锁产生的原因,可以通过改进程序有效的避免死锁的产生。

5)如果应用并不介意死锁的出现,那么可以在应用中对发现的死锁进行处理。

6)确定更合理的事务大小,小事务更少地倾向于冲突。

7)如果你正使用锁定读,(SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE),试着用更低的隔离级别,比如READ COMMITTED。

8)以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

相关 [mysql 优化] 推荐:

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.

Zabbix 的 MySQL 优化

- - SegmentFault 最新的文章
为 Zabbix 优化 MySQL. 标签(空格分隔): Zabbix MySQL Optimizing 优化. Aurimas Mikalauskas,原文是. Zabbix 和 MySQL. 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO.

mysql优化方法

- - 数据库 - ITeye博客
通过show status和应用特点了解各种SQL的执行频率. 通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladmin extended-status命令获得. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 以下几个参数对Myisam和Innodb存储引擎都计数:.

Mysql性能优化

- - 数据库 - ITeye博客
MySQL性能优化.   性能优化是通过某些有效的方法来提高MySQL的运行速度,减少占用的磁盘空间. 性能优化包含很多方面,例如优化查询速度,优化更新速度和优化MySQL服务器等.   数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能. 语法:SHOW STATUE LIKE ‘value’;其中value参数是常用的几个统计参数.

MYSQL设计优化

- - CSDN博客推荐文章
本文将从各方面介绍优化mysql设计的一些方式. (1)定位需要优化的sql语句. 1)show status统计SQL语句频率. 对Myisam和Innodb存储引擎都计数的参数:. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 1.Com_select  执行select操作的次数,一次查询只累加1;.

MySQL优化之Explain

- - 数据库 - ITeye博客
Mysql Explain 详解. 例如: explain select * from t3 where id=3952602;. 二.explain输出解释. | id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |.