【postgresql初级使用】索引带来性能提升，它背后默默服务的维护者reindex功不可莫，并发维护与业务的取舍

码农世界 2024-06-07 后端 77 次浏览 0个评论

重建索引reindex

专栏内容：

postgresql使用入门基础
手写数据库toadb
并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

重建索引reindex
概述
命令介绍
- reindex工具
- SQL命令语法
- 案例分析
- - 查看索引大小
  - 总结
  - 结尾
    概述
    
    前面几章节有关索引的内容，介绍了索引的原理，根据索引算法不同列出了postgresql支持的五大索引类型，还有扩展索引类型；同时分几节介绍了创建不同用途的索引，如部分索引，唯一性索引，基于表达式的索引等等，满足不同场景需求。
    
    之前这些内容，都是分享索引的用法，索引在使用的过程中，不同的插入，删除会使得索引存储变得越来越稀疏，在查询索引数据时，需要从磁盘读取的数据量越来越多，使得索引查询优势不再明显。
    
    这就涉及到本节的内容-重建索引reindex，索性数据需要定期的进行维护，及时的消除索引数据的膨胀，使得索引查询效率能够保持。
    
    命令介绍
    
    索引如何来维护呢？我们来看一下reindex命令的语法。
    
    reindex工具
    
    有两种途径来执行reindex命令，
    - 一种是通过工具来执行
      在安装目录的bin目录下，会有名称为reindexdb的可执行工具，可以查看它的帮助进行操作。
```
[zpzhao@hatch bin]$ ./reindexdb --help
reindexdb reindexes a PostgreSQL database.
Usage:
  reindexdb [OPTION]... [DBNAME]
Options:
  -a, --all                    reindex all databases
      --concurrently           reindex concurrently
  -d, --dbname=DBNAME          database to reindex
  -e, --echo                   show the commands being sent to the server
  -i, --index=INDEX            recreate specific index(es) only
  -j, --jobs=NUM               use this many concurrent connections to reindex
  -q, --quiet                  don't write any messages
  -s, --system                 reindex system catalogs only
  -S, --schema=SCHEMA          reindex specific schema(s) only
  -t, --table=TABLE            reindex specific table(s) only
      --tablespace=TABLESPACE  tablespace where indexes are rebuilt
  -v, --verbose                write a lot of output
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit
Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database
Read the description of the SQL command REINDEX for details.
Report bugs to .
PostgreSQL home page: 
```
      - 一种是通过SQL命令来执行
        登陆客户端后，SQL命令中reindex可以进行重建索引。
        
        下面重点介绍一下SQL命令的语法与使用方法，工具命令也同样有这些选择。
        
        SQL命令语法
        
        REINDEX [ ( option, ...) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;
        
        语法说明
        
        option可选项，有三个选项供选择：
        
        CONCURRENTLY , 有此选项时，采用并行重建的方式，具体后面介绍；
        TABLESPACE new_tablespace，指定重建后的索引存储的表空间，如果不指定，则为当前表空间；
        VERBOSE ，打印一些进度信息；
        
        指定索引的范围，可以选择如下内容之一：
        
        index，具体某一个索引，后面name 为索引的名称；
        table, 重建指定表上的所有索引，name 为表名；
        schema, 重建指定schema上的所有索引， name为对应的schema名称；
        database, 重建指定database上的所有索引， name为指定的database，如不指定name时，为当前登陆的database;
        system, 重建系统表的索引，在指定database时，会跳过系统表的索引，此时可以不指定name。
        
        案例分析
        
        下面一起来看下如何维护索引，案例使用的表是products产品表，定义如下。
        
        CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL, category VARCHAR(255) );
        
        除了主键索引外，为了方便观测，在product_name上新增了一个idx_proname的默认索引类型的索引。
        
        查看索引大小
        
        我们先来看一下索引数据的大小。
        
        postgres=> create index idx_proname on products (product_name ); CREATE INDEX postgres=> select pg_size_pretty(pg_total_relation_size('idx_proname')) ; pg_size_pretty ---------------- 3104 kB (1 row)
        
        这里使用postgresql 内置函数 pg_total_relation_size 来获取索引的数据大小；
        
        pg_size_pretty函数，是把字节数转换为计量单位(KB,MB，GB等等)。
        
        可以看到，初建之后，索引大小为3104KB, 下面我们模拟使用。
        
        更新索引键的列，使得索引也会发生变化，让产品名后面再追加一个产品ID，生成新的产品名称。
        
        postgres=> update products set product_name = product_name || product_id ; UPDATE 100000
        
        经过update操作，所有行都会被更新了一遍，理论上每条数据的索引都要新生成一次。
        
        下面再来看索引数据的大小。
        
        postgres=> select pg_size_pretty(pg_total_relation_size('idx_proname')) ; pg_size_pretty ---------------- 6184 kB (1 row)
        
        果然，索引数据翻了一倍还多一些。
        
        为什么比一倍还多呢？这里因为Btree索引算法，是一个树形结构，叶子节点才会存储真正的键值，那么随着叶子节点的增多，树的中间层节点也会增加，所以就会比实际数据多占一些空间。
        
        经过长时间使用后，索引数据确实膨胀了不少，所以必须定期进行维护，下面看看维护后的效果。
        
        postgres=> reindex index idx_proname ; REINDEX
        
        执行SQL语句 reindex ,指定维护的索引idx_proname，很快就执行完成了。
        
        然后再看一下索引数据的大小，是不是恢复了呢？
        
        postgres=> select pg_size_pretty(pg_total_relation_size('idx_proname')) ; pg_size_pretty ---------------- 3104 kB (1 row)
        
        不出所料，数据行数没有发生变化，经过重建后，索引大小与新建时一模一样，太神奇了，又可以开心玩耍了。
        
        总结
        
        索引虽好，但不要贪哟，还需要时常维护的。本文介绍了索引维护命令reindex，它有两种命令形态，一是工具方式执行，另一种是SQL的方式。
        
        通过一个案例，可以清晰的看到，经过一系列模拟使用后，索引数据明显发生了膨胀，势必造成索引的磁盘IO增加，影响索引的性能，经过reindex之后，又恢复了它初建的大小。
        
        结尾
        
        非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！
        
        作者邮箱：study@senllang.onaliyun.com
        如有错误或者疏漏欢迎指出，互相学习。
        
        注：未经同意，不得转载！