SQL优化技巧 - 如何创建高效的索引

2024年1月15日 · 阅读需 19 分钟

PawSQL Team

Optimize your SQL Queries by Clicks!

本文介绍索引创建时需遵循一些准则，以达到最佳的查询性能和数据维护效率。本文基于最常用的B+树索引来举例，其他索引的使用有一定的限制条件，具体请参考《数据库索引的类型》。

基于您的工作负载创建索引

创建高效的索引最重要的原则是，基于您的工作负载（workload）创建索引，而不是基于您的表结构。索引的目的是为了提升数据库中操作的效率，因此任何其他不以工作负载出发的索引创建方法都是错误的。

针对数据库执行的所有 SQL 语句构成了该数据库的工作负载。在针对一个工作负载构建一组索引时，需要考虑工作负载的以下属性：

SQL类型，用户执行频繁插入新数据和修改现有数据的OLTP场景，多个索引可能会对性能产生负面影响，并对系统资源造成压力，建议创建最少数量的索引来满足您的索引要求。而在以查询为主的OLAP场景，您可以添加更多索引，每个索引具有多个键列，甚至可以添加函数索引和条件索引。
SQL频率，最为最频繁使用的查询创建索引，通过为这些查询创建最好的索引，能够最大限度的提升系统的整体性能。
SQL的重要性，查询越重要，您可能越想通过创建索引来进行性能优化。
SQL的本身的结构。

基于单个SQL的结构创建索引

索引的作用如下：

快速定位数据
避免排序
避免回表

本章节我们将通过分析SQL的结构来实现以上的功能，创建高效的索引。

为快速定位创建索引

索引可以通过匹配查询的条件快速的定位数据，查询的条件可能在WHERE子句、HAVING子句、ON子句中。索引和条件的匹配原则遵循最左前缀匹配原则.

最左前缀原则

最左前缀原则指的是，如果查询的时候等值的查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到，同时遇到范围查询(>、<、between、like)就会停止匹配，包括范围条件。

对于联合索引lineitem(l_shipdate,l_quantity),下面的SQL中前两个符合最左前缀原则，可以使用该索引。最后一个不满足最左前缀原则，无法使用该索引。

select * from lineitem where l_shipdate = date '2021-12-01' and l_quantity = 100; -- 可以使用索引
select * from lineitem where l_shipdate = date '2021-12-01'; -- 可以使用索引
select * from lineitem where l_quantity = 100; -- 不满足最左前缀原则，无法使用该索引

这三个SQL对于的执行计划如下：

-> Index lookup on lineitem using lidx (L_QUANTITY=100.00, L_SHIPDATE=DATE'2021-12-01')  (cost=0.35 rows=1)

-> Index lookup on lineitem using lidx ( L_SHIPDATE=DATE'2021-12-01')  (cost=0.35 rows=1)

-> Filter: (lineitem.L_QUANTITY = 100.00)  (cost=15208.05 rows=49486)
    -> Table scan on lineitem  (cost=15208.05 rows=148473)

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数(Cardinality)，Cardinality较大的放前面。

等值条件

单表等值条件
- COL = 'A'
- COL IN ('A')

关联等值条件, 在一个表作为被驱动表时的等值关联条件也可以被认为时等值条件被索引匹配使用。

T1.COL = T2.COL

select * from orders, lineitem where o_orderkey = l_orderkey;

-> Nested loop inner join  (cost=484815.77 rows=1326500)
    -> Table scan on orders  (cost=20540.71 rows=200128)
    -> Index lookup on lineitem using lineitem_idx(L_ORDERKEY=orders.O_ORDERKEY)  (cost=1.66 rows=7)

范围条件

范围操作符（>,>=,<,<=,BETWEEN）
IN ('A','B')
IS NOT NULL
IS NULL
LIKE 'ABC%'
COL = 'A' OR COL = 'B'

范围条件也可以用来快速定位数据，

create index lshipdate_idx on lineitem(l_shipdate);
explain format = tree select * from lineitem where l_shipdate >= date '2021-12-01';

-> Index range scan on lineitem using lshipdate_idx over ('2021-12-01' <= L_SHIPDATE), with index condition: (lineitem.L_SHIPDATE >= DATE'2021-12-01')  (cost=11855.06 rows=26344)

由于最左匹配原则，位于范围条件后面的索引列无法利用该索引。

为避免排序创建索引

对于B+树索引，由于其是按照索引键排序的，因此可以通过索引来避免在SQL执行中进行排序。涉及的SQL结构主要包括：

