索引是优化数据库性能的最重要工具之一。但是，创建过多的索引或索引错误的列也可能对性能产生负面影响。因此，在设计索引时遵循某些原则很重要。
原理1.根据您的工作量创建索引
创建有效索引的最重要原则是根据您的工作负载而不是表结构创建索引。索引的目的是提高数据库中操作的效率，而针对数据库进行的SQL语句构成了数据库的工作量。因此，任何其他不从工作负载开始的索引创建方法都是不正确的。
在构建一组工作负载索引时，我们需要考虑工作量的以下特征：
- SQL类型：在OLTP方案中，用户经常插入新数据并修改现有数据，多个索引可能会对性能产生负面影响。建议创建最小数量的索引来满足您的索引要求。在查询是多层用法的OLAP方案中，您可以添加更多索引，每个索引可以具有多个列，甚至可以创建函数索引和条件索引。
- SQL频率：应为最常用的查询创建索引。通过为这些查询创建最佳索引，可以最大化系统的整体性能。
- SQL查询的重要性：查询越重要，您可能想通过创建索引来优化其性能。
- SQL查询本身的结构。
原理2.根据SQL的结构创建索引
索引的功能如下：
- 快速找到数据
- 避免排序
- 避免查找
快速位置
索引可以通过匹配查询条件来快速定位数据，该查询条件可能在Where子句中，符号或子句上。索引和条件之间的匹配原理遵循最左的前缀匹配原理。
最左前缀匹配原理
最左端的前缀匹配原理是指当相等的查询条件准确匹配的最左侧列或索引的几列时，该列可用于匹配索引。当遇到范围查询时（>，<，在类似）中，匹配停止，但是范围条件匹配。
对于复合索引lineitem (l_shipdate, l_quantity)，下方的前两个SQL查询满足最左侧的前缀匹配原理，并且可以使用索引。最后一个不符合最左侧的前缀匹配原理，也不能使用索引。
select * from lineitem where l_shipdate = date '2021-12-01'; -- index can be used
select * from lineitem where l_shipdate = date '2021-12-01' and l_quantity = 100; -- index can be used
select * from lineitem where l_quantity = 100; -- The index cannot be used
这三个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_QUANTITY=100.00, 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)
除了最左侧的前缀原理外，在创建复合索引时，在确定其索引字段的顺序时，还应考虑不同值（基数）的数量。具有较高基数的字段应首先放置。
相等的条件
-
单桌相等条件
-
<> 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)
范围条件
- 范围运算符（>，> =，<，<，<=，之间）
- in'，'，'*
- 不是null
- 是null
- 喜欢'abc％'
- col ='a'或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结构主要包括：
- 的组
- 订购
- 独特的
- ...订购的分区...
考虑到我们有一个索引lshipdate_idx，如下所示
create index lshipdate_idx on lineitem(l_shipdate);
您可以看到以下SQLS利用lshipdate_idx索引的执行计划以避免分类。
- sql1（订购）
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（组成）
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（独特）
select DISTINCT l_shipdate from lineitem;
- SQL3的执行计划
-> Covering index skip scan for deduplication on lineitem using lshipdate_idx (cost=4954.90 rows=15973)
- sql4（...订购的分区...）
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 。
-> 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;
-
index on（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)
-
index on（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）
本地分区索引
在索引维护方面，本地索引比全局索引更容易管理。当您添加，删除或截断表分区时，本地索引将自动维护其索引分区。 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
如果我们在EXTRACT(DAY from l_shipdate)上创建了一个函数索引，如下所示，您将看到优化器使用索引并选择一个更好的执行计划。
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
从执行计划中，我们可以在使用该功能索引后看到，性能提高了900％。
部分索引
部分索引（条件索引）是构建在表格子集上的索引，该子集由条件表达式定义，该索引仅包含满足条件表达式的表中的那些行。
部分索引在相对严格的条件下使用。只有当数据库可以识别查询的某些条件在逻辑上涵盖索引条件表达式的定义时，该部分索引才能用于查询。
以以下条件索引为例，索引的条件表达为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都支持它们。
索引合并
索引合并是一种优化技术，它使用多个索引执行单桌数据访问。当表上的多个条件参与查询并且每个条件都有合适的索引时，索引合并可以在检索数据之前合并多个索引的结果，这可以提高查询性能。
对于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，Oracle和SQL Server等各种数据库支持。但是，并非所有数据库都支持所有类型的索引合并，并且优化行为可能会在同一数据库的不同版本中有所不同。
外键索引
建议在外键上创建索引。该原则似乎与第一个原则相矛盾（基于您的工作量创建索引），但实际上，这是一致的，因为在现实世界应用中，大多数表关系关系基于主要和外国密钥。在外键上创建索引可以提高表关联的效率。
在mySQL中，如果将字段定义为外键，则默认情况下在其上创建索引。但是，在PostgreSQL及其相关数据库中，将某些字段设置为外国密钥不会自动在这些字段上创建索引。
原理3.索引设计的限制
设计索引时，重要的是要考虑以下约束：
- 每个表的最大索引数：创建太多索引可能会对写作表现产生负面影响，因为表格或更新表的每个插入都必须更新索引。因此，重要的是要限制每个表的索引数。
- 每个索引的最大列数：用太多列创建索引也会对性能产生负面影响，因为该索引可能变得太大而无法高效。因此，限制每个索引的列数很重要。
- 磁盘空间使用率：索引可以占用大量磁盘空间。因此，重要的是要考虑设计索引时可用的磁盘空间的量。
要设计和维护满足这些约束的索引，可以使用以下方法：
- 索引选择：可以在最重要的SQL语句或最常用的查询上提供索引。
- 用于索引的列选择：索引应在具有最佳过滤效果的列上创建基于列的单个值选择性的评估。您应该避免在经常更新的列上创建索引。
- 索引合并：通过以复合索引以正确顺序设计列，可以使用一个索引来加速多个查询。
- 索引删除：可以定期删除未使用的索引以释放磁盘空间并减少维护开销。
概括
总而言之，创建索引的过程可以抽象为在上述约束下定义索引的好处，使用启发式算法来计算在特定约束下最大化整体工作量益处的索引集。这也是PawSQL索引引擎的内部逻辑。