最近由于需要大概研究了下MYSQL随机抽取实现思路方法举个例子要从tablename表中随机提取条记录大家般写法就是:SELECT * FROM tablename ORDER BY RAND LIMIT 1

但是后来我查了下MYSQL官方手册里面针对RAND提示大概意思就是在ORDER BY从句里面不能使用RAND这样会导致数据列被多次扫描但是在MYSQL 3.23版本中仍然可以通过ORDER BY RAND来实现随机

但是真正测试下才发现这样效率非常低个15万余条库查询5条数据居然要8秒以上查看官方手册也说rand放在ORDER BY 子句中会被执行多次自然效率及很低
You cannot use a column with RAND values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
搜索Google网上基本上都是查询max(id) * rand来随机获取数据
SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

但是这样会产生连续5条记录解决办法只能是每次查询条查询5次即便如此也值得15万条表查询只需要0.01秒不到

下面语句采用是JOINmysql论坛上有人使用
SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND) FROM `table` )
ORDER BY id LIMIT 1;

我测试了下需要0.5秒速度也不错但是跟上面语句还是有很大差距总觉有什么地方不正常

于是我把语句改写了下
SELECT * FROM `table`
WHERE id >= (SELECT floor(RAND * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;

这下效率又提高了查询时间只有0.01秒

最后再把语句完善下加上MIN(id)判断我在最开始测试时候就是没有加上MIN(id)判断结果有半时间总是查询到表中前面几行
完整查询语句是:
SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;

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

最后在php中对这两个语句进行分别查询10次
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN语法比直接在WHERE中使用效率还要高很多