本文章来给大家介绍一篇关于mysql中使用order by 和limit查询变慢解决办法，因为在mysql分页利用limit来操作，同时排序也需要使用了order by ，所以经常会两者同是使用到，但是如果数据量大的话就会碰到查询很慢很慢，下面我来给大家介绍解决办法。
先来看一下测试性能,代码如下:
- 1.显示行0-9(10总计,查询花费32.4894秒)
- 2.SQL查询:SELECT*
- 3.FROMtables
- 4.WHEREm_id
- 5.IN(50,49)
- 6.ORDERBYidDESC
- 7.LIMIT10
- 显示行0-9(10总计,查询花费32.4894秒)
- SQL查询:SELECT*
- FROMtables
- WHEREm_id
- IN(50,49)
- ORDERBYidDESC
- LIMIT10
- 1.显示行0-9(10总计,查询花费0.0497秒)
- 2.SQL查询:SELECT*
- 3.FROMtables
- 4.WHEREm_id
- 5.IN(50,49)
- 6.LIMIT10
- 显示行0-9(10总计,查询花费0.0497秒)
- SQL查询:SELECT*
- FROMtables
- WHEREm_id
- IN(50,49)
- LIMIT10
- 1.显示行0-29(1,333总计,查询花费0.0068秒)
- 2.SQL查询:SELECT*
- 3.FROMtables
- 4.WHEREm_id
- 5.IN(50,49)
- 6.ORDERBYidDESC
- 7.
- 显示行0-29(1,333总计,查询花费0.0068秒)
- SQL查询:SELECT*
- FROMtables
- WHEREm_id
- IN(50,49)
- ORDERBYidDESC
- 1.显示行0-29(1,333总计,查询花费0.12秒)
- 2.SQL查询:SELECT*
- 3.FROMtables
- 4.WHEREm_id
- 5.IN(50,49)
- 6.ORDERBYm_id,idDESC
- 7.
- 显示行0-29(1,333总计,查询花费0.12秒)
- SQL查询:SELECT*
- FROMtables
- WHEREm_id
- IN(50,49)
- ORDERBYm_id,idDESC
- 1.显示行0-29(1,333总计,查询花费0.0068秒)
- 2.SQL查询:SELECT*
- 3.FROMtables
- 4.FORCEINDEX(m_id)//强制索引
- 5.WHEREm_id
- 6.IN(50,49)
- 7.ORDERBYidDESC
- 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,比如代码如下:
