如何优化这条MySQL随机查询语句？排序与分页是否为性能瓶颈？

嘿，这个问题我太有共鸣了！之前维护的一个高访问量网站也踩过ORDER BY RAND()的坑，一上线直接把数据库CPU拉满，咱们来一步步解决它～

为什么原查询会变慢？ #

你写的SELECT * FROM table WHERE city LIKE example ORDER by RAND() Limit 10，核心问题出在ORDER BY RAND()上：数据库会给符合WHERE条件的每一行生成一个随机数，然后对这些随机数做全量排序，最后取前10条。如果你的表很大，或者city LIKE过滤后的结果集还不小，这个排序操作的开销会非常大——尤其是高访问量页面，每个请求都触发一次全量排序，数据库资源直接被耗尽，网站自然就变慢了。

索引到底有没有用？ #

有用！但要看怎么用：

• 索引能帮你优化WHERE city LIKE example这部分的过滤效率：给city字段建普通索引后，数据库可以快速定位到符合条件的行，减少后续要处理的数据量。
• 注意：如果你的LIKE写法是'%example%'（前后都有通配符），大部分数据库（比如MySQL）的索引会失效，因为前缀不确定；但如果是'example%'（前缀匹配），索引就能正常生效，过滤速度会快很多。
• 不过就算过滤后的数据少了，ORDER BY RAND()的全排序开销还是存在，所以咱们的核心优化点是避免全量排序。

几种更优的查询写法 #

1. 随机ID法（推荐，适合有自增/连续ID的表） #

如果你的表有自增主键id，可以先获取符合条件的ID范围，再生成随机ID定位数据：

-- 第一步：获取符合条件的ID边界
SELECT MIN(id), MAX(id) INTO @min_id, @max_id FROM table WHERE city LIKE 'example%';

-- 第二步：生成随机ID并取数（自动兼容ID间隙）
SELECT * FROM table 
WHERE id >= FLOOR(@min_id + RAND() * (@max_id - @min_id + 1)) 
  AND city LIKE 'example%'
ORDER BY id
LIMIT 10;

这种方式完全避免了全量排序，数据库只需要根据随机ID定位到行的位置，再往后取10条，性能提升非常明显。

2. 随机偏移量法（适合结果集较小的场景） #

如果city LIKE过滤后的总条数不多，可以先查总条数，再生成随机偏移量：

-- 获取符合条件的总条数
SELECT COUNT(*) INTO @total FROM table WHERE city LIKE 'example%';

-- 生成随机偏移量并取数
SELECT * FROM table 
WHERE city LIKE 'example%'
LIMIT FLOOR(RAND() * @total), 10;

注意：如果总条数很大，大偏移量会导致数据库扫描大量行才能定位到位置，这种场景下不推荐用。

3. 预生成随机值法（适合超高并发场景） #

如果页面访问量极高，把随机排序的成本转移到后台定时任务：

1. 给表加一个random_score字段（比如浮点型）；
2. 用脚本定时更新这个字段为0-1之间的随机数（比如每天凌晨更新一次）；
3. 查询时直接用这个字段排序：

SELECT * FROM table 
WHERE city LIKE 'example%'
ORDER BY random_score
LIMIT 10;

还可以给(city, random_score)建联合索引，这样查询时直接走索引就能拿到数据，几乎没有额外开销，性能最优。

总结 #

• 索引对过滤条件有用，尽量用前缀匹配让索引生效，减少要处理的数据量；
• 尽量避免ORDER BY RAND()的全量排序，根据场景选择上面的优化方案；
• 高并发场景下，预生成随机值是性能最好的选择。

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