索引優化原則

• 非叢集索引預設，資料物理順序隨機；叢集索引決定物理順序。
• 高重複值 + 範圍查詢 + 排序/分組 → 叢集索引。
• 多欄位常同時存取 → 組合索引，前導欄位為最高頻。
• 索引覆蓋：查詢欄位全在索引中，避免回表。
• 索引非越多越好，維護成本隨寫入增長。

查詢改寫技巧

1. 海量查詢少用格式轉換（CAST/CONVERT）。
2. ORDER BY/GROUP BY 欄位加索引可避免 filesort。
3. 欄位運算移至等號右側：WHERE col+1=10 → WHERE col=9。
4. IN/OR 子句易失效，拆分為 UNION ALL 並各自帶索引條件。

MySQL 具體參數調優（4GB 記憶體範例）

[mysqld]
back_log = 384
key_buffer_size = 256M
max_allowed_packet = 4M
thread_stack = 256K
table_open_cache = 512
sort_buffer_size = 6M
read_buffer_size = 4M
join_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 256M
max_connections = 768
wait_timeout = 10
thread_concurrency = 8
skip-name-resolve
skip-locking

實戰案例：隨機抽樣優化

問題：ORDER BY RAND() LIMIT 5 在 15 萬筆資料耗時 8 秒。

原因：RAND() 在 ORDER BY 中會被多次評估。

方案一：JOIN 最大 ID

SELECT * FROM `table` AS t1
JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`) - (SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;

單次查詢 ~0.01 秒，需執行 5 次取 5 筆不連續資料。

方案二：WHERE id >= 隨機值

SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(RAND() * ((SELECT MAX(id) FROM `table`) - (SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;

同樣 ~0.01 秒，加入 MIN(id) 避免前段資料被過度抽中。

實戰案例：IN 子查詢優化為 COUNT(*)

-- 優化前：IN 子查詢導致全表掃描
SELECT T.img_id, T.thumb_path
FROM gallery_photofiles P
LEFT JOIN gallery_thumbs T ON T.img_id = P.img_id AND T.thumb_type='11'
WHERE P.owner_user_id = '1'
  AND P.img_id IN (SELECT A.img_id FROM gallery_album_img_link A WHERE A.img_id);

-- 優化後：COUNT(*) 利用索引快速判斷存在性
SELECT T.img_id, T.thumb_path
FROM gallery_photofiles P
LEFT JOIN gallery_thumbs T ON T.img_id = P.img_id AND T.thumb_type='11'
WHERE P.owner_user_id = '1'
  AND (SELECT COUNT(*) FROM gallery_album_img_link A WHERE A.img_id = P.img_id) > 0;

COUNT(*) 走索引掃描，速度大幅提升。