MySQL中NULL值引起的小锅

Python实战社群

Java实战社群

长按识别下方二维码，按需求添加

扫码关注添加客服

进Python社群▲

扫码关注添加客服

进Java社群▲

作者丨小孩子4919 

来源丨我们都是小青蛙（xiaoqingwa4919）

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表），这到底是因为啥呢？

原因

我一开始也对这个现象有点儿疑惑????，于是不得不再次打开看吐了的源码来看。

1. 比较两条记录是否相同的函数是cmp_rec_rec_with_match，如下图所示：

   

   其中的nulls_unequal参数是用来区别是否将两个null值认为是相等的。

2. 在计算基于磁盘的统计数据时，是这样调用该函数的：

   

   可以看到nulls_unequal参数是硬编码为FALSE。

3. 在计算基于内存的统计数据时，是这样调用该函数的：

   

   可以看到这种调用的方式就是正常的。

从实践来看，在计算基于内存的统计数据时，改变系统变量innodb_stats_method的值是起作用的，但是在计算基于磁盘的统计数据时，改变该系统变量的值是无效的。我也并不知道设计InnoDB的大叔为什么这么写，翻了翻代码也没看见这么写有什么特别的注释，之后还特意去看了MySQL文档中关于统计数据收集的相关章节，也没发现有特别声明这两者的区别。可能是一个bug?或者有啥深层次的含义？有知道的同学可以留言哈～

程序员专栏 扫码关注填加客服 长按识别下方二维码进群


近期精彩内容推荐：   程序员违规操作损失800万，被判5年半！ “大多数人，都死在了 30 岁” 实用，5个案例让 Python 输出漂亮的表格 如何在分布式场景下生成全局唯一 ID ？

在看点这里好文分享给更多人↓↓