风景
技术

大表聚合全字段索引优化

DB
文章目录

单表 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 内容， 这样一来，覆盖索引的意义就会被明显削弱。

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

Related Posts

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

We use cookies to improve your experience on our website