MySQL 5.7中varchar长度过大为何不使用索引？

在表连接操作中发现，当连接字段为长度过大的VARCHAR类型时，MySQL 5.7不会在连接中使用该字段的索引，导致查询时间大幅增加。以下是表定义与执行计划分析：

表定义 #

CREATE TABLE `LotRecordsRaw` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lotNumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `scrapingJobId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lotNumber_UNIQUE` (`lotNumber`),
  KEY `idx_Lot_lotNumber` (`lotNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=14551 DEFAULT CHARSET=latin1;

执行计划语句 #

explain
(
  select lotRecord.*
  from LotRecordsRaw lotRecord
  left join (
    select lotNumber, max(scrapingJobId) as id
    from LotRecordsRaw
    group by lotNumber
  ) latestJob on latestJob.lotNumber = lotRecord.lotNumber
)

当lotNumber为varchar(255)时，执行计划显示派生表未使用lotNumber上的索引；将其改为varchar(45)后，索引正常被使用，查询时间从100秒缩短至2秒。

原因分析 #

• 索引成本评估差异：MySQL优化器会计算使用索引的成本。对于varchar(255)的utf8mb4字段，每个字符占4字节，单条索引键长度可达1020字节，远高于varchar(45)的180字节。优化器判断长索引键在分组、连接时的IO和内存开销过大，全表扫描的成本反而更低，因此放弃使用索引。
• 派生表分组的成本判断：查询中的派生表需要按lotNumber分组取最大值，当字段过长时，优化器认为走索引进行分组排序的成本（比如索引扫描后合并结果）比全表扫描后排序更高，所以选择全表扫描。缩短字段长度后，索引的存储和处理成本骤降，优化器会优先选择索引来提升效率。
• MySQL 5.7优化器的局限性：5.7版本的优化器在长字段索引的成本估算逻辑上偏保守，相比8.0+版本，对长索引的收益判断不够精准，更容易触发全表扫描的执行计划。

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