MySQL实战45讲

• MySQL实战45讲
  • 第八节: 事务到底是隔离的还是不隔离的？
    • 在MySQL里，有两个“视图”的概念：
    • “快照”在MVCC里是怎么工作的？
    • 我们继续看一下图1中的三个事务，分析下事务A的语句返回的结果，为什么是k=1。
    • 更新逻辑
    • 事务的可重复读的能力是怎么实现的？
    • 小结
  • 第九节: 普通索引和唯一索引，应该怎么选择？
    • 查询过程
    • 更新过程
    • change buffer的使用场景
    • 索引选择和实践
    • change buffer 和 redo log
  • 第十节: MySQL为什么有时候会选错索引？
    • 优化器的逻辑
    • 索引选择异常和处理
  • 第十二节：为什么我的MySQL会“抖”一下？
  • 第十三节:为什么表数据删掉一半，表文件大小不变？
    • 参数innodb_file_per_table
    • 数据删除流程
    • 重建表
    • Online 和 inplace
  • 第十四节:count(*)这么慢，我该怎么办？
    • count(*)的实现方式
    • 不同的count用法（基于InnoDB引擎）
  • 第十五节: 答疑文章（一）：日志和索引相关问题
    • 日志相关问题

第八节: 事务到底是隔离的还是不隔离的？

示例：

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

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

整个专栏里面，都是默认 autocommit=1。

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

事务B查到的k的值是3，而事务A查到的k的值是1,

1. 在MySQL里，有两个“视图”的概念：

   • 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。 创建视图的语法是create view…，而它的查询方法与表一样。
   • 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持 RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现

   它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

2. “快照”在MVCC里是怎么工作的？

   在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

   • 这个快照是怎么实现的

     InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向 InnoDB的事务系统申请的，是按申请顺序严格递增的。

     而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且 把transaction id赋值给这个数据版本的事务ID，记为rowtrx_id。同时，旧的数据版本要保留， 并且在新的数据版本中，能够有信息可以直接拿到它。

     也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的rowtrx_id。

     如图2所示，就是一个记录被多个事务连续更新后的状态。

     

     图中虚线框里是同一行数据的4个版本，当前最新版本是V4，k的值是22，它是被transaction id 为25的事务更新的，因此它的rowtrx_id也是25。

   • 语句更新会生成undo log（回滚日志）,那么，undo log 在哪呢？

     图2中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而 是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依 次执行U3、U2算出来。

   • InnoDB是怎么定义快照 的?

     按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这 个事务执行期间，其他事务的更新对它不可见。

     因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我 启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

     当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数 据，它自己还是要认的。

     在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活 跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

     数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水 位。

     这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

     而数据版本的可见性规则，就是基于数据的rowtrx_id和这个一致性视图的对比结果得到的。 这个视图数组把所有的rowtrx_id 分成了几种不同的情况。

     

     这样，对于当前事务的启动瞬间来说，一个数据版本的rowtrx_id，有以下几种可能：

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

     比如，对于图2中的数据来说，如果有一个事务，它的低水位是18，那么当它访问这一行数据 时，就会从V4通过U3计算出V3，所以在它看来，这一行的值是11。

     有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？ 因为之后的更新，生成的版本一定属于上面的2或者3(a)的情况，而对它来说，这些新的数据版 本是不存在的，所以这个事务的快照，就是“静态”的了。

   InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建 快照”的能力。

3. 我们继续看一下图1中的三个事务，分析下事务A的语句返回的结果，为什么是k=1。

   这里，我们不妨做如下假设：

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

   这样，事务A的视图数组就是[99,100], 事务B的视图数组是[99,100,101], 事务C的视图数组是 [99,100,101,102]。

   为了简化分析，我先把其他干扰语句去掉，只画出跟事务A查询逻辑有关的操作：

   

   从图中可以看到，第一个有效更新是事务C，把数据从(1,1)改成了(1,2)。这时候，这个数据的最 新版本的rowtrx_id是102，而90这个版本已经成为了历史版本。

   第二个有效更新是事务B，把数据从(1,2)改成了(1,3)。这时候，这个数据的最新版本（即row trx_id）是101，而102又成为了历史版本。

   在事务A查询的时候，其实事务B还没有提交，但是它生成的(1,3)这个版本已 经变成当前版本了。但这个版本对事务A必须是不可见的，否则就变成脏读了。

   好，现在事务A要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起 的。所以，事务A查询语句的读数据流程是这样的：

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

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

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

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

   现在，我们用这个规则来判断图4中的查询结果，事务A的查询语句的视图数组是在事务A启动的 时候生成的，这时候：

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

