您好,登錄后才能下訂單哦!
這篇文章主要介紹MySQL統(tǒng)計信息的存儲有哪幾種,文中介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們一定要看完!
MySQL統(tǒng)計信息的存儲分為兩種,非持久化和持久化統(tǒng)計信息。
非持久化統(tǒng)計信息存儲在內(nèi)存里,如果數(shù)據(jù)庫重啟,統(tǒng)計信息將丟失。有兩種方式可以設(shè)置為非持久化統(tǒng)計信息:
1 全局變量, INNODB_STATS_PERSISTENT=OFF |
2 CREATE/ALTER表的參數(shù), STATS_PERSISTENT=0 |
非持久化統(tǒng)計信息在以下情況會被自動更新:
1 執(zhí)行ANALYZE TABLE |
2 innodb_stats_on_metadata=ON情況下,執(zhí)SHOW TABLE STATUS, SHOW INDEX, 查詢 INFORMATION_SCHEMA下的TABLES, STATISTICS |
3 啟用--auto-rehash功能情況下,使用mysql client登錄 |
4 表第一次被打開 |
5 距上一次更新統(tǒng)計信息,表1/16的數(shù)據(jù)被修改 |
非持久化統(tǒng)計信息的缺點顯而易見,數(shù)據(jù)庫重啟后如果大量表開始更新統(tǒng)計信息,會對實例造成很大影響,所以目前都會使用持久化統(tǒng)計信息。
5.6.6開始,MySQL默認使用了持久化統(tǒng)計信息,即INNODB_STATS_PERSISTENT=ON,持久化統(tǒng)計信息保存在表mysql.innodb_table_stats和mysql.innodb_index_stats。
持久化統(tǒng)計信息在以下情況會被自動更新:
1 INNODB_STATS_AUTO_RECALC=ON 情況下,表中10%的數(shù)據(jù)被修改 |
2 增加新的索引 |
innodb_table_stats是表的統(tǒng)計信息,innodb_index_stats是索引的統(tǒng)計信息,各字段含義如下:
innodb_table_stats | |
database_name | 數(shù)據(jù)庫名 |
table_name | 表名 |
last_update | 統(tǒng)計信息最后一次更新時間 |
n_rows | 表的行數(shù) |
clustered_index_size | 聚集索引的頁的數(shù)量 |
sum_of_other_index_sizes | 其他索引的頁的數(shù)量 |
innodb_index_stats | |
database_name | 數(shù)據(jù)庫名 |
table_name | 表名 |
index_name | 索引名 |
last_update | 統(tǒng)計信息最后一次更新時間 |
stat_name | 統(tǒng)計信息名 |
stat_value | 統(tǒng)計信息的值 |
sample_size | 采樣大小 |
stat_description | 類型說明 |
為更好的理解innodb_index_stats,建一張測試表做說明:
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;
寫入數(shù)據(jù)如下:
查看t1表的統(tǒng)計信息,需主要關(guān)注stat_name和stat_value字段
tat_name=size時:stat_value表示索引的頁的數(shù)量
stat_name=n_leaf_pages時:stat_value表示葉子節(jié)點的數(shù)量
stat_name=n_diff_pfxNN時:stat_value表示索引字段上唯一值的數(shù)量,此處做一下具體說明:
1、n_diff_pfx01表示索引第一列distinct之后的數(shù)量,如PRIMARY的a列,只有一個值1,所以index_name='PRIMARY' and stat_name='n_diff_pfx01'時,stat_value=1。
2、n_diff_pfx02表示索引前兩列distinct之后的數(shù)量,如i2uniq的e,f列,有4個值,所以index_name='i2uniq' and stat_name='n_diff_pfx02'時,stat_value=4。
3、對于非唯一索引,會在原有列之后加上主鍵索引,如index_name='i1' and stat_name='n_diff_pfx03',在原索引列c,d后加了主鍵列a,(c,d,a)的distinct結(jié)果為2。
了解了stat_name和stat_value的具體含義,就可以協(xié)助我們排查SQL執(zhí)行時為什么沒有使用合適的索引,例如某個索引n_diff_pfxNN的stat_value遠小于實際值,查詢優(yōu)化器認為該索引選擇度較差,就有可能導(dǎo)致使用錯誤的索引。
我們查看執(zhí)行計劃,發(fā)現(xiàn)未使用正確的索引,如果是innodb_index_stats中統(tǒng)計信息差別較大引起,可通過以下方式處理:
1、手動更新統(tǒng)計信息,注意執(zhí)行過程中會加讀鎖:
ANALYZETABLE TABLE_NAME;
2、如果更新后統(tǒng)計信息仍不準確,可考慮增加表采樣的數(shù)據(jù)頁,兩種方式可以修改:
a) 全局變量INNODB_STATS_PERSISTENT_SAMPLE_PAGES,默認為20;
b) 單個表可以指定該表的采樣:
ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;
經(jīng)測試,此處STATS_SAMPLE_PAGES的最大值是65535,超出會報錯。
目前MySQL并沒有提供直方圖的功能,某些情況下(如數(shù)據(jù)分布不均)僅僅更新統(tǒng)計信息不一定能得到準確的執(zhí)行計劃,只能通過index hint的方式指定索引。新版本8.0會增加直方圖功能,讓我們期待MySQL越來越強大的功能吧!
以上是“MySQL統(tǒng)計信息的存儲有哪幾種”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對大家有幫助,更多相關(guān)知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。