環境
- MySQL 5.7
インデックスなき <derived2>
MySQLで遅いクエリがあり、EXPLAIN してみると、
| table | type | key |
|---|---|---|
<derived2> |
ALL | NULL |
と表示され、インデックスがあたっていないテーブルがありました。
そのテーブルの行数が多かったので、結果としてクエリ速度が遅くなっていました。
原因の把握
<derived2> とは
上の <derived2> は、FROM句のサブクエリで生成された派生テーブル (derived table) です。
下の例だと、t1 が派生テーブルになります。
SELECT * FROM (SELECT * FROM t1) AS derived_t1;
インデックスがあたらない原因
サブクエリ内で GROUP BY が用いられた場合、生成される派生テーブルにはインデックがあたらないようです。
The subquery for the derived table t1 can not be merged because it has a GROUP BY clause. Hence, MySQL 5.7 will materialize the result of this subquery, scan the resulting temporary table, and do primary-keys looks-up directly on the orders table.
「MySQL 5.7: Improved Performance of Queries with Derived Tables - MySQL Server Blog」より引用
つまり次のような場合、 derived_t1 にはインデックスはあたらない(はず)です。
SELECT price_total FROM (
SELECT SUM(price) price_total FROM t1
INNER JOIN t2 ON t2.t1_id = t1.id
GROUP BY t1.id
) AS derived_t1;
解決方法: INNER JOIN 句で GROUP BY
「サブクエリ + GROUP BY」が問題なので、 GROUP BY を INNER JOIN 句に移動させ、サブクエリをなくします。
例えば、上と同じ例を使うと、次のようなSQL文になります。
SELECT SUM(price) price_total FROM t1
INNER JOIN (
SELECT price, t1_id FROM t2
GROUP BY t1_id
) t2 ON t2.t1_id = t1.id
これでインデックスなき派生テーブルはなくなり、クエリ速度が改善されました。
まとめ
インデックスなき派生テーブルをなくすには、サブクエリはなくし、GROUP BY を INNER JOIN 句に寄せることが必要です。