印刻数据库

外观

OceanBase 表索引设计规范 ​

表设计规范 ​

1. 表命名规范 ​

  • 表名：使用小写字母、数字和下划线组合，以字母开头，不超过 64 个字符
  • 分区表：在表名后添加 _pt 后缀，如 order_pt
  • 历史表：在表名后添加 _history 后缀，如 order_history
  • 临时表：在表名前添加 tmp_ 前缀，如 tmp_order

2. 字段命名规范 ​

  • 字段名：使用小写字母、数字和下划线组合，以字母开头，不超过 64 个字符
  • 主键字段：使用 id 或表名缩写 + _id，如 order_id
  • 外键字段：使用关联表名缩写 + _id，如 user_id
  • 时间字段：
    • 创建时间：gmt_create
    • 修改时间：gmt_modified
    • 业务时间：根据业务含义命名，如 order_time

3. 字段类型选择 ​

业务场景推荐类型不推荐类型
整数 IDBIGINT UNSIGNEDINT（可能溢出）
字符串 IDVARCHAR(64)CHAR（空间浪费）
枚举类型VARCHAR(32)ENUM（修改困难）
时间字段DATETIME(3)TIMESTAMP（时区问题）
金额字段DECIMAL(18,4)FLOAT、DOUBLE（精度问题）
状态字段TINYINTINT（空间浪费）
大文本LOBVARCHAR(65535)（性能问题）

4. 表结构设计原则 ​

  • 避免宽表：单表字段数建议不超过 50 个
  • 避免冗余字段：尽量通过关联查询获取数据
  • 合理设置默认值：为非空字段设置合理默认值
  • 使用 NOT NULL：尽量避免使用 NULL，除非必要
  • 合理设置 AUTO_INCREMENT：仅用于自增主键，避免用于业务字段

索引设计基础 ​

1. 索引类型 ​

索引类型适用场景优缺点
B+ Tree 索引范围查询、排序、分组支持范围查询，查询效率高
哈希索引等值查询等值查询效率高，不支持范围查询
位图索引低基数列，如性别、状态空间效率高，适合位图运算
全文索引文本搜索支持全文检索，适合文本字段

2. 索引存储结构 ​

  • 聚簇索引：索引和数据存储在一起，主键默认为聚簇索引
  • 二级索引：索引和数据分开存储，需要回表查询
  • 联合索引：多个字段组成的索引，遵循最左前缀原则

3. 索引设计原则 ​

  • 选择性原则：选择选择性高的字段创建索引
  • 最左前缀原则：联合索引的查询条件需包含最左字段
  • 覆盖索引原则：查询字段全部包含在索引中，避免回表
  • 最小化原则：索引字段数尽可能少
  • 避免重复索引：避免创建重复或冗余索引

索引设计规范 ​

1. 主键设计 ​

  • 推荐使用 BIGINT UNSIGNED：避免使用 VARCHAR 作为主键
  • 自增主键：优先使用自增主键，避免主键值频繁更新
  • 复合主键：仅在必要时使用，如多对多关系表
  • 不使用业务字段：避免使用经常变化的业务字段作为主键

2. 唯一索引设计 ​

  • 唯一约束：对于需要唯一约束的字段，使用唯一索引
  • 联合唯一索引：多个字段组合唯一时，使用联合唯一索引
  • 避免过多唯一索引：唯一索引会增加写入开销

3. 普通索引设计 ​

  • 查询频繁的字段：为 WHERE、JOIN、ORDER BY、GROUP BY 中频繁使用的字段创建索引
  • 联合索引顺序：将选择性高的字段放在前面
  • 覆盖查询：设计联合索引覆盖常用查询字段
  • 避免索引膨胀：定期清理无用索引

4. 索引创建规范 ​

sql
-- 推荐：单列索引
CREATE INDEX idx_user_name ON user(name);

-- 推荐：联合索引，选择性高的字段在前
CREATE INDEX idx_order_user_time ON order(user_id, order_time);

-- 推荐：唯一索引
CREATE UNIQUE INDEX uk_user_email ON user(email);

-- 不推荐：冗余索引
CREATE INDEX idx_user_id ON order(user_id);
CREATE INDEX idx_user_id_time ON order(user_id, order_time); -- 冗余索引，因为 idx_user_id 已存在

分区表设计规范 ​

1. 分区类型选择 ​

分区类型适用场景优缺点
范围分区时间序列数据便于数据归档和查询
哈希分区随机分布数据数据分布均匀，查询性能稳定
列表分区枚举值分区便于按枚举值管理数据
复合分区复杂业务场景结合多种分区优势

2. 分区键选择 ​

  • 范围分区：选择时间字段或自增 ID 作为分区键
  • 哈希分区：选择选择性高的字段作为分区键
  • 列表分区：选择枚举类型字段作为分区键

3. 分区表创建规范 ​

