Mysql 随机查询10条数据效率最快的查询方法：三种方法对比[归档]
<h5>1）使用join 和 rand() 耗时 0.009</h5><pre>
SELECT * FROM `t_topic` AS t1 JOIN (
SELECT
ROUND( RAND() * (
(SELECT MAX(id) FROM `t_topic`) - (SELECT MIN(id) FROM `t_topic`)
) + (SELECT MIN(id) FROM `t_topic`)
) AS id
) AS t2 WHERE
t1.id >= t2.idORDER BY t1.id
LIMIT 10;</pre>
<p> </p>
<h5>2) 下面这条比上面那个还要慢几秒</h5>
<pre>
SELECT * FROM `t_topic`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `t_topic`)-(SELECT MIN(id) FROM `t_topic`)) + (SELECT MIN(id) FROM `t_topic`)))
ORDER BY id LIMIT 10;</pre>
<p> </p>
<h5>3）或者使用下面这个也可以测试也是 耗时0.0012</h5>
<pre>
SELECT * FROM
`t_topic` WHERE
id >= (
SELECT floor(RAND() * (SELECT MAX(id) FROM `t_topic`) )
) ORDER BY id
LIMIT 10;</pre>
<p> </p>
实例：mysql从600万数据中随机取10条数据<br />
<p>通常数据量少的时候我们会这样写：</p>
<pre>
SELECT * FROM _article ORDER BY RAND() LIMIT 10</pre>
<p>在1千条数据内，效果还行，但是，随着数据量慢慢增多，上了5万条数据的时候，上面的写法会导致数据库性能急剧下降。</p>
<p>现在数据量为：</p>
<p><img alt="" src="data/attachment/forum/202302/28/2ee41d4fd9a81f0729b5409cb6f45a32.jpg" /></p>
<p>我们来看下上面的随机取数据所消耗的时间：</p>
<p><img alt="" src="data/attachment/forum/202302/28/61cabe744732a02712502795b39b41fb.jpg" /></p>
<p>我的天呐，这样取数据，数据库会让你给搞爆的。</p>
<p>我们来看下大牛怎样写：</p>
<pre>
SELECT * FROM _article AS r1 JOIN
(SELECT CEIL(RAND() * (SELECT MAX(article_id) FROM_article)) AS article_id) AS r2
WHERE r1.article_id >= r2.article_id
ORDER BY r1.article_id ASC
LIMIT 10</pre>
<p><img alt="" src="data/attachment/forum/202302/28/9075a3b8c1f082ea227b94b4f04e992a.jpg" /></p>
<p> </p>
页:
[1]