MySQL :: MySQL 9.0 參考手冊 :: 10.9.6 優化器統計資訊

版本 9.0

8.4 目前
8.0

8.0 日文

MySQL 9.0 參考手冊 / ... / 優化器統計資訊

10.9.6 優化器統計資訊

column_statistics 資料字典表格儲存關於欄位值的直方圖統計資訊，供優化器在建構查詢執行計畫時使用。若要執行直方圖管理，請使用 ANALYZE TABLE 陳述式。

column_statistics 表格具有下列特性

表格包含所有資料類型（幾何類型（空間資料）和 JSON 除外）的欄位的統計資訊。
表格是持久性的，因此不需要在每次伺服器啟動時都建立欄位統計資訊。
伺服器會執行表格的更新；使用者則不會。

column_statistics 表格無法由使用者直接存取，因為它是資料字典的一部分。直方圖資訊可以使用 INFORMATION_SCHEMA.COLUMN_STATISTICS 來取得，它實作為資料字典表格的檢視。COLUMN_STATISTICS 具有下列欄位

SCHEMA_NAME、TABLE_NAME、COLUMN_NAME：統計資訊套用的綱要、表格和欄位的名稱。
HISTOGRAM：以直方圖形式儲存，描述欄位統計資訊的 JSON 值。

欄位直方圖包含儲存在欄位中數值範圍的部分的貯體。直方圖是 JSON 物件，以允許在欄位統計資訊的表示法中具有彈性。以下是直方圖物件的範例

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "collation-id": 8
}

直方圖物件具有下列鍵

buckets：直方圖貯體。貯體結構取決於直方圖類型。
對於 singleton 直方圖，貯體包含兩個值
- 值 1：貯體的值。類型取決於欄位資料類型。
- 值 2：代表該值累積頻率的倍精準度浮點數。例如，.25 和 .75 表示欄位中 25% 和 75% 的值小於或等於貯體值。
對於 equi-height 直方圖，貯體包含四個值
- 值 1、2：貯體的下限和上限值（含）。類型取決於欄位資料類型。
- 值 3：代表該值累積頻率的倍精準度浮點數。例如，.25 和 .75 表示欄位中 25% 和 75% 的值小於或等於貯體上限值。
- 值 4：在從貯體下限值到其上限值範圍內的相異值數量。
null-values：介於 0.0 和 1.0 之間的數字，表示 SQL NULL 值的欄位值比例。如果為 0，則欄位不包含 NULL 值。
last-updated：直方圖產生時的時間，以 UTC 值和 YYYY-MM-DD hh:mm:ss.uuuuuu 格式表示。
sampling-rate：介於 0.0 和 1.0 之間的數字，表示採樣以建立直方圖的資料比例。值 1 表示已讀取所有資料（未採樣）。
histogram-type：直方圖類型
- singleton：一個儲存桶代表該欄位中的單一值。當欄位中不同的值的數量小於或等於在產生直方圖的 ANALYZE TABLE 陳述式中指定的儲存桶數量時，就會建立此直方圖類型。
- equi-height：一個儲存桶代表一個數值範圍。當欄位中不同的值的數量大於在產生直方圖的 ANALYZE TABLE 陳述式中指定的儲存桶數量時，就會建立此直方圖類型。
number-of-buckets-specified：在產生直方圖的 ANALYZE TABLE 陳述式中指定的儲存桶數量。
data-type：此直方圖包含的資料類型。將直方圖從持久儲存讀取並解析到記憶體中時，需要此資訊。值為 int、uint (無號整數)、double、decimal、datetime 或 string (包括字元和二進位字串) 其中之一。
collation-id：直方圖資料的校對 ID。當 data-type 的值為 string 時，此值主要有意義。這些值對應於 Information Schema COLLATIONS 表格中的 ID 欄位值。

若要從直方圖物件中提取特定值，您可以使用 JSON 操作。例如

mysql> SELECT
         TABLE_NAME, COLUMN_NAME,
         HISTOGRAM->>'$."data-type"' AS 'data-type',
         JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+-----------------+-------------+-----------+--------------+
| TABLE_NAME      | COLUMN_NAME | data-type | bucket-count |
+-----------------+-------------+-----------+--------------+
| country         | Population  | int       |          226 |
| city            | Population  | int       |         1024 |
| countrylanguage | Language    | string    |          457 |
+-----------------+-------------+-----------+--------------+

如果適用，最佳化工具會針對已收集統計資料的任何資料類型的欄位使用直方圖統計資料。最佳化工具會套用直方圖統計資料，以根據欄位值與常數值比較的選擇性（篩選效果）來判斷列估計值。這些形式的述詞符合直方圖的使用條件

col_name = constant
col_name <> constant
col_name != constant
col_name > constant
col_name < constant
col_name >= constant
col_name <= constant
col_name IS NULL
col_name IS NOT NULL
col_name BETWEEN constant AND constant
col_name NOT BETWEEN constant AND constant
col_name IN (constant[, constant] ...)
col_name NOT IN (constant[, constant] ...)

例如，以下陳述式包含符合直方圖使用條件的述詞

SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0;
SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100;

與常數值比較的要求包括常數函數，例如 ABS() 和 FLOOR()

SELECT * FROM tbl WHERE col1 < ABS(-34);

直方圖統計資料主要適用於未索引的欄位。為適用於直方圖統計資料的欄位新增索引也可能有助於最佳化工具進行列估計。權衡取捨如下：

當修改表格資料時，必須更新索引。
直方圖僅在需要時建立或更新，因此修改表格資料時不會增加額外負擔。另一方面，當表格修改發生時，統計資料會逐漸過時，直到下次更新為止。

最佳化工具偏好從範圍最佳化工具取得的列估計值，而不是從直方圖統計資料取得的列估計值。如果最佳化工具判定適用範圍最佳化工具，則不會使用直方圖統計資料。

對於已索引的欄位，可以使用索引深入探討來取得相等比較的列估計值 (請參閱第 10.2.1.2 節「範圍最佳化」)。在這種情況下，直方圖統計資料不一定有用，因為索引深入探討可以產生更好的估計值。

在某些情況下，使用直方圖統計資料可能不會改善查詢執行 (例如，如果統計資料已過時)。若要檢查是否屬於這種情況，請使用 ANALYZE TABLE 重新產生直方圖統計資料，然後再次執行查詢。

或者，若要停用直方圖統計資料，請使用 ANALYZE TABLE 捨棄它們。停用直方圖統計資料的另一種方法是關閉 optimizer_switch 系統變數的 condition_fanout_filter 旗標 (雖然這也可能停用其他最佳化)

SET optimizer_switch='condition_fanout_filter=off';

如果使用直方圖統計資料，可以使用 EXPLAIN 來查看結果效果。考慮以下查詢，其中欄位 col1 沒有可用的索引

SELECT * FROM t1 WHERE col1 < 24;

如果直方圖統計資料指出 t1 中有 57% 的列符合 col1 < 24 述詞，即使沒有索引，也可以進行篩選，而且 EXPLAIN 會在 filtered 欄位中顯示 57.00。

上一步首頁上一層下一步