mysql数据库锁定机制

mysql> create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create index test_innodb_a_ind on test_innodb_lock(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index test_innodb_lock_b_ind on test_innodb_lock(b);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0

合理利用锁机制优化MySQL

MyISAM 表锁优化建议

对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。

所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

1、缩短锁定时间

缩短锁定时间，短短几个字，说起来确实听容易的，但实际做起来恐怕就并不那么简单了。如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。

尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；

尽可能的建立足够高效的索引，让数据检索更迅速；

尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；

利用合适的机会优化MyISAM表数据文件；

2、分离能并行的操作

说到MyISAM的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化，没办法再并行了。大家不要忘记了，MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性。

MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：

concurrent_insert=2，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行ConcurrentInsert;

concurrent_insert=1，当MyISAM存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行ConcurrentInsert;

concurrent_insert=0，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许ConcurrentInsert。

3、合理利用读写优先级

在本章各种锁定分析一节中我们了解到了MySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。所以，如果我们可以根据各自系统环境的差异决定读与写的优先级。如果我们的系统是一个以读为主，而且要优先保证查询性能的话，我们可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读的优先级低，即可让告诉MySQL尽量先处理读请求。当然，如果我们的系统需要有限保证数据写入的性能的话，则可以不用设置low_priority_updates参数了。

这里我们完全可以利用这个特性，将concurrent_insert参数设置为1，甚至如果数据被删除的可能性很小的时候，如果对暂时性的浪费少量空间并不是特别的在乎的话，将concurrent_insert参数设置为2都可以尝试。当然，数据文件中间留有空域空间，在浪费空间的时候，还会造成在查询的时候需要读取更多的数据，所以如果删除量不是很小的话，还是建议将concurrent_insert设置为1更为合适。

Innodb 行锁优化建议

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

要想合理利用Innodb的行级锁定，做到扬长避短，我们必须做好以下工作：

尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；

合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；

由于Innodb的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率

的的小建议，读者朋友可以根据各自的业务特点针对性的尝试：a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

系统锁定争用情况查询对于两种锁定级别，MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况，我们先看看

MySQL 实现的表级锁定的争用状态变量：

mysql> show status like ‘table%‘; 
+-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ 
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
 +-----------------------+-------+

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

Table_locks_immediate：产生表级锁定的次数；

Table_locks_waited：出现表级锁定争用而发生等待的次数；

两个状态值都是从系统启动后开始记录，没出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

对于Innodb所使用的行级锁定，系统中是通过另外一组更为详细的状态变量来记录的，如下：

mysql>showstatuslike‘innodb_row_lock%‘;
+-------------------------------+--------+|Variable_name|Value|+-------------------------------+--------+
|Innodb_row_lock_current_waits|0|
|Innodb_row_lock_time|490578|
|Innodb_row_lock_time_avg|37736|
|Innodb_row_lock_time_max|121411|
|Innodb_row_lock_waits|13|
+-------------------------------+--------+

Innodb 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

Innodb_row_lock_time_avg：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是Innodb_row_lock_time_avg（等待平均时长），Innodb_row_lock_waits（等待总次数）以及Innodb_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

此外，Innodb出了提供这五个系统状态变量之外，还提供的其他更为丰富的即时状态信息供我们分析使用。可以通过如下方法查看：

1.通过创建InnodbMonitor表来打开Innodb的monitor功能：

mysql> create table innodb_monitor(a int) engine=innodb;

Query OK, 0 rows affected (0.07 sec)

2.然后通过使用“SHOWINNODBSTATUS”查看细节信息（由于输出内容太多就不在此记录了）；

可能会有读者朋友问为什么要先创建一个叫innodb_monitor的表呢？因为创建该表实际上就是告诉Innodb我们开始要监控他的细节状态了，然后Innodb就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。

小结

本章以MySQLServer中的锁定简介开始，分析了当前MySQL中使用最为广泛的锁定方式表级锁定和行级锁定的基本实现机制，并通过MyISAM和Innodb这两大典型的存储引擎作为示例存储引擎所使用的表级锁定和行级锁定做了较为详细的分析和演示。然后，再通过分析两种锁定方式的特性，给出相应的优化建议和策略。最后了解了一下在MySQLServer中如何获得系统当前各种锁定的资源争用状况。希望本章内容能够对各位读者朋友在理解MySQL锁定机制方面有一定的帮助。

mysql数据库锁定机制

标签：进一步   也有   细节   定位   rem   不能   架构   演示   监控