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