印刻数据库

外观

OceanBase 索引类型与选择 ​

索引基本概念 ​

索引是数据库中用于提高查询性能的数据结构，通过创建索引可以减少数据扫描范围，加速查询速度。OceanBase 支持多种索引类型，每种索引有不同的特点和适用场景。

索引分类 ​

1. 按数据结构分类 ​

B+树索引 ​

B+树是 OceanBase 默认的索引类型，适用于大多数查询场景：

sql
-- 创建 B+树索引
CREATE INDEX idx_name ON table_name(column_name);

-- 创建唯一 B+树索引
CREATE UNIQUE INDEX idx_unique_name ON table_name(column_name);

-- 创建复合 B+树索引
CREATE INDEX idx_composite ON table_name(column1, column2, column3);

特点：

  • 适合范围查询和排序
  • 支持前缀匹配查询
  • 叶子节点包含完整数据或指向数据的指针
  • 支持多列复合索引

适用场景：

  • 频繁的范围查询
  • 排序和分组操作
  • 前缀匹配的模糊查询
  • 多条件组合查询

哈希索引 ​

哈希索引基于哈希表实现，适用于等值查询场景：

sql
-- 创建哈希索引
CREATE INDEX idx_hash ON table_name(column_name) USING HASH;

特点：

  • 等值查询速度快
  • 不支持范围查询
  • 不支持排序
  • 适合高基数列

适用场景：

  • 频繁的等值查询
  • 高基数列的查询
  • 不涉及范围操作的场景

位图索引 ​

位图索引适用于低基数列的查询场景：

sql
-- 创建位图索引
CREATE BITMAP INDEX idx_bitmap ON table_name(column_name);

特点：

  • 适合低基数列
  • 占用空间小
  • 支持多个位图索引的 AND/OR 操作
  • 不适合频繁更新的列

适用场景：

  • 低基数列的查询
  • 多条件组合查询
  • 数据仓库和 OLAP 场景
  • 静态或更新不频繁的数据

2. 按功能分类 ​

主键索引 ​

主键索引是表的主键列上的索引，用于唯一标识表中的每一行：

sql
-- 创建表时指定主键
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 或通过 ALTER 语句添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);

特点：

  • 唯一标识每行数据
  • 自动创建，无需手动创建
  • 通常是聚集索引
  • 不允许 NULL 值

适用场景：

  • 唯一标识表中的记录
  • 作为外键引用的目标
  • 频繁用于单行查询

唯一索引 ​

唯一索引确保索引列的值唯一：

sql
-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

-- 创建复合唯一索引
CREATE UNIQUE INDEX idx_unique_composite ON table_name(column1, column2);

特点：

  • 确保索引列值唯一
  • 允许 NULL 值（但最多一个 NULL）
  • 加速等值查询

适用场景：

  • 需要保证列值唯一性的场景
  • 如用户邮箱、手机号等唯一标识
  • 频繁用于等值查询的列

普通索引 ​

普通索引是最基本的索引类型，没有唯一性限制：

sql
-- 创建普通索引
CREATE INDEX idx_normal ON table_name(column_name);

-- 创建复合普通索引
CREATE INDEX idx_normal_composite ON table_name(column1, column2, column3);

特点：

  • 加速查询
  • 不限制列值唯一性
  • 可以创建多个

适用场景：

  • 频繁用于查询的列
  • 范围查询和排序操作
  • 多条件组合查询

全文索引 ​

全文索引用于文本内容的全文搜索：

sql
-- 创建全文索引
CREATE FULLTEXT INDEX idx_fulltext ON table_name(column_name);

-- 使用全文索引查询
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('keyword');

特点：

  • 支持文本内容的全文搜索
  • 支持关键词匹配
  • 支持相关性排序

适用场景：

  • 文本内容的搜索
  • 如文章、评论、产品描述等
  • 需要关键词匹配的场景

3. 按存储方式分类 ​

聚集索引 ​

聚集索引决定数据在物理存储上的顺序：

