存储引擎收集有关表的统计信息以供优化器使用。表统计信息基于值组，值组是一组具有相同键前缀值的行。对于优化器而言，一个重要的统计信息是平均值组大小。Storage engines collect statistics about tables for use by the optimizer. Table statistics are based on value groups, where a value group is a set of rows with the same key prefix value. For optimizer purposes, an important statistic is the average value group size.

MySQL 在以下方面使用平均值组大小：MySQL uses the average value group size in the following ways:

随着索引的平均值组大小增加，该索引在这两种用途上的作用就会减弱，因为每次查找的平均行数增加：为了使索引在优化方面有效，最好每个索引值针对表中的较少行。当某个索引值产生大量行时，该索引的作用就会减弱，MySQL 使用它的可能性也会降低。As the average value group size for an index increases, the index is less useful for those two purposes because the average number of rows per lookup increases: For the index to be good for optimization purposes, it is best that each index value target a small number of rows in the table. When a given index value yields a large number of rows, the index is less useful and MySQL is less likely to use it.

平均值组的大小与表的基数有关，即值组的数量。SHOW INDEX 语句显示一个基数值，该值基于 N/S，其中 N 是表中的行数，S 是平均值组大小。该比率给出表中值组的大致数量。The average value group size is related to table cardinality, which is the number of value groups. The SHOW INDEX statement displays a cardinality value based on N/S, where N is the number of rows in the table and S is the average value group size. That ratio yields an approximate number of value groups in the table.

对于基于 <=> 比较运算符的连接，NULL 并不与其他值有所不同：NULL <=> NULL，就像对于任何其他 N 一样，N <=> N。For a join based on the <=> comparison operator, NULL is not treated differently from any other value: NULL <=> NULL, just as N <=> N for any other N.

然而，对于基于 = 运算符的连接，NULL 与非 NULL 值不同：当 expr1 或 expr2（或两者）为 NULL 时，expr1 = expr2 不是 TRUE。这会影响 ref 对形式为 tbl_name.key = expr 的比较的访问：如果 expr 当前值为 NULL，MySQL 不会访问表，因为比较不可能为 TRUE。However, for a join based on the = operator, NULL is different from non-NULL values: expr1 = expr2 is not true when expr1 or expr2 (or both) are NULL. This affects ref accesses for comparisons of the form tbl_name.key = expr: MySQL does not access the table if the current value of expr is NULL, because the comparison cannot be true.

对于 = 比较，表中有多少 NULL 值并不重要。对于优化目的，相关的值是非 NULL 值组的平均大小。然而，目前 MySQL 并未启用收集或使用该平均大小。For = comparisons, it does not matter how many NULL values are in the table. For optimization purposes, the relevant value is the average size of the non-NULL value groups. However, MySQL does not currently enable that average size to be collected or used.

对于 InnoDB 和 MyISAM 表，你可以通过 innodb_stats_method 和 myisam_stats_method 系统变量来对表统计信息的收集进行一定控制。这些变量有三种可能的取值，如下所示：For InnoDB and MyISAM tables, you have some control over collection of table statistics by means of the innodb_stats_method and myisam_stats_method system variables, respectively. These variables have three possible values, which differ as follows:

如果你倾向于使用许多使用 <=> 而不是 = 的连接，NULL 值在比较中不会特殊处理，一个 NULL 等于另一个。在这种情况下，nulls_equal 是合适的统计方法。If you tend to use many joins that use <=> rather than =, NULL values are not special in comparisons and one NULL is equal to another. In this case, nulls_equal is the appropriate statistics method.

innodb_stats_method 系统变量具有全局值；myisam_stats_method 系统变量既有全局值也有会话值。设置全局值会影响对应存储引擎的表统计信息收集。设置会话值只会影响当前客户端连接的统计信息收集。这意味着您可以通过设置 myisam_stats_method 的会话值，强制使用特定方法重新生成表的统计信息，而不会影响其他客户端。The innodb_stats_method system variable has a global value; the myisam_stats_method system variable has both global and session values. Setting the global value affects statistics collection for tables from the corresponding storage engine. Setting the session value affects statistics collection only for the current client connection. This means that you can force a table's statistics to be regenerated with a given method without affecting other clients by setting the session value of myisam_stats_method.

要重新生成 MyISAM 表的统计信息，您可以使用以下任何方法：To regenerate MyISAM table statistics, you can use any of the following methods:

关于使用 innodb_stats_method 和 myisam_stats_method 的一些注意事项：Some caveats regarding the use of innodb_stats_method and myisam_stats_method: