mysql 锁优化

32ew12

一.myisam存储引擎锁优化


1.合理理由读写优先级
MyISAM 的表锁，写互相阻塞的表锁，
默认系统是写优先，可改为读有先：
low_priority_updates=1
如果我们的系统是一个以读为主，而且要优先保证查询性能的话，可以通过设置系统参数选项
low_priority_updates=1，将写的优先级设置为比读的优先级低，即可让告诉MySQL 尽量先处理读请
求。
若系统需要有限保证数据写入的性能的话，则可以不用设置low_priority_updates
参数了。
2.分离能并行的操作
Concurrent Insert（并发插入）的特性：
MyISAM 存储引擎有一个控制是否打开Concurrent Insert 功能的参数选项：concurrent_insert，可
以设置为0，1 或者2。三个值的具体说明如下：
a) concurrent_insert=2，无论MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据
而留下的空闲空间，都允许在数据文件尾部进行Concurrent Insert;
b) concurrent_insert=1，当MyISAM 存储引擎表数据文件中间不存在空闲空间的时候，可以从文
件尾部进行Concurrent Insert;
c) concurrent_insert=0，无论MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据
而留下的空闲空间，都不允许Concurrent Insert。

如果数据被删除的可能性很小的时候，切对暂时性的浪费少量空间并不是特别的在乎的话，可将concurrent_insert 参数设置
为2 ，数据文件中间留有空域空间，在浪费空间的时候，还会造成在查询的时候需要读取更多的数据，所以如果删除量不是很小的话，
还是建议将concurrent_insert 设置为1 更为合适。

3.缩短锁定时间

唯一的办法就是让我们的Query 执行时间尽可能的短。
a) 尽两减少大的复杂Query，将复杂Query 分拆成几个小的Query 分布进行；
b) 尽可能的建立足够高效的索引，让数据检索更迅速；
c) 尽量让MyISAM 存储引擎的表只存放必要的信息，控制字段类型；
d) 利用合适的机会优化MyISAM 表数据文件；

二.Innodb 行锁优化建议

要想合理利用Innodb 的行级锁定，做到扬长避短，我们必须做好以下工作：
a) 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb 因为无法通过索引键加锁而升级
为表级锁定；
b) 合理设计索引，让Innodb 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免
造成不必要的锁定而影响其他Query 的执行；
c) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定
的记录；
d) 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e) 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL 因为实现事务隔离级
别所带来的附加成本；

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

三.系统锁定争用情况查询       

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> show status like '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_row_lock_current_waits：当前正在等待锁定的数量；
● Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
● Innodb_row_lock_time_avg：每次等待所花平均时间；
● Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
● Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
比较重要的主要是Innodb_row_lock_time_avg（ 等待平均时长），Innodb_row_lock_waits（等待总次数）
以及Innodb_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，
我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

此外，Innodb 提供的其即时状态信息供我们分析使用。可以通过如下方法查看：
1. 通过创建Innodb Monitor 表来打开Innodb 的monitor 功能：
mysql> create table innodb_monitor(a int) engine=innodb;
Query OK, 0 rows affected (0.07 sec)
2. 然后通过使用“SHOW INNODB STATUS”查看细节信息
先创建monitor表实际上就是告诉Innodb 我们开始要监控他的细节状态了，然后Innodb 就会将比较详细的事务
以及锁定信息记录进入MySQL 的error log 中，以便我们后面做进一步分析使用。