存储引擎收集有关表的统计信息,供优化器使用。表统计基于值组,其中值组是一组具有相同键前缀值的行。出于优化器的目的,一个重要的统计数据是平均值组大小。
MySQL使用平均值组大小的方式如下:
估计每次ref访问必须读取多少行
估计部分连接产生多少行;也就是说,这种形式的操作产生的行数:
随着索引的平均值组大小的增加,该索引对这两个目的的用处较小,因为每次查找的平均行数增加:为了使索引有利于优化,最好每个索引值都针对表中的少量行。当一个给定的索引值产生大量行时,该索引就不那么有用了,MySQL也不太可能使用它。
平均值组大小与表基数有关,表基数是值组的数量。SHOW INDEX语句显示基于N/S的基数值,其中N是表中的行数,S是平均值组大小。该比率得出表中价值组的大致数量。
对于基于<=>比较运算符的连接,NULL的处理方式与任何其他值没有区别:NULL<=>NULL,就像N<=>N对任何其他N一样。
但是,对于基于=运算符的连接,NULL与非NULL值不同:当expr1或expr2(或两者)为NULL时,expr1=expr2不为真。这会影响tbl_name.key=expr形式的比较的ref访问:如果expr的当前值为NULL,MySQL不会访问该表,因为比较不能为真。
For=比较,表中有多少NULL值并不重要。出于优化目的,相关值是非NULL值组的平均大小。但是,MySQL目前无法收集或使用该平均大小。
对于InnoDB和MyISAM表,您可以分别通过InnoDB_stats_method和MyISAM_status系统变量来控制表统计信息的收集。这些变量有三个可能的值,其差异如下:
当变量设置为nulls_equal时,所有NULL值都被视为相同的(也就是说,它们都形成一个值组)。
如果NULL值组的大小远高于平均非NULL值组大小,则此方法会使平均值组大小向上倾斜。这使得在优化器看来,索引对于查找非NULL值的连接来说,并没有真正有用。因此,nulls_equal方法可能会导致优化器在应该使用索引进行ref访问时不使用索引。
当变量设置为nulls_incequal时,NULL值不被视为相同。相反,每个NULL值形成一个大小为1的单独值组。
如果有许多NULL值,此方法会使平均值组大小向下倾斜。如果平均非NULL值组大小较大,将每个NULL值计数为大小为1的组会导致优化器高估查找非NULL值的联接的索引值。因此,当其他方法可能更好时,nulls_incequal方法可能会导致优化器使用此索引进行引用查找。
当变量设置为nulls_ignored时,NULL值将被忽略。
如果你倾向于使用许多使用<=>而不是=的连接,那么NULL值在比较中并不特殊,一个NULL值等于另一个NULL。在这种情况下,nulls_equal是适当的统计方法。
innodb_stats_method系统变量具有全局值;myisam stats method系统变量具有全局值和会话值。
设置全局值会影响从相应存储引擎中收集表的统计信息。设置会话值仅影响当前客户端连接的统计信息收集。
这意味着您可以通过设置myisam_sets_method的会话值,强制使用给定的方法重新生成表的统计信息,而不会影响其他客户端。
要重新生成MyISAM表统计信息,可以使用以下任何方法:
更改表以使其统计信息过期(例如,插入一行然后删除它),然后设置myisam_status方法并发出ANALYZE table语句
关于使用innodb_stats_method和myisam_states_method的一些注意事项:
您可以强制显式收集表统计信息,如上所述。然而,MySQL也可能自动收集统计数据。
例如,如果在执行表的语句的过程中,其中一些语句修改了表,MySQL可能会收集统计数据。(例如,这可能发生在批量插入或删除,或某些ALTER TABLE语句中。)
如果发生这种情况,则使用innodb_stats_method或myisam_status _method当时的任何值收集统计数据。
因此,如果您使用一种方法收集统计信息,但在稍后自动收集表的统计信息时将系统变量设置为另一种方法,则使用另一种方式。
无法判断使用哪种方法为给定的表生成统计数据。
这些变量仅适用于InnoDB和MyISAM表。其他存储引擎只有一种收集表统计信息的方法。通常它更接近于nulls_equal方法。