Percona MySQL 5.6如何配置InnoDB優(yōu)化器永久統(tǒng)計(jì)信息

182次閱讀

共計(jì) 8282 個(gè)字符，預(yù)計(jì)需要花費(fèi) 21 分鐘才能閱讀完成。

這篇文章主要介紹 Percona MySQL 5.6 如何配置 InnoDB 優(yōu)化器永久統(tǒng)計(jì)信息，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

優(yōu)化器永久統(tǒng)計(jì)信息通過(guò)把統(tǒng)計(jì)信息保存在磁盤(pán)上，使得 MySQL 在選擇語(yǔ)句的執(zhí)行計(jì)劃時(shí)，會(huì)選擇相對(duì)一致的執(zhí)行計(jì)劃，提升了 SQL 執(zhí)行計(jì)劃的穩(wěn)定性。
當(dāng)開(kāi)啟 innodb_stats_persistent=ON 這個(gè)參數(shù)時(shí)或在建表時(shí)帶了 STATS_PERSISTENT= 1 參數(shù)，優(yōu)化器的統(tǒng)計(jì)信息會(huì)永久保存到磁盤(pán)上。在之前的版本，每當(dāng) MySQL 服務(wù)重啟或執(zhí)行某些特定操作時(shí)，優(yōu)化器的統(tǒng)計(jì)信息會(huì)被清除。
在表下一次被訪問(wèn)時(shí)，MySQL 會(huì)重新收集優(yōu)化器統(tǒng)計(jì)信息，這樣會(huì)導(dǎo)致統(tǒng)計(jì)信息的改變，從而導(dǎo)致 MySQL 在解析語(yǔ)句時(shí)執(zhí)行計(jì)劃的改變，進(jìn)而影響查詢性能。
優(yōu)化器永久統(tǒng)計(jì)信息保存在 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)

– 配置優(yōu)化器永久統(tǒng)計(jì)信息的自動(dòng)收集
當(dāng)表中條目發(fā)生改變時(shí)（10% 以上的行發(fā)生改變），innodb_stats_auto_recalc 參數(shù)決定是否重新收集統(tǒng)計(jì)信息。這個(gè)參數(shù)默認(rèn)是開(kāi)啟的?？梢栽?CREATE TABLE、ALTER TABLE 語(yǔ)句上面添加 STATS_AUTO_RECALC 選項(xiàng)來(lái)開(kāi)啟指定表的統(tǒng)計(jì)信息自動(dòng)收集。
統(tǒng)計(jì)信息的自動(dòng)收集是在后臺(tái)以異步的方式進(jìn)行的。當(dāng)對(duì)一張表執(zhí)行了影響表中 10% 行數(shù)的 DML 操作，在 innodb_stats_auto_recalc 參數(shù)開(kāi)啟的情況下，統(tǒng)計(jì)信息可能不會(huì)立刻開(kāi)始重新收集，這個(gè)收集可能會(huì)延遲幾十秒。如果需要最新的統(tǒng)計(jì)信息，可以執(zhí)行 ANALYZE TABLE 語(yǔ)句，在前臺(tái)統(tǒng)計(jì)收集統(tǒng)計(jì)信息。
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ù)沒(méi)有開(kāi)啟時(shí)，在表中索引字段數(shù)據(jù)發(fā)生大的改變時(shí)，例如表中被導(dǎo)入大量數(shù)據(jù)，或表有階段性的大改變索引字段的 DML 操作，需要及時(shí)執(zhí)行 ANALYZE TABLE 語(yǔ)句，來(lái)保證優(yōu)化器統(tǒng)計(jì)信息的準(zhǔn)確性。當(dāng)在一張已存在的表上創(chuàng)建索引時(shí)，不管是否開(kāi)啟 innodb_stats_auto_recalc 參數(shù)，索引的統(tǒng)計(jì)信息會(huì)自動(dòng)收集并保存在 innodb_index_stats 表中。

– 配置優(yōu)化器統(tǒng)計(jì)信息 Sampled Pages 的數(shù)量
在執(zhí)行計(jì)劃中，MySQL 查詢優(yōu)化器根據(jù)索引的 selectivity，使用索引分布統(tǒng)計(jì)信息來(lái)選擇使用的索引。當(dāng)執(zhí)行 ANALYZE TABLE 操作時(shí)，InnoDB 會(huì)對(duì)每個(gè)索引進(jìn)行采樣來(lái)估算 cardinality（某字段非重復(fù)值的數(shù)量），這個(gè)技術(shù)被稱(chēng)為 random dives。可以通過(guò) innodb_stats_persistent_sample_pages 參數(shù)來(lái)改變采樣使用的頁(yè)數(shù)，這個(gè)參數(shù)的默認(rèn)值是 20。當(dāng)發(fā)生下面情況時(shí)，可以考慮修改這個(gè)參數(shù)：

1、在 EXPLAIN 輸出中，統(tǒng)計(jì)信息不準(zhǔn)確，優(yōu)化器選擇了非最優(yōu)的執(zhí)行計(jì)劃?？梢酝ㄟ^(guò)比較 SELECT DISTINCT 索引字段和 mysql.innodb_index_stats 表中的索引的 cardinality，來(lái)查看索引實(shí)際的 cardinality 的準(zhǔn)確性。
如果統(tǒng)計(jì)信息不準(zhǔn)確，應(yīng)該增加 innodb_stats_persistent_sample_pages 這個(gè)參數(shù)的值，直到統(tǒng)計(jì)信息足夠準(zhǔn)確為止。如果將這個(gè)參數(shù)的值增加太大，會(huì)導(dǎo)致 ANALYZE TABLE 操作運(yùn)行緩慢。

2、ANALYZE TABLE 操作太慢。這時(shí)可以考慮減小 innodb_stats_persistent_sample_pages 這個(gè)參數(shù)的值，直到 ANALYZE TABLE 的執(zhí)行時(shí)間能在一個(gè)接受的范圍內(nèi)。然而，將這個(gè)參數(shù)的值設(shè)的太小，可能會(huì)導(dǎo)致統(tǒng)計(jì)信息的不準(zhǔn)確，進(jìn)而影響執(zhí)行計(jì)劃的優(yōu)劣。

3、如果在統(tǒng)計(jì)信息的準(zhǔn)確性和 ANALYZE TABLE 執(zhí)行時(shí)間之間不能取得平衡，考慮減少表中索引字段的數(shù)量或減少 ANALYZE TABLE 所分析的分區(qū)數(shù)量。

以上是“Percona MySQL 5.6 如何配置 InnoDB 優(yōu)化器永久統(tǒng)計(jì)信息”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完