MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.9.6 オプティマイザ統計

9.1 English
9.0 English
8.4 English
8.0 English

MySQL 8.0 リファレンスマニュアル / ... / オプティマイザ統計

このページは機械翻訳したものです。

8.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 ヒストグラムの場合、バケットには次の 2 つの値が含まれます:
- 値 1: バケットの値。型はカラムのデータ型によって異なります。
- 値 2: 値の累積周波数を表す double。たとえば、.25 および .75 は、カラムの値の 25% および 75% がバケット値以下であることを示します。
equi-height ヒストグラムの場合、バケットには次の 4 つの値が含まれます:
- 値 1, 2: バケットの下限値と上限値。型はカラムのデータ型によって異なります。
- 値 3: 値の累積周波数を表す double。たとえば、.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: 1 つのバケットは、カラム内の 1 つの値を表します。このヒストグラムタイプは、カラムの個別値の数が、ヒストグラムを生成した ANALYZE TABLE ステートメントで指定されたバケットの数以下の場合に作成されます。
- equi-height: 1 つのバケットは値の範囲を表します。このヒストグラムタイプは、カラム内の個別値の数が、ヒストグラムを生成した 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);

ヒストグラム統計は、主にインデックス付けされていないカラムに役立ちます。ヒストグラム統計が適用可能なカラムにインデックスを追加すると、オプティマイザが行の見積りを行うのにも役立ちます。トレードオフは次のとおりです:

テーブルデータが変更された場合は、インデックスを更新する必要があります。
ヒストグラムはオンデマンドでのみ作成または更新されるため、テーブルデータの変更時にオーバーヘッドは発生しません。一方、統計は、次回更新されるまで、テーブルの変更が発生すると徐々に期限切れになります。

オプティマイザは、ヒストグラム統計から取得したものよりも範囲オプティマイザ行の見積りを優先します。オプティマイザが範囲オプティマイザが適用されると判断した場合、ヒストグラム統計は使用されません。

インデックス付けされたカラムの場合、インデックス除算を使用して等価比較のために行の見積りを取得できます (セクション8.2.1.2「range の最適化」を参照)。この場合、ヒストグラム統計は必ずしも役に立つとはかぎりません。これは、インデックスの分割によって見積もりが向上するためです。

ヒストグラム統計を使用しても、クエリーの実行が改善されない場合があります (統計が最新でない場合など)。この場合に該当するかどうかを確認するには、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 が表示されます。

PREV HOME UP NEXT