几乎所有的关系型数据库都是基于成本的优化器，其中成本的计算都依赖于统计信息。所以优化器能否选择正确的执行计划，统计信息至关重要。
在innodb里，统计信息的相关参数如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show variables like '%innodb_stat%'; +--------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------+-------------+ | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | +--------------------------------------+-------------+ |
- innodb_stats_persistent：控制统计信息是否持久化，默认为ON，持久化统计信息将记录在mysql.innodb_table_stats、mysql.innodb_index_stats中。
- innodb_stats_auto_recalc：默认为ON，控制当持久化统计信息打开时，10%比例的数据变更，将自动触发统计信息的收集。也可以通过表属性STATS_AUTO_RECALC单独对表定制比例。如果设置为OFF将禁用该功能。
The innodb_stats_auto_recalc variable, which is enabled by default, controls whether statistics are calculated automatically when a table undergoes changes to more than 10% of its rows. You can also configure automatic statistics recalculation for individual tables by specifying the STATS_AUTO_RECALC clause when creating or altering a table.
Because of the asynchronous nature of automatic statistics recalculation, which occurs in the background, statistics may not be recalculated instantly after running a DML operation that affects more than 10% of a table, even when innodb_stats_auto_recalc is enabled. Statistics recalculation can be delayed by few seconds in some cases. If up-to-date statistics are required immediately, run ANALYZE TABLE to initiate a synchronous (foreground) recalculation of statistics.
If innodb_stats_auto_recalc is disabled, you can ensure the accuracy of optimizer statistics by executing the ANALYZE TABLE statement after making substantial changes to indexed columns. You might also consider adding ANALYZE TABLE to setup scripts that you run after loading data, and running ANALYZE TABLE on a schedule at times of low activity.
When an index is added to an existing table, or when a column is added or dropped, index statistics are calculated and added to the innodb_index_stats table regardless of the value of innodb_stats_auto_recalc.
- innodb_stats_method：控制在收集统计信息时，对于null之间的比较如何处理，默认为nulls_equal，含义是认为null之间相等。
- innodb_stats_include_delete_marked：控制未提交事务的情况下，收集统计信息时，被删除的行是否计算在内。默认为OFF。
- innodb_stats_persistent_sample_pages：控制收集持久统计信息时的默认采样page数。
- innodb_stats_transient_sample_pages：控制收集非持久化统计信息时的默认采样page数。
- innodb_stats_on_metadata：默认为OFF，当该参数启用时，SHOW TABLE STATUS, SHOW INDEX, 或者查询Information Schema TABLES or STATISTICS等表时，会触发非持久化统计信息收集。