PolarDB的自动索引推荐功能在复杂关联查询中的误判风险与人工审核流程建议

一、先搞懂啥是PolarDB的自动索引推荐？

不少做数据库优化的朋友都有过这种经历：线上业务跑着跑着，突然一个报表接口慢到超时，一查日志是几个表连起来查的SQL卡了。想加索引吧，得自己分析表结构、数据量、查询条件，万一加错了反而拖慢写操作，风险不小。PolarDB的自动索引推荐功能就是为了解决这个麻烦来的，它会盯着你数据库里跑的SQL，自动告诉你该给哪些表加什么索引，甚至能直接帮你创建，省了好多人工分析的功夫。

简单说，这个功能的核心逻辑就是：先收集一段时间内数据库里所有执行过的SQL（比如一天、一周），然后分析每个SQL的执行计划，找出那些因为没索引导致全表扫描、排序慢、关联慢的SQL，再结合表的数据分布（比如某个字段有多少个不同的值），生成最合适的索引建议。

不过要注意，这个功能是给PolarDB用户用的，PolarDB是阿里云推出的云原生关系型数据库，兼容MySQL、PostgreSQL这些常用的数据库协议，所以下面的例子我们就用MySQL兼容版的PolarDB来做，所有代码都是MySQL的语法。

二、复杂关联查询里的自动索引推荐，为啥会“瞎推荐”？

自动推荐听起来很智能，但遇到几个表连起来查的复杂场景，很容易出问题，也就是我们说的“误判”。误判不是说它完全错，而是推荐的索引要么没用，要么反而给数据库添负担，甚至引发性能问题。

2.1 啥样的查询算“复杂关联查询”？

先给大家举个真实业务里的例子，这个例子是某电商平台的订单报表查询，我们用MySQL语法来写，先建3张测试表，插点模拟数据，这样大家能更直观看到问题。

首先明确用的技术栈：PolarDB MySQL兼容版，MySQL 8.0语法。

第一步，建3张表：

