MySQL の暗黙的な型変換によって発生するインデックス障害の解決策

質問

仕事中、1 つの SQL クエリステートメントのみを実行するインターフェイスがあり、SQL は明らかに主キー列を使用しているものの、速度が非常に遅いことがわかりました。
MySQL で EXPLAINN を実行したところ、実行時に主キーインデックスが使用されず、テーブル全体のスキャンが実行されたことが判明しました。

再生

データテーブル DDL は次のようになります。user_id を主キーインデックスとして使用します。

 テーブル `user_message` を作成します (
   `user_id` varchar(50) NOT NULL COMMENT 'ユーザーID',
   `msg_id` int(11) NOT NULL COMMENT 'メッセージID',
   主キー (`user_id`)
 )ENGINE=InnoDB デフォルト文字セット=utf8mb4;

次のクエリステートメントを実行すると、キーでは主キーインデックスが使用されていることが示されていますが、行ではテーブル全体がスキャンされ、主キーインデックスが機能していないことが示されています。

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = 1;
 
 id|select_type|テーブル|パーティション|タイプ|可能なキー|キー|キー長|ref|行|フィルター済み|追加|
 --+------------+------------+-----------+----------+---------+---+---------+-------------------------+
  1|SIMPLE |user_message| |index|PRIMARY |PRIMARY|206 | |10000| 10.0|where の使用; index の使用|

調査の結果、データテーブルの user_id フィールドは VARCHAR 型であり、SQL ステートメントの user_id は INT 型であることがわかりました。 MySQL はステートメントを実行するときに型を変換します。これにより、型変換後に主キーインデックスが無効になります。

暗黙的な変換

MySQL の公式ドキュメント (https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html) では、MySQL の型の暗黙的な変換のルールが紹介されています。

演算子の両側のオペランドの型が一致しない場合、MySQL はオペランドの互換性を保つために型変換を実行します。これらの変換は暗黙的に行われます。比較演算の暗黙的な変換について次に説明します。

一方または両方の引数が NULL の場合、比較の結果は NULL になります。ただし、<=> 等価比較演算子の場合は例外で、NULL <=> NULL は変換なしで true と評価されます。
比較演算の両方の引数が文字列の場合、それらは文字列として比較されます。
両方の引数が整数の場合、それらは整数として比較されます。
16 進数値を数値と比較しない場合は、バイナリ文字列として扱われます。
引数の 1 つが TIMESTAMP または DATETIME 列であり、もう一方が定数である場合、比較が実行される前に定数がタイムスタンプに変換されます。これは IN() の引数に対しては実行されません。安全のため、比較を行うときは常に完全な datetime、date、または time 文字列を使用してください。たとえば、日付または時刻の値で BETWEEN を使用するときに最良の結果を得るには、CAST() を使用してそれらの値を目的のデータ型に明示的に変換します。
1 つ以上のテーブルに対する単一行のサブクエリは定数とは見なされません。たとえば、サブクエリが DATETIME 値と比較する整数を返す場合、比較は 2 つの整数として行われ、整数は時刻値に変換されません。前の項目を参照してください。この場合、CAST() を使用して、サブクエリの結果の整数値を DATETIME に変換します。
引数の 1 つが 10 進数値の場合、比較は他の引数に依存します。他の引数が小数値または整数値の場合、引数は小数値として比較されます。他の引数が浮動小数点値の場合、引数は浮動小数点値として比較されます。
それ以外の場合、引数は浮動小数点数 (実数) として比較されます。たとえば、文字列と数値のオペランドは浮動小数点数として比較されます。

上記の最後のルールに従って、前の SQL ステートメントでは、文字列と整数の比較が 2 つの浮動小数点の比較に変換されます。左側は文字列型 "1" が浮動小数点数 1.0 に変換されたもので、右側は INT 型 1 が浮動小数点数 1.0 に変換されたものになります。

論理的に考えると、両辺とも浮動小数点数なのでインデックスが使えるはずですが、実行時に使用されないのはなぜでしょうか？

その理由は、MySQL で文字列を浮動小数点型に変換するための変換規則が次のとおりであるためです。

1. 数字で始まらないすべての文字列は 0 に変換されます。

 CAST('abc' を UNSIGNED として選択)
 
 CAST('abc' を UNSIGNED として) |
 -----------------------+
                       0|

2. 数字で始まる文字列を変換する場合、最初の文字から最初の非デジタルコンテンツまでがインターセプトされます。

 SELECT CAST('0123abc' AS UNSIGNED) を選択
 
 CAST('0123abc' を UNSIGNED として) |
 ----------------------------+
                          123|

したがって、MySQL では、「1」、「1」、「1a」、「01」などの文字列はすべて 1 として数値に変換されます。

MySQL は上記の SQL 文を実行すると、各行の主キー列の値を浮動小数点数に変換し (主キーに対して CAST 関数を実行)、条件パラメータと比較します。インデックス列で関数を使用すると、インデックスが無効になり、最終的にはテーブル全体のスキャンが実行されます。

前の SQL で渡されたパラメータを文字列に変更するだけで、主キーインデックスを使用できるようになります。

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = '1';
 
 id|select_type|テーブル|パーティション|タイプ|可能なキー|キー|キー長|参照|行|フィルター済み|追加|
 --+-----------+-------------+------------+-----------+---------+---------+----------+-----------+-----------+
  1|SIMPLE |user_message| |ref |PRIMARY |PRIMARY|202 |const| 135| 100.0|インデックスを使用|

要約する

1. 条件列が文字列の場合、渡された条件パラメータが整数であれば、まず浮動小数点数に変換され、次にテーブル全体がスキャンされ、インデックスが失敗します。
2. 暗黙的な変換を避けるために、条件パラメータは可能な限り列と同じ型にするか、渡されたパラメータに対して変換関数を実行してインデックス列と同じ型に変換する必要があります。

参照する

1. MySQLの暗黙的な変換の簡単な分析

これで、MySQL の暗黙的な型変換によって発生するインデックス無効化の問題を解決する方法についての記事は終了です。MySQL の暗黙的な型変換によって発生するインデックス無効化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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