相關知識點: INNODB_STATS_PERSIST=ON或用STATS_PERSIST=1定義單個表時,優化器統計信息將持久化到磁碟。預設情況下,innodb_stats_persistent是啟用的。 持久統計信息存儲在mysql.innodb_table_stats和mysql.innodb ...
相關知識點:
INNODB_STATS_PERSIST=ON
或用STATS_PERSIST=1
定義單個表時,優化器統計信息將持久化到磁碟。預設情況下,innodb_stats_persistent
是啟用的。
持久統計信息存儲在mysql.innodb_table_stats
和mysql.innodb_index_stats
表中。
預設情況下啟用的innodb_stats_auto_recalc
變數控制當表中超過10%的行發生更改時是否自動計算統計信息。可以通過在創建或更改表時指定STATS_AUTO_RECALC
子句來為單個表配置自動統計數據重新計算。
由於在後臺進行的自動統計數據重新計算的非同步性質,即使啟用了innodb_stats_auto_recalc
,在運行影響表10%以上的DML操作後,也不會立即重新計算統計數據。在某些情況下,統計數據的重新計算可能會延遲幾秒鐘。如果立即需要最新的統計數據,運行ANALYZE TABLE以啟動統計數據的同步(前臺)重新計算。
如果禁用innodb_stats_auto_recalc
則可以通過在對索引列進行大量更改後執行ANALYZE TABLE語句來確保優化器統計信息的準確性。
當INNODB_STATS_PERSIST=OFF
或使用STATS_PERSIST=0
創建或更改單個表時,優化器統計信息不會持久化到磁碟。相反,統計信息存儲在記憶體中,當伺服器關閉時會丟失。統計數據也會通過某些操作和在某些條件下定期更新。
當向現有表中添加索引時,或者當添加或刪除列時,無論innodb_stats_auto_recalc
的值如何,都會計算索引統計信息並將其添加到innodb_index_stats
表中。
影響統計信息的五個參數
-
innodb_stats_persistent
:指定InnoDB索引統計信息是否持久化到磁碟,預設打開。 -
innodb_stats_persistent_sample_pages
:估計索引列的基數和其他統計信息(如由分析表計算的統計信息)時要採樣的索引頁數。增加該值可以提高索引統計信息的準確性,但為innodb_stats_persistent_sample_pages
設置較高的值可能會導致分析表執行時間過長。 -
innodb_stats_auto_recalc
:使InnoDB在表中的數據發生重大變化後自動重新計算持久統計信息。閾值為表中行數的10%,預設打開。 -
innodb_stats_include_delete_marked
:計算持久優化器統計信息時InnoDB是否包括已標記刪除的記錄,預設關閉。 -
innodb_stats_transient_sample_pages
:估計索引列的基數和其他統計信息(如由分析表計算的統計信息)時要採樣的索引頁數。預設值為8。增加該值可以提高索引統計信息的準確性,從而改進查詢執行計劃,但代價是在打開InnoDB表或重新計算統計信息時會增加I/O。該參數僅適用於為表禁用innodb_stats_persistent
的情況,如果啟用了INNODB_STATS_PERSIST
則應用INNODB_STATS_PERSIST_SAMPLE_PAGES
代替innodb_stats_sample_pages
總結:
1、非持久化統計信息在以下情況會被自動更新:
- 執行ANALYZE TABLE
innodb_stats_on_metadata=ON
情況下,執SHOW TABLE STATUS, SHOW INDEX, 查詢INFORMATION_SCHEMA下的TABLES, STATISTICS- 啟用--auto-rehash功能情況下,使用mysql client登錄
- 表第一次被打開
- 距上一次更新統計信息,表1/16的數據被修改
非持久化統計信息的缺點顯而易見,資料庫重啟後如果大量表開始更新統計信息,會對實例造成很大影響,所以目前都會使用持久化統計信息。
2、持久化統計信息在以下情況會被自動更新:
-
INNODB_STATS_AUTO_RECALC=ON
的情況下,表中10%的數據被修改 -
增加新的索引
3、統計信息不准確的處理
我們查看執行計劃,發現未使用正確的索引,如果是innodb_index_stats中統計信息差別較大引起,可通過以下方式處理:
- 手動更新統計信息,註意執行過程中會加讀鎖:
ANALYZETABLE TABLE_NAME;
- 如果更新後統計信息仍不准確,可考慮增加表採樣的數據頁,兩種方式可以修改:
a. 全局變數INNODB_STATS_PERSISTENT_SAMPLE_PAGES
預設為20;
b. 單個表可以指定該表的採樣:
ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;
經測試,此處STATS_SAMPLE_PAGES
的最大值是65535,超出會報錯。
c. 手動更新innodb_table_stats
和innodb_index_stats
表統計信息(修改這兩個表不會產生binlog),然後使用FLUSH TABLE tbl_name
語句載入更新後的統計信息。
Enjoy GreatSQL