OceanBase教程FG169-OceanBase全局索引本地索引
本文档风哥主要介绍OceanBase数据库的全局索引和本地索引，包括它们的基本概念、创建原则、使用场景和优化策略等。风哥教程参考OceanBase官方文档OceanBase索引管理指南、OceanBase分区表管理指南等。
通过本文的学习，您将了解如何创建和使用全局索引与本地索引，以提高分区表的查询性能。
目录大纲
Part01-基础概念与理论知识
1.1 全局索引基本概念
全局索引是指索引数据独立于分区表数据，具有以下特点：
- 索引数据独立：全局索引的数据存储在独立的分区中
- 跨分区查询：全局索引支持跨分区的高效查询
- 索引维护成本高：数据修改时需要维护全局索引
- 查询性能好：对于跨分区查询，全局索引性能更好
1.2 本地索引基本概念
本地索引是指索引数据与分区表数据存储在同一分区中，具有以下特点：
- 索引数据与分区数据同构：本地索引的分区方式与表的分区方式相同
- 维护成本低：数据修改时只需要维护对应分区的索引
- 查询性能受限：对于跨分区查询，本地索引需要扫描多个分区
- 适合局部查询：对于单个分区内的查询，本地索引性能更好
1.3 全局索引与本地索引对比
全局索引与本地索引的对比：
- 存储方式：全局索引独立存储，本地索引与表数据同分区存储
- 维护成本：全局索引维护成本高，本地索引维护成本低
- 查询性能：全局索引适合跨分区查询，本地索引适合局部查询
- 空间开销：全局索引空间开销较大，本地索引空间开销较小
- 适用场景：全局索引适合跨分区查询频繁的场景，本地索引适合局部查询频繁的场景
风哥提示：全局索引和本地索引各有优缺点，需要根据具体的业务场景选择合适的索引类型
Part02-生产环境规划与建议
2.1 全局索引设计建议
全局索引设计的建议：
- 适合场景：跨分区查询频繁的场景
- 索引列选择：选择查询频率高的列作为全局索引
- 索引数量：全局索引数量不宜过多，一般不超过3个
- 维护成本：考虑全局索引的维护成本，避免频繁修改数据
2.2 本地索引设计建议
本地索引设计的建议：
- 适合场景：局部查询频繁的场景
- 索引列选择：选择分区键或与分区键相关的列作为本地索引
- 索引数量：本地索引数量可以适当增加
- 维护成本：本地索引维护成本低，适合频繁修改数据的场景
2.3 索引选择策略
索引选择的策略：
- 根据查询模式选择：跨分区查询选择全局索引，局部查询选择本地索引
- 根据数据修改频率选择：数据修改频繁选择本地索引，数据修改不频繁选择全局索引
- 根据数据量选择：数据量大选择本地索引，数据量小选择全局索引
- 根据业务需求选择：根据具体的业务需求选择合适的索引类型
，风哥提示：。
Part03-生产环境项目实施方案
3.1 全局索引创建
创建全局索引的方法：
# 1. 创建分区表
CREATE TABLE fgedu_order (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (order_id)
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
，学习交流加群风哥微信: itpux-com。
Query OK, 0 rows affected (0.12 sec)
# 2. 创建全局索引
-- 创建全局索引
CREATE GLOBAL INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
-- 创建唯一全局索引
CREATE UNIQUE GLOBAL INDEX idx_fgedu_order_order_id ON fgedu_order(order_id);
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
3.2 本地索引创建
创建本地索引的方法：
# 1. 创建分区表
CREATE TABLE fgedu_order (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (order_id)
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
，学习交流加群风哥QQ113257174。
Query OK, 0 rows affected (0.12 sec)
# 2. 创建本地索引
-- 创建本地索引
CREATE LOCAL INDEX idx_fgedu_order_status ON fgedu_order(status);
-- 创建唯一本地索引
CREATE UNIQUE LOCAL INDEX idx_fgedu_order_product_id ON fgedu_order(product_id);
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
3.3 索引维护
索引维护的方法：
# 1. 查看索引信息
SHOW INDEX FROM fgedu_order;
+————-+————+——————————-+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+————-+————+——————————-+————–+————-+———–+————-+———-+——–+——+————+———+
| fgedu_order | 0 | PRIMARY | 1 | order_id | A | 0 | NULL | NULL | | BTREE | |
| fgedu_order | 1 | idx_fgedu_order_user_id | 1 | user_id | A | 0 | NULL | NULL | | BTREE | GLOBAL |
| fgedu_order | 0 | idx_fgedu_order_order_id | 1 | order_id | A | 0 | NULL | NULL | | BTREE | GLOBAL |
| fgedu_order | 1 | idx_fgedu_order_status | 1 | status | A | 0 | NULL | NULL | | BTREE | LOCAL |
| fgedu_order | 0 | idx_fgedu_order_product_id | 1 | product_id | A | 0 | NULL | NULL | | BTREE | LOCAL |
+————-+————+——————————-+————–+————-+———–+————-+———-+——–+——+————+———+
，更多视频教程www.fgedu.net.cn。
# 2. 删除索引
-- 删除全局索引
DROP INDEX idx_fgedu_order_user_id ON fgedu_order;
-- 删除本地索引
DROP INDEX idx_fgedu_order_status ON fgedu_order;
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.08 sec)
Part04-生产案例与实战讲解
4.1 全局索引使用实战
全局索引使用的实战案例：
场景描述
某电商系统的订单表按年份分区，需要根据用户ID跨分区查询订单。
实施步骤
- 创建分区表
- 创建全局索引
- 执行跨分区查询
- 验证查询性能
# 1. 创建分区表
CREATE TABLE fgedu_order (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,更多学习教程公众号风哥教程itpux_com。
product_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (order_id)
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
Query OK, 0 rows affected (0.12 sec)
# 2. 创建全局索引
CREATE GLOBAL INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
Query OK, 0 rows affected (0.08 sec)
# 3. 插入测试数据
-- 插入2023年数据，from DB视频:www.itpux.com。
INSERT INTO fgedu_order (order_id, user_id, product_id, amount, create_time, status)
VALUES (1, 100, 1001, 100.00, '2023-01-01 10:00:00', 'COMPLETED');
-- 插入2024年数据
INSERT INTO fgedu_order (order_id, user_id, product_id, amount, create_time, status)
VALUES (2, 100, 1002, 200.00, '2024-01-01 10:00:00', 'COMPLETED');
-- 插入2025年数据
INSERT INTO fgedu_order (order_id, user_id, product_id, amount, create_time, status)
VALUES (3, 100, 1003, 300.00, '2025-01-01 10:00:00', 'COMPLETED');
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
# 4. 执行跨分区查询
SELECT * FROM fgedu_order WHERE user_id = 100;
+———-+———+————+——–+———————+———–+
| order_id | user_id | product_id | amount | create_time | status |
+———-+———+————+——–+———————+———–+
| 1 | 100 | 1001 | 100.00 | 2023-01-01 10:00:00 | COMPLETED |
| 2 | 100 | 1002 | 200.00 | 2024-01-01 10:00:00 | COMPLETED |
| 3 | 100 | 1003 | 300.00 | 2025-01-01 10:00:00 | COMPLETED |
+———-+———+————+——–+———————+———–+
# 5. 验证查询性能
EXPLAIN SELECT * FROM fgedu_order WHERE user_id = 100;
+—-+————-+————-+————+——+—————————+—————————+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————————+—————————+———+——-+——+———-+——-+
| 1 | SIMPLE | fgedu_order | NULL | ref | idx_fgedu_order_user_id | idx_fgedu_order_user_id | 8 | const | 3 | 100.00 | NULL |
+—-+————-+————-+————+——+—————————+—————————+———+——-+——+———-+——-+
4.2 本地索引使用实战
本地索引使用的实战案例：
场景描述
某电商系统的订单表按年份分区，需要根据订单状态在单个分区内查询订单。
实施步骤
- 创建分区表
- 创建本地索引
- 执行局部查询
- 验证查询性能
# 1. 创建分区表
CREATE TABLE fgedu_order (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (order_id)
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
Query OK, 0 rows affected (0.12 sec)
# 2. 创建本地索引
CREATE LOCAL INDEX idx_fgedu_order_status ON fgedu_order(status);
Query OK, 0 rows affected (0.08 sec)
# 3. 插入测试数据
-- 插入2023年数据
INSERT INTO fgedu_order (order_id, user_id, product_id, amount, create_time, status)
VALUES (1, 100, 1001, 100.00, '2023-01-01 10:00:00', 'COMPLETED');
INSERT INTO fgedu_order (order_id, user_id, product_id, amount, create_time, status)
VALUES (2, 101, 1002, 200.00, '2023-01-02 10:00:00', 'PENDING');
-- 插入2024年数据
INSERT INTO fgedu_order (order_id, user_id, product_id, amount, create_time, status)
VALUES (3, 102, 1003, 300.00, '2024-01-01 10:00:00', 'COMPLETED');
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
# 4. 执行局部查询
SELECT * FROM fgedu_order WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' AND status = 'COMPLETED';
+———-+———+————+——–+———————+———–+
| order_id | user_id | product_id | amount | create_time | status |
+———-+———+————+——–+———————+———–+
| 1 | 100 | 1001 | 100.00 | 2023-01-01 10:00:00 | COMPLETED |
+———-+———+————+——–+———————+———–+
# 5. 验证查询性能
EXPLAIN SELECT * FROM fgedu_order WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' AND status = 'COMPLETED';
+—-+————-+————-+————+——+—————————+—————————+———+——-+——+———-+————————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————————+—————————+———+——-+——+———-+————————–+
| 1 | SIMPLE | fgedu_order | p2023 | ref | idx_fgedu_order_status | idx_fgedu_order_status | 82 | const | 1 | 100.00 | Using index condition; Using where |
+—-+————-+————-+————+——+—————————+—————————+———+——-+——+———-+————————–+
Part05-风哥经验总结与分享
5.1 全局索引与本地索引最佳实践
OceanBase全局索引与本地索引的最佳实践：
- 根据查询模式选择：跨分区查询选择全局索引，局部查询选择本地索引
- 根据数据修改频率选择：数据修改频繁选择本地索引，数据修改不频繁选择全局索引
- 根据数据量选择：数据量大选择本地索引，数据量小选择全局索引
- 合理设计索引：根据业务需求合理设计索引，避免过度使用索引
- 定期维护：定期检查索引使用情况，优化索引
- 测试验证：在生产环境之前，测试索引的效果
5.2 常见问题与解决方案
全局索引与本地索引使用中常见的问题与解决方案：
# 1. 全局索引维护成本高
- 症状：数据修改时性能下降
- 解决方案：减少全局索引数量，或选择本地索引
# 2. 本地索引跨分区查询性能差
- 症状：跨分区查询速度慢
- 解决方案：对于频繁的跨分区查询，使用全局索引
# 3. 索引选择不当
- 症状：查询性能不佳
- 解决方案：根据查询模式和业务需求选择合适的索引类型
# 4. 索引过多
- 症状：存储空间占用大，维护成本高
- 解决方案：删除不必要的索引，只保留必要的索引
# 5. 索引失效
- 症状：查询时索引失效
- 解决方案：优化查询语句，确保使用索引
风哥提示：全局索引和本地索引各有优缺点，需要根据具体的业务场景选择合适的索引类型
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
