java Android linux命令 微软 Windows apache linux centos mysql wordpress Python shell google php Firefox HTML5 Ubuntu nginx 开源 程序员

InnODB和MyISAM索引统计集合

最近做一个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

延伸阅读

评论