乱序写入导致的索引膨胀(B-tree, GIN, GiST皆如此)

最新推荐文章于 2023-08-15 14:04:58 发布

转载最新推荐文章于 2023-08-15 14:04:58 发布 · 508 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/272109

文章标签：

#数据库

本文探讨了PostgreSQL中B-Tree、GIN、GiST及SP-GiST索引的膨胀问题，通过对比不同写入顺序对索引大小的影响，提出了通过重建索引来优化索引膨胀的方法。

背景

有些场景，用户会发现重建索引，索引比原来更小。

通常这种情况是索引字段乱序写入，导致索引频繁分裂，使得索引页并不是百分百填满。膨胀使然。

B-Tree索引

由于索引页中的数据是有序的，因此在乱序写入时，索引页可能出现分裂，分裂多了，空洞就会多起来（一页里面没有填满）。

例子

1、先建索引，乱序写入。

postgres=# create table t_idx_split(id int);  
CREATE TABLE  
postgres=# create index idx_t_idx_split on t_idx_split (id);  
CREATE INDEX  
postgres=# insert into t_idx_split select random()*10000000 from generate_series(1,10000000);  
INSERT 0 10000000  
postgres=# \di+ t_idx_sp  
  
postgres=# \di+ idx_t_idx_split   
                                List of relations  
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description   
--------+-----------------+-------+----------+-------------+--------+-------------  
 public | idx_t_idx_split | index | postgres | t_idx_split | 280 MB |   
(1 row)

2、先建索引，顺序写入。

postgres=# truncate t_idx_split ;  
TRUNCATE TABLE  
postgres=# \di+ idx_t_idx_split   
                                  List of relations  
 Schema |      Name       | Type  |  Owner   |    Table    |    Size    | Description   
--------+-----------------+-------+----------+-------------+------------+-------------  
 public | idx_t_idx_split | index | postgres | t_idx_split | 8192 bytes |   
(1 row)  
  
postgres=# insert into t_idx_split select generate_series(1,10000000);  
INSERT 0 10000000  
postgres=# \di+ idx_t_idx_split   
                                List of relations  
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description   
--------+-----------------+-------+----------+-------------+--------+-------------  
 public | idx_t_idx_split | index | postgres | t_idx_split | 214 MB |   
(1 row)

3、先写入，后建索引。

postgres=# drop index idx_t_idx_split ;  
DROP INDEX  
postgres=# create index idx_t_idx_split on t_idx_split (id);  
CREATE INDEX  
postgres=# \di+ idx_t_idx_split   
                                List of relations  
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description   
--------+-----------------+-------+----------+-------------+--------+-------------  
 public | idx_t_idx_split | index | postgres | t_idx_split | 214 MB |   
(1 row)

很显然，顺序写入时，索引大小和后建索引大小一致，没有出现膨胀。

GIN索引

GIN索引也是树结构，也有膨胀的可能。

对于gin索引，实际上膨胀现象更加的明显，因为通常GIN是对多值类型的索引，而多值类型，通常输入的顺序更加无法保证。

GIN主树索引页会膨胀较厉害。

GiST和SP-GiST索引

同样存在这个现象，当写入的空间数据BOUND BOX是空间无序写入的，那么就会导致膨胀。

重建索引，可以收缩膨胀

建议并行建索引，防止堵塞DML

使用CONCURRENTLY关键字，并行创建索引，不会堵塞DML，但是创建索引的时间比正常创建索引的时间会略长。

Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]