MySQLが日付フィールドインデックスを使用しない理由の要約

背景

テーブルでは、dataTime フィールドは varchar 型に設定され、保存されるデータは日付形式で、フィールドにインデックスが設定されます。しかし、ログレコードには、このテーブルに関する遅いクエリがあります。クエリステートメントは次のとおりです。
select * from digitaltwin_meteorological where dataTime > '2021-10-15';
EXPLAIN は SQL ステートメントを分析し、SQL ステートメントが完全なテーブルスキャンを実行していることを検出します。 SQL で dataTime インデックス列が使用されるのはなぜですか。また、テーブル全体のスキャンが実行される理由はなぜですか。

探検する

1:当初、dataTime フィールドの型が varchar であると考えられていたため、MySQL は日付のサイズ順ではなく文字列順にインデックスをソートし、範囲クエリを実行するときに日付順にインデックス範囲のパーティション分割を実行できませんでした。そこで、dataTime を datatime 型に変更してステートメントを分析したところ、依然として完全なテーブルスキャンが行われていることがわかりました。

2:クエリ条件の値を変更する

 digitaltwin_meteorological から count(*) を選択します。ここで、dataTime > '2021-10-15' です。

実行結果は3910です。

EXPLAIN デジタルツインのmeteorologicalから*を選択し、dataTime > '2021-10-15'を選択します。

SQL ステートメントの分析結果は、完全なテーブルスキャンです。

ここに画像の説明を挿入

レコードの数を確認するために、クエリ条件を 16 に変更します。

 digitaltwin_meteorological から count(*) を選択します。ここで、dataTime > '2021-10-16' です。

クエリ結果は 2525 です。16 番のクエリステートメントを分析してみましょう。

EXPLAIN デジタルツインのmeteorologicalから*を選択し、dataTime > '2021-10-16'を選択します。

実行結果は、インデックスを使用する範囲クエリです。

ここに画像の説明を挿入

このことから、クエリされるレコードの数が多い場合、MySQL はフルテーブルスキャンの方が効率的であると判断してフルテーブルスキャンを実行することがわかります。クエリ内のレコードが少ない場合、MySQL はインデックスクエリを使用します。
テーブル内のデータの総量は 19714 レコードです。つまり、2525/19714 = 13% の場合、MySQL はインデックスクエリを使用します。 3910/19714=20% の場合、MySQL は完全なテーブルスキャンを実行します。

3: dataTime を datetime データ型に変更した場合、クエリ条件に引用符を追加する必要がありますか? dataTime クエリ条件の引用符を削除して結果を確認します。

EXPLAIN digitaltwin_meteorological から * を選択し、dataTime > 2021-10-16 を指定します。

ここに画像の説明を挿入
引用符を削除すると、再び完全なテーブルスキャンになることがわかります。したがって、フィールドタイプが varchar か datetime かに関係なく、クエリ条件の値は引用符で囲む必要があります。引用符がない場合、MySQL はこの値に対して何らかの計算を実行します。実際、引用符がない場合、2021-10-16 は 16 日の日付ではなくなります。次の SQL を見てみましょう。

 digitaltwin_meteorological から count(*) を選択します。ここで、dataTime > 2021-10-16 です。

計算結果は 19714 となり、これはテーブル全体のデータです。そのため、datetime クエリ条件も引用符で囲む必要があります。

4: 上記の分析はすべて、datetime 型の場合の dataTime に関するものです。元のフィールドタイプは varchar なので、これを varchar タイプに変更すると、上記の結論は依然として当てはまりますか? タイプを変更して、SQL を再度実行します。

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

ここに画像の説明を挿入

varchar 型に変更すると、クエリ 16 は範囲スキャンではなくフルテーブルスキャンになることがわかります。
条件を 17 番に変更して実行結果を確認します。

EXPLAIN デジタルツインのmeteorologicalから*を選択し、dataTime > '2021-10-17'を選択します。

ここに画像の説明を挿入

17日のクエリではインデックスクエリが使用されました。 17日のデータ量は1749であることがわかります。
したがって、フィールドタイプが varchar の場合、1749/19714 = 9% のケースでインデックスが使用され、2525/19714 = 13% のケースでテーブル全体がスキャンされます。
つまり、データ型が datetime の場合、クエリ結果がテーブル全体のデータの 13% を占めるとインデックスクエリが実行され、データ型が varchar の場合、クエリ結果がテーブル全体のデータの 13% を占めるとテーブル全体のスキャンが実行されます。これが、日付タイプを varchar ではなく datetime に設定する理由の 1 つです。

要約する

上記の分析を通じて、次のような結論を導き出すことができます。
1. 範囲クエリでは、クエリされるデータの量が特定の範囲に達すると、MySQL は完全なテーブルスキャンの方が効率的であると判断し、インデックスではなく完全なテーブルスキャンを実行します。
2. クエリを実行するときは、datetime フィールドタイプの値も引用符で囲む必要があります。そうしないと、MySQL は日付で処理しません。
3. 日付形式のデータの場合、varchar 型に設定すると、範囲クエリでインデックスクエリまたはフルテーブルスキャンを使用するための臨界値は、datetime 型クエリでインデックスクエリまたはフルテーブルスキャンを使用するための臨界値よりも低くなります。そのため、日付型データを datetime 型に設定すると、インデックスクエリが使用される可能性が高くなります。

MySQL が日付フィールドインデックスを使用しない理由に関するこの記事はこれで終わりです。MySQL 日付フィールドインデックスに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: