12.PG数据库-重建索引

本文介绍了如何在PostgreSQL中使用REINDEX命令重建索引,包括其语法结构、原因(如索引损坏、膨胀等)、注意事项(如权限需求和性能影响),并推荐在非业务时段进行以减小影响。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1.重建索引

(1)Postgresql重建索引是通过REINDEX命令来实现的
语法结构 
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
注释说明:
INDEX        重新创建指定的索引。
TABLE        重新创建指定表的所有索引。
SCHEMA       重新创建指定 SCHEMA 的所有索引。
DATABASE     重新创建指定 DATABASE 的所有索引。
SYSTEM       重新创建当前数据库中在系统目录上的所有索引。
CONCURRENTLY 并发重新创建索引,不阻止对该表的并发插入、更新或删除。

(2)重建索引的原因
当由于软件bug或者硬件原因导致的索引不可用;
当索引包含许多空的或者近似于空的页,这个在b-tree索引会发生;
检查索引膨胀情况,如果索引膨胀太大, 会影响性能, 建议重建索引,
数据库系统修改了存储参数,需要重建不然就会失效(如修改fillfactor参数);
创建并发索引时失败,遗留了失效的索引

(3)重建索引注意事项
重建索引不同的级别的重构需要不同的权限,比如table那么就需要有这个表的权限即需要有操作索引的权限,如超级用户postgres拥有这个权限。
重构索引的目的是为了当索引的数据不可信时,即对于成本的计算会出现偏差较大,无益于优化器得到最优的执行计划以至于性能优化失败。
重构索引类似于先删除所有再创建一个索引,但是索引的条目是重新开始的。重构时当前索引是不能写的,因为此时有排他锁。
在8.1版本之前REINDEX DATABASE 只包含系统索引,并不是期望的所有指定数据库的索引。7.4版本之前REINDEX TABLE不会自动执行下级TOAST tables。
如果索引在用CONCURRENTLY选项创建失败,该索引保留为一个“invalid”。 
这类索引是无用的,可以方便的用REINDEX来重建它们。注意,只有REINDEX INDEX可以在无效的索引上执行并发创建。 

表和索引使用查看 
PostgreSQL的contrib模块提供了额外的硬盘使用信息查看方式
pgstattuple:
这个模块包含可获得常规行元组数据和索引页面的详细分析的功能
提供了pgstatetuple()和pgstatindex()两个统计表和索引的方法
比较系统表pg_class的表统计信息,这个模块还统计了表中的dead tuples
表未分析前,使用pgstatetuple()函数就能精确查询表的 page 数据,
而此时 pg_class 还没数据,说明 pg_relpages 查询了表的 page 物理文件信息。 

2.重建索引的基本语法如下

REINDEX INDEX [CONCURRENTLY] IDX_ID; 

REINDEX TABLE [CONCURRENTLY]  T_TAB1;

REINDEX SCHEMA USER1;

REINDEX DATABASE MYDATABASE; 

REINDEX SYSTEM MYDATABASE2;

3.总结

使用REINDEX命令可以非常方便的重建索引,但是需要注意重建索引会消耗性能,一般放在非业务时间段操作为宜。