GROUP BY
ORDER BY
DISTINCT
PARTITION BY... ORDER BY...

create index lshipdate_idx on lineitem(l_shipdate);

可以看到下面的SQL的执行计划通过访问lshipdate_idx索引避免了排序

SQL1 (ORDER BY)

select * from lineitem order by l_shipdate limit 10;

SQL1执行计划

-> Limit: 10 row(s)  (cost=0.02 rows=10)
    -> Index scan on lineitem using lshipdate_idx  (cost=0.02 rows=10)

SQL2(GROUP BY)

select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by l_shipdate;

SQL2执行计划

-> Group aggregate: sum(lineitem.L_QUANTITY)  (cost=30055.35 rows=148473)
    -> Index scan on lineitem using lshipdate_idx  (cost=15208.05 rows=148473)

SQL3(DISTINCT)

select DISTINCT l_shipdate from lineitem;

SQL3执行计划

-> Covering index skip scan for deduplication on lineitem using lshipdate_idx  (cost=4954.90 rows=15973)

SQL4(PARTITION BY... ORDER BY...)

select rank() over (partition by L_SHIPDATE order by L_ORDERKEY)  from lineitem;

SQL4执行计划

WindowAgg  (cost=0.29..545.28 rows=10000 width=28)
->  Index Only Scan using lshipdate_idx on lineitem  (cost=0.29..370.28 rows=10000 width=20)

需要注意：

对于分组和去重，顺序不一致是没有关系的；
对于排序，排序字段的顺序需要和索引字段的顺序一致，否则没有办法利用索引来避免排序；
对于同时有分组和排序的，需要把索引列放在前面。

譬如对于下面的SQL

select l_shipdate, l_orderkey,  sum(l_quantity) as sum_qty from lineitem group by l_shipdate,l_orderkey order by l_orderkey;

情形1，建索引(l_shipdate, l_orderkey)，索引访问，需排序，代价为486.526

-> Sort: lineitem.L_ORDERKEY  (actual time=479.465..486.526 rows=149413 loops=1)
    -> Stream results  (cost=30055.35 rows=148473) (actual time=0.175..423.447 rows=149413 loops=1)
        -> Group aggregate: sum(lineitem.L_QUANTITY)  (cost=30055.35 rows=148473) (actual time=0.170..394.978 rows=149413 loops=1)
            -> Index scan on lineitem using lshipdate_idx2  (cost=15208.05 rows=148473) (actual time=0.145..359.567 rows=149814 loops=1)

情形2，建索引(l_orderkey,l_shipdate)，索引访问，避免排序，代价228.401，性能提升120%

-> Group aggregate: sum(lineitem.L_QUANTITY)  (cost=30055.35 rows=148473) (actual time=0.067..228.401 rows=149413 loops=1)
    -> Index scan on lineitem using lshipdate_idx3  (cost=15208.05 rows=148473) (actual time=0.052..194.479 rows=149814 loops=1)

为避免回表创建索引

当查询中的列都在索引列中时，数据库只需要访问索引即可获取所需的数据，避免了回表操作。在某些场景下，可以大幅的提升查询效率。

对于如下的SQL语句，

select l_shipdate, l_orderkey,  sum(l_quantity) as sum_qty from lineitem group by l_orderkey,l_shipdate;

索引（l_orderkey,l_shipdate）中没有包含l_quantity，需要回表，执行计划如下，代价194.875

-> Group aggregate: sum(lineitem.L_QUANTITY)  (cost=30055.35 rows=148473) (actual time=0.044..194.875 rows=149413 loops=1)
    -> Index scan on lineitem using lshipdate_idx3  (cost=15208.05 rows=148473) (actual time=0.034..159.863 rows=149814 loops=1)

索引（l_orderkey,l_shipdate，l_quantity ）中包含l_quantity，不需要回表，执行计划如下，代价113.433，性能提升约71.8%

-> Group aggregate: sum(lineitem.L_QUANTITY)  (cost=30055.35 rows=148473) (actual time=0.035..113.433 rows=149413 loops=1)
    -> Covering index scan on lineitem using lshipdate_idx4  (cost=15208.05 rows=148473) (actual time=0.026..82.266 rows=149814 loops=1)

其他相关

分区表的索引

对于分区表，不同的数据库对分区表索引的支持不一样，总体来说，分区表可以建立以下三种类型的索引

本地分区索引（PostgreSQL/MySQL/Oracle/Opengauss）
全局分区索引（Oracle）
全局非分区索引（Oracle/Opengauss）

本地分区索引

