devbox
weixin_40725706
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

POSTGRESQL (PG) 性能优化之索引使用技巧（复合多列索引、局部索引、表达式索引、覆盖索引、唯一索引）_postgresql 多列索引

作者：weixin_40725706 | 2024-05-27 06:16:59

赞

踩

postgresql 多列索引

一 复合索引（多列索引）

复合索引也称多列索引，是在某个关系表上的多列建立索引，为了提高索引查询效率，将经常使用的列放在复合索引的前面。当查询语句中where字句引用了复合索引中的所有列或者先导列，复合索引会带来查询性能提升。

目前PG只有B树索引、GiST、GIN和BRIN索引类型支持复合索引，最多可以支持32个列。

复合索引使用实例：

等值情况下，where子句中有先导列a，不管后面跟着b或者c或者d，或者三者的组合，都会使用复合索引，但是如果where子句中没有先导列a，则b或者c或者d或者三者组合都不能使用复合索引。

  1. --等值情况下索引使用情况
  2. test=# create table t1(a int, b int, c int, d int);
  3. CREATE TABLE
  4. test=# insert into t1 values(generate_series(1,100000), generate_series(1,1000), generate_series(1,10000), generate_series(1,1000000));
  5. INSERT 0 1000000
  6. test=# \d t1
  7.                  Table "public.t1"
  8.  Column |  Type   | Collation | Nullable | Default
  9. --------+---------+-----------+----------+---------
  10.  a      | integer |           |          |
  11.  b      | integer |           |          |
  12.  c      | integer |           |          |
  13.  d      | integer |           |          |
  14.  
  15. test=# explain select * from t1 where a=10 and b=30 and c=1555 and d=888999;
  16.                                QUERY PLAN
  17. -------------------------------------------------------------------------
  18.  Gather  (cost=1000.00..13768.43 rows=1 width=16)
  19.    Workers Planned: 2
  20.    ->  Parallel Seq Scan on t1  (cost=0.00..12768.33 rows=1 width=16)
  21.          Filter: ((a = 10) AND (b = 30) AND (c = 1555) AND (d = 888999))
  22. (4 rows)
  23.  
  24. test=# create index on t1(a,b,c,d);
  25. CREATE INDEX
  26. test=# analyze t1;
  27. ANALYZE
  28. test=# \d t1
  29.                  Table "public.t1"
  30.  Column |  Type   | Collation | Nullable | Default
  31. --------+---------+-----------+----------+---------
  32.  a      | integer |           |          |
  33.  b      | integer |           |          |
  34.  c      | integer |           |          |
  35.  d      | integer |           |          |
  36. Indexes:
  37.     "t1_a_b_c_d_idx" btree (a, b, c, d)
  38.  
  39. test=# explain select * from t1 where a=10 and b=30 and c=1555;
  40.                                   QUERY PLAN
  41. -------------------------------------------------------------------------------
  42.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  43.    Index Cond: ((a = 10) AND (b = 30) AND (c = 1555))
  44. (2 rows)
  45.  
  46. test=# explain select * from t1 where a=10 and b=30;
  47.                                   QUERY PLAN
  48. -------------------------------------------------------------------------------
  49.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  50.    Index Cond: ((a = 10) AND (b = 30))
  51. (2 rows)
  52.  
  53. test=# explain select * from t1 where a=10 and c=1555;
  54.                                   QUERY PLAN
  55. -------------------------------------------------------------------------------
  56.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  57.    Index Cond: ((a = 10) AND (c = 1555))
  58. (2 rows)
  59.  
  60. test=# explain select * from t1 where a=10 and d=888999;
  61.                                   QUERY PLAN
  62. -------------------------------------------------------------------------------
  63.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  64.    Index Cond: ((a = 10) AND (d = 888999))
  65. (2 rows)
  66.  
  67. test=# explain select * from t1 where b=30 and c=1555 and d=888999;
  68.                               QUERY PLAN
  69. ----------------------------------------------------------------------
  70.  Gather  (cost=1000.00..12726.77 rows=1 width=16)
  71.    Workers Planned: 2
  72.    ->  Parallel Seq Scan on t1  (cost=0.00..11726.67 rows=1 width=16)
  73.          Filter: ((b = 30) AND (c = 1555) AND (d = 888999))
  74. (4 rows)
  75.  
  76. test=# explain select * from t1 where b=30;
  77.                              QUERY PLAN
  78. ---------------------------------------------------------------------
  79.  Gather  (cost=1000.00..10643.43 rows=1 width=16)
  80.    Workers Planned: 2
  81.    ->  Parallel Seq Scan on t1  (cost=0.00..9643.33 rows=1 width=16)
  82.          Filter: (b = 30)
  83. (4 rows)
  84.  
  85. test=# explain select * from t1 where b=30 and d=888999;
  86.                               QUERY PLAN
  87. ----------------------------------------------------------------------
  88.  Gather  (cost=1000.00..11685.10 rows=1 width=16)
  89.    Workers Planned: 2
  90.    ->  Parallel Seq Scan on t1  (cost=0.00..10685.00 rows=1 width=16)
  91.          Filter: ((b = 30) AND (d = 888999))
  92. (4 rows)

