はじめに
「MySQLは単一テーブルに1つのインデックスしか使えない」は実は正確ではありません。条件によっては2つのインデックスを合成して使うインデックスマージが発生します。
また ORDER BY + LIMIT の組み合わせでは、複合インデックスを使うことで Using filesort（追加ソート）を消せます。
今回は両方を実際に EXPLAIN で確認しました。
環境構築
Docker で MySQL 8.0 を起動
docker run -d \
--name mysql-practice \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306 \
mysql:8.0
world データベースをインポート
curl -L https://downloads.mysql.com/docs/world-db.tar.gz -o world-db.tar.gz
tar xzf world-db.tar.gz
docker exec -i mysql-practice mysql -uroot -proot < world-db/world.sql
インデックスマージ最適化
インデックスを追加して確認
USE world;
ALTER TABLE city ADD INDEX idx_name (Name);
ALTER TABLE city ADD INDEX idx_population (Population);
EXPLAIN SELECT * FROM city
WHERE Name = 'Tokyo' OR Population > 5000000;
+----+-------+-------------+-------------+-------------------------+-------------------------+----------+------+----------+--------------------------------------------------------+
| id | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------+-------------+-------------+-------------------------+-------------------------+----------+------+----------+--------------------------------------------------------+
| 1 | city | index_merge | idx_population,idx_name | idx_name,idx_population | 140,4 | NULL | 25 | 100.00 | Using sort_union(idx_name,idx_population); Using where |
+----+-------+-------------+-------------+-------------------------+-------------------------+----------+------+----------+--------------------------------------------------------+
読み解き
| カラム | 値 | 意味 |
|---|---|---|
type |
index_merge |
2つのインデックスを合成して使っている |
key |
idx_name,idx_population |
両方のインデックスが使われた |
Extra |
Using sort_union(...) |
2つのインデックス結果をソートしてマージした |
OR 条件なので2つの条件を別々にインデックスで検索してから結果を合成しています。
① idx_name で Name='Tokyo' にマッチする行を取得
② idx_population で Population>5000000 にマッチする行を取得
③ 両方の結果をソートして重複を除いてマージ
インデックスマージの種類
| 種類 | 発生条件 | 内容 |
|---|---|---|
sort_union |
OR条件 | 各インデックスの結果をソートしてマージ |
union |
OR条件（range同士） | ソートなしでマージ |
intersect |
AND条件 | 各インデックスの結果の積集合 |
注意点
インデックスマージより複合インデックスの方が効率的なことが多いです。また UPDATE / DELETE でインデックスマージが起きると両方のインデックスレコードがロックされてロック範囲が広がります。
-- 後片付け
ALTER TABLE city DROP KEY idx_name;
ALTER TABLE city DROP KEY idx_population;
ORDER BY LIMIT最適化
インデックスなしの状態で確認
EXPLAIN SELECT * FROM country
WHERE Continent = 'Asia'
ORDER BY Population DESC
LIMIT 10;
+----+-------+------+---------------+---------------+---------+-------+------+----------+---------------------------------------+
| id | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------+------+---------------+---------------+---------+-------+------+----------+---------------------------------------+
| 1 | country | ref | idx_continent | idx_continent | 1 | const | 51 | 100.00 | Using index condition; Using filesort |
+----+-------+------+---------------+---------------+---------+-------+------+----------+---------------------------------------+
Using filesort が出ています。Continent のインデックスで絞り込んだあと、Population での追加ソートが発生しています。
単独インデックスでは解決できない
ALTER TABLE country ADD INDEX idx_population (Population);
EXPLAIN SELECT * FROM country
WHERE Continent = 'Asia'
ORDER BY Population DESC
LIMIT 10;
-- 結果は変わらず Using filesort のまま
WHERE と ORDER BY が別々のカラムにあるため、単独インデックスでは解決できません。Continent で絞り込んでから Population でソートするという2ステップが必要です。
複合インデックスで解決する
ALTER TABLE country DROP KEY idx_population;
ALTER TABLE country ADD INDEX idx_continent_population (Continent, Population);
EXPLAIN SELECT * FROM country
WHERE Continent = 'Asia'
ORDER BY Population DESC
LIMIT 10;
+----+-------+------+----------------------------------------+--------------------------+---------+-------+------+----------+----------------------------------+
| id | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------+------+----------------------------------------+--------------------------+---------+-------+------+----------+----------------------------------+
| 1 | country | ref | idx_continent,idx_continent_population | idx_continent_population | 1 | const | 51 | 100.00 | Using where; Backward index scan |
+----+-------+------+----------------------------------------+--------------------------+---------+-------+------+----------+----------------------------------+
Using filesort が消えました。
Backward index scan とは
idx_continent_population は (Continent, Population) の順で昇順にソートされています。今回は ORDER BY Population DESC（降順）なので、インデックスを逆方向にたどることでソートなしに降順の結果が得られます。これが Backward index scan です。
インデックスの並び：ASC（小 → 大）
ORDER BY DESC → 逆方向にたどるだけでOK（ソート不要）
MySQL 8.0以降で導入された最適化です。
結果の比較
| 単独インデックス | 複合インデックス | |
|---|---|---|
key |
idx_continent |
idx_continent_population |
Extra |
Using filesort |
Backward index scan |
| ソート | 追加ソートあり | インデックスで解決 |
まとめ
インデックスマージ
-
OR条件で複数インデックスがsort_unionで合成されることがある（type: index_merge） - ただし複合インデックスの方が効率的なことが多い
-
UPDATE/DELETEでインデックスマージが起きるとロック範囲が広がる
ORDER BY LIMIT最適化
-
WHERE+ORDER BYの両方を複合インデックスにまとめるとUsing filesortが消える -
ORDER BY DESCの場合はBackward index scanでインデックスを逆方向にたどって解決 - Railsで
.order(population: :desc).limit(10)のようなクエリに直結する