老叶观点:MySQL开发规范之我见(更新版)

大多数MySQL规范在网上也都能找得到相关的分享,在这里要分享的是老叶个人认为比较重要的,或者容易被忽视的,以及容易被混淆的一些地方。

1、默认使用InnoDB引擎

【老叶观点】已多次呼吁过了,InnoDB适用于几乎99%的MySQL应用场景,而且在MySQL 5.7的系统表都改成InnoDB了,还有什么理由再死守MyISAM呢。

此外,频繁读写的InnoDB表,一定要使用具有自增/顺序特征的整型作为显式主键。

当然了,也不是说MyISAM就一无是处,比如老叶之前就把MyISAM用于临时导数据数据(把数据导入MyISAM,一番处理后再入到InnoDB表)、或者一些特殊的数据统计类场景用MyISAM(大数据量下MyISAM全表顺序读取比InnoDB有明显优势)可能比较合适。前提是,你得非常清楚MyISAM引擎的优势在哪里。

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

2、InnoDB表行记录物理长度不超过8KB

【老叶观点】InnoDB的data page默认是16KB,基于B+Tree的特点,一个data page中需要至少存储2条记录。因此,当实际存储长度超过8KB(尤其是TEXT/BLOB列)的大列(large column)时会引起“page-overflow存储”,类似ORACLE中的“行迁移”。

因此,如果必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储。如果不太频繁,可以考虑继续保留在主表中。

当然了,如果将 innodb_page_size 选项修改成 8KB,那么行记录物理长度建议不超过4KB。

[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

3、是否使用表分区(partition)

【老叶观点】在一些使用表分区后明显可以提升性能或者运维便利性的场景下,还是建议使用表分区。

比如老叶就在zabbix的数据库采用TokuDB引擎的前提下,又根据时间维度使用了分区。这样的好处是保证zabbix日常应用不受到影响前提下,方便管理员例行删除过去数据,只需要删除相应分区即可,不需再执行一个非常慢的DELETE而影响整体性能。

当然了,使用表分区可能不利于物理扩展,比如大数据量下想要做哈希水平拆分,这个就见仁见智了,如果你的业务场景下使用表分区更有好处,就放心大胆的用吧。该进行拆分就用拆分方案,不要继续抱着表分区方案不放。

迁移Zabbix数据库到TokuDB

4、是否使用存储过程、触发器

【老叶观点】在一些合适的场景下,用存储过程、触发器也完全没问题。

我们以前就是利用存储完成游戏业务逻辑处理,性能上不是问题,而且一旦需求有变更,只需修改存储过程,变更代价很低。我们还利用触发器维护一个频繁更新的表,对这个表的所有变更都将部分字段同步更新到另一个表中(类似物化视图的变相实现),也不存在性能问题。

有同行认为存储过程和触发器的应用可能会导致发生锁等待、死锁时排查问题上的困惑。嗯,这个是有这个可能性,不过如果真发生时,根据当时现场记录的SQL反查对应的存储过程或触发器,应该也不是难事,只不过要求DBA对线上业务环境更要了然于胸了。

总的来说,不要把MySQL的存储过程和触发器视为洪水猛兽,用好的话,没有问题的,真遇到问题了再优化也不迟。另外,MySQL因为对视图的处理也不太理想也没有物化视图,因此视图能不用就尽量少用吧。

5、选择合适的类型

【老叶观点】除了常见的建议外,还有其他几个要点:

5.1、用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。

5.2、枚举类型可以使用ENUM,ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR),性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据。

5.3、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME。其实从5.6开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大。

5.4、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示)。在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果。

5.5、杜绝直接 SELECT * 读取全部字段,当表中存在 TEXT/BLOB 大列的时候就会是灾难了。可能本来不需要读取这些列,但因为偷懒写成 SELECT * 导致内存buffer pool被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

相应地,在写INSERT时,也要写上相对应的字段列表。

要求在SQL中写清楚每个字段的重要意义还在于,当业务需要表DDL发生更新后,如果不写清楚字段,可能会导致旧业务代码不可用,这个就折腾大发了。

6、关于索引

【老叶观点】除了常见的建议外,还有几个要点:

