溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹Percona MySQL 5.6如何配置InnoDB優(yōu)化器永久統(tǒng)計信息，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
優(yōu)化器永久統(tǒng)計信息通過把統(tǒng)計信息保存在磁盤上，使得MySQL在選擇語句的執(zhí)行計劃時，會選擇相對一致的執(zhí)行計劃，提升了SQL執(zhí)行計劃的穩(wěn)定性。
當開啟innodb_stats_persistent=ON這個參數(shù)時或在建表時帶了STATS_PERSISTENT=1參數(shù)，優(yōu)化器的統(tǒng)計信息會永久保存到磁盤上。在之前的版本，每當MySQL服務重啟或執(zhí)行某些特定操作時，優(yōu)化器的統(tǒng)計信息會被清除。
在表下一次被訪問時，MySQL會重新收集優(yōu)化器統(tǒng)計信息，這樣會導致統(tǒng)計信息的改變，從而導致MySQL在解析語句時執(zhí)行計劃的改變，進而影響查詢性能。
優(yōu)化器永久統(tǒng)計信息保存在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)
--配置優(yōu)化器永久統(tǒng)計信息的自動收集
當表中條目發(fā)生改變時（10%以上的行發(fā)生改變），innodb_stats_auto_recalc參數(shù)決定是否重新收集統(tǒng)計信息。這個參數(shù)默認是開啟的。可以在CREATE TABLE、ALTER TABLE語句上面添加STATS_AUTO_RECALC選項來開啟指定表的統(tǒng)計信息自動收集。
統(tǒng)計信息的自動收集是在后臺以異步的方式進行的。當對一張表執(zhí)行了影響表中10%行數(shù)的DML操作，在innodb_stats_auto_recalc參數(shù)開啟的情況下，統(tǒng)計信息可能不會立刻開始重新收集，這個收集可能會延遲幾十秒。如果需要最新的統(tǒng)計信息，可以執(zhí)行ANALYZE TABLE語句，在前臺統(tǒng)計收集統(tǒng)計信息。
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參數(shù)沒有開啟時，在表中索引字段數(shù)據(jù)發(fā)生大的改變時，例如表中被導入大量數(shù)據(jù)，或表有階段性的大改變索引字段的DML操作，需要及時執(zhí)行ANALYZE TABLE語句，來保證優(yōu)化器統(tǒng)計信息的準確性。當在一張已存在的表上創(chuàng)建索引時，不管是否開啟innodb_stats_auto_recalc參數(shù)，索引的統(tǒng)計信息會自動收集并保存在innodb_index_stats表中。
--配置優(yōu)化器統(tǒng)計信息Sampled Pages的數(shù)量
在執(zhí)行計劃中，MySQL查詢優(yōu)化器根據(jù)索引的selectivity，使用索引分布統(tǒng)計信息來選擇使用的索引。當執(zhí)行ANALYZE TABLE操作時，InnoDB會對每個索引進行采樣來估算cardinality（某字段非重復值的數(shù)量），這個技術被稱為random dives?？梢酝ㄟ^innodb_stats_persistent_sample_pages參數(shù)來改變采樣使用的頁數(shù)，這個參數(shù)的默認值是20。當發(fā)生下面情況時，可以考慮修改這個參數(shù)：
1、在EXPLAIN輸出中，統(tǒng)計信息不準確，優(yōu)化器選擇了非最優(yōu)的執(zhí)行計劃?？梢酝ㄟ^比較SELECT DISTINCT索引字段和mysql.innodb_index_stats表中的索引的cardinality，來查看索引實際的cardinality的準確性。
如果統(tǒng)計信息不準確，應該增加innodb_stats_persistent_sample_pages這個參數(shù)的值，直到統(tǒng)計信息足夠準確為止。如果將這個參數(shù)的值增加太大，會導致ANALYZE TABLE操作運行緩慢。
2、ANALYZE TABLE操作太慢。這時可以考慮減小innodb_stats_persistent_sample_pages這個參數(shù)的值，直到ANALYZE TABLE的執(zhí)行時間能在一個接受的范圍內(nèi)。然而，將這個參數(shù)的值設的太小，可能會導致統(tǒng)計信息的不準確，進而影響執(zhí)行計劃的優(yōu)劣。
3、如果在統(tǒng)計信息的準確性和ANALYZE TABLE執(zhí)行時間之間不能取得平衡，考慮減少表中索引字段的數(shù)量或減少ANALYZE TABLE所分析的分區(qū)數(shù)量。
以上是“Percona MySQL 5.6如何配置InnoDB優(yōu)化器永久統(tǒng)計信息”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關知識，歡迎關注億速云行業(yè)資訊頻道！
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內(nèi)容。
