GreatSQL統計信息相關知識點

相關知識點：

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、非持久化統計信息在以下情況會被自動更新：

1. 執行ANALYZE TABLE
2. innodb_stats_on_metadata=ON情況下，執SHOW TABLE STATUS, SHOW INDEX, 查詢INFORMATION_SCHEMA下的TABLES, STATISTICS
3. 啟用--auto-rehash功能情況下，使用mysql client登錄
4. 表第一次被打開
5. 距上一次更新統計信息，表1/16的數據被修改

非持久化統計信息的缺點顯而易見，資料庫重啟後如果大量表開始更新統計信息，會對實例造成很大影響，所以目前都會使用持久化統計信息。

2、持久化統計信息在以下情況會被自動更新：

1. INNODB_STATS_AUTO_RECALC=ON的情況下，表中10%的數據被修改

2. 增加新的索引

3、統計信息不准確的處理

我們查看執行計劃，發現未使用正確的索引，如果是innodb_index_stats中統計信息差別較大引起，可通過以下方式處理：

1. 手動更新統計信息，註意執行過程中會加讀鎖：

ANALYZETABLE TABLE_NAME;

2. 如果更新後統計信息仍不准確，可考慮增加表採樣的數據頁，兩種方式可以修改：

​ 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