4. 更新逻辑

   事务B的update语句，如果按照一致性读，好像结果不对哦？

   图5中，事务B的视图数组是先生成的，之后事务C才提交，不是应该看不见(1,2)吗，怎么能 算出(1,3)来？

   

   是的，如果事务B在更新之前查询一次数据，这个查询返回的k的值确实是1。

   但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。 因此，事务B此时的set k=k+1是在（1,2）的基础上进行的操作。

   所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的 值，称为“当前读”（current read）。

   因此，在更新的时候，当前读拿到的数据是(1,2)，更新后生成了新版本的数据(1,3)，这个新版本 的rowtrx_id是101。

   所以，在执行事务B查询语句的时候，一看自己的版本号是101，最新数据的版本号也是101，是 自己的更新，可以直接使用，所以查询得到的k的值是3。

   这里我们提到了一个概念，叫作当前读。其实，除了update语句外，select语句如果加锁，也是 当前读。

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

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

   假设事务C不是马上提交的，而是变成了下面的事务C’，会怎么样呢？

   

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

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

   

   到这里，我们把一致性读、当前读和行锁就串起来了。

5. 事务的可重复读的能力是怎么实现的？

   可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如 果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

   而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

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

   那么，我们再看一下，在读提交隔离级别下，事务A和事务B的查询语句查到的k，分别应该是多 少呢？

   “start transaction with consistent snapshot; ”的意思是从这个语句开始，创 建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于 普通的start transaction

   下面是读提交时的状态图，可以看到这两个查询语句的创建视图数组的时机发生了变化，就是图 中的read view框。（注意：这里，我们用的还是事务C的逻辑直接提交，而不是事务C’）

   !

   这时，事务A的查询语句的视图数组是在执行这个语句的时候创建的，时序上(1,2)、(1,3)的生成 时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

   • (1,3)还没提交，属于情况1，不可见；
   • (1,2)提交了，属于情况3，可见。

   所以，这时候事务A查询语句返回的是k=2。 显然地，事务B查询结果k=3。

6. 小结

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

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

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

   为什么表结构不支持“可重复读”？这是因为表结构没有对应的行数据，也没有 rowtrx_id，因此只能遵循当前读的逻辑。

   MySQL 8.0已经可以把表结构放在InnoDB字典里了，也许以后会支持表结构的可重复 读。

第九节: 普通索引和唯一索引，应该怎么选择？

​ 从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么 呢？

​ 还是用第四节： 深入浅出索引（上）中的例子来说明，假设字段 k 上的值 都不重复。

接下来，我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。

1. 查询过程

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

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

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

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

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

   当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下 一个数据页，这个操作会稍微复杂一些。 但是，我们之前计算过，对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概 率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以 忽略不计。

2. 更新过程

   change buffer

   当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中 的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在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的处理流程是怎样的?

   第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：

   • 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
   • 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

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

   第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB的处理流程如下：

   • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结 束；
   • 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

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

3. change buffer的使用场景

   用change buffer对更新过程的加速作用，change buffer只限于用在普通索引的场景下，而不适用于唯一索引。

   普通 索引的所有场景，使用change buffer都可以起到加速作用吗？

   因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动 作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面 上要更新的次数越多），收益就越大。

   因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

   反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记 录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问 IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来 说，change buffer反而起到了副作用。

4. 索引选择和实践

   普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上 是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

   如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在 其他情况下，change buffer都能提升更新性能。

   特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的。所以，当你有一个 类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索 引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入 速度。

