MyISAM表锁优化"建议"

其使用表锁在锁定过程比行锁或页锁级别附加成本小,锁定本身资源也最少,但是锁定颗粒度较大,所以,造成锁定资源的争用也会比其他级别要多,导致并发处理能力很低;

为了改善并发能力,如下建议:

1.缩短锁定时间,让query执行时间尽量的短

*减少复杂Query,将复杂Query分拆几个小的Query分布进行

*建立高效index,提高检索数据速度

*让myisam表只存放必要信息,控制字段类型

*合适机会优化myisam表数据文件

2.分离并行的操作,myisam是读写互相阻塞的表锁,并发插入(Concurrent Insert)特性

* value=2,表数据文件中间部分是否存在因为删除数据而留下空闲空间,容许数据文件尾进行并发插入

* value=1,表数据文件中间不存在空闲空间,可以从文件尾进行并发插入

* value=0,表数据文件中间部分是否存在因为删除数据留下空闲空间,不容许并发插入;

3.合理利用读写优先级

myisam表锁中默认写优先级大于读优先级,如果一个系统以读为主,且要优先保证查询性能的话,设置参数:

low_priority_updates=1,告诉mysql先处理读请求;

如果数据被删除可能性很小,对暂时性浪费空间不在乎,可以concurrent_insert=2(尝试)

数据文件空间有空域空间,在浪费空间时候,还会造成查询时间需要读取更多数据,所以删除数据量不是很小的,还是建议concurrent_insert=1;

InnoDB行锁优化"建议"

InnoDB使用行级锁,锁定机制带来的性能损耗可能比表级锁更高一些,并发能力要比Myisam要好,但是也需要我们合理利用innodb锁机制，否则也会带来其他想不到的问题：

如下建议:

*数据检索通过index完成,避免InnoDB无法通过index加锁而升级为表锁；

*合理利用index,让Innodb在index上加锁尽可能准确，缩小锁定范围，避免造成因锁定而影响其他Query执行;

*减少基于范围数据检索过滤条件,避免因间隙锁带来负面影响而锁定不该锁定记录;

*控制事务大小,减少锁定时间和资源；

*在业务环境许可下，尽量使用低级别事务隔离,减少MySQL因实现事务隔离级别带来附加成本;

因InnoDB行级锁和事务性,肯定会产生死锁,减少死锁小建议：

*类似业务模块中，尽可能按照相同访问顺序来访问，防止产生死锁；

*在同一个事务中，尽可能做到一次锁定所有资源，减少死锁产生概率；

*对非常容易产生死锁业务部分，可以尝试升级锁定颗粒度,同过表锁减少死锁概率；

系统资源锁定争用情况查询：

1.表锁：

mysql>show status like '%table%';

Table_locks_immediate:表级锁次数

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

--2个值是从系统启动后开始记录,每次出现对应事件数量+1，如果table_locks_waited值高,系统表级锁定争用现象严重,需要分析原因；

2.InnoDB 行级锁

mysql> show status like '%innodb_row_lock%';

Innodb行级锁,记录锁定等待次数，还记录锁定总时长，每次平均时长和最大时长,此外非累积当前正在等待锁定数量,对各个状态说明如下：

Innodb_row_lock_current_waits:当前正在等待锁定数量

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

Innodb_row_lock_time_avg:每次等待所花费平均时间

Innodb_row_lock_max:从系统启动到现在等待最长一次所花费时间

Innodb_row_lock_waits:从系统启动到现在总共等待次数

较重要Innodb_row_lock_time_avg\Innodb_row_lock_waits\Innodb_row_lock_time 排序依次：等待次数很高,每次等待时长也不小时候,分析原因;

Innodb提供5个系统状态变量,丰富即使状态信息;

1) 创建Innodb Monitor 表来打开InnoDB的monitor功能

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

2) mysql > show innodb status 查看细节信息

摘自网络

ITPub博客

锁机制优化MySQL

最新文章