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

作者：袖梨 2022-11-14

先来看一下测试性能

代码如下

复制代码

1.显示行 0 - 9 (10 总计, 查询花费 32.4894 秒)
2.SQL 查询: SELECT *
3.FROM tables
4.WHERE m_id
5.IN ( 50, 49 )
6.ORDER BY id DESC
7.LIMIT 10
显示行 0 - 9 (10 总计, 查询花费 32.4894 秒)
SQL 查询: SELECT *
FROM tables
WHERE m_id
IN ( 50, 49 )
ORDER BY id DESC
LIMIT 10

1.显示行 0 - 9 (10 总计, 查询花费 0.0497 秒)
2.SQL 查询: SELECT *
3.FROM tables
4.WHERE m_id
5.IN ( 50, 49 )
6.LIMIT 10
显示行 0 - 9 (10 总计, 查询花费 0.0497 秒)
SQL 查询: SELECT *
FROM tables
WHERE m_id
IN ( 50, 49 )
LIMIT 10

1.显示行 0 - 29 (1,333 总计, 查询花费 0.0068 秒)
2.SQL 查询: SELECT *
3.FROM tables
4.WHERE m_id
5.IN ( 50, 49 )
6.ORDER BY id DESC
7.

显示行 0 - 29 (1,333 总计, 查询花费 0.0068 秒)
SQL 查询: SELECT *
FROM tables
WHERE m_id
IN ( 50, 49 )
ORDER BY id DESC

1.显示行 0 - 29 (1,333 总计, 查询花费 0.12秒)
2.SQL 查询: SELECT *
3.FROM tables
4.WHERE m_id
5.IN ( 50, 49 )
6.ORDER BY m_id, id DESC
7.

显示行 0 - 29 (1,333 总计, 查询花费 0.12秒)
SQL 查询: SELECT *
FROM tables
WHERE m_id
IN ( 50, 49 )
ORDER BY m_id, id DESC

1.显示行 0 - 29 (1,333 总计, 查询花费 0.0068 秒)
2.SQL 查询: SELECT *
3.FROM tables
4.FORCE INDEX ( m_id ) //强制索引
5.WHERE m_id
6.IN ( 50, 49 )
7.ORDER BY id DESC
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 LIMIT

50, 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子句进一步限定返回的行。