5、索引

[TOC]
# 概念

- **索引**类似书籍的目录，通过目录中的关键字信息，找到书中对应的信息页。索引可以减少搜索元组的时间，提升数据的访问速度。没有索引时，只能遍历表中所有的元组，效率较低。
- 使用索引会增加插入、更新和删除操作的处理时间，因为这些操作需要同步更新索引信息。
- 索引需要额外的存储空间。创建过多索引可能对数据库性能产生负面影响。

---

# 索引的分类 - 数据组织方式分类

**按数据组织方式分类**：

- **Btree索引**：
  - 使用类似于B+树的结构来存储数据的键值，能够快速查找索引。

- **Gist索引**：
  - 适用于几何和地理等多维数据类型和集合数据类型。

- **Gin索引**：
  - GIN索引是倒排索引，能够处理包含多个键的值（比如数组）。

- **Psort**：
  - 针对列存表进行局部排序索引。

![title](/api/file/getImage?fileId=682c42502cbdfd19ee0006aa)

---

# 索引的分类 - 索引方式分类

**按索引方式分类**：

- **唯一索引**：
  - 创建唯一性索引，每次添加数据时检测表中是否有重复值。
  - 只有行存表的B-tree索引和列存表的B-tree索引支持唯一索引。
  - 主键约束和唯一约束会自动创建一个唯一索引。

- **多字段索引**：
  - 索引键值包含多个字段，最多可以声明32个字段。

- **部分索引**：
  - 只包含表的一部分数据的索引，常用于在分布不一致的表中，只索引出现频率高的键。

- **表达式索引**：
  - 基于表的一个或多个字段的表达式索引。

**示例**：

1. 创建唯一索引：
   ```sql
   ALTER TABLE author_row DISTRIBUTE BY HASH(authid);
   CREATE UNIQUE INDEX author_row_idx1 ON author_row(author_id);
   ```

2. 创建按照 PSORT 方式组织的多字段索引：
   ```sql
   CREATE INDEX author_col_idx ON author_col USING PSORT(author_id, authid_name);
   ```

3. 创建部分索引：
   ```sql
   CREATE INDEX author_row_idx2 ON author_row(author_id) WHERE author_id < 100;
   ```

4. 创建 BTREE 方式组织的表达式索引：
   ```sql
   CREATE INDEX author_row_idx3 ON author_row USING BTREE(SUBSTR(authro_address, 1, 4));
   ```

---

# 索引的分类 - 基表类型分类

**按基表类型分类**：

- **全局索引**：
  - 在非分区表上创建的索引。

- **索引分区**：
  - 在分区表上创建的索引，不支持创建部分索引。
  
**示例**：
- 不指定索引分区名字：
  ```sql
  CREATE INDEX order_p_idx1 ON orders_p(order_date) LOCAL;
  ```

- 为每个索引分区指定名字：
  ```sql
  CREATE INDEX order_p_idx2 ON orders_p(order_date) LOCAL (
      PARTITION p0_idx2,
      PARTITION p1_idx2,
      PARTITION p2_idx2,
      PARTITION p3_idx2
  );
  ```

---

# 索引修改与删除

**操作命令**：

- **ALTER INDEX**：
  - 修改索引或索引分区的名字。
  - 设置索引或索引分区为不可用。

- **DROP INDEX**：
  - 删除索引，必要时添加 `CASCADE` 进行级联删除。

**示例**：
- 修改索引名字：

```sql
  ALTER INDEX author_row_idx1 RENAME TO idx1_author_row;
```

- 修改索引分区的名字：

```sql
  ALTER INDEX order_p_idx2 RENAME PARTITION p0_idx2 TO idx2_p0;
  ```

- 设置索引不可用：
  ```sql
  ALTER INDEX idx1_author_row UNUSABLE;
  ```

- 设置索引分区不可用：
  ```sql
  ALTER INDEX order_p_idx2 MODIFY PARTITION p0_idx2 UNUSABLE;
  ```

- 删除索引：
  ```sql
  DROP INDEX order_p_idx2;
  ```

---

# 重建索引

**以下情况需重建索引**：

- 索引崩溃，并且不再包含有效数据。
- 索引变得“臃肿”，包含大量空页或接近空页。
- 为索引更改了存储参数（例如填充因子），并希望这个更改完全生效。

**索引重建的两种方式**：

- `REINDEX`
- `ALTER INDEX name REBUILD`

**示例**：
- 重建索引：

```sql
  ALTER INDEX idx1_author_row REBUILD;
```

- 重建索引分区：
```sql
  REINDEX order_p PARTITION p2;
```

---

# 索引的使用

**创建索引时，以下建议作为参考**：

- 在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 在经常使用 `WHERE` 子句的列上创建索引，加快条件的判断速度。
- 为经常出现在关键字 `ORDER BY`、`GROUP BY`、`DISTINCT` 后面的字段建立索引。

---

# 索引的利与弊

**索引的优点**：

- 点查询提速显著，直接定位到需要的位置，减少无效IO。
- 多条件组合查询，可以过滤大量数据，缩小扫描范围。
- 利用倒排索引加速全文检索。
- 利用等值条件索引查询速度快的优势，结合嵌套循环提高多表连接效率。
- 提供主键和唯一性约束，满足业务需要。
- 利用Btree索引天然有序的特点，优化查询计划。

**索引的缺点**：

- 索引页面占用额外空间，导致一定的磁盘膨胀。
- 每次数据导入同时需要更新索引，影响导入性能。
- 索引页面没有可见性，存在垃圾数据，需要定期清理。
- 索引扫描性能并不总是比顺序扫描性能更好，若优化器判断有误，可能导致查询性能劣化。
- 索引需要记录XLOG，增加日志量。
- 每个索引至少需要一个文件，增加备份恢复、扩容等操作的代价。

鉴于索引的使用是一把双刃剑，创建索引要谨慎，只在有需要的列上创建，不能在过滤大量数据的条件列上创建索引。

DBLOG

导航1234

最近发表

友情链接