配置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)