MySQL本地查询耗时1秒，服务器端需30秒，求优化方案

性能差异的核心原因 #

• 索引缺失：服务器端puntuacion表缺少针对性复合索引，原SQL中10个独立子查询+1个聚合查询会重复扫描全表；本地环境数据量小，无索引也能快速完成，服务器数据量大时全表扫描成本剧增。
• 数据量级差异：本地测试数据量远小于服务器，多表LEFT JOIN会生成庞大临时数据集，服务器处理大临时表的IO和内存开销远超本地。
• 配置参数不合理：服务器MySQL的innodb_buffer_pool_size、tmp_table_size等参数未适配数据量，导致临时表写入磁盘，性能断崖式下降；本地配置可能更适合小数据场景。
• 锁竞争影响：服务器上存在其他业务操作，UPDATE语句长时间持有行锁或表锁，引发等待队列，拉长执行时间。

SQL语句优化方案 #

原SQL的最大问题是重复访问puntuacion表，通过条件聚合将多次查询合并为一次，大幅减少IO开销：

UPDATE jugador j
LEFT JOIN (
    SELECT 
        FK_Jugador,
        SUM(Puntuacion_Decimal) AS TotalPuntuacion,
        MAX(CASE WHEN IndiceLast = 0 THEN Puntuacion_Decimal END) AS U1,
        MAX(CASE WHEN IndiceLast = 1 THEN Puntuacion_Decimal END) AS U2,
        MAX(CASE WHEN IndiceLast = 2 THEN Puntuacion_Decimal END) AS U3,
        MAX(CASE WHEN IndiceLast = 3 THEN Puntuacion_Decimal END) AS U4,
        MAX(CASE WHEN IndiceLast = 4 THEN Puntuacion_Decimal END) AS U5,
        MAX(CASE WHEN IndiceLast = 5 THEN Puntuacion_Decimal END) AS U6,
        MAX(CASE WHEN IndiceLast = 6 THEN Puntuacion_Decimal END) AS U7,
        MAX(CASE WHEN IndiceLast = 7 THEN Puntuacion_Decimal END) AS U8,
        MAX(CASE WHEN IndiceLast = 8 THEN Puntuacion_Decimal END) AS U9,
        MAX(CASE WHEN IndiceLast = 9 THEN Puntuacion_Decimal END) AS U10
    FROM puntuacion
    WHERE Validado = 1
    GROUP BY FK_Jugador
) p ON j.Id = p.FK_Jugador
SET 
    j.Puntuacion = p.TotalPuntuacion,
    j.U1 = p.U1,
    j.U2 = p.U2,
    j.U3 = p.U3,
    j.U4 = p.U4,
    j.U5 = p.U5,
    j.Jugando = FALSE,
    j.Valor = POW(
        0.181818182 * IFNULL(p.U1, 3.5) +
        0.163636364 * IFNULL(p.U2, 3.5) +
        0.145454545 * IFNULL(p.U3, 3.5) +
        0.127272727 * IFNULL(p.U4, 3.5) +
        0.109090909 * IFNULL(p.U5, 3.5) +
        0.090909091 * IFNULL(p.U6, 3.5) +
        0.072727273 * IFNULL(p.U7, 3.5) +
        0.054545455 * IFNULL(p.U8, 3.5) +
        0.036363636 * IFNULL(p.U9, 3.5) +
        0.018181818 * IFNULL(p.U10, 3.5),
        1.125
    )
WHERE j.FK_Equipo = 1232;

索引优化建议 #

给puntuacion表创建覆盖索引，让聚合查询无需回表：

CREATE INDEX idx_puntuacion_validado_jugador_indice ON puntuacion (Validado, FK_Jugador, IndiceLast, Puntuacion_Decimal);

服务器配置调整 #

• 增大innodb_buffer_pool_size至服务器内存的50%-70%，让更多数据缓存到内存
• 调大tmp_table_size和max_heap_table_size，避免临时表写入磁盘
• 若连接数据量大，适当提升join_buffer_size

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