postgresql索引_pgsql添加索引

在 PostgreSQL 中，索引是用于提高查询性能的重要工具。索引是一种数据结构，可以加速特定列的查询操作。

---

pgsql索引

要使用索引，首先需要创建索引。可以通过以下方式在表上创建索引：

CREATE INDEX index_name ON table_name (column_name);

其中，index_name 是索引的名称，table_name 是要创建索引的表名，column_name 是要索引的列名。

例如，如果要在名为 users 的表上为 username 列创建索引，可以执行以下命令：

CREATE INDEX idx_users_username ON users (username);

创建索引后，可以在查询中使用它来提高性能。当查询使用了索引列作为过滤条件时，数据库可以使用索引来快速定位到匹配的行，而不需要逐行扫描整个表。

以下是一个使用索引的例子：

SELECT * FROM users WHERE username = 'John';
--上述查询中，如果 username 列有索引，数据库可以直接使用索引定位到包含用户名为 'John' 的行，从而提高查询效率。

通常情况下，以下情况会用到索引：

1. 频繁用于查询的列，例如经常用于过滤、排序或连接操作的列。
2. 大型表，其中包含大量数据行。
3. 需要快速响应时间的查询。

但是，需要注意的是，索引也有一些缺点。创建和维护索引需要额外的存储空间和处理时间。此外，当对表进行更新（如插入、更新和删除）时，索引也需要相应地进行更新，这可能会带来一定的性能开销。

因此，在使用索引时需要权衡利弊，根据具体应用场景和查询需求来决定是否添加索引。

索引的高效性主要归功于其特殊的数据结构

在 PostgreSQL 中，默认的索引实现是B树（B-tree）。B树是一种自平衡的搜索树，用于存储索引键（列值）和指向对应数据的指针。B树的特点使得它能够在平均时间复杂度为 O(log n) 的情况下实现高效的查询。

当创建索引时，根据索引列的值构建了一个有序的 B 树结构。每个节点包含多个键和指针，用于确定索引键的位置。通过这种结构，数据库可以利用二分查找的算法从根节点开始逐层比较索引键的值，以在树中快速定位到指定的数据行。

通过索引的这种组织方式，数据库可以避免全表扫描，而是只需从树的根节点开始沿着合适的路径向下遍历，定位到符合条件的数据行。这种方式避免了不必要的磁盘 I/O 操作，从而显著提高了查询效率。

除了 B 树索引，PostgreSQL 还支持其他类型的索引，如哈希索引、GIN 索引和 GiST 索引，用于不同类型的查询优化。每种索引类型都有其特定的数据结构和适用场景。

需要注意的是，索引的效果不是绝对的，其性能取决于数据分布、查询条件和使用的索引类型等因素。因此，在使用索引时，需要仔细评估和测试，并根据实际情况做出合理的决策。

索引失效的情况

1. 不适当的查询条件：当查询条件过于模糊、范围过大或与大部分数据行匹配时，索引的效果会减弱甚至失效。例如，一个查询条件为 WHERE name LIKE '%abc%' 的模糊查询可能无法利用索引进行快速定位，而需要扫描整个表。

2. 列数据类型转换：当索引列与查询的条件列类型不匹配时，可能会导致索引失效。例如，如果索引列为字符串类型，而查询条件中使用了数字类型，可能无法有效使用索引。

3. 高基数列：索引的基数是指索引列中不重复值的个数。当索引列的基数很高时，即列中有大量不重复的值，索引的选择性降低，可能导致失效。

4. 不平衡的数据分布：如果数据在表中分布不均匀，即某些索引值的行数远远超过其他值，索引的效果可能会下降。例如，一个只有两个可能值的列上的索引就几乎没有效果。

5. 大量更新操作：如果对索引列频繁进行插入、更新或删除操作，会导致索引维护的开销增加，并可能影响查询性能。

在这些情况下，索引可能无法提供预期的性能优势，甚至可能比全表扫描更慢。在优化查询性能时，需要仔细分析查询语句、表结构和数据分布等因素，以确保正确选择和使用索引，并进行性能测试和调整。

索引失效的具体场景

1. 模糊查询：当使用了以通配符 % 开头的模糊查询条件（如 LIKE '%abc'）时，B树索引无法有效利用前缀匹配，导致索引失效。

2. 小范围查询：当查询条件的范围过小，比如只匹配到表中的少数几行数据时，优化器可能会判断全表扫描的性能更好，因此可能导致索引失效。

3. OR 查询：当查询条件使用 OR 连接多个子条件时，每个子条件可能涵盖不同索引，而优化器无法同时使用多个不同索引，因此可能导致索引失效。

4. 组合索引顺序使用不当：当表上有多列组合索引时，查询条件的顺序可能与索引的列顺序不一致时，索引失效。例如，索引是 (column1, column2)，但查询条件是 WHERE column2 = ... AND column1 = ...。

5. 高基数列条件查询：当使用索引列的高基数（不重复值个数很多）进行查询时，由于索引的选择性下降，可能导致索引失效。特别是当查询范围涵盖了大部分表的行时，优化器可能会选择全表扫描而不是利用索引。

6. NULL 值查询：对于一个包含 NULL 值的索引列，查询条件为 WHERE column_name IS NULL 是可以利用索引的。但是，如果查询条件为 WHERE column_name = NULL，则索引可能无法起作用，并导致失效。