CodeBuddy助力MySQL索引优化：航道管理订单查询SQL性能提升实战

友友们，早上好啊。今天继续AI工具实战系列。我将分享最近几个月使用ai工具用于工作的那些事。既是一次知识的分享，又是一次自我的一次总结，也希望自己的一些使用经验可以帮助到大家。下面正文开始。

一、问题提出

上个月，我们团队遇到了一个问题。就是在某个项目的航道管理信息系统中，"查询特定船舶近30天航行记录"是核心功能之一。但是在系统升级后，该查询性能显著下降：

比如这样的：

SELECT * FROM navigation_orders WHERE vessel_id = ? AND entry_time >= ?

在MySQL 环境下，该查询执行耗时稳定在1.1-1.3秒，远超业务要求的300ms响应阈值。作为技术团队，我们需要在快速完成性能优化，确保系统能够支撑即将到来的汛期航道监控高峰。

二、技术环境

• 数据库环境：MySQL （InnoDB引擎）
• 表结构：

CREATE TABLE navigation_orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    vessel_id VARCHAR(20) NOT NULL COMMENT '船舶识别号',
    entry_time DATETIME NOT NULL COMMENT '进入航道时间',
    exit_time DATETIME COMMENT '离开航道时间',
    cargo_type VARCHAR(50) COMMENT '货物类型',
    tonnage DECIMAL(12,2) COMMENT '载重量(吨)',
    -- 其他航道管理相关字段...
    INDEX idx_vessel (vessel_id)  -- 原有单字段索引
) ENGINE=InnoDB COMMENT='航道通行记录表';

• 数据规模：120万条航行记录（含历史3年数据）
• 性能瓶颈：汛期单日新增记录超5000条，全表扫描导致I/O压力剧增

三、优化过程

阶段1：初始诊断与索引优化

首先我向CodeBuddy提交完整优化请求：

"navigation_orders表包含字段order_id(主键)、vessel_id(varchar)、entry_time(datetime)、tonnage(decimal)等，当前数据量120万条。执行SQL：SELECT * FROM navigation_orders WHERE vessel_id = 'VESSEL123' AND entry_time >= '2024-06-01'耗时1.2秒，如何优化？"

提出问题

AI分析结果：

执行计划分析：

- 当前使用PRIMARY索引（全表扫描）

- 缺少复合索引(vessel_id, entry_time)

- SELECT *导致不必要的数据传输

- 船舶识别号字段类型建议优化

优化建议：

1. 创建复合索引：

CREATE INDEX idx_vessel_entry ON navigation_orders(vessel_id, entry_time);

2. 考虑将vessel_id改为CHAR(12)（若格式固定）

3. 后续可考虑字段筛选优化

实施效果： 我们在执行索引创建后，查询耗时从1.2秒降至430ms。通过EXPLAIN分析可见查询已使用新索引，但扫描行数仍较高（约8000行）。这还是无法达到我们的预期要求。

阶段2：SQL精简优化

继续向CodeBuddy提问：

"创建联合索引后耗时430ms，如何进一步优化？"

继续提问

AI建议：

优化建议：

- 避免使用SELECT *，明确指定业务所需字段

- 优先查询索引覆盖字段

- 示例优化SQL：

SELECT order_id, vessel_id, entry_time, exit_time, tonnage

FROM navigation_orders

WHERE vessel_id = ? AND entry_time >= ?

- 考虑添加EXIT_TIME索引（如高频查询离港时间）

实施效果： 修改后的SQL执行耗时降至270ms，满足业务要求。进一步验证发现：

• 数据传输量减少约55%
• 查询类型变为range扫描
• 使用Using index覆盖索引特性

这样操作后，整体的查询确实快多了。

四、优化效果验证

性能对比

执行计划分析

优化前后EXPLAIN结果对比：

-- 优化前
EXPLAIN SELECT * FROM navigation_orders 
WHERE vessel_id = 'VESSEL123' AND entry_time >= '2024-06-01';
-- type: ALL (全表扫描)
-- rows: 1200000

-- 优化后
EXPLAIN SELECT order_id, vessel_id, entry_time, exit_time, tonnage 
FROM navigation_orders 
WHERE vessel_id = 'VESSEL123' AND entry_time >= '2024-06-01';
-- type: range (范围扫描)
-- key: idx_vessel_entry
-- rows: 110 (预估行数大幅减少)

后续优化方向

虽然当前查询已满足性能要求，但考虑未来数据增长（预计年增50万条），我们还会可进一步优化比如：

1. 实现查询结果缓存（针对高频查询船舶）
2. 对历史数据（超过2年）进行归档处理
3. 添加(vessel_id, exit_time)复合索引（支持离港时间查询）
4. 定期分析索引使用率（使用pt-index-usage工具）

这些优化也是后期项目中必须增加的。

好了，今天的分享就到这里。友友们，我们下篇见咯！