各存储引擎使用三种类型锁定机制
读锁定,一个新客户端在申请获取读锁定资源的时候,需要满足两个条件:
写锁定
(影响读操作,同时也影响写操作)
InnoDB 的锁定是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间标记锁定信息实现的。这种锁定方式被称为 “NEXT-KEY locking”(间隙锁)
间隙锁弱点:锁定一个范围之后,即使某些不存在的键值也会被无辜锁定,造成锁定的时候无法插入键值锁定内的任何数据。
通过索引实现锁定的方式存在其他几个较大的性能隐患:
当 Query 无法利用索引的时候,InnoDB 会放弃使用 行级锁定 而改用 表级锁定 ,造成并发性能降低;
当 Query 使用的索引并不包含所有过滤条件时,数据检索使用到的索引键中的数据可能有部分不属于 Query 的结果集行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键。
当 Query 在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同 (索引只是过滤条件的一部分), 他们一样会被锁定。
缩短锁定时间
分离能并行的操作
合理利用读写优先级
表级锁定 默认情况下写优先级大于读,如果读操作多的时候,可以设置读优先级高,可设置参数 low_priority_updates = 1。
MySQL 内部有两组专用的状态变量记录系统内部资源争用情况。
表级锁定的争用状态变量
mysql> show status like ‘table%’;
Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而发生等待的次数
Table_locks_immediate 值大于 Table_locks_waited 5000 是比较合适的,在大就需要分析问题所在。
两个状态值都是从系统启动后开始记录,每出现一次加 1,如果这里 Table_locks_waited 状态值比较高,说明表级锁定争用严重,需进一步分析。
InnoDB 行级锁定状态变量记录
sql> show status like ‘innodb_row_lock%’;
Innodb_row_lock_current_waites:当前正在等待锁定的数量;
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 Monitor 表来打开 InnoDB 的 monitor 功能
mysql > create table innodb_monitor(a int) engine=innodb;
然后执行 ”show innodb status” 查看详细信息
为什么创建 innodb_monitor 表?
创建该表就是告诉 InnoDB 我们要开始监控他的详细信息,然后 InnoDB 就会将比较详细的事务级锁定信息记录到 MySQL 的 error log 中,以便后面做进一步分析。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞5
添加新评论1 条评论
2017-11-13 16:52