MySQLインデックスが効かない？初心者向けデータベース高速化の落とし穴と解決策

1. インデックスとは何か？（辞書の索引で例えると）

1. インデックスとは何か？（辞書の索引で例えると）

データベース（情報を整理して保存する巨大な箱）を扱うとき、避けて通れないのが「パフォーマンス」の問題です。データが数万件、数百万件と増えていくと、目的のデータを探すのに時間がかかるようになります。これを解決するのが「インデックス」です。

身近な例でいうと、厚さ10センチもある国語辞典を想像してください。「データベース」から特定の言葉を探すとき、もし索引がなければ、あなたは1ページ目から順番にめくって探さなければなりません。これを専門用語で「フルスキャン」と呼びます。

一方、巻末の「さ行」から「最適化」という言葉を見つけて、そのページに直接飛ぶのが「インデックス」を使った検索です。これにより、検索スピードは劇的に向上します。しかし、この便利な索引も、使い勝手が悪いと無視されてしまいます。

2. ケース1：検索条件で「計算や加工」をしている場合

2. ケース1：検索条件で「計算や加工」をしている場合

初心者が最もやってしまいがちなのが、検索する対象のデータに対して計算や加工を施してしまうことです。データベースは「そのままの形」でインデックスを管理しているため、少しでも加工されると、どの索引を使えばいいか分からなくなってしまいます。

例えば、会員の年齢（age）にインデックスが貼ってあるテーブルがあるとします。

id | name      | age | email
---+-----------+-----+-------------------
1  | 山田太郎   | 25  | taro@example.com
2  | 佐藤花子   | 19  | hanako@example.com
3  | 鈴木一郎   | 30  | ichiro@example.com
4  | 田中次郎   | 42  | tanaka@example.com
5  | 伊藤美紀   | 28  | miki@example.com

ここで、「現在の年齢に5を足したら30歳以上の人を検索したい」という命令を、以下のように書いてみましょう。

SELECT *
FROM users
WHERE age + 5 >= 30;

この書き方では、MySQLは「age + 5」の結果をすべての行で計算し直さなければならないため、インデックスを使いません。解決するには、以下のように右側の数字を計算する形にします。

SELECT *
FROM users
WHERE age >= 25;

実行結果は以下のようになります。

id | name      | age | email
---+-----------+-----+-------------------
1  | 山田太郎   | 25  | taro@example.com
3  | 鈴木一郎   | 30  | ichiro@example.com
4  | 田中次郎   | 42  | tanaka@example.com
5  | 伊藤美紀   | 28  | miki@example.com

3. ケース2：LIKE検索で「後方一致」や「部分一致」を使っている場合

3. ケース2：LIKE検索で「後方一致」や「部分一致」を使っている場合

文字列を検索するときに使う「LIKE（〜のようなもの）」という命令も注意が必要です。特に、文字の先頭に「%（ワイルドカード）」を付けると、インデックスは使われません。

辞書の索引で「『データベース』という言葉を探してください」と言われればすぐ見つかりますが、「『ース』で終わる言葉を全部探してください」と言われたらどうでしょうか？結局、辞書を最初から最後まで見て、末尾が「ース」になっているものを探すしかありませんよね。

id | name      | department
---+-----------+------------
1  | 山田太郎   | 営業部
2  | 佐藤花子   | 開発部
3  | 鈴木一郎   | 営業企画部
4  | 田中次郎   | 総務部
5  | 伊藤美紀   | 開発二部

以下のような「後ろ側が一致するものを探す」書き方は、インデックスが効きません。

SELECT *
FROM employees
WHERE department LIKE '%部';

逆に「開発%」のように、先頭が決まっている「前方一致」であれば、インデックスを活用して素早く検索できます。

4. ケース3：データの型が一致していない場合

4. ケース3：データの型が一致していない場合

データベースには、数字を入れる「数値型」や、文字を入れる「文字列型」という区別があります。この「型」が検索条件と一致していないと、MySQLは内部でこっそり型変換を行いますが、その代償としてインデックスが使われなくなります。

例えば、商品ID（product_id）が「文字列型（VARCHAR）」で定義されているのに、検索時に「数値」として指定してしまうケースです。

id | product_id | product_name
---+------------+--------------
1  | 0001       | ノート
2  | 0002       | ペン
3  | 0003       | 消しゴム
4  | 1001       | ハサミ

-- product_idが文字列型なのに、数値として検索（インデックス不可）
SELECT *
FROM products
WHERE product_id = 1001;

このように書くと、データベースは「0001という文字を数字の1に変換して比較しよう」と頑張ってしまいますが、その過程で索引が使えなくなります。正しくは、以下のように引用符（'）で囲って、文字として指定する必要があります。

-- 正しい書き方（インデックスが効く）
SELECT *
FROM products
WHERE product_id = '1001';

id | product_id | product_name
---+------------+--------------
4  | 1001       | ハサミ

5. ケース4：否定形（IS NOT NULLや!=）を使っている場合

5. ケース4：否定形（IS NOT NULLや!=）を使っている場合

「〜ではないもの」という探し方も、基本的にはインデックスが苦手な分野です。「佐藤さん以外の人」を探すとき、索引には「佐藤さん」の場所は書いてありますが、「佐藤さん以外」の場所はまとまって書かれていないからです。

特に、値が空っぽであることを示す「NULL」を否定する「IS NOT NULL」などは、データの大部分が該当する場合、MySQLが「索引を見るより全部見たほうが早い」と判断してフルスキャンを選択します。

id | task_name  | status
---+------------+---------
1  | 企画作成    | 完了
2  | 資料修正    | NULL
3  | 打ち合わせ  | 進行中
4  | 報告書提出  | NULL

-- 否定形はインデックスが効きにくい
SELECT *
FROM tasks
WHERE status IS NOT NULL;

6. インデックスを有効活用するために大切なこと

6. インデックスを有効活用するために大切なこと

インデックスを貼ったのに効果が出ないときは、まず「EXPLAIN文」というものを使ってみましょう。これは、MySQLに「この命令をどうやって実行するつもり？」と尋ねるための魔法の言葉です。

SQLの先頭に「EXPLAIN」と付けるだけで、インデックスが使われているかどうかの計画書を表示してくれます。ここで「type」という項目が「ALL」になっていたら、残念ながらインデックスが無視されて全データを確認している状態です。

データベースの最適化は、パズルに似ています。コンピュータがどのようにデータを探しているのかを想像しながら、命令文を「コンピュータが読みやすい形」に整えてあげることが、高速なシステムを作る第一歩です。