让全表扫描变索引查找的魔法

     在昨天的教程中，我们学会了如何诊断SQL性能问题，今天我们将深入实战，掌握索引优化的核心技能，让那些拖慢系统的全表扫描查询瞬间提速百倍！

为什么索引如此重要？

    在OceanBase中，索引是提升查询性能的最直接手段。没有索引的查询就像在图书馆里找一本书，需要逐本翻阅；而有了索引，就像使用了图书目录，可以直接定位到目标位置。

索引优化的核心价值

• 查询速度提升：从O(n)的全表扫描变为O(log n)的索引查找
• 资源消耗降低：减少CPU、I/O和内存的消耗
• 并发能力提升：更快的查询意味着更高的系统吞吐量

实战：优化订单查询性能

让我们以一个真实的业务场景为例，优化订单表的查询性能。

场景描述

我们的电商平台需要根据订单号快速查询订单详情，但随着数据量增长，查询越来越慢。

第一步：创建测试表

-- 创建订单表

CREATE TABLE t_order (

    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    order_no VARCHAR(32) NOT NULL,

    user_id BIGINT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,

    status TINYINT NOT NULL DEFAULT 0,

    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_user_id (user_id),

    INDEX idx_create_time (create_time)

) PARTITION BY HASH(id) PARTITIONS 8;

第二步：插入测试数据

-- 插入100万条测试数据

INSERT INTO t_order (order_no, user_id, amount, status)

SELECT

    CONCAT('ORD', LPAD(FLOOR(RAND() * 999999), 6, '0')),

    FLOOR(RAND() * 10000),

    ROUND(RAND() * 1000, 2),

    FLOOR(RAND() * 4)

FROM

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t4,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t5,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t6,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t7

LIMIT 1000000;

执行计划对比分析

❌ 优化前：全表扫描的惨痛代价

让我们先看看没有索引时的查询性能：

-- 查看执行计划

EXPLAIN SELECT * FROM t_order WHERE order_no = 'ORD123456';

执行计划输出：

=============================================================

|ID|OPERATOR  |NAME             |EST. ROWS|COST |

-------------------------------------------------------------

|0 |TABLE SCAN|t_order          |1000000  |128681|

=============================================================

Outputs & filters:

-------------------------------------

  0 - output([t_order.id], [t_order.order_no], ...),

      filter([t_order.order_no = 'ORD123456']),

      partitions(p0, p1, p2, p3, p4, p5, p6, p7)

性能分析：

• 全表扫描：需要扫描所有8个分区，共计100万行数据
• 高成本：COST值高达128681，查询效率极低
• 资源浪费：大量I/O和CPU资源被浪费在无用的数据扫描上

✅ 优化后：索引查找的优雅解决方案

现在我们为订单号字段创建唯一索引：

-- 创建唯一索引

CREATE UNIQUE INDEX uk_order_no ON t_order(order_no);

再次查看执行计划：

-- 查看优化后的执行计划

EXPLAIN SELECT * FROM t_order WHERE order_no = 'ORD123456';

执行计划输出：

=============================================================

|ID|OPERATOR  |NAME             |EST. ROWS|COST |

-------------------------------------------------------------

|0 |TABLE GET |t_order(uk_order_no)|1      |85   |

=============================================================

Outputs & filters:

-------------------------------------

  0 - output([t_order.id], [t_order.order_no], ...),

      filter(nil),

      access([t_order.order_no]),

      range_key([t_order.order_no]),

      range_cond([t_order.order_no = 'ORD123456'])

性能提升分析：

• 精准定位：直接通过索引定位到目标数据，无需扫描
• 成本骤降：COST值从128681降至85，性能提升1500倍
• 资源节约：I/O和CPU消耗几乎可以忽略不计

索引优化的最佳实践

1. 索引创建原则

选择性原则：优先为选择性高的列创建索引

• 选择性 = 不重复值数量 / 总行数
• 选择性越接近1，索引效果越好

最左前缀原则：复合索引要遵循最左前缀匹配

-- 复合索引创建示例

CREATE INDEX idx_user_status_time ON t_order(user_id, status, create_time);

-- 以下查询能用到索引

SELECT * FROM t_order WHERE user_id = 1001;

SELECT * FROM t_order WHERE user_id = 1001 AND status = 1;

SELECT * FROM t_order WHERE user_id = 1001 AND status = 1 AND create_time > '2024-01-01';

-- 以下查询不能用到索引

SELECT * FROM t_order WHERE status = 1; -- 跳过了最左列

2. 索引维护策略

定期分析统计信息：

-- 收集表统计信息

ANALYZE TABLE t_order;

-- 查看统计信息

SELECT * FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_ORDER';

监控索引使用情况：

-- 查看索引使用统计

SELECT * FROM oceanbase.GV$OB_INDEX_USAGE WHERE table_name = 'T_ORDER';

3. 避坑指南

避免过度索引：

• 每个额外索引都会增加写操作的开销
• 平衡读写性能，只创建必要的索引

注意隐式类型转换：

-- 错误示例：字符串字段与数字比较

SELECT * FROM t_order WHERE order_no = 123456; -- 可能导致索引失效

-- 正确写法

SELECT * FROM t_order WHERE order_no = '123456';

高级索引技术

1. 覆盖索引优化

当查询的所有字段都在索引中时，可以避免回表查询：

-- 创建覆盖索引

CREATE INDEX idx_user_amount ON t_order(user_id, amount);

-- 以下查询可以直接从索引获取数据，无需回表

EXPLAIN SELECT user_id, amount FROM t_order WHERE user_id = 1001;

2. 分区索引优化

对于分区表，合理设计本地索引：

-- 创建本地分区索引

CREATE INDEX idx_status_time ON t_order(status, create_time) LOCAL;

性能验证与监控

1. 实际性能测试

-- 测试优化前查询耗时

SELECT SQL_NO_CACHE * FROM t_order WHERE order_no = 'ORD123456';

-- 测试优化后查询耗时

-- 对比两次查询的执行时间

2. 慢查询监控

-- 查看慢查询日志

SELECT * FROM oceanbase.GV$OB_SQL_AUDIT

WHERE tenant_name = 'my_tenant'

  AND elapsed_time > 1000000 -- 耗时超过1秒

ORDER BY elapsed_time DESC;

今日小结

今天我们通过实战演练，掌握了OceanBase索引优化的核心技能：

1. 识别性能瓶颈：通过执行计划分析找出全表扫描问题
2. 创建有效索引：为高频查询字段创建合适的索引
3. 验证优化效果：对比执行计划，量化性能提升
4. 建立监控机制：持续监控索引使用情况和查询性能

关键收获：一个正确的索引可以让查询性能提升数百倍，但错误的索引设计反而会拖慢系统。掌握索引优化的"道"与"术"，是成为优秀DBA的必经之路！

课后思考

1. 如果一个查询条件中有多个字段，如何决定创建单列索引还是复合索引？
2. 索引会占用额外的存储空间，如何在查询性能和存储成本之间找到平衡点？
3. 对于频繁更新的表，索引维护的开销应该如何评估？