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 をよろしくお願いいたします。