一、背景

在开发博客网站随机文章的时候，刚开始没有注意到这个问题，随便加了一个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杂记”，了解相关信息可以关注“Java杂记”。