mysql中使用order by 和limit查询变慢解决办法

本文章来给大家介绍一篇关于mysql中使用order by 和limit查询变慢解决办法，因为在mysql分页利用limit来操作，同时排序也需要使用了order by ，所以经常会两者同是使用到，但是如果数据量大的话就会碰到查询很慢很慢，下面我来给大家介绍解决办法。

先来看一下测试性能,代码如下:

1. 1.显示行0-9(10总计,查询花费32.4894秒)
2. 2.SQL查询:SELECT*
3. 3.FROMtables
4. 4.WHEREm_id
5. 5.IN(50,49)
6. 6.ORDERBYidDESC
7. 7.LIMIT10
8. 显示行0-9(10总计,查询花费32.4894秒)
9. SQL查询:SELECT*
10. FROMtables
11. WHEREm_id
12. IN(50,49)
13. ORDERBYidDESC
14. LIMIT10
17. 1.显示行0-9(10总计,查询花费0.0497秒)
18. 2.SQL查询:SELECT*
19. 3.FROMtables
20. 4.WHEREm_id
21. 5.IN(50,49)
22. 6.LIMIT10
23. 显示行0-9(10总计,查询花费0.0497秒)
24. SQL查询:SELECT*
25. FROMtables
26. WHEREm_id
27. IN(50,49)
28. LIMIT10
31. 1.显示行0-29(1,333总计,查询花费0.0068秒)
32. 2.SQL查询:SELECT*
33. 3.FROMtables
34. 4.WHEREm_id
35. 5.IN(50,49)
36. 6.ORDERBYidDESC
37. 7.
39. 显示行0-29(1,333总计,查询花费0.0068秒)
40. SQL查询:SELECT*
41. FROMtables
42. WHEREm_id
43. IN(50,49)
44. ORDERBYidDESC
47. 1.显示行0-29(1,333总计,查询花费0.12秒)
48. 2.SQL查询:SELECT*
49. 3.FROMtables
50. 4.WHEREm_id
51. 5.IN(50,49)
52. 6.ORDERBYm_id,idDESC
53. 7.
55. 显示行0-29(1,333总计,查询花费0.12秒)
56. SQL查询:SELECT*
57. FROMtables
58. WHEREm_id
59. IN(50,49)
60. ORDERBYm_id,idDESC
64. 1.显示行0-29(1,333总计,查询花费0.0068秒)
65. 2.SQL查询:SELECT*
66. 3.FROMtables
67. 4.FORCEINDEX(m_id)//强制索引
68. 5.WHEREm_id
69. 6.IN(50,49)
70. 7.ORDERBYidDESC
71. 8.

上面的办法如果数据量上千万级也是会很慢的,有可能查询一次到10秒或更长.

优化limit和offset,MySQL的limit工作原理就是先读取n条记录,然后抛弃前n条,读m条想要的,所以n越大,性能会越差,代码如下:

优化前SQL:SELECT * FROM member ORDER BY last_active LIMIT 50,5

优化后SQL:SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT50,5) USING (member_id)

分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行,而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列.

对mysql服务器优化也可以提升性能了.

1、只返回需要的数据

返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据,就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的,避免这类事件需要注意:

A、横向来看,不要写SELECT *的语句,而是选择你需要的字段.

B、纵向来看,合理写WHERE子句,不要写没有WHERE的SQL语句.

C、注意SELECT INTO后的WHERE子句,因为SELECT INTO把数据插入到临时表,这个过程会锁定一些系统表,如果这个WHERE子句返回的数据过多或者速度太慢,会造成系统表长期锁定,诸塞其他进程.

D、对于聚合查询,可以用HAVING子句进一步限定返回的行.

2、尽量少做重复的工作

这一点和上一点的目的是一样的,就是尽量减少无效工作,但是这一点的侧重点在客户端程序,需要注意的如下:

A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

D、合并对同一表同一条件的多次UPDATE,比如代码如下: