mysql 锁优化


一.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 中,以便我们后面做进一步分析使用。

 

posted @ 2015-03-26 11:38  饿狼的传说  阅读(423)  评论(0编辑  收藏  举报