SQL优化技巧 - 排序方向不同导致索引失效

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公众号