Mysql深度讲解 - 查询优化器（二）

前言

上一篇【Mysql深度讲解 - 查询优化器（一）】主要讲了查询优化器怎么用，以及查询优化的成本计算，那么这些成本数据是怎么统计呢？本篇将会继续对Mysql中查询优化器的数据统计做一个解析。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

存储统计数据

InnoDB提供了两种方式存储统计数据：

1. 存储在磁盘上。
2. 存储在内存中，当服务器关闭时这些统计数据也被清掉。

当前统计数据的存储方式可以通过查看系统变量innodb_stats_persistent来查看存储方式。innodb_stats_persistent的值是ON就代表着统计数据被存储到磁盘中。反之为OFF统计数据被存储到内存中。5.6.6之前的版本innodb_stats_persistent默认为OFF，之后的版本中默认情况下都是ON的状态，也就是说5.6.6以后都是默认存在磁盘上。这个数据可以通过命令：SHOW VARIABLES like 'innodb_stats_persistent'查看。

由于InnoDB本身就是以表为单位收集并存储统计数据的，所以可以把某些表的统计数据(包括索引统计数据)存储在磁盘上，把另一些表的统计数据存储内存中，比如再创建或者修改表的时候指定stats_persistent=1(ON)或者stats_persistent=0(OFF)来指定该表统计数据的存储位置。

当我们把某个表所有的统计数据存储到磁盘上时，InnoDB其实是把这部分数据存储到了下面两张表里：

• innodb_table_stats：存储表的统计数据，每条记录对应一个表的统计数据。
• innodb_index_stats：存储索引的统计数据，每条记录对应一个索引的一个统计项的统计数据。

统计数据的更新

Mysql中是否自动重新计算统计数据是由系统变量innodb_stats_auto_recalc控制的，其默认是ON 也就是自动更新默认是开启的。Mysql中每个表都维护了一个变量，用于记录对该表进行的增删改操作的数量。如果修改记录数超过了该表总记录的10%，并且变量innodb_stats_auto_recalc=ON。服务器就会更新该表的统计数据，然后更新innodb_table_stats表和 innodb_index_stats表的相关数据。但是这个过程是异步执行的，也就是说即使表的数据改变超过了10%，也不会立即反映到两个表上，会有一定的延时。可以使用命令：SHOW VARIABLES like 'innodb_stats_auto_recalc'查看该变量的状态。

如果innodb_stats_auto_recalc的值为OFF，也同样可以调用Mysql中预留的命令去手动更新统计数据，更新的命令为：ANALYZE TABLE table_name。或者在table maintenance界面点击Analyze Table按钮。当觉得Mysql的成本计算器没有按照预想的使用目标索引的时候，可以尝试先更新统计数据，再进行计算成本。

索引统计数据

在利用索引去执行sql语句查询时，可能会有许多的单点区间，比如使用in语句是就会使得查询语句包含很多单点区间，例如：

Select * from table1 where name in ('a','b','c','d', ...);

假设这个table1表有一个针对name字段创建的索引idx_tb1_nm。由于这个索引不一定是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少。所以Mysql只能先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录。通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

如果只有少量单点区间，使用index dive的方式去统计相应的记录数并不会占用太多的资源。可是如果单点区间很多，比如有1000个，那么MySQL的查询优化器要进行1000次 index dive操作，才能计算出这些单点区间对应的索引记录条数。单点区间的数量越多，对应的index dive操作次数也就越多，对于数据库性能就是一个非常大的消耗。所以MySQL提供了一个系统变量eq_range_index_dive_limit， 可以使用命：SHOW VARIABLES LIKE 'eq_range_index_dive_limit'查看，默认值为200。

也就是说Mysql默认使用index dive操作的最大单点区间个数为200。如果大于200就会使用索引统计数据来进行估算，刚才已经说了innodb_index_stats表存的就是索引的统计数据，此时就用的上了，可以使用SHOW INDEX FROM table_name命令去查看，这里就以上篇中的city表为例：SHOW INDEX FROM city。

Table	Key_name	Column_name	Cardinality	Index_type
city	PRIMARY	ID	4117	BTREE
city	CountryCode	CountryCode	232	BTREE
city	idx_city_NP	Name	4001	BTREE
city	idx_city_NP	Population	4079	BTREE

结合show table status like 'city'一起分析，这个命令是显示当前表的统计数据。

Name	Engine	Row_format	Rows	Data_length	Index_length	……
city	InnoDB	Dynamic	4117	393216	81920	……

这里面最重要的就是Cardinality属性，表示索引列中不重复值的个数。可以看到city表中一共有4117行数据，而ID这个字段的Cardinality属性也是4117，这就意味着该列中没有任何的重复数据。如果Cardinality属性为1，意味着该列中只有一个值，也就是全部都是重复的数据。不过要注意的是Cardinality属性也是一个估算值，是用来估算查询成本用的，并不是精确值。可以根据这个属性来估算in语句中的参数所对应的记录数：

• 使用SHOW TABLE STATUS语句查询Rows值。
• 使用SHOW INDEX语句查询Cardinality值。
• 根据上面两个值可以算出某个索引对于某个列平均单个值的重复次数Rows/Cardinality
• 所以总回表的记录数就是：in语句中的参数个数*Rows/Cardinality

NULL值处理

上面已经说了，统计数据中的不重复列对于计算成本是一个很重要的指标。对于如何理解NULL值，会对查询优化器造成很大的影响，一般来说有三种理解方式：

1. NULL值代表一个未确定的值，每个NULL值都是独一无二的，在统计列不重复值的时候应该都当作独立的。
2. NULL值在业务上就是代表空值， NULL值所代表的意义是一样的，就是代表该cell不需要填充任何数据，在统计列不重复值的时候应该合并只计算为一个值。
3. NULL完全没有意义，在统计列不重复值的时候应该被忽略。

Mysql同样也提供了一个系统变量innodb_stats_method来表示处理NULL值得策略，查看该变量值可以使用show variables like 'innodb_stats_method'命令，一共有三种值：

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

因此索引列最好不要有NULL值。但是如果一定要索引中有NULL值存在，也是能够利用到索引的，但是也是要符合最左原则。比如：

表：t1(a,b,c,d)，index：t1(b,c,d)
Select * from t1 where b is null; -- 可以利用到索引，因为null值是最小的值，会被排列在最左，符合索引最左原则
Select * from t1 where b is not null; -- 不能利用到索引，因为后续的索引全部都不是null，不符合最左原则

总结

那么到此查询优化器的知识基本上结束了，希望通过笔者的帖子大家能够掌握查询优化器是如何使用的，以及各种表中各种属性是如何影响到查询优化器进行成本操作的，以及如何选用的查询方式。那么下一篇【Mysql深度讲解 – Join语句】将会主要对Join字段进行一个讲解。