MySQL 預設對 GROUP BY 字段進行排序。若不需要排序結果,可用 ORDER BY NULL 禁用:
-- 優化前
SELECT goods_id, count(*) FROM t GROUP BY goods_id;
-- 優化後
SELECT goods_id, count(*) FROM t GROUP BY goods_id ORDER BY NULL;
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL 不支援函數索引,會導致全表掃描 + 臨時表排序,大數據下極慢。
核心原理:在子查詢中生成隨機 ID,再透過 JOIN 或範圍條件命中索引。
SELECT id FROM t_innodb_random t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t_innodb_random)) AS nid) t2
ON t1.id = t2.nid
LIMIT 1;
執行計劃:type=ref,rows=1,耗時 0.00 sec。
SELECT id FROM t_innodb_random
WHERE id > (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t_innodb_random)))
LIMIT 1;
雖為 type=index 全索引掃描,但 LIMIT 1 使掃描提前終止,耗時 0.00 sec。
SELECT id FROM t_innodb_random t1
JOIN (SELECT RAND() * (SELECT MAX(id) FROM t_innodb_random) AS nid) t2
ON t1.id > t2.nid
LIMIT 1000;
執行計劃:type=range,rows≈196k,耗時 0.00 sec(vs ORDER BY RAND() 的 0.41 sec)。
-- 原始寫法(慢)
SELECT id FROM table ORDER BY RAND() LIMIT n;
-- 優化寫法(快)
SELECT id FROM table t1
JOIN (SELECT RAND() * (SELECT MAX(id) FROM table) AS nid) t2
ON t1.id > t2.nid
LIMIT n;
id)。從 全表掃描 + 檔案排序(O(N log N)) 降級為 索引範圍查詢(O(log N + n)),百萬級數據從秒級降至毫秒級。