MySQL中NULL值引起的小锅

Python实战社群

Java实战社群

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

扫码关注添加客服

进Python社群▲

扫码关注添加客服

进Java社群▲

innodb_stats_method的作用

• 单表查询中单点区间太多，比方说这样：

  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+
  树索引去同步统计每个单点区间对应的记录的数量，所以也只能依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

+------+
| col  |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
+------+

• 有的人认为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这两个值）。

• nulls_equal
  ：认为所有NULL
  值都是相等的。这个值也是innodb_stats_method
  的默认值。
  如果某个索引列中NULL
  值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
• nulls_unequal
  ：认为所有NULL
  值都是不相等的。
  如果某个索引列中NULL
  值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
• nulls_ignored
  ：直接把NULL
  值忽略掉。

两种不同的统计数据存储方式

• 永久性的统计数据
  这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
• 非永久性的统计数据
  这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

问题

原因

1. 比较两条记录是否相同的函数是cmp_rec_rec_with_match
   ，如下图所示：
   
   其中的nulls_unequal
   参数是用来区别是否将两个null
   值认为是相等的。
2. 在计算基于磁盘的统计数据时，是这样调用该函数的：
   
   可以看到nulls_unequal
   参数是硬编码为FALSE
   。
3. 在计算基于内存的统计数据时，是这样调用该函数的：
   
   可以看到这种调用的方式就是正常的。

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

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



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