-- 1. 用户表：存平台注册用户的基础信息
CREATE TABLE `user` (
  `user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户唯一ID',
  `phone` varchar(20) NOT NULL COMMENT '用户手机号',
  `register_time` datetime NOT NULL COMMENT '注册时间',
  PRIMARY KEY (`user_id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 2. 订单表：存用户的所有订单信息
CREATE TABLE `order` (
  `order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单唯一ID',
  `user_id` bigint NOT NULL COMMENT '下单用户ID',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `status` tinyint NOT NULL COMMENT '订单状态：1=待支付，2=已支付，3=已完成，4=已取消',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 3. 商品表：存订单对应的商品明细（一个订单可能有多个商品）
CREATE TABLE `order_item` (
  `item_id` bigint NOT NULL AUTO_INCREMENT COMMENT '明细唯一ID',
  `order_id` bigint NOT NULL COMMENT '所属订单ID',
  `sku_id` bigint NOT NULL COMMENT '商品SKU ID',
  `price` decimal(10,2) NOT NULL COMMENT '商品单价',
  `count` int NOT NULL COMMENT '购买数量',
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品明细表';

然后插点模拟数据，让表有一定的数据量，比如用户表插10万条，订单表插100万条，订单明细表插300万条：

-- 模拟用户表数据：10万条，手机号随机生成，注册时间随机分布
INSERT INTO `user` (`phone`, `register_time`)
SELECT 
  CONCAT('13', LPAD(FLOOR(RAND()*1000000000), 9, '0')),
  DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND()*365) DAY)
FROM seq_1_to_100000; -- seq_1_to_100000是PolarDB自带的序列函数，用来生成连续数字

-- 模拟订单表数据：100万条，用户ID随机关联用户表，时间随机
INSERT INTO `order` (`user_id`, `order_time`, `order_amount`, `status`)
SELECT 
  FLOOR(RAND()*100000)+1, -- 随机选1-10万的用户ID
  DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND()*365) DAY),
  ROUND(RAND()*1000, 2),
  FLOOR(RAND()*4)+1
FROM seq_1_to_1000000;

-- 模拟订单明细表数据：300万条，一个订单平均3个商品
INSERT INTO `order_item` (`order_id`, `sku_id`, `price`, `count`)
SELECT 
  FLOOR(RAND()*1000000)+1, -- 随机选1-100万的订单ID
  FLOOR(RAND()*100000)+1,
  ROUND(RAND()*1000, 2),
  FLOOR(RAND()*5)+1
FROM seq_1_to_3000000;

接下来是那个复杂的关联查询SQL，这个SQL是用来查“2023年下半年，已完成订单的用户，他们的手机号、下单时间、订单金额、购买的商品数量和总金额”，用来做月度经营分析的：

SELECT 
  u.phone, -- 用户手机号
  o.order_time, -- 下单时间
  o.order_amount, -- 订单金额
  COUNT(oi.item_id) AS product_count, -- 购买商品总数量
  SUM(oi.price * oi.count) AS total_product_amount -- 商品总金额
FROM `user` u
JOIN `order` o ON u.user_id = o.user_id -- 用户表连订单表
JOIN `order_item` oi ON o.order_id = oi.order_id -- 订单表连明细表
WHERE 
  o.order_time BETWEEN '2023-07-01' AND '2023-12-31' -- 限定时间范围
  AND o.status = 3 -- 限定已完成订单
GROUP BY u.user_id, o.order_id -- 按用户和订单分组
ORDER BY o.order_time DESC; -- 按时间倒序排序

这个SQL就是典型的复杂关联查询：3张表关联，有时间范围过滤、状态过滤，还有分组和排序。

2.2 自动推荐的索引，为啥会误判？

我们把这个SQL放到PolarDB的自动索引推荐里跑，它会给我们推荐3个索引，我们来逐个说问题：

2.2.1 推荐的索引和问题分析

PolarDB的自动索引推荐结果一般会显示在控制台，格式大概是这样的：

-- 推荐索引1：给订单表加联合索引 idx_order_time_status_user_id(order_time, status, user_id)
CREATE INDEX idx_order_time_status_user_id ON `order`(`order_time`, `status`, `user_id`);

-- 推荐索引2：给订单明细表加联合索引 idx_order_id_price_count(order_id, price, count)
CREATE INDEX idx_order_id_price_count ON `order_item`(`order_id`, `price`, `count`);

-- 推荐索引3：给用户表加联合索引 idx_user_id_phone(user_id, phone)
CREATE INDEX idx_user_id_phone ON `user`(`user_id`, `phone`);

咋一看这三个索引好像都对，每个索引都覆盖了查询里用到的字段，但实际加了之后会出大问题，我们来逐个拆解：

问题1：订单表的联合索引，顺序错了

推荐的索引是idx_order_time_status_user_id(order_time, status, user_id)，把order_time放在最前面。但我们的WHERE条件里，status是固定值3，order_time是范围查询（BETWEEN）。根据MySQL的联合索引规则，范围查询的字段必须放在最后，否则后面的字段会失效。

也就是说，这个索引的实际效果是：只有order_time会被用到，status和user_id根本用不上，相当于这个索引退化成了idx_order_time(order_time)，完全浪费了空间。

更合理的索引应该是把固定值的status放在最前面，然后是范围查询的order_time，最后是关联用的user_id，也就是idx_status_order_time_user_id(status, order_time, user_id)，这样三个字段都能用到。

问题2：订单明细表的索引，过度冗余

推荐的索引是idx_order_id_price_count(order_id, price, count)，这个索引的问题是：price和count是数值型字段，而且我们的SQL里是对这两个字段做乘法后求和，索引里存这两个字段完全没用，反而会让索引变得特别大。

因为我们的SQL是按order_id关联，只需要快速找到某个order_id对应的所有明细，然后拿到price和count计算就行，所以只需要给order_id加索引就够了，也就是idx_order_id(order_id)，完全不需要把price和count加到索引里。

问题3：用户表的索引，完全没必要

用户表的user_id已经是主键了，MySQL的主键索引本身就是最顶级的索引，关联的时候肯定会用到。而且我们的SQL里只需要拿到phone，主键索引里已经包含了所有字段，根本不需要再加idx_user_id_phone(user_id, phone)这个冗余索引。

2.2.2 误判的核心原因

为啥自动推荐会犯这些错误？本质上是它只考虑了“覆盖查询用到的字段”，没考虑三个关键点：

1. 没考虑联合索引的字段顺序规则：不知道固定值、范围查询、关联字段的顺序优先级；
2. 没考虑索引的空间成本：把不需要的字段加到索引里，会让索引变大，写操作（插入、更新、删除）的时候，需要更新的索引变多，速度变慢；
3. 没考虑主键的天然优势：不知道主键已经包含了所有字段，不需要额外加索引。

2.3 误判的后果有多严重？

可能有人觉得，不就是加了几个没用的索引吗？大不了删掉就是了，能有啥问题？其实后果比你想的严重：

1. 写操作变慢：订单表和明细表都是高频写的表（每天有大量订单插入），加了冗余索引后，每次插入订单，都要更新更多的索引，导致插入速度变慢，甚至出现数据库的写瓶颈；
2. 存储成本增加：索引是要占磁盘空间的，比如我们的订单明细表有300万条数据，加了那个冗余的索引，可能会多占几百MB甚至几GB的空间，长期下来存储成本会增加；
3. 优化器混乱：数据库的优化器在选执行计划的时候，会考虑所有的索引，冗余索引太多，优化器可能会选错索引，反而导致查询变慢。

三、怎么避免误判？得有一套人工审核流程

自动索引推荐不是不能用，而是不能直接用，必须经过人工审核。下面这套审核流程是我们在实际项目里总结出来的，覆盖了从拿到推荐结果到最终上线的全环节，适合大多数团队用。

3.1 第一步：先过滤无效推荐

拿到自动推荐的结果后，先做第一轮过滤，把明显没用的推荐直接删掉，不用浪费时间分析。过滤的规则有3个：

1. 主键相关的推荐：如果推荐的索引里包含主键，比如用户表的user_id是主键，推荐再加user_id的索引，直接删掉；
2. 已有索引的冗余推荐：如果推荐的索引和已有索引的前缀完全一样，比如已有索引是idx_order_time(order_time)，推荐的索引是idx_order_time_status(order_time, status)，那这个推荐是有用的，不用删；但如果已有索引是idx_order_time_status(order_time, status)，推荐的是idx_order_time(order_time)，直接删掉；
3. 单字段索引的冗余推荐：如果推荐的是单字段索引，而且这个字段已经在某个联合索引的前缀里，直接删掉，比如已有联合索引idx_status_order_time(status, order_time)，推荐的单字段索引idx_status(status)，直接删掉。

3.2 第二步：分析每个推荐的合理性

对过滤后剩下的推荐，逐个分析，分析的时候要抓住4个关键点：

关键点1：联合索引的字段顺序是否合理

这个是最容易出问题的地方，记住一个优先级规则：固定值条件 > 范围查询条件 > 关联条件 > 排序条件 > 分组条件。

我们拿刚才的订单表推荐索引来举例：

• WHERE条件里的status是固定值（3），优先级最高，应该放在最前面；
• order_time是范围查询（BETWEEN），优先级次之，放在status后面；
• user_id是关联条件，优先级再次之，放在order_time后面；
• 所以合理的顺序是status、order_time、user_id，而自动推荐的是order_time、status、user_id，顺序错了，这个推荐要调整。

关键点2：索引是否真的能用到

判断一个索引能不能用到，最简单的方法是看SQL的执行计划，也就是用EXPLAIN语句来分析。

比如我们把自动推荐的订单表索引加进去，然后执行EXPLAIN，看执行计划的key列（实际用到的索引）：

EXPLAIN SELECT ...; -- 就是刚才的关联查询SQL

如果key列显示的是idx_order_time_status_user_id，那说明这个索引真的用到了；如果key列显示的是idx_order_time或者其他索引，那说明这个推荐的索引没用到，是无效的。

关键点3：索引的空间成本是否可接受

索引是要占空间的，尤其是大表的联合索引，空间成本可能很高。判断空间成本的方法是，用PolarDB的information_schema库来查表的大小和索引的大小：

-- 查订单表的总大小（包括数据和索引）
SELECT 
  table_name,
  round(data_length/1024/1024, 2) AS data_size_mb, -- 数据大小，单位MB
  round(index_length/1024/1024, 2) AS index_size_mb -- 索引大小，单位MB
FROM information_schema.tables 
WHERE table_schema = '你的数据库名' AND table_name = 'order';

如果推荐的索引加进去后，索引大小会增加超过10%，那就要慎重考虑，尤其是写操作频繁的表，索引太大会严重影响写性能。

关键点4：写操作的影响是否可控

对写操作频繁的表（比如订单表、明细表），加索引前一定要评估对写操作的影响。评估的方法是，先在测试环境加索引，然后模拟真实的写操作（比如每秒插入1000条订单），看写操作的响应时间有没有明显增加。

如果测试环境里写操作的响应时间增加超过20%，那这个索引就要调整，比如把不必要的字段去掉，减少索引的大小。

3.3 第三步：测试验证

分析完后，不能直接上线，要先在测试环境验证，验证的内容有3个：

1. 查询性能验证：加了索引后，查询的响应时间有没有明显降低，比如原来要10秒的查询，加了索引后降到1秒以内；
2. 写性能验证：加了索引后，写操作的响应时间有没有明显增加，比如原来插入订单的响应时间是10ms，加了索引后变成15ms，这个是可以接受的；如果变成30ms，就要调整；
3. 业务影响验证：加了索引后，会不会影响其他业务的查询，比如原来某个查询用的是另一个索引，加了新索引后，优化器选错了索引，导致那个查询变慢。

3.4 第四步：灰度上线

验证通过后，也不能直接全量上线，要先灰度上线，比如先给10%的用户加索引，观察线上的性能变化，确认没问题后再全量上线。

灰度上线的好处是，如果加了索引后出现问题，可以快速回滚，不会影响所有用户。

四、适用场景和注意事项

4.1 自动索引推荐的适用场景

自动索引推荐不是所有场景都能用，适合用的场景有2个：

1. 新业务上线前的优化：新业务的SQL比较固定，数据量还不大，自动推荐的索引准确率比较高，人工审核后可以放心用；
2. 历史SQL的优化：比如对之前没优化过的老业务SQL，自动推荐可以快速找出潜在的索引问题，省了人工分析的时间。

4.2 注意事项

用自动索引推荐的时候，还要注意2个点：

1. 不要直接用自动创建索引的功能：自动创建索引是把推荐的索引直接加到线上，跳过了人工审核，风险很大，一定要关掉这个功能，自己手动加索引；
2. 定期清理冗余索引：自动推荐的索引加了之后，可能过一段时间业务变了，索引就没用了，所以要定期（比如每个季度）清理冗余索引，减少数据库的负担。

五、总结

PolarDB的自动索引推荐功能是个好工具，能帮我们省很多人工分析的功夫，但在复杂关联查询里，很容易出现误判，导致索引没用甚至影响性能。只要我们有一套规范的人工审核流程，先过滤无效推荐，再分析每个推荐的合理性，然后测试验证，最后灰度上线，就能把自动索引推荐的优势发挥出来，同时避免误判的风险。

最后要记住，自动工具只是辅助，不能完全替代人工判断，尤其是复杂的业务场景，还是要靠我们自己对业务和数据库的理解，才能做出最优的优化方案。