优化关联三张表的MySQL查询速度的方法求助

一、核心索引优化 #

针对三张表的关联逻辑和过滤条件，添加以下针对性索引，直接解决JOIN和过滤的性能瓶颈：

• cbcsightings表：创建联合覆盖索引 (rID, species, count)，关联字段rID/species用于快速匹配关联表，count字段放入索引避免回表查询。
• cbcspecies表：创建联合索引 (latin_name, lepgroup)，latin_name作为关联主键加速JOIN，lepgroup直接在索引中完成过滤，无需扫描全表。
• cbcrecords表：创建联合索引 (rConfirmed, rDate)，将等值条件rConfirmed='Y'放在索引首位，rDate用于快速范围筛选，避免函数导致的索引失效。

二、查询逻辑简化与改写 #

原查询中YEAR(rDate)函数会触发全表扫描，将其改写为范围查询，直接利用rDate索引：

• 替换YEAR(rDate) > 2020为 rDate >= '2021-01-01'
• 替换YEAR(rDate) = YEAR(CURRENT_DATE)为 rDate >= DATE_FORMAT(CURDATE(), '%Y-01-01') AND rDate < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL 1 YEAR)

同时简化重复条件，优化后的完整查询：

SELECT 
    SUM(IF(cr.rConfirmed = 'Y' AND cr.rDate >= '2021-01-01' AND cs.lepgroup = 'B', csgt.count, 0)) AS totButterflies,
    SUM(IF(cr.rConfirmed = 'Y' AND cr.rDate >= '2021-01-01' AND cs.lepgroup = 'M', csgt.count, 0)) AS totMoths,
    SUM(IF(cr.rConfirmed = 'Y' AND cr.rDate >= DATE_FORMAT(CURDATE(), '%Y-01-01') AND cr.rDate < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL 1 YEAR) AND cs.lepgroup = 'B', csgt.count, 0)) AS ThisYearButterflies,
    SUM(IF(cr.rConfirmed = 'Y' AND cr.rDate >= DATE_FORMAT(CURDATE(), '%Y-01-01') AND cr.rDate < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL 1 YEAR) AND cs.lepgroup = 'M', csgt.count, 0)) AS ThisYearMoths
FROM cbcsightings csgt
INNER JOIN cbcspecies cs ON cs.latin_name = csgt.species
INNER JOIN cbcrecords cr ON cr.cbcrec_id = csgt.rID

注：若业务允许，将LEFT JOIN改为INNER JOIN，可过滤无效关联数据，进一步减少计算量。

三、生产环境与开发环境性能差异排查 #

1. 数据库配置对比：检查托管服务器的innodb_buffer_pool_size（建议设置为服务器内存的50%-70%）、query_cache（MySQL8.0已移除，旧版本需确认是否启用）等核心参数，是否与开发环境存在差异。
2. 存储与碎片问题：生产环境可能使用机械硬盘，或因频繁读写导致表碎片，可在低峰期执行OPTIMIZE TABLE cbcsightings, cbcrecords, cbcspecies;整理碎片。
3. 服务器负载：用top、iostat等命令查看生产服务器的CPU、内存、磁盘IO占用，确认是否有其他进程抢占资源。
4. 缓存机制：开发环境可能因数据量小、查询频率高触发缓存，而生产环境缓存命中率低，可通过SHOW ENGINE INNODB STATUS查看缓冲池命中率。

四、替代冗余字段的优雅方案 #

若不想破坏数据库范式，可采用预计算方案：

1. 创建统计表存储预计算结果：

CREATE TABLE lep_statistics (
    stat_id INT AUTO_INCREMENT PRIMARY KEY,
    tot_butterflies INT DEFAULT 0,
    tot_moths INT DEFAULT 0,
    this_year_butterflies INT DEFAULT 0,
    this_year_moths INT DEFAULT 0,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 编写定时任务（如每天凌晨），执行优化后的查询更新统计表数据，首页直接查询该表即可将响应时间降至毫秒级。

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