MySQl 配置InnoDB持久化的優化器統計資訊
配置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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2871746/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 配置InnoDB配置非持久優化器統計資訊引數MySql優化
- Percona MySQL 5.6 配置InnoDB優化器永久統計資訊MySql優化
- Mysql優化系列（1）--Innodb重要引數優化MySql優化
- mysql伺服器和配置優化MySql伺服器優化
- MySQL優化-安裝配置優化MySql優化
- MySQL InnoDB行鎖優化建議MySql優化
- 【DBMS_STATUS】Oracle控制優化器統計資訊的使用Oracle優化
- MySQL 持久化系統變數MySql持久化變數
- 【STATS】Oracle匯入匯出優化器統計資訊Oracle優化
- MySQL中Innodb如何計算索引的統計資訊？MySql索引
- 《MySQL 效能優化》之 InnoDB 儲存引擎MySql優化儲存引擎
- (mysql優化-3) 系統優化MySql優化
- TiDB 優化器實現的基礎：統計資訊的收集TiDB優化
- SQL優化之統計資訊和索引SQL優化索引
- MySQL MyISAM/InnoDB高併發優化經驗MySql優化
- MySQL引數配置優化MySql優化
- MySQL 優化六（InnoDB 下 update 資料出現表鎖之優化）MySql優化
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- Redis持久化及其配置Redis持久化
- Java emoji持久化mysqlJava持久化MySql
- ActiveMQ-mysql持久化MQMySql持久化
- 管好統計資訊，開啟SQL優化之門SQL優化
- MySQL優化之系統變數優化MySql優化變數
- 淺談mysql配置優化和sql語句優化MySql優化
- redis之 Redis持久化配置Redis持久化
- Docker下Nacos持久化配置Docker持久化
- Redis的持久化設計Redis持久化
- Myisam & InnoDB 優化引數優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- mysql 優化：使用show status檢視MySQL伺服器狀態資訊MySql優化伺服器
- Oracle優化案例-擴充套件統計資訊（十四）Oracle優化套件
- Mysql資料庫優化系列（一）------Mysql伺服器優化思路MySql資料庫優化伺服器
- MySQL設計與優化MySql優化
- Oracle 12c資料庫優化器統計資訊收集的最佳實踐Oracle資料庫優化
- CUDA優化之執行配置和暫存器優化優化
- Redis的兩種持久化方式-快照持久化（RDB）和AOF持久化Redis持久化
- mysql的優化MySql優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化