如何优化含JOIN与时间过滤条件的千万级MySQL关联查询？

先帮你拆解下查询逻辑：你需要获取2021-03-03展示的横幅，以及这些横幅在2021-03-03至2021-03-06之间的点击记录。查询慢的核心原因大概率是索引覆盖不足或者内存缓存不足导致大量磁盘IO，以下是具体的优化步骤：

1. 优化表索引（最关键的一步） #

当前两张表的单键索引无法适配你的"过滤+关联+返回字段"全流程需求，需要创建针对性的复合覆盖索引：

针对shows表 #

查询需要先过滤created_at = '2021-03-03'，再获取id（关联用）和data（返回用），创建复合覆盖索引：

CREATE INDEX idx_shows_created_at_id_data ON shows(created_at, id, data);

这个索引能让数据库直接从索引中获取所有需要的数据，无需回表查询主数据，大幅减少IO操作。

针对clicks表 #

查询需要过滤created_at范围、通过show_id关联shows，同时返回id和data，可以创建两种方向的复合覆盖索引，根据执行计划选择更优的：

• 如果执行计划是先查shows再匹配clicks，用这个索引：

CREATE INDEX idx_clicks_show_id_created_at_id_data ON clicks(show_id, created_at, id, data);

• 如果执行计划是先过滤clicks时间范围再关联shows，用这个索引：

CREATE INDEX idx_clicks_created_at_show_id_id_data ON clicks(created_at, show_id, id, data);

你可以通过EXPLAIN查看执行计划的key字段和rows预估行数，判断哪个索引更适配。

注意：先保留原单键索引，等新索引验证生效后，再考虑清理冗余索引，避免影响其他业务。

2. 调整InnoDB缓冲池配置 #

正如你提到的，将innodb_buffer_pool_size调整为12G是非常有效的优化手段。你的两张表各有4000万条记录，数据量庞大，足够大的缓冲池可以把常用的索引和数据加载到内存，彻底避免频繁的磁盘读写。

修改方式 #

• 永久生效：修改MySQL配置文件（如my.cnf/my.ini）：

innodb_buffer_pool_size = 12G

修改后重启MySQL服务。

• 临时生效（生产环境无重启条件时）：

SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;

3. 辅助优化手段 #

• 更新表统计信息：确保MySQL优化器能基于最新的表数据生成最优执行计划：

ANALYZE TABLE shows, clicks;

• 分区表（可选）：如果你的查询经常按created_at范围过滤，可以考虑按日期对两张表做分区（比如按月份），查询时只会扫描对应分区的数据，减少扫描范围。

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