sql
-- 推荐：按时间范围分区
CREATE TABLE order_pt (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  order_time DATETIME(3) NOT NULL,
  amount DECIMAL(18,4) NOT NULL,
  PRIMARY KEY (order_id, order_time)
) PARTITION BY RANGE COLUMNS(order_time) (
  PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
  PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
  PARTITION p202303 VALUES LESS THAN ('2023-04-01'),
  PARTITION p202304 VALUES LESS THAN ('2023-05-01')
);

-- 推荐：按哈希分区
CREATE TABLE user_pt (
  user_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(64) NOT NULL,
  PRIMARY KEY (user_id)
) PARTITION BY HASH(user_id) PARTITIONS 16;

索引优化规范 ​

1. 索引维护 ​

  • 定期分析表：使用 ANALYZE TABLE 分析表统计信息
  • 定期检查索引使用情况：通过 V$INDEX_USAGE 视图查看索引使用情况
  • 删除无用索引：删除长期未使用的索引
  • 重建索引：定期重建碎片化严重的索引

2. 索引使用优化 ​

sql
-- 推荐：使用索引覆盖查询
SELECT user_id, order_time FROM order WHERE user_id = 123;

-- 不推荐：使用 SELECT *，可能导致回表
SELECT * FROM order WHERE user_id = 123;

-- 推荐：使用最左前缀原则
SELECT * FROM order WHERE user_id = 123 AND order_time > '2023-01-01';

-- 不推荐：违反最左前缀原则
SELECT * FROM order WHERE order_time > '2023-01-01';

-- 推荐：使用索引列作为排序字段
SELECT * FROM order WHERE user_id = 123 ORDER BY order_time;

-- 不推荐：使用非索引列排序
SELECT * FROM order WHERE user_id = 123 ORDER BY amount;

3. 索引性能监控 ​

sql
-- 查看索引使用情况
SELECT * FROM V$INDEX_USAGE WHERE table_name = 'order';

-- 查看索引大小
SELECT index_name, ROUND(bytes/1024/1024, 2) AS size_mb FROM V$INDEX_SIZE WHERE table_name = 'order';

-- 查看慢查询中未使用索引的语句
SELECT * FROM V$SLOW_QUERY WHERE is_index_used = 0;

常见索引设计错误 ​

1. 过多索引 ​

问题：创建过多索引会增加写入开销，降低性能 解决方案：

  • 只创建必要的索引
  • 定期清理无用索引
  • 优先使用联合索引覆盖多个查询场景

2. 不当的联合索引顺序 ​

问题：联合索引字段顺序不当，导致索引使用率低 解决方案：

  • 将选择性高的字段放在前面
  • 考虑查询频率和场景
  • 遵循最左前缀原则

3. 索引字段类型不匹配 ​

问题：查询条件字段类型与索引字段类型不匹配，导致索引失效 解决方案：

  • 确保查询条件字段类型与索引字段类型一致
  • 避免在索引字段上使用函数

4. 忽略覆盖索引 ​

问题：未充分利用覆盖索引，导致回表查询 解决方案：

  • 设计索引时考虑常用查询字段
  • 使用 EXPLAIN 分析查询计划
  • 调整索引覆盖常用查询

5. 主键设计不当 ​

问题：使用 VARCHAR 或频繁更新的字段作为主键 解决方案：

  • 优先使用 BIGINT UNSIGNED 自增主键
  • 避免使用业务字段作为主键
  • 复合主键仅在必要时使用

索引设计最佳实践 ​

1. 小表（< 100 万行） ​

  • 主键索引：必须创建
  • 唯一索引：根据业务需求创建
  • 普通索引：根据查询需求创建，建议不超过 5 个

2. 中表（100 万 - 1000 万行） ​

  • 主键索引：必须创建
  • 唯一索引：根据业务需求创建
  • 普通索引：精心设计，建议不超过 8 个
  • 考虑使用覆盖索引减少回表

3. 大表（> 1000 万行） ​

  • 主键索引：必须创建
  • 唯一索引：根据业务需求创建
  • 普通索引：严格控制，建议不超过 10 个
  • 必须使用分区表
  • 考虑使用二级分区
  • 定期维护索引，清理无用索引

4. 热点表 ​

  • 使用分区表分散热点
  • 考虑使用局部索引
  • 避免在热点字段上创建过多索引
  • 定期监控索引使用情况

索引设计案例 ​

1. 订单表设计 ​

sql
-- 表结构
CREATE TABLE `order` (
  `order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `order_time` DATETIME(3) NOT NULL COMMENT '下单时间',
  `pay_time` DATETIME(3) DEFAULT NULL COMMENT '支付时间',
  `amount` DECIMAL(18,4) NOT NULL COMMENT '订单金额',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态',
  `gmt_create` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `gmt_modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`order_id`),
  KEY `idx_user_time` (`user_id`, `order_time`),
  KEY `idx_shop_time` (`shop_id`, `order_time`),
  KEY `idx_status_time` (`status`, `order_time`),
  KEY `idx_pay_time` (`pay_time`)
) COMMENT '订单表';

