大表聚合全字段索引优化

单表 300 多万条数据，单台账 30 多万条数据。对 30 多万条数据执行多字段聚合查询，耗时约 25 秒，查询命中了台账 ID 索引；如果没有索引、执行全表扫描，则耗时超过 50 秒。

以下分多种情况，使用 EXPLAIN ANALYZE 获取执行信息并进行分析。

全字段索引

高负载下花费时间：6 秒

低负载下实际花费时间：0.495 秒

使用情况：必须强制声明索引，并且 GROUP BY 字段顺序需要与索引顺序一致。

说明：由于索引过长，优化器通常不会主动选择该索引，只能通过强制声明使用。 另外，GROUP BY 字段顺序如果与索引顺序不一致，同样会产生临时表。

-> Group aggregate: min(dcc_enterprise_quota_lmc_copy1.id), sum(dcc_enterprise_quota_lmc_copy1.quantity), sum(dcc_enterprise_quota_lmc_copy1.total_price_ex_tax), sum(dcc_enterprise_quota_lmc_copy1.total_price_in_tax)  (cost=284547 rows=151239) (actual time=0.143..4010 rows=12281 loops=1)
    -> Filter: ((dcc_enterprise_quota_lmc_copy1.ledger_id = 2064266180383657987) and (dcc_enterprise_quota_lmc_copy1.del_flag = 0) and (dcc_enterprise_quota_lmc_copy1.`type` in ('人工','主材','半成品','商品砼','沥青砼','周转材料','辅材','设备','材料','机械','专业分包')))  (cost=227638 rows=569093) (actual time=0.122..2629 rows=307520 loops=1)
        -> Covering index range scan on dcc_enterprise_quota_lmc_copy1 using idx_quota_lmc_ledger_type over (del_flag = 0 AND ledger_id = 2064266180383657987 AND type = '专业分包') OR (del_flag = 0 AND ledger_id = 2064266180383657987 AND type = '主材') OR (9 more)  (cost=227638 rows=569093) (actual time=0.119..1962 rows=307520 loops=1)

全字段索引：WHERE 条件不在索引最前

高负载下花费时间：53 秒

低负载下实际花费时间：4.97 秒

-> Table scan on <temporary>  (actual time=53024..53104 rows=12281 loops=1)
    -> Aggregate using temporary table  (actual time=53024..53024 rows=12281 loops=1)
        -> Filter: ((dcc_enterprise_quota_lmc_copy1.del_flag = 0) and (dcc_enterprise_quota_lmc_copy1.ledger_id = 2064266180383657987) and (dcc_enterprise_quota_lmc_copy1.`type` in ('人工','主材','半成品','商品砼','沥青砼','周转材料','辅材','设备','材料','机械','专业分包')))  (cost=4.32e+6 rows=397) (actual time=0.216..24784 rows=307520 loops=1)
            -> Covering index scan on dcc_enterprise_quota_lmc_copy1 using idx_quota_lmc_ledger_type1  (cost=4.32e+6 rows=3.93e+6) (actual time=0.209..21657 rows=3.96e+6 loops=1)

全字段索引：GROUP BY 与索引顺序不一致

高负载下花费时间：24 秒

低负载下实际花费时间：2.485 秒

-> Table scan on <temporary>  (actual time=23618..23716 rows=12281 loops=1)
    -> Aggregate using temporary table  (actual time=23618..23618 rows=12281 loops=1)
        -> Filter: ((dcc_enterprise_quota_lmc_copy1.del_flag = 0) and (dcc_enterprise_quota_lmc_copy1.ledger_id = 2064266180383657987) and (dcc_enterprise_quota_lmc_copy1.`type` in ('人工','主材','半成品','商品砼','沥青砼','周转材料','辅材','设备','材料','机械','专业分包')))  (cost=227638 rows=569093) (actual time=0.14..3160 rows=307520 loops=1)
            -> Covering index range scan on dcc_enterprise_quota_lmc_copy1 using idx_quota_lmc_ledger_type over (del_flag = 0 AND ledger_id = 2064266180383657987 AND type = '专业分包') OR (del_flag = 0 AND ledger_id = 2064266180383657987 AND type = '主材') OR (9 more)  (cost=227638 rows=569093) (actual time=0.137..2019 rows=307520 loops=1)

条件及分组字段索引：不包含部分 SELECT 字段

高负载下花费时间：18 秒

低负载下实际花费时间：1.2 秒

说明：从执行计划来看整体表现不错，查询命中了索引，也没有生成临时表， 但是由于索引不包含部分查询字段，需要进行大量回表，回表耗时仍然比较明显。

-> Group aggregate: min(dcc_enterprise_quota_lmc_copy1.id), sum(dcc_enterprise_quota_lmc_copy1.quantity), sum(dcc_enterprise_quota_lmc_copy1.total_price_ex_tax), sum(dcc_enterprise_quota_lmc_copy1.total_price_in_tax)  (cost=734761 rows=150308) (actual time=0.78..18221 rows=12281 loops=1)
    -> Index range scan on dcc_enterprise_quota_lmc_copy1 using idx_quota_lmc_ledger_type_c1 over (del_flag = 0 AND ledger_id = 2064266180383657987 AND type = '专业分包') OR (del_flag = 0 AND ledger_id = 2064266180383657987 AND type = '主材') OR (9 more), with index condition: ((dcc_enterprise_quota_lmc_copy1.del_flag = 0) and (dcc_enterprise_quota_lmc_copy1.ledger_id = 2064266180383657987) and (dcc_enterprise_quota_lmc_copy1.`type` in ('人工','主材','半成品','商品砼','沥青砼','周转材料','辅材','设备','材料','机械','专业分包')))  (cost=678241 rows=565199) (actual time=0.747..16580 rows=307520 loops=1)

注意事项

1. 长复合索引体积较大，优化器评估成本较高时，可能不会主动选择该索引，需要根据实际执行计划判断是否使用 FORCE INDEX。 如果表和索引建立时间较长、统计信息已经不准确，可以执行：

   ANALYZE TABLE {tableName};

   更新统计信息，辅助优化器重新评估执行计划。
2. GROUP BY 字段顺序需要与联合索引中对应字段的顺序一致， 否则通常无法直接利用索引顺序完成聚合，可能生成临时表，查询速度会明显下降。
3. 用于过滤的 WHERE 条件字段应放在联合索引前部， 用于 GROUP BY 的字段跟在后面。 查询执行时先进行数据过滤，再进行分组；如果过滤字段不在合适的索引前导位置， 可能退化为较大范围的索引扫描，甚至扫描整个索引。
4. 本次测试场景中的过滤条件主要为等值查询和 IN 查询。 范围查询字段会影响联合索引后续字段的利用能力，需要结合实际查询条件和执行计划单独设计索引。

测试环境说明

以上“花费时间”为数据库处于高负载状态时的查询时间， “实际花费时间”为数据库处于低负载状态时的查询时间。 两种情况下查询 SQL 和索引命中情况一致。

优化过程中发现，有一个存储过程存在明显性能问题。 多个流程正在等待相关表锁，同时存储过程还在大批量执行低效查询， 最终导致数据库整体负载升高，服务处理性能明显下降。

高负载下的查询时间同样具有一定参考意义。 当数据量继续按倍数增长时，可以使用该时间评估查询在高负载环境中的性能上限。 从本次测试结果来看，不同索引方案之间的优化比例，在高负载和低负载情况下整体基本一致。

表锁与事务排查

首先查看当前元数据锁情况，并获取相关线程 ID：

SELECT *
FROM performance_schema.metadata_locks;

获取相关的 trx_mysql_thread_id 后，再查询事务状态和未提交持续时间：

SELECT
    trx_mysql_thread_id,
    trx_started,
    trx_state,
    trx_query
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id IN (xxx);

这个问题是在创建索引时发现的。 之前是在复制表上进行测试，索引能够正常创建； 后续在原表上进行测试时，索引创建执行了两个小时仍然没有完成， 最终通过排查锁和事务状态发现了上述问题。

AI 挑毛病

一、原说法中关于 ANALYZE TABLE 的问题

原说法：

长复合索引优化器可能会优先排除，需要强制声明，或者 ANALYZE TABLE {tableName}。

1. ANALYZE TABLE 的作用定位不准确

ANALYZE TABLE 主要用于更新表和索引统计信息， 帮助优化器进行更准确的成本估算，并不能强制优化器选择长复合索引。

