MySQl 配置InnoDB持久化的优化器统计资讯
2022-03-20 13:47MySQL
配置InnoDB的优化器统计资讯
介绍如何为InnoDB表配置持久化和非持久化的优化器统计资讯。
永续性优化器统计资料将被持久储存可以跨跃伺服器的重启，从而实现更大的计划稳定性和更一致的查询效能。永续性优化器统计资料还提供了控制和灵活性以及这些额外的好处:
.您可以使用innodb_stats_auto_recalc配置选项来控制是否在对錶进行重大更改后自动更新统计资讯
.您可以使用STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句以及CREATE TABLE和ALTER TABLE语句为单个表配置优化器统计资讯
.您可以在mysql.innodb_table_stats和mysql.innodb_index_stats表中查询优化器的统计资料
mysql> select * from mysql.innodb_table_stats;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: mysql
+---------------+-------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------------+---------------------+--------+----------------------+--------------------------+
| cs | address | 2021-06-03 16:17:22 | 3 | 1 | 0 |
| cs | articles | 2021-01-12 16:39:07 | 2 | 1 | 0 |
| cs | bit_test | 2021-06-18 16:28:55 | 2 | 1 | 0 |
| cs | individual | 2021-05-27 17:20:38 | 8 | 1 | 0 |
| cs | person | 2021-06-03 16:13:47 | 2 | 1 | 0 |
| cs | person1 | 2021-05-27 17:02:34 | 3 | 1 | 0 |
| cs | rewrite_rules | 2020-07-15 17:34:15 | 3 | 1 | 0 |
| cs | sequence | 2021-01-18 10:45:15 | 0 | 1 | 0 |
| cs | t | 2021-09-13 22:27:49 | 0 | 1 | 0 |
| cs | t1 | 2021-07-06 16:00:24 | 2 | 1 | 0 |
| cs | test | 2021-06-23 15:47:12 | 0 | 1 | 0 |
| cs | test2 | 2021-06-23 15:57:48 | 2 | 1 | 0 |
| d1 | T1 | 2021-08-06 17:16:37 | 0 | 1 | 0 |
| d1 | child | 2021-08-17 16:27:43 | 2 | 1 | 0 |
| d1 | t | 2021-08-17 17:30:55 | 0 | 1 | 0 |
| d1 | t1 | 2021-08-09 16:18:51 | 0 | 1 | 0 |
| mysql | articles | 2021-01-08 15:21:02 | 8 | 1 | 1 |
| mysql | child | 2021-03-01 11:39:44 | 0 | 1 | 1 |
| mysql | client_firms#P#r0 | 2021-03-01 10:13:09 | 0 | 1 | 0 |
| mysql | client_firms#P#r1 | 2021-03-01 10:13:09 | 0 | 1 | 0 |
| mysql | client_firms#P#r2 | 2021-03-01 10:13:09 | 0 | 1 | 0 |
| mysql | client_firms#P#r3 | 2021-03-01 10:13:09 | 0 | 1 | 0 |
| mysql | count | 2020-01-06 10:45:24 | 0 | 1 | 0 |
| mysql | cs | 2020-04-02 18:58:57 | 0 | 1 | 0 |
| mysql | customer | 2021-03-01 11:43:54 | 0 | 1 | 0 |
| mysql | gtid_executed | 2019-06-17 14:28:37 | 0 | 1 | 0 |
| mysql | imptest | 2019-10-28 11:47:04 | 2 | 1 | 0 |
| mysql | jemp | 2021-04-26 08:12:27 | 4 | 1 | 1 |
| mysql | lc#P#p0 | 2021-03-01 10:20:18 | 0 | 1 | 0 |
| mysql | lc#P#p1 | 2021-03-01 10:20:18 | 0 | 1 | 0 |
| mysql | lc#P#p2 | 2021-03-01 10:20:18 | 0 | 1 | 0 |
| mysql | lc#P#p3 | 2021-03-01 10:20:18 | 0 | 1 | 0 |
| mysql | my_stopwords | 2021-01-08 16:42:36 | 0 | 1 | 0 |
| mysql | new_table | 2021-01-13 16:53:36 | 0 | 1 | 0 |
| mysql | opening_lines | 2021-01-08 16:46:10 | 8 | 1 | 1 |
| mysql | parent | 2021-03-01 11:39:33 | 0 | 1 | 0 |
| mysql | product | 2021-03-01 11:43:50 | 0 | 1 | 0 |
| mysql | product_order | 2021-03-01 11:44:23 | 0 | 1 | 2 |
| mysql | rc#P#p0 | 2021-02-26 11:21:58 | 0 | 1 | 0 |
| mysql | rc#P#p1 | 2021-02-26 11:21:58 | 0 | 1 | 0 |
| mysql | rc#P#p2 | 2021-02-26 11:21:58 | 0 | 1 | 0 |
| mysql | rc#P#p3 | 2021-02-26 11:21:58 | 0 | 1 | 0 |
| mysql | rc#P#p4 | 2021-02-26 11:21:58 | 0 | 1 | 0 |
| mysql | sales | 2021-01-20 17:00:50 | 0 | 1 | 0 |
| mysql | t | 2021-04-26 15:37:14 | 0 | 1 | 0 |
| mysql | t1 | 2021-04-26 11:04:17 | 0 | 1 | 0 |
| mysql | t2 | 2021-04-26 11:05:46 | 0 | 1 | 0 |
| mysql | t3 | 2021-04-26 11:05:58 | 0 | 1 | 0 |
| mysql | t4 | 2021-04-26 11:11:41 | 0 | 1 | 0 |
| mysql | t5 | 2020-10-10 16:24:57 | 0 | 1 | 0 |
| mysql | test | 2020-01-07 10:56:04 | 0 | 1 | 0 |
| mysql | total | 2021-02-19 15:22:54 | 0 | 1 | 0 |
| mysql | triangle | 2021-03-01 14:57:31 | 3 | 1 | 0 |
| query_rewrite | rewrite_rules | 2020-07-15 16:36:38 | 3 | 1 | 0 |
| sys | sys_config | 2019-06-17 14:28:41 | 6 | 1 | 0 |
+---------------+-------------------+---------------------+--------+----------------------+--------------------------+
55 rows in set (0.04 sec)
mysql> select * from mysql.innodb_index_stats;
+---------------+-------------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+-------------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| cs | address | PRIMARY | 2021-06-03 16:17:22 | n_diff_pfx01 | 3 | 1 | address_id |
| cs | address | PRIMARY | 2021-06-03 16:17:22 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | address | PRIMARY | 2021-06-03 16:17:22 | size | 1 | NULL | Number of pages in the index |
| cs | articles | GEN_CLUST_INDEX | 2021-01-12 16:39:07 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |
| cs | articles | GEN_CLUST_INDEX | 2021-01-12 16:39:07 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | articles | GEN_CLUST_INDEX | 2021-01-12 16:39:07 | size | 1 | NULL | Number of pages in the index |
| cs | bit_test | GEN_CLUST_INDEX | 2021-06-18 16:28:55 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |
| cs | bit_test | GEN_CLUST_INDEX | 2021-06-18 16:28:55 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | bit_test | GEN_CLUST_INDEX | 2021-06-18 16:28:55 | size | 1 | NULL | Number of pages in the index |
| cs | individual | PRIMARY | 2021-05-27 17:20:38 | n_diff_pfx01 | 8 | 1 | individual_id |
| cs | individual | PRIMARY | 2021-05-27 17:20:38 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | individual | PRIMARY | 2021-05-27 17:20:38 | size | 1 | NULL | Number of pages in the index |
| cs | person | PRIMARY | 2021-06-03 16:13:47 | n_diff_pfx01 | 2 | 1 | person_id |
| cs | person | PRIMARY | 2021-06-03 16:13:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | person | PRIMARY | 2021-06-03 16:13:47 | size | 1 | NULL | Number of pages in the index |
| cs | person1 | PRIMARY | 2021-05-27 17:02:34 | n_diff_pfx01 | 3 | 1 | person_id |
| cs | person1 | PRIMARY | 2021-05-27 17:02:34 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | person1 | PRIMARY | 2021-05-27 17:02:34 | size | 1 | NULL | Number of pages in the index |
| cs | rewrite_rules | PRIMARY | 2020-07-15 17:34:15 | n_diff_pfx01 | 3 | 1 | id |
| cs | rewrite_rules | PRIMARY | 2020-07-15 17:34:15 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | rewrite_rules | PRIMARY | 2020-07-15 17:34:15 | size | 1 | NULL | Number of pages in the index |
| cs | sequence | GEN_CLUST_INDEX | 2021-01-18 10:45:15 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| cs | sequence | GEN_CLUST_INDEX | 2021-01-18 10:45:15 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | sequence | GEN_CLUST_INDEX | 2021-01-18 10:45:15 | size | 1 | NULL | Number of pages in the index |
| cs | t | GEN_CLUST_INDEX | 2021-09-13 22:27:49 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| cs | t | GEN_CLUST_INDEX | 2021-09-13 22:27:49 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | t | GEN_CLUST_INDEX | 2021-09-13 22:27:49 | size | 1 | NULL | Number of pages in the index |
| cs | t1 | GEN_CLUST_INDEX | 2021-07-06 16:00:24 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |
| cs | t1 | GEN_CLUST_INDEX | 2021-07-06 16:00:24 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | t1 | GEN_CLUST_INDEX | 2021-07-06 16:00:24 | size | 1 | NULL | Number of pages in the index |
| cs | test | PRIMARY | 2021-06-23 15:47:12 | n_diff_pfx01 | 0 | 1 | id |
| cs | test | PRIMARY | 2021-06-23 15:47:12 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | test | PRIMARY | 2021-06-23 15:47:12 | size | 1 | NULL | Number of pages in the index |
| cs | test2 | PRIMARY | 2021-06-23 15:57:48 | n_diff_pfx01 | 1 | 1 | id |
| cs | test2 | PRIMARY | 2021-06-23 15:57:48 | n_diff_pfx02 | 2 | 1 | id,ts |
| cs | test2 | PRIMARY | 2021-06-23 15:57:48 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| cs | test2 | PRIMARY | 2021-06-23 15:57:48 | size | 1 | NULL | Number of pages in the index |
| d1 | T1 | GEN_CLUST_INDEX | 2021-08-06 17:16:37 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| d1 | T1 | GEN_CLUST_INDEX | 2021-08-06 17:16:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| d1 | T1 | GEN_CLUST_INDEX | 2021-08-06 17:16:37 | size | 1 | NULL | Number of pages in the index |
| d1 | child | PRIMARY | 2021-08-17 16:27:43 | n_diff_pfx01 | 2 | 1 | id |
| d1 | child | PRIMARY | 2021-08-17 16:27:43 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| d1 | child | PRIMARY | 2021-08-17 16:27:43 | size | 1 | NULL | Number of pages in the index |
| d1 | t | GEN_CLUST_INDEX | 2021-08-17 17:30:55 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| d1 | t | GEN_CLUST_INDEX | 2021-08-17 17:30:55 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| d1 | t | GEN_CLUST_INDEX | 2021-08-17 17:30:55 | size | 1 | NULL | Number of pages in the index |
| d1 | t1 | GEN_CLUST_INDEX | 2021-08-09 16:18:51 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| d1 | t1 | GEN_CLUST_INDEX | 2021-08-09 16:18:51 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| d1 | t1 | GEN_CLUST_INDEX | 2021-08-09 16:18:51 | size | 1 | NULL | Number of pages in the index |
| mysql | articles | FTS_DOC_ID_INDEX | 2021-01-08 15:21:02 | n_diff_pfx01 | 8 | 1 | FTS_DOC_ID |
| mysql | articles | FTS_DOC_ID_INDEX | 2021-01-08 15:21:02 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | articles | FTS_DOC_ID_INDEX | 2021-01-08 15:21:02 | size | 1 | NULL | Number of pages in the index |
| mysql | articles | PRIMARY | 2021-01-08 15:21:02 | n_diff_pfx01 | 8 | 1 | id |
| mysql | articles | PRIMARY | 2021-01-08 15:21:02 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | articles | PRIMARY | 2021-01-08 15:21:02 | size | 1 | NULL | Number of pages in the index |
| mysql | child | GEN_CLUST_INDEX | 2021-03-01 11:39:44 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | child | GEN_CLUST_INDEX | 2021-03-01 11:39:44 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | child | GEN_CLUST_INDEX | 2021-03-01 11:39:44 | size | 1 | NULL | Number of pages in the index |
| mysql | child | par_ind | 2021-03-01 11:39:44 | n_diff_pfx01 | 0 | 1 | parent_id |
| mysql | child | par_ind | 2021-03-01 11:39:44 | n_diff_pfx02 | 0 | 1 | parent_id,DB_ROW_ID |
| mysql | child | par_ind | 2021-03-01 11:39:44 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | child | par_ind | 2021-03-01 11:39:44 | size | 1 | NULL | Number of pages in the index |
| mysql | client_firms#P#r0 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | client_firms#P#r0 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | client_firms#P#r0 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | size | 1 | NULL | Number of pages in the index |
| mysql | client_firms#P#r1 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | client_firms#P#r1 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | client_firms#P#r1 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | size | 1 | NULL | Number of pages in the index |
| mysql | client_firms#P#r2 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | client_firms#P#r2 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | client_firms#P#r2 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | size | 1 | NULL | Number of pages in the index |
| mysql | client_firms#P#r3 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | client_firms#P#r3 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | client_firms#P#r3 | GEN_CLUST_INDEX | 2021-03-01 10:13:09 | size | 1 | NULL | Number of pages in the index |
| mysql | count | GEN_CLUST_INDEX | 2020-01-06 10:45:24 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | count | GEN_CLUST_INDEX | 2020-01-06 10:45:24 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | count | GEN_CLUST_INDEX | 2020-01-06 10:45:24 | size | 1 | NULL | Number of pages in the index |
| mysql | cs | GEN_CLUST_INDEX | 2020-04-02 18:58:57 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | cs | GEN_CLUST_INDEX | 2020-04-02 18:58:57 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | cs | GEN_CLUST_INDEX | 2020-04-02 18:58:57 | size | 1 | NULL | Number of pages in the index |
| mysql | customer | PRIMARY | 2021-03-01 11:43:54 | n_diff_pfx01 | 0 | 1 | id |
| mysql | customer | PRIMARY | 2021-03-01 11:43:54 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | customer | PRIMARY | 2021-03-01 11:43:54 | size | 1 | NULL | Number of pages in the index |
| mysql | gtid_executed | PRIMARY | 2019-06-17 14:28:37 | n_diff_pfx01 | 0 | 1 | source_uuid |
| mysql | gtid_executed | PRIMARY | 2019-06-17 14:28:37 | n_diff_pfx02 | 0 | 1 | source_uuid,interval_start |
| mysql | gtid_executed | PRIMARY | 2019-06-17 14:28:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | gtid_executed | PRIMARY | 2019-06-17 14:28:37 | size | 1 | NULL | Number of pages in the index |
| mysql | imptest | GEN_CLUST_INDEX | 2019-10-28 11:47:04 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |
| mysql | imptest | GEN_CLUST_INDEX | 2019-10-28 11:47:04 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | imptest | GEN_CLUST_INDEX | 2019-10-28 11:47:04 | size | 1 | NULL | Number of pages in the index |
| mysql | jemp | GEN_CLUST_INDEX | 2021-04-26 08:12:27 | n_diff_pfx01 | 4 | 1 | DB_ROW_ID |
| mysql | jemp | GEN_CLUST_INDEX | 2021-04-26 08:12:27 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | jemp | GEN_CLUST_INDEX | 2021-04-26 08:12:27 | size | 1 | NULL | Number of pages in the index |
| mysql | jemp | i | 2021-04-26 08:12:27 | n_diff_pfx01 | 4 | 1 | g |
| mysql | jemp | i | 2021-04-26 08:12:27 | n_diff_pfx02 | 4 | 1 | g,DB_ROW_ID |
| mysql | jemp | i | 2021-04-26 08:12:27 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | jemp | i | 2021-04-26 08:12:27 | size | 1 | NULL | Number of pages in the index |
| mysql | lc#P#p0 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | lc#P#p0 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | lc#P#p0 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | size | 1 | NULL | Number of pages in the index |
| mysql | lc#P#p1 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | lc#P#p1 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | lc#P#p1 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | size | 1 | NULL | Number of pages in the index |
| mysql | lc#P#p2 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | lc#P#p2 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | lc#P#p2 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | size | 1 | NULL | Number of pages in the index |
| mysql | lc#P#p3 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | lc#P#p3 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | lc#P#p3 | GEN_CLUST_INDEX | 2021-03-01 10:20:18 | size | 1 | NULL | Number of pages in the index |
| mysql | my_stopwords | GEN_CLUST_INDEX | 2021-01-08 16:42:36 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | my_stopwords | GEN_CLUST_INDEX | 2021-01-08 16:42:36 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | my_stopwords | GEN_CLUST_INDEX | 2021-01-08 16:42:36 | size | 1 | NULL | Number of pages in the index |
| mysql | new_table | GEN_CLUST_INDEX | 2021-01-13 16:53:36 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | new_table | GEN_CLUST_INDEX | 2021-01-13 16:53:36 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | new_table | GEN_CLUST_INDEX | 2021-01-13 16:53:36 | size | 1 | NULL | Number of pages in the index |
| mysql | opening_lines | FTS_DOC_ID_INDEX | 2021-01-08 16:46:10 | n_diff_pfx01 | 8 | 1 | FTS_DOC_ID |
| mysql | opening_lines | FTS_DOC_ID_INDEX | 2021-01-08 16:46:10 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | opening_lines | FTS_DOC_ID_INDEX | 2021-01-08 16:46:10 | size | 1 | NULL | Number of pages in the index |
| mysql | opening_lines | PRIMARY | 2021-01-08 16:46:10 | n_diff_pfx01 | 8 | 1 | id |
| mysql | opening_lines | PRIMARY | 2021-01-08 16:46:10 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | opening_lines | PRIMARY | 2021-01-08 16:46:10 | size | 1 | NULL | Number of pages in the index |
| mysql | parent | PRIMARY | 2021-03-01 11:39:33 | n_diff_pfx01 | 0 | 1 | id |
| mysql | parent | PRIMARY | 2021-03-01 11:39:33 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | parent | PRIMARY | 2021-03-01 11:39:33 | size | 1 | NULL | Number of pages in the index |
| mysql | product | PRIMARY | 2021-03-01 11:43:50 | n_diff_pfx01 | 0 | 1 | category |
| mysql | product | PRIMARY | 2021-03-01 11:43:50 | n_diff_pfx02 | 0 | 1 | category,id |
| mysql | product | PRIMARY | 2021-03-01 11:43:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | product | PRIMARY | 2021-03-01 11:43:50 | size | 1 | NULL | Number of pages in the index |
| mysql | product_order | PRIMARY | 2021-03-01 11:44:23 | n_diff_pfx01 | 0 | 1 | no |
| mysql | product_order | PRIMARY | 2021-03-01 11:44:23 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | product_order | PRIMARY | 2021-03-01 11:44:23 | size | 1 | NULL | Number of pages in the index |
| mysql | product_order | customer_id | 2021-03-01 11:44:23 | n_diff_pfx01 | 0 | 1 | customer_id |
| mysql | product_order | customer_id | 2021-03-01 11:44:23 | n_diff_pfx02 | 0 | 1 | customer_id,no |
| mysql | product_order | customer_id | 2021-03-01 11:44:23 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | product_order | customer_id | 2021-03-01 11:44:23 | size | 1 | NULL | Number of pages in the index |
| mysql | product_order | product_category | 2021-03-01 11:44:23 | n_diff_pfx01 | 0 | 1 | product_category |
| mysql | product_order | product_category | 2021-03-01 11:44:23 | n_diff_pfx02 | 0 | 1 | product_category,product_id |
| mysql | product_order | product_category | 2021-03-01 11:44:23 | n_diff_pfx03 | 0 | 1 | product_category,product_id,no |
| mysql | product_order | product_category | 2021-03-01 11:44:23 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | product_order | product_category | 2021-03-01 11:44:23 | size | 1 | NULL | Number of pages in the index |
| mysql | rc#P#p0 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | rc#P#p0 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | rc#P#p0 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | size | 1 | NULL | Number of pages in the index |
| mysql | rc#P#p1 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | rc#P#p1 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | rc#P#p1 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | size | 1 | NULL | Number of pages in the index |
| mysql | rc#P#p2 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | rc#P#p2 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | rc#P#p2 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | size | 1 | NULL | Number of pages in the index |
| mysql | rc#P#p3 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | rc#P#p3 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | rc#P#p3 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | size | 1 | NULL | Number of pages in the index |
| mysql | rc#P#p4 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | rc#P#p4 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | rc#P#p4 | GEN_CLUST_INDEX | 2021-02-26 11:21:58 | size | 1 | NULL | Number of pages in the index |
| mysql | sales | GEN_CLUST_INDEX | 2021-01-20 17:00:50 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | sales | GEN_CLUST_INDEX | 2021-01-20 17:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | sales | GEN_CLUST_INDEX | 2021-01-20 17:00:50 | size | 1 | NULL | Number of pages in the index |
| mysql | t | GEN_CLUST_INDEX | 2021-04-26 15:37:14 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | t | GEN_CLUST_INDEX | 2021-04-26 15:37:14 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t | GEN_CLUST_INDEX | 2021-04-26 15:37:14 | size | 1 | NULL | Number of pages in the index |
| mysql | t1 | PRIMARY | 2021-04-26 11:04:17 | n_diff_pfx01 | 0 | 1 | c1 |
| mysql | t1 | PRIMARY | 2021-04-26 11:04:17 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t1 | PRIMARY | 2021-04-26 11:04:17 | size | 1 | NULL | Number of pages in the index |
| mysql | t2 | PRIMARY | 2021-04-26 11:05:46 | n_diff_pfx01 | 0 | 1 | c1 |
| mysql | t2 | PRIMARY | 2021-04-26 11:05:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t2 | PRIMARY | 2021-04-26 11:05:46 | size | 1 | NULL | Number of pages in the index |
| mysql | t3 | PRIMARY | 2021-04-26 11:05:58 | n_diff_pfx01 | 0 | 1 | c1 |
| mysql | t3 | PRIMARY | 2021-04-26 11:05:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t3 | PRIMARY | 2021-04-26 11:05:58 | size | 1 | NULL | Number of pages in the index |
| mysql | t4 | PRIMARY | 2021-04-26 11:11:41 | n_diff_pfx01 | 0 | 1 | c1 |
| mysql | t4 | PRIMARY | 2021-04-26 11:11:41 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t4 | PRIMARY | 2021-04-26 11:11:41 | size | 1 | NULL | Number of pages in the index |
| mysql | t5 | GEN_CLUST_INDEX | 2020-10-10 16:24:57 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | t5 | GEN_CLUST_INDEX | 2020-10-10 16:24:57 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t5 | GEN_CLUST_INDEX | 2020-10-10 16:24:57 | size | 1 | NULL | Number of pages in the index |
| mysql | test | GEN_CLUST_INDEX | 2020-01-07 10:56:04 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | test | GEN_CLUST_INDEX | 2020-01-07 10:56:04 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | test | GEN_CLUST_INDEX | 2020-01-07 10:56:04 | size | 1 | NULL | Number of pages in the index |
| mysql | total | GEN_CLUST_INDEX | 2021-02-19 15:22:54 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |
| mysql | total | GEN_CLUST_INDEX | 2021-02-19 15:22:54 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | total | GEN_CLUST_INDEX | 2021-02-19 15:22:54 | size | 1 | NULL | Number of pages in the index |
| mysql | triangle | GEN_CLUST_INDEX | 2021-03-01 14:57:31 | n_diff_pfx01 | 3 | 1 | DB_ROW_ID |
| mysql | triangle | GEN_CLUST_INDEX | 2021-03-01 14:57:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | triangle | GEN_CLUST_INDEX | 2021-03-01 14:57:31 | size | 1 | NULL | Number of pages in the index |
| query_rewrite | rewrite_rules | PRIMARY | 2020-07-15 16:36:38 | n_diff_pfx01 | 3 | 1 | id |
| query_rewrite | rewrite_rules | PRIMARY | 2020-07-15 16:36:38 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| query_rewrite | rewrite_rules | PRIMARY | 2020-07-15 16:36:38 | size | 1 | NULL | Number of pages in the index |
| sys | sys_config | PRIMARY | 2019-06-17 14:28:41 | n_diff_pfx01 | 6 | 1 | variable |
| sys | sys_config | PRIMARY | 2019-06-17 14:28:41 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| sys | sys_config | PRIMARY | 2019-06-17 14:28:41 | size | 1 | NULL | Number of pages in the index |
+---------------+-------------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
191 rows in set (0.00 sec)
.可以检视mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列检视统计资讯最后一次更新的时间。
.您可以手动修改mysql.innodb_table_stats和mysql.innodb_index_stats表强制执行特定的查询优化计划或在不修改资料库的情况下测试可选计划。
预设情况下，持久化优化器统计特性是启用的(innodb_stats_persistent=ON)。
非持久优化器统计资讯在每次伺服器重启时和一些其他操作之后被清除，并在下一个表访问时重新计算。因此，在重新计算统计资讯时可能会产生不同的估计，导致执行计划中的不同选择和查询效能的变化
本节还提供了有关估计ANALYZE TABLE複杂度的资讯，这在试图在準确的统计资料和ANALYZE TABLE执行时间之间取得平衡时可能很有用。
配置持久优化器统计资讯引数
永续性优化器统计资讯特性将统计资讯储存到磁碟，并在伺服器重启时保持这些统计资讯的永续性，从而提高了计划的稳定性，这样优化器就更有可能在每次给定查询时做出一致的选择。
当innodb_stats_persistent=ON或使用STATS_PERSISTENT=1建立或修改单个表时，优化器统计资讯被持久化到磁碟。innodb_stats_persistent预设启用。
以前，在每次伺服器重启和一些其他操作之后，都会清除优化器统计资讯，并在下一个表访问时重新计算。因此，在重新计算统计资讯时可能会产生不同的估计，导致查询执行计划中的不同选择，从而导致查询效能的变化。
永续性统计资讯储存在mysql.innodb_table_stats和mysql.innodb_index_stats表中。
要恢复使用非持久优化器统计资讯，可以使用ALTER TABLE tbl_name STATS_PERSISTENT=0语句修改表。
为持久优化器统计资讯配置自动统计资讯计算
innodb_stats_auto_recalc配置选项在预设情况下是启用的，它决定是否在表发生重大更改(超过10%的行)时自动计算统计资料。您还可以在CREATE TABLE或ALTER TABLE语句中使用STATS_AUTO_RECALC子句为单个表配置自动统计资讯重新计算。innodb_stats_auto_recalc预设启用。
mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON |
+--------------------------+-------+
1 row in set (0.01 sec)
由于自动统计资讯重新计算的非同步特性(发生在后台)，即使启用了innodb_stats_auto_recalc，当DML操作影响一个表的10%以上时，统计资料可能不会立即重新计算。在某些情况下，统计资讯的重新计算可能会延迟几秒钟。如果在更改表的重要部分后需要立即更新统计资讯，则执行analyze table来启动同步(前台)统计资讯的重新计算。
如果innodb_stats_auto_recalc被禁用，那幺在对索引的列进行大量更改之后，为每个适用的表发出ANALYZE TABLE语句，以确保优化器统计资料的準确性。您可以在将代表性资料载入到表中之后，在设定指令码中执行此语句，并在DML操作显着改变了索引列的内容之后定期执行它，或者在活动较少的时候排程执行它。当一个新的索引被新增到一个现有的表时，索引统计资讯被计算并新增到innodb_index_stats表中，而不管innodb_stats_auto_recalc的值是多少。
要确保在建立新索引时收集统计资讯，可以启用innodb_stats_auto_recalc选项，也可以在启用持久统计模式时，在建立每个新索引后执行ANALYZE TABLE。
为个别表配置优化器统计资讯引数
innodb_stats_persistent,innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全域性配置选项。要覆盖这些系统範围的设定，併为各个表配置优化器统计资讯引数，可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句来实现。
.STATS_PERSISTENT指定InnoDB表是否启用持久化统计资讯。预设值导致表的持久统计资讯设定由innodb_stats_persistent配置选项决定。值1启用表的持久统计，而值0关闭此特性。在通过CREATE TABLE或ALTER TABLE语句启用持久统计资讯后，在将代表性资料载入到表中之后，发出ANALYZE TABLE语句来计算统计资讯
.STATS_AUTO_RECALC指定是否自动重新计算InnoDB表的持久统计资讯。预设值导致表的持久统计资讯设定由innodb_stats_auto_recalc配置选项决定。当表中10%的资料发生变化时，值1将导致重新计算统计资料。0可以防止对该表进行自动重新计算;使用此设定，在对錶进行实质性更改后，发出一条ANALYZE TABLE语句来重新计算统计资料。
.STATS_SAMPLE_PAGES指定在估计索引列的基数和其他统计资讯(例如由ANALYZE TABLE计算的统计资讯)时要抽样的索引页数。
三个子句都在下面的CREATE TABLE示例中指定:
mysql> CREATE TABLE t1 (
-> id int(8) NOT NULL auto_increment,
-> data varchar(255),
-> date datetime,
-> PRIMARY KEY ( id ),
-> INDEX DATE_IX ( date )
-> ) ENGINE=InnoDB,
-> STATS_PERSISTENT=1,
-> STATS_AUTO_RECALC=1,
-> STATS_SAMPLE_PAGES=25;
Query OK, 0 rows affected (0.09 sec)
配置InnoDB优化器统计资讯的取样页面数
MySQL查询优化器使用关于键分布的估计统计资讯，根据索引的相对选择性为执行计划选择索引。像ANALYZE TABLE这样的操作会导致InnoDB从表上的每个索引中随机取样页，以估计索引的基数。(这种技术被称为随机潜水。)
为了控制统计资讯估计的质量(从而为查询优化器提供更好的资讯)，可以使用引数innodb_stats_persistent_sample_pages更改取样页面的数量，这个引数可以在执行时设定
Innodb_stats_persistent_sample_pages的预设值是20。作为一般指导原则，当遇到以下问题时，请考虑修改此引数：
1.统计资料不够準确，优化器会选择次优计划，如EXPLAIN输出所示。通过比较索引的实际基数(在索引列上执行SELECT DISTINCT返回)和mysql.innodb_index_stats持久化统计表提供的估计，可以检查统计资讯的準确性。
如果确定统计资讯不够準确，则应该增加innodb_stats_persistent_sample_pages的值，直到统计资讯估计足够準确。然而，过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE执行缓慢
2.ANALYZE TABLE太慢。在这种情况下，innodb_stats_persistent_sample_pages应该减少，直到ANALYZE TABLE的执行时间是可接受的。然而，过多地降低该值可能会导致第一个问题:不準确的统计资料和不够理想的查询执行计划
如果无法在精确的统计资料和ANALYZE TABLE执行时间之间取得平衡，那幺可以考虑减少表中索引列的数量，或者限制分割槽的数量，以降低ANALYZE TABLE的複杂性。表主键中的列数也需要考虑，因为主键列被附加到每个非唯一索引。
在永续性统计资讯计算中包括删除标记的记录
预设情况下，InnoDB在计算统计资讯读取未提交的资料。在一个未提交事务从表中删除行的情况下，InnoDB在计算行估计和索引统计时，会排除被标记删除的记录，这可能会导致使用READ UNCOMMITTED以外的事务隔离级别併发操作表的其他事务的执行计划不是最优的。为了避免这种情况，可以启用innodb_stats_include_delete_marked，以确保在计算持久优化器统计资料时，InnoDB包含有标记删除的记录。
当innodb_stats_include_delete_marked被启用后，analyze table在计算统计资讯时会考虑被标记为删除的记录。
innodb_stats_include_delete_marked是一个全域性设定会影响所有的innodb表，并且它只应用于永续性优化器统计。
innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的。
InnoDB持久化统计资讯表
持久统计特性依赖于mysql资料库的内部管理表innodb_table_stats和innodb_index_stats。这些表在所有安装、升级和从源构建过程中自动设定。
mysql> desc innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(199) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| n_rows | bigint(20) unsigned | NO | | NULL | |
| clustered_index_size | bigint(20) unsigned | NO | | NULL | |
| sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
mysql> desc innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(199) | NO | PRI | NULL | |
| index_name | varchar(64) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name | varchar(64) | NO | PRI | NULL | |
| stat_value | bigint(20) unsigned | NO | | NULL | |
| sample_size | bigint(20) unsigned | YES | | NULL | |
| stat_description | varchar(1024) | NO | | NULL | |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
innodb_table_stats和innodb_index_stats表都包含一个last_update列，显示InnoDB上次更新索引统计资讯的时间，如下例所示:
mysql> select * from innodb_table_stats \G
*************************** 1. row ***************************
database_name: cs
table_name: address
last_update: 2021-06-03 16:17:22
n_rows: 3
clustered_index_size: 1
sum_of_other_index_sizes: 0
mysql> select * from innodb_index_stats where table_name='address' \G
*************************** 1. row ***************************
database_name: cs
table_name: address
index_name: PRIMARY
last_update: 2021-06-03 16:17:22
stat_name: n_diff_pfx01
stat_value: 3
sample_size: 1
stat_description: address_id
*************************** 2. row ***************************
database_name: cs
table_name: address
index_name: PRIMARY
last_update: 2021-06-03 16:17:22
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 3. row ***************************
database_name: cs
table_name: address
index_name: PRIMARY
last_update: 2021-06-03 16:17:22
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
3 rows in set (0.00 sec)
innodb_table_stats和innodb_index_stats是普通表，可以手动更新。手动更新统计资讯的能力使得在不修改资料库的情况下强制执行特定的查询优化计划或测试备选计划成为可能。如果您手动更新统计资讯，请执行FLUSH TABLE tbl_name命令让MySQL重新载入更新后的统计资讯。
永续性统计资讯被认为是本地资讯，因为它们与伺服器例项相关。因此，当自动统计资讯重新计算髮生时，innodb_table_stats和innodb_index_stats表不会被複制。如果您执行ANALYZE TABLE来启动统计资讯的同步重新计算，那幺这个语句将被複制(
除非您抑制了对它的日誌记录)，并在複製从伺服器上进行重新计算。
InnoDB持久化统计资讯表示例
innodb_table_stats表每个表包含一行。下面的例子演示了收集到的资料。
表t1包含一个主索引(列a、b)二级索引(列c、d)和唯一索引(列e、f):
mysql> CREATE TABLE t1 (
-> a INT, b INT, c INT, d INT, e INT, f INT,
-> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)
插入五行样本资料后，表如下所示:
mysql> insert into t1 values(1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);
Query OK, 5 rows affected (0.12 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 1 | 10 | 11 | 100 | 101 |
| 1 | 2 | 10 | 11 | 200 | 102 |
| 1 | 3 | 10 | 11 | 100 | 103 |
| 1 | 4 | 10 | 12 | 200 | 104 |
| 1 | 5 | 10 | 12 | 100 | 105 |
+---+---+------+------+------+------+
5 rows in set (0.00 sec)
要立即更新统计资讯，执行ANALYZE TABLE(如果启用了innodb_stats_auto_recalc，假设改变的錶行达到10%的阈值，统计资讯会在几秒钟内自动更新)
mysql> analyze table t1;
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| mysql.t1 | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> select * from innodb_table_stats where table_name='t1' and database_name='mysql';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| mysql | t1 | 2022-02-17 14:52:13 | 5 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.01 sec)
表t1的表统计资讯显示InnoDB最后更新表统计资讯的时间为(2022-02-17 14:52:13)，表中的行记录数为5，集簇索引大小为1个索引页，其它索引大小为2个索引页。
mysql> select * from innodb_table_stats where table_name='t1' and database_name='mysql' \G
*************************** 1. row ***************************
database_name: mysql
table_name: t1
last_update: 2022-02-17 14:52:13
n_rows: 5
clustered_index_size: 1
sum_of_other_index_sizes: 2
1 row in set (0.01 sec)
innodb_index_stats表包含每个索引的多行。innodb_index_stats表中的每一行都提供了与特定索引统计相关的资料，在stat_name列中显示命名，在stat_description列中显示描述。例如:
mysql> select * from innodb_index_stats where table_name='t1' and database_name='mysql';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | n_diff_pfx01 | 1 | 1 | a |
| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | n_diff_pfx02 | 5 | 1 | a,b |
| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | size | 1 | NULL | Number of pages in the index |
| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx01 | 1 | 1 | c |
| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx02 | 2 | 1 | c,d |
| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx03 | 2 | 1 | c,d,a |
| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx04 | 5 | 1 | c,d,a,b |
| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t1 | i1 | 2022-02-17 14:52:13 | size | 1 | NULL | Number of pages in the index |
| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | n_diff_pfx01 | 2 | 1 | e |
| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | n_diff_pfx02 | 5 | 1 | e,f |
| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
14 rows in set (0.01 sec)
stat_name列显示了以下型别的统计资讯:
.size: 当tat_name=size时, stat_value列显示索引中的总页数。
.n_leaf_pages: 当stat_name=n_leaf_pages时, stat_value列显示索引中叶页的数量。
.n_diff_pfxNN: 当stat_name=n_diff_pfx01时，stat_value列显示索引中第一列的distinct值的数量。当stat_name=n_diff_pfx02时，stat_value列显示索引中前两列的distinct值的数量。另外，stat_name=n_diff_pfxNN，stat_description列显示了被计数的索引列的逗号分隔列表。
为了进一步说明n_diff_pfxNN统计资料所提供的基数资料，考虑t1表示例。如下所示，用一个主索引(列a、b)、一个辅助索引(列c、d)和一个唯一索引(列e、f)建立了t1表。
mysql> CREATE TABLE t1 (
-> a INT, b INT, c INT, d INT, e INT, f INT,
-> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)
插入五行样本资料后，表如下所示:
mysql> insert into t1 values(1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);
Query OK, 5 rows affected (0.12 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 1 | 10 | 11 | 100 | 101 |
| 1 | 2 | 10 | 11 | 200 | 102 |
| 1 | 3 | 10 | 11 | 100 | 103 |
| 1 | 4 | 10 | 12 | 200 | 104 |
| 1 | 5 | 10 | 12 | 100 | 105 |
+---+---+------+------+------+------+
5 rows in set (0.00 sec)
当查询index_name,stat_name,stat_value和stat_description且where条件为stat_name like 'n_diff%'，返回结果如下:
mysql> select index_name,stat_name,stat_value,stat_description from innodb_index_stats where table_name='t1' and
-> database_name='mysql' and stat_name like 'n_diff%';
+------------+--------------+------------+------------------+
| index_name | stat_name | stat_value | stat_description |
+------------+--------------+------------+------------------+
| PRIMARY | n_diff_pfx01 | 1 | a |
| PRIMARY | n_diff_pfx02 | 5 | a,b |
| i1 | n_diff_pfx01 | 1 | c |
| i1 | n_diff_pfx02 | 2 | c,d |
| i1 | n_diff_pfx03 | 2 | c,d,a |
| i1 | n_diff_pfx04 | 5 | c,d,a,b |
| i2uniq | n_diff_pfx01 | 2 | e |
| i2uniq | n_diff_pfx02 | 5 | e,f |
+------------+--------------+------------+------------------+
8 rows in set (0.00 sec)
对于primary索引，这里有两个n_diff%行。行数等于索引中的列数。
注意：对于非唯一索引 ，InnoDB会附加主键索引的列到非唯一索引中。
.当index_name=PRIMARY和stat_name=n_diff_pfx01时,stat_value值为1，这说明索引中第一个列(a)包含一个distinct值。列a的distinct值可以通过检视錶t1中的列a的值来进行确认,只有单个distinct值1。计数列(a)显示在结果集的stat_description列中。
.当index_name=PRIMAY和stat_name=n_diff_pfx02时，stat_value值为5，这说明索引中前两列包含五个distinct值。列a和b的distinct值可以通过检视錶t1中的列a和b的值来进行确认，有五个distinct值(1,1),(1,2),(1,3),(1,4),(1,5)。计数列(a，)显示在结果集的stat_description列中。
对于二级索引(i1)，有4个n_diff%行。二级索引只定义了两个列(c,d)，但是二级索引有四个n_diff%行，因为InnoDB将所有非唯一的索引都以主键作为字尾。因此，二级索引列(c,d)和主键列(a,b)有4个n_diff%行，而不是2个。
.当index_name=i1和stat_name=n_diff_pfx01时，stat_value的值为1,这说明索引中第一列(c）包含一个distinct值。列c的distinct值可以通过检视錶t1中的列c的资料来进行确认。计数列c在stat_description列中显示。
.当index_name=i1和stat_name=n_diff_pfx02时，stat_value的值为2，这说明索引中前两列(c,d)包含两个distinct值。列c和d的distinct值可以通过检视錶t1中的列c和d的资料来进行确认。计数列(c,d)在stat_description列中显示
.当index_name=i1和stat_name=n_diff_pfx03，stat_value的值为2，这说明索引中前三列(c,d,a)包含两个distinct值。列c,d和a的distinct值可以通过检视錶t1中列c,d和a的资料来进行确认，有两个distinct值(10,11,1)和(10,12,1)。计数列(c,d,a)在stat_desciption列中显示
.当index_name=i1和stat_name=n_diff_pfx04，stat_value的值为5，这说明索引中四列(c,d,a,b)包含五个distinct值。列c,d,a和b的distinct值可以通过检视錶t1中列c,d,a和b的资料来进行确认，有五个distinct值(10,11,1,1)和(10,11,1,2)，（10,11,1,3),(10,12,1,4)和(10,12,1,5)。计数列(c,d,a,b)在stat_description列中显示
对于唯一索引(i2uniq)，有两个n_diff%行。
.当index_name=i2uniq和stat_name=n_diff_pfx01时，stat_value值为2，这说明索引中第一列(e)包含两个distinct值。列e的distinct值可以通过检视錶t1的列e的资料来进行确认，有两个distinct值(100)和(200)。计数列e在stat_description列中显示。
.当index_name=i2uniq和stat_name=n_diff_pfx02时，stat_value值为5，这说明索引中两列(e,f)包含五个distinct值。列e和f的distinct值可以通过检视錶t1的列e和f的资料来进行确认，有五个distinct值(100,101),(200,102),(100,103),(200,104)和(100,105)。计数列(e,f)在stat_description列中显示。
使用innodb_index_stats表获取索引大小
表、分割槽或子分割槽的索引大小可以使用innodb_index_stats表来检索。在下面的例子中，检索表t1的索引大小。
mysql> select sum(stat_value) pages,index_name,sum(stat_value)*@@innodb_page_size size
-> from mysql.innodb_index_stats where table_name='t1' and database_name='mysql' and stat_name='size' group by index_name;
+-------+------------+-------+
| pages | index_name | size |
+-------+------------+-------+
| 1 | PRIMARY | 16384 |
| 1 | i1 | 16384 |
| 1 | i2uniq | 16384 |
+-------+------------+-------+
3 rows in set (0.04 sec)
mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)
对于分割槽或子分割槽，可以使用带有修改后的WHERE子句的相同查询来检索索引大小。例如，下面的查询检索表t1的分割槽的索引大小
mysql> select sum(stat_value) pages,index_name,sum(stat_value)*@@innodb_page_size size
-> from mysql.innodb_index_stats where table_name like't1#P%' and database_name='mysql' and stat_name='size' group by index_name;
Empty set (0.01 sec)
很赞哦！ ()
微信收款码
支付宝收款码