Mysql随机取样——ORDER BY RAND()优化

The palest ink is better than best memory——好记性不如烂笔头。2013补记


一、关键词:

随机取样、order by rand()

二、业务场景:

一款新产品上线后,刚开始用户比较少,不够活跃。但,出于运营需要,比如社交产品首页Feed流随机出老动态,系统修改发布时间显示~电商产品的商品列表随机显示商品,不至于每次用户看见的商品都一样——空城计——一种活跃的假象。所以研发就有了随机取样的活儿……

三、事例:

使用mysql的都知道,如下这样是随机取样的最直接方便的方式:

SELECT * FROM product_info  ORDER BY RAND() LIMIT 10

执行过程如下:
这里写图片描述

可是,ORDER BY RAND()超级慢,在200W记录表中,单次执行需要17s+….如果这样上线,用户并发访问,数据库肯定吃不消。

查阅MySQL官方手册得知:在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。
如下:http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand

RAND() in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join). Thus, for optimizer purposes, RAND() is not a constant value and cannot be used for index optimizations. For more information, see Section 9.2.1.3.5, “Function Call Optimization”.

Use of a column with RAND() values in an ORDER BY or GROUP BY clause may yield unexpected results because for either clause a RAND() expression can be evaluated multiple times for the same row, each time returning a different result.

搜索Google,网上基本上都是使用MAX() /MIN()、 RAND()、FLOOR()/ROUND()函数相互结合达到效果,如下SQL1::

SELECT *
FROM `product_info` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `product_info`)-(SELECT MIN(id) FROM `product_info`))+(SELECT MIN(id) FROM `product_info`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 10;

或简单一点如下SQL2:

SELECT * FROM `product_info` p join (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `product_info`)) AS id ) as p1 
WHERE p.id >= p1.id
ORDER BY p.id LIMIT 10;

速度是挺快,但是结果集不够随机,往往数据在一个区间——不离散,所以不符合需求。

把SQL2改造如下SQL3(两者的区别就在于JOIN和FROM子查询的不同):

SELECT * FROM `product_info`
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `product_info`))) 
ORDER BY id LIMIT 10;

查询一次,基本耗时在0.09s,执行过程如下:
这里写图片描述

把SQL3继续改成如下,结果较离散一些(执行效率差不多):

SELECT * FROM `product_info`
WHERE id >= (SELECT FLOOR( RAND()*((SELECT MAX(id) FROM product_info)-(SELECT MIN(id) FROM product_info))+(SELECT MIN(id) FROM product_info)))
ORDER BY id LIMIT 10;

这里写图片描述

评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值