特点：

  • 数据行的物理顺序与索引顺序一致
  • 每个表只能有一个聚集索引
  • 通常是主键索引
  • 查询速度快

适用场景：

  • 频繁根据主键查询
  • 范围查询和排序操作

非聚集索引 ​

非聚集索引的索引顺序与数据物理存储顺序无关：

特点：

  • 索引顺序与数据物理顺序无关
  • 每个表可以有多个非聚集索引
  • 索引包含指向数据行的指针
  • 适合频繁查询的列

适用场景：

  • 频繁用于查询的非主键列
  • 范围查询和排序操作
  • 多条件组合查询

索引选择原则 ​

1. 考虑查询模式 ​

  • 等值查询：优先选择哈希索引或 B+树索引
  • 范围查询：必须选择 B+树索引
  • 排序和分组：选择 B+树索引
  • 全文搜索：选择全文索引
  • 低基数列：考虑位图索引

2. 考虑列的基数 ​

  • 高基数列：适合 B+树索引或哈希索引
  • 低基数列：适合位图索引
  • 中等基数列：适合 B+树索引

3. 考虑更新频率 ​

  • 频繁更新的列：

    • 尽量少创建索引
    • 避免创建位图索引
    • 考虑索引维护成本

  • 不频繁更新的列：

    • 可以创建多个索引
    • 适合创建各种类型的索引

4. 考虑索引维护成本 ​

  • 索引会占用额外存储空间
  • 插入、更新、删除操作会维护索引
  • 索引越多，维护成本越高
  • 需要平衡查询性能和维护成本

5. 考虑复合索引的顺序 ​

  • 将选择性高的列放在前面
  • 将频繁用于查询的列放在前面
  • 考虑最左前缀原则

索引使用示例 ​

1. B+树索引示例 ​

sql
-- 表结构
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10,2)
);

-- 创建单列 B+树索引
CREATE INDEX idx_department ON employees(department_id);

-- 创建复合 B+树索引
CREATE INDEX idx_name ON employees(last_name, first_name);

-- 创建包含列的索引
CREATE INDEX idx_hire_salary ON employees(hire_date) INCLUDE (salary);

-- 使用 B+树索引的查询
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2021-01-01' ORDER BY hire_date;

2. 哈希索引示例 ​

sql
-- 创建哈希索引
CREATE INDEX idx_emp_id_hash ON employees(emp_id) USING HASH;

-- 使用哈希索引的查询
SELECT * FROM employees WHERE emp_id = 1001;

3. 位图索引示例 ​

sql
-- 创建低基数列的表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    status VARCHAR(20), -- 低基数列：'pending', 'shipped', 'delivered'
    order_date DATE
);

-- 创建位图索引
CREATE BITMAP INDEX idx_order_status ON orders(status);

-- 使用位图索引的查询
SELECT * FROM orders WHERE status = 'shipped';
SELECT * FROM orders WHERE status = 'shipped' AND order_date > '2023-01-01';

4. 全文索引示例 ​

sql
-- 创建包含文本内容的表
CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(100),
    content TEXT,
    publish_date DATE
);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_article_content ON articles(content);

-- 使用全文索引查询
SELECT * FROM articles WHERE MATCH(content) AGAINST('database performance optimization');

-- 使用全文索引并按相关性排序
SELECT *, MATCH(content) AGAINST('database performance') AS relevance 
FROM articles 
WHERE MATCH(content) AGAINST('database performance') 
ORDER BY relevance DESC;

索引选择最佳实践 ​

1. 优先考虑 B+树索引 ​

B+树索引是最通用的索引类型，适用于大多数查询场景。在不确定使用哪种索引类型时，优先选择 B+树索引。

2. 根据查询模式选择索引 ​

  • 等值查询：B+树索引或哈希索引
  • 范围查询：必须使用 B+树索引
  • 全文搜索：使用全文索引
  • 低基数列：考虑位图索引

3. 合理设计复合索引 ​

  • 将选择性高的列放在前面
  • 考虑最左前缀原则
  • 避免创建过多的复合索引
  • 考虑索引覆盖查询

