数据库服务器上的MySQL查询该选单列还是联合索引?从执行计划判断

发布人:A5数据发布时间:2026-10-25 15:28阅读量:65

有时给慢查询补了联合索引,MySQL仍选单列索引;也有时执行计划显示用了索引,查询却没有明显变快。这并不矛盾:索引选择的关键不是列数,而是查询能否借助索引缩小扫描范围、保持所需顺序,并减少回表。执行计划能说明优化器打算怎样访问数据,实际执行指标才能进一步检验这个选择是否划算。

对于同时按租户、状态和时间筛选的订单查询,联合索引通常比单独的时间索引更有针对性;但如果查询只按时间检索,或者租户条件筛不掉多少行,结论就可能相反。应针对具体 SQL 和参数比较,而不是给整张表指定一个“通用最优索引”。

先界定问题:比较的是哪条查询的访问路径

以一张 orders 表为例,假定 id 是 InnoDB 主键,业务经常查询某租户最近的已支付订单:

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
  AND created_at < '2025-02-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

现在比较两个候选索引:

KEY idx_created_at (created_at)
KEY idx_tenant_status_time (tenant_id, status, created_at)

单列索引可以按时间范围定位,也可能利用逆序扫描满足排序;但 tenant_id 和 status 不在这个索引中。数据库读到时间范围内的索引记录后,仍需判断它是否属于目标租户、目标状态。由于查询还要返回 total_amount,符合条件的记录也需要读取表中数据。

联合索引则可以先定位 tenant_id = 42、status = 'paid' 对应的索引区域,再按 created_at 取时间范围内的记录。对于这条 ORDER BY created_at DESC LIMIT 50 查询,它还有机会按索引顺序取到所需行,较早停止扫描。不过,联合索引同样不包含 total_amount,不能因此称为覆盖索引。

这里的边界很重要:“用了索引”只说明存在一条索引访问路径,不说明扫描行数少,也不说明整条查询都由索引完成。单列索引、联合索引和全表扫描,最终都要放在同一条 SQL 的筛选、排序、返回列和数据分布下比较。

联合索引为什么可能少做许多工作

B+Tree 索引按索引列的顺序组织。对 (tenant_id, status, created_at) 而言,可以把它理解为先按租户分组,在租户内部按状态排列,再在对应状态内按时间排列。查询的前两列是等值条件,第三列是范围条件,因而能较集中地读取目标记录。

联合索引为什么可能少做许多工作配图

设表中有 200 万行,某个月的订单占 20 万行。下面只是用于说明扫描差异的示例,并非固定性能比例:

候选路径为取得 50 条结果,可能读取的索引记录主要额外工作
idx_created_at约 1.2 万条逐条判断租户和状态,可能发生较多回表
idx_tenant_status_time约 50 条读取符合条件的记录,并为返回列回表

如果目标租户的已支付订单只占最近订单的很小一部分,沿时间索引往回找,可能要越过许多其他租户或状态的记录,才能凑齐 50 条。联合索引通过前导等值列减少了这部分无效读取。

反过来,如果应用查询的是“全站最近 50 条订单”,没有租户和状态条件,idx_created_at 就能直接服务排序和 LIMIT。此时 (tenant_id, status, created_at) 中的时间顺序被前两列分隔,不能把它当作全表统一的时间顺序使用。这也是“联合索引包含了时间列,所以一定能替代时间单列索引”这一判断不成立的原因。

列顺序也不能只按“选择性最高的列排第一”决定。对于上述查询,前两列都是等值条件,时间列同时参与范围过滤和排序;换成其他 SQL,如果缺少 status 条件,(tenant_id, status, created_at) 就未必适合按某租户的时间顺序直接取最近记录。索引应服务一组明确的高频查询,而不是机械地把所有筛选列拼在一起。

哪些因素会让执行计划改变

即使 SQL 不变,优化器也可能在不同数据和参数下选择不同索引。判断时尤其要看以下几类变化:

  • 条件的区分度。租户规模可能相差很大,paid 在不同租户中的占比也可能不同。同一索引对小租户很有效,对大租户未必有同样收益。
  • 时间范围和 LIMIT。查最近 50 条,与查整年并返回几十万条,适合的访问路径可能不同。前者可能依靠索引顺序提前结束,后者的回表成本可能占主导。
  • 返回列。示例查询需要 total_amount。增加该列到索引可能减少回表,却会扩大索引,增加写入和维护成本;不能仅凭一次查询决定做成覆盖索引。
  • 排序是否与索引顺序匹配。等值条件之后的索引列,才更容易为这条查询提供所需顺序。EXPLAIN 出现 Using filesort 时,需要结合待排序行数判断代价;它不自动等于磁盘排序,也不必然是瓶颈。
  • 统计信息与数据变化。优化器依据统计信息估算行数和代价。如果数据分布已明显变化,估算可能偏离实际。偶发的参数差异也可能让一份执行计划不能代表全部请求。

