存儲引擎搜集優化器使用的表的統計信息。表統計基于數數值組,其中數數值組是一系列有相同的關鍵字前綴值的記錄。對于優化器,重要的統計即為數數值組的平均大小。
MySQL用下述方式使用平均數數值組:
· 估計必須為每個ref訪問讀取多少行
· 估計部分聯接將產生多少行;也就是說,下述形式的操作將產生的行數:
· (...) JOIN tbl_name ON tbl_name.key = expr
隨著索引的平均數數值組大小的增加,索引將更沒有用,因為每個查找的平均行數增加:為了讓索引有利于優化目的,最好是每個索引值對應表內的少量行數。當某個給定的索引值產生較多行時,索引更加沒有用,MySQL更不可能使用它。
平均數數值組大小與表的集的勢相關,即數數值組的數目。SHOW INDEX語句顯示集的勢值(基于N/S),其中N是表內的記錄數,S是平均數數值組大小。該比例產生表內數數值組的大約數。
對于基于<=>比較 操作符的聯接,NULL并不視為與任何其它值不同:NULL <=> NULL,正如對于其它N ,N <=> N。
然而,對于基于=操作符的聯接,NULL與非NULL值不同:當expr1或expr2(或兩者)為NULL時,expr1 = expr2不為真。這樣影響比較形式tbl_name.key = expr的ref訪問:如果expr當前的值為NULL,MySQL不會訪問表,因為比較不能為真。
對于=比較,表內有多少NULL值并不重要。為了優化目的,相關值為非NULL數值組的平均大小。然而,MySQL目前不允許搜集或使用該平均大小。
對于MyISAM表,你可以使用myisam_stats_method系統變量部分控制表統計信息的搜集。該變量有兩個可能的不同值,如下所示:
· 當myisam_stats_method為nulls_equal時,所有NULL值被視為相等的(也就是說,它們都形成一個數值組)。
如果NULL數值組大小遠大于平均非NULL數值組大小,該方法向上傾斜平均數數值組大小。這樣使索引對于優化器來說比它實際為查找非NULL值的聯接更加沒有用。結果是,nulls_equal方法會使優化器進行ref訪問時本應使用索引而沒有使用。
· 當myisam_stats_method為nulls_unequal時,NULL值不視為相同。相反,每個NULL值形成一個單獨的數值組,大小為1。
如果你有許多NULL值,該方法向下傾斜平均數數值組大小。如果平均非NULL數值組較大,統計大小為1的每個組的NULL值會使優化器過高估計查找非NULL值的聯接的索引值。結果是,當其它方法會更好時,nulls_unequal方法會使優化器為ref查找使用該索引。
如果你要使用許多使用<=>而不是=的聯接,在比較過程中NULL值并不特殊,一個NULL等于另一個NULL。在這種情況下,nulls_equal是合適的統計方法。
myisam_stats_method系統變量有全局和會話值。設置全局值會影響MyISAM 為所有MyISAM表的統計的搜集。設置會話值只影響當前客戶連接的統計的搜集。這說明你可以強制用給定的方法重新生成表的統計的搜集,而不需要因為設置myisam_stats_method的會話值而影響其它客戶。
可以使用下面任一方法來重新生成表的統計信息:
· 設置myisam_stats_method,然后執行CHECK TABLE語句
· 執行myisamchk --stats_method=method_name --analyze
· 更改表,使其統計信息不為最新(例如,插入一行然后刪除它),然后設置myisam_stats_method并執行ANALYZE TABLE語句
使用myisam_stats_method的一些警告:
你可以強制顯式搜集表的統計信息,如上所述。然而,MySQL也可以自動搜集統計信息。例如,如果在為表執行語句的過程中,一些語句修改了表,MySQL可以搜集統計信息。(例如,大批插入或刪除,或者執行ALTER TABLE語句時可能發生)。如果發生,使用myisam_stats_method此時所有的值搜集統計信息。這樣,如果你使用一個方法搜集統計信息,但當后面自動搜集一個表的統計信息時myisam_stats_method被設置為另一個方法,將使用其它方法。
對于給定的MyISAM表,還不能說出使用哪個方法來產生統計信息。
myisam_stats_method只適合MyISAM表。其它存儲引擎只有一個方法來搜集表的統計信息。通常它接近于nulls_equal方法。