为何MySQL 5.7执行简单查询时未使用lotNumber索引？

问题描述 #

执行以下查询时速度缓慢：

SELECT text FROM LogMessages where lotNumber = 5556677;

通过EXPLAIN分析发现，尽管idx_LogMessages_lotNumber被列为可选索引，但实际执行时未使用该索引，而是进行全表扫描。

EXPLAIN输出

mysql> explain SELECT text FROM LogMessages where lotNumber = 5556677;
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table                        | partitions | type | possible_keys                                                                | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | LogMessages                  | NULL       | ALL  | idx_LogMessages_lotNumber                                                    | NULL | NULL    | NULL | 35086603 |    10.00 | Using where |
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
1 row in set, 5 warnings (0.07 sec)

表结构

CREATE TABLE `LogMessages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lotNumber` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idLogMessages_UNIQUE` (`id`),
  KEY `idx_LogMessages_lotNumber` (`lotNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=37545325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

解决思路 #

• 修复类型不匹配问题
  表中lotNumber是varchar类型，但查询条件用了数字5556677，MySQL会隐式转换字符串为数字，直接导致索引失效。修改查询语句，给条件值添加引号：

  SELECT text FROM LogMessages where lotNumber = '5556677';
  

• 更新表统计信息
  如果索引存在但优化器没选择使用，可能是表的统计信息过时。执行以下命令更新统计信息，让优化器能准确评估索引成本：

  ANALYZE TABLE LogMessages;
  

  同时可以用SHOW INDEX FROM LogMessages;确认索引状态正常，没有损坏。

• 使用覆盖索引避免回表
  EXPLAIN显示符合条件的行数占比约10%（350万行左右），MySQL可能认为用索引后回表查询的成本高于全表扫描。此时可以创建包含lotNumber和text的联合覆盖索引，让查询直接从索引获取数据，无需回表：

  CREATE INDEX idx_lotNumber_text ON LogMessages(lotNumber, text);
  

• 强制使用索引（谨慎操作）
  如果确定使用索引更高效，可通过FORCE INDEX强制优化器选择指定索引，但不建议长期依赖这种方式，优先让优化器自主判断：

  SELECT text FROM LogMessages FORCE INDEX(idx_LogMessages_lotNumber) where lotNumber = '5556677';
  

• 整理表碎片
  表数据量较大（近3700万行），可能存在碎片导致索引效率下降。在业务低峰期执行以下命令整理表和索引碎片：

  OPTIMIZE TABLE LogMessages;
  

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