优化ORDER BY RAND()：大表关联随机查询性能提升问询

原查询慢的核心原因 #

你当前的SQL先执行全量关联生成庞大的中间结果集，再用ORDER BY rand()对整个结果集排序——这种方式会触发全表扫描+全量排序，完全无法利用索引优化，数据量增长时性能必然持续下降。

针对ID有间隔场景的高效优化方案 #

下面的方法能将查询耗时压到0.01秒级别，且适配ID不连续的情况：

方案1：先抽随机主键再关联（推荐，性能最优）

核心思路是缩小随机筛选的范围到主表s，再关联其他表，避免对超大中间集排序：

1. 先统计主表中符合条件的记录数（可缓存结果，减少重复计算）：

SELECT COUNT(*) FROM `s` WHERE enabled = 1 AND exists = 1 AND fileEnabled = 1;

2. 在应用层生成随机偏移量（比如用代码生成0到count-1之间的随机数），获取4个有效主键：

SELECT id FROM `s` WHERE enabled = 1 AND exists = 1 AND fileEnabled = 1 LIMIT {offset}, 1;

重复执行4次拿到4个不重复的ID（或用一次查询取多个，注意去重）。
3. 用拿到的主键关联其他表：

SELECT `s`.*, `sic`.*, `c`.*, `con`.*, `c1`.*, `c2`.*, `c3`.*, `cur`.*, `s1`.*, `s2`.*
FROM `s`
LEFT JOIN `sic` ON sic.id = s.id
LEFT JOIN `c` ON c.id = s.cou_id
LEFT JOIN `con` ON con.id = c.con_id
LEFT JOIN `c1` ON s.col_id = c1.id
LEFT JOIN `c2` ON s.bac_id = c2.id
LEFT JOIN `c3` ON c3.id = s.col2_id
LEFT JOIN `cp` ON cp.id = s.cp_id
LEFT JOIN `cur` ON cur.id = cp.cur_id
LEFT JOIN `s1` ON s.s_width_id = s1.id
LEFT JOIN `s2` ON s.s_height_id = s2.id
WHERE s.id IN ({id1}, {id2}, {id3}, {id4})
AND s.enabled = 1 AND s.exists = 1 AND s.fileEnabled = 1;

方案2：单SQL子查询过滤（适合不想改应用逻辑的场景）

用子查询先在主表中筛选随机ID，再关联其他表，避免全量关联后排序：

SELECT `s`.*, `sic`.*, `c`.*, `con`.*, `c1`.*, `c2`.*, `c3`.*, `cur`.*, `s1`.*, `s2`.*
FROM (
    SELECT id
    FROM `s`
    WHERE enabled = 1 AND exists = 1 AND fileEnabled = 1
    ORDER BY RAND()
    LIMIT 4
) AS random_s
JOIN `s` ON s.id = random_s.id
LEFT JOIN `sic` ON sic.id = s.id
LEFT JOIN `c` ON c.id = s.cou_id
LEFT JOIN `con` ON con.id = c.con_id
LEFT JOIN `c1` ON s.col_id = c1.id
LEFT JOIN `c2` ON s.bac_id = c2.id
LEFT JOIN `c3` ON c3.id = s.col2_id
LEFT JOIN `cp` ON cp.id = s.cp_id
LEFT JOIN `cur` ON cur.id = cp.cur_id
LEFT JOIN `s1` ON s.s_width_id = s1.id
LEFT JOIN `s2` ON s.s_height_id = s2.id;

方案3：预缓存随机ID（高并发场景）

如果查询频率极高，可定期（如每分钟）预查询一批符合条件的s表ID存入缓存（如Redis），每次取随机记录时直接从缓存中选几个ID再关联查询，能把耗时降到最低。

配套索引优化 #

给主表s创建联合索引：

CREATE INDEX idx_s_active ON `s`(enabled, exists, fileEnabled, id);

这个索引能让主表的筛选和ID查询直接走索引，无需回表，进一步提升速度。同时确保所有关联表的关联字段（如sic.id、c.id等）为主键或已建索引。

内容的提问来源于stack exchange，提问作者tomasr