MySQLの整数および文字列インデックスの無効化または暗黙的な変換に関する簡単な説明

問題の概要

今日、仕事中に、DBA が突然、SQL に暗黙的な変換があり、インデックスを使用していないことを示す SQL を発見しました。確認したところ、varchar 型のフィールドであることがわかりました。条件を使用して数値を渡しました。機密保持契約に違反する恐れがあるため、ここでは画像を投稿しません。同様の状況を再現して、皆さんに見ていただきたいと思います。

問題の再現

まず、効果を上げるために、USER_ID を varchar 型に設定し、一意のインデックスを追加したユーザーテーブル test_user を作成します。

テーブルtest_userを作成します（
  ID int(11) NOT NULL AUTO_INCREMENT、
  USER_ID varchar(11) DEFAULT NULL COMMENT 'ユーザーアカウント',
  USER_NAME varchar(255) DEFAULT NULL COMMENT 'ユーザー名',
  AGE int(5) デフォルト NULL コメント '年齢',
  COMMENT varchar(255) DEFAULT NULL COMMENT 'はじめに',
  主キー (ID)
  ユニークキー UNIQUE_USER_ID (USER_ID) BTREE 使用
)ENGINE=InnoDB デフォルト文字セット=utf8;

テーブルデータは以下のとおりです（データは前回のMySQL記事と同じデータを使用しています。MySQLはUNIONを使用して2つのクエリを接続し、ソートに失敗しますが、テーブル構造が異なることに注意してください）。

ID	ユーザーID	ユーザー名	年	コメント
1	111	ハッピールーキー	18	今日はとても幸せです
2	222	悲しい新人	21	今日は悲しい
3	333	真面目な新人	30	今日はとても深刻だ
4	444	ハッピールーキー	18	今日はとても幸せです
5	555	真面目な新人	21	今日は深刻だ

次に次のSQLを実行します

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

与えられた説明は次のようになります。

id	選択タイプ	テーブル	パーティション	タイプ	可能なキー	鍵	キーの長さ	参照	行	フィルター	余分な
1	単純	テストユーザー		全て					5		where の使用

条件を引用符で囲んで次のように説明します。

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

この時点で、varchar 型フィールドは文字列としてクエリされたときにはインデックスを使用しますが、数値型としてクエリされたときにはインデックスを使用しないことがわかりました。

問題の拡大

質問は、フィールドが整数でインデックスがある場合、文字列でクエリを実行するときにインデックスは使用されないのでしょうか?練習すれば完璧になります。引き続きテストを続けましょう。

-- USER_IDの型を整数に変更する CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT、
  USER_ID int(11) DEFAULT NULL COMMENT 'ユーザーアカウント',
  USER_NAME varchar(255) DEFAULT NULL COMMENT 'ユーザー名',
  AGE int(5) デフォルト NULL コメント '年齢',
  COMMENT varchar(255) DEFAULT NULL COMMENT 'はじめに',
  主キー（ID）、
  ユニークキー UNIQUE_USER_ID (USER_ID) BTREE 使用
) ENGINE=InnoDB AUTO_INCREMENT=6 デフォルト CHARSET=utf8;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

上記の 2 つのステートメントを実行した後、int 型フィールドは、文字列としてクエリされるか数値としてクエリされるかに関係なく、インデックスが作成されることがわかりました。

結論は

使用するフィールドが数値型の場合、引用符を追加するか追加しないか（SQLでは一重引用符と二重引用符は同じ効果があります）はインデックスの使用に影響しません。
フィールドが文字列型の場合、インデックスは引用符なしのクエリには使用できませんが、引用符付きのクエリには通常どおりインデックスを使用できます。

まとめると、文字列型がインデックス化されない状況を回避するために、今後 SQL を書くときには引用符を追加するのが最善だと思います。より深い原則についてはさらに検討する必要があります。ご意見がありましたら、ぜひ議論してください。

これで、MySql 整数インデックスと文字列インデックスの無効化または暗黙的な変換に関するこの記事は終了です。MySql 整数インデックスと文字列インデックスの無効化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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