跳到主要内容

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

· 阅读需 3 分钟
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

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镜像的方式提供部署安装。

联系我们​