最近做一个mysql专题学习。在了解到mysql变量时myisam_stats_method引导出MyISAM索引统计集合。然后了解InnODB和MyISAM索引统计集合，以下是对官网的翻译以及自己附加些少理解。
官网地址：http://dev.mysql.com/doc/refman/5.6/en/myisam-index-statistics.html
储存引擎收集优化器使用的统计信息，表统计信息来自数值组，数值组是指具有相同关键字前缀的行记录。为了优化，一个重要的统计就是平均数值组。ps：下面会提到平均数值组的来源根据
MYSQL用在以下两种情况用到平均数值组：
1、估计每个索引值访问需要读多少行数据
2、估计一些联接产生多少行，如下面的联结：
JOIN tbl_name ON tbl_name.key = expr
随着平均数值组的增加，则每个查找平均行数也增加，那索引就更加没用。所以达到优化的目的，最好的是每个索引值（ref）指向少数行。当指向行数越多，读的次数也多，mysql更不会使用索引。
ps ：上面说的索引值指向函数，根据不同引擎存数的数据库而不同，这里最要讲的是MyISAM的，MyISAM的索引是保存每个索引值以及行数据的地址，而InnODB则是索引值和索引列数据包含在一起。
平均数值组和什么有关系？
平均数值组大小与表的势值（cardinality），即数值组的个数。用show index显示势值（N/S），N是表内记录数，S是平均数值组的大小。
—————————————————–
为了便于理解，以下是笔者的测试：
在表中：
mysql> describe t_limit; +---------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------+------+-----+---------+----------------+ | id | int(6) | NO | PRI | NULL | auto_increment | | content | char(125) | NO | | NULL | | +---------+-----------+------+-----+---------+----------------+
表t_limit中id是主键
show index from t_limit; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | t_limit | 0 | PRIMARY | 1 | id | A | 966 | NULL | NULL | | BTREE | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
可以看到势值是966，表数据有1008 按照公式C=N/S =>966=1008/S=>S=1.043 也就是说MYSQL估计在t_limit中每个索引值需要读1.04行表数据，这个值越小索引被mysql利用的几率就越大，也就是说Cardinality 的值越大越好（这个也是网上流传很多的说法，但很少有说缘由）。还有的是势值是个估算值，每次用Analyze后值会变动。
————————————————————–
对于基于<=>（<=、=>）比较操作符的联接，NULL被mysql视为和其他值相同：NULL <=> NULL，正如对于其它N，N <=> N。
但是对于基于=操作符的联接，NULL与非NULL值不同：当expr1或expr2(或两者)为NULL时，expr1 = expr2不为真。这样影响比较形式tbl_name.key = expr的ref访问：如果expr当前的值为null，MySQL不会访问表，因为比较不能为真。
对于=比较，表内有多少NULL值并不重要。为了优化目的，相关值为非NULL数值组的平均大小。然而，MySQL目前不允许搜集或使用该平均大小
ps：这是mysql针对所有存储引擎对where join 中有NULL表达式的处理方式，只要心中有这个概念，先不要纠结原因，下面有方法修改myisam_stats_method和 InnoDB_stats_method 系统变量让mysql怎么对待null值
对于myisam和innodb表，可以通过设置myisam_stats_method和 InnoDB_stats_method 系统变量告诉mysql怎么收集统计信息，也就是如何去处理NUll值，Mysql现在有三个值：nulls_equal、nulls_unequal、nulls_ignored
1、nulls_equal，则对于数据库里面所有Null都是相等的（mysql看待null是一样的）
当数据表中null值数量远远大于非null值数量，那么平均数值组增大（因为数据库对所有表里面的null值都认为是一个值，那么Non_unique的值很小，从而C=N/S 计算出s很大），这样使索引对于优化器来说比它实际为查找非NULL值的联接更加没有用。结果是，nulls_equal方法会使优化器进行索引值（ref）访问时本应使用索引而没有使用。
PS：对待null相等，这势值很小，C=N/S 则平均数组值S则很大，前面说了平均数组值越大，则用的索引的机会就越少。
2、nulls_unequal，null被认为是不一样（尽管我们眼中是一样），每一个null形成单独的数组值。
如果null数量很多，则该方法向下倾斜平均数数值组大小（就是偏小），这是我们想要的。
如果非null数量很多，null小，统计大小为1的每个组的NULL值会使优化器过高估计查找非NULL值的联接的索引值，按照笔者的理解就是这平均数数值组偏大，即时用其他方法（如遍历表）更好，mysql还是会用索引。但是这不是我们想要的。
所以用nulls_unequal是适合用null很多的情况下。PS：这段不完全按照官网英文翻译，加上笔者的些理解。
3、nulls_ignored，则null被忽略。
PS：myisam_stats_method默认是nulls_unequal
如果你要使用许多使用“<=>”操作符号而不是=的联接，在比较过程中null值并不特殊，一个null等于另一个null。在这种情况下，nulls_equal是合适的统计方法。但是若是=号的话则用nulls_unequal
myisam_stats_method系统变量有全局和会话值。设置全局值会影响MyISAM 为所有MyISAM表的统计的搜集。设置会话值只影响当前客户连接的统计的搜集。这说明你可以强制用给定的方法重新生成表的统计的搜集，而不需要因为设置myisam_stats_method的会话值而影响其它客户。
重新生成表的统计信息：
1、设置myisam_stats_method，然后执行CHECK TABLE语句
2、执行myisamchk –stats_method=method_name –analyze
3、更改表，使其统计信息不为最新，然后设置myisam_stats_method并执行ANALYZE TABLE语句
官网地址：http://dev.mysql.com/doc/refman/5.6/en/myisam-index-statistics.html
转载请注明：爱开源 » InnODB和MyISAM索引统计集合