您的位置: 首页 > 编程笔记

MySQL的统计信息学习总结

MySQL 统计学 时间:2019-11-08  查看:11   收藏

统计信息概念

 

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      |


                

 

0% (0)
0% (0)
0.094575s