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