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所分析的分割槽數量。
當開啟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所分析的分割槽數量。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2119478/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQl 配置InnoDB持久化的優化器統計資訊MySql持久化優化
- MySQL 配置InnoDB配置非持久優化器統計資訊引數MySql優化
- [轉]MySQL 5.6 my.cnf配置優化MySql優化
- Percona MySQL 5.6 HINT介紹MySql
- MySQL中Innodb如何計算索引的統計資訊？MySql索引
- 【MySQL】5.6.x InnoDB Error Table mysql.innodb_table_stats not foundMySqlError
- percona server5.6跟5.5的差異統計Server
- MySQL最優配置模板（ 5.6&5.7轉）MySql
- Mysql優化系列（1）--Innodb重要引數優化MySql優化
- mysql伺服器和配置優化MySql伺服器優化
- MySQL InnoDB行鎖優化建議MySql優化
- 【STATS】Oracle匯入匯出優化器統計資訊Oracle優化
- [MySQL5.6]Innodb新的監控表INNODB_METRICSMySql
- Percona MySQL 5.6 RPM包方式安裝流程MySql
- 【DBMS_STATUS】Oracle控制優化器統計資訊的使用Oracle優化
- mysql 5.6效能監控表innodb_metricsMySql
- MySQL 5.6 遭遇 OS bug INNODB MONITOR OUTPUT 事件MySql事件
- 《MySQL 效能優化》之 InnoDB 儲存引擎MySql優化儲存引擎
- MySQL優化-安裝配置優化MySql優化
- MySQL 5.6，5.7的優化器對於count(*）的處理方式MySql優化
- MySQL5.6之use_index_extensions優化MySqlIndex優化
- Percona MySQL 5.6 WHERE 條件中 OR 的索引測試MySql索引
- SQL優化之統計資訊和索引SQL優化索引
- MySQL MyISAM/InnoDB高併發優化經驗MySql優化
- mysql子查詢的缺陷以及5.6的優化MySql優化
- MySQL InnoDB儲存引擎更新Cardinality統計資訊的策略介紹MySql儲存引擎
- TiDB 優化器實現的基礎：統計資訊的收集TiDB優化
- MySQL 統計資訊MySql
- MySQL 5.6 InnoDB儲存引擎體系結構圖MySql儲存引擎
- 管好統計資訊，開啟SQL優化之門SQL優化
- MySQL引數配置優化MySql優化
- MySQL 優化六（InnoDB 下 update 資料出現表鎖之優化）MySql優化
- mysql 優化：使用show status檢視MySQL伺服器狀態資訊MySql優化伺服器
- percona之資料庫資訊統計工具介紹資料庫
- (mysql優化-3) 系統優化MySql優化
- MySQL InnoDB記憶體配置MySql記憶體
- MySQL 配置InnoDB清理排程MySql
- MySQL InnoDB頁面大小配置MySql