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 起動コマンドの詳細な説明

推薦する

Nginxの仕組みの詳細な説明

Nginxの仕組みNginx はコアとモジュールで構成されています。 Nginx 自体は実際にはほと...

Reactでプロキシを有効にする2つの実用的な方法

プロキシを有効にする2つの方法React には、直接使用できるカプセル化された Ajax リクエスト...

DockerにELKをインストールしてJSON形式のログ分析を実装する方法

ELKとは何ですか? ELK は、Elastic が提供するログ収集およびフロントエンド表示ソリュー...

mysql IS NULL インデックスケースの説明を使用する

導入MySQL の SQL クエリ ステートメントで is null、is not null、!= ...

ウェブページのテーブルの境界線を設定する方法

<br />前回は、Web テーブルにセルの線を設定する方法を学びました。今日は、Web...

MySQL データ型の選択原則

目次小さいけれど美しいシンプルにNULL値を避けるデータタイプを選択する手順データ型の紹介1. 文字...

CSSで背景ぼかしを設定する方法

ページを作成するときに、ページの見栄えを良くするために、背景画像を設定する必要があることがよくありま...

Zabbix を使用して Nginx/Tomcat/MySQL を監視する方法の詳細なチュートリアル

目次ZabbixはNginxを監視するZabbixはTomcatを監視するZabbixはMySQLを...

HTML ハイパーリンク タグ_Powernode Java アカデミー

HTML を学習したり使用したりしたことがある人なら、<a> タグについてよく知っている...

よく使われるCSSカプセル化方法の概要

1. pc-reset PCスタイルの初期化 /* 正規化.css */ html{ 行の高さ: 1...

CSS3 背景コントロールプロパティと色遷移を使用してグラデーション効果を実現します。

CSS3 背景画像関連互換性: IE9+背景クリップ 背景画像描画領域background-cli...

Windows 10 に Apache 2.4.41 をインストールするチュートリアル

1. Apache 2.4.41 のインストールと設定最初のステップは、以下に示すように、https...

Win10にnginxをインストールして設定するプロセス

1. はじめにNginx は、無料のオープンソースの高性能 HTTP サーバーおよびリバース プロキ...

jQueryは記事の折りたたみと展開の機能を実装します

この記事の例では、記事の折りたたみと展開の機能を実現するためのjQueryの具体的なコードを参考まで...

HTML Webページの例を使用してヘッドエリアコードの意味を説明する

例を使って、Webページのヘッダー情報の意味を理解しましょう。 <!DOCTYPE HTML ...