MySQL数据库的锁定机制及优化

一.Mysql数据库锁定机制

　　1.行级锁定(row-level)

　　　　锁定对象的粒度最小,发生锁定资源争用的概率小,提高高并发系统的整体性能

　　　　粒度小,每次获取和释放锁做的事情更多,消耗大.行级锁最容易发生死锁

　　2.表级锁定(table-level)

　　　　最大粒度的锁,逻辑简单,负面影响小,获取锁和释放锁速度快

　　　　并发量大大减少

　　3.页级锁定(page-level)

　　　　粒度和开销介于两者之间,会发生死锁

　　　　总结:随着锁粒度的减小,锁定相同数据量的数据消耗的内存越来越多,实现算法也越来越复杂

　　　　　　不过,请求遇到锁等待的可能性降低,系统整体并发性提高

　　　　　　发生死锁有死锁的解决机制

　　　　　　使用表级锁定的主要是MyISAM,Memory,CSV等非事务性存储引擎,

　　　　　　行级锁主要是Innodb,NDB Cluster存储引擎

　　　　　　页级锁BerkeleyDB存储引擎

二.优化

　　MyISAM表锁的优化(提高并发能力)

　　　　1.缩短锁定时间

　　　　　　a.尽量减小大的复制Query,将复制的拆分

　　　　　　b.建立高效索引,

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

　　　　　　d.优化MyISAM表数据文件

　　　　2.分离能并行的操作(MyISAM)存储引擎有Concurrent Insert特性,可以设置0,1,2(建议1)

　　　　3.合理利用读写优先级(默认写优先级大于读)

　　　　　　可根据系统的实际设置,若系统以读为主,则设置读优先级大于写low_priority_updates=1

　　Innodb行锁优化建议

　　　　1.尽量让所有的数据检索通过索引完成,

　　　　2.合理设计索引

　　　　3.尽可能减少基于范围的数据检索过滤条件

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

　　　　5.在业务允许的情况下,尽量使用低级别的事务隔离

　　　　6.减少死锁的发生