mysql随机查询效率优化
最近由于需要研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是： SELECT * FROM content ORDER BY RAND () LIMIT 1 【3万条记录查询花费 0.3745 秒（下同）；从mysql slow query log看出“ORDER BY RAN
最近由于需要研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是：
<code><span>SELECT </span><span>*</span><span> FROM content ORDER BY RAND</span><span>()</span><span> LIMIT </span><span>1</span></code>
【3万条记录查询花费 0.3745 秒（下同）；从mysql slow query log看出“ORDER BY RAND() ”全表扫描了2次！】
后来我查了一下MYSQL的官方手册，里面针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中，仍然可以通过ORDER BY RAND()来实现随机。
但是真正测试一下才发现这样效率非常低。一个15万余条的库，查询5条数据，居然要8秒以上。查看官方手册，也说rand()放在ORDER BY 子句中会被执行多次，自然效率及很低。
搜索Google，采用JOIN，查询max(id) * rand()来随机获取数据。
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> AS t1 JOIN </span><span>(</span><span>SELECT ROUND</span><span>(</span><span>RAND</span><span>()</span><span>*</span><span>(</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))</span><span> AS id</span><span>)</span><span> AS t2 WHERE t1</span><span>.</span><span>id </span><span>>=</span><span> t2</span><span>.</span><span>id ORDER BY t1</span><span>.</span><span>id ASC LIMIT </span><span>1</span><span>;</span></code>
【查询花费 0.0008 秒，飘易认为可以推荐使用这个语句！！】
但是这样会产生连续的5条记录。解决办法只能是每次查询一条，查询5次。即便如此也值得，因为15万条的表，查询只需要0.01秒不到。
有一个方法：
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> AS a JOIN </span><span>(</span><span> SELECT MAX</span><span>(</span><span> ID </span><span>)</span><span> AS ID FROM </span><span>`content`</span><span>)</span><span> AS b ON </span><span>(</span><span> a</span><span>.</span><span>ID </span><span>>=</span><span> FLOOR</span><span>(</span><span> b</span><span>.</span><span>ID </span><span>*</span><span> RAND</span><span>(</span><span>)</span><span>)</span><span>)</span><span> LIMIT </span><span>5</span><span>;</span></code>
上面这种方式保证了一定范围内的随机,查询花费 0.4265 秒，也不推荐。
下面的语句，mysql的论坛上有人使用
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> WHERE id </span><span>>=</span><span>(</span><span>SELECT FLOOR</span><span>(</span><span> MAX</span><span>(</span><span>id</span><span>)</span><span>*</span><span> RAND</span><span>())</span><span> FROM </span><span>`content`</span><span>)</span><span> ORDER BY id LIMIT </span><span>1</span><span>;</span></code>
【查询花费 1.2254 秒，飘易强烈不推荐！因为实测后，3万行的表，这个语句竟然会扫描500万行！！】
跟上面的语句还是有很大差距。总觉有什么地方不正常。于是我把语句改写了一下。
<code><strong><span><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> WHERE id </span><span>>=</span><span>(</span><span>SELECT floor</span><span>(</span><span>RAND</span><span>()</span><span>*</span><span>(</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)))</span><span> ORDER BY id LIMIT </span><span>1</span><span>;</span></span></strong></code>
【查询花费 0.0012 秒】
这下，效率又提高了，查询时间只有0.01秒
最后，再把语句完善一下，加上MIN(id)的判断。我在最开始测试的时候，就是因为没有加上MIN(id)的判断，结果有一半的时间总是查询到表中的前面几行。
完整查询语句是：
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> WHERE id </span><span>>=</span><span>(</span><span>SELECT floor</span><span>(</span><span> RAND</span><span>()</span><span>*</span><span>((</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)-(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))</span><span>+</span><span>(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)))</span><span> ORDER BY id LIMIT </span><span>1</span><span>;</span></code>
【查询花费 0.0012 秒】
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> AS t1 JOIN </span><span>(</span><span>SELECT ROUND</span><span>(</span><span>RAND</span><span>()</span><span>*</span><span>((</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)-(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))+(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))</span><span> AS id</span><span>)</span><span> AS t2 WHERE t1</span><span>.</span><span>id </span><span>>=</span><span> t2</span><span>.</span><span>id ORDER BY t1</span><span>.</span><span>id LIMIT </span><span>1</span><span>;</span></code>
【查询花费 0.0008 秒】
最后在php中对这两个语句进行分别查询10次，
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。（via）
======================================
【好了，最后飘易来总结下】：
第一种方案，即原始的 Order By Rand() 方法：
<code><span>$sql</span><span>=</span><span>"SELECT * FROM content ORDER BY rand() LIMIT 12"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$n</span><span>=</span><span>1</span><span>;</span><span>
$rnds</span><span>=</span><span>''</span><span>;</span><span>while</span><span>(</span><span>$row</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>)){</span><span>
$rnds</span><span>=</span><span>$rnds</span><span>.</span><span>$n</span><span>.</span><span>". <a href="show%22</span><span>.</span><span>%24row</span><span>%5B</span><span>" id><span>].</span><span>"-"</span><span>.</span><span>strtolower</span><span>(</span><span>trim</span><span>(</span><span>$row</span><span>[</span><span>'title'</span><span>])).</span><span>"'>"</span><span>.</span><span>$row</span><span>[</span><span>'title'</span><span>].</span><span>"</span></a><br>\n"</span><span>;</span><span>
$n</span><span>++;</span><span>}</span></code>3万条数据查12条随机记录，需要0.125秒，随着数据量的增大，效率越来越低。
第二种方案，改进后的 JOIN 方法：
<code><span>for</span><span>(</span><span>$n</span><span>=</span><span>1</span><span>;</span><span>$n</span><span><span>12</span><span>;</span><span>$n</span><span>++){</span><span>
$sql</span><span>=</span><span>"SELECT * FROM `content` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `content`)) AS id) AS t2
WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$yi</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>);</span><span>
$rnds </span><span>=</span><span> $rnds</span><span>.</span><span>$n</span><span>.</span><span>". <a href="show%22</span><span>.</span><span>%24yi</span><span>%5B</span><span>" id><span>].</span><span>"-"</span><span>.</span><span>strtolower</span><span>(</span><span>trim</span><span>(</span><span>$yi</span><span>[</span><span>'title'</span><span>])).</span><span>"'>"</span><span>.</span><span>$yi</span><span>[</span><span>'title'</span><span>].</span><span>"</span></a><br>\n"</span><span>;</span><span>}</span></span></code>3万条数据查12条随机记录，需要0.004秒，效率大幅提升，比第一种方案提升了约30倍。缺点：多次select查询，IO开销大。
第三种方案，SQL语句先随机好ID序列，用 IN 查询（飘易推荐这个用法，IO开销小，速度最快）：
<code><span>$sql</span><span>=</span><span>"SELECT MAX(id),MIN(id) FROM content"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$yi</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>);</span><span>
$idmax</span><span>=</span><span>$yi</span><span>[</span><span>0</span><span>];</span><span>
$idmin</span><span>=</span><span>$yi</span><span>[</span><span>1</span><span>];</span><span>
$idlist</span><span>=</span><span>''</span><span>;</span><span>for</span><span>(</span><span>$i</span><span>=</span><span>1</span><span>;</span><span>$i</span><span><span>20</span><span>;</span><span>$i</span><span>++){</span><span>if</span><span>(</span><span>$i</span><span>==</span><span>1</span><span>){</span><span> $idlist</span><span>=</span><span>mt_rand</span><span>(</span><span>$idmin</span><span>,</span><span>$idmax</span><span>);</span><span>}</span><span>else</span><span>{</span><span> $idlist</span><span>=</span><span>$idlist</span><span>.</span><span>','</span><span>.</span><span>mt_rand</span><span>(</span><span>$idmin</span><span>,</span><span>$idmax</span><span>);</span><span>}</span><span>}</span><span>
$idlist2</span><span>=</span><span>"id,"</span><span>.</span><span>$idlist</span><span>;</span><span>
$sql</span><span>=</span><span>"select * from content where id in ($idlist) order by field($idlist2) LIMIT 0,12"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$n</span><span>=</span><span>1</span><span>;</span><span>
$rnds</span><span>=</span><span>''</span><span>;</span><span>while</span><span>(</span><span>$row</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>)){</span><span>
$rnds</span><span>=</span><span>$rnds</span><span>.</span><span>$n</span><span>.</span><span>". <a href="show%22</span><span>.</span><span>%24row</span><span>%5B</span><span>" id><span>].</span><span>"-"</span><span>.</span><span>strtolower</span><span>(</span><span>trim</span><span>(</span><span>$row</span><span>[</span><span>'title'</span><span>])).</span><span>"'>"</span><span>.</span><span>$row</span><span>[</span><span>'title'</span><span>].</span><span>"</span></a><br>\n"</span><span>;</span><span>
$n</span><span>++;</span><span>}</span></span></code>3万条数据查12条随机记录，需要0.001秒，效率比第二种方法又提升了4倍左右，比第一种方法提升120倍。注，这里使用了 order by
field($idlist2) 是为了不排序，否则 IN 是自动会排序的。缺点：有可能遇到ID被删除的情况，所以需要多选几个ID。
测试方法：
<code><span>$t </span><span>=</span><span> microtime</span><span>(</span><span>true</span><span>);</span><span>//执行语句</span><span> echo microtime</span><span>(</span><span>true</span><span>)</span><span>-</span><span> $t</span><span>;</span></code>
参考：
http://blog.csdn.net/zxl315/article/details/2435368
http://jan.kneschke.de/projects/mysql/order-by-rand/
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
뜨거운 도구
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
드림위버 CS6
시각적 웹 개발 도구
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
뜨거운 주제
7366
15
1628
14
1354
52
1266
25
1214
29
PHP에서 MySQL 백업 및 복원을 사용하는 방법은 무엇입니까?
Jun 03, 2024 pm 12:19 PM
PHP에서 MySQL 데이터베이스를 백업하고 복원하는 작업은 다음 단계에 따라 수행할 수 있습니다. 데이터베이스 백업: mysqldump 명령을 사용하여 데이터베이스를 SQL 파일로 덤프합니다. 데이터베이스 복원: mysql 명령을 사용하여 SQL 파일에서 데이터베이스를 복원합니다.
PHP에서 MySQL 쿼리 성능을 최적화하는 방법은 무엇입니까?
Jun 03, 2024 pm 08:11 PM
선형 복잡성에서 로그 복잡성까지 조회 시간을 줄이는 인덱스를 구축하여 MySQL 쿼리 성능을 최적화할 수 있습니다. SQL 삽입을 방지하고 쿼리 성능을 향상하려면 PREPAREDStatements를 사용하세요. 쿼리 결과를 제한하고 서버에서 처리되는 데이터의 양을 줄입니다. 적절한 조인 유형 사용, 인덱스 생성, 하위 쿼리 사용 고려 등 조인 쿼리를 최적화합니다. 쿼리를 분석하여 병목 현상을 식별하고, 캐싱을 사용하여 데이터베이스 로드를 줄이고, 오버헤드를 최소화합니다.
PHP를 사용하여 MySQL 테이블에 데이터를 삽입하는 방법은 무엇입니까?
Jun 02, 2024 pm 02:26 PM
MySQL 테이블에 데이터를 삽입하는 방법은 무엇입니까? 데이터베이스에 연결: mysqli를 사용하여 데이터베이스에 대한 연결을 설정합니다. SQL 쿼리 준비: 삽입할 열과 값을 지정하는 INSERT 문을 작성합니다. 쿼리 실행: query() 메서드를 사용하여 삽입 쿼리를 실행하면 확인 메시지가 출력됩니다.
PHP에서 MySQL 저장 프로시저를 사용하는 방법은 무엇입니까?
Jun 02, 2024 pm 02:13 PM
PHP에서 MySQL 저장 프로시저를 사용하려면: PDO 또는 MySQLi 확장을 사용하여 MySQL 데이터베이스에 연결합니다. 저장 프로시저를 호출하는 문을 준비합니다. 저장 프로시저를 실행합니다. 결과 집합을 처리합니다(저장 프로시저가 결과를 반환하는 경우). 데이터베이스 연결을 닫습니다.
PHP를 사용하여 MySQL 테이블을 만드는 방법은 무엇입니까?
Jun 04, 2024 pm 01:57 PM
PHP를 사용하여 MySQL 테이블을 생성하려면 다음 단계가 필요합니다. 데이터베이스에 연결합니다. 데이터베이스가 없으면 작성하십시오. 데이터베이스를 선택합니다. 테이블을 생성합니다. 쿼리를 실행합니다. 연결을 닫습니다.
MySQL 8.4에서 mysql_native_password가 로드되지 않음 오류를 수정하는 방법
Dec 09, 2024 am 11:42 AM
MySQL 8.4(2024년 최신 LTS 릴리스)에 도입된 주요 변경 사항 중 하나는 "MySQL 기본 비밀번호" 플러그인이 더 이상 기본적으로 활성화되지 않는다는 것입니다. 또한 MySQL 9.0에서는 이 플러그인을 완전히 제거합니다. 이 변경 사항은 PHP 및 기타 앱에 영향을 미칩니다.
C++ 프로그램 최적화: 시간 복잡도 감소 기술
Jun 01, 2024 am 11:19 AM
시간 복잡도는 입력 크기를 기준으로 알고리즘의 실행 시간을 측정합니다. C++ 프로그램의 시간 복잡성을 줄이는 팁에는 데이터 저장 및 관리를 최적화하기 위한 적절한 컨테이너(예: 벡터, 목록) 선택이 포함됩니다. Quick Sort와 같은 효율적인 알고리즘을 활용하여 계산 시간을 단축합니다. 여러 작업을 제거하여 이중 계산을 줄입니다. 불필요한 계산을 피하려면 조건부 분기를 사용하세요. 이진 검색과 같은 더 빠른 알고리즘을 사용하여 선형 검색을 최적화합니다.
오라클 데이터베이스와 mysql의 차이점
May 10, 2024 am 01:54 AM
Oracle 데이터베이스와 MySQL은 모두 관계형 모델을 기반으로 하는 데이터베이스이지만 호환성, 확장성, 데이터 유형 및 보안 측면에서 Oracle이 우수하고, MySQL은 속도와 유연성에 중점을 두고 중소 규모 데이터 세트에 더 적합합니다. ① Oracle은 광범위한 데이터 유형을 제공하고, ② 고급 보안 기능을 제공하고, ③ 엔터프라이즈급 애플리케이션에 적합하고, ① MySQL은 NoSQL 데이터 유형을 지원하고, ② 보안 조치가 적고, ③ 중소 규모 애플리케이션에 적합합니다.