5. change buffer 和 redo log

   redo log见第二节：日志系统：一条SQL更新语句是如何执行的？

   现在，我们要在表上执行这个插入语句：

   insert into t(id,k) values(id1,k1),(id2,k2);
   

   们假设当前k索引树的状态，查找到位置后，k1所在的数据页在内存(InnoDB buffer pool)中，k2所在的数据页不在内存中。如图2所示是带change buffer的更新状态图。

   

   分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间 （t.ibd）、系统表空间（ibdata1）。

   这条更新语句做了如下的操作（按照图中的数字顺序）：

   1. Page 1在内存中，直接更新内存；
   2. Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个 信息;
   3. 将上述两个动作记入redo log中（图中3和4）。

   做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了 两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

   同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

   那在这之后的读请求，要怎么处理呢？

   比如，我们现在要执行 select *fromt where k in (k1, k2)。这里，画了这两个读请求的流程 图。

   如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表 空间（ibdata1）和 redo log（ib_log_fileX）无关了。所以，在图中就没画出这两部分。

   

   从图中可以看到：

   1. 读Page 1的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL之后如果 读数据，是不是一定要读盘，是不是一定要从redo log里面把数据更新以后才可以返回？其 实是不用的。你可以看一下图3的这个状态，虽然磁盘上还是之前的数据，但是这里直接从 内存返回结果，结果是正确的。
   2. 要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作 日志，生成一个正确的版本并返回结果。

   可以看到，直到需要读Page 2的时候，这个数据页才会被读入内存。

   redo log 主要节省的是随 机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消 耗。

第十节: MySQL为什么有时候会选错索引？

我本地操作和课程中的结果不一致，本节内容就沿用课程中的吧

示例：

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

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;

使用存储过程往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

分析一条SQL语句：

select * from t where a between 10000 and 20000;

这条查询语句的执行也确实符合预期，key这个字段值是’a’，表示优化器选择了索 引a。

---

再执行下面的操作

这时候，session B的查询语句select *fromt where a between 10000 and 20000就不会再选择 索引a了。我们可以通过慢查询日志（slowlog）来查看一下具体的执行情况。

下面的三条SQL语句，就是这个实验过程。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*//*使用force index(a)来让优化器强制使用索引a*/

• 第一句，是将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日 志中；
• 第二句，Q1是session B原来的查询；
• 第三句，Q2是加了force index(a)来和session B原来的查询语句执行情况对比。

如图3所示是这三条SQL语句执行完成后的慢查询日志。

我本地是版本是8.0.25，慢查询日志在mysql.slow_log这个表中

可以看到，Q1扫描了10万行，显然是走了全表扫描，执行时间是14毫秒。Q2扫描了10001行， 执行了10毫秒。也就是说，我们在没有使用force index的时候，MySQL用错了索引，导致了更长的执行时间。

1. 优化器的逻辑

   优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库 里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越 少，消耗的CPU资源越少。

   扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行 综合判断。

   我们这个简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描 行数的时候出问题了。

   • 扫描行数是怎么判断的？

     MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根 据统计信息来估算记录数。

     这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越 好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越 大，索引的区分度越好。

     我们可以使用show index方法，看到一个索引的基数。如图4所示，就是表t的show index的结果 。虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不 同，而且其实都不准确。

     

   • MySQL是怎样得到索引的基数的呢？

     MySQL采样统计的方 法:

     • 为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太 高了，所以只能选择“采样统计”。
     • 采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均 值，然后乘以这个索引的页面数，就得到了这个索引的基数。
     • 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的 时候，会自动触发重新做一次索引统计。

     在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选 择：

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

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

     从图4中看到，这次的索引统计值（cardinality列）虽然不够精确，但大体上还是差不多 的，选错索引一定还有别的原因。

     其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要 扫描多少行。

     接下来，我们再一起看看优化器预估的，这两个语句的扫描行数是多少。

     

     rows这个字段表示的是预计扫描行数。

     其中，Q1的结果还是符合预期的，rows的值是104620；但是Q2的rows值是37116，偏差就大 了。而图1中我们用explain命令看到的rows是只有10001行，是这个偏差误导了优化器的判断。

     这是因为，如果使用索引a，每次从索引a上拿到一个值，都要回到主键索引上查出整行数据， 这个代价优化器也要算进去的。

     而如果选择扫描10万行，是直接在主键索引上扫描的，没有额外的代价。

     优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执 行时间看来，这个选择并不是最优的。

     使用普通索引需要把回表的代价算进去，在图1执行explain的时候，也考虑了这个策略的代价 ， 但图1的选择是对的。也就是说，这个策略并没有问题。

     所以MySQL选错索引，这件事儿还得归咎到没能准确地判断出扫描行数。

   analyze table t 命令，可以用来重新统计索引信息。

   发现explain的结果预估的rows值跟实际情况差距比较大，可以采用analyze table 表名这个 方法来处理。

   其实，如果只是索引统计不准确，通过analyze命令可以解决很多问题，但是前面我们说了，优 化器可不止是看扫描行数。

   依然是基于这个表t，我们看看另外一个语句：

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

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

   我们先来看一下a、b这两个索引的结构图。

   

   如果使用索引a进行查询，那么就是扫描索引a的前1000个值，然后取到对应的id，再到主键索 引上去查出每一行，然后根据字段b来过滤。显然这样需要扫描1000行。

   如果使用索引b进行查询，那么就是扫描索引b的最后50001个值，与上面的执行过程相同，也是 需要回到主键索引上取值再判断，所以需要扫描50001行。

   所以你一定会想，如果使用索引a的话，执行速度明显会快很多。那么，下面我们就来看看到底 是不是这么一回事儿。

   图8是执行explain的结果。

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

   

   可以看到，返回结果中key字段显示，这次优化器选择了索引b，而rows字段显示需要扫描的行 数是50198。

   从这个结果中，你可以得到两个结论：

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

