MySQL優(yōu)化器統(tǒng)計(jì)信息的配置指南
這段內(nèi)容出自 MySQL 官方文檔，主題是 “配置非持久性優(yōu)化器統(tǒng)計(jì)信息參數(shù)”（Non-Persistent Optimizer Statistics Parameters），屬于 InnoDB 存儲(chǔ)引擎的性能調(diào)優(yōu)部分。下面我將逐段為你詳細(xì)解釋這段內(nèi)容的含義，并用通俗易懂的語言幫助你理解其背后的原理和實(shí)際影響。
核心概念：什么是“優(yōu)化器統(tǒng)計(jì)信息”？
在 MySQL 中，查詢優(yōu)化器（Query Optimizer） 負(fù)責(zé)決定執(zhí)行 SQL 語句的最佳方式，比如是否使用某個(gè)索引、用哪個(gè)索引、是否進(jìn)行全表掃描等。
為了做出這些決策，優(yōu)化器需要知道一些關(guān)于表和索引的數(shù)據(jù)分布情況，例如：
- 一張表有多少行？
- 某個(gè)索引有多少不同的值？（即“基數(shù)”，Cardinality）
- 索引的選擇性如何？
這些數(shù)據(jù)被稱為 “優(yōu)化器統(tǒng)計(jì)信息”（Optimizer Statistics）。
什么是“非持久性”統(tǒng)計(jì)信息？
MySQL 提供兩種方式來存儲(chǔ)這些統(tǒng)計(jì)信息：
| 類型 | 是否保存到磁盤 | 特點(diǎn) |
|---|---|---|
| 持久性（Persistent） | ? 是 | 統(tǒng)計(jì)信息寫入磁盤，重啟后不丟失 |
| 非持久性（Non-Persistent） | ? 否 | 統(tǒng)計(jì)信息只存在內(nèi)存中，重啟后丟失 |
默認(rèn)情況下，innodb_stats_persistent = ON，也就是啟用持久性統(tǒng)計(jì)信息。
但如果你設(shè)置：
SET GLOBAL innodb_stats_persistent = OFF;
或者在創(chuàng)建/修改表時(shí)指定：
CREATE TABLE t (...) STATS_PERSISTENT=0; ALTER TABLE t STATS_PERSISTENT=0;
那么這張表的統(tǒng)計(jì)信息就是 非持久性的 —— 只存在內(nèi)存中，MySQL 重啟后就會(huì)丟失，下次啟動(dòng)時(shí)需要重新采樣生成。
非持久性統(tǒng)計(jì)信息何時(shí)更新？
當(dāng)統(tǒng)計(jì)信息是非持久性時(shí)，它們會(huì)在以下幾種情況下被自動(dòng)更新（重新計(jì)算）：
1. 執(zhí)行 ANALYZE TABLE
ANALYZE TABLE my_table;
這是最直接的方式，手動(dòng)觸發(fā)統(tǒng)計(jì)信息更新。
2. 查詢?cè)獢?shù)據(jù)（如 SHOW TABLE STATUS, SHOW INDEX） + 開啟了 innodb_stats_on_metadata=ON
默認(rèn)這個(gè)選項(xiàng)是 OFF，但如果開啟：
SET GLOBAL innodb_stats_on_metadata = ON;
那么每次執(zhí)行：
SHOW TABLE STATUSSHOW INDEX- 查詢
information_schema.TABLES或STATISTICS表
都會(huì)導(dǎo)致 InnoDB 重新統(tǒng)計(jì)表的索引信息！
注意：這可能導(dǎo)致性能問題！
- 如果你的庫有很多表或索引，這類操作會(huì)變慢。
- 執(zhí)行計(jì)劃可能不穩(wěn)定（因?yàn)槊看尾樵獢?shù)據(jù)都可能改變統(tǒng)計(jì)值，進(jìn)而改變執(zhí)行計(jì)劃）。
所以建議：生產(chǎn)環(huán)境不要開啟 innodb_stats_on_metadata。
3. 使用 mysql 客戶端并啟用 --auto-rehash（默認(rèn)行為）
當(dāng)你運(yùn)行：
mysql -u user -p
默認(rèn)啟用了 --auto-rehash，它支持命令行自動(dòng)補(bǔ)全數(shù)據(jù)庫名、表名、列名。
但它的工作原理是：打開所有 InnoDB 表 → 觸發(fā)統(tǒng)計(jì)信息更新！
后果：客戶端啟動(dòng)變慢，尤其在大庫上。
解決辦法：關(guān)閉 auto-rehash
mysql --disable-auto-rehash -u user -p
4. 第一次打開一張表
當(dāng)某張表第一次被訪問（打開）時(shí)，InnoDB 會(huì)檢查是否需要更新統(tǒng)計(jì)信息。
5. 表數(shù)據(jù)變化超過閾值（1/16 ≈ 6.25%）
如果自上次統(tǒng)計(jì)以來，表中有 超過 1/16 的數(shù)據(jù)被修改（插入、刪除、更新），InnoDB 會(huì)認(rèn)為統(tǒng)計(jì)信息過期，下次打開表時(shí)自動(dòng)重新采樣更新。
這是一個(gè)啟發(fā)式規(guī)則，防止執(zhí)行計(jì)劃基于過時(shí)的統(tǒng)計(jì)信息做出錯(cuò)誤選擇。
如何控制統(tǒng)計(jì)信息的準(zhǔn)確性？—— innodb_stats_transient_sample_pages
由于統(tǒng)計(jì)信息是通過 采樣 得來的（稱為 random dives：隨機(jī)讀取若干頁），所以它的準(zhǔn)確性取決于采樣量。
參數(shù)說明：
- 參數(shù)名：
innodb_stats_transient_sample_pages - 作用范圍：全局（GLOBAL）
- 適用場(chǎng)景：僅當(dāng)
innodb_stats_persistent = OFF時(shí)有效（非持久性統(tǒng)計(jì)） - 默認(rèn)值：8 頁
- 可調(diào)范圍：一般建議 8~100，太高會(huì)影響性能
工作原理：
InnoDB 從每個(gè)索引中隨機(jī)抽取若干個(gè)數(shù)據(jù)頁，分析其中的鍵值分布，估算出索引的“基數(shù)”（Cardinality）。
比如：
- 主鍵索引：每頁都抽一點(diǎn)，估算總行數(shù)。
- 普通索引：看有多少不同值，判斷選擇性。
設(shè)置方法：
SET GLOBAL innodb_stats_transient_sample_pages = 20;
調(diào)整采樣頁數(shù)的影響
| 設(shè)置值 | 優(yōu)點(diǎn) | 缺點(diǎn) |
|---|---|---|
| 太?。ㄈ?1~2） | 快，I/O 少 | 統(tǒng)計(jì)極不準(zhǔn)，可能導(dǎo)致優(yōu)化器選錯(cuò)索引，引發(fā)全表掃描 |
| 適中（如 8~20） | 平衡速度與精度 | 大表可能仍不夠準(zhǔn) |
| 太大（如 100+） | 更準(zhǔn)確 | 每次更新統(tǒng)計(jì)信息都要讀很多頁 → 打開表變慢，SHOW TABLE STATUS 變卡 |
特別提醒：
- 對(duì) 大表或頻繁用于 JOIN 的表，8 頁采樣很可能不夠！
- 不準(zhǔn)確的統(tǒng)計(jì) → 優(yōu)化器誤判索引有效性 → 導(dǎo)致 全表掃描（Full Table Scan） → 性能急劇下降。
最佳實(shí)踐建議
優(yōu)先使用持久性統(tǒng)計(jì)信息
SET GLOBAL innodb_stats_persistent = ON; -- 默認(rèn)已開啟
這樣統(tǒng)計(jì)信息保存在磁盤上，重啟不失效，更穩(wěn)定。
避免頻繁觸發(fā)統(tǒng)計(jì)更新
關(guān)閉 innodb_stats_on_metadata
SET GLOBAL innodb_stats_on_metadata = OFF;
- 客戶端連接時(shí)加
--disable-auto-rehash，提升連接速度。
合理設(shè)置采樣頁數(shù)
- 如果你確實(shí)使用非持久性統(tǒng)計(jì)（如舊版本 MySQL），且有大量大表：
SET GLOBAL innodb_stats_transient_sample_pages = 32; -- 或 64
- 測(cè)試不同值對(duì)統(tǒng)計(jì)準(zhǔn)確性和性能的影響，找到平衡點(diǎn)。
定期執(zhí)行 ANALYZE TABLE
尤其是在大批量導(dǎo)入/刪除數(shù)據(jù)之后，手動(dòng)更新統(tǒng)計(jì)信息，確保執(zhí)行計(jì)劃最優(yōu)。
不要臨時(shí)調(diào)大 sample_pages → 執(zhí)行 ANALYZE → 再調(diào)小
因?yàn)榻y(tǒng)計(jì)信息會(huì)在多種場(chǎng)景下自動(dòng)更新（不只是 ANALYZE），這樣做沒有意義，反而增加復(fù)雜度。
根據(jù)表大小調(diào)整策略
- 小表：8 頁足夠
- 大表：建議提高采樣頁數(shù)（如 32~64）
- 混合型數(shù)據(jù)庫：折中取值（如 20~32）
總結(jié)：一句話理解全文
當(dāng) MySQL 的 InnoDB 表使用非持久性統(tǒng)計(jì)信息時(shí)，統(tǒng)計(jì)結(jié)果只存在內(nèi)存中，重啟丟失；系統(tǒng)會(huì)在特定操作（如 ANALYZE TABLE、打開表、數(shù)據(jù)變更過多等）時(shí)自動(dòng)重新采樣；采樣的準(zhǔn)確度由 innodb_stats_transient_sample_pages 控制——太小不準(zhǔn)，太大影響性能，應(yīng)根據(jù)表的大小和業(yè)務(wù)需求權(quán)衡設(shè)置。
附加：常見問題解答
Q: 我應(yīng)該用持久性還是非持久性統(tǒng)計(jì)？
A: 推薦持久性（默認(rèn)）。更穩(wěn)定，適合生產(chǎn)環(huán)境。非持久性主要用于兼容老版本或特殊調(diào)試。
Q: 為什么我的 SHOW TABLE STATUS 很慢？
A: 可能是開啟了 innodb_stats_on_metadata=ON，導(dǎo)致每次都要重新統(tǒng)計(jì)。關(guān)閉它即可。
Q: 統(tǒng)計(jì)信息不準(zhǔn)會(huì)導(dǎo)致什么后果？
A: 查詢優(yōu)化器可能選擇錯(cuò)誤的執(zhí)行計(jì)劃，比如該用索引卻做了全表掃描，導(dǎo)致查詢極慢。
Q: 多久更新一次統(tǒng)計(jì)信息？
A: 自動(dòng)機(jī)制：當(dāng)數(shù)據(jù)變更超過約 6.25% 時(shí)，下次訪問表會(huì)觸發(fā)更新。也可以手動(dòng) ANALYZE TABLE。
如果你提供具體的 MySQL 版本和業(yè)務(wù)場(chǎng)景（比如有沒有大表、是否頻繁導(dǎo)入數(shù)據(jù)），我可以給出更精確的配置建議。
以上就是MySQL優(yōu)化器統(tǒng)計(jì)信息的配置指南的詳細(xì)內(nèi)容，更多關(guān)于MySQL優(yōu)化器統(tǒng)計(jì)信息配置的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了如何使用idea連接服務(wù)器上的mysql,具體步驟本文給大家介紹的非常詳細(xì),需要的朋友可以參考下2024-02-02
這篇文章主要介紹了mysql如何才能保證數(shù)據(jù)的一致性問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教<BR>2024-03-03
最新Navicat?15?for?MySQL破解+教程?正確破解步驟
Navicat?for?MySQL是一個(gè)針對(duì)MySQL數(shù)據(jù)庫而開發(fā)的第三方mysql管理工具，該軟件可以用于?MySQL?數(shù)據(jù)庫服務(wù)器版本?3.21?或以上的和?MariaDB?5.1?或以上，這篇文章主要介紹了最新Navicat?15?for?MySQL破解+教程?正確破解步驟,需要的朋友可以參考下2023-04-04
這篇文章主要介紹了mysql 判斷是否為子集的方法步驟，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
這篇文章給大家分享8個(gè)不得不說的MySQL陷阱，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧2018-03-03
這篇文章主要介紹了Windows7中配置安裝MySQL 5.6解壓縮版的方法以及安裝過程中遇到的問題及解決方法，這里推薦給有需要的小伙伴2014-12-12
Mysql實(shí)現(xiàn)導(dǎo)出表結(jié)構(gòu)和數(shù)據(jù)過程
文章主要內(nèi)容是關(guān)于如何導(dǎo)出和導(dǎo)入MySQL數(shù)據(jù)庫中的表結(jié)構(gòu)和數(shù)據(jù),包括導(dǎo)出指定表的結(jié)構(gòu)和數(shù)據(jù),以及如何在本地和遠(yuǎn)程服務(wù)器之間傳輸數(shù)據(jù),文章還提到在PHP中使用`mysql_connect`函數(shù)時(shí)的一些注意事項(xiàng)2025-12-12
這篇文章主要介紹了MySQL 行轉(zhuǎn)列詳情，MySQL 行轉(zhuǎn)列語句不難，具體的詳細(xì)資料，感興趣的小伙伴可以參考一下2022-01-01
這篇文章主要給大家介紹了關(guān)于MySQL按指定字符合并以及拆分的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2020-06-06
Slave memory leak and trigger oom-killer
這篇文章主要介紹了Slave memory leak and trigger oom-killer,需要的朋友可以參考下2016-07-07