mysql

Table of Contents

目的

对mysql的几个技术点做出总结,提供以后遇到问题的排查思路,算是增进学习

索引列存在很多NULL

MySQL查询优化器决策是否使用某个索引执行查询时的依据是使用该索引的成本是否足够低，而成本很大程度上取决于需要扫描的二级索引记录数量占表中所有记录数量的比例 对待Null总共分为3种方式: mysql提供了一个名为innodb_stats_method的系统变量，相当于在计算某个索引列不重复值的数量时如何对待NULL值这个锅甩给了用户，这个系统变量有三个候选值：

  1. nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。 如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问
  2. nulls_unequal：认为所有NULL值都是不相等的,如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问
  3. nulls_ignored：直接把NULL值忽略掉

两种不同的统计数据存储方式

InnoDB提供了两种存储统计数据的方式：

  1. 永久性的统计数据 这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
  2. 非永久性的统计数据 这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据
    MySQL提供系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据,在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中 不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中.可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

当STATS_PERSISTENT=1时，表明把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明把该表的统计数据临时的存储到内存中。如果在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值

深度分页

MySQL是在server层准备向客户端发送记录的时候才会去处理LIMIT子句中的内容，由于MySQL实现LIMIT子句的局限性，在处理诸如LIMIT 5000, 1这样的语句时就无法通过使用二级索引来加快查询速度,通过子查询,利用2级索引,或者主键索引就可以实现

SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
    WHERE t.id = d.id;

SELECT id FROM t ORDER BY key1 LIMIT 5000, 1作为一个子查询单独存在，由于该子查询的查询列表只有一个id列，MySQL可以通过仅扫描二级索引idx_key1执行该子查询，然后再根据子查询中获得到的主键值去表t中进行查找

聚簇索引

这个实际上关注点在于是否回表查询

Count

对于COUNT()、COUNT(常数)、COUNT(主键)形式的COUNT函数来说，优化器可以选择最小的索引执行查询，从而提升效率，执行过程是一样的，只不过在判断表达式是否为NULL时选择不同的判断方式，这个判断为NULL的过程的代价可以忽略不计，所以COUNT()、COUNT(常数)、COUNT(主键)所需要的代价是相同的
而对于COUNT(非主键列)来说，server层必须要从InnoDB中读到包含非主键列的记录，所以优化器并不能选择代价最小的索引
InnoDB的记录都是存储在数据页中的（页面大小默认为16KB），而每个数据页的Page Header部分都有一个统计当前页面中记录数量的属性PAGE_N_RECS,假设执行COUNT函数的时候直接去把各个页面的这个PAGE_N_RECS属性相加
对于普通的SELECT语句来说，每次查询都要从记录的版本链上找到可见的版本才算是读到了记录；对于加了FOR UPDATE或LOCK IN SHARE MODE后缀的SELECT语句来说，每次查询都要给记录添加合适的锁。所以这个读取每一条记录的过程,在InnoDB的实现中是无法跳过的，InnoDB必须读一条记录，返给server层一条记录。

GROUP BY

查找sql_mode的系统变量

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

关注ONLY_FULL_GROUP_BY,不允许非分组列放到查询列表中

InnoDB加锁

锁是一个内存结构，InnoDB中用lock_t这个结构来定义 其中的type_mode是用于区分这个锁结构到底是行锁还是表锁，如果是表锁的话是意向锁、直接对表加锁、还是AUTO-INC锁，如果是行锁的话，具体是正经记录锁、gap锁还是next-key锁 在InnoDB的实现中，InnoDB的行锁是与记录一一对应的。即使是对于gap锁来说，在实现上也是为某条记录生成一个锁结构，然后该锁结构的类型是gap锁而已，并不是专门为某个区间生成一个锁结构。该gap锁的功能就是每当有别的事务插入记录时，会检查一下待插入记录的下一条记录上是否已经有一个gap锁的锁结构，如果有的话就进入阻塞状态 加锁就是在内存中生成这样的一个锁结构（除了生成锁结构，还有一种称作隐式锁的加锁方式，不用生成锁结构）一种优化方案，即同一个事务，在同一个页面上加的相同类型的锁都放在同一个锁结构里 一条语句加什么锁受多种因素影响:

  1. 事务的隔离级别
  2. 语句执行时使用的索引类型（比如聚簇索引、唯一二级索引、普通二级索引）
  3. 是否是精确匹配
  4. 是否是唯一性搜索
  5. 具体执行的语句类型（SELECT、INSERT、DELETE、UPDATE）
  6. 是否开启innodb_locks_unsafe_for_binlog系统变量
  7. 记录是否被标记删除

过程:

  1. MySQL是分server层和存储引擎层两部分，每当执行一个查询时，server层负责生成执行计划，即选取即将使用的索引以及对应的扫描区间。我们这里以InnoDB为例，针对每一个扫描区间，都会：
  2. server层向InnoDB要扫描区间的第1条记录
  3. InnoDB通过B+树定位到扫描区间的第1条记录（如果定位的是二级索引记录并有回表需求则回表获取完整的聚簇索引记录），然后返回给server层
  4. server层判断记录是否符合搜索条件，如果符合则发送给客户端，不符合则跳过。继续向InnoDB要下一条记录 此处将记录发送给客户端其实是发送到本地的网络缓冲区，缓冲区大小由net_buffer_length控制，默认是16KB大小。等缓冲区满才真正发送网络包到客户端
  5. InnoDB根据记录的单向链表以及页面之间的双向链表找到下一条记录（如果定位的是二级索引记录并有回表需求则回表获取完整的聚簇索引记录），返回给server层。
  6. server层处理该记录，并向InnoDB要下一条记录
  7. 重复,直到InnoDB读到一条不符合边界条件的记录为止