不等值情况下和等值规则一致，即where条件中必须有先导列a，否则不能使用复合索引。

  1. --不等值情况下使用和等值一致
  2. test=# explain select * from t1 where a=10 and b<30 and c>9988 and d=888999;
  3.                                   QUERY PLAN
  4. -------------------------------------------------------------------------------
  5.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  6.    Index Cond: ((a = 10) AND (b < 30) AND (c > 9988) AND (d = 888999))
  7. (2 rows)
  8.  
  9. test=# explain select * from t1 where a=10 and b<30 and c>9988;
  10.                                   QUERY PLAN
  11. -------------------------------------------------------------------------------
  12.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  13.    Index Cond: ((a = 10) AND (b < 30) AND (c > 9988))
  14. (2 rows)
  15.  
  16. test=# explain select * from t1 where a=10 and c>9988;
  17.                                   QUERY PLAN
  18. -------------------------------------------------------------------------------
  19.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..8.45 rows=1 width=16)
  20.    Index Cond: ((a = 10) AND (c > 9988))
  21. (2 rows)
  22.  
  23. test=# explain select * from t1 where a<876 and b<30 and c>9988 and d=888999;
  24.                                    QUERY PLAN
  25. --------------------------------------------------------------------------------
  26.  Index Only Scan using t1_a_b_c_d_idx on t1  (cost=0.42..40.23 rows=1 width=16)
  27.    Index Cond: ((a < 876) AND (b < 30) AND (c > 9988) AND (d = 888999))
  28. (2 rows)
  29.  
  30. test=# explain select * from t1 where b<30 and c>9988 and d=888999;
  31.                               QUERY PLAN
  32. ----------------------------------------------------------------------
  33.  Gather  (cost=1000.00..12726.77 rows=1 width=16)
  34.    Workers Planned: 2
  35.    ->  Parallel Seq Scan on t1  (cost=0.00..11726.67 rows=1 width=16)
  36.          Filter: ((b < 30) AND (c > 9988) AND (d = 888999))
  37. (4 rows)

二 部分索引（局部索引）

部分索引也称局部索引，是建立在关系表上得子集，而该子集是由一个条件表达式定义的（叫做部分索引的谓词）。该索引只包含表中那些满足这个谓词的行。

部分索引得有点在于提高数据插入和更新的效率，减少维护成本，因为部分索引不是在所有情况下都需更新索引，只有符合部分索引条件表达式的数据才会更新索引。另外部分索引比普通索引要小，减少索引存储空间。

部分索引主要在排除公共值、排除不感兴趣的值方面比较有用，比如IP网段排除等。

