mysql随机查询的优化
mysql随机查询的优化
mysql随机查询最常见的写法如下:1 SELECT * FROM tablename ORDER BY RAND() LIMIT 1
php手册上如此解释:
About selecting random rows from a MySQL table:
SELECT * FROM tablename ORDER BY RAND() LIMIT 1
works for small tables, but once the tables grow larger than 300,000 records or so this will be very slow because MySQL will have to process ALL the entries from the table, order them randomly and then return the first row of the ordered result, and this sorting takes long time. Instead you can do it like this (atleast if you have an auto_increment PK):
SELECT MIN(id), MAX(id) FROM tablename;
Fetch the result into $a
$id=rand($a[0],$a[1]);
SELECT * FROM tablename WHERE id>=’$id’ LIMIT 1.
大意是说,如果你用 ORDER BY RAND() 来随机读取记录的话,当数据表记录达到30万或者更多的时候,mysql将非常吃力.所以php手册里给了一种方法,结合php来实现:
首先 SELECT MIN(id), MAX(id) FROM tablename; 取数据库里最大最小值;
然后 $id=rand($a[0],$a[1]); 产生一个随机数;
最后 SELECT * FROM tablename WHERE id>=’$id’ LIMIT 1 将上面产生的随机数带入查询;
很显然上面是最有效率的。
如果需要多条记录的话，就循环查询，并记得去除重复记录。
其它的一些方法可以自行查阅一下google或者百度。