InnODB和MyISAM索引统计集合 -- MySQL -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> MySQL --> InnODB和MyISAM索引统计集合

InnODB和MyISAM索引统计集合

浏览:5200次出处信息

最近做一个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值的联接更加没有用。结果是，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

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：MySQL vs MariaDB vs Percona 之TPCC性能测试

后一篇：为什么字段尽可能用NOT NULL，而不是NULL >>

文章信息

作者：Benwin 来源： Benwin 's blog
标签： myisam_stats_met 索引索引统计集合
发布时间：2012-10-28 23:25:28

建议继续学习

近3天十大热文