優化 Laravel 中大型 whereIn 查詢緩慢問題 #

Laravel Eloquent whereIn() 每個人都用過，但你曉得但 whereIn() 量大時，可能會造成查詢緩慢問題之一嗎? 他不一定是資料 index 索引的問題，可能是更底層問題喔 這邊提供一個 Laravel 資料庫查詢效能優化的手式

Laravel 查詢緩慢的情境 #

常有的需求是使用指定的 id 取出多個使用者 (e.g. my_table.users.id)

一般透過 eloquent 可能會這樣做 (這邊先不考慮分頁問題)

User::whereIn('id', [1,2,3,4....... x100])->get()

而實際上 Eloquent 會轉換為 SQL statement，如下~ 可以看到 SQL Binding Parameter 的 ?

SELECT * FROM users WHERE id in (?, ?, ?, ?, ?, ......)

但查詢對象達到一定數量之後，會發現資料庫回應速度變慢!

查詢緩慢的問題原因 #

這是因為 Eloquent QueryBuilder 在綁定查詢參數時造成的，他不是 Laravel 的問題而是 PHP Data Objects (PDO) 一個古老的 Bug，PDO 在進行參數綁定時緩慢造成的 (可以想象他要依序綁上上百萬的參數...)

在 Laravel. 5.7 之後可以改用 whereIntegerInRaw 替換 whereIn() 就如字面上的意思 (raw) 會直接將參數置入 sql 內，省去 PDO bindings 上參數的動作

替換後的查詢語法為：

User::whereIntegerInRaw('id', [1,2,3,4....... x100])->get()

實際的 SQL statement，如下~ 直接就能看到 RAW SQL

SELECT * FROM users WHERE id in (1, 2, 3, 4, 5, ......)

實際上測試如下 #

簡單重現，使用 range(1, 50000) 執行 whereIn()，約耗時 11x ms

$avg = [];
for ($i = 0; $i < 10; $i++) {
    $s = now();
    $c =User::query()->whereIn('id', $list)->count();
    $avg[] =  $s->diffInMilliseconds(now());
}
$r = array_sum($avg)/count($avg);

改用 whereIntegerInRaw() 進行查詢，可以看見大幅降低，耗時約 4x ms

$avg = [];
for ($i = 0; $i < 10; $i++) {
    $s = now();
    $c = User::query()->whereIntegerInRaw('id', $list)->count();
    $avg[] = $s->diffInMilliseconds(now());
}
array_sum($avg)/count($avg);

使用限制 #

當然這麼好的功能當然有些限制要注意

• 參數只能是 integers

• 要注意 SQL injection

Laravel Source Code #

• whereIn()https://github.com/laravel/framework/blob/0ac6e5592da1a172d9685591c5f82fb1fb8995fb/src/Illuminate/Database/Query/Builder.php#L1365-L1399

可以看到將透過 addBinding() 會將目標對象的 Key 逐一綁進 SQL Query 的語句中~

• whereIntegerInRaw()https://github.com/laravel/framework/blob/0ac6e5592da1a172d9685591c5f82fb1fb8995fb/src/Illuminate/Database/Query/Builder.php#L1447-L1464