可见一般情况下，server层和存储引擎层是以记录为单位进行通信的，而InnoDB读取一条记录最重要的函数就是row_search_mvcc： row_search_mvcc里的关于加锁的代码就会发现，其实流程还是很简单的：

  1. 定位扫描区间的第一条记录。
  2. 步骤2. 如果扫描区间是从右到左扫描，那么需要给扫描区间最右边的记录的下一条记录添加一个gap锁（在隔离级别不小于REPEATABLE READ并且也没有开启innodb_locks_unsafe_for_binlog系统变量的情况下）。
  3. 对于Infimum记录是不加锁的，对于Supremum记录加next-key锁（在隔离级别不小于REPEATABLE READ并且也没有开启innodb_locks_unsafe_for_binlog系统变量的情况下）。
  4. 对于精确匹配的扫描区间来说，当扫描区间中的记录都被读完后，需对扫描区间后的第一条记录加一个gap锁即可，并且向server层返回可结束本扫描区间的查询的信息（在隔离级别不小于REPEATABLE READ并且也没有开启innodb_locks_unsafe_for_binlog系统变量的情况下）。
  5. 事务的隔离级别不大于RC，开启innodb_locks_unsafe_for_binlog系统变量，唯一性搜索并且该记录的delete_flag不为1，对于>= 主键的这种边界条件来说，当前记录恰好是开始边界记录，则对记录加正经记录锁，否则添加next-key锁。
  6. 判断ICP条件是否成立。如果当前记录是二级索引记录，并且已经不在扫描区间中，则向server层返回可结束本扫描区间的查询的信息
  7. 如果对二级索引记录进行加锁，还需要对相应的聚簇索引记录加正经记录锁（使用覆盖索引，并且加S型锁的记录可跳过此步骤）
  8. 判断当前记录是否已不在扫描区间中，如果不在的话，则向server返回可结束本扫描区间的查询的信息
  9. 如果server层收到可结束本扫描区间的查询的信息，则结束本扫描区间的查询，否则继续向InnoDB要下一条记录，InnoDB根据记录所在的链表获取到下一条记录后，从步骤3开始新一轮的轮回 即使是全表扫描的加锁读，加的也是行锁而不是表锁，当进行全表扫描时，其实就是相当于扫描主键值在(-∞, +∞)这个扫描区间中的聚簇索引记录，针对每一条聚簇索引记录，都需要执行一次row_search_mvcc函数，都需要进行如上所述的各种判断，最后决定给扫描的记录加什么锁

Innodb状态

使用SHOW ENGINE INNODB STATUS这个命令来查看当前系统中每个tx都加了哪些锁：

mysql> SHOW ENGINE INNODB STATUS\G
...此处省略很多信息
------------
TRANSACTIONS
------------
Trx id counter 38944
Purge done for trx's n:o < 38452 undo n:o < 0 state: running but idle
History list length 262
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479631937824, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 38938, ACTIVE 1875 sec
2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 29, OS thread handle 123145576628224, query id 690 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 38938, sees < 38938

...此处省略很多信息
只保留关于TRANSACTIONS的相关信息，这里列出了每个事务获取锁的情况。如果我们想看到更详细的加锁情况，可以开启innodb_status_output_locks：
mysql> SET GLOBAL innodb_status_output_locks = ON;
Query OK, 0 rows affected (0.01 sec)
死锁
SHOW ENGINE INNODB STATUS来查看关于InnoDB存储引擎的一些状态信息，系统最近一次发生死锁时的加锁情况
mysql> SHOW ENGINE INNODB STATUS\G
...省略了好多其他信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-06-20 13:39:19 0x70000697e000
*** (1) TRANSACTION:
TRANSACTION 30477, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics
select * from hero where id = 3 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000007517; asc     u ;;
 2: len 7; hex 80000001d0011d; asc        ;;
 3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
 4: len 3; hex e89c80; asc    ;;

*** (2) TRANSACTION:
TRANSACTION 30478, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
select * from hero where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000007517; asc     u ;;
 2: len 7; hex 80000001d0011d; asc        ;;
 3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
 4: len 3; hex e89c80; asc    ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000007517; asc     u ;;
 2: len 7; hex 80000001d00110; asc        ;;
 3: len 7; hex 6ce58898e5a487; asc l      ;;
 4: len 3; hex e89c80; asc    ;;

*** WE ROLL BACK TRANSACTION (2)
------------
...

分析思路,1, 查看死锁日志2. 分析等待死锁的其他事务

binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

上例中bin_log系统变量的值为ON，生成binlog，若为OFF表明不生成binlog。 同一条SQL语句，随着启动选项binlog-format的不同，可能生成不同类型的binlog事件：

  1. 当以启动选项–binlog-format=STATEMENT启动MySQL服务器时，生成的binlog称作基于语句的日志。此时只会将一条SQL语句将会被完整的记录到binlog中，而不管该语句影响了多少记录。
  2. 当以启动选项–binlog-format=ROW启动MySQL服务器时，生成的binlog称作基于行的日志。此时会将该语句所改动的记录的全部信息都记录上。
  3. 当以启动选项–binlog-format=MIXED启动MySQL服务器时，生成的binlog称作基于行的日志。此时在通常情况下采用基于语句的日志，在某些特殊情况下会自动转为基于行的日志（这些具体情况请参考：