Percona MySQL 5.6如何配置InnoDB優化器永久統計信息

這篇文章主要介紹Percona MySQL 5.6如何配置InnoDB優化器永久統計信息，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

優化器永久統計信息通過把統計信息保存在磁盤上，使得MySQL在選擇語句的執行計劃時，會選擇相對一致的執行計劃，提升了SQL執行計劃的穩定性。
當開啟innodb_stats_persistent=ON這個參數時或在建表時帶了STATS_PERSISTENT=1參數，優化器的統計信息會永久保存到磁盤上。在之前的版本，每當MySQL服務重啟或執行某些特定操作時，優化器的統計信息會被清除。
在表下一次被訪問時，MySQL會重新收集優化器統計信息，這樣會導致統計信息的改變，從而導致MySQL在解析語句時執行計劃的改變，進而影響查詢性能。
優化器永久統計信息保存在mysql.innodb_table_stats和mysql.innodb_index_stats這兩張表中。

mysql> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 5.6.31-77.0-log |
+-----------------+
1 row in set (0.01 sec)

mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> desc mysql.innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                    | Type                | Null | Key | Default           | Extra                       |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name            | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name               | varchar(64)         | 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> select * from mysql.innodb_table_stats;
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| fire          | t1            | 2016-06-11 23:12:34 |  392945 |                  801 |                      481 |
| fire          | t2            | 2016-06-11 23:15:12 | 2080004 |                 4070 |                     2341 |
| fire          | test          | 2016-06-09 01:23:06 |       0 |                    1 |                        0 |
| mysql         | gtid_executed | 2016-06-07 01:28:28 |       0 |                    1 |                        0 |
| sys           | sys_config    | 2016-06-07 01:28:30 |       2 |                    1 |                        0 |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
5 rows in set (0.08 sec)

mysql> desc mysql.innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(64)         | 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)
mysql> select * from mysql.innodb_index_stats;
+---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name    | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| fire          | t1            | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | n_diff_pfx01 |     392945 |          20 | DB_ROW_ID                         |
| fire          | t1            | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | n_leaf_pages |        763 |        NULL | Number of leaf pages in the index |
| fire          | t1            | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | size         |        801 |        NULL | Number of pages in the index      |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | n_diff_pfx01 |          2 |           4 | a                                 |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | n_diff_pfx02 |     395866 |          20 | a,DB_ROW_ID                       |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | n_leaf_pages |        403 |        NULL | Number of leaf pages in the index |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | size         |        481 |        NULL | Number of pages in the index      |
| fire          | t2            | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | n_diff_pfx01 |    2079570 |          20 | DB_ROW_ID                         |
| fire          | t2            | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | n_leaf_pages |       4038 |        NULL | Number of leaf pages in the index |
| fire          | t2            | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | size         |       4070 |        NULL | Number of pages in the index      |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | n_diff_pfx01 |          3 |           5 | a                                 |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | n_diff_pfx02 |    2084334 |          20 | a,DB_ROW_ID                       |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | n_leaf_pages |       2122 |        NULL | Number of leaf pages in the index |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | size         |       2341 |        NULL | Number of pages in the index      |
| fire          | test          | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| fire          | test          | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| fire          | test          | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | n_diff_pfx01 |          0 |           1 | source_uuid                       |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | n_diff_pfx02 |          0 |           1 | source_uuid,interval_start        |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | size         |          1 |        NULL | Number of pages in the index      |
| sys           | sys_config    | PRIMARY         | 2016-06-07 01:28:30 | n_diff_pfx01 |          2 |           1 | variable                          |
| sys           | sys_config    | PRIMARY         | 2016-06-07 01:28:30 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| sys           | sys_config    | PRIMARY         | 2016-06-07 01:28:30 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
24 rows in set (0.00 sec)

--配置優化器永久統計信息的自動收集
當表中條目發生改變時（10%以上的行發生改變），innodb_stats_auto_recalc參數決定是否重新收集統計信息。這個參數默認是開啟的。可以在CREATE TABLE、ALTER TABLE語句上面添加STATS_AUTO_RECALC選項來開啟指定表的統計信息自動收集。
統計信息的自動收集是在后臺以異步的方式進行的。當對一張表執行了影響表中10%行數的DML操作，在innodb_stats_auto_recalc參數開啟的情況下，統計信息可能不會立刻開始重新收集，這個收集可能會延遲幾十秒。如果需要最新的統計信息，可以執行ANALYZE TABLE語句，在前臺統計收集統計信息。
mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.21 sec)

如果innodb_stats_auto_recalc參數沒有開啟時，在表中索引字段數據發生大的改變時，例如表中被導入大量數據，或表有階段性的大改變索引字段的DML操作，需要及時執行ANALYZE TABLE語句，來保證優化器統計信息的準確性。當在一張已存在的表上創建索引時，不管是否開啟innodb_stats_auto_recalc參數，索引的統計信息會自動收集并保存在innodb_index_stats表中。

--配置優化器統計信息Sampled Pages的數量
在執行計劃中，MySQL查詢優化器根據索引的selectivity，使用索引分布統計信息來選擇使用的索引。當執行ANALYZE TABLE操作時，InnoDB會對每個索引進行采樣來估算cardinality（某字段非重復值的數量），這個技術被稱為random dives。可以通過innodb_stats_persistent_sample_pages參數來改變采樣使用的頁數，這個參數的默認值是20。當發生下面情況時，可以考慮修改這個參數：

1、在EXPLAIN輸出中，統計信息不準確，優化器選擇了非最優的執行計劃。可以通過比較SELECT DISTINCT索引字段和mysql.innodb_index_stats表中的索引的cardinality，來查看索引實際的cardinality的準確性。
如果統計信息不準確，應該增加innodb_stats_persistent_sample_pages這個參數的值，直到統計信息足夠準確為止。如果將這個參數的值增加太大，會導致ANALYZE TABLE操作運行緩慢。

2、ANALYZE TABLE操作太慢。這時可以考慮減小innodb_stats_persistent_sample_pages這個參數的值，直到ANALYZE TABLE的執行時間能在一個接受的范圍內。然而，將這個參數的值設的太小，可能會導致統計信息的不準確，進而影響執行計劃的優劣。

3、如果在統計信息的準確性和ANALYZE TABLE執行時間之間不能取得平衡，考慮減少表中索引字段的數量或減少ANALYZE TABLE所分析的分區數量。

以上是“Percona MySQL 5.6如何配置InnoDB優化器永久統計信息”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！