部分索引实例：

  1. test=# create index on t1(b) where b < 500;
  2. CREATE INDEX
  3. test=# analyze t1;
  4. ANALYZE
  5. test=# \d t1
  6.                  Table "public.t1"
  7.  Column |  Type   | Collation | Nullable | Default
  8. --------+---------+-----------+----------+---------
  9.  a      | integer |           |          |
  10.  b      | integer |           |          |
  11.  c      | integer |           |          |
  12.  d      | integer |           |          |
  13. Indexes:
  14.     "t1_a_b_c_d_idx" btree (a, b, c, d)
  15.     "t1_b_idx" btree (b) WHERE b < 500
  16.  
  17. test=# explain select * from t1 where b <150;
  18.                              QUERY PLAN
  19. ---------------------------------------------------------------------
  20.  Index Scan using t1_b_idx on t1  (cost=0.28..9.52 rows=71 width=16)
  21.    Index Cond: (b < 150)
  22. (2 rows)
  23.  
  24. test=# explain select * from t1 where b <650;
  25.                               QUERY PLAN
  26. -----------------------------------------------------------------------
  27.  Gather  (cost=1000.00..10723.03 rows=797 width=16)
  28.    Workers Planned: 2
  29.    ->  Parallel Seq Scan on t1  (cost=0.00..9643.33 rows=332 width=16)
  30.          Filter: (b < 650)
  31. (4 rows)
  32.  
  33. test=# explain select * from t1 where b > 300;
  34.                               QUERY PLAN
  35. -----------------------------------------------------------------------
  36.  Gather  (cost=1000.00..10721.03 rows=777 width=16)
  37.    Workers Planned: 2
  38.    ->  Parallel Seq Scan on t1  (cost=0.00..9643.33 rows=324 width=16)
  39.          Filter: (b > 300)
  40. (4 rows)
  41.  
  42. test=# explain select * from t1 where b > 499;
  43.                               QUERY PLAN
  44. -----------------------------------------------------------------------
  45.  Gather  (cost=1000.00..10689.63 rows=463 width=16)
  46.    Workers Planned: 2
  47.    ->  Parallel Seq Scan on t1  (cost=0.00..9643.33 rows=193 width=16)
  48.          Filter: (b > 499)
  49. (4 rows)

三 表达式索引

表达式索引是基于关系表的一列或者多列计算而来的一个函数或者标量表达式来建立的索引，可以根据计算结果快速获取表中的内容。但是表达式索引存储的是表达式的值，并不是在使用索引时候创建的，而是在创建索引的时候计算好的，因此插入数据或者数据更新时，需要进行表达式计算，索引创建会比较慢，因此表达式索引的维护代价比较昂贵，需要谨慎使用。

最常见的是创建基于函数的索引，比如经常使用lower(name)或者upper(name)函数做大小写无关的比较，但是因为用了lower或者upper函数，无法有效利用name列上得索引，此时就需要表达式索引：

  1. test=# create table t2(id int, name text);
  2. CREATE TABLE
  3. test=# insert into t2 values(generate_series(1,100000), md5(random()::text));
  4. INSERT 0 100000
  5. test=# create index t2_name_idx1 on t2(name);
  6. CREATE INDEX
  7. test=# analyze t2;
  8. ANALYZE
  9. test=# \d t2
  10.                  Table "public.t2"
  11.  Column |  Type   | Collation | Nullable | Default
  12. --------+---------+-----------+----------+---------
  13.  id     | integer |           |          |
  14.  name   | text    |           |          |
  15. Indexes:
  16.     "t2_name_idx1" btree (name)
  17.  
  18. test=# explain select * from t2 where name='ada';
  19.                                QUERY PLAN
  20. ------------------------------------------------------------------------
  21.  Index Scan using t2_name_idx1 on t2  (cost=0.42..8.44 rows=1 width=37)
  22.    Index Cond: (name = 'ada'::text)
  23. (2 rows)
  24.  
  25. test=# explain select * from t2 where lower(name)='ada';
  26.                        QUERY PLAN
  27. --------------------------------------------------------
  28.  Seq Scan on t2  (cost=0.00..2334.00 rows=500 width=37)
  29.    Filter: (lower(name) = 'ada'::text)
  30. (2 rows)
  31.  
  32. test=# create index t2_name_idx2 on t2(lower(name));
  33. CREATE INDEX
  34. test=# analyze t2;
  35. ANALYZE
  36. test=# \d t2
  37.                  Table "public.t2"
  38.  Column |  Type   | Collation | Nullable | Default
  39. --------+---------+-----------+----------+---------
  40.  id     | integer |           |          |
  41.  name   | text    |           |          |
  42. Indexes:
  43.     "t2_name_idx1" btree (name)
  44.     "t2_name_idx2" btree (lower(name))
  45.  
  46. test=# explain select * from t2 where lower(name)='ada';
  47.                                QUERY PLAN
  48. ------------------------------------------------------------------------
  49.  Index Scan using t2_name_idx2 on t2  (cost=0.42..8.44 rows=1 width=37)
  50.    Index Cond: (lower(name) = 'ada'::text)
  51. (2 rows)

