MySQL SQL 語句優化實戰：10 大核心技巧與避坑指南

MySQL SQL 語句優化實戰：10 大核心技巧與避坑指南

1. 避免 SELECT *

明確列出所需列，雖速度提升不明顯，但能節省網路傳輸與記憶體。

2. LIKE 用法謹慎

LIKE '%aaa%' 不走索引；LIKE 'aaa%' 可用索引。非必要勿用 LIKE。

3. 列上勿做運算，否則索引失效

-- 錯誤：全表掃描
SELECT * FROM users WHERE YEAR(adddate) < 2007;
-- 正確：走索引
SELECT * FROM users WHERE adddate < '2007-01-01';

4. 拒絕 NOT IN 與 <>

均導致全表掃描。NOT IN 改用 NOT EXISTS；id <> 3 改用 id > 3 OR id < 3。

5. 為查詢快取優化查詢

相同查詢重複執行時，MySQL 會快取結果。但某些函數（如 NOW()、RAND()）會導致不快取。

6. 絕對禁止 ORDER BY RAND()

MySQL 必須為每行執行 RAND() 並排序，CPU 開銷極大，即使 LIMIT 1 也無濟於事。

7. 深分頁 LIMIT huge_num, offset 效能陷阱

LIMIT 1000,20 需掃描 1020 行再丟棄前 1000 行。優化三策略：

1. 子查詢 + 回表：SELECT * FROM payment a INNER JOIN (SELECT payment_id FROM payment ORDER BY rental_id LIMIT 100,10) b ON a.payment_id = b.payment_id;
2. BETWEEN ... AND（適用連續 ID，實用性有限）。
3. 業務協作：前端傳遞上一頁最後一條排序鍵 last_page_record，下一頁直接範圍查找。

8. 用 COUNT(*) 而非 COUNT(id)

COUNT(*) 經優化器優化，效能更好。

9. 批量插入合併語句

-- 多條 INSERT 合併為單條
INSERT INTO t(id,name) VALUES (1,'test1'),(2,'test2'),(3,'test3');

10. ORDER BY 利用索引避免 Filesort

設計索引順序匹配 ORDER BY 列，直接返回有序結果集。