MySql sql优化之order by desc/asc limit M

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描;看起来非常的简单,但是我经常看到很多性能较差的sql没有利用这个优化规律,下面将结合一些实际的案例来分析说明:

案例一:

一条sql执行非常的慢,执行时间为:

root@test 02:00:44
 
SELECT * FROM test_order_desc WHERE  END_TIME>now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12;
 
+---------+-----------+------------+------+---------------------+---------------------+-------------------
Data1.....................................................................................................
 
Data2.....................................................................................................
 
+---------+-----------+------------+------+---------------------+---------------------+-------------------
12 ROWS IN SET (0.49 sec)
执行计划如下:
root@test_db01:53:23
 
EXPLAIN SELECT * FROM test_order_desc  WHERE  END_TIME > now()
 ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12;
 
+----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+-----
 
| id | select_type | TABLE    | TYPE  | possible_keys   | KEY    | key_len | REF  | ROWS   | Extra     |
 
+----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+-----
 
|  1 | SIMPLE      | test_order_desc | range | ind_hot_endtime | ind_hot_endtime | 9       | NULL | 113549 | USING WHEREUSING filesort |
 
+----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+-----

Ind_hot_endtime索引为:

root@test_db01:52:45:SHOW INDEX FROM test_order_desc;
 
Ind_hot_endtime(end_time,count_num)

在注意到sql中满足过滤条件end_time>now()的有113549行,在加上剩余的条件中含有order by,这样会造成排序的结果集非常的大,执行非常的耗费资源;于是分析sql,在sql中包括了order by desc limit这样的排序条件后,新增适当的索引满足排序的条件,同时由于有limit的限制结果集,当扫描到满足条件的行数后退出查询,那么我们来看看优化效果:

添加索引:

root@test 02:01:06:ALTER TABLE test_order_desc ADD INDEX ind_gmt_create(gmt_create,count_num);
 
Query OK, 211945 ROWS affected (6.71 sec)
 
Records: 211945  Duplicates: 0  Warnings: 0

再次执行sql,观察其执行时间:

root@test 02:01:35:
 
SELECT  * FROM test_order_desc WHERE  END_TIME > now()   ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12;
 
+---------+-----------+------------+------+---------------------+---------------------+
col2...................................................................................
 
+---------+-----------+------------+------+---------------------+---------------------+
 
Data1..................................................................................
 
Data2..................................................................................
 
+---------+-----------+------------+------+---------------------+---------------------+
 
12 ROWS IN SET (0.00 sec)

可以看到执行时间已经降到了毫秒以下,查看其执行计划:

root@test 02:01:42:
 
EXPLAIN SELECT * FROM test_order_desc  WHERE  END_TIME > now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12;
 
+----+-------------+----------+-------+-----------------+----------------+---------+------+------+-------------+
 
| id | select_type | TABLE    | TYPE  | possible_keys   | KEY  | key_len | REF  | ROWS | Extra |
 
+----+-------------+----------+-------+-----------------+----------------+---------+------+------+--------
 
|  1 | SIMPLE      | test_order_desc | INDEX | ind_hot_endtime | ind_gmt_create | 14      | NULL |  48 | USING WHERE |

可以看到优化器已经选择了ind_gmt_create索引扫描,这样的话就避免了对结果集进行排序的过程,同时优化器预估扫描14行数据就会得到满足查询条件的数据(END_TIME > now()),执行计划非常的理想。

 

案例二:

root@127.0.0.1 : test_db 16:05:15:
EXPLAIN  SELECT b.*,a.*,k.*    FROM instance b LEFT OUTER JOIN image a  ON b.image_id=a.image_id  LEFT OUTER JOIN key_pair k  ON b.key_pair_id=k.key_pair_id  LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS  IN (1,8) AND   b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;


B表的idx_uid_stat_inid的索引列包括了(user_id,status,instance_no):

我们从执行计划上分析来看,表的连接顺序为:b—>r_a—>a—>k,可以看到执行计划的第一行中需要扫描49212行的数据,同时由于status采用的是in的方式,instance_no即使在索引中也用不上,这样就导致了排序使用到了临时表,这也是导致sql执行慢的原因。我们看到sql中的最后一个排序为order by b.instance_no asc limit 37300,50,这里我们好像可以看到优化的曙光,调整数据库的索引以满足B表的排序需求:

root@127.0.0.1 : test_db 16:05:04 ALTER TABLE instance ADD INDEX ind_user_id(user_id,instance_no);
 
Query OK, 0 ROWS affected (0.56 sec)

调整索引后查看执行计划:

root@127.0.0.1 : test_db 16:09:42
EXPLAIN  SELECT b.*,a.*,k.*    FROM instance b LEFT OUTER JOIN image a  ON b.image_id=a.image_id  LEFT OUTER JOIN key_pair k  ON b.key_pair_id=k.key_pair_id  LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS  IN (1,8) AND   b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;

我们加上force index强制走我们新加的索引:

root@127.0.0.1 : test_db 16:10:24
EXPLAIN  SELECT b.*,a.*,k.*    FROM instance b  force INDEX (ind_user_id) LEFT OUTER JOIN image a  ON b.image_id=a.image_id  LEFT OUTER JOIN key_pair k  ON b.key_pair_id=k.key_pair_id  LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS  IN (1,8) AND   b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;

可以看到在加上提示符后,使用到了我们新加的索引,扫描的行数为54580行,执行时间:

root@127.0.0.1 : test_db 16:10:30
SELECT b.*,a.*,k.*    FROM instance b  force INDEX (ind_user_id) LEFT OUTER JOIN image a  ON b.image_id=a.image_id  LEFT OUTER JOIN key_pair k  ON b.key_pair_id=k.key_pair_id  LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND   b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;
(0.49 sec)

原始的执行时间:
root@127.0.0.1 : test_db 16:10:51:

SELECT b.*,a.*,k.*    FROM instance b   LEFT OUTER JOIN image a  ON b.image_id=a.image_id  LEFT OUTER JOIN key_pair k  ON b.key_pair_id=k.key_pair_id  LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS  IN (1,8) AND   b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;
(1.28 sec)

总结:
Order by desc/asc limit的优化技术有时候在你无法建立很好索引的时候,往往会得到意想不到的优化效果,但有时候有一定的局限性,优化器可能不会按照你既定的索引路径扫描,优化器需要考虑到查询列的过滤性以及limit的长度,当查询列的选择性非常高的时候,使用sort的成本是不高的,当查询列的选择性很低的时候,那么使用order by +limit的技术是很有效的。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
1月前
|
SQL Oracle 关系型数据库
MySQL的sql_mode模式说明及设置
MySQL的sql_mode模式说明及设置
434 112
|
22天前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
21天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
20天前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
150 11
|
6天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
12月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
421 13
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
271 9

推荐镜像

更多