羅昱鈞的技術筆記：從面試戰場到 AI 實戰: 從 Laravel 到 MySQL 底層：資深後端面試與實務中，最容易踩的 24 個資料庫陷阱

作為一名 Laravel 後端工程師，我們習慣了 Eloquent 的優雅：DB::transaction() 包起來，Model::with() 載入關聯，paginate() 搞定分頁。寫起來輕快順手，但在高併發、大資料量或 Senior 面試的場合，這些「封裝好的優雅」常常會成為最致命的暗器。

面試官追問的往往不是語法怎麼寫，而是底層的運作邏輯：「資料為什麼會錯？為什麼會慢？為什麼會鎖死？索引為什麼沒生效？」

這篇文章整理了 24 個從實務到面試都極度高頻的 MySQL 陷阱，並梳理出資深工程師必須具備的資料庫思維。

一、索引（Index）的盲點與陷阱

1. 有 Index ≠ 一定會使用 Index

對欄位建了索引，不代表 MySQL 就一定吃得到：

對欄位做運算或函式處理：WHERE LOWER(email) = '...' 或 WHERE DATE(created_at) = '2026-09-18'，都會讓 B-Tree 索引失效（除非使用 MySQL 8.0+ 的 Functional Index）。
隱式型態轉換：字串欄位傳數字（例如 WHERE phone = 0912345678），MySQL 會自動呼叫轉型函式，導致全表掃描。

2. `LIKE '%keyword%'` 的 B-Tree 特性

B-Tree 索引是 由左至右 排序的。

LIKE 'iphone%'：能利用索引快速定位範圍（前綴匹配）。
LIKE '%iphone%'：搜尋任意位置，B-Tree 無法利用排序特性，直接退化成全表掃描。

擴充思考：面對模糊搜尋需求，應考慮 MySQL FULLTEXT 索引，或引入 Elasticsearch / OpenSearch。

3. 最左匹配原則（Leftmost Prefix Rule）

假設組合索引為 INDEX(country, status, created_at)，它的結構像是一棵樹：先排 country，再排 status，最後排 created_at。

WHERE country = 'TW' AND status = 'active' ➔ 有效使用索引。
WHERE status = 'active' ➔ 無法使用該組合索引（因為跳過了最左邊的 country）。

4. 組合索引的順序不是隨便排的

設計 INDEX(A, B, C) 時，不能只因為「這三個欄位都會查」就隨意排列。資深工程師會綜合評估：

Query Pattern：哪些是等值查詢（=），哪些是範圍查詢（>、LIKE）？
Cardinality & Selectivity：選擇性高的欄位（重複值少，如 user_id）通常放在前面，選擇性低的（如 status）放在後面。
Order By & Group By：能否順便利用索引的排序特性消除 filesort？

5. ORDER BY 有 Index ≠ 不需要排序

INDEX(user_id, created_at) 在 WHERE user_id = 100 ORDER BY created_at DESC 時表現完美。

但如果是 WHERE status = 'active' ORDER BY created_at DESC，且索引是 (status, created_at)，只要 status 篩選出的資料量過大，Optimizer 評估後仍可能選擇使用內建排序算法，跳出 Using filesort。

二、執行計畫（EXPLAIN）與誤區

6. 看到 `type = ALL` 不代表一定有問題

type = ALL 代表 Full Table Scan（全表掃描）。
如果該 Table 只有 20 筆資料，MySQL 評估「全表掃描」的 IO 成本比「先讀 Index 再回表（Bookmark Lookup）」還要低，選擇全表掃描才是最合適的決策。

原則：執行計畫必須結合 資料規模 與 查詢成本（Cost） 綜合評估，不要看到 ALL 或 Using filesort 就直覺認定是 Bug。

7. `COUNT(*)` vs `COUNT(id)` 的迷思

在 InnoDB 中，「COUNT(id) 一定比 COUNT(*) 快」完全是錯誤傳言。

語意差異：COUNT(*) 計算總行數；COUNT(col) 僅計算該欄位 非 NULL 的行數。
效能表現：MySQL Optimizer 對 COUNT(*) 做過極度優化，會自動選擇最小的次級索引（Secondary Index）來掃描行數，效能通常等於或優於 COUNT(id)。

