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

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

以下もご興味があるかもしれません:
  • MySQLの整数および文字列インデックスの無効化または暗黙的な変換に関する簡単な説明
  • MySQLの驚くべき暗黙の変換
  • MySQLの暗黙的な変換について話す
  • MySQLの暗黙的な変換問題の解決
  • MySQL インデックス無効化の暗黙的な変換の問題

<<:  Vueダイナミックフォームの詳細な応用

>>:  docker version es、milvus、minio 起動コマンドの詳細な説明

推薦する

フレックスレイアウトの改行スペースでの align-content の使用

1. この記事で実装した効果図は以下のとおりです。レイアウトの右側に Flex レイアウトを使用し、...

テーブルレイアウトの長所と短所、そして推奨されない理由

テーブルの欠点1. テーブルは他の HTML タグよりも多くのバイトを占有します。 (ダウンロード時...

Vue axios インターセプターは、繰り返しリクエストのキャンセルによく使用されます。

導入前回の記事では、axios のシンプルなカプセル化と、axios インターセプターの適用シナリオ...

Tinymce リッチテキストを使用して Vue のツールバーボタンをカスタマイズする実践

目次tinymce、tinymce ts、tinymce-vue 宣言ファイルをインストールするパッ...

ページネーションの例とベストプラクティス

<br />構造と階層により複雑さが軽減され、読みやすさが向上します。記事やサイトが整理...

CSS初心者向けチュートリアル: 背景画像を画面全体に表示する

インターフェース全体に背景画像を表示したい場合は、当然 body に背景を追加することを考えます。コ...

MySQL の FIND_IN_SET() と IN の違いを簡単に分析します

以前、あるプロジェクトでMysql FIND_IN_SET関数を使用したことがありますが、非常に便利...

CSS3で蓮の花が咲くアニメーション効果を実現

まずは効果を見てみましょう:この効果は非常に華やかに見えますが、原理は複雑ではありません。1 枚の花...

Tomcat10 Catalinaのログの文字化けの問題を解決する

実行環境、Idea2020バージョン、Tomcat10、実行時にTomcat CatalinaLog...

ネイティブ JS で音楽プレーヤーを実装するためのサンプル コード

この記事では主に、次のように共有されるネイティブ JS 音楽プレーヤーのサンプル コードを紹介します...

docker と docker-compose による eureka の高可用性の実現の詳細な説明

最近、新しいプロジェクトでは springcloud と docker が使用されています。この 2...

MySQLにおける正規表現の一般的な使用法

MySQL における Regexp の一般的な使用法特定の文字列を含むあいまい一致# コンテンツフィ...

ウェブサイトのビジュアルデザインの重要なポイント

手工芸デザインからグラフィックデザイン、そしてウェブデザインまで、デザインの原則は同じままですが、私...

Nginx 構成 SSL および WSS 手順の紹介

目次序文1. Nginxのインストール1. Nginxをダウンロードする2. 依存関係をインストール...

モバイルデバイス上のぼやけた小さなアイコンの問題を解決する方法

序文以前、画像とテキストの垂直方向のずれの問題について説明しました。ここで示した小さな例では、小さな...