如何优化MySQL COUNT查询以提升执行速度？

1. 优先排查PHP层面性能问题 #

如果未执行SQL仅加载PHP文件就有数秒耗时，优先排查生成$industryInsertSql数组的业务逻辑：

• 是否在生成该数组时执行了慢查询、大量循环计算、未加缓存的重复IO操作
• 可在生成数组前后加耗时统计定位具体慢的代码块，大部分情况下该场景的耗时和SQL本身无关

2. 调整索引结构，使用联合覆盖索引 #

你当前创建的是三个字段的独立索引，单次查询大多只能用到其中一个，过滤后还要回表查询剩余字段，性能损耗大。建议创建如下联合覆盖索引：

CREATE INDEX idx_ind_reg_len ON fr_skills_trends (industry, reg_date, skillsTrends_len, skillsTrends, jobtitle, industry_url);

这里的skillsTrends_len是下文提到的冗余长度字段，索引包含所有查询需要的字段后，可直接走索引查询无需回表。

3. 新增冗余字段避免函数运算无法命中索引 #

当前LENGTH(skillsTrends)<=35和ORDER BY LENGTH(skillsTrends)都对字段做了函数运算，无法直接命中索引。可以新增一个TINYINT类型的冗余字段skillsTrends_len，在写入/更新数据时自动计算存储skillsTrends的长度，将查询对应部分改为：

-- 条件部分
AND skillsTrends_len <=35
-- 排序部分
ORDER BY skillsTrends_len DESC

调整后条件过滤和排序都能用到索引，避免文件排序。

4. 修正GROUP BY逻辑问题 #

当前查询GROUP BY skillsTrends但SELECT了jobtitle、industry、industry_url三个非聚合、非分组字段，在开启ONLY_FULL_GROUP_BY（MySQL 5.7及以上默认开启）的情况下会直接报错，未开启的情况下返回的这三个字段值也是随机的，不符合业务逻辑。你需要确认分组逻辑调整：

• 如果是要统计每个技能对应的不同岗位/行业数据，需要调整分组字段，补充jobtitle、industry到GROUP BY中
• 如果不需要这三个字段，直接从SELECT中移除即可，进一步减少查询数据量

5. 优化IN查询性能 #

如果$industryInsertSql数组元素数量超过100个，长IN查询的解析和匹配性能会大幅下降：

• 元素数量过千时建议将这些industry值写入临时表，用INNER JOIN 临时表 ON fr_skills_trends.industry = 临时表.industry代替IN查询
• PHP层面可以先对$industryInsertSql做去重处理，减少IN的参数数量

6. 其他细节优化 #

• 统计行数如果不需要排除skillsTrends为NULL的情况，将COUNT(skillsTrends)改为COUNT(*)，性能更好
• 可以在PHP层面提前计算3个月前的时间字符串，替换SQL中的NOW() - INTERVAL 3 MONTH，避免SQL层面的时间计算
• 确认reg_date字段是DATE/DATETIME类型而非字符串类型，避免隐式类型转换导致索引失效

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