2. 索引选择异常和处理

   一种方法是，像我们第一个例子一样，采用force index强行选择一个索引。

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

   第二种方法就是，我们可以考虑修改 语句，引导MySQL使用我们期望的索引。

   比如，在这个例子里，显然把“order by b limit 1” 改 成 “order by b,a limit 1” ，语义的逻辑是相同的。

   之前优化器选择使用索引b，是因为它认为使用索引b可以避免排序（b本身是索引，已经是有序 的了，如果选择索引b的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为 代价更小。

   现在order by b,a 这种写法，要求按照b,a排序，就意味着使用这两个索引都需要排序。因此，扫 描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描1000行的索引a。

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

   这里还有一种改法

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

在这个例子里，我们用limit 100让优化器意识到，使用b索引代价是很高的。其实是我们根据数 mysql> select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1; 据特征诱导了一下优化器，也不具备通用性。

**第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选 择，或删掉误用的索引。**

3. ##### 上期问题

+ 如果某次写入使用了change buffer机制，之后主机异常 重启，是否会丢失change buffer和数据？

  答案是不会丢失。虽然是只更新内存，但是在事务提 交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。

+ merge的过程是否会把数据直接写回磁盘？

  merge的执行流程是这样的：

  1. 从磁盘读入数据页到内存（老版本的数据页）； 
  2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新 版数据页； 
  3.  写redo log。这个redo log包含了数据的变更和change buffer的变更。

   到这里merge过程就结束了。这时候，数据页和内存中change buffer对应的磁盘位置都还没有修 改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

### 第十一节:怎么给字符串字段加索引？

​	MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

