MySQL的统计信息学习总结

 

MySQL统计信息是指数据库通过采样、统计出来的表、索引的相关信息，例如，表的记录数、聚集索引page个数、字段的Cardinality....。MySQL在生成执行计划时,需要根据索引的统计信息进行估算,计算出最低代价（或者说是最小开销）的执行计划.MySQL支持有限的索引统计信息，因存储引擎不同而统计信息收集的方式也不同. MySQL官方关于统计信息的概念介绍几乎等同于无，不过对于已经接触过其它类型数据库的同学而言，理解这个概念应该不在话下。相对于其它数据库而言，MySQL统计信息无法手工删除。MySQL 8.0之前的版本，MySQL是没有直方图的。

 

统计信息参数

 

MySQL的InnoDB存储引擎的统计信息参数有7（个别版本有8个之多），如下所示：

 

MySQL 5.6.41 有8个参数：

 

mysql> show variables like ‘innodb_stats%‘;

+--------------------------------------+-------------+

| 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_sample_pages            | 8           |

| innodb_stats_transient_sample_pages  | 8           |

+--------------------------------------+-------------+

8 rows in set (0.00 sec)

 

MySQL 8.0.18 有7个参数：

 

mysql> show variables like ‘innodb_stats%‘;

+--------------------------------------+-------------+

| 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_stats_auto_recalc	是否自动触发更新统计信息。当被修改的数据超过10%时就会触发统计信息重新统计计算
innodb_stats_include_delete_marked	控制在重新计算统计信息时是否会考虑删除标记的记录。
innodb_stats_method	对null值的统计方法
innodb_stats_on_metadata	操作元数据时是否触发更新统计信息
innodb_stats_persistent	统计信息是否持久化
innodb_stats_sample_pages	不推荐使用，已经被innodb_stats_persistent_sample_pages替换
innodb_stats_persistent_sample_pages	持久化抽样page数
innodb_stats_transient_sample_pages	瞬时抽样page数

 

 

参数innodb_stats_auto_recalc

 

 

该参数innodb_stats_auto_recalc控制是否自动重新计算统计信息，当表中数据有大于10%被修改时就会重新计算统计信息（注意，由于统计信息重新计算是在后台发生，而且它是异步处理，这个可能存在延时，不会立即触发，具体见下面介绍）。如果关闭了innodb_stats_auto_recalc,需要通过analyze table来保证统计信息的准确性。不管有没有开启全局变量innodb_stats_auto_recalc。即使innodb_stats_auto_recalc=OFF时，当新索引被增加到表中，所有索引的统计信息会被重新计算并且更新到innodb_index_stats表上。

 

 

 

下面验证一下系统变量innodb_stats_auto_recalc=OFF时，创建索引时，会触发该表所有索引重新统计计算。

 

mysql> set global innodb_stats_auto_recalc=off;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘innodb_stats_auto_recalc%‘;

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_stats_auto_recalc | OFF   |

+--------------------------+-------+

1 row in set (0.00 sec)

mysql> select * from mysql.innodb_index_stats 

    -> where database_name=‘MyDB‘ and table_name = ‘test‘;

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

| MyDB          | test       | GEN_CLUST_INDEX | 2019-10-28 14:54:48 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |

| MyDB          | test       | GEN_CLUST_INDEX | 2019-10-28 14:54:48 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | GEN_CLUST_INDEX | 2019-10-28 14:54:48 | size         |          1 |        NULL | Number of pages in the index      |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

3 rows in set (0.00 sec)

mysql> create index ix_test_name on test(name);

mysql> select * from mysql.innodb_index_stats 

    -> where database_name=‘MyDB‘ and table_name = ‘test‘;

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

| MyDB          | test       | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |

| MyDB          | test       | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | size         |          1 |        NULL | Number of pages in the index      |

| MyDB          | test       | ix_test_name    | 2019-10-28 22:02:07 | n_diff_pfx01 |          1 |           1 | name                              |

| MyDB          | test       | ix_test_name    | 2019-10-28 22:02:07 | n_diff_pfx02 |          2 |           1 | name,DB_ROW_ID                    |

| MyDB          | test       | ix_test_name    | 2019-10-28 22:02:07 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | ix_test_name    | 2019-10-28 22:02:07 | size         |          1 |        NULL | Number of pages in the index      |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

7 rows in set (0.00 sec)

 

 

下面是我另外一个测试，全局变量innodb_stats_auto_recalc=ON的情况，修改表的属性STATS_AUTO_RECALC=0，然后新建索引，测试验证发现也会重新计算所有索引的统计信息。

mysql> select * from mysql.innodb_index_stats 

    -> where database_name=‘MyDB‘ and table_name = ‘test‘;

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

| MyDB          | test       | PRIMARY    | 2019-10-30 15:49:00 | n_diff_pfx01 |          0 |           1 | id                                |

| MyDB          | test       | PRIMARY    | 2019-10-30 15:49:00 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | PRIMARY    | 2019-10-30 15:49:00 | size         |          1 |        NULL | Number of pages in the index      |

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

3 rows in set (0.01 sec)

mysql> ALTER TABLE test STATS_AUTO_RECALC=0;

Query OK, 0 rows affected (0.27 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from mysql.innodb_index_stats 

    -> where database_name=‘MyDB‘ and table_name = ‘test‘;

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

| MyDB          | test       | PRIMARY    | 2019-10-30 15:49:00 | n_diff_pfx01 |          0 |           1 | id                                |

| MyDB          | test       | PRIMARY    | 2019-10-30 15:49:00 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | PRIMARY    | 2019-10-30 15:49:00 | size         |          1 |        NULL | Number of pages in the index      |

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

3 rows in set (0.00 sec)

mysql> CREATE INDEX ix_test_name ON test(name);

Query OK, 0 rows affected (1.41 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from mysql.innodb_index_stats 

    -> where database_name=‘MyDB‘ and table_name = ‘test‘;

+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

| database_name | table_name | index_name   | last_update         | stat_name    | stat_value | sample_size | stat_description                  |

+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

| MyDB          | test       | PRIMARY      | 2019-10-30 15:54:22 | n_diff_pfx01 |          0 |           1 | id                                |

| MyDB          | test       | PRIMARY      | 2019-10-30 15:54:22 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | PRIMARY      | 2019-10-30 15:54:22 | size         |          1 |        NULL | Number of pages in the index      |

| MyDB          | test       | ix_test_name | 2019-10-30 15:54:22 | n_diff_pfx01 |        999 |          17 | name                              |

| MyDB          | test       | ix_test_name | 2019-10-30 15:54:22 | n_diff_pfx02 |        999 |          17 | name,id                           |

| MyDB          | test       | ix_test_name | 2019-10-30 15:54:22 | n_leaf_pages |         17 |        NULL | Number of leaf pages in the index |

| MyDB          | test       | ix_test_name | 2019-10-30 15:54:22 | size         |         18 |        NULL | Number of pages in the index      |

+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

7 rows in set (0.00 sec)

mysql> 

 

关于统计信息重新计算延时，官方的介绍如下：

 

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

 

 

参数innodb_stats_include_delete_marked

 

重新计算统计信息时是否会考虑删除标记的记录.

innodb_stats_include_delete_marked can be enabled to ensure that delete-marked records are included when calculating persistent optimizer statistics.