MySqlの整数インデックスと文字列インデックスの失敗または暗黙の変換問題

問題の概要

今日、職場でDBAが突然、SQLの中にインデックスを経由しない暗黙の変換があることを示す部分を発見しました。調べてみると、それはvarchar型のフィールドで、条件を使って数値を渡していました。NDAに抵触する恐れがあるので、ここに画像は載せませんが、似たような状況を再現して見ます。

問題の再現

まず、ユーザー・テーブル test_user を作成し、USER_ID を varchar に設定し、効果的なユニーク・インデックスを作成します。

CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID varchar(11) DEFAULT NULL COMMENT 'user account',
  USER_NAME varchar(255) DEFAULT NULL COMMENT 'User Name',
  AGE int(5) DEFAULT NULL COMMENT 'Age',
  COMMENT varchar(255) DEFAULT NULL COMMENT 'Profile',
  PRIMARY KEY (ID)
  UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

テーブルのデータは以下の通りです（へへっ データは前回のMysqlの記事をそのまま使用しています）。 MySQLがUNIONを使って2つのクエリを結合する際のソート失敗について (同じデータですが、テーブルの構造が違うので注意してください)

次に、以下のSQLを実行します。

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

与えられた解釈は次のようなものであることがわかった。

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

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

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

質問の引き出し

そこで問題なのは、フィールドが整数でインデックス化されている場合、文字列としてクエリしたときにインデックス化されないか、ということです。続けてこれを検証してみましょう。

-- Change the type of USER_ID to an integer
CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID int(11) DEFAULT NULL COMMENT 'user account',
  USER_NAME varchar(255) DEFAULT NULL COMMENT 'User name',
  AGE int(5) DEFAULT NULL COMMENT 'Age',
  COMMENT varchar(255) DEFAULT NULL COMMENT 'Profile',
  PRIMARY KEY (ID),
  UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

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

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

結論

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

要約すると、私はあなたがこの文字列型は、状況のインデックスに行かない避けるために引用符を追加するすべての注意を払うときに、将来的にSQLを記述する方が良いと思う、深い原則は、もう少し掘りする必要があります、任意のコメントを議論することができる場合。

MySql整数インデックスと文字列インデックスの障害または暗黙の変換問題についてのこの記事は、このに導入され、より関連するMySql整数インデックスと文字列インデックスの障害の内容はBinaryDevelopの以前の記事を検索してくださいまたは次の関連記事を閲覧し続けるあなたは将来的にBinaryDevelopをよりサポートします願っています！.