【PGCCC】 复合索引和部分索引，竟然能让查询速度提升 275 倍！

索引对于加速数据库查询和提高 PostgreSQL 应用程序的性能至关重要。但是，并非所有索引都以相同的方式发挥作用。复合索引和部分索引是两种常见类型，每种类型都有不同的用途和对性能的影响。本文我们将深入探讨复合索引和部分索引是什么、它们如何运作以及何时使用它们来为数据库实现最佳结果。

复合索引

复合索引是在多个列上创建的，使 PostgreSQL 能够高效处理搜索条件中包含多个列的查询。当查询频繁使用多个字段过滤或排序数据时，这种类型的索引尤其有用。例如，在“last_name”和“first_name”上同时创建索引可以加速查询中同时指定两个名称的搜索。

使用复合索引的优点

• 复合索引可以充当覆盖索引，这意味着它们包含查询所需的所有列。这允许数据库直接从索引中检索数据，而无需查看主表，从而减少所需的 I/O
  操作数。
• 如果我们经常运行按“last_name”和“first_name”排序的查询，则这些列上的复合索引将通过提供预先排序的顺序来加快它们的速度，从而使排序更快、更高效。
• 我们还可以对相同的列使用具有 UNIQUE 约束的复合索引，以确保更快的性能和数据完整性。

使用复合索引的缺点

• 如果复合索引包含查询中很少一起使用的列，则它不会提高性能，甚至可能由于更新索引所需的额外工作而降低性能。
• 如果复合索引中的任何列频繁更新，则必须经常修改索引，这可能会对性能产生负面影响。
• 通过 INSERT 或 UPDATE 查询频繁更新索引会增加存储使用量并需要更多维护。
• 复合索引中列的顺序至关重要；只有当查询从第一列开始或按顺序匹配前几列时，PostgreSQL 才能有效地使用索引。
• 当您的表具有许多唯一值时，请使用复合索引；如果只有少数唯一值，则复合索引可能无效。

复合索引示例

让我们考虑一个用于存储销售数据的简单表，该表具有主键，但没有其他索引。该表可能如下所示

postgres=# \d sales
                                    表“public.sales”
   列|类型|排序规则|可空|默认                 
-------------+-----------+-----------+-------------+---------------------------------------- 
sale_id |整数||非空|nextval('sales_sale_id_seq'::regclass) 
customer_id |整数||非空|  
product_id |整数||非空|  
sale_date |日期||非空| 
金额|数字(10,2)||非空| 
索引：
    “sales_pkey”主键，btree(sale_id)

让我们执行一个简单的SELECT查询来获取product_id = 408的所有销售信息，其中sale_date是2024-08-17。

postgres = # EXPLAIN ANALYZE SELECT * FROM sales WHERE product_id = 408 AND sale_date = '2024-08-17';
                                                    查询计划                                                     
------------------------------------------------------------------------------------------------------------------
收集（成本=1000.00..7310.30 行=3 宽度=22）（实际时间=4.374..19.059 行=4 循环=1）
   计划的工人：2
   启动的工人：2 
   -> 销售并行序列扫描（成本=0.00..6310.00 行=1 宽度=22）（实际时间=9.093..14.098 行=1 循环=3）
         过滤器：（（product_id = 408）AND（sale_date = '2024-08-17'::date））
         过滤器删除的行：166665
计划时间：0.251 毫秒
执行时间：19.078 毫秒
（8 行）

PostgreSQL 规划器选择使用并行顺序扫描来获取所需结果，这是有道理的，因为目前还没有索引。这需要19.078 毫秒才能完成。

现在，让我们在同一个表中的product_id和sale_date列上创建一个复合索引。

创建索引 idx_sales_product_id_sale_date ON sales(product_id, sale_date);
创建索引

现在，让我们再次运行相同的SELECT查询。

解释分析选择*从销售中获取产品id = 408 和销售日期 = '2024-08-17'；
                                                              查询计划                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------
销售位图堆扫描（成本=4.45..16.22 行=3 宽度=22）（实际时间=0.048..0.055 行=4 循环=1）
   重新检查条件：（（product_id = 408）AND（sale_date = '2024-08-17'::date））
   堆块：精确=4 
   -> idx_sales_product_id_sale_date 位图索引扫描（成本=0.00..4.45 行=3 宽度=0）（实际时间=0.045..0.045 行=4 循环=1）
         索引条件：（（product_id = 408）AND（sale_date = '2024-08-17'::date））
计划时间：0.265 毫秒
执行时间： 0.074 毫秒
（7 行）

哇！执行时间从 19.078 毫秒缩短至 0.074 毫秒，性能提高了近 275 倍。

部分索引

部分索引仅索引满足特定条件的数据子集，而不是覆盖表中的所有行。它们非常适合只频繁查询部分数据的情况，例如仅索引活跃用户或近期交易。通过定位特定的数据子集，部分索引可以减少存储需求并提高查询性能。

使用部分索引的优点

• 部分索引仅覆盖表的一部分，与完整索引相比，存储成本较低。
• 它们的尺寸较小，在插入、更新和删除过程中需要的维护时间和资源较少。
• UPDATE 操作通常更快，因为部分索引不需要在每次更改时更新。

使用部分索引的缺点

• 部分索引不涵盖所有数据，因此随着表大小的增加，非索引数据上的连接或过滤可能会导致性能下降。
• 仅当索引子集被频繁查询时，部分索引才有用；否则，如果查询不符合索引条件，它们可能不会提高性能。

部分索引示例

让我们创建一个表来存储 COVID-19 数据。并在表中插入 300 万条虚拟记录。

如果不存在，则创建表 covid_data（id SERIAL PRIMARY KEY、国家 varchar(20)、标题 varchar(10)、名称 varchar(20)、接种疫苗 varchar(3)）；
CREATE TABLE 
ostgres=# INSERT INTO covid_data (country, title, names, vaccinated) 
postgres-# SELECT 
-- 从预定义列表中随机选择国家
(ARRAY['USA', 'Canada', 'UK', 'Germany', 'France', 'India', 'China', 'Brazil', 'Australia', 'Japan'])[floor(random() * 10 + 1)], 
-- 从预定义列表中随机选择头衔
(ARRAY['Mr.', 'Ms.', 'Dr.', 'Prof.'])[floor(random() * 4 + 1)], 
-- 从预定义列表中随机选择姓名
(ARRAY['John', 'Jane', 'Alex', 'Emily', 'Michael', 'Sarah', 'David', 'Laura', 'Robert', 'Linda'])[floor(random() * 10 + 1)], 
-- 随机疫苗接种状态（“是”或“否”）
CASE 
  WHEN random() < 0.8 THEN '是' -- 80% 的可能性为“是” 
  ELSE '否' -- 20% 的可能性为“否” 
END 
FROM generate_series(1, 3000000);

首先，让我们使用EXPLAIN ANALYZE运行SELECT查询来分析执行计划并获取结果。

postgres=# EXPLAIN ANALYZE SELECT * FROM covid_data WHERE vaccinated = '是' AND country = '英国' AND title = '教授';
                                                          查询计划                                                           
--------------------------------------------------------------------------------------------------------------------------
收集（成本=1000.00..27738.89 行=1 宽度=174）（实际时间=0.361..450.847 行=59916 循环=1）
   计划的工人：2
   启动的工人：2 
   -> 对 covid_data 进行并行序列扫描（成本=0.00..26738.79 行=1 宽度=174）（实际时间=0.075..432.697 行=19972 循环=3）
         过滤器：（（（vaccinated)::text = 'Yes'::text）AND（（country)::text = 'UK'::text）AND（（title)::text = 'Prof.'::text））
         过滤器删除的行数：980028
