MySQL 5.6 の「暗黙的な変換」によりインデックスが失敗し、データが不正確になる

背景

• SQL クエリを実行するときに、where 条件の vachar 型フィールドの単一引用符を削除しようとしました。このとき、非常に高速であるはずのこのステートメントが、実際には非常に遅いことがわかりました。この varchar フィールドには複合インデックスがあります。エントリの合計数は 58989 であり、一重引用符がない場合でも、取得されるデータは必要なデータではありません。
• MySQL 5.6バージョンを使用しており、innoDBエンジンの実際の状況は以下のとおりです

実行結果を見てみましょう

上記の説明では、where 条件内の文字列はすべて一重引用符なしの数字でなければならないことにも注意する必要があります。そうでなければエラーが報告されます

見つかったデータが、必要なデータではない可能性もあります。下記の通り

分析する

1. 実行結果から、対応するインデックスが一重引用符で使用されていることがわかります。一重引用符がない場合、インデックスは使用されず、テーブル全体のスキャンが実行されます。
2. なぜこのようなことが起こるのでしょうか? MySQL のオプティマイザが型変換を直接実行しないのはなぜですか?

• SQL ステートメントに一重引用符を使用すると、その型が文字列データ型 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、および SET であることを示します。 。
• 一重引用符がない場合、これは int、bigDecimal などの文字列以外の型であることを意味します。
• サブタイトルや特殊記号を含む文字列を一重引用符で囲まないと、型変換が失敗し、SQL を実行できなくなります。

上の図に示すように:

1054 - 'where 句' に不明な列 '000w1993521' があります。時間: 0.008000 秒

まずSQLの実行プロセスを見てみましょう

（オンライン写真）

• まず、インデックス フィールドに対して関数操作を実行すると (この場合、キャスト関数は暗黙的な変換を実行します)、インデックス値の順序が破壊される可能性があるため、オプティマイザはツリー検索機能を放棄することを決定するという結論に達します。 (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
• [外部リンク画像の転送に失敗しました。ソースサイトにはアンチホットリンクメカニズムがある可能性があります。画像を保存して直接アップロードすることをお勧めします (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
• つまり、BINARY、CAST()、または CONVERT() を使用してインデックス列を変換すると、MySQL がインデックスを効率的に使用できない可能性があることに注意してください。
• 暗黙的な変換のため、取得されたデータは不正確です。変換後、数値型が異なり、不等が等になります。
  

暗黙的な変換

1. 生成条件<br /> 演算子が異なる型のオペランドで使用される場合、オペランドの互換性を保つために型変換が行われます。暗黙的な変換は次の場合に発生します。

1. 2 つのパラメータのうち少なくとも 1 つが NULL の場合、比較の結果も NULL になります。例外として、<=> を使用して 2 つの NULL を比較すると 1 が返されます。どちらの場合も、型変換は必要ありません。
2. 両方のパラメータは文字列であり、型変換なしで文字列として比較されます。
3. 両方のパラメータは整数なので、型変換なしで整数として比較されます。
4. 16 進数値は、数値以外の値と比較するとバイナリ文字列として扱われます。
5. 1つのパラメータがTIMESTAMPまたはDATETIMEで、もう1つのパラメータが定数の場合、定数はタイムスタンプに変換されます。
6. 1 つのパラメータが 10 進数型の場合、他のパラメータが 10 進数または整数であれば、整数は比較のために 10 進数に変換されます。他のパラメータが浮動小数点数の場合、10 進数は比較のために浮動小数点数に変換されます。
7. それ以外の場合は、比較の前に両方の引数が浮動小数点数に変換されます。

2. 実際に遭遇した状況を分析する

1. すると、上で述べた例は整数と文字列の比較であり、他のケースに属するものであることは明らかです。それではまずインデックス失敗の理由を分析してみましょう

• 暗黙的な変換であるため、比較する値はすべて浮動小数点数に変換して比較する必要があります。
• まずクエリ条件値を浮動小数点数に変換し、次にテーブルのレコード値を変換します。そのため、以前に作成されたインデックスソートは有効ではなくなります。暗黙的な変換 (関数) によって元の値が変更されているため、オプティマイザーはここでインデックスを使用せず、直接フル テーブル スキャンを使用します。
  

2. 上記のクエリ結果に示すように、一致しない値（または部分的に一致する値）をクエリします。実際にソース コードを確認する必要があります。これは MYsql の暗黙的な変換ルールです。ここでは詳細に分析しません（関連する文書が見つからなかったため）
歴史的な理由により、古い設計との互換性が必要です。明示的な変換を実行するには、MySQL の型変換関数 cast と convert を使用できます。
要約する

• 暗黙的な変換や関数を使用すると、インデックスが失敗し、データが不正確になる可能性があります。
• 暗黙的な変換条件とルール
• 暗黙的な変換によってインデックスが失敗する具体的な理由は、比較値を異なる型に変換する必要があることです。
• 暗黙的な型変換は避けてください。暗黙的な変換には、主に、一貫性のないフィールド型、in パラメータ内の複数の型、一貫性のない文字セット型または校正ルールなどが含まれます。
  

参照する
型変換
https://xiaomi-info.github.io/2019/12/24/mysql-暗黙的変換/
https://zhuanlan.zhihu.com/p/95170837

MySQL 5.6 の「暗黙の変換」によって発生するインデックスの無効化と不正確なデータに関するこの記事はこれで終わりです。MySQL 5.6 の暗黙の変換によって発生するインデックスの無効化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。