MySQL :: MySQL 8.4 參考手冊 :: 10.3.8 InnoDB 和 MyISAM 索引統計資訊收集

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / InnoDB 和 MyISAM 索引統計資訊收集

10.3.8 InnoDB 和 MyISAM 索引統計資訊收集

儲存引擎收集有關資料表的統計資訊，以供最佳化工具使用。資料表統計資訊基於值群組，其中值群組是一組具有相同索引鍵前置值 (key prefix value) 的資料列。為了最佳化工具的目的，一個重要的統計資訊是平均值群組大小。

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 不為真。這會影響 ref 形式為 tbl_name.key = expr 的比較存取：如果 expr 的目前值為 NULL，則 MySQL 不會存取資料表，因為比較結果不可能為真。

對於 = 比較，資料表中有多少個 NULL 值並不重要。為了最佳化的目的，相關的值是非 NULL 值群組的平均大小。但是，MySQL 目前不允許收集或使用該平均大小。

對於 InnoDB 和 MyISAM 資料表，您可以分別透過 innodb_stats_method 和 myisam_stats_method 系統變數，來控制資料表統計資訊的收集。這些變數有三個可能的值，它們的不同之處如下

當變數設定為 nulls_equal 時，所有 NULL 值都會被視為相同（也就是說，它們全部構成單一值群組）。
如果 NULL 值群組大小遠高於平均非 NULL 值群組大小，則此方法會將平均值群組大小向上傾斜。這會使索引對最佳化工具而言，看起來比實際針對尋找非 NULL 值的聯結的用處還小。因此，當 nulls_equal 方法應該使用索引進行 ref 存取時，可能會導致最佳化工具不使用索引。
當變數設定為 nulls_unequal 時，NULL 值不會被視為相同。相反地，每個 NULL 值會形成一個大小為 1 的獨立值群組。
如果您有很多 NULL 值，這種方法會使平均值群組大小向下傾斜。如果平均非 NULL 值群組大小很大，將每個 NULL 值都算作大小為 1 的群組，會導致最佳化工具高估用於尋找非 NULL 值的聯結的索引值。因此，nulls_unequal 方法可能會導致最佳化工具在其他方法可能更好的情況下，對 ref 查找使用此索引。
當變數設定為 nulls_ignored 時，NULL 值會被忽略。

如果您傾向於使用許多使用 <=> 而不是 = 的聯結，那麼 NULL 值在比較中並不特殊，一個 NULL 值等於另一個 NULL 值。在這種情況下，nulls_equal 是適當的統計方法。

innodb_stats_method 系統變數具有全域值；myisam_stats_method 系統變數則同時具有全域值和連線階段值。設定全域值會影響來自對應儲存引擎的表格的統計資料收集。設定連線階段值僅會影響目前用戶端連線的統計資料收集。這表示您可以透過設定 myisam_stats_method 的連線階段值，強制以給定的方法重新產生表格的統計資料，而不會影響其他用戶端。

若要重新產生 MyISAM 表格統計資料，您可以使用以下任何一種方法：

執行 myisamchk --stats_method=method_name --analyze
變更表格使其統計資料過時（例如，插入一列然後刪除它），然後設定 myisam_stats_method 並發出 ANALYZE TABLE 陳述式

關於使用 innodb_stats_method 和 myisam_stats_method 的一些注意事項

您可以如上所述強制明確收集表格統計資料。但是，MySQL 也可能會自動收集統計資料。例如，如果在執行表格的陳述式過程中，其中一些陳述式修改了表格，則 MySQL 可能會收集統計資料。（例如，這可能會發生在大量插入或刪除，或某些 ALTER TABLE 陳述式中。）如果發生這種情況，則會使用當時 innodb_stats_method 或 myisam_stats_method 所具有的值來收集統計資料。因此，如果您使用一種方法收集統計資料，但當稍後自動收集表格的統計資料時，系統變數設定為另一種方法，則會使用另一種方法。
沒有辦法知道為給定表格產生統計資料時使用了哪種方法。
這些變數僅適用於 InnoDB 和 MyISAM 表格。其他儲存引擎只有一種收集表格統計資料的方法。通常，它更接近 nulls_equal 方法。