带索引的status字段作为查询条件时查询缓慢问题排查

问题背景 #

操作一张6.9GB、约2800万条记录的pedidos表，存在以下查询性能差异：

• 仅按marketplace_id=64查询：响应20ms
• 按marketplace_id=64 AND status_pedido_id=1/3查询：响应600-900ms
• 按marketplace_id=64 AND status_pedido_id=2查询：响应超30秒
• 补充现象：销量多的店铺（对应marketplace_id记录数多）查询更快，销量少的更慢

表结构、查询耗时及EXPLAIN ANALYZE结果见下方：

表结构 #

CREATE TABLE `pedidos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT NULL,
  `tipo_pedido_id` int NOT NULL DEFAULT '1' COMMENT '1 - Venda, 2 - Comissão, 3 - Assinatura',
  `usuario_id` int DEFAULT NULL,
  `cliente_id` int DEFAULT NULL,
  `estabelecimento_id` int DEFAULT NULL,
  `marketplace_id` int DEFAULT NULL,
  `status_pedido_id` int NOT NULL DEFAULT '1',
  `cliente_cartao_id` int DEFAULT NULL,
  `valor_bruto` decimal(10,2) NOT NULL DEFAULT '0.00',
  `valor_liquido` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tipo_pagamento` varchar(50) DEFAULT NULL,
  `bandeira` varchar(50) DEFAULT NULL,
  `parcelas` int DEFAULT NULL,
  `markup` decimal(10,2) DEFAULT NULL,
  `capture_mode` varchar(100) DEFAULT NULL,
  `zoop_transaction_id` varchar(100) DEFAULT NULL,
  `zoop_plan_id` varchar(100) DEFAULT NULL,
  `pos_identification_number` varchar(100) DEFAULT NULL,
  `authorization_code` varchar(100) DEFAULT NULL,
  `authorization_nsu` varchar(100) DEFAULT NULL,
  `oculto` int DEFAULT '0',
  `splitted_taxa_recorrente` tinyint(1) DEFAULT '0',
  `splitted_link` tinyint(1) NOT NULL DEFAULT '0',
  `splitted_sale` tinyint(1) DEFAULT '0',
  `splitted_invoice` tinyint(1) DEFAULT '0',
  `splitted` tinyint(1) NOT NULL DEFAULT '0',
  `taxed` tinyint(1) NOT NULL DEFAULT '0',
  `antecipado` tinyint(1) DEFAULT NULL,
  `referencia` varchar(255) DEFAULT '',
  `msg_erro` varchar(200) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `online_taxed` tinyint(1) NOT NULL DEFAULT '0',
  `removed` datetime DEFAULT NULL,
  `processed` datetime DEFAULT NULL,
  `internal_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cliente_id` (`cliente_id`),
  KEY `usuario_id` (`usuario_id`),
  KEY `estabelecimento_id` (`estabelecimento_id`),
  KEY `status_pedido_id` (`status_pedido_id`),
  KEY `zoop_transaction_id` (`zoop_transaction_id`),
  KEY `pos_identification_id` (`pos_identification_number`),
  KEY `bandeira` (`bandeira`),
  KEY `removed` (`removed`),
  KEY `listagemVendas` (`parent_id`,`created`,`oculto`,`marketplace_id`),
  KEY `pedidos_cliente_cartao_id_foreign_idx` (`cliente_cartao_id`),
  KEY `created` (`created`),
  KEY `idx_marketplace_id` (`marketplace_id`),
  KEY `processed` (`processed`),
  KEY `id` (`id`),
  CONSTRAINT `pedidos_cliente_cartao_id_foreign_idx` FOREIGN KEY (`cliente_cartao_id`) REFERENCES `clientes_cartoes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `pedidos_ibfk_1` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `pedidos_ibfk_2` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `pedidos_ibfk_3` FOREIGN KEY (`estabelecimento_id`) REFERENCES `estabelecimentos` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `pedidos_ibfk_4` FOREIGN KEY (`status_pedido_id`) REFERENCES `status_pedidos` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=50717358 DEFAULT CHARSET=latin1;

查询耗时情况 #

• 快速查询：SELECT * FROM pedidos where marketplace_id = 64 limit 100;，响应时间20ms
• 较快查询：SELECT * FROM pedidos WHERE marketplace_id = 64 and status_pedido_id = 1 limit 100;，响应时间900ms（返回4条结果）
• 慢查询：SELECT * FROM pedidos WHERE marketplace_id = 64 and status_pedido_id = 2 limit 100;，响应时间30+秒（结果远超100条）
• 较快查询：SELECT * FROM pedidos where marketplace_id = 64 and status_pedido_id = 3 limit 100;，响应时间600ms（返回100条结果）

EXPLAIN ANALYZE结果 #

快速查询（status_pedido_id=3）

explain analyze SELECT * FROM pedidos where marketplace_id = 64 and status_pedido_id = 3 limit 100;

-> Limit: 100 row(s)  (cost=7596.98 rows=100) (actual time=894.745..960.513 rows=100 loops=1)
    -> Filter: ((pedidos.status_pedido_id = 3) and (pedidos.marketplace_id = 64))  (cost=7596.98 rows=8882) (actual time=894.744..960.501 rows=100 loops=1)
        -> Intersect rows sorted by row ID  (cost=7596.98 rows=8883) (actual time=894.738..960.426 rows=100 loops=1)
            -> Index range scan on pedidos using idx_marketplace_id over (marketplace_id = 64)  (cost=102.01 rows=108180) (actual time=0.086..0.890 rows=1938 loops=1)
            -> Index range scan on pedidos using status_pedido_id over (status_pedido_id = 3)  (cost=674.22 rows=2326222) (actual time=0.041..888.081 rows=882406 loops=1)

慢查询（status_pedido_id=2）

explain analyze SELECT * FROM pedidos where marketplace_id = 64 and status_pedido_id = 2 limit 100;

-> Limit: 100 row(s)  (cost=36770.23 rows=100) (actual time=224685.952..225138.592 rows=100 loops=1)
    -> Filter: (pedidos.marketplace_id = 64)  (cost=36770.23 rows=54062) (actual time=224685.951..225138.572 rows=100 loops=1)
        -> Index range scan on pedidos using status_pedido_id over (status_pedido_id = 2), with index condition: (pedidos.status_pedido_id = 2)  (cost=36770.23 rows=14164526) (actual time=0.069..223866.996 rows=15977367 loops=1)

性能差异原因 #

从EXPLAIN ANALYZE结果可以明确两个核心原因：

1. 执行计划选择差异：
   • 当status_pedido_id=3时，MySQL选择对idx_marketplace_id和status_pedido_id两个索引做交集运算：先通过idx_marketplace_id获取marketplace_id=64的1938条记录，再和status_pedido_id=3的记录做交集，快速筛选出符合条件的结果，很快就能取到100条。
   • 当status_pedido_id=2时，MySQL仅选择走status_pedido_id索引，因为该状态的记录量极大（约1597万条，占总数据的57%），优化器错误判断了过滤成本：它需要扫描这1597万条记录，逐行检查marketplace_id=64，直到找到100条符合条件的记录，这导致了大量的IO和计算开销。
2. 数据分布影响：
   销量多的店铺（marketplace_id对应的记录数多），在扫描status_pedido_id=2的海量数据时，更快能碰到符合条件的记录；而销量少的店铺需要扫描更多行才能凑够100条，因此耗时更长。

解决方案 #

1. 创建联合索引（最优方案） #

针对marketplace_id + status_pedido_id的查询模式，创建联合索引可以让MySQL直接定位到符合两个条件的记录，无需扫描大量数据：

CREATE INDEX idx_marketplace_status ON pedidos(marketplace_id, status_pedido_id);

该索引会先按marketplace_id分组，再按status_pedido_id排序，查询时能直接定位到marketplace_id=64 AND status_pedido_id=2的记录范围，快速返回前100条。

2. 临时强制使用marketplace_id索引（应急方案） #

如果暂时无法创建索引，可以强制MySQL使用idx_marketplace_id索引，先获取marketplace_id=64的记录，再过滤status_pedido_id=2：

SELECT * FROM pedidos FORCE INDEX (idx_marketplace_id) 
WHERE marketplace_id = 64 AND status_pedido_id = 2 LIMIT 100;

此方案仅作为临时应急，长期来看还是联合索引更可靠。

3. 更新表统计信息 #

MySQL的优化器依赖表统计信息选择执行计划，更新统计信息可能让优化器做出更合理的选择：

ANALYZE TABLE pedidos;

但此方法无法从根本上解决status_pedido_id=2数据量过大导致的执行计划偏差，仅作为辅助手段。

内容的提问来源于stack exchange，提问作者Dayglor