在索引维护性方面，本地索引比全局索引容易管理，当你在进行添加、删除、truncate表分区时，本地索引会自动维护其索引分区。MySQL和PostgreSQL只支持本地分区索引; Oracle和Opengauss创建本地分区索引时需指定关键字local.

create index lshipdate_idx on lineitem(l_shipdate) local;

全局分区索引

和表分区类似，索引的分区键和表的分区键没有必然的关系，甚至非分区表也可以建立全局分区索引。Oracle支持全局分区索引。

全局非分区索引

对于全局非分区索引，当你对表分区进行操作时，索引可能变得不可用，需显式的更新或重建索引。在索引效率方面，全局索引在不包含分区字段的查询中，效率比本地分区索引更高效。Oracle和Opengauss为分区表默认创建的是全局非分区索引。

create index lshipdate_idx on lineitem(l_shipdate) global;
create index lshipdate_idx on lineitem(l_shipdate);

在进行分区操作时，需要增加update global index关键字重建索引，否则索引不可用。

alter table t DROP PARTITION partition_name update global index;

函数索引

函数索引（或表达式索引）即基于函数或表达式的索引，它使用函数或是表达式提供计算好的值作为索引列构建索引，可以在不修改应用程序的情况下提高查询性能。

函数索引的使用需要函数或是表达式和SQL查询中的表达式严格匹配，所以它使用的条件较为严格，适合针对重要查询或是频次较高的查询重点优化。

select * from lineitem where EXTRACT(DAY from l_shipdate) = 1;

建在l_shipdate在执行计划中没有被使用

Seq Scan on lineitem  (cost=0.00..1870.24 rows=238 width=158) (actual time=0.502..10.655 rows=1616 loops=1)
  Filter: (EXTRACT(day FROM l_commitdate) = '1'::numeric)
  Rows Removed by Filter: 46000
Planning Time: 0.107 ms
Execution Time: 10.709 ms

通过创建函数索引，执行计划中的代价降低为原来的1/10.

create index idx on lineitem(EXTRACT(DAY from l_shipdate));

Bitmap Heap Scan on lineitem  (cost=6.13..593.60 rows=238 width=158) (actual time=0.216..0.981 rows=1620 loops=1)
  Recheck Cond: (EXTRACT(day FROM l_shipdate) = '1'::numeric)
  Heap Blocks: exact=889
  ->  Bitmap Index Scan on idx  (cost=0.00..6.08 rows=238 width=0) (actual time=0.149..0.149 rows=1620 loops=1)
        Index Cond: (EXTRACT(day FROM l_shipdate) = '1'::numeric)
Planning Time: 0.102 ms
Execution Time: 1.075 ms

条件索引

