曾经有个开发前辈告诉过小王：“代码是不会骗人的”。

代码是确定的，每一行的逻辑都是严格执行的，不会有例外。

这是程序猿赖以生存的基石。

每每想到这个说法，小王就觉得蛮安心的。

但在实际的工作中，代码真的给我们的感觉是不会骗人吗？我们真的安心吗？

在一次又一次debug的过程中，小王可不是有过一次两次的怀疑，而是千次万次的TMD这是怎么回事？！

代码确实是不会骗人的，只不过我自己的认知有局限。

没有从源头上理解问题，没有找到最底层的代码逻辑。

但又有几个人能看到最底层的代码逻辑？

我们踩在的是应用层的应用层的应用层的云彩，每一步好像都不那么稳固。

大地，云深不知处。

这次笔记整理自极客时间《MySQL实战45讲》第10讲。

MySQL优化器也会犯错、会抽风？如果不知道这背后的深层原因，必然又要像小王一样怀疑人生啊。

创建表：

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

我们定义 idata 这个存储过程：

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();

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

插入数据后，如果有语句：

select * from t where a between 10000 and 20000;

使用 explain 命令看到的这条语句的执行情况：

使用了索引a，到这里还没有任何问题，如果这样操作：

session A 开启一个事务，session B 把数据都删除，然后调用idata存储过程，又插入10万行数据。

现在我们可以做如下的验证：

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*/

第一句，是将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；

第二句，Q1 是 session B 原来的查询；

第三句，Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。【force index(a) 来让优化器强制使用索引 a】

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

神奇的事情发生了，session B 的查询语句 select * from t where a between 10000 and 20000 居然没有走索引a。

什么情况？

首先，选择索引是优化器的工作。

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

它的选择标准主要有，扫描行数，是否使用临时表，是否排序等因素来综合判断。

例子中的简单查询没有设计临时表和排序，那选错索引肯定是判断扫描行数出错了。

那么，优化器是如何判断扫描行数的呢？

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

这个统计信息就是索引的“区分度”。

一个索引上不同的值的个数，我们称之为“基数”，即基数越大，索引的区分度越好。

使用show index 方法，看到一个索引的基数（cardinality），虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。

所以MySQL是怎么得到这个基数的呢？

如果把整个表一行行统计，获得精确结果，这个代价太高了，所以只能选择“采样统计”的方法。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

另外，数据表是会持续更新的，索引统计信息也不会固定不变。当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

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

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

虽然不论是N=8还是N=20，结果都是不够精确的，但从上图看，索引统计值（cardinality）区别还是不大的。

那我们再看看上面Q1和Q2的explain：

Q1的结果rows=104620，因为没有用上索引a，10W行；

Q2的结果rows=37116，跟图一中的rows=10001，偏差有点大，正是这个偏差导致优化器判断错误。

但是稍等，即便是偏差到了37116行，也要比扫描10W行好的多吧，为什么会不用索引a呢？

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

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

优化器估计，主键索引的10W和普通索引的3W7，会是主键索引更快，才没用索引a。

当然，从图5的结果看，优化器的选择是错误的。还是走普通索引a要更快一些。

好的，那再回到另一个问题：为什么在开启session A事务、在session B事务上删掉数据又插入数据，一顿操作之后，rows会从图1的10001变成图5的37116呢？

这是因为，session A 开启了事务并没有提交，所以，之前插入的 10 万行数据是不能删除的（默认隔离级别是可重复读的）。

因此，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。

这样，索引 a 上的数据其实就有两份。

当然这么说来，主键索引也有两份，那么前面Q1的结果应该是20W，但根据图5看，结果就是10W。

这是因为，主键是直接按照表的行数来估计的。

而表的行数，优化器直接用的是 show table status 的值。

这就是优化器的心路历程，令人迷惑，但很有道理。

因此，没有什么是不能理解的，请一定相信：

代码确实是不会骗人的。

详细学习链接：