第7章:优化 / 7.4. 优化数据库结构 / 7.4.7. MyISAM索引统计集合

存储引擎搜集优化器使用的表的统计信息。表统计基于数数值组,其中数数值组是一系列有相同的关键字前缀值的记录。对于优化器,重要的统计即为数数值组的平均大小。

MySQL用下述方式使用平均数数值组:

·         估计必须为每个ref访问读取多少行

·         估计部分联接将产生多少行;也就是说,下述形式的操作将产生的行数:

·                  (...) JOIN tbl_name ON tbl_name.key = expr

随着索引的平均数数值组大小的增加,索引将更没有用,因为每个查找的平均行数增加:为了让索引有利于优化目的,最好是每个索引值对应表内的少量行数。当某个给定的索引值产生较多行时,索引更加没有用,MySQL更不可能使用它。

平均数数值组大小与表的集的势相关,即数数值组的数目。SHOW INDEX语句显示集的势值(基于N/S),其中N是表内的记录数,S是平均数数值组大小。该比例产生表内数数值组的大约数。

对于基于<=>比较 操作符的联接,NULL并不视为与任何其它值不同:NULL <=> NULL,正如对于其它N N <=> N

然而,对于基于=操作符的联接,NULL与非NULL值不同:当expr1expr2(或两者)NULL时,expr1 = expr2不为真。这样影响比较形式tbl_name.key = exprref访问:如果expr当前的值为NULLMySQL不会访问表,因为比较不能为真。

对于=比较,表内有多少NULL值并不重要。为了优化目的,相关值为非NULL数值组的平均大小。然而,MySQL目前不允许搜集或使用该平均大小。

对于MyISAM表,你可以使用myisam_stats_method系统变量部分控制表统计信息的搜集。该变量有两个可能的不同值,如下所示:

·         myisam_stats_methodnulls_equal时,所有NULL值被视为相等的(也就是说,它们都形成一个数值组)

如果NULL数值组大小远大于平均非NULL数值组大小,该方法向上倾斜平均数数值组大小。这样使索引对于优化器来说比它实际为查找非NULL值的联接更加没有用。结果是,nulls_equal方法会使优化器进行ref访问时本应使用索引而没有使用。

·         myisam_stats_methodnulls_unequal时,NULL值不视为相同。相反,每个NULL值形成一个单独的数值组,大小为1

如果你有许多NULL值,该方法向下倾斜平均数数值组大小。如果平均非NULL数值组较大,统计大小为1的每个组的NULL值会使优化器过高估计查找非NULL值的联接的索引值。结果是,当其它方法会更好时,nulls_unequal方法会使优化器为ref查找使用该索引。

如果你要使用许多使用<=>而不是=的联接,在比较过程中NULL值并不特殊,一个NULL等于另一个NULL。在这种情况下,nulls_equal是合适的统计方法。

myisam_stats_method系统变量有全局和会话值。设置全局值会影响MyISAM 为所有MyISAM表的统计的搜集。设置会话值只影响当前客户连接的统计的搜集。这说明你可以强制用给定的方法重新生成表的统计的搜集,而不需要因为设置myisam_stats_method的会话值而影响其它客户。

可以使用下面任一方法来重新生成表的统计信息:

·         设置myisam_stats_method,然后执行CHECK TABLE语句

·         执行myisamchk --stats_method=method_name --analyze

·         更改表,使其统计信息不为最新(例如,插入一行然后删除它),然后设置myisam_stats_method并执行ANALYZE TABLE语句

使用myisam_stats_method的一些警告:

你可以强制显式搜集表的统计信息,如上所述。然而,MySQL也可以自动搜集统计信息。例如,如果在为表执行语句的过程中,一些语句修改了表,MySQL可以搜集统计信息。(例如,大批插入或删除,或者执行ALTER TABLE语句时可能发生)如果发生,使用myisam_stats_method此时所有的值搜集统计信息。这样,如果你使用一个方法搜集统计信息,但当后面自动搜集一个表的统计信息时myisam_stats_method被设置为另一个方法,将使用其它方法。

对于给定的MyISAM表,还不能说出使用哪个方法来产生统计信息。

myisam_stats_method只适合MyISAM表。其它存储引擎只有一个方法来搜集表的统计信息。通常它接近于nulls_equal方法。