Mysql 5.6の「暗黙的な変換」によって引き起こされるインデックスの失敗と不正確なデータの問題を解決する

mysql ビデオ チュートリアル Mysql 5.6 のインデックスの失敗と不正確なデータの問題を解決するためのコラムの紹介

関連する無料学習の推奨事項: mysql ビデオ チュートリアル

背景

• 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 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s

まず、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. 条件を生成します演算子の型が異なる場合オペランドを一緒に使用すると、オペランドの互換性を保つために型変換が行われます。暗黙的な変換が発生します
暗黙的な変換の条件:

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

2. 遭遇した実際の状況を分析します

1. そうすれば、上で提案した例は整数と文字列の比較であり、他の状況に属することが理解できるでしょう。 。次に、まずインデックスの失敗の理由を分析しましょう

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

2. 上記のクエリ結果など、一致しない値 (または部分的に一致した値) をクエリします。実際にソース コードを確認する必要がありますが、これは MYsql の暗黙の変換ルールです。ここでは詳しく分析しません（関連するドキュメントが見つからないため）
歴史的な理由により、古い設計との互換性が必要ですが、MySQL の型変換関数 Cast と Convert を使用して明示的に変換できます。
概要

• 暗黙的な変換と関数を使用すると、インデックスが失敗し、選択されたデータが不正確になります。
• 暗黙的な変換の条件とルール
• 暗黙的な変換によりインデックス障害が発生する具体的な理由は、比較値を型変換する必要があるため、障害が発生するためです。
• 暗黙的な型変換を避けてください。暗黙的な変換の種類には主に、一貫性のないフィールド タイプ、複数の型を含むパラメータ、一貫性のない文字セット タイプや照合規則などが含まれます。

#プログラミング学習について詳しく知りたい方は、

php trainingのコラムに注目してください！

以上がMysql 5.6の「暗黙的な変換」によって引き起こされるインデックスの失敗と不正確なデータの問題を解決するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。