MySQL中NULL值引起的小锅
我们都是小青蛙，呱呱呱呱呱！ 喜欢快乐的生活，最爱说笑话～
在阅读本文前最好越多过下列文章，否则文中出现的部分名词大家可能会有点儿陌生：
这一系列文章主要说明了一个道理： MySQL查询优化器决策是否使用某个索引执行查询时的依据是使用该索引的成本是否足够低，而成本很大程度上取决于需要扫描的二级索引记录数量占表中所有记录数量的比例 。
innodb_stats_method的作用
我们知道 索引列不重复的值的数量 这个统计数据对于 MySQL 查询优化器十分重要，因为通过它可以计算出在索引列中平均一个值重复多少行，它的应用场景主要有两个：
-
单表查询中单点区间太多，比方说这样：
SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');当
IN里的参数数量过多时，采用index dive的方式直接访问B+树索引去同步统计每个单点区间对应的记录的数量就太耗费性能了，所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。 -
连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用
ref访问方法来对被驱动表进行查询，比方说这样：SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...;
在真正执行对
t2表的查询前，t1.comumn的值是不确定的，所以我们也不能通过index dive的方式直接访问B+树索引去同步统计每个单点区间对应的记录的数量，所以也只能依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。
在统计索引列不重复的值的数量时，有一个比较烦的问题就是索引列中出现 NULL 值怎么办，比方说某个索引列的内容是这样：
+------+ | col | +------+ | 1 | | 2 | | NULL | | NULL | +------+
此时计算这个 col 列中不重复的值的数量就有下边的分歧：
-
有的人认为
NULL值代表一个未确定的值，所以设计MySQL的大叔才认为任何和NULL值做比较的表达式的值都为NULL，就是这样：mysql> SELECT 1 = NULL; +----------+ | 1 = NULL | +----------+ | NULL | +----------+ 1 row in set (0.00 sec) mysql> SELECT 1 != NULL; +-----------+ | 1 != NULL | +-----------+ | NULL | +-----------+ 1 row in set (0.00 sec) mysql> SELECT NULL = NULL; +-------------+ | NULL = NULL | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> SELECT NULL != NULL; +--------------+ | NULL != NULL | +--------------+ | NULL | +--------------+ 1 row in set (0.00 sec)
所以每一个
NULL值都是独一无二的，也就是说统计索引列不重复的值的数量时，应该把NULL值当作一个独立的值，所以col列的不重复的值的数量就是：4（分别是1、2、NULL、NULL这四个值）。 -
有的人认为其实
NULL值在业务上就是代表没有，所有的NULL值代表的意义是一样的，所以col列不重复的值的数量就是：3（分别是1、2、NULL这三个值）。 -
有的人认为这
NULL完全没有意义嘛，所以在统计索引列不重复的值的数量时压根儿不能把它们算进来，所以col列不重复的值的数量就是：2（分别是1、2这两个值）。
设计 MySQL 的大叔蛮贴心的，他们提供了一个名为 innodb_stats_method 的系统变量，相当于在计算某个索引列不重复值的数量时如何对待 NULL 值这个锅甩给了用户，这个系统变量有三个候选值：
-
nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。如果某个索引列中
NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。 -
nulls_unequal：认为所有NULL值都是不相等的。如果某个索引列中
NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。 -
nulls_ignored：直接把NULL值忽略掉。
反正这个锅是甩给用户了，当你选定了 innodb_stats_method 值之后，优化器即使选择了不是最优的执行计划，那也跟设计 MySQL 的大叔们没关系了哈～ 当然对于用户的我们来说， 最好不在索引列中存放NULL值才是正解 。
两种不同的统计数据存储方式
InnoDB 提供了两种存储统计数据的方式：
-
永久性的统计数据
这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
-
非永久性的统计数据
这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。
设计 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 的值作为该属性的值。
问题
有同学在小册群中反应在使用基于磁盘的统计数据时，将 innodb_stats_method 系统变量设置成不同的值，但是发现对应的统计数据却并未发生预想的变化（可以通过 SHOW INDEX FROM tbl_name 或者查看 mysql 数据库下的 innodb_index_stats 表），这到底是因为啥呢？
原因
我一开始也对这个现象有点儿疑惑 ，于是不得不再次打开看吐了的源码来看。
-
比较两条记录是否相同的函数是
cmp_rec_rec_with_match，如下图所示：
其中的
nulls_unequal参数是用来区别是否将两个null值认为是相等的。 -
在计算基于磁盘的统计数据时，是这样调用该函数的：
可以看到
nulls_unequal参数是硬编码为FALSE。 -
在计算基于内存的统计数据时，是这样调用该函数的：
可以看到这种调用的方式就是正常的。
从实践来看，在计算基于内存的统计数据时，改变系统变量 innodb_stats_method 的值是起作用的，但是在计算基于磁盘的统计数据时，改变该系统变量的值是无效的。我也并不知道设计 InnoDB 的大叔为什么这么写，翻了翻代码也没看见这么写有什么特别的注释，之后还特意去看了MySQL文档中关于统计数据收集的相关章节，也没发现有特别声明这两者的区别。可能是一个bug?或者有啥深层次的含义？有知道的同学可以留言哈～
小青蛙历史文章（历史文章，不容错过）：
MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！
长按关注小青蛙，都是干货喔
原文链接为《MySQL是怎样运行的： 从根儿上理解MySQL》小册链接
About The Author