• 统计信息过时，可能导致优化器误判索引成本，选择不合适的执行计划。
• 即使统计信息已经更新，超长联合索引仍可能因为索引体积大、I/O 成本高而被优化器放弃。

2. FORCE INDEX 与 ANALYZE TABLE 不是等价方案

• FORCE INDEX 是直接指定查询使用某个索引。
• ANALYZE TABLE 是更新统计信息，辅助优化器重新计算成本。

更准确的表述为：

长复合索引因索引体积较大，优化器评估其执行成本较高时，可能主动放弃使用。 可以通过 FORCE INDEX 强制指定索引； 如果是因为统计信息陈旧导致优化器误判索引优劣， 可以执行 ANALYZE TABLE {tableName} 更新统计信息， 辅助优化器重新评估执行计划。

二、原文整体存在的问题

1. 索引顺序与执行逻辑表述存在边界问题

原文：

WHERE 条件的字段必须在索引前面，GROUP BY 跟在后面， 执行是先过滤再分组，顺序不对会引发索引内的全数据扫描。

该说法没有区分等值条件字段和范围条件字段。 联合索引通常应优先放置等值过滤字段，范围条件字段会影响其后续字段继续参与索引定位。

同时，也不是所有字段顺序不匹配都会直接退化为整个索引扫描。 如果索引前部仍然存在可用的等值字段，查询可能只是扫描范围扩大，不一定完全退化为索引全扫描。

2. GROUP BY 产生临时表的解释不完整

原文：

GROUP BY 的顺序必须与索引顺序一致，否则会产生临时表。

当索引能够按照分组字段顺序提供有序数据时，MySQL 可以直接进行流式聚合， 从而避免临时表。

但除了字段顺序外，函数计算、表达式、隐式类型转换等情况， 也可能导致 MySQL 无法直接使用索引顺序完成分组。

3. 覆盖索引与回表场景描述较少

原文主要说明了索引缺少部分 SELECT 字段时回表耗时较高， 但没有进一步区分覆盖索引和普通联合索引。

• 覆盖索引包含查询需要的过滤字段、分组字段和聚合字段，可以避免回表。
• 普通联合索引如果缺少聚合字段，需要根据索引记录中的主键再次访问聚簇索引，产生大量随机 I/O。

4. 范围查询规则表述不准确

原文：

WHERE 条件只支持精确查询，范围查询字段会使索引失效， 除非把该字段加入 GROUP BY。

更准确的说法是：

• 范围查询字段本身仍然可以使用索引进行范围扫描。
• 范围字段之后的联合索引字段，通常无法继续用于缩小扫描范围。
• 把范围字段加入 GROUP BY，并不能恢复后续索引字段的过滤能力。

例如，联合索引为 (a, b, c)：

WHERE a = 1
  AND b > 10

其中 a 可以用于等值定位，b 可以用于范围扫描， 但 c 通常不能继续用于缩小扫描范围。

5. 缺少 FORCE INDEX 和长复合索引的风险说明

FORCE INDEX 会限制优化器根据数据分布变化动态选择执行计划。 当数据量、数据分布或业务条件变化后，被强制指定的索引可能不再是最优方案。

长复合索引还会带来较高的存储空间和写入维护成本。 数据执行插入、更新和删除时，需要同步维护更大的索引结构。

6. 测试结论存在适用范围

本次测试基于固定台账、固定数据规模和固定的 type 集合。 当筛选后的数据量明显减少，或者 IN 条件中的枚举数量变化时， 不同索引方案之间的性能差异也可能发生变化。

三、AI 给出的核心问题总结

1. 知识点错误：范围查询导致联合索引失效的描述不准确。
2. 方案混淆：混淆了 ANALYZE TABLE 和 FORCE INDEX 的作用。
3. 逻辑片面：只强调索引字段顺序，没有说明其他影响执行计划的因素。
4. 实操缺失：没有说明长索引和强制索引对写入及长期维护的影响。
5. 概念模糊：没有清晰区分覆盖索引、回表、索引全扫描和临时表。

个人补充

