mysql如收集統計資訊

查詢最佳化器使用統計資訊為sql選擇執行計劃

Mysql沒有直方圖資訊，也無法手工刪除統計資訊

 

如何收集統計資訊

Analyze table收集表和索引統計資訊，適用於MyISAM和InnoDB；

對於innodb表，還可以使用以下選項

1

表第一次開啟的時候

2

表修改的行超過1/16或者20億條

 ./row/row0mysql.c:row_update_statistics_if_needed

3

執行show index/table或者查詢information_schema.tables/statistics表時

在訪問以下表時，innodb表的統計資訊可自動收集

information_schema.TABLES

information_schema.STATISTICS

information_schema.PARTITIONS

information_schema.KEY_COLUMN_USAGE

information_schema.TABLE_CONSTRAINTS

information_schema.REFERENTIAL_CONSTRAINTS

information_schema.table_constraints

 

innodb_stats_on_metadata引數用來控制此行為，設定為false時不更新統計資訊

Innodb_stats_sample_pages每次收集統計資訊時取樣的頁數，預設為8

 

每個表維護一個stat_modified_counter，每次DML更新1行就加1，直到滿足閾值則自動收集統計資訊，並把此值清0；

函式dict_update_statistics用於更新統計資訊，但若有多個執行緒同時檢測到閾值，會導致多次呼叫，浪費了系統資源；

可以直接修改程式碼，讓dict_update_statistics對stat_modified_counter加鎖，避免併發執行；http://dinglin.iteye.com/blog/1815392

5.6提供選項innodb_stats_persistent，預設on，將analyze table產生的統計資訊儲存於磁碟，直至下次analyze table為止，此舉避免了統計資訊動態更新，保證了執行計劃的穩定，對於大表也節省了收集統計資訊的所需資源;

 

除非當前sql執行計劃不佳，否則不應經常analyze table收集統計資訊

 

Innodb_stats_method和myisam_stats_method

計算統計資訊時，擁有相同key prefix的行算作一個value group(類似oracle索引中的num_distinct，其值越多意味著索引選擇性越好)，average group size是非常重要的指標，即平均一個索引值返回的錶行數，主要有兩個用途：

1估算每次ref access要讀取多少行

2 估算一個partial join要產生多少行 (…) join tab on tab.key = expr

 

由此可知，average group size越高則索引選擇性越低，表基數即value group數量計算公式為N/S(N:錶行數 S:average group size)，可透過show index檢視

 

除了主鍵，索引不可避免的會遇到Null(對於<=>運算子，NULL和Non-null被同等對待，而Null = Null則會返回false)，mysql將NULL視作無窮小；

收集統計資訊時，為了靈活的處理Null，InnoDB/MyISAM各引入一個引數Innodb_stats_method/myisam_stats_method，分別三個候選值：nulls_equal/nulls_unequal/nulls_ignored(其中innod_stats_method只有全域性變數)

Nulls_equal：所有Null都相等，即算作一個value group；若Null過多則會導致average group size偏大

Nulls_unequal：所有Null互不相同，每個算作一個value group；如果non-null group size過大且null數量過多，此設定會拉低整體的average group size，可能導致濫用索引

Nulls_ignored：忽略Null

 

對於已經收集的統計資訊，無法分辨其採用了那種方式；對於非InnoDB/MyISAM表，只有一種收集方式，即nulls_equal；

 

手工收集統計資訊需要呼叫analyze table，但若表自上次analye至今沒有任何改動，即便呼叫此命令實際也不會收集統計資訊，需先讓統計資訊過期(插入一行再刪除即可)

Mysql也可自動收集，諸如bulk insert/delete以及某些alter table語句均會觸發

 

 

如何檢視統計資訊

Show index from table或檢視information_schema.statistics表

Show table status或information_schema.tables表

 

http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/

http://www.mysqlperformanceblog.com/2008/09/03/analyze-myisam-vs-innodb/