MySQL クエリのパフォーマンス改善テクニック -その10- 型変換を回避しろ

clown

2024年2月4日 20:31

フルスタックエンジニア（）な clown の note にようこそ。

はじめに

今回やること

MySQL のクエリパフォーマンス改善のひとつとして、今回は型変換を回避する方法を扱います。

対象者

RSDB を使ってて、ユーザから重いって言われてるあなた
DB 担当になったけど、何から手を付けていいかわかんないあなた
MySQL を使っているあなた（他 DB でも基本はそんなに違わないです）

ざっくり説明

今日も DBMS と格闘してますか？

正直、クエリなんて究極は動けばいいんですけど、暗黙的に型変換をさせないようにデータを入力するかしないかだけで、パフォーマンスに差が出ます。

あなたの書いたこのクエリ。型変換ばっかりしてて激重だよね？

なんていうお小言を貰っちゃったりします。
そんなこと言われたってどうしたら…
なんて言わずに

型変換をさせないクエリを書けばいいじゃない。

というのが今回の話。
そんなことも知らないの？とか言われるのが嫌！
クエリが遅いのは判ってるのに、なんら手をうてない自分が嫌！
という悩みを解決するのがこの記事です。

準備

下記のテーブルを用意します。
前段となる「MySQL で explain してますか？」の記事を読んでいただくと、より味わいが深くなります。

CREATE TABLE `sumple_users` (
    `id`         int(11)      NOT NULL AUTO_INCREMENT
   ,`username`   varchar(64)  NOT NULL DEFAULT ''
   ,`email`      varchar(255) NOT NULL
   ,`password`   varchar(255) NOT NULL
   ,`login_hash` varchar(255) NOT NULL DEFAULT ''
   ,`created_at` timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ,`updated_at` timestamp    NOT NULL DEFAULT '0000-00-00 00:00:00'
   ,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

このテーブルには、こんなデータが入っています。

mysql> select * from `sumple_users`;
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
| id    | username   | email                | password | login_hash | created_at          | updated_at          |
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
|     1 | clown1     | clown1@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     2 | clown2     | clown2@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     3 | clown3     | clown3@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     4 | clown4     | clown4@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     5 | clown5     | clown5@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |

～
中略
～

|  9995 | clown9995  | clown9995@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9996 | clown9996  | clown9996@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9997 | clown9997  | clown9997@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9998 | clown9998  | clown9998@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9999 | clown9999  | clown9999@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 10000 | clown10000 | clown10000@email.com | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
10000 rows in set (0.01 sec)

型変換をさせない

型変換ってなぁに？

変数の型を別の型に変換すること。

変数は、値を入れておくことができる箱。とでもイメージしてください。
この箱には、どういう種類のモノを入れられるのかを決めてあります。
果物と書いてあれば、果物だけを入れることができます。
麺類と書いてあれば、麺類だけを入れることができます。
それが変数。（ちょっと過言）

果物を入れる箱に麺類を入れようとすると、基本的に怒られます。
が、果物を入れる箱に貼ってあるラベルを消して、麺類と上書きすれば麺類を入れても怒られません。
これが型変換。（ちょっと過言）

ということは？

MySQL では、カラム定義で指定した型に合うように値を変換してくれます。
前述の準備で作った `sumple_users` テーブルにある `username` カラムは varchar 型で定義しました。
ここに数値（数字ではない）を当てはめて走査するとしたらこんな感じになります。

SELECT * FROM `sumple_users` WHERE username = 1;

一見間違ってないように見えますが、思い出してください。数値を当てはめようとしています。数字であるなら、「'1'」と書かなければなりません。
そう、クォーテーションを付与しているかいないか。この差が大きいんです。
この「1」という数値を「'1'」という文字型に一度変換する作業がバックグラウンドで発生するのでパフォーマンスが落ちます。
今回例示したレベルだと問題ないですが、大量のレコードを抽出するとなったら、レコードの量に比例してオーバーヘッドになるわけです。

さらに…

インデックスも効かなくなります。
なので、型に合わせたデータを保存したり抽出クエリで書いてあげるべきなのです。
今回で言えば悪い例は

SELECT * FROM `sumple_users` WHERE username = 1;

良い例は

SELECT * FROM `sumple_users` WHERE username = '1';

となります。
この型変換を意識できるかできないかだけで、クエリのパフォーマンスが変化しますので気を付けてクエリを書いてあげてください。