8. 寫成一行 SQL 不代表比較快

SQL 是你寫的聲明式指令（Declarative Query），Execution Plan 才是 MySQL 實際執行的程序。複雜的單行 SQL 可能導致 Optimizer 產生極度低效的執行計畫，必要時拆成簡單查詢或適當使用 Temporary Table 反而更好。

三、三值邏輯與 JOIN 的語意陷阱

9. NULL 的三值邏輯（TRUE, FALSE, UNKNOWN）

SQL 中的 NULL 代表「未知」。

NULL = NULL 的結果是 UNKNOWN，而不是 TRUE。
查詢必須使用 WHERE col IS NULL。

10. `NOT IN` 遇到 `NULL` 的毀滅性陷阱

SQL
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

如果 orders.user_id 中只要有一筆資料是 NULL，整個 NOT IN 子查詢的結果就會直接變成 空集合（0 rows）。

最佳實踐：在處理排除邏輯時，優先使用 NOT EXISTS 或 LEFT JOIN ... WHERE right.id IS NULL。

11. `LEFT JOIN` 的 `WHERE` 條件會將其隱式轉為 `INNER JOIN`

SQL
SELECT users.* 
FROM users 
LEFT JOIN orders ON orders.user_id = users.id 
WHERE orders.status = 'paid';

WHERE orders.status = 'paid' 強制排除了沒有訂單的 NULL 列（NULL = 'paid' 為 false），導致這個 LEFT JOIN 實質退化成了 INNER JOIN。

如果希望保留沒有訂單的 User，必須將條件寫在 ON 區塊：

SQL
LEFT JOIN orders ON orders.user_id = users.id AND orders.status = 'paid'

12. 不要背「INNER JOIN 一定比 LEFT JOIN 快」

JOIN 的效能取決於驅動表（Driven Table）的選擇、JOIN 欄位是否有 Index、卡片度（Cardinality）以及 Optimizer 的決策。兩者核心差異在於 業務語意，而非單純的快慢。

四、交易（Transaction）、鎖（Lock）與併發控制

13. Transaction ≠ Lock

Transaction（交易）：定義操作邊界，保證 ACID 特性與隔離等級（Isolation）。
Lock（鎖）：控制併發交易競爭同一資源時的存取順序。
呼叫 DB::transaction() 只是開啟交易，不會自動鎖住所有讀取的資料。

14. `SELECT ... FOR UPDATE` 不是萬能鎖

使用 FOR UPDATE 時必須注意：

是否在交易區塊內？
是否命中索引？（若未命中索引，InnoDB 可能會鎖住整個掃描範圍，甚至升級為類似 Table Lock 的效果）。
Isolation Level 決定了它是鎖住特定 Row，還是鎖住區間（Gap Lock / Next-Key Lock）。

15. 死鎖（Deadlock）的根本解法

併發系統中，當 Transaction A 與 B 以相反順序獲取資源時就會產生 Deadlock。

解決 Deadlock 不能僅靠 catch (DeadlockException) 然後 retry，資深架構師會從以下層面防禦：

業務層面：嚴格規範所有交易存取資源的 固定順序（如永遠先鎖 User 再鎖 Order）。
架構層面：縮短交易時間、減少鎖定範圍、避免在交易內呼叫耗時的第三方 API。

16. MVCC（多版本併發控制）與一致性讀取

InnoDB 預設使用 MVCC 來達成高併發讀寫。

一致性讀取（Consistent Read）：普通 SELECT 讀取的是快照（Snapshot），不會加鎖，也不會被其他交易的 UPDATE 阻塞。
鎖定讀取（Locking Read）：SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 會讀取最新實時資料並加鎖。

五、高併發實戰：超賣與一致性

17. 「資料庫有 Index，所以不會超賣」是經典謬誤

PHP
// 錯誤範例：存在 Race Condition
$product = Product::find(1); // stock = 1
if ($product->stock > 0) {
    $product->decrement('stock');
}

兩個請求同時通過 if ($product->stock > 0) 判斷，庫存就會被扣成 -1。

正確解法：利用資料庫原子操作（Atomic Update）與條件檢查：