怪讨厌的。有些内容，例如隐式转换、范围字段、数据量变化，以及索引本身的优缺点， 都属于索引优化中的基础常识，本来就不是这次讨论的重点。 让 AI 挑毛病，它还真把这些全部列出来了。

不过关于 FORCE INDEX 的弊端，AI 说得没有问题。 后续在主表上重新测试时，优化器已经能够自动选择覆盖索引， 有可能是执行 ANALYZE TABLE 更新统计信息后起了作用。 所以能不使用强制索引，还是尽量不要使用。

另外，“知识点错误”和“方案混淆”实际上存在一定重复。 范围查询的描述属于知识点错误，而将 ANALYZE TABLE 和 FORCE INDEX 当作类似方案，既可以归类为知识点错误，也可以归类为方案混淆。 如果作为文章总结，合并成一项会更加简洁。

如果不是原查询性能实在太差，我也不想使用覆盖索引。 覆盖索引的字段冗余较大，会明显增加存储空间和写入维护成本。

此外，这张表中还有一个 lmc_standard 字段， 该字段内容可能很长，加入联合索引后存在索引长度超限的风险。 如果索引长度超过数据库限制，创建索引时通常会直接报错，此时就必须重新设计索引。

如果需要保留 lmc_standard 字段，只能考虑使用前缀索引， 即只截取字段前面一定长度的内容加入索引。

但前缀索引只能保存字段前缀，无法完整覆盖原字段。 查询仍然需要回表读取完整的 lmc_standard 内容， 这样一来，覆盖索引的意义就会被明显削弱。

因此，与其维护一个体积很大、存在长度风险且最终仍可能需要回表的覆盖索引， 不如退一步，选择若干区分度较高的过滤字段和分组字段建立相对精简的联合索引， 在索引大小、查询效率、写入成本和维护风险之间取得平衡。

执行计划补充：怎么看 EXPLAIN ANALYZE

一、先分清「优化器估算」和「实际执行」

EXPLAIN ANALYZE 中，一个算子的括号通常可以看到两组信息：

(cost=2042 rows=0.489)
(actual time=190..193 rows=125 loops=1)

前一组是优化器在执行 SQL 之前的成本估算， 后一组才是 SQL 真正执行后采集到的实际运行数据。

1. 优化器估算

(cost=2042 rows=0.489)

• cost：优化器计算出的相对成本，用于比较不同执行计划。 它不是毫秒，也不能直接理解为查询耗时。
• rows：优化器预计该算子每次执行返回的行数， 主要根据索引统计信息、字段基数、数据分布等进行估算。

这部分最重要的用途，不是判断 SQL 实际跑得有多慢，而是理解：

优化器为什么会选择当前这个执行计划。

如果估算行数和实际行数差距非常大， 就说明优化器对数据分布的判断可能存在偏差， 这种情况下索引统计信息、字段选择性等就值得进一步检查。

2. 实际执行数据

(actual time=190..193 rows=125 loops=1)

这里才是真正进行性能分析时重点关注的数据：

• loops：该算子实际被执行了多少次。
• rows：平均每次循环返回多少行。
• actual time=A..B：
  • A：平均每次循环获取第一行数据所需要的时间。
  • B：平均每次循环读取完全部数据所需要的时间。
  • 单位为毫秒。

当 loops=1 时，看起来比较直观。

当 loops > 1 时，则需要特别注意：

rows × loops

才能得到这个算子大致处理的总行数；

actual time 的 B × loops

则可以估算该算子在所有循环中的累计执行时间。

另外，执行计划是树形结构， 父算子的时间包含其下面整个子树的执行时间， 因此不能简单把各层 actual time 相加， 否则会产生重复计算。

二、看嵌套循环：重点关注 rows × loops

分析 Nested Loop 一类执行计划时，一个非常实用的判断方式是：

实际处理行数 ≈ 单次返回行数 × 循环次数

性能问题往往不是单纯因为 loops 多， 也不是单纯因为单次扫描行数多，而是：

大量循环 × 每次大量扫描。

两个数同时变大以后，内层算子的开销就会被外层循环不断放大。

例如慢查询中的瓶颈算子：

Index lookup on selected_aggregation using idx
(actual time=0.0154..3.49 rows=2345 loops=1187)

计算一下：

2345 × 1187 = 2,783,515

