大規模なデータを持つ MySQL でクエリを発行する際は速度に気を付ける必要があります。計算量が大きいクエリはユーザーを長く待たせます。これの対策としてインデックスで並び替えや探索の回数を減らすという方法があります。インデックスですが、使われているか使われていないかは explain で見ることができます。explain select ... といったことをすると次の様に MySQL がどのようにクエリを実行するかの計画が表示されます。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | items | null | ALL | PRIMARY,items_item_id_status_index | null | null | null | 6 | 16.67 | Using where |
| 1 | SIMPLE | ic | null | ref | item_categories_item_id_category_id_unique,item_categories_item_id_index,item_categories_category_id_foreign | item_categories_item_id_category_id_unique | 8 | hoge_web.items.item_id | 3 | 100 | null |
| 1 | SIMPLE | cm | null | eq_ref | PRIMARY | PRIMARY | 8 | hoge_web.ic.category_id | 1 | 100 | null |
この機能自体は便利なのですが、問題がありそうなクエリに対して都度実行するのは手間です。特にプログラム中の全クエリで適切にインデックスが使われているか否かを検査する目的では現実的でありません。自動的に explain したくなります。この自動的に explain する機能ですは PHP のフレームワークである Laravel で既にあります。
sql-dumper は Laravel 用のライブラリで、sql-dumper のコードでくくった範囲で実行された SQL の explain を自動でしてくれます。これは例えば次の様にできます。
// app/Http/Kernel.php
protected $routeMiddleware = [
'sql.dump' => \Cerbero\SqlDumper\Http\Middleware\SqlDump::class,
];
// app/Providers/RouteServiceProvider.php
Route::prefix('api')
->middleware('api')
// ミドルウェアとして使うとそのルーティングの中で実行されたクエリ全部の explainをログに残せます
->middleware('sql.dump')
->namespace($this->namespace)
->group(base_path('routes/api.php'));
設定ファイルはphp artisan vendor:publish --tag=sql-dumperでプロジェクト内に出力でき、例えば次の様にできます。
<?php
use Cerbero\SqlDumper\Dumpers\EmailDumper;
use Cerbero\SqlDumper\Dumpers\HtmlDumper;
use Cerbero\SqlDumper\Dumpers\MarkdownDumper;
return [
/*
|--------------------------------------------------------------------------
| Default SQL dumper
|--------------------------------------------------------------------------
|
| 未指定時に使われる出力方法の設定。初期設定は HtmlDumper::class。
| Cerbero\SqlDumper\Dumpers\DumperInterface を満たした独自クラスも設定できます。
| The class name of the dumper that is invoked by default when dumping SQL
| statements by calling the `ds()` helper function. By default the HTML
| dumper is used, but feel free to pick any of the available dumpers
|
*/
'default_dumper' => HtmlDumper::class,
/*
|--------------------------------------------------------------------------
| Dumpers fine-tuning
|--------------------------------------------------------------------------
|
| 各クラスの出力方法の細かい部分です。
| path => now()->format('YmdHis'). 'なんとかかんとか.log' の様にすると日時単位でログが分割されます。
| Some dumpers rely on sensible defaults to run properly without requiring
| the end user to set parameters manually. However you can set your own
| values here by defining the dumpers and the parameters to override
|
*/
HtmlDumper::class => [
// The path of the HTML file to generate e.g. storage_path('foo.html')
'path' => null,
// The path of the template that the HTML file should use
'template' => null,
],
MarkdownDumper::class => [
// The path of the markdown file to generate e.g. storage_path('foo.md')
'path' => null,
],
EmailDumper::class => [
// The path of the HTML template that the email should use
'template' => null,
// The email recipient. If not specified, the configuration "mail.from.address" is used
'recipient' => null,
// Whether the email should be queued
'queue' => false,
],
];
これを使うと次の様に各 SQL の explain 結果を記録できます。
出力した後はフォーマットに応じて集計するなり、検索するなりすれば問題がありそうなクエリを見つけやすくなります。注意点として本番環境ではなるべく使わない方がいいという展があります。explain の実行コストはただではないため、記録をとること自体が処理の遅れにつながりやすいです。どうしても本番環境で調査すべき時はしょうがないですが、なるべく開発環境、ステージング環境での実行にとどめておいた方がいいです。
