MySQL索引未命中疑难：SELECT *时索引失效，指定字段/日期范围则正常

问题现象 #

1. SELECT * 搭配宽松日期条件时索引失效 #

执行指定索引的SELECT *查询，WHERE条件为id_servidor = 10 AND pago = '1' AND data_pago <= '2021-05-05'，执行计划显示全表扫描：

mysql> explain SELECT  *  FROM fatura  USE INDEX (datapago_serv_pago)   WHERE id_servidor = 10 AND pago = '1' AND data_pago <= '2021-05-05';
+----+-------------+--------+------------+------+--------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys      | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+--------+------------+------+--------------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | fatura | NULL       | ALL  | datapago_serv_pago | NULL | NULL    | NULL | 10199216 |     0.00 | Using where |
+----+-------------+--------+------------+------+--------------------+------+---------+------+----------+----------+-------------+

2. 仅查询主键字段时索引正常生效 #

仅查询uid_字段时，该索引可正常触发，执行计划显示使用索引：

mysql> explain SELECT  uid_  FROM fatura  USE INDEX (datapago_serv_pago)   WHERE id_servidor = 10 AND pago = '1' AND data_pago <= '2021-05-05';
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys      | key                | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | fatura | NULL       | range | datapago_serv_pago | datapago_serv_pago | 5       | NULL | 5099608 |     0.01 | Using where; Using index |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+

3. 缩小日期范围后SELECT * 索引正常生效 #

当WHERE条件增加data_pago >= '2021-04-01'缩小范围后，即使执行SELECT *，索引也能正常使用：

explain SELECT  *  FROM fatura  USE INDEX (datapago_serv_pago)   WHERE id_servidor = 10 AND pago = '1' AND data_pago >= '2021-04-01'  AND data_pago <= '2021-05-05';
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys      | key                | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | fatura | NULL       | range | datapago_serv_pago | datapago_serv_pago | 5       | NULL | 158342 |     0.01 | Using index condition |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+

表结构信息 #

CREATE TABLE `fatura` (
  `uid_` bigint unsigned NOT NULL,
  `uid_cliente` bigint unsigned NOT NULL,
  `uid_cliente_servico` bigint unsigned NOT NULL,
  `id_servidor` tinyint unsigned NOT NULL,
  `id` int unsigned NOT NULL DEFAULT '0',
  `data_cadastro` date NOT NULL,
  `valor` decimal(12,2) NOT NULL DEFAULT '0.00',
  `vencimento` date NOT NULL DEFAULT (0),
  `pago` tinyint unsigned NOT NULL DEFAULT '0',
  `data_pago` date NOT NULL DEFAULT (0),
  `valor_pago` decimal(12,2) unsigned NOT NULL DEFAULT '0.00',
  `historico` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `id_cliente` int unsigned NOT NULL DEFAULT (0),
  `id_servico` int unsigned NOT NULL DEFAULT (0),
  `nome` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `desativada` tinyint unsigned NOT NULL DEFAULT '0',
  `operador_inclusao` varchar(50) NOT NULL,
  `operador_liquidacao` varchar(50) NOT NULL,
  `forma_pago` varchar(50) NOT NULL,
  `status_banco` tinyint NOT NULL DEFAULT (0),
  PRIMARY KEY (`uid_`),
  KEY `vencimento` (`vencimento`) USING BTREE,
  KEY `id_cliente` (`id_cliente`),
  KEY `id_servico` (`id_servico`),
  KEY `pago` (`pago`),
  KEY `id_servidor` (`id_servidor`),
  KEY `id` (`id`) USING BTREE,
  KEY `uid_cliente` (`uid_cliente`),
  KEY `data_pago` (`data_pago`),
  KEY `data_cadastro` (`data_cadastro`),
  KEY `historico` (`historico`),
  KEY `uid_cliente_servico` (`uid_cliente_servico`),
  KEY `desativada` (`desativada`),
  KEY `operador_inclusao` (`operador_inclusao`),
  KEY `operador_liquidacao` (`operador_liquidacao`),
  KEY `venc_serv_pago` (`vencimento`,`id_servidor`,`pago`),
  KEY `forma_pago` (`forma_pago`),
  KEY `status_banco` (`status_banco`),
  KEY `datapago_serv_pago` (`data_pago`,`id_servidor`,`pago`),
  KEY `vencimento_serv_pago` (`data_pago`,`id_servidor`,`pago`)
)

原因分析 #

1. 优化器成本评估逻辑：MySQL优化器会对比全表扫描和索引查询的成本。当查询返回的数据量占表总数据量比例过高（此案例中接近50%），走索引后需要大量回表操作（随机IO），而全表扫描是顺序IO，优化器会认为全表扫描成本更低，因此选择跳过索引。即使使用USE INDEX，也只是建议优化器考虑指定索引，而非强制使用。
2. 覆盖索引生效场景：仅查询uid_时，由于InnoDB二级索引的叶子节点会存储主键值，该索引datapago_serv_pago可以直接返回所需数据，无需回表，属于覆盖索引查询，成本远低于全表扫描，因此优化器选择使用索引。
3. 数据量缩小后的成本变化：当缩小日期范围后，返回数据量大幅降低（仅约1.5%），回表操作的总成本低于全表扫描，优化器自然选择走索引。

解决方案 #

1. 强制使用索引：如果确认必须使用该索引，使用FORCE INDEX(datapago_serv_pago)替代USE INDEX，强制优化器选择指定索引：

   SELECT * FROM fatura FORCE INDEX (datapago_serv_pago) WHERE id_servidor = 10 AND pago = '1' AND data_pago <= '2021-05-05';
   

2. 使用覆盖索引：避免SELECT *，只查询需要的字段，若常用字段固定，可将这些字段加入索引，构建覆盖索引，消除回表成本：

   ALTER TABLE fatura ADD INDEX datapago_serv_pago_cover (data_pago, id_servidor, pago, uid_cliente, valor); -- 示例，加入常用字段
   

3. 更新表统计信息：执行ANALYZE TABLE fatura让优化器获取更准确的数据分布统计，帮助优化器做出更合理的选择。

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