[PHP & MySQL] 検索と詳細取得を分離&INDEXチューニングで爆速にスピードアップした

とあるECサイト案件の話。

商品の詳細情報を表示するページなのですが、開発中に書いたメモだと1クエリ0.006秒だったのものが、いつの間にか0.8秒とかでSLOWクエリに出てくるように。

開発中はテスト中でレコード件数が20万件くらいだったのが、現在は70万件くらいあるので、多少は遅くなるんだろうけど、いくらなんでも遅くなりすぎでしょ！ということで、まずは基本のindexのチューニングで緊急処理をしました。

まずはベンチマーク

まずはベンチマークを取ってみました。

DB環境

table1 => レコード数：約70万件
table2 => レコード数：約100件

もともとのクエリ

EXPLAIN SELECT `t1`.`id`,{以下省略},,, FROM `table1` AS `t1`, `table2` AS `t2` WHERE `t1`.`maker_id` = &#039;xxx&#039; AND `t1`.`maker_item_code` = &#039;xxxx&#039; AND `t1`.`status` &lt;&gt; 9999 AND `t1`.`price` &gt;0 ORDER BY `t1`.`status` DESC, `t1`.`stock` DESC

Showing rows 0 - 1 (2 total, Query took 0.7975 seconds.)
---
 id     select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  i   ALL     NULL    NULL    NULL    NULL    637073  Using where
1   SIMPLE  es  eq_ref  PRIMARY     PRIMARY     4   items.t1.store_id   1   Using index

件数が100件くらいしかないtable2のほうでINDEXを使って、肝心のtable1のほうは全件検索（type=ALL）になってます。

そもそも、WHERE句も多ければORDERもふんだんに使っているのでINDEXを使うわけがありませんし、このクエリに対応したINDEXを張るとかなりサイズが大きくなって「ほぼほぼALLと同じINDEX」になって、パフォーマンスが落ちそうです。

検索と詳細を分離してINDEXを使う

ということで、ここはMySQLのINDEXチューニングだけではどうにもならなそうなので、指示を出しているPHPプログラム側を見直す前提で、クエリを変更してみます。

INDEXを使えるように、table2との連結も外して、余計なものをそぎ落としたクエリにします。

まずは、INDEXを追加します。
本番データを傷つけないように、table1をコピーしてtable1_testを用意して、そこでテストをしています。

alter table items_v2 add index code_price (maker_item_code,price,regular_price);

続いて、ベンチマーク。

EXPLAIN SELECT `t1`.`id` FROM `table1_test` AS `t1` WHERE `t1`.`maker_id` = &#039;xxx&#039; AND `t1`.`maker_item_code` = &#039;xxxx&#039; AND `t1`.`status` &lt;&gt; 9999 AND `t1`.`price` &gt;0 ORDER BY `t1`.`status` DESC, `t1`.`stock` DESC, `t1`.`price` ASC

Showing rows 0 - 1 (2 total, Query took 0.0025 seconds.)

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  i   range   mcode_price     mcode_price     304     NULL    30  Using index condition; Using where; Using filesort

indexが効くだけで1秒未満になりました。元々から比べると約0.8秒、体感では1秒くらいスピードが違います。

しかし、このクエリだと、別の処理でそれぞれのデータを個別に持ってくる分、スクリプト側で速度が落ちる可能性があるので、そちらがどれくらいコストがかかるかを計測します。

個別データ取得コストを計測

こちらは、MySQL的には簡単です。

EXPLAIN SELECT id, code, ,,,, FROM table1_test WHERE id = X;

Showing rows 0 - 0 (1 total, Query took 0.0007 seconds.)

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  table1_test     const   PRIMARY,id,...  PRIMARY     4   const   1   NULL

プライマリーキーを使っているので当然高速です。

1アイテムあたり0.0007秒なので、仮に10アイテムでも0.007秒しか増えませんのでほぼ体感ではわからない範囲。これなら、元々のスクリプトより削減が出来そうです。

実装

ベンチマークが良さそうだったので実装します。

もともとのスクリプト

MySQL 指定の条件にマッチするデータを検索して、必要なデータも一緒に持ってくる
PHP それを元にごにょごにょごにょ

という感じでした。

並び替えや必要データの処理は、ほぼMySQL側に任せていた感じです。

新しいスクリプト

MYSQL 指定の条件にマッチするデータを検索してIDだけ持ってくる
PHP foreachとかでループしつつ、、、
MYSQL ID=xの詳細データを持ってくる「SELECT id, code, ,,,, WHERE id = X;」
PHP 持ってきたデータを元に、欲しかった形に整形
PHP それを元にごにょごにょごにょ

という感じにしました。

極力MySQL側で整形したほうがPHPプログラムとしてはシンプルなんですが、0.2秒の高速化はユーザーの体感速度では結構大きいので、ユーザーメリットを優先します。

結果

CodeIgniterのベンチマークコマンドで対象の部分を速度を計測しました。

旧スクリプト	新スクリプト
0.80秒台	0.007秒くらい

タイミングによってバラバラですが、旧スクリプトは概ね0.80秒台、新スクリプトは0.007秒くらいとなりました。

INDEXの貼り忘れくらいでこんなに速度が違ってくるとは驚きです。

データが多くなってくると挙動も変わってくるので、MySQLではSLOW QUERYの定点観測が大事だなと痛感しました。

新着記事