優化 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，如下~

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，如下~

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

—

實際上測試如下

簡單重現，使用 range(1, 50000) 執行 whereIn()，前三次花費時間為 2.4x 秒

後三次改用 whereIntegerInRaw() 進行查詢，可以看見大幅降低所需時間至 0.2~0.3 秒

使用限制

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

• 參數只能是 integers
• 要注意 SQL injection