优化四张超大（约1亿行）关联表的MySQL SELECT查询速度

首先，咱们先从你的执行计划和查询逻辑入手，找到当前慢的核心原因：

• 原始查询里的子查询生成了一个<derived2>临时表，这个临时表没有索引，导致后续的JOIN操作需要全量扫描这38702行数据，这是主要的性能瓶颈之一。
• 虽然后续的关联都用到了索引，但因为临时表的全量遍历，导致整体扫描行数达到了99672，随着返回行数增加，耗时线性增长。

下面是几个可以落地的优化方向：

1. 移除衍生表，改用直接JOIN #

把原来的子查询改成直接关联my_rel表，这样MySQL可以直接利用my_rel上的Link索引过滤出符合条件的linker，避免生成无索引的临时表。修改后的SQL如下：

SELECT 
    r.linker, 
    IF(s.isSecond='1', c2.title, c1.title) AS Title, 
    IF(s.isSecond='1', c2.author, c1.author) AS Author, 
    IF(s.isSecond='1', c2.date, c1.date) AS Date 
FROM my_rel r
INNER JOIN my_stat s ON r.linker = s.linker
LEFT JOIN my_content_1 c1 ON s.isSecond='0' AND s.linker = c1.linker
LEFT JOIN my_content_2 c2 ON s.isSecond='1' AND s.linker = c2.linker
WHERE r.linkTo = '86sgv_ksg:0040608';

2. 优化my_stat表的索引 #

当前my_stat表用linker的唯一索引做关联，但查询中还需要用到isSecond字段做判断。创建(linker, isSecond)的联合索引，可以让MySQL在关联时直接获取到isSecond的值，避免回表查询：

ALTER TABLE my_stat ADD INDEX idx_linker_isSecond (linker, isSecond);

3. 用UNION ALL替代条件分支JOIN #

因为isSecond只有0和1两种取值，我们可以把查询拆成两个独立的分支，分别关联对应的内容表，再用UNION ALL合并结果。这种方式可以避免IF函数的计算开销，同时让每个分支的查询都更高效：

SELECT 
    r.linker, 
    c1.title AS Title, 
    c1.author AS Author, 
    c1.date AS Date 
FROM my_rel r
INNER JOIN my_stat s ON r.linker = s.linker
INNER JOIN my_content_1 c1 ON s.linker = c1.linker
WHERE r.linkTo = '86sgv_ksg:0040608' AND s.isSecond = '0'

UNION ALL

SELECT 
    r.linker, 
    c2.title AS Title, 
    c2.author AS Author, 
    c2.date AS Date 
FROM my_rel r
INNER JOIN my_stat s ON r.linker = s.linker
INNER JOIN my_content_2 c2 ON s.linker = c2.linker
WHERE r.linkTo = '86sgv_ksg:0040608' AND s.isSecond = '1';

4. 调整MySQL配置参数 #

结合你的服务器配置（64G内存），可以优化以下参数来提升内存使用率，减少磁盘IO：

• innodb_buffer_pool_size: 设置为32G-45G（物理内存的50%-70%），让更多的表数据和索引缓存到内存中。
• join_buffer_size: 适当增大（比如设置为2M），提升关联操作的内存缓冲区大小，避免使用磁盘临时表。

5. 长期优化：合并内容表（可选） #

my_content_1和my_content_2结构完全一致，只是存储不同类型的文档。如果业务允许，可以考虑合并成一张表，增加一个content_type字段区分类型。这样可以减少关联的表数量，简化查询逻辑，同时统一维护索引，长期来看能显著提升查询效率。

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