计划时间：0.096 毫秒
执行时间：456.062 毫秒
（8 行）

由于没有索引，因此使用了顺序扫描，并在456 毫秒内返回结果。

现在，让我们创建三个索引，每个索引用于 SELECT 查询中的列。

postgres=# 在 covid_data(vaccinated) 上创建索引 vaccinated_full_idx；
创建索引
postgres=# 在 covid_data(country) 上创建索引 country_full_idx；
创建索引
postgres=# 在 covid_data(title) 上创建索引 title_full_idx；
创建索引

现在，让我们再次运行相同的SELECT查询并检查执行时间是否减少。

postgres=# EXPLAIN ANALYZE SELECT * FROM covid_data WHERE vaccinated = '是' AND country = '英国' AND title = '教授';
                                                                   查询计划                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
对 covid_data 进行位图堆扫描（成本=495.29..499.31 行=1 宽度=174）（实际时间=100.977..229.152 行=59435 循环=1）
   重新检查条件：（（（title)::text = 'Prof.'::text）AND（（country)::text = 'UK'::text）AND（（vaccinated)::text = 'Yes'::text））
   堆块：精确=19605 
   -> BitmapAnd（成本=495.29..495.29 行=1 宽度=0）（实际时间=97.916..97.917 行=0 循环=1）
         -> 对 title_full_idx 进行位图索引扫描（成本=0.00..164.93行=15000 宽度=0）（实际时间=23.191..23.192 行=749479 循环=1）
               索引条件：（（title)::text = 'Prof.'::text）
         -> country_full_idx 上的位图索引扫描（成本=0.00..164.93 行=15000 宽度=0）（实际时间=11.334..11.334 行=299158 循环=1）
               索引条件：（（country)::text = 'UK'::text）
         -> vaccinated_full_idx 上的位图索引扫描（成本=0.00..164.93 行=15000 宽度=0）（实际时间=62.001..62.001 行=2400565 循环=1）
               索引条件：（（vaccinated)::text = '是'::文本）
计划时间：0.477 毫秒
执行时间：232.855 毫秒
（12 行）
postgres=# SELECT pg_size_pretty(pg_relation_size('title_full_idx')); 
pg_size_pretty  
---------------- 
20 MB （ 1 行） postgres=# SELECT pg_size_pretty(pg_relation_size ('country_full_idx')); pg_size_pretty ---------------- 20 MB（1 行） postgres=# SELECT pg_size_pretty( pg_relation_size ( ' 
vaccinated_full_idx 
')); pg_size_pretty  ---------------- 20 MB （1 行）

这次执行时间减少到232 毫秒，性能提升了1.9 倍。PostgreSQL 使用近60 MB来存储这些索引。

现在，让我们使用部分索引来看看是否可以同时优化速度和空间并取得更好的结果。

创建部分索引

postgres = # CREATE INDEX vaccinated_pa​​rtial_idx ON covid_data(vaccinated) WHERE vaccinated = '是' AND country = '英国' AND title = '教授'；
创建索引

现在执行相同的 SELECT 查询

postgres=# EXPLAIN ANALYZE SELECT * FROM covid_data WHERE vaccinated = 'Yes' AND country = 'UK' AND title = 'Prof.';
                                                                查询计划                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------
使用 vaccinated_pa​​rtial_idx 对 covid_data 进行索引扫描（成本=0.29..8.30 行=1 宽度=174）（实际时间=0.023..79.981 行=60644 循环=1）
计划时间：0.237 毫秒
执行时间：83.855 毫秒
（3 行）
postgres=# SELECT pg_size_pretty(pg_relation_size('vaccinated_pa​​rtial_idx')); 
pg_size_pretty  
---------------- 
424 kB 
（1 行）

哇！通过部分索引，我们实现了5.4 倍的性能提升，并且大小减少了99.29％。
#PG证书#PG考试#postgresql初级#postgresql中级#postgresql高级