はじめに
LaravelでWebアプリケーション開発を開発する際、クエリビルダを使ってクエリを組み立てる場面は多々あります。
クエリビルダのようなORマッパーを使うと、プログラミング言語でコードを書くときと同じ感覚でSQLを記述することができるため、とても便利です。
一方で、このようにSQLを気軽に書けてしまうからこそ、クエリビルダでクエリを作る際には、SQLにおける細かい挙動を意識しづらくなることがあります。
その中でも本記事で取り上げるのは、「データ型のミスマッチによるインデックスへの影響」です。
1. インデックスは張って終わりじゃない
データ検索を高速にしてくれるインデックス。しかし、いつでもその効果が得られるとは限りません。
クエリの書き方によっては、インデックスが効かなくなるケースもあります。
つまり、インデックスは、単に作成して終わりではなく、正しく使うことが重要ということです。
具体的なケースは、下記の記事などに詳しく書かれていますので、よければ見てみてください。
この記事ではとりわけ、「データ型の不一致」によってインデックスが効かない場合について、ORMを用いて実践的にその挙動を調べてみます。
2. MySQLでのデータ型不一致時の挙動
クエリビルダの話へ入る前に、そもそもMySQLにおいて、データ型が異なることによるインデックスの効果がどうなのかについて。
既にわかりやすくまとめられている記事があるので、本記事では省略させてください。
確かに、インデックスが張られているカラムのデータ型とは異なるデータ型を使った場合に、インデックスを用いた検索が行われていないことがわかるかと思います。
3. クエリビルダでのデータ型不一致時の挙動
この記事のメインテーマです。
では、クエリビルダによってクエリを作成する場合にも、このデータ型の問題は意識する必要があるのでしょうか？
クエリビルダないしORマッパーといえば、プログラミング言語上でSQLを扱いやすくしてくれる便利なツールというイメージがあり、この辺りの細かい挙動はよろしくやってくれそうな気がします。
が、実際はどうなのか？
"百聞は一見に如かず"ということで、早速試してみましょう。
3-1. データの準備
ECサイトアプリでの顧客の購入履歴を想定して、簡単なテーブルを用意します。
purchase_historiesのテーブル定義
| カラム名 | 型 | インデックス |
|---|---|---|
| id | BigInt | Primary |
| user_id | BigInt | - |
| product_code | VARCHAR(200) | idx_product_code |
| purchase_date | date | - |
product_codeカラムにインデックスを張っています。
今回はこのカラムに対して、文字列で検索した場合と、数値で検索した場合とを比較します。
（それ以外のカラムは、雰囲気のために用意しています。特に使用しないです。）
インデックスの効果がわかりやすいように、30万レコードを用意します。
マイグレーションとシーダーを使ってデータを用意します。
マイグレーション
上記のテーブル定義に合わせて、Laravelのマイグレーションを用いてテーブルを用意します。
public function up(): void
{
Schema::create('purchase_histories', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->string('product_code', 200);
$table->date('purchase_date');
$table->index('product_code', 'idx_product_code');
});
}
public function down(): void
{
Schema::dropIfExists('purchase_histories');
}
データ作成
Seederを利用して、データを用意します。
class PurchaseHistorySeeder extends \Illuminate\Database\Seeder
{
public function run(): void
{
$batchSize = 500;
$total = 300000;
for ($i = 0; $i < $total; $i += $batchSize) {
$data = [];
for ($j = 0; $j < $batchSize; $j++) {
$data[] = [
'user_id' => rand(1, 10000),
'product_code' => (string)rand(1000, 9999),
'purchase_date' => now()->subDays(rand(0, 365)),
];
}
DB::table('purchase_histories')->insert($data);
}
}
}
3-2. カラムの型 = 検索値のデータ型 の場合
まずは、インデックスが張られているカラムの型と同じデータ型を用いて、クエリビルダを実行してみます。
実行内容
- 検索したいproduct_codeを文字列型で10個、配列として用意
- この配列を使って、whereInでデータ取得
- 実行時間を確認
これを10回計測します。
for ($i = 0; $i < 10; $i++) {
$productCodesStr = array_map(fn() => (string) rand(1000, 9999), range(1, 10));
$start = microtime(true);
$purchaseHistories = DB::table('purchase_histories')
->whereIn('product_code', $productCodesStr)
->get();
$time = microtime(true) - $start;
echo "Query #{$i} took " . number_format($time * 1000, 2) . " ms\n";
}
実行結果
1回目は、接続確立や初期化などの都合で時間がかかっていると思うので無視するとして、平均 2ms くらいです。
Query #0 took 26.42 ms
Query #1 took 4.36 ms
Query #2 took 2.34 ms
Query #3 took 2.29 ms
Query #4 took 1.73 ms
Query #5 took 1.75 ms
Query #6 took 1.35 ms
Query #7 took 1.49 ms
Query #8 took 1.48 ms
Query #9 took 1.55 ms
3-3. カラムの型 != 検索値のデータ型 の場合
次に、インデックスが張られているカラムの型とは異なる、数値からなる配列を使って同じようにクエリビルダを実行してみましょう。
実行内容
- 配列の要素が数値型であること以外、処理は同じです
for ($i = 0; $i < 10; $i++) {
$productCodesInt = array_map(fn() => rand(1000, 9999), range(1, 10));
$start = microtime(true);
$purchaseHistories = DB::table('purchase_histories')
->whereIn('product_code', $productCodesInt)
->get();
$time = microtime(true) - $start;
echo "Query #{$i} took " . number_format($time * 1000, 2) . " ms\n";
}
実行結果
こちらも同じく1回目は無視すると、平均 35ms ほどです。
文字列型の時よりも明らかに遅くなっていることがわかります。
Query #0 took 59.06 ms
Query #1 took 35.33 ms
Query #2 took 34.79 ms
Query #3 took 34.21 ms
Query #4 took 35.49 ms
Query #5 took 34.90 ms
Query #6 took 35.89 ms
Query #7 took 35.03 ms
Query #8 took 34.54 ms
Query #9 took 33.84 ms
3-4. 発行されたSQLの確認
ここまでの動作確認で、おそらくインデックスが使われていないことは明確です。
ただ、念の為、実行されたSQLが実際にどうなっていたのかも確認してみます。
文字列型の場合
bindingsが確かに文字列になっています。
$productCodesStr = array_map(fn() => (string) rand(1000, 9999), range(1, 10));
$query = DB::table('purchase_histories')
->whereIn('product_code', $productCodesStr);
dump([
'query' => $query->toSql(),
'bindings' => $query->getBindings(),
]);
// ↓↓
array:2 [
"query" => "select * from `purchase_histories` where `product_code` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
"bindings" => array:10 [
0 => "3716"
1 => "7762"
2 => "9102"
3 => "8170"
4 => "2298"
5 => "2476"
6 => "7520"
7 => "9535"
8 => "1203"
9 => "6284"
]
]
数値型の場合
こちらは、bindingsが数値になっています。
$productCodesInt = array_map(fn() => rand(1000, 9999), range(1, 10));
$query = DB::table('purchase_histories')
->whereIn('product_code', $productCodesInt);
dump([
'query' => $query->toSql(),
'bindings' => $query->getBindings(),
]);
// ↓↓
array:2 [
"query" => "select * from `purchase_histories` where `product_code` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
"bindings" => array:10 [
0 => 2976
1 => 7929
2 => 4619
3 => 9769
4 => 8227
5 => 5273
6 => 5776
7 => 2189
8 => 1058
9 => 8269
]
]
3-5. EXPLAINで実行計画の確認
最後に、実行計画を確認してみます。
文字列型の場合
type = range となっており、想定通りインデックスを使った検索が行われていることがわかります。
$productCodesStr = array_map(fn() => (string) rand(1000, 9999), range(1, 10));
$query = DB::table('purchase_histories')
->whereIn('product_code', $productCodesStr);
$explain = DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings());
dump(['explain' => $explain]);
// ↓↓
array:1 [
"explain" => array:1 [
0 => {#6022
+"id": 1
+"select_type": "SIMPLE"
+"table": "purchase_histories"
+"partitions": null
+"type": "range"
+"possible_keys": "idx_product_code"
+"key": "idx_product_code"
+"key_len": "1022"
+"ref": null
+"rows": 335
+"filtered": 100.0
+"Extra": "Using index condition"
}
]
]
数値型の場合
type = ALL となっており、フルテーブルスキャンとなってしまっていることがわかります。
$productCodesInt = array_map(fn() => rand(1000, 9999), range(1, 10));
$query = DB::table('purchase_histories')
->whereIn('product_code', $productCodesInt);
$explain = DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings());
dump(['explain' => $explain]);
// ↓↓
array:1 [
"explain" => array:1 [
0 => {#5222
+"id": 1
+"select_type": "SIMPLE"
+"table": "purchase_histories"
+"partitions": null
+"type": "ALL"
+"possible_keys": "idx_product_code"
+"key": null
+"key_len": null
+"ref": null
+"rows": 299523
+"filtered": 50.0
+"Extra": "Using where"
}
]
]
4. まとめ - やっぱりクエリビルダでもデータ型には気をつけよう
以上の結果から、クエリビルダを使う場合でも、データ型の不一致によってインデックスの使用有無が変化するということがわかりました。
何気なくソースコードを書いていると、変数のデータ型とカラムのデータ型との対応まで意識されないこともあるかと思います。
その結果、なぜか処理速度が低下してしまうなんていうことも。
そうならないためには、クエリビルダの便利さに乗っかりすぎず、「裏側ではどういうSQLが組み立てられるのだろうか」とイメージしながら実装していくことが大事だと思います。
5. おまけ - 文字列と数値が混ざった場合
何となく結果はわかりきっていると思いますが、文字列と数値とが両方含まれている場合はどうなるのでしょうか？
$productCodesInt = array_map(fn() => rand(1000, 9999), range(1, 5));
$productCodesStr = array_map(fn() => (string) rand(1000, 9999), range(1, 5));
$query = DB::table('purchase_histories')
->whereIn('product_code', [...$productCodesInt, ...$productCodesStr]);
$explain = DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings());
dump(['explain' => $explain]);
// ↓↓
array:1 [
"explain" => array:1 [
0 => {#5232
+"id": 1
+"select_type": "SIMPLE"
+"table": "purchase_histories"
+"partitions": null
+"type": "ALL"
+"possible_keys": "idx_product_code"
+"key": null
+"key_len": null
+"ref": null
+"rows": 299523
+"filtered": 50.0
+"Extra": "Using where"
}
]
]
皆さんの想像通り、インデックスは使用されませんでした。
やはり、データ型は常に意識することが大切ですね。