SQL优化技巧 - 排序方向不同导致索引失效
· 阅读需 3 分钟
Copyright © 2023 PawSQL
问题定义
ORDER BY 子句中的所有表达式必须按统一的 ASC 或 DESC 方向排序,以便利用索引;如果ORDER BY 语句对多个不同条件使用不同方向的排序无法使用索引。
譬如在TPCH的lineitem
的表上创建索引:
create index l_partkey_suppkey_idx on lineitem(l_partkey, l_suppkey);
- 正向索引扫描,正确利用索引,避免排序
explain analyze select * from lineitem l order by l.L_PARTKEY, l.L_SUPPKEY limit 1;
-> Limit: 1 row(s) (cost=0.00 rows=1) (actual time=0.065..0.065 rows=1 loops=1)
-> Index scan on l using LINEITEM_FK2 (cost=0.00 rows=1) (actual time=0.063..0.063 rows=1 loops=1)
- 反向索引扫描,正确利用索引,避免排序
explain analyze select * from lineitem l order by l.L_PARTKEY desc, l.L_SUPPKEY desc limit 1;
-> Limit: 1 row(s) (cost=0.00 rows=1) (actual time=0.033..0.033 rows=1 loops=1)
-> Index scan on l using LINEITEM_FK2 (reverse) (cost=0.00 rows=1) (actual time=0.032..0.032 rows=1 loops=1)
- 无法利用索引,全表扫描
explain analyze select * from lineitem l order by l.L_PARTKEY desc, l.L_SUPPKEY limit 1;
-> Limit: 1 row(s) (cost=33963.20 rows=1) (actual time=246.462..246.462 rows=1 loops=1)
-> Sort: l.L_PARTKEY DESC, l.L_SUPPKEY, limit input to 1 row(s) per chunk (cost=33963.20 rows=330097) (actual time=246.460..246.460 rows=1 loops=1)
-> Table scan on l (cost=33963.20 rows=330097) (actual time=0.109..186.665 rows=330122 loops=1)
审查预警
PawSQL针对此情况提供相应的SQL审查预警。
预警级别
- 提示
预警触发条件
- 排序表达式是字段,无运算
- 排序字段来自同一张数据库表
- 排序存在升序(不指定为升序)和降序的混合
数据库类型及版本
- MySQL
- openGauss
- Oracle
- PostgreSQL
- KingbaseES
- MariaDB
关于PawSQL
PawSQL专注数据库性能优化的自动化和智能化,支持MySQL,PostgreSQL,openGauss,Oracle等,提供的SQL优化产品包括
- PawSQL Cloud,在线自动化SQL优化工具,支持SQL审查,智能查询重写、基于代价的索引推荐,适用于数据库管理员及数据应用开发人员,
- PawSQL Advisor,IntelliJ 插件, 适用于数据应用开发人员,可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
- PawSQL Engine, 是PawSQL系列产品的后端优化引擎,可以独立安装部署,并通过http/json的接口提供SQL优化服务。PawSQL Engine以docker镜像的方式提供部署安装。
联系我们
- 邮件:service@pawsql.com
- Twitter: https://twitter.com/pawsql
- 扫描关注PawSQL公众号