<think>嗯,用户想了解如何在 PostgreSQL 中创建索引。首先,我需要回忆一下相关的知识。记得创建索引的基本语法是使用CREATE INDEX命令,对吧?不过可能需要详细说明不同索引类型,比如B-tree、Hash、GiST、SP-GiST、GIN、BRIN这些,因为PostgreSQL支持多种索引类型。 用户可能对基础语法不太清楚,所以需要先介绍最简单的单列索引,比如CREATE INDEX index_name ON table_name (column_name);。然后,组合索引的话,就是多个列的组合,语法应该是CREATE INDEX后面跟上多个列名。还有唯一索引,需要用UNIQUE关键字,这样可以确保列的值唯一。 另外,用户可能不知道索引类型的选择对性能的影响。比如B-tree适合范围查询,而GIN适合全文搜索或者数组类型的数据。这时候需要给出不同索引类型的应用场景,帮助用户理解何时使用哪种索引。 还需要注意索引的命名规范,比如使用有意义的名称,方便后续维护。删除索引的语法也要提到,因为用户创建错误时可能需要删除。DROP INDEX index_name; 这样用户就知道如何操作了。 另外,用户可能关心并发创建索引的问题,因为在生产环境中,创建索引可能会锁表,影响性能。这时候需要介绍CONCURRENTLY选项,允许在创建索引时不阻塞写操作,但可能会更耗时。 还要考虑示例代码,比如如何在实际表中创建索引,可能用户需要看到具体的例子才能理解。比如引用中的例子,创建了一个表tab_test_1,然后创建了索引idx_1,这个例子可以说明创建过程。 另外,用户可能想知道索引的维护,比如重建索引,REINDEX命令,但用户的问题主要是创建,所以暂时不需要深入,但可以提一下相关引用中的信息,比如引用3提到REINDEX,可以作为扩展知识。 还要注意用户可能存在的误区,比如索引并不是越多越好,过多的索引会影响写入性能,需要合理使用。这可能是在相关问题的部分提到,比如优化方法中的索引选择。 最后,根据用户提供的引用,需要正确引用相关文献。比如引用4详细说明了创建索引的语法,包括组合索引和唯一索引,所以回答时要对应这些内容。引用5给出了一个实际的创建示例,可以在示例部分引用。 总结一下,回答的结构应该是先介绍基本语法,然后不同索引类型,接着是高级选项如并发创建,再给出示例,最后注意事项和优化建议。确保每一步都清晰,并且引用正确的资料,比如引用2提到创建索引是性能优化的基本步骤,可以强调这一点。</think>### PostgreSQL 创建索引方法及语法详解 #### 一、基础语法 在 PostgreSQL 中,创建索引的核心语法为: $$CREATE\ [UNIQUE]\ INDEX\ [CONCURRENTLY]\ index\_name\ ON\ table\_name\ [USING\ method]\ (column\_name\ [ASC|DESC], ...);$$ 其中: - `UNIQUE`:表示唯一索引,确保列值唯一[^4] - `CONCURRENTLY`:非阻塞方式创建索引(避免表锁)[^5] - `USING method`:指定索引类型,默认为 B-tree #### 二、常用索引类型及示例 1. **单列索引** ```sql CREATE INDEX idx_employee_name ON employees (last_name); ``` 适用于对单个字段的快速查询,如`WHERE last_name = 'Smith'` 2. **组合索引** ```sql CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary DESC); ``` 优化多条件查询,如`WHERE department_id=5 AND salary>10000` 3. **唯一索引** ```sql CREATE UNIQUE INDEX idx_unique_email ON users (email); ``` 强制保证字段唯一性,功能类似`UNIQUE`约束但可包含表达式 4. **函数索引** ```sql CREATE INDEX idx_lower_title ON books (LOWER(title)); ``` 支持对表达式计算结果建立索引 #### 三、高级索引类型 | 索引类型 | 适用场景 | 示例 | |----------|---------------------------|--------------------------| | GiST | 地理数据、全文搜索 | `USING GIST(geom_column)` | | GIN | 数组、JSONB、全文搜索 | `USING GIN(jsonb_column)` | | BRIN | 大型有序数据集 | `USING BRIN(create_time)` | #### 四、并发创建索引 为避免锁表影响业务,建议生产环境使用: ```sql CREATE INDEX CONCURRENTLY idx_orders_date ON orders (order_date); ``` 注意:此方式耗时更长且需要额外磁盘空间[^5] #### 五、索引管理 1. 查看索引 ```sql \d table_name -- 在psql中查看 ``` 2. 删除索引 ```sql DROP INDEX IF EXISTS idx_old_index; ``` #### 六、最佳实践 1. 优先为`WHERE`子句和`JOIN`条件中的列建索引[^2] 2. 避免对频繁更新的表创建过多索引 3. 定期使用`REINDEX`重建碎片化索引[^3] 4. 对大表使用`pg_repack`进行在线重建
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值