6.1、超过20个长度的字符串列,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出,一般略大于平均长度一点就可以了。

6.2、定期用 pt-duplicate-key-checker 工具检查并删除冗余的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a),就可以删除 idx2 索引了。

6.3、有多字段联合索引时,WHERE中过滤条件的字段顺序无需和索引一致,但如果有排序、分组则就必须一致了。

比如有联合索引 idx1(a, b, c),那么下面的SQL都可以完整用到索引:

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到,WHERE中字段顺序并没有和索引字段顺序一致

SELECT ... WHERE b = ? AND a = ? AND c = ?;

SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;

SELECT ... WHERE a = ? AND b = ? ORDER BY c;

SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;

SELECT ... WHERE a = ? ORDER BY b, c;

SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

而下面几个SQL则只能用到部分索引,或者可利用到ICP特性:

SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分

SELECT ... WHERE a IN (?, ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND b IN (?, ?); -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- EXPLAIN显示用到 (a, b, c) 整个索引,同时有ICP

SELECT ... WHERE a = ? AND c = ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND c >= ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

ICP(index condition pushdown)是MySQL 5.6的新特性,其机制会让索引的其他部分也参与过滤,减少引擎层和server层之间的数据传输和回表请求,通常情况下可大幅提升查询效率。

下面的几个SQL完全用不到该索引:

SELECT ... WHERE b = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... ORDER BY b;

SELECT ... ORDER BY b, a;

从上面的几个例子就能看的出来,以往强调的WHERE条件字段顺序要和索引顺序一致才能使用索引的 “常识性误导” 无需严格遵守。

此外,有些时候查询优化器指定的索引或执行计划可能并不是最优的,可以手工指定最优索引,或者修改session级的 optimizer_switch 选项,关闭某些导致效果反而更差的特性(比如index merge通常是好事,但也遇到过用上index merge后反而更差的,这时候要么强制指定其中一个索引,要么可以临时关闭 index merge 特性)。

7、其他

7.1、哪怕是基于索引的条件过滤,如果优化器意识到总共需要扫描的数据量超过30%时(ORACLE里貌似是20%,MySQL目前是30%,没准以后会调整),就会直接改变执行计划为全表扫描,不再使用索引。

7.2、多表JOIN时,要把过滤性最大(不一定是数据量最小哦,而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表。此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序。

7.3、绝大多数情况下,排序的大家通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧。

7.4、利用 pt-query-digest 定期分析slow query log,并结合 Box Anemometer 构建slow query log分析及优化系统。

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

备注:若无特别说明,以上规范建议适用于MySQL 5.6及之前的版本(并且主要是5.6版本,尤其是ICP特性、DATETIME变化这两个地方)。5.7及之后的版本可能会有些变化,个别规范建议需要相应调整。

延伸阅读:

UPDATE能走索引还会锁全表吗

导读

执行UPDATE时,WEHRE条件列虽已有索引,但还会锁全表,肿么回事?

问题描述

叶师傅有次上课过程中执行UPDATE测试案例时,发现虽然WHERE条件列已有索引,有时候能利用二级索引进行更新(且只锁定相应必要的几行记录),但有时候却变成了根据主键进行更新,且会锁全表。我们先来看看下面的例子。

测试表 t1

CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中数据

+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
|  6 |  8 |  5 |  0 |
|  7 |  6 |  6 | 10 |
| 10 | 10 |  4 |  0 |
+----+----+----+----+

case1:根据二级索引UPDATE,不锁全表

先看执行计划

yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=8\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: range
possible_keys: c2
          key: c2
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where

启动两个session执行UPDATE测试

session1 session 2
mysql> begin;
mysql> update t1 set c4=123 where c2>=8;

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> begin;
mysql> select * from t1 where c2 = 7 for update;


1 row in set (0.00 sec)
#直接可得到结果,不会被阻塞

case2:根据PRIMARY KEY更新,锁全表

yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=6\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: index
possible_keys: c2
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where

我们能看到本次执行计划是 根据主键索引进行更新,且会锁全表。

同样地,启动两个session执行UPDATE测试

session1 session 2
mysql> begin;
mysql> update t1 set c4=123 where c2>=6;

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> begin;
mysql> select * from t1 where c2 = 3 for update;
#无法得到结果,被阻塞了
mysql> rollback;
#执行rollback,释放锁

1 row in set (4.23 sec)
#session1释放锁后才能得到结果

查看行锁等待情况

yejr@imysql.com [yejr]>select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2017-08-15 15:20:20
                    wait_age: 00:00:17
               wait_age_secs: 17
                locked_table: `yejr`.`t1`
                locked_index: PRIMARY  <--主键上加锁
                 locked_type: RECORD
              waiting_trx_id: 268350
         waiting_trx_started: 2017-08-15 15:20:20
             waiting_trx_age: 00:00:17
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 13
               waiting_query: select * from t1 where c2 = 3 for update
             waiting_lock_id: 268350:387:3:4
           waiting_lock_mode: X
             blocking_trx_id: 268349
                blocking_pid: 12
              blocking_query:  NULL
            blocking_lock_id: 268349:387:3:4
          blocking_lock_mode: X
        blocking_trx_started: 2017-08-15 15:20:18
            blocking_trx_age: 00:00:19
    blocking_trx_rows_locked: 8  <-- 所有记录都被加锁了
  blocking_trx_rows_modified: 3  <---持有锁的事务更新了3行记录
     sql_kill_blocking_query: KILL QUERY 12
sql_kill_blocking_connection: KILL 12

问题分析

好了,案例说完了,也该说原因了。

肾好的同学可能记得我说过一个结论:当MySQL预估扫描行数超过全表总数约 20% ~ 30% 时,即便有二级索引,也会直接升级为全表扫描。

这个结论的原因并不难理解,二级索引的顺序和主键顺序一般来说是不一样的,根据二级索引的顺序回表读数据时,实际上对于主键则很可能是随机扫描,因此当需要随机扫描的数量超过一定比例时(一般是20% ~ 30%),则优化器会决定直接改成全表扫描。

上述说法出处:WHERE Clause Optimization

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

不过,上面这个结论是针对读数据的情况,UPDATE/DELETE修改数据时是否也这样呢?

答案是肯定的,要不然上面的测试结果怎么解释……

按照官方开发者的说法,当优化器评估根据二级索引更新行数超过约50%(从上面测试结果来看,其实20% ~ 30%就已经是这样了,不过这个比例并不是固定值,会根据各种代价计算自动调整)就会改成走主键索引,并且锁全表,这么做是既定的策略,原因和上面一样,也是为了提高检索效率。

总结

老调重弹,几点建议:

  • 不管检索还是更新,都尽可能利用索引;
  • 不要一次性检索或更新大批量数据,建议分城多批次;
  • 事务尽快提交,降低行锁持有时间及其影响。

本文提到的问题,我也提了个BUG#87021,有兴趣的可以去看看。

延伸阅读

InnoDB事务何时创建read view

导读

InnoDB事务中是一启动就创建read view,还是什么时候?

几个关于事务的基本概念

说到事务,我们不得不先说下什么是ACID、MVCC、consistent read、isolation level、read view 等几个基本概念。

ACID

ACID是事务的原子性、一致性、隔离性、持久性4个单词的首字母缩写。所有的事务性数据库系统都遵循这4个特性,InnoDB亦是如此。关于ACID的具体解释请自行 google/bing 搜索。

MVCC

是multiversion concurrency control的简称,也就是多版本并发控制,是个很基本的概念。MVCC的作用是让事务在并行发生时,在一定隔离级别前提下,可以保证在某个事务中能实现一致性读,也就是该事务启动时根据某个条件读取到的数据,直到事务结束时,再次执行相同条件,还是读到同一份数据,不会发生变化(不会看到被其他并行事务修改的数据)。
有了 MVCC 就可以提高事务的并行度,因为可以利用锁机制实现资源控制而无需等待其他事务先执行。

read view

InnoDB的MVCC机制使用的内部快照。在不同的隔离级别下,事务启动时(有些情况下,可能是SQL语句开始时)看到的数据快照版本可能也不同。在RR、RC、RU(READ UNCOMMITTED)等几个隔离级别下会用到
read view。

一致性读

读请求基于某个时间点得到一份那时的数据快照,不管同时其他事务对数据的修改。查询过程中,若其他事务修改了数据,那么就需要从 undo log中获取旧版本的数据。这么做可以有效避免因为需要加锁(来阻止其他事务同时对这些数据的修改)而导致事务并行度下降的问题。

在可重复读(REPEATABLE READ,简称RR)隔离级别下,数据快照版本是在第一个读请求时发起的。在读已提交(READ COMMITTED,简称RC)隔离级别下,则是在每次读请求时都会重新创建一份快照。

一致性读是InnoDB在RR和RC下处理SELECT请求的默认模式。由于一致性读不会在它请求的表上加锁,其他事务可以同时修改数据不受影响。

何时创建read view

其实,我们从上面的解释已经明白了,在RC隔离级别下,是每个SELECT都会获取最新的read view;而在RR隔离级别下,则是当事务中的第一个SELECT请求才创建read view。
我们通过几个例子来加强下。

1. RR级别

首先,确认隔离级别

yejr@imysql.com [test]>select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

测试1:事务启动后立即发起SELECT请求

session1 session2
begin; begin;
select * from t1 where a=10;
+—-+——+—+
a | b | c |
+—-+——+—+
| 10 | 8 | 1 | | select * from t1 where a=10;
+—-+——+—+
| 10 | 8 | 1 |

事务中第一个SELECT立即创建read view

update t1 set c=10 where a=10;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

select * from t1 where a=10;
+—-+——+—+
| 10 | 8 | 1 |

再次读取,结果还是一样

commit;

提交事务

select * from t1 where a=10;
+—-+——+—+
| 10 | 8 | 1 |

再次读取,结果仍然一样

结论可见:RR中第一个SELECT已经创建好read view,之后不会再发生变化

测试2:另一个事物提交后才发起SELECT请求

session1 session2
begin; begin;
select * from t1 where a=10;
+—-+——+—+
a | b | c |
+—-+——+—+
| 10 | 8 | 1 | |   |
| update t1 set c=10 where a=10;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 |  

commit;

提交事务

select * from t1 where a=10;
+—-+——+—+
| 10 | 8 | 10 |

session1提交后才发起SELECT,可以读取到最新版本

结论可见:RR中是发起SELECT时才创建read view,而不是事务刚启动时就创建

2. RC级别

根据上面提到的说法,RC隔离级别下,是每次发起SELECT都会创建read view,也就是每次SELECT都能读取到已经COMMIT的数据,所以才存在不可重复读、幻读 现象。

修改&确认隔离级别

yejr@imysql.com [test]>set session transaction isolation level read committed;

yejr@imysql.com [test]>select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

开始测试

session1 session2
begin; begin;
select * from t1 where a=10;
+—-+——+—+
a | b | c |
+—-+——+—+
| 10 | 8 | 101 | | select * from t1 where a=10;
+—-+——+—+
| a | b | c |
+—-+——+—+
| 10 | 8 | 101 |
update t1 set c=102 where a=10;commit;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 0 rows affected (0.02 sec)

 
  select * from t1 where a=10;
+—-+——+—+
| 10 | 8 | 102 |

session1提交后再次发起SELECT,可以读取到最新版本

begin;update t1 set c=103 where a=10;commit;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 0 rows affected (0.02 sec)

 
  select * from t1 where a=10;
+—-+——+—+
| 10 | 8 | 103 |

再次发起SELECT,又可以读取到最新版本

总结

  • RR级别下,事务中的第一个SELECT请求才开始创建read view;
  • RC级别下,事务中每次SELECT请求都会重新创建read view;

延伸阅读

两个INSERT发生死锁原因剖析

导读

两个INSERT也能发生死锁?貌似不可思议,实际上是正常的。

开始之前,关于锁、死锁,我们要先统一下几点认知:

  • 死锁是由于多个事务相互持有对方所需要的锁,结果导致事务都无法继续,进而触发死锁检测,其中某个事务会被回滚,释放相应的锁,其他事务得以正常继续;简言之,就是多个事务之间的锁等待产生了回路,死循环了;
  • 死锁发生时,会立刻被检测到,并且回滚某个事务,而不会长时间阻塞、等待;
  • 从MySQL 5.7.15开始,新增选项 innodb_deadlock_detect,没记错的话应该是阿里率先实现的功能。当它设置为 OFF 时(默认值是 ON),InnoDB会不检测死锁,在高并发场景(例如“秒杀”)业务中特别有用,可以提高事务并发性能;
  • 在启用死锁检测时,InnoDB默认的最大检测深度为200,在上面提到的高并发高竞争场景下,热点数据上的锁等待队列可能很长,死锁检测代价很大。或者当等待队列中所有的行锁总数超过 100万 时,也会认为发生死锁,直接触发死锁检测;
  • InnoDB行锁等待超时默认为50秒,一般建议设置5-10秒就够了;
  • 有时候,可能会口误把 长时间的行锁等待 说成是 死锁,其实二者完全不一样,不要犯这种常识性口误。

好了,正式开始今天的案例。

先看测试表DDL:

yejr@imysql.com [yejr]>show create table d\G
********************** 1. row **********************
       Table: d
Create Table: CREATE TABLE `d` (
  `i` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

yejr@imysql.com [yejr]>select * from d;
+---+
| i |
+---+
| 1 |
+---+

然后我们执行下面的测试:

session1 session2 session3
begin;
delete from d where i=1;
   
  begin;
insert into d select 1;
 
    begin;
insert into d select 1;
commit;    
  Query OK, 1 row affected (11.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这时候我们看下InnoDB STATUS的输出

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-02 14:59:08 0x700004208000
*** (1) TRANSACTION:
TRANSACTION 274616, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 16, OS thread handle 123145373167616, query id 398 localhost root executing
insert into d select 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 510 page no 3 n bits 72 index PRIMARY of table `yejr`.`d` trx id 274616 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000430b3; asc     0 ;;
 2: len 7; hex 3b0000018027a4; asc ;    ' ;;

*** (2) TRANSACTION:
TRANSACTION 274617, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 18, OS thread handle 123145371549696, query id 400 localhost root executing
insert into d select 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 510 page no 3 n bits 72 index PRIMARY of table `yejr`.`d` trx id 274617 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000430b3; asc     0 ;;
 2: len 7; hex 3b0000018027a4; asc ;    ' ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 510 page no 3 n bits 72 index PRIMARY of table `yejr`.`d` trx id 274617 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000430b3; asc     0 ;;
 2: len 7; hex 3b0000018027a4; asc ;    ' ;;

*** WE ROLL BACK TRANSACTION (2)

从上面这个输出来看,我们看到的现场是两个 insert 请求发生了死锁。单纯看这2个SQL的话,应该是产生锁等待才对,而不是死锁。

按照我们常规理解,session1 未 commit 前,应该是持有 i=1 上的record lock(X),而session2 和 session3 则都在等待这个锁的释放。而实际上呢,肯定不是这样的,否则也不至于发生死锁了。

关于InnoDB行锁更详细的知识点我们以后找时间再说。这次的案例其实在MySQL官方文档上已经解释过了,而且也给了演示案例(如本例)。文档中是这么说的:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

【敲黑板、划重点】If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. 

划重点的核心内容是:当需要进行唯一性冲突检测时,需要先加一个 S 锁。这样的话,上面的测试案例加锁过程就不是之前推测的那样,而是像下面这样了:

session1 session2 session3
begin;
delete from d where i=1;
持有i=1的record lock(X)
   
  begin;
insert into d select 1;

检测到冲突,请求i=1的record lock(S)被阻塞,等待ing

 
    begin;
insert into d select 1;

检测到冲突,请求i=1的record lock(S)被阻塞,等待ing

commit;

提交,释放i=1上的锁

   
  成功insert,并持有i=1的record lock(X)

Query OK, 1 row affected (11.82 sec)
Records: 1 Duplicates: 0 Warnings: 0

请求i=1上的record lock(X),触发死锁检测,失败
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

上面这个例子中,i 列如果改成 唯一索引,也是同样的结果。因为只有 主键以及唯一索引才需要进行唯一冲突检测,非唯一普通索引无需检测。

另一个类似的案例见下:

session1 session2
begin;
select * from d where i = 1 lock in share mode;
 
  begin;
select * from d where i = 1 lock in share mode;
delete from d where i = 1;

被session2阻塞了,等待中

 
  delete from d where i = 1;

检测到死锁

通过上面这两个案例,其实想要告诉大家的是:发生死锁时,不能只看现场,还得分析过程,才能知道真正的原因。

死锁发生条件:
1、至少2个独立的线程(会话)。
2、单位操作中包含多个相对独立的获得临界区资源步骤,有一定的时间差
3、多个线程(会话)之间由于需要获得某一个临界区资源必须有相互等待的情况发生,并且等待出现环状。

参考

意想不到的MySQL复制延迟原因

导读

线上有个MySQL实例,存在严重的复制延迟问题,原因出乎意料。

线上有个MySQL 5.7版本的实例,从服务器延迟了3万多秒,而且延迟看起来好像还在加剧。

MySQL版本

Server version:     5.7.18-log MySQL Community Server (GPL)

看下延迟状况

yejr@imysql.com:mysql3306.sock : (none) > show slave status\G
              Master_Log_File: mysql-bin.013225
          Read_Master_Log_Pos: 1059111551
        Relay_Master_Log_File: mysql-bin.013161
          Exec_Master_Log_Pos: 773131396
                  Master_UUID: e7c35a95-ffb1-11e6-9620-90e2babb5b90

我们看到,binlog文件落后了64个,相当的夸张。

MySQL 5.7不是已经实现并行复制了吗,怎么还会延迟这么厉害?

先检查系统负载。
先看I/O子系统负载,没看到这方面存在瓶颈。

再看mysqld进程的CPU消耗。

可以看到mysqld进程的CPU消耗长时间超过100%。

再检查MySQL复制现场,确认了几个频繁更新的表都有主键,以及必要的索引,相应的DML操作,也几乎都是基于主键或唯一索引条件执行的,排除无主键、无合理索引方面的因素。

最后只能祭出perf top神器了。

perf top -p `pidof mysqld`

看到perf top最后的报告是这样的

Samples: 107K of event 'cycles', Event count (approx.): 29813195000                                                                                                                              
Overhead  Shared Object        Symbol                                                                                                                                                            
  56.19%  mysqld               [.] bitmap_get_next_set                                                                                                                                           
  16.18%  mysqld               [.] build_template_field                                                                                                                                          
   4.61%  mysqld               [.] ha_innopart::try_semi_consistent_read                                                                                                                         
   4.44%  mysqld               [.] dict_index_copy_types                                                                                                                                         
   4.16%  libc-2.12.so         [.] __memset_sse2                                                                                                                                                 
   2.92%  mysqld               [.] ha_innobase::build_template

我们看到, bitmap_get_next_set 这个函数调用占到了 56.19%,非常高,其次是 build_template_field 函数,占了 16.18%。

经过检查MySQL源码并请教MySQL内核开发专家,最后确认这两个函数跟启用表分区有关系。查询下当前实例有多少个表分区:

yejr@imysql.com:mysql3306.sock : (none) > select count(*) from partitions where partition_name is not null;
+----------+
| count(*) |
+----------+
|    32128 |
+----------+
1 row in set (11.92 sec)

我勒个去,竟然有3万多个表分区,难怪上面那两个函数调用那么高。

这个业务数据库几个大表采用每天一个分区方案,而且把直到当年年底所有分区也都给提前创建好了,所以才会有这么多。

不过,虽然有这么多表分区,在master服务器上却不存在这个瓶颈,看起来是在主从复制以及大量表分区的综合因素下才有这个瓶颈,最终导致主从复制延迟越来越严重。

知道问题所在,解决起来就简单了。把直到下个月底前,其余还用不到的表分区全部删除,之后约只剩下1.6万个分区。重启slave线程,问题解决,主从复制延迟很快就消失了。

最后,这个问题对应的bug(#85352)在此,已在5.6.38、5.7.20、8.0.3等版本修复了。