SQL
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;

執行後檢查受影響行數（affected_rows）是否為 1。

18. 分散式鎖（Redis Lock）不能取代 DB 資料庫一致性

Redis Lock 可以擋掉大部分前端重複衝擊，但面對網路暫斷、Lock 到期釋放（Timeout）、程序崩潰（Crash）等極端狀況，Database Constraint（如 UNIQUE Key、Check Constraint）永遠是保證資料不髒掉的最後一道防線。

19. Unique Index 是防重最後一道防線

在程式碼寫 if (!Order::where('order_no', $no)->exists()) 永遠無法擋住高併發下的重複插入。資料庫層級的 UNIQUE KEY 才是硬性保證。

20. Soft Delete + Unique Constraint 的坑

Laravel 的 SoftDeletes 特性是將資料標記 deleted_at != NULL。

若 email 設為 DB 唯一索引，當使用者刪除帳號後重新註冊相同 email，DB 會因為被軟刪除的舊資料仍存在而報錯 Duplicate entry。

解法：將組合索引擴展為 UNIQUE(email, deleted_at)（需注意 NULL 在 DB Unique 上的行為差異），或改用物理硬刪除歸檔表。

六、大資料量與架構優化

21. N+1 問題不只是 Eager Loading

Laravel 中可以用 Order::with('user')->get() 解決 N+1。

但當數據量達到數百萬級時，with() 會產生巨大的 WHERE id IN (...) 查詢，可能導致 PHP 記憶體溢出（Memory Limit Exceeded） 或 MySQL 解析過大 SQL 語法。大數據量下應採用分批處理（chunk / lazy）或適度做冗餘欄位。

22. 深分頁（Offset Pagination）的效能黑洞

SQL
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000;

MySQL 為了拿到這 20 筆資料，必須先掃描並拋棄前面的 1,000,000 筆資料，IO 成本極高。

解法：改用 Cursor / Keyset Pagination（Laravel 的 cursorPaginate()）：

SQL
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

七、資深工程師的核心面試觀念

23. 避免給出「絕對答案」

在 MySQL 的世界裡，幾乎沒有「絕對」：

❌ 「JOIN 一定比 Subquery 快」
❌ 「索引一定比全表掃描快」
❌ 「COUNT(id) 比 COUNT(*) 快」

成熟的回答模式永遠是：

「通常情況下……，但這取決於資料規模、Cardinality、Isolation Level、Query Pattern 以及 MySQL Optimizer 最終產生的 Execution Plan。」

24. 構建完整的交易架構思考鏈

當面試官問到：「交易如果出問題怎麼辦？」或「如何處理高併發扣庫存？」時，試著將知識點串聯成完整的故事線：

\text{Transaction} \rightarrow \text{Race Condition} \rightarrow \text{Lock / Atomic Update} \rightarrow \text{Deadlock 防禦} \rightarrow \text{MVCC / Isolation Level} \rightarrow \text{DB Constraint 防禦} \rightarrow \text{冪等性（Idempotency）與 Queue 補償機制}

當你能順暢地從 Laravel API 層一路向下講到 MySQL 的鎖機制與分散式一致性，面試官就會知道，你是一位真正經手過高併發、具備實戰解決問題能力的資深後端架構師。

羅昱鈞的技術筆記：從面試戰場到 AI 實戰

2026年9月18日星期五

從 Laravel 到 MySQL 底層：資深後端面試與實務中，最容易踩的 24 個資料庫陷阱

一、索引（Index）的盲點與陷阱

1. 有 Index ≠ 一定會使用 Index

2. `LIKE '%keyword%'` 的 B-Tree 特性

3. 最左匹配原則（Leftmost Prefix Rule）

4. 組合索引的順序不是隨便排的

5. ORDER BY 有 Index ≠ 不需要排序

二、執行計畫（EXPLAIN）與誤區

6. 看到 `type = ALL` 不代表一定有問題

7. `COUNT(*)` vs `COUNT(id)` 的迷思

8. 寫成一行 SQL 不代表比較快

三、三值邏輯與 JOIN 的語意陷阱

9. NULL 的三值邏輯（TRUE, FALSE, UNKNOWN）

