916
innodb的統計信息對執行計劃預估的影響實例
實為吾之愚見,望諸君酌之!聞過則喜，與君共勉
第一節 innodb引擎統計信息
mysql會依據innodb表的數據變化閾值來自動收集和計算表的統計信息(innodb_stats_auto_recalc)以供優化器使用，統計信息的收集是先通過獲取一部分符合條件的索引頁中的leaf page（是leaf page，不是non-leaf page）的數據，然後通過對這些采集的leaf page計算估計出不同值的數量，進而估算出的信息，信息采集的準確度除了和數據本身的構成有關，還與采集page數量有關，數量越多，采集精度越準確，在mysql5.6中引入了Persistent Optimizer Statistics來解決之前的Non-Persistent Optimizer Statistics帶來的一些問題，可以使用innodb_stats_persistent_sample_pages/innodb_stats_sample_pages控製采集精度，innodb_stats_sample_pages已經不推薦使用。innodb_stats_persistent_sample_pages參數是全局的，如果想單獨指定某個表的采集page數量，可以使用STATS_SAMPLE_PAGES選項，采集信息結果不準確甚至過度不準確會影響執行計劃的生成，造成語句的執行出現問題以至於影響數據庫的正常運行，這時可能就需要手動指定采集page數量來收集準確的統計信息，矯正執行計劃。查看mysql的統計信息（5.6）可以從mysql.innodb_table_stats and mysql.innodb_index_stats以及information_schema.INNODB_SYS_TABLESTATS獲取
第二節 準備數據和對比測試
2.1 建測試表
CREATE TABLE `MOCK_DATA` (
`autoid` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) DEFAULT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`gender` varchar(50) DEFAULT NULL,
`ip_address` varchar(20) DEFAULT NULL,
PRIMARY KEY (`autoid`),
KEY `first_name` (`first_name`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16580327 DEFAULT CHARSET=latin1
其中autoid是clustered index，first_name和id是secondary index，且id是1至1000連續的數字循環插入，
即id列隻有1至1000這些數字
2.2 生成測試數據
使用mockaroo臨時生成16384000行測試數據：
2.3 查詢統計信息表
Index_name為id的stat_value為15185，即以index列為索引，在16384000的數據裏有15185個不同值（實際是1至1000個不同值），index_name索引有18368個索引頁（nonleaf page+leaf page），有15985個葉子頁(leaf page)
該表經過統計，預估有16312847行數據，primary index有82736個索引頁，除primary index外其他索引一共有40128個索引頁（正好是innodb_index_stats中first_name和id索引頁的和）
Index name為id的索引其Cardinality是30377（與innodb_index_stats中的stat_value的distinct value的數值不同）
如上獲取的一些統計信息是在innodb_stats_persistent_sample_pages為20的情況下，手動analyze table MOCK_DATA生成的
2.4 執行查詢
執行如下語句：
SET optimizer_trace="enabled=on";
EXPLAIN EXTENDED select count(1) from MOCK_DATA where id=1
查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表的優化器追蹤信息：
以上是正常情況下的執行計劃，下麵進行修改統計信息數據，模擬統計信息對執行計劃的影響在哪裏
2.5 修改統計信息
隻修改innodb_table_stats的n_rows變為10，同樣執行：
SET optimizer_trace="enabled=on";
EXPLAIN EXTENDED select count(1) from MOCK_DATA where id=1
如下：
執行計劃：
和之前的對比，執行計劃有明顯的變化，再次查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表的優化器追蹤信息：
通過對比發現，雖然生成的執行計劃使用的索引和access type沒有錯誤，但是在生成過程中的cost與之前相比已經變化明顯了。不準確的統計信息有很大可能對優化器的cost預估產生影響。所以我們可能有時候需要手工的進行統計信息的收集，除了統計信息還有很多情況會對optimizer的執行計劃生成產生影響，比如索引的數量，索引數據的分布等等
第三節 統計信息收集
統計信息收集最常用的是analyze table 和optimize table，一般情況下這兩個操作是有效的，但是也有少數情況analyze table和optimize table完全失效（獲取不了準確的統計信息），即使我們知道表和索引的數據分布並非如此，我們也無法使用analyze和optimize來獲取，此時可能就需要更精確的收集，拿上麵的表舉例子，其有16384000行數據，使用analyze 和optimize 在innodb_stats_persistent_sample_pages為20的情況下，對數據量大的表和索引預估可能並不完全準確（16384000已經比較準確了），如果我們需要其預估完全準確的話（正常情況下不需要完全準確，會加重統計信息采集時間），我們可以對innodb表嚐試如下兩種方式：
1， 調大innodb_stats_persistent_sample_pages的值，然後再執行analyze table
2，單獨設置該表的STATS_SAMPLE_PAGES數量
2.1 調整innodb_stats_persistent_sample_pages
通過如下信息：
因為隻有一個Primary key有92634和leaf page，沒有其他的unique key，這裏
分別設置innodb_stats_persistent_sample_pages為60000和92634和92635，
然後與默認的20進行對比，如下：
1），set global innodb_stats_persistent_sample_pages=60000：
2），set global innodb_stats_persistent_sample_pages=92634：
3），set global innodb_stats_persistent_sample_pages=92635:
通過對比，當設置為92635（leaf page+1）時，數量才可以完全的準確，此時的mysql.innodb_index_stats表如下，已經很準確了，如下
2.2 調整STATS_SAMPLE_PAGES
同樣分別設置STATS_SAMPLE_PAGES為60000和92634和92635
1），STATS_SAMPLE_PAGES=60000
2），STATS_SAMPLE_PAGES=92634以及STATS_SAMPLE_PAGES=92634
3），調整STATS_SAMPLE_PAGES設置為 65535，該參數最大為65535（STATS_SAMPLE_PAGES的最大值，文檔未標明，測試所得，應該是代碼限製）
未能達到innodb_stats_persistent_sample_pages的效果，當STATS_SAMPLE_PAGES為65535時，此時innodb_index_stats的信息如下：
2.3 問題延伸
2.4 測試結果
通過測試，發現默認采集20個leaf page一般情況是比較準確的，故正常情況下，我們是不需要手工幹預的，可以交給mysql根據數據量的變化自動統計，太精確的采集page數量過多會造成係統的負擔，隻有當明確的得知統計信息錯誤（表中的數據分布並非如此），而且默認采集page數量使用analyze和optimize無法獲取更精確的統計信息時可以嚐試這樣做
問題：為何STATS_SAMPLE_PAGES最大值代碼裏限製為65535暫時不清楚為何，測試所得其最大為65535
最後更新：2017-08-18 17:02:28