前言
换了工作之后, 接近半年没有发博客了 (一直加班),emmmm..... 今天好不容易有时间, 记录下工作中遇到的一些问题, 接下来应该重拾知识点了. 因为新公司工作中 MySQL 库经常出现查询慢, 锁等待, 节点挂掉........ 等一系列问题. 导致每个程序员头都很大, 一味抱怨 "为什么我就查一条数据这么卡","我 TM 加了索引的啊, 怎么还怎么慢"........... 我想默默说的是, 大部分 MySQL 出现锁等待, 查询奇慢的情况基本都是因为 SQL 写的不好 (有坑), 或者数据表设计的不完善. 对, 不用想! 这些所有的坑很大一部分都是自己造成的. 那么是什么原因造成的, 大部分只是抱怨, 而不去关注 MySQL 的一些细节问题, 比如: MySQL 行锁的细节, 什么情况下会使用表锁等. 所以今天先讨论记录下 InnoDB 特有的行锁的一些细节, 加强认识.
InnoDB 不同于 MyISAM 最大的两个特点就是: 一是支持事务, 二是支持行锁; 毋庸置疑, 因为这两个特性大部分都采用 InnoDB 引擎, 其中的支持行锁就是 InnoDB 适合多并发优势所在, 但是行锁的一些细节没有深入理解过的话, 可能会造成一定的误解, 造成 "看似命中索引, 走行锁, 结果却是表锁, 最终导致锁等待情况".
一, InnoDB 行锁的实现方式
通过给索引上的索引项加锁来实现的, 也就意味着: 只有通过索引条件检索数据, InnoDB 才使用行级锁, 否则, InnoDB 将使用表锁. 这一点在实际应用中特别需要注意, 不然的话可能导致大量的锁冲突, 从而影响引发并发性能.
实验一: 对没有索引的加锁, 导致表锁
1) 准备工作: 建 tab_no_index 表, 表中无任何索引, 并插入数据
2)Session_1: 我们给 id=1 的行加上排它锁 (for update), 由于 id 没有索引, 实际上是表级锁;
3)Session_2: 我们给 id=2 的行加上排它锁 (for update), 由于 id 没有索引, 所以去申请表级锁, 但是却出现了锁等待! 原因就是在没有索引的情况下, InnoDB 只能使用表锁.
备注: MySQL 中的 for update 仅适用于 InnoDB(因为是只有此引擎才有行级锁), 并且必须开启事务, 在 begin 与 commit 之间才生效. for update 是在数据库中上锁用的, 可以为数据库中的行上一个排它锁. 当一个事务的操作未完成时候, 其他事务可以对这行读取但是不能写入或更新, 只能等该事务 Rollback, Commit, Lost connection...
实验二: 对有索引的键值加锁, 会对所有涉及到的数据行加锁
1) 准备工作: 对 id 建索引如下
2)Session_1: 此时 id 是有索引的, 我们对 id=1 and name=1 的一行加排它锁;
3)Session_2: 访问不同于 Session_1 的 id=1, name=5 行, 但是索引键值是一样的, 照样等待锁, 锁冲突了.
实验三: 多个索引时, 不同的事务可以使用不同的索引锁定不同的行, 不论什么索引, InnoDB 都会使用行锁对数据加锁 (对有索引的行数据).
1) 准备工作: 对 tab_no_index 追加 name 索引: alter table tab_no_index add index name(name);
2)Session_1: 开启事务对 id=1 的行加排它锁, 即对 name=1 与 name=5 两个数据加锁.
3)Session_2: 开启事务对 name=2 行加锁, 因为该数据没有被加锁, 索引可以获得锁
4)Session_3: 再对 name=5 的数据进行加锁, 由于该数据记录已被 Session_1 锁定, 所以等待获得锁.
注意事项: 即便使用了索引, 但还是要看 MySQL 具体对 SQL 的执行计划, 不一定能使用到
如我们对实验三对 name='2'进行加锁, 误以为 name 是 int 类型, 本来 name 是有索引的, 但是最后结果导致表锁:
具体请看 MySQL 的索引情况. 具体可以参考之前我的一篇博文 MySQL 优化 (1)-------- 常用的优化步骤, MySQL 优化 (2)-------- 常用优化
二, 间隙锁 (Next-Key 锁)
当用范围条件而不是相等条件检索数据, 并请求共享或者排它锁的时候, InnoDB 会给符合条件的已有数据记录的索引项加锁; 对于不在范围内的但并不存在的记录, 叫做 "间隙 (GAP)",InnoDB 也会对这个间隙加锁, 这就是所谓的间隙锁.
如: select * from where id>100 for update 对 id 大于 100 的数据对加锁, 但是此时数据中 id 只有 1,2....100,101, 不仅对存在的 101 的记录加锁, 还会对大于 101 不存在的数据的间隙加锁.
此外, 对使用相等条件请求给一个不存在的记录加锁, InnoDB 也会使用间隙锁, 如下:
Session_1: 对不存在的 id=6 的记录加锁
Session_2: 插入 id=6 的记录, 也会出现锁等待
三, 什么时候使用表锁?
对于 InnoDB 表, 在绝大部分情况下都应该使用行锁, 因为事务和行锁往往是我们之所以选择 InnoDB 表的理由, 但在个别情况下也使用表级锁;
1) 事务需要更新大部分或全部数据, 表又比较大, 如果使用默认的行锁, 不仅这个事务执行效率低, 而且可能造成其他事务长时间等待和锁冲突;
2) 事务涉及多个表, 比较复杂, 很可能引起死锁, 造成大量事务回滚.
使用表锁需要注意几点:
1) 使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁, 表级锁不是 InnoDB 存储引擎层管理的, 而是由其上一层 MySQL Server 负责的
2) 在用 LOCK TABLES 对 InnoDB 表加锁时需要注意, 要将 Autocommit 设置为 0, 否则 MySQL 不会给表加锁; 事务结束前, 不要用 UNLOCK TABLES 释放表锁, 因为 UNLOCK_TABLES 隐含提交事务; COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加表级锁.
- SET AUTOCOMMIT=0;
- LOCK TABLES table1 WRITE, table2 READ,...;
- [do something....]
- COMMIT;
- UNLOCK TABLES;
总结:
从设计之初, 就应该建立良好的索引机制, 避免对关键字段搜索时造成表锁;
避免长时间事务未提交等情况, 导致锁冲突, 死锁等情况;
不要老是抱怨数据库有问题, 应该从自身写的 SQL 分析出发, 学会分析 (数据库不行大部分是因为 SQL 写的有问题, 没错, 是自身问题);
不要总是觉得这是 DBA 该做的事, 开发者应该学会基本的 SQL 常识 (如 MySQL 的最左索引, 回表, 索引覆盖等知识), 学会基本的优化步骤.
主要参考资料:《深入浅出 MySQL》(有需要 PDF 电子书的伙伴可以评论或者私信我)
来源: https://www.cnblogs.com/jian0110/p/12721924.html