10. `NOT IN` 遇到 `NULL` 的毀滅性陷阱

11. `LEFT JOIN` 的 `WHERE` 條件會將其隱式轉為 `INNER JOIN`

12. 不要背「INNER JOIN 一定比 LEFT JOIN 快」

四、交易（Transaction）、鎖（Lock）與併發控制

13. Transaction ≠ Lock

14. `SELECT ... FOR UPDATE` 不是萬能鎖

15. 死鎖（Deadlock）的根本解法

16. MVCC（多版本併發控制）與一致性讀取

五、高併發實戰：超賣與一致性

17. 「資料庫有 Index，所以不會超賣」是經典謬誤

18. 分散式鎖（Redis Lock）不能取代 DB 資料庫一致性

19. Unique Index 是防重最後一道防線

20. Soft Delete + Unique Constraint 的坑

六、大資料量與架構優化

21. N+1 問題不只是 Eager Loading

22. 深分頁（Offset Pagination）的效能黑洞

七、資深工程師的核心面試觀念

23. 避免給出「絕對答案」

24. 構建完整的交易架構思考鏈

沒有留言:

張貼留言

Laravel vs Lumen：當 Laravel 自身變得足夠輕量，Lumen 還有存在的必要嗎？

檢舉濫用情形

2026年9月18日 星期五

從 Laravel 到 MySQL 底層：資深後端面試與實務中，最容易踩的 24 個資料庫陷阱

一、 索引（Index）的盲點與陷阱

1. 有 Index ≠ 一定會使用 Index

2. LIKE '%keyword%' 的 B-Tree 特性

3. 最左匹配原則（Leftmost Prefix Rule）

4. 組合索引的順序不是隨便排的

5. ORDER BY 有 Index ≠ 不需要排序

二、 執行計畫（EXPLAIN）與誤區

6. 看到 type = ALL 不代表一定有問題

7. COUNT(*) vs COUNT(id) 的迷思

8. 寫成一行 SQL 不代表比較快

三、 三值邏輯與 JOIN 的語意陷阱

9. NULL 的三值邏輯（TRUE, FALSE, UNKNOWN）

10. NOT IN 遇到 NULL 的毀滅性陷阱

11. LEFT JOIN 的 WHERE 條件會將其隱式轉為 INNER JOIN

12. 不要背「INNER JOIN 一定比 LEFT JOIN 快」

四、 交易（Transaction）、鎖（Lock）與併發控制

13. Transaction ≠ Lock

14. SELECT ... FOR UPDATE 不是萬能鎖

15. 死鎖（Deadlock）的根本解法

16. MVCC（多版本併發控制）與一致性讀取

五、 高併發實戰：超賣與一致性

17. 「資料庫有 Index，所以不會超賣」是經典謬誤

18. 分散式鎖（Redis Lock）不能取代 DB 資料庫一致性

19. Unique Index 是防重最後一道防線

20. Soft Delete + Unique Constraint 的坑

六、 大資料量與架構優化

21. N+1 問題不只是 Eager Loading

22. 深分頁（Offset Pagination）的效能黑洞

七、 資深工程師的核心面試觀念

23. 避免給出「絕對答案」

24. 構建完整的交易架構思考鏈

沒有留言:

張貼留言

Laravel vs Lumen：當 Laravel 自身變得足夠輕量，Lumen 還有存在的必要嗎？

2026年9月18日星期五

一、索引（Index）的盲點與陷阱

2. `LIKE '%keyword%'` 的 B-Tree 特性

二、執行計畫（EXPLAIN）與誤區

6. 看到 `type = ALL` 不代表一定有問題

7. `COUNT(*)` vs `COUNT(id)` 的迷思

三、三值邏輯與 JOIN 的語意陷阱

10. `NOT IN` 遇到 `NULL` 的毀滅性陷阱

11. `LEFT JOIN` 的 `WHERE` 條件會將其隱式轉為 `INNER JOIN`

四、交易（Transaction）、鎖（Lock）與併發控制

14. `SELECT ... FOR UPDATE` 不是萬能鎖

五、高併發實戰：超賣與一致性

六、大資料量與架構優化

七、資深工程師的核心面試觀念