印刻数据库

外观

OceanBase 索引创建与维护 ​

索引创建核心概念 ​

索引是数据库中用于提高查询性能的数据结构，通过创建索引可以快速定位和访问表中的数据。OceanBase 数据库支持多种索引类型，包括 B 树索引、位图索引和全文索引等。合理创建和维护索引对于提高 OceanBase 集群的查询性能至关重要。

索引创建原则 ​

  • 选择性原则：选择选择性高的列创建索引，即列值重复率低的列
  • 最左前缀原则：复合索引中，查询条件应包含索引的最左前缀列
  • 覆盖索引原则：尽量使用覆盖索引，减少回表查询
  • 避免过度索引：过多索引会影响写入性能，增加存储开销
  • 考虑查询模式：根据实际查询场景创建合适的索引

索引创建方法 ​

1. 创建表时创建索引 ​

在 CREATE TABLE 语句中直接定义索引，是最常用的索引创建方式。

语法 ​

sql
CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    [INDEX index_name (column1 [, column2, ...]) [index_options]]
);

示例 ​

sql
-- 创建单字段索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_email (email)
);

-- 创建复合索引
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_user_date (user_id, order_date)
);

-- 创建唯一索引
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    sku VARCHAR(50),
    name VARCHAR(100),
    UNIQUE INDEX idx_sku (sku)
);

2. 使用 CREATE INDEX 创建索引 ​

对于已存在的表，可以使用 CREATE INDEX 语句创建索引。

语法 ​

sql
CREATE [UNIQUE] [BITMAP] INDEX index_name
    ON table_name (column1 [, column2, ...])
    [index_options];

示例 ​