部分索引(Partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的，该索引只包含表中那些满足这个条件表达式的行。

条件索引被使用的条件比较严格，只有在数据库能够识别出该查询的WHERE条件在逻辑上涵盖了该索引的条件表达式定义时，这个部分索引才能被用于该查询。

以下的条件索引为例，其索引的条件表达式为l_shipdate > '2022-01-01'

create index l_partkey_idx on lineitem(l_partkey) where l_shipdate > '2022-01-01';

由于下面的查询语句的条件l_shipdate = date '2021-12-01'没有落到此索引条件表达式的范围内，该索引将不会被用到，所以执行计划采用的是全表扫描。

select l_partkey , count(1) from lineitem where l_shipdate = date '2021-12-01' and l_partkey < 100 group by l_partkey ;

GroupAggregate  (cost=1870.25..1870.27 rows=1 width=12)
  Group Key: l_partkey
  ->  Sort  (cost=1870.25..1870.26 rows=1 width=4)
        Sort Key: l_partkey
        ->  Seq Scan on lineitem  (cost=0.00..1870.24 rows=1 width=4)
              Filter: ((l_partkey < 100) AND (l_shipdate = '2021-12-01'::date))

而下面的查询语句的条件l_shipdate = date '2022-12-01'在条件表达式的范围内，数据库优化器将会采用此索引，可以看到性能有大幅提升。

select l_partkey , count(1) from lineitem where l_shipdate = date '2022-12-01' and l_partkey < 100 group by l_partkey ;

GroupAggregate  (cost=402.37..402.39 rows=1 width=12)
  Group Key: l_partkey
  ->  Sort  (cost=402.37..402.38 rows=1 width=4)
        Sort Key: l_partkey
        ->  Index Scan using lorderkey_idx on lineitem  (cost=0.28..402.36 rows=1 width=4)
              Filter: ((l_partkey < 100) AND (l_shipdate = '2022-12-01'::date))

特别提醒：MySQL目前还不支持条件索引，而PostgreSQL、Opengauss、Oracle都支持。

索引融合

索引融合（Index Merge）是使用多个索引来完成一次单表数据访问的优化技术。当查询中涉及一个表的多个条件时，如果这些条件分别有合适的索引，索引融合可以在回表之前将多个索引的结果合并，以提高查询性能。

在lineitem表上有在l_shipdate以及l_partkey的单列索引，对于以下的SQL

select * from lineitem where l_shipdate = date '2010-12-01' or l_partkey=100;

PostgreSQL的执行计划

Bitmap Heap Scan on lineitem  (cost=9.05..202.96 rows=59 width=158)
  Recheck Cond: ((l_shipdate = '2010-12-01'::date) OR (l_partkey = 100))
  ->  BitmapOr  (cost=9.05..9.05 rows=59 width=0)
        ->  Bitmap Index Scan on l_shipdate_idx  (cost=0.00..4.70 rows=54 width=0)
              Index Cond: (l_shipdate = '2010-12-01'::date)
        ->  Bitmap Index Scan on l_partkey_idx  (cost=0.00..4.33 rows=5 width=0)
              Index Cond: (l_partkey = 100)

MySQL的执行计划

-> Filter: ((lineitem.L_SHIPDATE = DATE'2010-12-01') or (lineitem.L_PARTKEY = 100))  (cost=12.53 rows=21)
    -> Deduplicate rows sorted by row ID  (cost=12.53 rows=21)
        -> Index range scan on lineitem using l_shipdate_idx over (L_SHIPDATE = '2010-12-01')  (cost=1.11 rows=1)
        -> Index range scan on lineitem using l_partkey_idx over (L_PARTKEY = 100)  (cost=3.03 rows=20)

可以看出，MySQL及PostgreSQL都支持索引融合优化优化。

外键索引

应该在外键上键上创建索引，这个原则似乎和第一个原则（基于您的工作负载创建索引）相矛盾，然而事实上却是一致的，因为在真实应用中，表之间的关联绝大多数都是基于主外键来进行的。通过在外键上建立索引，可以提升表关联的效率。

在MySQL中，如果某个字段被定义为外键，默认会有对应的索引建立在上面；而在PostgreSQL系列的数据库中，将某些字段设置外键并不会自动在这些字段上建上索引。

创建索引时的约束条件

创建索引时，虽然它们可以提高读取性能，但是索引也不是免费的午餐，创建索引也具有一定的代价，譬如索引会对写入性能产生负面影响，因为对于数据库管理器写入表的每一行，它还必须更新任何受影响的索引。基于此，我们通常会限制每个表创建的最大索引数量。而且索引还会占用一定的磁盘空间，在磁盘空间比较紧张的系统上，索引的大小和数量也需要进行控制。这部分内容我们称之为约束条件。我们的目标就是在给定的约束条件下，创建合适的索引，以最大限度的提升系统的整体性能。

单表索引数目
索引字段数目
索引磁盘空间

针对以上的约束条件，我们通常通过以下的方法创建和维护索引：

索引列取舍：通过对列的单值选择率的评估，在过滤效果最好的列上建立索引; 通过对工作负载的分析，避免在频繁更新的列上建立索引。
索引取舍：通过对工作负载的分析，在最重要的SQL或是使用频率最高的查询上提供索引。
索引合并：索引满足组最左前缀匹配原则，所以可以通过设计索引列的排列顺序，达到一个索引加速多个SQL的查询。
索引删除：通过命令或工具定期采集索引的使用情况，将不再使用的索引进行删除。

总结

本质来讲，索引创建的过程可以抽象化为基于以上的约束条件，定义索引的收益，使用启发式算法，计算在满足特定约束条件下，整个工作负载收益最大的索引集合，这也是PawSQL索引推荐引擎的内部逻辑。

联系我们

PawSQL: https://www.pawsql.com

邮件：service@pawsql.com

Twitter: https://twitter.com/pawsql

扫描关注PawSQL公众号

PawSQL

基于您的工作负载创建索引​

基于单个SQL的结构创建索引​

为快速定位创建索引​

最左前缀原则​

等值条件​

范围条件​

为避免排序创建索引​

为避免回表创建索引​

其他相关​

分区表的索引​

本地分区索引​

全局分区索引​

全局非分区索引​

函数索引​

条件索引​

索引融合​

外键索引​

创建索引时的约束条件​

总结​

联系我们​