1. 树叶云首页
  2. 技术资讯

如何创建高效的索引，你知道吗？

运维••技术资讯

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

准则1：基于您的工作负载创建索引

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

在针对一个工作负载构建一组索引时，需要考虑工作负载的以下属性：

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

准则2：基于单个SQL的结构创建索引

索引的作用如下：

  • 快速定位数据
  • 避免排序
  • 避免回表
  • 实现行级锁(MySQL，另文讨论)
  • 实现唯一性约束

本章节我们将通过分析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较大的放前面。

等值条件（Index lookup）

  • 单表等值条件
  • 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)

范围条件(Index range scan)

  • 范围操作符（>,>=,<,<=,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 orderby l_shipdate limit10;
  • 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)
selectDISTINCT 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 orderby 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)

需要注意：

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

譬如对于下面的SQL。

select l_shipdate, l_orderkey,  sum(l_quantity)as sum_qty from lineitem groupby l_shipdate,l_orderkey orderby 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)

为避免回表创建索引（Covering index scan）

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

对于如下的SQL语句。

select l_shipdate, l_orderkey,  sum(l_quantity)as sum_qty from lineitem groupby 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关键字重建索引，否则索引不可用。

altertable 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 <100groupby 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 <100groupby 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系列的数据库中，将某些字段设置外键并不会自动在这些字段上建上索引。

准则3：创建索引时的约束条件

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

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

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

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

总结

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

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/239283.html<

赞 (0)
运维的头像运维
00
上一篇2025-04-23 08:20
下一篇 2025-04-23 08:22

相关推荐

  • 如何制作个人主题技术资讯

    个人主题怎么制作？

    制作个人主题是一个将个人风格、兴趣或专业领域转化为视觉化或结构化内容的过程，无论是用于个人博客、作品集、社交媒体账号还是品牌形象，核心都是围绕“个人特色”展开，以下从定位、内容规划、视觉设计、技术实现四个维度,详细拆解制作个人主题的完整流程，明确主题定位：找到个人特色的核心主题定位是所有工作的起点，需要先回答……

    运维的头像运维
    2025-11-20
    0
  • 如何管理社群营销技术资讯

    社群营销管理关键是什么？

    社群营销的核心在于通过建立有温度、有价值、有归属感的社群，实现用户留存、转化和品牌传播，其管理需贯穿“目标定位-内容运营-用户互动-数据驱动-风险控制”全流程,以下从五个维度展开详细说明：明确社群定位与目标社群管理的首要任务是精准定位，需明确社群的核心价值（如行业交流、产品使用指导、兴趣分享等）、目标用户画像……

    运维的头像运维
    2025-11-20
    0
  • 香港公司如何网站备案技术资讯

    香港公司网站备案需要什么材料？

    香港公司进行网站备案是一个涉及多部门协调、流程相对严谨的过程，尤其需兼顾中国内地与香港两地的监管要求，由于香港公司注册地与中国内地不同，其网站若主要服务内地用户或使用内地服务器，需根据服务器位置、网站内容性质等，选择对应的备案路径（如工信部ICP备案或公安备案），以下从备案主体资格、流程步骤、材料准备、注意事项……

    运维的头像运维
    2025-11-20
    0
  • 如何企业上云推广技术资讯

    如何企业上云推广

    企业上云已成为数字化转型的核心战略,但推广过程中需结合行业特性、企业痛点与市场需求，构建系统性、多维度的推广体系，以下从市场定位、策略设计、执行落地及效果优化四个维度，详细拆解企业上云推广的实践路径，精准定位：明确目标企业与核心价值企业上云并非“一刀切”的方案，需先锁定目标客户群体，提炼差异化价值主张，客户分层……

    运维的头像运维
    2025-11-20
    0
  • PS如何设计搜索框技术资讯

    PS设计搜索框的实用技巧有哪些？

    在PS中设计一个美观且功能性的搜索框需要结合创意构思、视觉设计和用户体验考量，以下从设计思路、制作步骤、细节优化及交互预览等方面详细说明，帮助打造符合需求的搜索框，设计前的规划明确使用场景：根据网站或APP的整体风格确定搜索框的调性，例如极简风适合细线条和纯色，科技感适合渐变和发光效果，电商类则可能需要突出搜索……

    运维的头像运维
    2025-11-20
    0

发表回复

您的邮箱地址不会被公开。必填项已用 * 标注