rand()函數埋的一個坑，大家注意了

一、背景

在開發部落格網站随機文章的時候，剛開始沒有注意到這個問題，随便加了一個rand()函數，資料量小的時候性能還可以，一旦資料達到幾十萬的時候，就會産生性能問題。

二、問題分析

剛開始的代碼如下：

1. if(!StringUtils.isEmpty(postParam.getSortType())){

2. if(postParam.getSortType().equals(PostConstant.SORTTYPE_COMMMENT)){

3. example.setOrderByClause(" comment_count desc ");

4. }else if(postParam.getSortType().equals(PostConstant.SORTTYPE_DATE)){

5. example.setOrderByClause(" post_date desc ");

6. }else if(postParam.getSortType().equals(PostConstant.SORTTYPE_RANDOM)){

7. example.setOrderByClause(" RAND() ");

8. log.info("開始加載随機文章清單。。。。");

9. }else if(postParam.getSortType().equals(PostConstant.SORTTYPE_VIEW)){

10. example.setOrderByClause(" post_date desc ");

11. }

12. }else{

13. example.setOrderByClause(" post_date desc ");

14. }

15. Page<WpPosts> page =(Page<WpPosts>) wpPostsMapper.selectByExample(example);

啟動程式，檢視随機文章，背景日志報錯

1. INFO | 2018-11-19 18:43:53,040 | JWordpres-v2.0 | [http-nio-80-exec-17-47] (o.s.b.f.x.XmlBeanDefinitionReader:317) | Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]

2. INFO | 2018-11-19 18:43:53,202 | JWordpres-v2.0 | [http-nio-80-exec-17-47] (o.s.j.s.SQLErrorCodesFactory:126) | SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

3. ERROR | 2018-11-19 18:43:53,231 | JWordpres-v2.0 | [http-nio-80-exec-17-47] (o.a.c.c.C.[.[.[.[dispatcherServlet]:181) | Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException:

4. ### Error querying database. Cause: java.sql.SQLException: Incorrect key file for table '/tmp/#sql_7c6d_0.MYI'; try to repair it

5. ### The error may exist in cn/liuhaihua/web/mapper/WpPostsMapper.java (best guess)

6. ### The error may involve cn.liuhaihua.web.mapper.WpPostsMapper.selectByExample-Inline

7. ### The error occurred while setting parameters

8. ### SQL: SELECT id,post_author,post_date,post_date_gmt,post_content,post_title,post_excerpt,post_status,comment_status,ping_status,post_password,post_name,to_ping,pinged,post_modified,post_modified_gmt,post_content_filtered,post_parent,guid,menu_order,post_type,post_mime_type,comment_count FROM wp_posts WHERE ( post_type = ? and post_status = ? ) order by RAND() LIMIT 10

9. ### Cause: java.sql.SQLException: Incorrect key file for table '/tmp/#sql_7c6d_0.MYI'; try to repair it

10. ; uncategorized SQLException for SQL []; SQL state [HY000]; error code [126]; Incorrect key file for table '/tmp/#sql_7c6d_0.MYI'; try to repair it; nested exception is java.sql.SQLException: Incorrect key file for table '/tmp/#sql_7c6d_0.MYI'; try to repair it] with root cause

11. java.sql.SQLException: Incorrect key file for table '/tmp/#sql_7c6d_0.MYI'; try to repair it

檢視sql 發現如下代碼：

1. SELECT

2. id,

3. post_author,

4. post_date,

5. post_date_gmt,

6. post_content,

7. post_title,

8. post_excerpt,

9. post_status,

10. comment_status,

11. ping_status,

12. post_password,

13. post_name,

14. to_ping,

15. pinged,

16. post_modified,

17. post_modified_gmt,

18. post_content_filtered,

19. post_parent,

20. guid,

21. menu_order,

22. post_type,

23. post_mime_type,

24. comment_count

25. FROM

26. wp_posts

27. WHERE

28. (

29. post_type = "post"

30. AND post_status = "publish"

31. )

32. ORDER BY

33. RAND()

34. LIMIT 10

這個sql會造成嚴重的性能問題，rand()造成在系統檔案上來回排序。非常損耗性能

1. [Err] 126 - Incorrect key file for table '/tmp/#sql_7c6d_0.MYI'; try to repair it

三、優化方案

知道問題所在，優化方案其實也蠻簡單的。原理如下

1. 1首先 select count(*) from test where $where; （計算所需要的資料的總條數）

2. 2然後 $id=rand($a[0],$a[1]); 産生一個随機數;

3. 3最後 SELECT * FROM tablename WHERE id>='$id' LIMIT 1 将上面産生的随機數帶入查詢;

修改代碼如下：

1. if(!StringUtils.isEmpty(postParam.getSortType())){

2. if(postParam.getSortType().equals(PostConstant.SORTTYPE_COMMMENT)){

3. example.setOrderByClause(" comment_count desc ");

4. }else if(postParam.getSortType().equals(PostConstant.SORTTYPE_DATE)){

5. example.setOrderByClause(" post_date desc ");

6. }else if(postParam.getSortType().equals(PostConstant.SORTTYPE_RANDOM)){

7. //example.setOrderByClause(" RAND() ");

8. /***

9. * 首先 select count(*) from test where $where; （計算所需要的資料的總條數）

10. *然後 $id=rand($a[0],$a[1]); 産生一個随機數;

11. *最後 SELECT * FROM tablename WHERE id>='$id' LIMIT 1 将上面産生的随機數帶入查詢;

12. */

13. log.info("開始加載随機文章清單。。。。");

14. Random random = new Random();

15. int randId =random.nextInt(count);

16. criteria.andGreaterThan("id", randId);

17. }else if(postParam.getSortType().equals(PostConstant.SORTTYPE_VIEW)){

18. example.setOrderByClause(" post_date desc ");

19. }

20. }else{

21. example.setOrderByClause(" post_date desc ");

22. }

23. Page<WpPosts> page =(Page<WpPosts>) wpPostsMapper.selectByExample(example);

這樣修改後之後，系統完美運作，sql查詢時間縮短到0.058秒

四、總結

其實mysql官網也說明這種情況了，意思是說當記錄超過30萬，rand這種方法就不可用,需要更換方案。

1. works for small tables, but once the tables grow larger than 300,000 records or

2. so this will be very slow because MySQL will have to process ALL the entries from the table,

3. order them randomly and then return the first row of the ordered result,

4. and this sorting takes long time.

5. Instead you can do it like this (atleast if you have an auto_increment PK):

官方建議修改成這樣

1. SELECT MIN(id), MAX(id) FROM tablename;

2. Fetch the result into $a

3. $id=rand($a[0],$a[1]);

4. SELECT * FROM tablename WHERE id>='$id' LIMIT 1

原文釋出時間為：2018-11-20

本文作者：HARRIES

本文來自雲栖社群合作夥伴“

Java雜記

”，了解相關資訊可以關注“

”。