也就是说，这个算子累计大约处理了 278 万行数据。

再看时间：

3.49ms × 1187 ≈ 4143ms

大约就是 4.1 秒。

整个查询耗时约 4.7 秒，这一层已经占据了绝大部分执行时间， 因此基本可以确认这里就是主要性能瓶颈。

再对比优化后的执行计划：

Single-row index lookup on selected_aggregation using PRIMARY
(actual time=0.00331..0.00334 rows=1 loops=1187)

虽然循环次数仍然是：

loops=1187

但每次只查询一条数据：

1 × 1187 = 1187 行

累计执行时间大约为：

0.00334ms × 1187 ≈ 4ms

两种执行计划的外层循环次数完全一样， 真正发生变化的是内层每次循环需要扫描的数据量：

2345 行/次 → 1 行/次

因此累计扫描量从约 278 万行下降到 1187 行， 内层算子的耗时也从约 4.1 秒降低到了几毫秒。

这个例子也很直观地说明：

Nested Loop 本身并不一定慢，真正危险的是把一个扫描量很大的操作放到 Nested Loop 的内层。

三、两个执行计划到底差在哪里

对比项	慢查询（约 4.7 秒）	快查询（约 0.19 秒）
关联逻辑	id = t1.id OR parent_id = t1.id， 需要同时匹配自身和子节点	id = t1.id， 只匹配自身
selected_aggregation 访问方式	使用普通二级索引，需要扫描项目下较大范围的数据	使用主键索引进行单行精准查找
单次扫描量	约 2345 行/次	1 行/次
循环次数	1187 次	1187 次
累计处理量	约 278 万行	1187 行
内层执行成本	较大的范围扫描被外层循环反复执行	内层变成低成本主键点查

这两个 SQL 的性能差距，本质上并不在外层循环。

两边都是：

loops=1187

真正决定性能的是：

每次循环到底需要处理多少数据

慢查询每进入一次内层，就需要处理两千多条记录； 快查询每进入一次内层，只需要通过主键获取一条记录。

外层循环次数相同的情况下， 内层访问方式的区别最终被放大了上千次。

四、这个慢查询为什么会慢

慢查询中的核心条件类似：

id = t1.id
OR parent_id = t1.id

这里需要同时通过两个不同字段进行匹配。

并不能简单理解为“出现 OR 就一定导致索引失效”， 但在当前这个执行计划中， 单个索引无法同时高效完成两个分支的定位， 最终使 selected_aggregation 的访问方式退化成了较大范围的数据扫描。

单次扫描两千多行本身可能还不算特别严重， 但这个扫描操作位于 Nested Loop 内层，又被执行了 1187 次：

2345 × 1187 ≈ 278 万行

最终就变成了整个查询最主要的性能瓶颈。

因此，这类查询优化时应该优先考虑：

能不能把内层的“大范围扫描”变成“小范围扫描”或者“索引点查”。

如果业务必须保留“匹配自身 + 匹配子节点”的逻辑，可以考虑把：

id = ?
OR parent_id = ?

拆成两个独立查询，让两个分支分别使用适合自己的索引，再合并结果。

例如思路上拆成：

-- 匹配自身
SELECT ...
WHERE id = ?

UNION ALL

-- 匹配子节点
SELECT ...
WHERE parent_id = ?

这样：

id = ?

可以走主键或者对应索引，

parent_id = ?

也可以单独使用 parent_id 索引， 而不需要让一个 OR 条件同时承担两个字段的访问路径。

当然，如果两个分支可能返回同一条数据， 就不能直接无脑使用 UNION ALL， 需要根据业务数据关系选择 UNION， 或者增加额外的去重逻辑。

所以这里更准确的一句话结论应该是：

慢查询的核心问题不是 OR 这个关键字本身， 而是跨字段 OR 使当前执行计划无法对两个条件都进行高效索引定位， 导致内层每次循环都扫描大量数据； 这个扫描又被 Nested Loop 的 1187 次循环进一步放大。 优化的关键，是拆分访问路径，让不同条件分别使用合适的索引， 从而把内层的大范围扫描重新变成索引点查或小范围扫描。

explain ANALYZE文本1:

-> Nested loop semijoin  (cost=43428 rows=231) (actual time=4111..4778 rows=165 loops=1)
    -> Filter: ((t1.del_flag = 0) and (t1.parent_id is null) and ((t1.`type` is null) or (t1.`type` not in ('人工','机械'))))  (cost=2021 rows=19.4) (actual time=0.117..12 rows=1187 loops=1)
        -> Index lookup on t1 using idx (pre_bid_cost_project_id=1982392634168127105)  (cost=2021 rows=2361) (actual time=0.0888..8.97 rows=2361 loops=1)
    -> Nested loop inner join  (cost=2775 rows=11.9) (actual time=4.01..4.01 rows=0.139 loops=1187)
        -> Nested loop inner join  (cost=2517 rows=238) (actual time=1.14..3.85 rows=29 loops=1187)
            -> Filter: ((selected_aggregation.del_flag = 0) and ((selected_aggregation.id = t1.id) or (selected_aggregation.parent_id = t1.id)))  (cost=2020 rows=23.6) (actual time=1.13..3.82 rows=1.29 loops=1187)
                -> Index lookup on selected_aggregation using idx (pre_bid_cost_project_id=1982392634168127105)  (cost=2020 rows=2361) (actual time=0.0154..3.49 rows=2345 loops=1187)
            -> Covering index lookup on selected_rel using uk_bill_subcontract1 (pre_bid_cost_project_id=1982392634168127105, aggregation_id=selected_aggregation.id)  (cost=1.04 rows=10.1) (actual time=0.00928..0.0215 rows=22.4 loops=1536)
        -> Filter: ((selected_lmc.subcontract_type = 'labor_subcontract') and (selected_lmc.is_self_purchased = 1) and (selected_lmc.del_flag = 0) and (selected_lmc.pre_bid_cost_project_id = 1982392634168127105))  (cost=0.605 rows=0.05) (actual time=0.00556..0.00556 rows=0.00479 loops=34465)
            -> Single-row index lookup on selected_lmc using PRIMARY (id=selected_rel.bill_lmc_id)  (cost=0.605 rows=1) (actual time=0.00508..0.00512 rows=1 loops=34465)

explain ANALYZE文本2:

 
-> Nested loop semijoin  (cost=2042 rows=0.489) (actual time=190..193 rows=125 loops=1)
    -> Nested loop inner join  (cost=2041 rows=0.968) (actual time=0.136..14 rows=1187 loops=1)
        -> Filter: ((t1.del_flag = 0) and (t1.parent_id is null) and ((t1.`type` is null) or (t1.`type` not in ('人工','机械'))))  (cost=2021 rows=19.4) (actual time=0.126..9.35 rows=1187 loops=1)
            -> Index lookup on t1 using idx (pre_bid_cost_project_id=1982392634168127105)  (cost=2021 rows=2361) (actual time=0.0914..8.54 rows=2361 loops=1)
        -> Filter: ((selected_aggregation.del_flag = 0) and (selected_aggregation.pre_bid_cost_project_id = 1982392634168127105))  (cost=0.914 rows=0.05) (actual time=0.00359..0.00369 rows=1 loops=1187)
            -> Single-row index lookup on selected_aggregation using PRIMARY (id=t1.id)  (cost=0.914 rows=1) (actual time=0.00331..0.00334 rows=1 loops=1187)
    -> Nested loop inner join  (cost=12.4 rows=0.505) (actual time=0.151..0.151 rows=0.105 loops=1187)
        -> Covering index lookup on selected_rel using uk_bill_subcontract1 (pre_bid_cost_project_id=1982392634168127105, aggregation_id=t1.id)  (cost=2.08 rows=10.1) (actual time=0.00461..0.0173 rows=24.5 loops=1187)
        -> Filter: ((selected_lmc.subcontract_type = 'labor_subcontract') and (selected_lmc.is_self_purchased = 1) and (selected_lmc.del_flag = 0) and (selected_lmc.pre_bid_cost_project_id = 1982392634168127105))  (cost=0.482 rows=0.05) (actual time=0.00531..0.00531 rows=0.00429 loops=29137)
            -> Single-row index lookup on selected_lmc using PRIMARY (id=selected_rel.bill_lmc_id)  (cost=0.482 rows=1) (actual time=0.00491..0.00494 rows=1 loops=29137)