另外，我们也可能经常进行如下的查询来查询人名相关的数据，这时候需要创建基于复杂表达式的函数索引：

  1. test=# create table t3(id int, first_name text, last_name text);
  2. CREATE TABLE
  3. test=# insert into t3 values(generate_series(1,100000), md5(random()::text), md5(random()::text));
  4. INSERT 0 100000
  5. test=# \d t3
  6.                    Table "public.t3"
  7.    Column   |  Type   | Collation | Nullable | Default
  8. ------------+---------+-----------+----------+---------
  9.  id         | integer |           |          |
  10.  first_name | text    |           |          |
  11.  last_name  | text    |           |          |
  12.  
  13. test=# explain select * from t3 where (first_name || ' ' || last_name) = 'Jim Covert';
  14.                                 QUERY PLAN
  15. ---------------------------------------------------------------------------
  16.  Seq Scan on t3  (cost=0.00..2985.00 rows=500 width=70)
  17.    Filter: (((first_name || ' '::text) || last_name) = 'Jim Covert'::text)
  18. (2 rows)
  19.  
  20. test=# create index on t3(first_name, last_name);
  21. CREATE INDEX
  22. test=# analyze t3;
  23. ANALYZE
  24. test=# \d t3
  25.                    Table "public.t3"
  26.    Column   |  Type   | Collation | Nullable | Default
  27. ------------+---------+-----------+----------+---------
  28.  id         | integer |           |          |
  29.  first_name | text    |           |          |
  30.  last_name  | text    |           |          |
  31. Indexes:
  32.     "t3_first_name_last_name_idx" btree (first_name, last_name)
  33.  
  34. test=# explain select * from t3 where (first_name || ' ' || last_name) = 'Jim Covert';
  35.                                 QUERY PLAN
  36. ---------------------------------------------------------------------------
  37.  Seq Scan on t3  (cost=0.00..2985.00 rows=500 width=70)
  38.    Filter: (((first_name || ' '::text) || last_name) = 'Jim Covert'::text)
  39. (2 rows)
  40.  
  41. test=# create index on t3((first_name || ' ' || last_name));
  42. CREATE INDEX
  43. test=# analyze t3;
  44. ANALYZE
  45. test=# \d t3
  46.                    Table "public.t3"
  47.    Column   |  Type   | Collation | Nullable | Default
  48. ------------+---------+-----------+----------+---------
  49.  id         | integer |           |          |
  50.  first_name | text    |           |          |
  51.  last_name  | text    |           |          |
  52. Indexes:
  53.     "t3_expr_idx" btree (((first_name || ' '::text) || last_name))
  54.     "t3_first_name_last_name_idx" btree (first_name, last_name)
  55.  
  56. test=# explain select * from t3 where (first_name || ' ' || last_name) = 'Jim Covert';
  57.                                   QUERY PLAN
  58. -------------------------------------------------------------------------------
  59.  Index Scan using t3_expr_idx on t3  (cost=0.42..8.44 rows=1 width=70)
  60.    Index Cond: (((first_name || ' '::text) || last_name) = 'Jim Covert'::text)
  61. (2 rows)

四 覆盖索引

谈覆盖索引之前，首先要了解PG中Index Only Scan，该扫描方式可以只通过索引就能得到元祖，而不需要回表产生额外的堆访问。基本思想是直接从每一个索引项中直接返回值，而不需要去访问堆数据。目前只有B树索引可以使用Index Only Scan。

能够使用Index Only Scan的索引需要包含查询中所有引用关系表的列。因此上面讲到的复合索引可以适用于Index Only Scan（复合索引实例中有Index Only Scan例子），但是复合索引要求的存储空间比较大，为了减少开销，PG引入了覆盖索引。覆盖索引包含where条件中的查询列，但是非where条件中的列通过INCLUDE完成，从而完成从索引中获取数据而不需要访问堆读取数据。

