Home > Database > Mysql Tutorial > MySQL随机取数据最高效率的方法_MySQL

MySQL随机取数据最高效率的方法_MySQL

WBOY
Release: 2016-06-01 13:45:07
Original
897 people have browsed it

bitsCN.com

发现在SQL语句里有一个 ORDER BY rand() 这样的一个语句,这个说是用着方便,但是效率实在是太低了,于是我用了以下的方法来优化,就是用JOIN表的方法来达到这个取随机数据行的方法,你可以用 EXPLAIN SQL语句来分析一下以下两条SQL语句的效率,当然,数据量至少上10万以上才能看出性能。
 
[1]普通方法, 效率太低
 
SELECT * FROM table ORDER BY rand() LIMIT 10;
 
[2] JOIN的方法:
 
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 10;
 

作者“威尔软件”
 

bitsCN.com
Related labels:
use
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template