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
  15. 1.显示行0-9(10总计,查询花费0.0497秒)
  16. 2.SQL查询:SELECT*
  17. 3.FROMtables
  18. 4.WHEREm_id
  19. 5.IN(50,49)
  20. 6.LIMIT10
  21. 显示行0-9(10总计,查询花费0.0497秒)
  22. SQL查询:SELECT*
  23. FROMtables
  24. WHEREm_id
  25. IN(50,49)
  26. LIMIT10
  27. 1.显示行0-29(1,333总计,查询花费0.0068秒)
  28. 2.SQL查询:SELECT*
  29. 3.FROMtables
  30. 4.WHEREm_id
  31. 5.IN(50,49)
  32. 6.ORDERBYidDESC
  33. 7.
  34. 显示行0-29(1,333总计,查询花费0.0068秒)
  35. SQL查询:SELECT*
  36. FROMtables
  37. WHEREm_id
  38. IN(50,49)
  39. ORDERBYidDESC
  40. 1.显示行0-29(1,333总计,查询花费0.12秒)
  41. 2.SQL查询:SELECT*
  42. 3.FROMtables
  43. 4.WHEREm_id
  44. 5.IN(50,49)
  45. 6.ORDERBYm_id,idDESC
  46. 7.
  47. 显示行0-29(1,333总计,查询花费0.12秒)
  48. SQL查询:SELECT*
  49. FROMtables
  50. WHEREm_id
  51. IN(50,49)
  52. ORDERBYm_id,idDESC
  53. 1.显示行0-29(1,333总计,查询花费0.0068秒)
  54. 2.SQL查询:SELECT*
  55. 3.FROMtables
  56. 4.FORCEINDEX(m_id)//强制索引
  57. 5.WHEREm_id
  58. 6.IN(50,49)
  59. 7.ORDERBYidDESC
  60. 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,比如代码如下:

UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’
相关广告
  • mysql中使用order by 和limit查询变慢解决办法 mysql中使用order by 和limit查询变慢解决办法 mysql中使用order by 和limit查询变慢解决办法
相关阅读

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

2019/10/10 17:34:41 | 谷歌SEO算法 | 自媒体