[性能优化] MySQL ORDER BY RAND()的替代方案

这是开发签到助手时遇到的问题……

需求:从数据库取没有签到的TID

原来的代码:

SELECT tid FROM sign_log WHERE status IN (0, 1) AND date='{$date}' ORDER BY RAND() LIMIT 0,1

当 sign_log 这个表非常大的时候,会出现 filesort 罢工的现象

EXPLAIN 的提示:Using where; Using temporary; Using filesort

这是由于 MySQL 在执行过程中需要遍历数据库,然后给每行分配一个权重值(因为用的是RAND函数),之后再用 filesort 的方式排序临时表,最后输出。

只是取一个tid而已……有必要遍历整个表么?有没有替代的方案呢?

答案是肯定的。这个算法分为3步,将上面一个查询拆解为2个:

1.从数据库取出最大数据量:

SELECT COUNT(*) FROM `sign_log` WHERE status IN (0, 1) AND date='{$date}'

2.用 PHP 的 rand 函数计算出 offset 值

$offset = rand(1, $count) - 1;

3.直接从数据库取出这一行的tid:

SELECT tid FROM `sign_log` LIMIT {$offset},1

(再次运行时可以略过第一步)

测试结果(共13506行数据):

ORDER BY RAND():
Using where; Using temporary; Using filesort
运行时间:0.0050160884857178秒

优化后:
查询1:Using where
查询2:Using index(注:tid字段有索引)
总运行时间:0.0021970272064209秒

改进后不仅提升了运行速度,还解决了 filesort 数据量上限的问题

标签: PHP MySQL 性能优化


已有 2 条评论
  1. Dx. Dx.

    :grin: 请教个问题,输入百度账号自动获取cookie是怎么做到的?还有获取验证码。就是api.ikk.me那个

  2. sh sh

    select t1.tid from sign_log t1 join
    (select round(rand() * ((select max(tid) frin sign_log) - select min(tid) from sign_log) + (select min(tid) from sign_log)) as tid) as t2
    where ti.tid >= t2.tid
    order by ti.tid
    limit 1;
    可用于不连续的tid

添加新评论