はじめに
こんにちは、menu事業部サーバーサイドエンジニアの新田です。
「あれ？このSQL、ちょっと気になるな…」先日、PRで指摘した小さな違和感。それが、MySQLのパフォーマンスを大きく左右するかもしれない、意外な挙動の発見につながりました。
今回は、MySQL 5.7 における暗黙的な型変換というテーマに焦点を当て、varchar(128) 型と bigint 型のカラムに対して、誤って異なる型の値でクエリを実行してしまった場合に何が起こるのか、詳しく解説します。
特に、インデックスが機能しなくなる理由を深掘りすることで、より安全で効率的なSQLの書き方を考えていきたいと思います。
varchar(128) 型カラムへの数値比較
varchar(128) 型の user_id カラムを持つテーブル users を例に考えます。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id VARCHAR(128) NOT NULL,
age INT NOT NULL,
INDEX user_id_idx (user_id)
);
この user_id カラムに対して、シングルクォートを外して数値として比較するクエリを発行してみましょう。
SELECT * FROM users WHERE user_id = 123;
このクエリでは、user_id カラムが varchar(128) 型であるにも関わらず、数値 123 と比較しています。この時、MySQL は user_id カラムの値を数値に暗黙的に変換しようとします。
具体的には、user_id の値の先頭から数値として解釈できる部分を取り出し、数値に変換します。例えば、user_id が '123abc' の場合、'123' が数値に変換されます。
もし、先頭が数値として解釈できない場合（'abc123' など）は、0に変換されます。
この変換により、インデックス user_id_idx は 元の文字列ではなく、変換された数値に対して 作用することになります。
そのため、インデックスが効かなくなり、全テーブルスキャンが発生してしまいます。
explain SELECT * FROM users WHERE user_id = 123;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
type カラムが ALL になっていることから、全テーブルスキャンが発生していることがわかります。
bigint 型カラムへの文字列比較
今度は、bigint 型の user_id カラムを持つテーブル orders を例に考えます。
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
INDEX user_id_idx (user_id)
);
この user_id カラムに対して、シングルクォートをつけて文字列として比較するクエリを発行してみましょう。
SELECT * FROM orders WHERE user_id = '123';
このクエリでは、user_id カラムが bigint 型であるにも関わらず、文字列 '123' と比較しています。
この場合も、MySQL は user_id カラムの値を文字列に暗黙的に変換します。
しかし、bigint 型の値を文字列に変換する場合は、元の数値の情報が完全に保持されます。
例えば、user_id が 123 の場合、変換後の文字列は '123' となり、元の値と一致します。
そのため、インデックス user_id_idx は有効に機能し、高速に検索することができます。
explain SELECT * FROM orders WHERE user_id = '123';
+----+-------------+--------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | const | user_id_idx | user_id_idx | 8 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
type カラムが const になっていることから、インデックスが有効に利用され、高速に検索できていることがわかります。
まとめ
MySQL 5.7 では、型違いのクエリを発行した場合でも、暗黙的な型変換によってインデックスが効く場合があります。
しかし、varchar 型カラムに対して数値で比較する場合は、値の変換によりインデックスが正しく機能しなくなるため注意が必要です。
クエリのパフォーマンスを向上させるためには、カラムの型と一致する型の値でクエリを発行することが重要です。
どうしても型違いのクエリを発行する必要がある場合は、CAST 関数などを使用して明示的に型変換を行うようにしましょう。
▼採用情報
レアゾン・ホールディングスは、「世界一の企業へ」というビジョンを掲げ、「新しい"当たり前"を作り続ける」というミッションを推進しています。
現在、エンジニア採用を積極的に行っておりますので、ご興味をお持ちいただけましたら、ぜひ下記リンクからご応募ください。