覆盖索引实例：

  1. test=# create index on t1(b,d);
  2. CREATE INDEX
  3. test=# analyze t1;
  4. ANALYZE
  5. test=# \d t1
  6.                  Table "public.t1"
  7.  Column |  Type   | Collation | Nullable | Default
  8. --------+---------+-----------+----------+---------
  9.  a      | integer |           |          |
  10.  b      | integer |           |          |
  11.  c      | integer |           |          |
  12.  d      | integer |           |          |
  13. Indexes:
  14.     "t1_a_b_c_d_idx" btree (a, b, c, d)
  15.     "t1_b_d_idx" btree (b, d)
  16.     "t1_b_idx" btree (b) WHERE b < 500
  17.  
  18. test=# explain select c from t1 where b > 800 and d > 9988;
  19.                                QUERY PLAN
  20. ------------------------------------------------------------------------
  21.  Index Scan using t1_b_d_idx on t1  (cost=0.42..116.42 rows=62 width=4)
  22.    Index Cond: ((b > 800) AND (d > 9988))
  23. (2 rows)
  24.                                                      ^
  25. test=# create index t1_b_d_c_idx on t1(b,d) include (c);
  26. CREATE INDEX
  27. test=# analyze t1;
  28. ANALYZE
  29. test=# \d t1
  30.                  Table "public.t1"
  31.  Column |  Type   | Collation | Nullable | Default
  32. --------+---------+-----------+----------+---------
  33.  a      | integer |           |          |
  34.  b      | integer |           |          |
  35.  c      | integer |           |          |
  36.  d      | integer |           |          |
  37. Indexes:
  38.     "t1_a_b_c_d_idx" btree (a, b, c, d)
  39.     "t1_b_d_c_idx" btree (b, d) INCLUDE (c)
  40.     "t1_b_d_idx" btree (b, d)
  41.     "t1_b_idx" btree (b) WHERE b < 500
  42.  
  43. test=# explain select c from t1 where b > 800 and d > 9988;
  44.                                    QUERY PLAN
  45. --------------------------------------------------------------------------------
  46.  Index Only Scan using t1_b_d_c_idx on t1  (cost=0.42..360.78 rows=204 width=4)
  47.    Index Cond: ((b > 800) AND (d > 9988))
  48. (2 rows)

五 唯一索引

唯一索引用来强制列值的唯一性，或者是多个列组合值的唯一性。目前只有B树索引能够被声明为唯一。

当一个索引被声明为唯一时，索引中不允许多个表行具有相同的索引值。空值被视为不相同。PG会自动为定义了一个唯一约束或主键的表创建一个唯一索引。该索引包含组成主键或唯一约束的所有列（可能是一个多列索引）。

唯一索引实例：

  1. --为主键自动创建唯一索引：
  2. test=# create table t4(id int primary key, name text);
  3. CREATE TABLE
  4. test=# \d t4
  5.                  Table "public.t4"
  6.  Column |  Type   | Collation | Nullable | Default
  7. --------+---------+-----------+----------+---------
  8.  id     | integer |           | not null |
  9.  name   | text    |           |          |
  10. Indexes:
  11.     "t4_pkey" PRIMARY KEY, btree (id)
  12.  
  13. test=#
  14. --为UNIQUE约束自动创建唯一索引：
  15. test=# create table t5(id int unique, name text);
  16. CREATE TABLE
  17. test=# \d t5
  18.                  Table "public.t5"
  19.  Column |  Type   | Collation | Nullable | Default
  20. --------+---------+-----------+----------+---------
  21.  id     | integer |           |          |
  22.  name   | text    |           |          |
  23. Indexes:
  24.     "t5_id_key" UNIQUE CONSTRAINT, btree (id)
  25.  
  26. --手动创建唯一索引：
  27. test=# create table t6(id int, name text);
  28. CREATE TABLE
  29. test=# \d t6
  30.                  Table "public.t6"
  31.  Column |  Type   | Collation | Nullable | Default
  32. --------+---------+-----------+----------+---------
  33.  id     | integer |           |          |
  34.  name   | text    |           |          |
  35.  
  36. test=# create unique index t6_id_idx_u on t6(id);
  37. CREATE INDEX
  38. test=# \d t6
  39.                  Table "public.t6"
  40.  Column |  Type   | Collation | Nullable | Default
  41. --------+---------+-----------+----------+---------
  42.  id     | integer |           |          |
  43.  name   | text    |           |          |
  44. Indexes:
  45.     "t6_id_idx_u" UNIQUE, btree (id)

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号