数据库索引设计原则

在这里插入图片描述

1. 概述

索引是优化数据库性能最重要的工具之一。但是，创建过多的索引或索引错误的列也会对性能产生负面影响。因此，在设计索引时遵循一定的原则很重要。

2. 原则A - 根据工作负载创建索引

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

在为某个工作负载构建一组索引时，我们需要考虑该工作负载的以下特征：

SQL 类型：在 OLTP 场景中，用户频繁插入新数据和修改现有数据，多个索引可能会对性能产生负面影响。建议创建满足索引需求的最小索引数量。在查询占主导地位的 OLAP 场景中，您可以添加更多索引，每个索引可以有多个列，甚至可以创建函数索引和条件索引。
SQL 频率：应该为最常用的查询创建索引。通过为这些查询创建最佳索引，可以最大限度地提高系统的整体性能。
SQL 查询的重要性：查询越重要，您就越希望通过创建索引来优化其性能。
SQL 查询本身的结构。

在这里插入图片描述

3. 原则B - 根据SQL结构创建索引

索引的作用如下：

快速定位数据
避免排序
避免表查找

3.1 快速定位

索引可以通过匹配查询条件来快速定位数据，查询条件可以是WHERE子句，HAVING子句，也可以是ON子句，索引与条件的匹配原则遵循最左前缀匹配原则。

3.1.1 最左前缀匹配原则

最左前缀匹配原则是指当相等的查询条件准确匹配索引最左边的连续列或某几列时，该列可以用来匹配索引。当遇到范围查询（>、<、between、like）时，匹配停止，但范围条件仍然匹配。

对于组合索引来说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)

除了最左前缀原则外，创建复合索引时还应考虑不同值的数量（基数）来决定索引字段的顺序。基数较高的字段应放在第一位。

3.1.2 平等条件

单表平等条件

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)

由于最左匹配原则，遵循范围条件的索引列无法利用索引。

3.2 避免排序

对于 B+ 树索引，由于是按照索引键排序的，因此在 SQL 查询中可以使用 B+ 树来避免排序。涉及的 SQL 结构主要包括：

GROUP BY
ORDER BY
DISTINCT
PARTITION BY… ORDER BY…

lshipdate_idx假设我们有如下索引

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。

-> 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)

3.3 避免表查找

当查询的所有列都在索引列中时，数据库只需要访问索引就可以获得所需的数据，避免了查表。在某些场景下，这可以大大提高查询效率。

对于以下 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)