博学笃行·盛德日新

怎样随机的推荐10条记录_一条sql语句引发的思考


技术

1. 缘起

座位左边的小伙伴,遇到一台服务器负载彪高,top一下看mysql的cpu占用超过100%。嘿,那应该就是mysql的问题啦,比较武断,但这个判断99%的情况下,应该没有错。
进入mysql,然后进行show full processlist查看数据库的当前进程。

MariaDB [xxx]> show full processlist;
+------+----------+-----------------+------+---------+------+----------------------+----------------------------------------------------------------------+----------+
| Id   | User     | Host            | db   | Command | Time | State                | Info                                                                 | Progress |
+------+----------+-----------------+------+---------+------+----------------------+----------------------------------------------------------------------+----------+
| 2886 | root     | localhost       | xxx  | Query   |    0 | init                 | show full processlist                                                |    0.000 |
| 3154 | secret   | 127.0.0.1:44104 | xxx  | Query   |    0 | Copying to tmp table | select id,name from books where status = 1 order by rand() limit 10  |    0.000 |    

光从这个语句来看,似乎很好的解决了老板们(或产品经理们)提出来的需求,随机抽取10条状态为1(status = 1)的书本的id和名字。
但是呢,这种开发者,应该比较适合那些“小而美”的小公司,因为这个语句在数据量小的时候,一点问题都没有,而且可以完美的解决需求。
但是呢,作为开发者,我们还是需要有点追求。您看,现在不就是造成系统负载彪高了么,影响网站访问了么。

order by rand()本身效率问题

大量的事实证明这个语句的效率,在大表的时候,执行很慢,官方也不推荐使用这种方法。

Copying to tmp table问题

因为这个表的数据,超过了30w行记录,直接使用order by rand()需要去扫描每一行,然后给出一个随机数。因为查询的数据超出了tmp_table_size的限制,那么mysql用tmp保存查询结果,然后返回给客户端。
这里涉及到数据扫描(30w行)时间、查询数据Copying to tmp table的时间、网络传输的时间,这样的语句不慢也是没有天理了。

2. 几个解决方法

2.1 优化sql

SELECT b.id,
       b.name,
       ROUND(RAND() * x.mmid) 'rand_ind'
  FROM  book b,
        (SELECT MAX(t.id) 'mmid' FROM book t) x
  WHERE b.status = 1 
  ORDER BY rand_ind
  LIMIT 10;

下面这个方法虽然复杂一点,但是可以进行更好的随机rand_ind

SELECT b.id,
       b.name,
       FLOOR(1 + RAND() * x.mmid) 'rand_ind'
  FROM  book b,
        (SELECT MAX(t.id) - 1 'mmid' FROM book t) x
  WHERE b.status = 1 
  ORDER BY rand_ind
  LIMIT 10;

上面两个方法其实也只是解决了order by rand()的效率问题,遇到大数据量查询的时候,一样会有Copying to tmp table的问题。

2.2 结合具体的业务程序

其实我是一直没有搞懂,为什么一定要在数据库里做rand()等等的函数操作。我知道有些程序员就是,能在数据库里用一条sql语句查询出来的数据,绝对不会多写一行代码在业务里进行处理。
但是实际上有些业务逻辑判断,放到代码里执行效率会更高,数据库就当做是一个存储,不要把计算的事情交给他。

Order by rand() is very slow on large tables,

I found the following workaround in a php script:

Select min(id) as min, max(id) as max from table;
Then do random in php

$rand = rand($min, $max);
Then

'Select * from table where id>'.$rand.' where status = 1 limit 10';
Seems to be quite fast....

2.3 redis-list方式

使用方法2的时候,解决了一部分的效率问题,但是也引入了一个新的问题。此时取出来的数据有可能是连续的10个id的数据。
最近redis使用的比较多,看看结合redis能不能实现我们的需求呢?最后发现redis的list结构我们正好可以利用起来。

基本的思路就是,把所有的books表里的status为1的数据都缓存到redis的list列表里,在内存里加载数据,会比在mysql里查询数据快n多倍呢。

如何避免掉进redis的坑里

  • 坑1:一次加载整个list 如果我们每次用户请求过来的时候,都把整个list加载到php(以php为例,其他语言类似)里,那么每次从redis里读取几十万的id记录,然后使用php去随机10个记录,也是一个庞大的网络开销和计算开销。显然这种方式并不可取。

  • 坑2:分10次去获取记录id 那我们要读取10个随机记录,只能拿10个随机数去连接10次redis,然后得到10个随机的id记录嘛?这样连续的10次网络建立连接的过程,也不是我们希望看到的。
    那有没有一种方法,可以一次性取出这随机的10条记录呢?其实我们可以结合redis的lua脚本来实现的。

  • 使用redis+lua,解决随机10条记录id的获取 Redis命令的计算能力并不算很强大,使用Lua语言则可以在很大程度上弥补Redis的这个不足。在Redis中,执行Lua语言是原子性,也就是说Redis执行Lua的时候是不会被中断的,具备原子性,这个特性有助于Redis对并发数据一致性的支持。

2.4 推荐:redis-set方式

redis的set(集合)可以有效的规避数据重复的问题,推荐他更是因为,redis的set数据结构,有SRANDMEMBER指令可以直接获取指定个数的随机集合元素值。方便高效的解决问题。

参考

  1. MySQL: Alternatives to ORDER BY RAND()
  2. Profiling MySQL Queries for Better Performance
  3. SRANDMEMBER

评论