4. 避免过度索引 ​

  • 索引越多，维护成本越高
  • 插入、更新、删除操作会变慢
  • 占用更多存储空间
  • 优化器选择索引的成本增加

5. 定期维护索引 ​

  • 定期分析表，更新统计信息
  • 定期重建碎片较多的索引
  • 删除不再使用的索引
  • 监控索引使用情况

6. 考虑索引覆盖 ​

  • 设计包含查询所需所有列的索引
  • 避免回表查询，提高查询性能
  • 适合频繁执行的查询

索引使用注意事项 ​

1. 最左前缀原则 ​

复合索引遵循最左前缀原则，只有使用索引的最左列或连续的前缀列才能使用索引：

sql
-- 创建复合索引
CREATE INDEX idx_a_b_c ON table_name(a, b, c);

-- 可以使用索引的查询
SELECT * FROM table_name WHERE a = 1;
SELECT * FROM table_name WHERE a = 1 AND b = 2;
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3;

-- 无法使用索引的查询
SELECT * FROM table_name WHERE b = 2;
SELECT * FROM table_name WHERE c = 3;
SELECT * FROM table_name WHERE b = 2 AND c = 3;

2. 避免索引失效 ​

以下情况可能导致索引失效：

sql
-- 1. 使用函数操作索引列
SELECT * FROM table_name WHERE UPPER(column_name) = 'VALUE';

-- 2. 使用不等于操作符
SELECT * FROM table_name WHERE column_name != 'VALUE';

-- 3. 使用 LIKE 以通配符开头
SELECT * FROM table_name WHERE column_name LIKE '%VALUE';

-- 4. 对索引列进行计算
SELECT * FROM table_name WHERE column_name + 1 = 10;

-- 5. 使用 OR 连接条件，其中一个条件没有索引
SELECT * FROM table_name WHERE indexed_column = 1 OR non_indexed_column = 2;

3. 索引列的数据类型 ​

  • 确保索引列的数据类型与查询条件的数据类型一致
  • 避免隐式类型转换
  • 选择合适的数据类型，减少存储空间

4. 索引与分区表 ​

  • 分区表的索引可以是本地索引或全局索引
  • 本地索引与分区一一对应
  • 全局索引跨越所有分区
  • 根据查询模式选择合适的索引类型

常见问题（FAQ） ​

Q1: 如何选择合适的索引类型？ ​

A1: 选择索引类型的方法：

  • 分析查询模式，确定是等值查询还是范围查询
  • 考虑列的基数，高基数列适合 B+树或哈希索引，低基数列适合位图索引
  • 考虑更新频率，频繁更新的列尽量少创建索引
  • 考虑查询性能和维护成本的平衡

Q2: 每个表应该创建多少个索引？ ​

A2: 索引数量建议：

  • 没有固定的数量限制，但建议每个表不超过 5-10 个索引
  • 根据查询需求创建必要的索引
  • 定期清理不再使用的索引
  • 平衡查询性能和维护成本

Q3: 复合索引的列顺序如何确定？ ​

A3: 复合索引列顺序的确定原则：

  • 将选择性高的列放在前面
  • 将频繁用于查询的列放在前面
  • 考虑最左前缀原则
  • 根据查询条件的组合顺序调整

Q4: 哈希索引和 B+树索引有什么区别？ ​

A4: 哈希索引和 B+树索引的区别：

  • 哈希索引适合等值查询，不支持范围查询
  • B+树索引支持等值查询和范围查询
  • 哈希索引查询速度快，但适用场景有限
  • B+树索引适用场景广泛，是默认的索引类型

Q5: 如何监控索引的使用情况？ ​

A5: 监控索引使用情况的方法：

  • 使用 EXPLAIN 分析查询计划，查看是否使用索引
  • 查看索引的统计信息
  • 监控索引的维护成本
  • 使用数据库自带的索引使用监控工具
  • 定期审计索引使用情况