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

版本 8.4

9.0
8.0

8.0 日文

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

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：長條圖產生時的時間，採用 YYYY-MM-DD hh:mm:ss.uuuuuu 格式的 UTC 值。
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。

上一個首頁上一層下一個