MySQL 5.7.20中双TEXT列SELECT DISTINCT查询过慢问题咨询

这确实是个让人头疼的问题，我来帮你梳理下可能的原因和解决方案：

一、MySQL 5.5到5.7可能导致性能下降的关键变更 #

从版本升级的角度来看，这几个核心变化大概率是罪魁祸首：

• 临时表存储引擎逻辑调整：MySQL 5.7默认将临时表的存储引擎切换为InnoDB（而5.5默认是MyISAM）。InnoDB的磁盘临时表在处理大字段（比如TEXT）的去重/排序时，开销比MyISAM高不少——尤其是当临时表需要刷到磁盘时，性能差距会被放大。
• TEXT字段的处理逻辑优化副作用：5.7对DISTINCT的实现做了优化，但针对TEXT这类大字段，反而可能引入额外的哈希计算或排序开销。5.5中可能直接基于原始数据做去重，而5.7可能先尝试对字段做哈希或排序再去重，这对TEXT来说成本极高。
• 字符集/排序规则的默认变更：如果升级后你的表默认字符集从latin1改成了utf8mb4（5.7开始默认是utf8mb4），TEXT字段的比较和排序成本会直线上升——utf8mb4每个字符最多占4字节，计算哈希或排序时的运算量比latin1大很多。
• 优化器开关的默认开启：5.7默认开启了一些新的优化器开关（比如derived_merge），虽然大部分场景下是好事，但个别复杂查询可能因此生成更差的执行计划。

二、针对该查询的索引优化建议 #

因为你要对两个TEXT字段做DISTINCT，直接建联合索引(author, sort_author)不太现实（MySQL对TEXT字段的索引有前缀长度限制，默认utf8mb4下最多只能索引前191个字符），可以试试这几个方案：

• 前缀联合索引：为两个字段创建前缀索引，比如：

  CREATE INDEX idx_author_sort_prefix ON itemsbyauthor(author(100), sort_author(100));
  

  注意前缀长度要根据你的业务数据调整——要保证足够的区分度，避免因为前缀相同导致去重不准确。虽然这种索引无法直接完成DISTINCT，但能大幅减少全表扫描的范围，让临时表的生成更快。
• 新增哈希字段并建索引：这是更高效的方案，步骤如下：
  1. 新增两个哈希字段：

     ALTER TABLE itemsbyauthor ADD COLUMN author_hash VARCHAR(32) GENERATED ALWAYS AS (MD5(author)) STORED;
     ALTER TABLE itemsbyauthor ADD COLUMN sort_author_hash VARCHAR(32) GENERATED ALWAYS AS (MD5(sort_author)) STORED;
     

  2. 建联合哈希索引：

     CREATE INDEX idx_author_sort_hash ON itemsbyauthor(author_hash, sort_author_hash);
     

  3. 查询时通过哈希值快速去重，再验证原字段：

     SELECT DISTINCT a.author, a.sort_author 
     FROM itemsbyauthor a
     JOIN (SELECT DISTINCT author_hash, sort_author_hash FROM itemsbyauthor) b
     ON a.author_hash = b.author_hash AND a.sort_author_hash = b.sort_author_hash;
     

     这个方案利用哈希值体积小的特点，让索引快速完成去重，再回表取原字段，性能提升非常明显。唯一要注意的是哈希冲突概率——MD5的冲突概率极低，业务上基本可以忽略。
• 尝试用GROUP BY替代DISTINCT：有时候MySQL对GROUP BY的执行计划优化和DISTINCT不同，可以试试：

  SELECT author, sort_author FROM itemsbyauthor GROUP BY author, sort_author;
  

  看看EXPLAIN是否会有变化（虽然大部分场景下两者执行计划一致，但值得一试）。

三、需要检查的MySQL配置参数 #

调整这些配置可能会直接缓解性能问题：

• tmp_table_size 和 max_heap_table_size：这两个参数控制内存临时表的最大大小，默认值可能很小（比如16M）。如果你的查询需要的临时表超过这个大小，就会转成磁盘临时表，速度骤降。可以根据服务器内存情况调大，比如设置为1G：

  tmp_table_size = 1G
  max_heap_table_size = 1G
  

• innodb_temp_data_file_path：如果用的是InnoDB临时表，要确保这个参数指定的临时表空间在快速磁盘上（比如SSD），并且有足够的空间。默认路径可能和数据文件在一起，如果是机械磁盘会很慢。
• sort_buffer_size：如果查询涉及排序（5.7的DISTINCT可能用排序实现去重），调大这个参数可以减少磁盘排序的次数，建议设置为64M左右（根据内存情况调整）。
• optimizer_switch：可以尝试关闭derived_merge开关，看看是否能改变执行计划：

  SET optimizer_switch = 'derived_merge=off';
  

  这个需要测试，因为不同场景效果不同。

四、其他排查方向 #

• 检查表碎片：升级后表可能产生大量碎片，执行OPTIMIZE TABLE itemsbyauthor;整理碎片，减少全表扫描的开销。
• 验证字符集和排序规则：对比5.5和5.7中表的字符集/排序规则，如果5.7中是utf8mb4而5.5是latin1，可以考虑改回原来的字符集（如果业务允许），或者将排序规则改为utf8mb4_bin（二进制排序，速度更快，但区分大小写）。
• 查看慢查询日志：开启慢查询日志，查看该查询的具体执行细节——比如是否磁盘IO过高，或者CPU占用率拉满，帮助定位具体瓶颈。

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