```sql
create table SUser(
ID bigint unsigned primary key,
email varchar(64),
)engine=innodb;

比如，这两个在email字段上创建索引的语句：

alter table SUser add index index1(email);
alter table SUser add index index2(email(6));

第一个语句创建的index1索引里面，包含了每个记录的整个字符串；而第二个语句创建的index2 索引里面，对于每个记录都是只取前6个字节。

!

从图中你可以看到，由于email(6)这个索引结构中每个邮箱字段都只取前6个字节（即： zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

但这同时带来的损失是，可能会增加额外的记录扫描次数。

接下来，我们再看看下面这个语句，在这两个索引定义下分别是怎么执行的。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是index1（即email整个字符串的索引结构），执行顺序是这样的：

1. 从index1索引树找到满足索引值是'zhangssxyz@xxx.com’的这条记录，取得ID2的值；
2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
2. 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢 弃；
3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取 整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变 多。

但是，对于这个查询语句来说，如果你定义的index2不是email(6)而是email(7），也就是说取 email字段的前7个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查 询成本。

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的 键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：

select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用 这个语句：

select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的L4~L7中，找出不小于 L * 95%的值，假设这里L6、L7都满足，你就可以 选择前缀长度为6。

前缀索引对覆盖索引的影响

前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止 如此，我们再看一下另外一个场景。

你先来看看这个SQL语句：

select id,email from SUser where email='zhangssxyz@xxx.com';

与前面例子中的SQL语句

select id,name,email from SUser where email='zhangssxyz@xxx.com';

相比，这个语句只要求返回id和email字段。

所以，如果使用index1（即email整个字符串的索引结构）的话，可以利用覆盖索引，从index1查 到结果后直接就返回了，不需要回到ID索引再去查一次。而如果使用index2（即email(6)索引结 构）的话，就不得不回到ID索引再去判断email字段的值。

即使你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息， 但InnoDB还是要回到id索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信 息。

也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀 索引时需要考虑的一个因素。

其他方式

​ 第一种方式是使用倒序存储。

select field_list from t where id_card = reverse('input_id_card_string');

​ 第二种方式是使用hash字段。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

​ 然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码 可能存在冲突，也就是说两个不同的身份证号通过crc32()函 数得到的结果可能是相同的，所以你 的查询语句where部分要判断id_card的值是否精确相同。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string';

​ 使用倒序存储和使用hash字段这两种方法的异同点。

​ 首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的 方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y]的所有市民了。同样 地，hash字段的方式也只能支持等值查询。

​ 它们的区别，主要体现在以下三个方面：

	1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字 段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如 果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。 
	2.  在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash 字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的 话，reverse函数额外消耗的CPU资源会更小些。 
	3.  从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽 然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储 方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

小结

字符串字段创建索引的场景,可以使用的 方式有：

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支 持范围扫描。

第十二节：为什么我的MySQL会“抖”一下？

1. 你的SQL语句为什么变“慢”了

   InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志 叫作redo log（重做日志），在更新内存写 完redo log后，就返回给客户端，本次更新成功。

   把内存里的数据写入磁盘的过程，术语就是 flush。

   当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写 入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

   平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

   什么情况会引发数据库的flush过程呢？

   • 第一种场景是，InnoDB的redo log写满了。这时候系统会停止所有更新操作，把 checkpoint往前推进，redo log留出空间可以继续写。

     

     checkpoint可不是随便往前修改一下位置就可以的。比如图2中，把checkpoint位置从CP推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都flush到磁盘上。之后，图 中从write pos到CP’之间就是可以再写入的redo log的区域。

   • 第二种场景是，系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰 一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据 页，然后拿redo log出来应用不就行了？这里其实是从性能考虑的。如果刷脏页一定会写盘， 就保证了每个数据页有两种状态：

     • 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
     • 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

   • 第三种场景是，MySQL认为系统“空闲”的时候。刷“脏页”。

   • 第四种场景是，MySQL正常关闭的情况。这时候，MySQL会把内存的脏页都flush到磁 盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

   上面四种场景对性能的影响：

   • 第三种情况是属于MySQL空闲时的操作，这时系统没什么压力，而第四种场景是数据库 本来就要关闭了。这两种情况下，你不会太关注“性能”问题。
   • 第一种是“redo log写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况 的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更 新数会跌为0。
   • 第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池 （buffer pool）管理内存，缓冲池中的内存页有三种状态：
     • 第一种是，还没有使用的；
     • 第二种是，使用了并且是干净页；
     • 第三种是，使用了并且是脏页。

   InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

   而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久 不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是 脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

   所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

   1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
   2. 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

   所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

   InnoDB刷脏页的控制策略

   首先，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时 候，可以刷多快。

   这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。这个值我建议你设 置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试。

   下面的语句是用来测试磁盘随机 读写的命令：

   fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
   

   虽然我们现在已经定义了“全力刷脏页”的行为，但平时总不能一直是全力刷吧？毕竟磁盘能力不 能只用来刷脏页，还需要服务用户请求。所以接下来，我们就一起看看InnoDB怎么控制引擎按 照“全力”的百分比来刷脏页。

   如果你来设计策略控制刷脏页的速度，会参考哪些因 素呢？

   如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是redo log写 满。

   InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。

   InnoDB会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论 是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源 并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因

   要尽量避免这种情况，你就要合理地设置innodb_io_capacity的值，并且平时要多关注脏页比 例，不要让它经常接近75%。

   其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到 的，具体的命令参考下面的代码：

   select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
   select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
   select @a/@b;
   

   再看一个有趣的策略。

   一旦一个查询请求需要在执行过程中先flush掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁 边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还 可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放 到一起刷。

   在InnoDB中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为1的时候会有上述 的“连坐”机制，值为0时表示不找邻居，自己刷自己的。

   找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO。机械硬盘的随机IOPS 一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。

   而如果使用的是SSD这类IOPS比较高的设备的话，建议你把innodb_flush_neighbors的值 设置成0。因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操 作，减少SQL语句响应时间。

   在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

第十三节:为什么表数据删掉一半，表文件大小不变？

​ 一个InnoDB表包含两部 分，即：表结构定义和数据。在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里。而 MySQL 8.0版本，则已经允许把表结 构定义放在系统数据表中了。因为表结构定义占用的空间很 小，所以我们今天主要讨论的是表数据。

1. 参数innodb_file_per_table

   表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table控制的：

   • 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一 起；
   • 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

   从MySQL 5.6.6版本开始，它的默认值就是ON了。

   建议不论使用MySQL的哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文 件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文 件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

   将innodb_file_per_table设置为ON，是推荐做法，我们接下来的讨论都是基于这个 设置展开的。

   在删除整个表的时候，可以使用drop table命令回收表空间。

2. 数据删除流程

   先再来看一下InnoDB中一个索引的示意图。在前面提到过，InnoDB里的数据都是用B+树的结构组织的。

   

   假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入 一个ID在300和600之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

   现在，你已经知道了InnoDB的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记 录，会怎么样？整个数据页就可以被复用了。

   数据页的复用跟记录的复用是不同的。

   记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4这条记录被删除后，如果 插入一个ID是400的行，可以直接复用这个空间。但如果插入的是一个ID是800的行，就不能复 用这个位置了。

   而当整个页从B+树里面摘掉以后，可以复用到任何位置。以图1为例，如果将数据页page A上的 所有记录删除以后，page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新 页的时候，page A是可以被复用的。

   如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一 个数据页就被标记为可复用。

   进一步地，如果我们用delete命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记 为可复用。但是磁盘上，文件不会变小。

   delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件 的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被 使用的空间，看起来就像是“空洞”。

   不止是删除数据会造成空洞，插入数据也会。

   如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造 成索引的数据页分裂。

   假设图1中page A已经满了，这时我要再插入一行数据，会怎样呢？

   

   可以看到，由于page A满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面 page B来保存数据了。页分裂完成后，page A的末尾就留下了空洞（注意：实际上，可能不止1 个记录的位置是空洞）。

   另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造 成空洞的。

   也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就 能达到收缩表空间的目的。

   而重建表，就可以达到这样的目的。

3. 重建表

   在重建表的时候，InnoDB不会把整 张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

   可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A 里读出来再插入到表B中。

   由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键 索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完 成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。

   使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前，这个命 令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要你自己创建，MySQL会自 动完成转存数据、交换表名、删除旧表的操作。

   

   显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到 表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新。也就是说，这个 DDL不是Online的。

   而在MySQL 5.6版本开始引入的Online DDL，对这个操作流程做了优化。

   简单描述一下引入了Online DDL之后，重建表的流程：

   1. 建立一个临时文件，扫描表A主键的所有数据页；
   2. 用数据页中表A的记录生成B+树，存储到临时文件中；
   3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（rowlog）中，对应的是图 中state2的状态；
   4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的 数据文件，对应的就是图中state3的状态；
   5. 用临时文件替换表A的数据文件。

​ 可以看到，与图3过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方 案在重建表的过程中，允许对表A做增删改操作。这也就是 Online DDL名字的来源。

​ 图4的流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据 之前就退化成读锁了。 为什么要退化呢？为了实现Online，MDL读 锁不会阻塞增删改操作。 那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。 而对于一个大表来说，Online DDL最耗时的过 程就是拷贝数据到临时表的过程，这个步骤的执 行期间可以接受增删改操作。所以，相对于整个DDL过程来说，锁的时间非常短。对业务来说， 就可以认 为是Online的。

​ 上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来 说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控 制操作时 间。如果想要比较安全的操作的话，我推荐你使用GitHub开源的gh-ost来做。

5. Online 和 inplace

   在图3中，我们把表A中的数据导出来的存放位置叫作tmp_table。这是一个临 时表，是在server层创建的。

   在图4中，根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出 来的。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是 一个“原地”操作，这就是“inplace”名称的来源。

   重建表的这个语句alter table t engine=InnoDB，其实隐含的意思是：

   alter table t engine=innodb,ALGORITHM=inplace;
   

   跟inplace对应的就是拷贝表的方式了，用法是：

   alter table t engine=innodb,ALGORITHM=copy;
   

   当你使用ALGORITHM=copy的时候，表示的是强制拷贝表，对应的流程就是图3的操作过程。

   inplace跟Online是不是就是一个意思？

   其实不是的，只是在重建表这个逻辑中刚好是这样而已。

   比如，如果我要给InnoDB表的一个字段加全文索引，写法是：

   alter table t add FULLTEXT(field_name)
   

   这个过程是inplace的，但会阻塞增删改操作，是非Online的。

   如果说这两个逻辑之间的关系是什么的话，可以概括为：

   1. DDL过程如果是Online的，就一定是inplace的；
   2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文 索引（FULLTEXTindex）和空间索引(SPATIAL index)就属于这种情况。

   ---

   optimize table、analyze table和alter table这三种方式重建表的区别

   • 从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）默认的就是上面图4 的流程了；
   • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程 中加了MDL读锁；
   • optimize table t 等于recreate+analyze。

第十四节:count(*)这么慢，我该怎么办？

1. count(*)的实现方式

   在不同的MySQL引擎中，count(*)有不同的实现方式。

   • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数， 效率很高；
   • 而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出 来，然后累积计数。

   我们在这篇文章里讨论的是没有过滤条件的count(*)，如果加了where 条件 的话，MyISAM表也是不能返回得这么快的。

   ---

   为什么InnoDB不跟MyISAM一样，也把数字存起来呢？

   这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。这里，我用一个算count(*)的例子来为你解释一下。

   假设表t中现在有10000条记录，我们设计了三个用户并行的会话。

   • 会话A先启动事务并查询一次表的总行数；
   • 会话B启动事务，插入一行后记录后，查询表的总行数；
   • 会话C先启动一个单独的语句，插入一行记录后，查询表的总行数。

   我们假设从上到下是按照时间顺序执行的，同一行语句是在同一时刻执行的。

   

   你会看到，在最后一个时刻，三个会话A、B、C会同时查询表t的总行数，但拿到的结果却不同

   这和InnoDB的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发 控制，也就是MVCC来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*)请求来说，InnoDB只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基 于这个查询”的表的总行数。

   ---

   当然，现在这个看上去笨笨的MySQL，在执行count(*)操作的时候还是做了优化的。

   InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是 主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得 到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正 确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

   如果你用过showtable status 命令的话，就会发现这个命令的输出结果里面也有一个 TABLE_ROWS用于显示这个表当前有多少行，这个命令执行挺快的，那这个TABLE_ROWS能 代替count(*)吗？

   实际上，TABLE_ROWS就是从这个采样估算得来的，因此它也很不准。 有多不准呢，官方文档说误差可能达到40%到50%。所以，show table status命令显示的行 数也不能直接使用。

   小结一下：

   • MyISAM表虽然count(*)很快，但是不支持事务；
   • show table status命令虽然返回很快，但是不准确；
   • InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

2. 不同的count用法（基于InnoDB引擎）

   首先你要弄清楚count()的语义。count()是一个聚合函数，对于返回的结果集，一行行地 判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

   所以，count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字 段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

   至于分析性能差别的时候，你可以记住这么几个原则：

   1. server层要什么就给什么；
   2. InnoDB只给必要的值；
   3. 现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

   对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server 层。server层拿到id后，判断是不可能为空的，就按行累加。

   对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个 数字“1”进去，判断是不可能为空的，按行累加。

   单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎 返回id会涉及到解析数据行，以及拷贝字段值的操作。

   对于count(字段)来说：

   1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
   2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再 判断一下，不是null才累加。

   但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不 是null，按行累加。

   看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照 count(*)来处理，多么简单的优化啊。

   当然，MySQL专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多 了，而且MySQL已经优化过count(*)了，你直接使用这种用法就可以了。

   所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以建议，尽量使用count(*)。

第十五节: 答疑文章（一）：日志和索引相关问题

1. 日志相关问题

   在第二节：日志系统：一条SQL更新语句是如何执行的？讲到binlog（归档日 志）和redo log（重做日志）配合崩溃恢复的时候，用的是反证法，说明了如果没有两阶段提 交，会导致MySQL出现主备数据不一致等问题。

   有同学问，在两阶段提交的不同瞬间，MySQL如果发生异常重启，是怎 么保证数据完整性的？

   再放一次两阶段提交的图，方便你学习下面的内容。

   

   这个例子里面，没有显式地开启事务，因此这个update语句自己就是一个事务，在执行 完成后提交事务时，就会用到这个“commit步骤“。

   在两阶段提交的不同时刻，MySQL异常重启会出现什么现象？

   如果在图中时刻A的地方，也就是写入redo log 处于prepare阶段之后、写binlog之前，发生了崩 溃（crash），由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会 回滚。这时候，binlog还没写，所以也不会传到备库。

   如果在图中在时刻B，也就是binlog写完，redo log还没commit前发生 crash，那崩溃恢复的时候MySQL会怎么处理？

   我们先来看一下崩溃恢复时的判断规则。

   1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；

   2. 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：

      a. 如果是，则提交事务；

      b. 否则，回滚事务。

   这里，时刻B发生crash对应的就是2(a)的情况，崩溃恢复过程中事务会被提交。

   ---

   追问1：MySQL怎么知道binlog是完整的?

   回答：一个事务的binlog是有完整格式的：

   • statement格式的binlog，最后会有COMMIT；
   • row格式的binlog，最后会有一个XID event。

   另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确 性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验 checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。

   ---

   追问2：redo log 和 binlog是怎么关联起来的?

   回答：它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

   • 如果碰到既有prepare、又有commit的redo log，就直接提交；
   • 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

   ---

   追问3：处于prepare阶段的redo log加上完整binlog，重启就能恢 复，MySQL为什么要这么设计?

   回答：其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻B，也就 是binlog写完以后MySQL发生崩溃，这时候binlog已经写入了，之后就会被从库（或者用这个 binlog恢复出来的库）使用。

   所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

   ---

   追问4：如果这样的话，为什么还要两阶段提交呢？干脆先redo log写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

   回答：其实，两阶段提交是经典的分布式系统问题，并不是MySQL独有的。

   如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。

   对于InnoDB引擎来说，如果redo log提交完成了，事务就不能回滚（如果这还允许回滚，就可能 覆盖掉别的事务的更新）。而如果redo log直接提交，然后binlog写入的时候失败，InnoDB又回 滚不了，数据和binlog日志又不一致了。

   追问5：不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩 溃恢复，又能支持归档，不就可以了？

   回答：这位同学的意思是，只保留binlog，然后可以把提交流程改成这样：…-> “数据更新到内 存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？意思是，只保留binlog，然后可以把提交流程改成这样：…-> “数据更新到内 存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？

   答案是不可以。

   如果说历史原因的话，那就是InnoDB并不是MySQL的原生存储引擎。MySQL的原生引擎是 MyISAM，设计之初就有没有支持崩溃恢复。

   InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支 持的引擎了。

   InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那就用InnoDB原有的redo log 好了。

   ---

   追问6：那能不能反过来，只用redo log，不要binlog？

   回答：如果只从崩溃恢复的角度来讲是可以的。你可以把binlog关掉，这样就没有两阶段提交 了，但系统依然是crash-safe的。

   但是，如果你了解一下业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog都是 开着的。因为binlog有着redo log无法替代的功能。

   一个是归档。redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保 留，redo log也就起不到归档的作用。

   一个就是MySQL系统依赖于binlog。binlog作为MySQL一开始就有的功能，被用在了很多地方。 其中，MySQL系统高可用的基础，就是binlog复制。

   还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费MySQL的binlog来更新 自己的数据。关掉binlog的话，这些下游系统就没法输入了。

   总之，由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redo log还做不到。

   ---

   追问7：redo log一般设置多大？

   回答：redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的 能力就发挥不出来了。

   如果磁盘够大，建议将redo log设置为4个文 件、每个文件1GB。

   追问8：正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的 还是从buffer pool更新过来的呢？

   回答：redo log里面到底是什么？

   实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也 就不存在“数据最终落盘，是由redo log更新过去”的情况。

   1. 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终 数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redo log毫无关系。
   2. 在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就 会将它读到内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到了 第一种情况的状态。

   追问9：redo log buffer是什么？是先修改内存，还是先写redo log文件？

   回答：在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

   begin;
   insert into t1 ...
   insert into t2 ...
   commit;
   

   这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在 还没commit的时候就直接写到redo log文件里。

   所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时 候，数据的内存被修改了，redo log buffer也写入了日志。

   但是，真正把日志写到redo log文件（文件名是 ib_logfile+数字），是在执行commit语句的时候 做的。

   这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。但是可能会出现“被 动写入磁盘”，比如内存不够、其他事务提交等情况。

   单独执行一个更新语句的时候，InnoDB会自己启动一个事务，在语句执行完成的时候提交。过 程跟上面是一样的，只不过是“压缩”到了一个语句里面完成。