06. 全局锁和表锁：给表加个字段怎么有这么多障碍

根据加锁的范围，MySQL里面的锁大概可以分成全局锁、表级锁和行锁。
这一节聊一聊全局锁和表级锁，锁的设计比较复杂，不会设计锁的具体实现细节，主要介绍的是碰到锁时的现象和其背后的原理。

全局锁
全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock(FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的一下语句会被阻塞；数据更新语句（数据的增伤改）、数据定义语句（包括建表、修改表结构）和更新类书屋的提交语句。
典型使用场景：全库逻辑备份。
通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份。整个备份过程中，数据库完全处于只读状态。

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数-single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
那么为什么有mysqldump，还需要FTWRL呢？一致性读是好，但前提是存储引擎要支持这个隔离级别。比如，对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这是，我们就需要FTWRL命令了。
所以，single-transaction方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法。这往往是DBA要求业务开发人员使用InnoDB替代MyISAM的原因之一。

那么问题来了，既然要全库只读，为什么不适用set global readonly = true的方式呢？
确实readonly方式也可以让全库进入只读状态，但还是建议使用FTWRL的方式，主要有两个原因：

  1. 有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大。
  2. 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

业务的更新不只是增删改数据（DML），还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，对里面任何一个表做加字段操作，都是会被锁住的。

表级锁
MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（metadata lock，简称MDL）。
表锁的语法是lock tables ... read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的呃线程的读写以外，也先限定了本线程接下来的操作对象。
举个栗子：如果在某个线程A中执行了lock tables t1 read, t2 write;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不适用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是元数据锁。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。
在MySQL5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表结构变更操作的时候，加MDL写锁。

  • 读锁之间不互斥，因此你可以有多个线层同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

事务中的MDL锁，在语句执行时开始申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

讨论问题：如何安全地给小表加字段？
首先要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema库的innodb_trx表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。
但是，如果要变更的表是一个热点表，虽然数据流不大，但是上面的请求很频繁，而你不得不加个字段，应该怎么做？
比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

小结
这一节学习了MySQL的全局锁和表级锁。
全局锁主要用于逻辑备份。对于全部是InnoDB引擎的库，建议选择使用-single-transaction参数，对应用会更友好。
表锁一般是在数据库引擎不支持行锁的时候才会被用到的，建议升级成InnoDB引擎。
MDL会在语句执行时申请锁，知道事务提交才释放，变更表结构的时候，一定要小心不要导致锁住线上查询和更新。

小问题
备份一般都会在备库上执行，你在用-single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一个列。这时候，从备库上会看到什么现象呢？
解析：
假设这个DDL是针对表t1的，这里备份过程中的关键语句如下：

  1. -- 开启RR
  2. Q1: set session transaction isolation level repeatable read;
  3. -- 启动事务
  4. Q2: start transaction with consistent snapshot;
  5. -- 保存点
  6. Q3: savepoint sp;
  7. -- 时刻1
  8. Q4: show create table `t1`;
  9. -- 时刻2
  10. Q5：select * from `t1`;
  11. -- 时刻3
  12. Q6: rollback to savepoint sp;
  13. -- 时刻4
  14. /* other tables */

备份开始的时候，为了确保RR（可重复读）隔离级别，再设置一次RR隔离级别（Q1）；
启动事务，这里用WITH CONSISTENT SHAPSHOT确保这个语句执行完就可以得到一个一致性视图（Q2）；
设置一个保存点，这个很重要（Q3）；
show create是为了拿到表结构（Q4），然后正式导数据（Q5），回滚到savepoint sp，在这里的作用是释放t1的MDL锁（Q6）。

DDL从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

  1. 如果在时刻1之前到达，现象：没有影响，备库拿到的是DDL后的表结构。
  2. 如果在时刻2到达，则表结构被改过，Q5执行的时候，报Table definition has changed, please retry transaction，现象：mysqldump终止。
  3. 如果在时刻2和时刻3之间到达，mysqldump占着t1的MDL读锁，binlog被阻塞，现象：主从延迟，知道Q6执行完成。
  4. 如果在时刻4之后到达，mysqldump释放了MDL读锁，现象：没有影响，备库拿到的是DDL前的表结构。

07. 行锁功过：怎么减少行锁对性能的影响

MySQL的行锁是引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

这一节学习InnoDB的行锁，以及如何通过减少锁冲突来提升业务并发度。

两阶段锁
举个栗子：在下面的操作序列中，事务B的update语句执行时会是什么现象呢？假设字段id是表t的主键。
image.png
这个问题的结论取决于事务A在执行完两条update语句后，持有哪些锁，以及在什么时候释放。实际上事务A是由的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。

两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不仅需要了就立刻释放，而是要等事务结束后才释放。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

假设你负责实现一个电影票在线交易业务，顾客A要在影院B购买电影票。简化一点，这个业务需要设计以下操作：

  1. 从顾客A账户余额中扣除电影票价；
  2. 从影院B的账户余额增加这种电影票价；
  3. 记录一条交易日志。

也就是说，要完成这个交易，我们需要update两条记录，并insert一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？
试想如果同时有另外一个顾客C要在影院B买票，那么这个事务冲突的部分就是语句2.因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，你的MySQL就挂了。你登上服务器一看，CPU消耗接近100%，但整个数据库每秒就执行不到100个事务。这是什么原因呢？

这里，就要说到死锁和死锁检测了。

死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无线等待的状态，称为死锁。
举个栗子：
image.png
这时候，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。
事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。
当出现死锁以后，有两种策略：

  1. 直接进入等待，直到超时。这个超时时间可以通过参数innodb_wait_timeout来设置。
  2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，标识开启这个逻辑。

在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
但是，我们又不可能把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且innodb_deadlock_detect的默认值本身就是on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并非线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。

问题解决：如何解决由这种热点行更新导致的性能问题呢？
痛点在于：死锁检测要耗费大量的CPU资源。

第一种，头痛医头，如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当作一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。
第二种，控制并发度。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。
因此，这个并发控制要最做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改MySQL源码的人，也可以做在MySQL里面。基于思路就是：对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。

如果团队里暂时没有数据库方面的专家，不能实现这样的方案，能不能从设计上优化这个问题呢？
可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加余额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少等待个数，也就减少了死锁检测的CPU消耗。

这个方案看上去是无损的，但其实这类方案需要根据业务做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成0的时候，代码要有特殊处理。

小结
两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不仅需要了就立刻释放，而是要等事务结束后才释放。
安排事务语句：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。
死锁检测：

  1. 超时回滚，通过参数innodb_wait_timeout设置。
  2. 主动检测思索，通过参数innodb_deadlock_detect设置。

热点行更新导致的性能问题：

  1. 数据库服务器端做并发控制。修改MySQL源码：对于相同行的更新，在进入引擎之前排队。或者考虑在中间件消息队列实现。
  2. 考虑将一行改成逻辑上的多行来减少锁冲突。

小问题
如果你要删除一个表里面的前10000行数据，有以下三种方法：

  1. 直接执行：delete from T limit 10000；
  2. 在一个连接中循环执行20次：delete from T limit 500；
  3. 在20个连接中同时执行：delete fron T limit 500。

解析：
选择第二种方式。
第一种方式：单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
第三种方式：会人为造成锁冲突。

08. 事务到底是隔离还是不隔离的

前情回顾
事务为可重复读级别，事务T启动的时候会创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了诗句，事务T看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像与世无争，不受外界影响。

问题引入
一个事务要更新一行，如果刚好有另外一个事务拥有这一行的行锁，它又不能这么超然了，会被锁住，进入等待状态。问题是，既然进入了等待状态，那么等到这个事务自己获取到行锁要更新数据的时候，它读到的值又是什么呢？

举个栗子，表的初始化语句如下：

  1. create table `t` (
  2.     `id` int(11) NOT NULL,
  3.   `k` int(11) FDEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) engine = InnoDB
  7. insert into t(id, k) values(1, 1), (2, 2);

image.png
这里，我们需要注意的时事务的启动时机：

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot这个命令。

在这个例子中，事务C没有显示地使用begin/commit， 表示这个update语句本身就是一个事务，语句完成的时候会启动提交。事务B在更新了行之后查询；事务A在一个只读事务中查询，并且时间顺序上是在事务B的查询之后。

这时，事务B查到的k的值3，而事务A查到的k的值是1。

事务视图
view视图是用于查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。
InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Commited，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

抛出问题：快照在MVCC里是怎么工作的？
在可重复读隔离级别下，事务在启动的时候就拍了个快照。注意，这个快照是基于整库的。
InnoDB里面每个事务都有一个唯一的事务ID，叫做transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按照顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。

如下图所示，就是一个记录被多个事务连续更新后的状态。
image.png
图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如：需要V2的时候，就是通过V4依次执行U3、U2算出来。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
因此，一个事务只需要在启动的时候声明说：以我启动的时刻为准，如果一个数据版本是我启动之前生成的，就认；如果是我启动以后才生成的，就不认，我必须要找到它的上一个版本。
当然，如果上一个版本也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动时瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。
数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
而数据版本的可见性规划，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。
这个视图数组把所有的row trx_id分成了几种不同的情况。
image.png
这样，对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

  1. 如果落在绿色部分，表示这个版本是已提交的事务或者当前事务自己生成的，这个数据是可见的；
  2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
  3. 如果落在黄色部分，那就包括两种情况：
    1. 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
    2. 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

问题解答
回到前面的问题，分析下事务A的语句返回结果，为什么k=1。
不妨假设：

  1. 事务A开始前，系统里面只有一个活跃事务ID是99；
  2. 事务A、B、C的版本号分别是100、101、102，且当前系统里只有这四个事务；
  3. 三个事务开始前，(1, 1) 这一行数据的row trx_id是99。

这样，事务A的视图数组就是[99.100]，事务B的视图数组[99, 100, 101]，事务C的视图数组是[99, 100, 101, 102]。
image.png
第一个有效更新是事务C，把数据从(1, 1)改成了(1, 2)。这时候，这个数据的最新版本row trx_id是102，而90这个版本已经成为了历史版本。
第二个有效更新是事务B，把数据从(1, 1)改成了(1, 3)。这时候，这个数据的最新版本row trx_id是101，而102又成了历史版本。
事务A查询的时候，其实事务B还没有提交，但是它生成的(1, 3)这个版本已经变成当前版本了。但这个版本对事务A必须是不可见的，否则就变成脏读了。
事务A读取数据的时候，它的视图数组是[99, 100]。当然了，读数据都是从当前版本读起的。

使用以上视图数组判断，事务A查询语句的读数据流程是这样的：

  1. 找到(1, 3) 的时候，判断出row trx_id = 101，比高水位大，处于红色区域，不可见；
  2. 接着，找到上一个历史版本，一看row trx_id = 102，比高水位大，处于红色区域，不可见；
  3. 再往前找，终于找到了(1, 1)，它的row trx_id = 90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务A不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

  1. 版本未提交，不可见；
  2. 版本已提交，但是是在视图创建后提交的，不可见；
  3. 版本已提交，而且是在视图创建前提交的，可见。

用上述规则判断查询结果，事务A的查询语句的视图数组是在事务A启动的时候生成的，这时候：

  • (1, 3)还没提交，属于情况1，不可见；
  • (1, 2)虽然提交了，但是是在视图数组创建之后提交的，属于情况2，不可见；
  • (1, 1)是在视图数组创建之前提交的，可见。

使用时间先后顺序来判断，分析起来轻松不少。

更新逻辑
事务B的update逻辑，如果按照一致性读，那么结果应该是（1, 1）。
image.png
事务在更新之前查询一次数据，这个查询返回的k值确实是1。
但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。
因此，事务B此时的set k = k + 1是在(1, 2)的基础上进行的操作。

所以，这里的规则是：更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读。
因此，在更新的时候，当前读拿到的数据是(1, 2)，更新后生成了新版本的数据(1, 3)，这个新版本的row trx_id是101。

除了update语句外，select语句如果加锁，也是当前读。

所以，如果把事务A的查询语句select * from t where id = 1修改一下，加上lock in share mode或者for update，也可以读到版本号是101的数据，返回的k的值是3。下面这两个select语句，就是分别加了读锁和写锁：

  1. select k from t where id=1 lock in share mode;
  2. select k from t where id=1 for update;

再往前一步，假设事务C不是马上提交的，而是变成了下面的事务C，会怎么样呢？
image.png
事务C的不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。前面说过，虽然事务C还没提交，但是(1, 2)这个版本也已经生成了，并且是当前的最新版本。那么，事务B的更新语句会怎么处理呢？

这时候，我们在上一节中提到的“两阶段锁协议”就要上场了。事务C没提交，也就是说(1, 2)这个版本上的写锁还没释放。而事务B是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务C释放这个锁，才能继续它的当前读。

image.png
回到本节开头的问题：事务的可重复读是怎么实现的？
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

  • 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
  • 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

那么，在读已提交隔离级别下，事务A查到的k=3，事务B查到的k=2。

小结
InnoDB的行数据有多个版本，每个数据版本有自己row trx_id，每个事务或者语句有自己的一致性视图。
普通查询语句是一致性读，一致性会根据row trx_id和一致性视图确定数据版本的可见性。

  • 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
  • 对于读已提交，查询只承认在语句启动前就已经提交完成的数据。

而当前读，总是读取已经提交完成的最新版本。

小问题
事务隔离级别是可重复读，表的初始化语句如下：

  1. CREATE TABLE `t` (
  2. `id` int(11) NOT NULL,
  3. `c` int(11) DEFAULT NULL,
  4. PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB;
  7. insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

现在，把所有“字段c和id值相等的行”的c值清零，但是却发现了一个诡异的、改不掉的情况。
请你构造出这种情况，并说明其原理。
解析：
场景构造如下：
image.png
image.png
以上两种场景都能让session A复现以下效果：
image.png

09. 普通索引和唯一索引，应该怎么选择

场景引入
假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语句：

  1. select name from user where id_card = '....';

所以，你一定会考虑在id_card字段上建索引。

由于身份证号字段比较大，我不建议你把身份证号当做主键，那么现在有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。
那么，从性能的角度考虑，选择唯一索引还是普通索引呢？选择的依据是什么？
image.png
下面，从查询语句和更新语句的性能影响来进行分析。

查询过程
假设，执行查询的语句是select id from T where k = 5。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

  • 对于普通索引来说，查找到满足条件的第一个记录(5, 500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是这个不同带来的性能差距是微乎其微的。

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，对于整形字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，扔可以认为这个操作成本对于现在的CPU来说可以忽略不计。

更新过程
为了说明普通索引和唯一索引对更新语句性能的影响这个问题，我们先了解一下change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4, 400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要讲数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

如果要在这张表中插入一个新记录(4, 400)的话，InnoDB的处理流程如下：

  1. 这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：
    1. 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
    2. 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

这种情况下，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。

  1. 这个记录要更新的目标页不在内存中。这是，InnoDB的处理流程如下：
    1. 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
    2. 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

将数据从磁盘读入内存设计随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景
从上面InnoDB的处理流程可以了解到，change buffer对于更新过程的加速作用，也只限于用在普通索引的场景下，而不适用于唯一索引。
那么，现在有一个问题就是：普通索引的所有场景，使用change buffer都可以起到加速作用吗？
因为merge的时候是真正进行数据更新的时刻，而change buffer 主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多，收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问的概率比较小，此时change buffer的使用效果最好。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即出发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

索引选择和实践
唯一索引和普通索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change bufer都能提升更新性能。
在实际使用中，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。
特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把change buffer尽量开大，以确保这个“历史数据”表的数据写入速度。

小结
从索引选择开始，学习了数据的查询和更新过程，然后了解了change buffer的机制以及应用场景，最后进行了索引选择的实践。
由于唯一索引用不上change buffer的优化机制，因此如果如果业务可以接受，从性能角度出发建议优先考虑非唯一索引。

小问题
change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事儿，再从磁盘读入数据可就没有merge过程，就等于是数据丢失了，会不会出现这种情况呢？
解析：
答案是不会丢失。虽然是只更新内存，但是在事务提交的时候，change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。

10. MySQL为什么有时候会选错索引

场景引入
建一个简单的表，表里有a、b两个字段，并分别建上索引：

  1. create table `t` (
  2.  `id` int(11) NOT NULL,
  3.   `a` int(11) DEFAULT NULL,
  4.   `b` int(11) DEFAULT NULL,
  5.   PRIMARY KEY (`id`),
  6.   KEY `a`(`a`),
  7.   KEY `b`(`b`)
  8. ) engine = InnoDB;

然后，往表t中插入1-万行数据，取值按证书递增，即(1, 1, 1) ~ (100000, 100000, 100000)。
使用存储过程插入数据：

  1. delimiter ;;
  2. create procedure idata()
  3. begin
  4.  declare i int;
  5.  set i=1;
  6.  while(i<=100000)do
  7.  insert into t values(i, i, i);
  8.  set i=i+1;
  9.  end while;
  10. end;;
  11. delimiter ;
  12. call idata();

接下来，分析一条SQL语句：

  1. select * from t where a between 10000 and 20000;

通过explain查看这条语句的执行情况：
image.png
查询语句的执行确实符合预期，key这个字段值是a，表示优化器选择了索引a。
但是，如果在我们准备好的包含10万行数据的表上，做如下操作：
image.png
这里，session A开始了一个事务。随后，session B把数据都删除后，又调用了 idata 这个存储过程，插入了10万行数据。
这时候，session B的查询语句select * from t where a between 10000 and 20000就不会再选择索引a了。
可以通过慢查询日志来查看具体的执行情况。
为了说明优化器选择的结果是否正确，增加一个对照，即：使用force index(a)来让优化器强制使用索引a。
在session B下执行以下三条SQL语句，即为实验内容：

  1. set long_query_time=0;
  2. select * from t where a between 10000 and 20000; /*Q1*/
  3. select * from t force index(a) where a between 10000 and 20000;/*Q2*/
  1. 将慢查询日志的阈值设置为0，表示这个线程加下来的语句都会被记录入慢查询日志中；
  2. Q1是session B原始查询；
  3. Q2是加了force index(a)的查询。

如下图是执行完成后的慢查询日志：
image.png
可以看到，Q1的Rows_examined为10万行，显然是走了全表扫描，执行时间是79ms。Q2的Rows_examined扫描了10001行，执行了9ms。也就是说，我们在没有使用force index的时候，MySQL用错了索引，导致了更长的执行时间。

在这个删除历史数据和新增数据的场景里，MySQL竟然用错了索引。

优化器的逻辑
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。
当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
上面简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描行数的时候出问题了。

那么，问题就是：扫描行数是怎么判断的？
MySQL在真正开始执行语句之前，并不能景区精确地满足这个条件的记录有多少条，而只能根据统计信息来估计记录数。
这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”。也就是说，这个技术越大，索引的区分度越好。
我们可以使用show index方法，看到一个索引的基数。如下图所示，就是表t的show index将结果。索然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。
image.png
那么，MySQL是怎样得到索引的基数的呢？这里，MySQL2021年8月29日 14:51:4是进行采样统计的。
为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。
采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。
在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

  • 设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
  • 设置为off的时候，表示统计信息值存储在内存中。这时，默认的N是8，M是16。

由于是采样统计，所以不管N是20还是8，这个基数都是很容易不准的。

但，这还不是全部。
上图中可以看到，这次的索引统计值虽然不够精确，但是大体上还是差不多的，选错索引一定还有别的原因。
其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。

接下来，再看一下优化器预估的，这两个语句的扫描行数是多少。
image.png
rows这个字段表示的是预计扫描行数。
其中，Q1的结果还是符合预期的，rows的值是100768；但是Q2的值是10001，偏差就大了。
而最开始的explain命令看到的rows只有10001行，是这个误差误导了优化器的判断。

那么，优化器为什么放着扫描10001行的执行计划不用，却选择了扫描行数是100768的执行计划呢？
这是因为，如果使用索引a，每次从索引a上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
而如果选择扫描10万行，是直接在主键索引上扫描的，没有额外的代价。
优化器会估算这两个选择的代价，从结果来看，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

MySQL选错索引，根本原因是没能准确地判断出扫描行数。
既然是统计信息不对，那就修正。analyze table t命令，可以用来重新统计索引信息。
执行效果如下：
image.png
这次的结果就是正确的。

实践：发现explain的结果预估的rows值跟实际情况差距较大，可以采用这个方法来处理。
其实，如果只是索引统计信息不准确，通过analyze命令解决很多问题，但是优化器不止看扫描行数。

基于上表，看另外一个语句：

  1. select * from t where (a between 1 and 1000) and (b between 5000 and 100000) order by b limit 1;

从条件上看，这个查询没有符合条件的记录，因此会返回空集合。

在开始执行这个语句之前，可以先设想一下，如果你来选择索引，会选择哪一个？
为了便于分析，先看一下a、b这两个索引的结构图：image.png
如果使用索引a进行查询，那么就是扫描索引a的前1000个值，然后取到对应的id，再到主键索引上去查出每一行，然后根据字段b来过滤。显然这样需要扫描1000行。
如果使用索引b进行查询，那么就是扫描索引b的最后50001个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描50001行。

下图是explain的执行结果：
image.png
可以看到，返回结果中key字段显示，这次优化器选择了索引b，而rows字段显示需要扫描的行数50128。

从这个结果可以得出两个结论：

  1. 扫描行数的估计值不准确；
  2. 这个例子中MySQL又选错了索引。

索引选择异常和处理
其实大多数时候优化器都能找到正确的索引，但偶尔还是会碰到上面举例的这两种情况：原本可以执行得很快的SQL语句，执行速度却比预期的慢很多，应该怎么办呢？

第一种方法就是，采用force index强行选择索引。MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中以此判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

下面是第二个查询语句的执行效果：
image.png
原本需要执行0.14s，使用force index之后，只用了0.01s。
也就是说，优化器没有选择正确的索引，force index起到了矫正的作用。

不过很多程序员不喜欢使用force index，一是这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。
但其实使用force index最主要的问题还是变更的及时性。。因为选错索引的情况还是比较少出现的，所以开始的时候通常不会写上force index。而是等到线上出现问题的时候，才会去修改SQL语句、加上force index。但是修改之后还要测试和发布，对于生产系统来说，这个过程不够敏捷。

所以，数据库的问题最好还是在数据库内部解决，那么，在数据库里面该如何解决呢？

既然优化器放弃了使用索引a，说明a还不够合适，所以第二种方法就是，我们可以考虑修改语句，引导MySQL使用我们期望的索引。比如，在第二个查询中，显然把order by b limit 1改成order by b, a limit 1，语义的逻辑是相同的：

  1. select * from t where (a between 1 and 1000) and (b between 5000 and 100000) order by b, a limit 1;

执行效果如下：
image.png
之前优化器选择使用索引b，是因为它觉得使用索引b可以避免排序（b本身已经是索引，通过order by b已经是有序的了，如果选择索引b的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。
现在order by b, a这种写法，要求按照b, a排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描1000行的索引a。

当然，这种修改并不是通用的优化手段，只是刚好在这个语句里有limit 1，因此如果有满足条件的记录，order by b limit 1和order by b, a limit 1都会返回b是最小的那一行，逻辑上一直，才可以这么做。

如果你觉得修改语义这件事儿不好，这里还有一种改法，

  1. select * from t where (a between 1 and 1000) and (b between 5000 and 100000) order by b, a limit 100)alias limit 1;

执行效果如下：
image.png
在这个修改中，我们用limit 100让优化器意识到，使用b索引代价是很高的。其实就是根据数据特征诱导了一下优化器，也不具备通用性。

第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
不过，在这个例子中，我没有找到通过新增索引来改变优化器行为的方法。这种情况其实比较少，尤其是经过DBA索引优化后的库，再碰到这个bug，找到一个更合适的索引一般比较难。

小结
这一节学习了索引统计的更新机制，并提到了优化器存在选错索引的可能性。
对于由于索引统计信息不准确导致的问题，可以用analyze table来解决。
而对于其他优化器误判的情况，可以在应用端使用force index来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

小问题
前面我们在构造第一个例子的过程中，通过session A的配合，让session B删除数据后又重新插入了一遍数据，然后就发现explain结果中，rows字段从10001变成37000多。 而如果没有session A的配合，只是单独执行delete from t、call idata()、explain这三句话，会看到rows字段其实还是10000左右。你可以自己验证一下这个结果。 这是什么原因呢？
解析：
结果验证，rows还是10001啊。验证失败。