在数据库服务器上,还要把索引问题与资源瓶颈区分开。索引选错可能增加逻辑读取、物理 I/O 和 CPU 消耗;但应用连接池排队、锁等待、网络往返或缓存命中变化,也会抬高请求耗时。不能只看到接口慢,就把原因归给索引。

用执行计划比较,而不是只看 key 列

先核对服务器版本与现有索引,再用生产中有代表性的参数查看计划。以下语句是只读检查,不会创建或删除索引:

SELECT VERSION();

SHOW INDEX FROM orders;

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
  AND created_at < '2025-02-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

读计划时,可以按“访问方式—扫描规模—额外工作”的顺序看:

计划信息重点判断
key、possible_keys实际选了哪个索引;候选索引是否在考虑范围内
type是较有针对性的范围或引用访问,还是扫描了大量索引记录、表记录
rows、filtered优化器估计会读多少行、过滤后剩多少;它们是估算,不是实测
Extra是否有额外排序、索引条件下推等;Using index 通常表示覆盖索引,不能与“用了某个索引”混为一谈

例如,计划选了 idx_created_at,type 显示为范围访问,看起来并不差;但如果 rows 很大、过滤后只剩很少记录,问题可能是“按时间找到了许多行,却逐条排除了其他租户的订单”。仅凭 key = idx_created_at 无法看出这层代价。

当两个索引都已经存在、且确认在测试环境执行查询的负载可接受时,可以分别指定索引观察差异。索引提示只用于诊断,不宜在未验证数据分布和长期变化前固化到业务 SQL:

EXPLAIN
SELECT id, created_at, total_amount
FROM orders FORCE INDEX (idx_created_at)
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
  AND created_at < '2025-02-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN
SELECT id, created_at, total_amount
FROM orders FORCE INDEX (idx_tenant_status_time)
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01 00:00:00'
  AND created_at < '2025-02-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

FORCE INDEX 的前提是索引实际存在;它展示“走这条路可能怎样”,不证明强制后的路径一定更快。尤其不要只比较计划里的 cost 数字:它是优化器的代价估算,并非耗时毫秒数。

如果版本支持 EXPLAIN ANALYZE(MySQL 8.0.18 起提供),可在测试环境对这条 SELECT 进一步查看实际执行中的行数、循环次数和耗时。与普通 EXPLAIN 不同,EXPLAIN ANALYZE 会真正执行查询;在大范围扫描或繁忙时段使用前,应先确认负载影响,并对照估算行数与实际行数。若两者偏差明显,要关注统计信息和参数分布,而不是立刻认定优化器“无故选错”。

什么时候选单列,什么时候选联合索引

对示例查询,如果小范围验证和代表性参数测试都表明:联合索引明显减少扫描、无需处理大量额外排序,而且写入成本可接受,那么保留或新增 (tenant_id, status, created_at) 有依据。若主要流量反而是跨租户按时间取最近记录,(created_at) 仍有独立价值;两类查询都重要时,也可能需要两个索引,但应衡量空间与写入开销。

新增索引不是零成本操作。它会占用磁盘与缓冲池空间,使插入、更新相关索引列和删除记录时多一份维护工作;建索引本身也会消耗 I/O、CPU,并可能影响业务。若准备在生产库变更,应先核对 MySQL 版本、表规模和可用空间,做好可恢复备份,选择合适的变更窗口并观察复制延迟及业务耗时。回滚方案可以是删除本次新增索引,但删除前仍须确认没有其他关键查询已依赖它;不要把回滚视为无影响操作。

最终判断可以落在三组对照上:同一条 SQL 使用不同索引时,实际扫描和回表工作相差多少;换成大、小租户及不同时间范围后,优势是否仍在;上线前后查询耗时分布、数据库 CPU、I/O 与写入表现是否共同支持这次选择。执行计划负责指出访问路径,代表性负载下的验证负责划定它的适用边界。

针对MySQL数据库及业务后台场景,A5数据提供中国香港、美国、日本、新加坡等地区的物理服务器资源,覆盖Xeon Gold、AMD EPYC等处理器平台,并配备SSD或NVMe存储及不同内存配置,为索引访问、数据读写和多任务业务运行提供硬件基础。结合不同地区的网络线路、带宽与存储方案,A5数据可承载网站、接口服务和数据库等多类业务部署。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/5740.html

相关推荐