sql
-- 创建单字段索引
CREATE INDEX idx_name ON users (name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_user_amount ON orders (user_id, amount);

-- 创建位图索引（适用于低基数列）
CREATE BITMAP INDEX idx_status ON orders (status);

3. 使用 ALTER TABLE 添加索引 ​

通过 ALTER TABLE 语句也可以为已存在的表添加索引。

语法 ​

sql
ALTER TABLE table_name ADD [UNIQUE] [BITMAP] INDEX index_name (column1 [, column2, ...]);

示例 ​

sql
-- 添加单字段索引
ALTER TABLE users ADD INDEX idx_phone (phone);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_username (username);

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_order_date_amount (order_date, amount);

索引维护操作 ​

1. 查看索引 ​

查看表的所有索引 ​

sql
-- 方法1：使用 SHOW INDEX 语句
SHOW INDEX FROM table_name;

-- 方法2：查询系统视图
SELECT * FROM information_schema.statistics WHERE table_schema = 'database_name' AND table_name = 'table_name';

查看索引的详细信息 ​

sql
-- 使用 ob_admin 工具查看索引信息
ob_admin index_info --table table_name --index index_name

2. 修改索引 ​

OceanBase 支持修改索引的部分属性，如索引名、索引注释等。

修改索引名称 ​

sql
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;

示例 ​

sql
ALTER TABLE users RENAME INDEX idx_email TO idx_user_email;

3. 删除索引 ​

当索引不再需要或影响性能时，可以删除索引。

语法 ​

sql
-- 方法1：使用 DROP INDEX 语句
DROP INDEX index_name ON table_name;

-- 方法2：使用 ALTER TABLE 语句
ALTER TABLE table_name DROP INDEX index_name;

示例 ​

sql
-- 删除单字段索引
DROP INDEX idx_name ON users;

-- 使用 ALTER TABLE 删除索引
ALTER TABLE orders DROP INDEX idx_user_amount;

4. 重建索引 ​

当索引出现碎片化或性能下降时，可以重建索引。

语法 ​

sql
-- 方法1：使用 ALTER TABLE 语句重建所有索引
ALTER TABLE table_name REBUILD INDEX;

-- 方法2：使用 ALTER TABLE 语句重建指定索引
ALTER TABLE table_name REBUILD INDEX index_name;

-- 方法3：删除并重新创建索引
DROP INDEX index_name ON table_name;
CREATE INDEX index_name ON table_name (columns);

示例 ​

sql
-- 重建表的所有索引
ALTER TABLE users REBUILD INDEX;

-- 重建指定索引
ALTER TABLE orders REBUILD INDEX idx_user_date;

索引维护策略 ​

1. 定期检查索引使用情况 ​

定期检查索引的使用情况，识别未使用或使用频率低的索引，及时清理不必要的索引。

查看索引使用统计 ​

sql
-- 查询索引使用情况
SELECT 
    table_name,
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM 
    oceanbase.gv$index_usage
WHERE 
    database_name = 'database_name' AND table_name = 'table_name';

2. 监控索引碎片化 ​

索引碎片化会影响查询性能，需要定期监控和处理。

查看索引碎片化情况 ​

sql
-- 查询索引碎片化信息
SELECT 
    table_name,
    index_name,
    btree_space_usage,
    leaf_node_count,
    empty_leaf_node_count
FROM 
    oceanbase.gv$index_stat
WHERE 
    database_name = 'database_name' AND table_name = 'table_name';

3. 定期重建索引 ​

对于碎片化严重的索引，需要定期重建以提高性能。

重建索引的时机 ​

  • 索引碎片化率超过 30%
  • 索引大小显著增长
  • 查询性能明显下降
  • 大量数据插入、更新或删除后

4. 优化索引结构 ​

根据业务需求和查询模式，定期优化索引结构。

优化策略 ​

  • 调整复合索引的列顺序
  • 拆分或合并索引
  • 添加或删除索引
  • 调整索引类型

索引创建与维护最佳实践 ​

1. 索引设计最佳实践 ​

  • 优先考虑主键索引：主键索引是表的聚集索引，查询性能最高
  • 合理设计复合索引：将查询频率高的列放在前面，选择性高的列放在前面
  • 避免重复索引：不要创建与现有索引功能重复的索引
  • 考虑覆盖索引：将查询中常用的列包含在索引中，减少回表查询
  • 限制索引数量：每个表的索引数量不宜过多，建议不超过 5-8 个

2. 索引创建最佳实践 ​

  • 在业务低峰期创建索引：创建索引会锁表或影响写入性能
  • 对于大表使用 ONLINE 方式创建索引：减少对业务的影响
  • 创建索引前评估影响：使用 EXPLAIN 分析索引对查询的影响
  • 测试索引性能：在测试环境验证索引的查询性能

3. 索引维护最佳实践 ​

  • 建立索引维护计划：定期检查、监控和优化索引
  • 使用自动化工具：利用 OCP 或其他监控工具自动监控索引状态
  • 记录索引变更：建立索引变更日志，便于追踪和回滚
  • 培训开发人员：提高开发人员的索引设计意识，避免不合理的索引创建

4. 大表索引操作最佳实践 ​

  • 对于超大型表，考虑分批次创建索引：减少单次操作对系统的影响
  • 使用并行方式创建索引：提高大表索引创建的速度
  • 在创建索引前备份数据：防止索引创建过程中出现意外
  • 监控索引创建进度：及时了解索引创建的状态和进度

常见索引问题处理 ​

1. 索引创建失败 ​

症状 ​

  • CREATE INDEX 语句执行失败
  • 报错信息包含 "Out of memory" 或 "Lock wait timeout"
  • 索引创建过程中系统负载过高

解决方案 ​

  • 增加系统内存或调整内存参数
  • 延长锁等待超时时间
  • 在业务低峰期创建索引
  • 对于大表，考虑分批次创建或使用 ONLINE 方式

2. 索引性能下降 ​

症状 ​

  • 使用索引的查询响应时间变长
  • 索引扫描行数增加
  • 索引碎片化率高

解决方案 ​

  • 重建碎片化严重的索引
  • 优化查询语句，确保使用索引的最左前缀
  • 调整索引结构，适应新的查询模式
  • 收集最新的统计信息

3. 索引占用空间过大 ​

症状 ​

  • 索引大小超过表数据大小
  • 存储使用率过高
  • 备份和恢复时间变长

解决方案 ​

  • 删除不必要的索引
  • 优化索引结构，减少索引列数量
  • 调整索引压缩参数
  • 考虑使用分区表，分散索引压力

常见问题（FAQ） ​

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

A1: 选择索引类型的依据：

  • B 树索引：适用于大多数查询场景，特别是高基数列
  • 位图索引：适用于低基数列，如性别、状态等
  • 全文索引：适用于文本搜索场景
  • 唯一索引：适用于需要保证列值唯一性的场景

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

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

  • 将查询频率最高的列放在最前面
  • 将选择性最高的列放在最前面
  • 考虑最左前缀原则，确保查询条件能命中索引
  • 将范围查询列放在最后面

Q3: 如何识别未使用的索引？ ​

A3: 识别未使用索引的方法：

  • 查询 oceanbase.gv$index_usage 视图，查看索引的使用统计
  • 监控索引的 rows_selected 指标，长时间为 0 的索引可能未被使用
  • 使用性能分析工具，如 OceanBase Performance Analyzer，识别未使用的索引

Q4: 大表创建索引会影响业务吗？ ​

A4: 大表创建索引可能会影响业务，具体影响取决于：

  • 索引创建方式：ONLINE 方式影响较小
  • 业务访问模式：写入频繁的表影响较大
  • 系统资源：资源充足的系统影响较小

建议在业务低峰期创建大表索引，并使用 ONLINE 方式。

Q5: 索引越多查询性能越好吗？ ​

A5: 不是，索引越多查询性能不一定越好。过多的索引会导致：

  • 写入性能下降，因为每次写入都需要更新所有相关索引
  • 存储开销增加，占用更多磁盘空间
  • 查询优化器选择困难，可能选择错误的索引
  • 索引维护成本增加

因此，应根据实际查询需求合理创建索引，避免过度索引。