2. 用户表设计 ​

sql
-- 表结构
CREATE TABLE `user` (
  `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `email` VARCHAR(128) NOT NULL COMMENT '邮箱',
  `mobile` VARCHAR(20) NOT NULL COMMENT '手机号',
  `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别',
  `age` TINYINT UNSIGNED DEFAULT NULL COMMENT '年龄',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态',
  `gmt_create` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `gmt_modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_username` (`username`),
  UNIQUE KEY `uk_email` (`email`),
  UNIQUE KEY `uk_mobile` (`mobile`),
  KEY `idx_gender_age` (`gender`, `age`)
) COMMENT '用户表';

索引设计工具 ​

1. OceanBase 内置工具 ​

EXPLAIN 命令 ​

sql
-- 查看查询计划
EXPLAIN SELECT * FROM order WHERE user_id = 123 AND order_time > '2023-01-01';

-- 查看详细执行计划
EXPLAIN EXTENDED SELECT * FROM order WHERE user_id = 123 AND order_time > '2023-01-01';

索引建议工具 ​

sql
-- 查看索引建议
SELECT * FROM V$INDEX_ADVICE WHERE table_name = 'order';

2. 第三方工具 ​

  • OceanBase Developer Center (ODC)：提供可视化的索引设计和优化建议
  • SQL Advisor：自动分析 SQL 并提供索引优化建议
  • 性能监控工具：Prometheus + Grafana 监控索引使用情况

常见问题（FAQ） ​

Q1: 如何确定一个字段是否适合创建索引？ ​

A1: 可以通过以下方法判断：

  • 计算字段的选择性：选择性 = 不同值数量 / 总行数，选择性越高越适合创建索引
  • 分析查询频率：频繁作为查询条件的字段适合创建索引
  • 考虑查询类型：范围查询、排序、分组操作需要索引支持

Q2: 联合索引的字段顺序如何确定？ ​

A2: 联合索引的字段顺序应考虑以下因素：

  • 字段的选择性：选择性高的字段放在前面
  • 查询频率：频繁作为查询条件的字段放在前面
  • 查询场景：覆盖更多查询场景
  • 排序和分组需求：用于排序和分组的字段放在后面

Q3: 什么时候需要使用覆盖索引？ ​

A3: 当查询的所有字段都包含在索引中时，可以使用覆盖索引，避免回表查询。适合以下场景：

  • 频繁执行的查询，且查询字段较少
  • 大表查询，回表开销较大
  • 内存资源有限，需要减少 I/O 操作

Q4: 如何处理大表的索引维护问题？ ​

A4: 处理大表索引维护的方法：

  • 使用分区表，减少索引维护范围
  • 在业务低峰期进行索引维护操作
  • 使用 ALTER TABLE ... REBUILD INDEX 进行在线索引重建
  • 定期分析表统计信息，确保优化器选择正确的索引

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

A5: 可以通过以下方式监控索引使用情况：

  • 查询 V$INDEX_USAGE 视图查看索引使用次数和频率
  • 使用慢查询日志分析未使用索引的查询
  • 监控索引大小变化，及时清理无用索引
  • 使用 OceanBase 监控工具查看索引相关指标

Q6: 主键和唯一索引有什么区别？ ​

A6: 主键和唯一索引的主要区别：

  • 主键是表的唯一标识，一个表只能有一个主键
  • 主键字段不允许为 NULL，唯一索引字段允许为 NULL（最多一个）
  • 主键默认是聚簇索引，唯一索引默认是二级索引
  • 主键用于唯一标识记录，唯一索引用于保证字段唯一性

Q7: 如何优化频繁更新的索引字段？ ​

A7: 优化频繁更新索引字段的方法：

  • 避免在频繁更新的字段上创建索引
  • 考虑使用局部索引
  • 优化更新逻辑，减少更新频率
  • 考虑使用其他方式保证数据一致性

Q8: 索引越多性能越好吗？ ​

A8: 不是。索引越多，写入性能越差，因为每次写入都需要更新所有相关索引。索引设计应遵循以下原则：

  • 只创建必要的索引
  • 优先使用联合索引覆盖多个查询场景
  • 定期清理无用索引
  • 控制索引数量，大表建议不超过 10 个索引

Q9: 如何处理热点数据的索引问题？ ​

A9: 处理热点数据索引问题的方法：

  • 使用分区表分散热点
  • 考虑使用局部索引
  • 避免在热点字段上创建过多索引
  • 使用读写分离，将查询流量分散到只读节点

Q10: 如何选择分区表的分区键？ ​

A10: 选择分区表分区键的原则：

  • 数据分布均匀
  • 符合业务查询模式
  • 便于数据管理和维护
  • 考虑数据生命周期管理

常见的分区键选择：

  • 时间字段：适合时间序列数据
  • 哈希值：适合随机分布数据
  • 业务标识：适合按业务维度分区