SQL 語句優化核心原則與百萬數據實戰：索引設計、子查詢改寫、RAND() 隨機查詢優化

優化核心原則

1. 索引設計：高重複值+範圍查詢+排序 → 群集索引；多列常一起查 → 復合索引 (最左前綴=最高頻列)；覆蓋索引消除回表。
2. 少用格式轉換：海量查詢中避免 CAST/CONVERT/DATE_FORMAT。
3. ORDER BY/GROUP BY 有索引則避免 filesort。
4. 列運算移至右側：col + 1 = 100 → col = 99。
5. IN/OR 拆分：不產生大量重複值時，拆為 UNION ALL 利用索引。

實戰案例：JOIN 去重優化

優化前 (冗余掃描)

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(*) 觸發半連接優化，避免物化子查詢，速度大幅提升。

RAND() 隨機查詢優化 (百萬級表)

❌ 低效寫法

SELECT * FROM t ORDER BY RAND() LIMIT 5;
-- RAND() 每行計算一次，需全表掃描+filesort，15萬行耗時 8 秒+

✅ 高效方案：主鍵範圍 + 子查詢

-- 方案 1：JOIN 子查詢 (推薦，穩定)
SELECT * FROM t AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t)) AS id) AS t2
  ON t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

-- 方案 2：WHERE 子查詢 (簡潔)
SELECT * FROM t
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t)))
ORDER BY id LIMIT 1;

-- 方案 3：含 MIN/MAX 邊界修正 (防止 ID 不連續導致前段數據概率高)
SELECT * FROM t
WHERE id >= (
    SELECT FLOOR(RAND() * ((SELECT MAX(id) FROM t) - (SELECT MIN(id) FROM t)) + (SELECT MIN(id) FROM t))
)
ORDER BY id LIMIT 1;

性能對比 (15 萬行表)

多條隨機記錄

單條高效，需多條時：

1. 應用層循環調用單條 SQL (5 次 × 0.01s = 0.05s)。
2. 生成 N 個隨機 ID 應用層 IN (id1, id2...) 查詢 (需 ID 密集)。

總結

• 隨機查詢核心：利用主鍵索引範圍掃描替代全表 RAND() 排序。
• 子查詢優化：半連接/物化/EXISTS 選擇取決於數據分佈與版本。
• 索引設計貫穿始終：最左前綴、覆蓋、基數 三要素。