优化含GROUP_CONCAT的MySQL查询：PHP8/MySQL适配后性能调优

API 探路者

2026-7-13

旧PHP/MySQL应用视图查询性能优化问题

我有一个旧的PHP5+MySQL应用，现已适配为PHP8/MySQL14.14版本。当前存在一个视图查询，涉及多表关联、多个GROUP_CONCAT、子查询及字符串拼接操作，具体SQL代码如下：

(SELECT `db_demandes`.`bdd`.`id`
        AS `Id`,
        `db_demandes`.`bdd`.`ref`
        AS `Ref`,
        `db_demandes`.`bdd`.`nodossier`
        AS `NoDossier`,
        `db_demandes`.`bdd`.`agentfirme`
        AS `Agentfirme`,
        `db_demandes`.`bdd`.`agentanterieur`
        AS `AgentAnterieur`,
        Concat(`db_demandes`.`bdd`.`nom`, '\r\n', `db_demandes`.`bdd`.`prenom`)
        AS `Demandeur`,
Concat(`db_demandes`.`bdd`.`no_o`, ' ', `db_demandes`.`bdd`.`rue_o`, '\r\n', `db_demandes`.`bdd`.`cp_o`, ' ', `db_demandes`.`bdd`.`localite_o`)
        AS `Adresse`,
`db_demandes`.`bdd`.`protection`
        AS `Protection`,
`db_demandes`.`bdd`.`type_objet`
        AS `Type_Objet`,
`db_demandes`.`bdd`.`datation_o`
        AS `Datation_O`,
`db_demandes`.`bdd`.`datedemandesubsideavt`
        AS `DateDemandeSubsideAVT`,
REPLACE(`db_demandes`.`bdd`.`datef1`, '+', '\n')
        AS `DateF1`,
REPLACE(`db_demandes`.`bdd`.`datear1`, '+', '\n')
        AS `DateAR1`,
`db_demandes`.`bdd`.`datedemandeavise`
        AS `DateDemandeAvise`,
`db_demandes`.`bdd`.`dateavisfirme_e`
        AS `DateAvisfirme_E`,
`db_demandes`.`bdd`.`dateavisfirme_o`
        AS `DateAvisfirme_O`,
`db_demandes`.`bdd`.`dtdatedemandeauthtrav`
        AS `dtDateDemandeAuthTrav`,
`db_demandes`.`bdd`.`dtdatedecisionDirection`
        AS `dtDateDecisionDirection`,
Group_concat(DISTINCT `avantpromesse`.`dtdate` SEPARATOR ' ')
        AS `V1`,
`db_demandes`.`bdd`.`daterefusavt`
        AS `DateRefusAVT`,
`db_demandes`.`bdd`.`email`
        AS `Email`,
`db_demandes`.`bdd`.`travaux_a_subventionner`
        AS `Travaux_a_subventionner`,
`db_demandes`.`bdd`.`pc`
        AS `PC`,
`db_demandes`.`bdd`.`ff`
        AS `FF`,
(SELECT Group_concat(`db_demandes`.`tblpromesses`.`montantestimatifpromesse`
        SEPARATOR
        ' ')
 FROM   `db_demandes`.`tblpromesses`
 WHERE  ( `db_demandes`.`tblpromesses`.`fidossier` = `db_demandes`.`bdd`.`ref` )
 ORDER  BY `db_demandes`.`tblpromesses`.`idpromesse`)
        AS `MontantsEstimatifs`,
(SELECT Group_concat(`db_demandes`.`tblpromesses`.`dtdatepromesse` SEPARATOR ' '
        )
 FROM   `db_demandes`.`tblpromesses`
 WHERE  ( `db_demandes`.`tblpromesses`.`fidossier` = `db_demandes`.`bdd`.`ref` )
 ORDER  BY `db_demandes`.`tblpromesses`.`idpromesse`)
        AS `DatesPromesses`,
Group_concat(DISTINCT `db_demandes`.`tblvisites`.`dtdate` SEPARATOR ' ')
        AS `Visites`,
`db_demandes`.`tblsuivi`.`dtavancementtravaux`
        AS `dtAvancementTravaux`,
Trim(Concat(`db_demandes`.`tblsuivi`.`dtobservations`, '\r\n', REPLACE(
          `db_demandes`.`tblsuivi`.`dtavancementtravaux`, '\r\n', 'X')))
        AS `dtObservations`,
`db_demandes`.`tblsuivi`.`dtetat`
        AS `dtEtat`,
`db_demandes`.`bdd`.`paiementsprevisionnels2011`
        AS `PaiementsPrevisionnels2011`,
`db_demandes`.`bdd`.`paiementsprevisionnels2012`
        AS `PaiementsPrevisionnels2012`,
REPLACE(`db_demandes`.`bdd`.`datef2`, '+', '\n')
        AS `DateF2`,
REPLACE(`db_demandes`.`bdd`.`datear2`, '+', '\n')
        AS `DateAR2`,
`db_demandes`.`bdd`.`daterefusap_t`
        AS `DateRefusAP_T`,
`db_demandes`.`bdd`.`tt`
        AS `TT`,
(SELECT Group_concat(`db_demandes`.`tbldemandes`.`montantsubside` SEPARATOR ' ')
 FROM   `db_demandes`.`tbldemandes`
 WHERE  ( `db_demandes`.`tbldemandes`.`montantsubside` =
          `db_demandes`.`bdd`.`ref` )
 ORDER  BY `db_demandes`.`tbldemandes`.`montantsubside`)
        AS `Montantsdemandes`,
(SELECT Group_concat(`db_demandes`.`tbldemandes`.`date_am_subside` SEPARATOR ' '
        )
 FROM   `db_demandes`.`tbldemandes`
 WHERE  ( `db_demandes`.`tbldemandes`.`date_am_subside` =
          `db_demandes`.`bdd`.`ref` )
 ORDER  BY `db_demandes`.`tbldemandes`.`date_am_subside`)
        AS `Dates_AM_demandes`,
(SELECT Group_concat(`db_demandes`.`tbldemandes`.`date_op_subside` SEPARATOR ' '
        )
 FROM   `db_demandes`.`tbldemandes`
 WHERE  ( `db_demandes`.`tbldemandes`.`date_op_subside` =
          `db_demandes`.`bdd`.`ref` )
 ORDER  BY `db_demandes`.`tbldemandes`.`date_op_subside`)
        AS `Dates_OP_demandes`,
`db_demandes`.`bdd`.`dtflagdeleted`
        AS `dtFlagDeleted`,
`db_demandes`.`tbldevis`.`dtdate`
        AS `dtDateDevis`,
`db_demandes`.`bdd`.`patrimoine`
        AS `Patrimoine`,
`db_demandes`.`bdd`.`telmobile`
        AS `TelMobile`,
`db_demandes`.`bdd`.`telprive`
        AS `TelPrive`,
`db_demandes`.`bdd`.`notes`
        AS `Notes`,
`db_demandes`.`bdd`.`notesagent`
        AS `NotesAgent`
 FROM   (((((`db_demandes`.`bdd`
             LEFT JOIN `db_demandes`.`tblsuivi`
                    ON(( `db_demandes`.`tblsuivi`.`fidossier` =
                      `db_demandes`.`bdd`.`ref` )))
            LEFT JOIN `db_demandes`.`tbldevis`
                   ON(( ( `db_demandes`.`tbldevis`.`fidossier` =
                        `db_demandes`.`bdd`.`ref` )
                        AND ( `db_demandes`.`tbldevis`.`iddevis` = (SELECT Max(
                              `db_demandes`.`tbldevis`.`iddevis`)
                                                                    FROM
                              `db_demandes`.`tbldevis`
                                                                    WHERE  (
                                    `db_demandes`.`tbldevis`.`fidossier` =
                            `db_demandes`.`bdd`.`ref` )) ) )))
           LEFT JOIN `db_demandes`.`tblvisites`
                  ON(( `db_demandes`.`tblvisites`.`fidossier` =
                     `db_demandes`.`bdd`.`ref` )))
          LEFT JOIN `db_demandes`.`tblvisites` `avantpromesse`
                 ON(( `avantpromesse`.`fidossier` =
         `db_demandes`.`bdd`.`ref` )))
         LEFT JOIN `db_demandes`.`vvisitemin`
                ON(( `vvisitemin`.`fidossier` = `db_demandes`.`bdd`.`ref` )))
 WHERE ( ( NOT(( Lcase(`db_demandes`.`bdd`.`nodossier`) LIKE '%avis%' )) )
          AND ( NOT(( Lcase(`db_demandes`.`bdd`.`nodossier`) LIKE '%dble%' )) )
        )
 GROUP  BY `db_demandes`.`bdd`.`nodossier`,
           `db_demandes`.`tblsuivi`.`idsuivi`
 ORDER  BY `db_demandes`.`bdd`.`nodossier` DESC);

该视图会被另一个SQL请求调用，即使最大的bdd表仅7000条记录，查询仍需数分钟才能完成，性能极差。我尝试移除部分GROUP_CONCAT的DISTINCT关键字但效果不佳，作为SQL新手，想了解该从何处着手优化？

优化方向建议

1. 修复子查询逻辑错误

tbldemandes相关的3个子查询条件明显错误：比如tbldemandes.montantsubside = bdd.ref，补贴金额和档案编号不可能是关联条件，应该改成tbldemandes.fidossier = bdd.ref（和tblpromesses的关联规则一致）。这个错误会导致子查询返回大量无关数据，是性能差的核心原因之一，必须优先修正。

2. 替换关联子查询为预聚合JOIN

当前每个关联子查询（比如tblpromesses的两个子查询）都会对主表每条记录单独执行一次查询，效率极低。可以先对这些表按fidossier预聚合GROUP_CONCAT结果，再和主表JOIN：

LEFT JOIN (
    SELECT 
        fidossier,
        GROUP_CONCAT(montantestimatifpromesse SEPARATOR ' ') AS MontantsEstimatifs,
        GROUP_CONCAT(dtdatepromesse SEPARATOR ' ') AS DatesPromesses
    FROM db_demandes.tblpromesses
    GROUP BY fidossier
) promesses ON promesses.fidossier = db_demandes.bdd.ref

这样只需要一次聚合操作，避免重复查询。

3. 移除冗余表关联

你重复JOIN了两次tblvisites（主表关联+avantpromesse别名关联），会导致结果集膨胀（笛卡尔积），后续GROUP BY去重会极大消耗资源。可以合并成一个预聚合查询处理V1和Visites：

LEFT JOIN (
    SELECT 
        fidossier,
        GROUP_CONCAT(DISTINCT dtdate SEPARATOR ' ') AS Visites,
        GROUP_CONCAT(DISTINCT dtdate SEPARATOR ' ') AS V1 -- 若V1有特定过滤条件，添加WHERE子句即可
    FROM db_demandes.tblvisites
    GROUP BY fidossier
) visites ON visites.fidossier = db_demandes.bdd.ref

你JOIN了vvisitemin但未使用任何字段，直接移除该关联，减少JOIN开销。

4. 优化`tbldevis`关联逻辑

当前tbldevis关联用了子查询取最大iddevis，每条主表记录都会执行一次MAX查询，改成预查询最大ID再JOIN：

LEFT JOIN (
    SELECT fidossier, MAX(iddevis) AS max_iddevis
    FROM db_demandes.tbldevis
    GROUP BY fidossier
) max_devis ON max_devis.fidossier = db_demandes.bdd.ref
LEFT JOIN db_demandes.tbldevis ON tbldevis.fidossier = db_demandes.bdd.ref AND tbldevis.iddevis = max_devis.max_iddevis

5. 添加针对性索引

针对关联、过滤、聚合字段创建索引，大幅提升查询速度：

bdd表：INDEX(nodossier, ref)（过滤条件+关联字段）
tblsuivi表：INDEX(fidossier, idsuivi)（关联字段+GROUP BY字段）
tblpromesses表：INDEX(fidossier, idpromesse, montantestimatifpromesse, dtdatepromesse)（关联字段+排序字段+聚合字段）
tbldemandes表：INDEX(fidossier, montantsubside, date_am_subside, date_op_subside)（修正关联条件后创建）
tblvisites表：INDEX(fidossier, dtdate)（关联字段+聚合字段）
tbldevis表：INDEX(fidossier, iddevis, dtdate)（关联字段+MAX字段+查询字段）

6. 简化GROUP BY和ORDER BY

如果tblsuivi.idsuivi是主键，且每个fidossier对应唯一idsuivi，可以只GROUP BYbdd.ref（主键），减少分组开销。
ORDER BYbdd.nodossier DESC可以通过在bdd表添加INDEX(nodossier DESC)优化排序性能。

7. 避免WHERE子句的函数转换

当前LCASE(bdd.nodossier) LIKE '%avis%'会导致索引失效，若数据库使用不区分大小写的排序规则（如utf8mb4_general_ci），直接写成：

WHERE bdd.nodossier NOT LIKE '%avis%' 
  AND bdd.nodossier NOT LIKE '%dble%'

无需额外函数转换。

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

优化含GROUP_CONCAT的MySQL查询：PHP8/MySQL适配后性能调优

优化方向建议 #

1. 修复子查询逻辑错误 #

2. 替换关联子查询为预聚合JOIN #

3. 移除冗余表关联 #

4. 优化tbldevis关联逻辑 #

5. 添加针对性索引 #

6. 简化GROUP BY和ORDER BY #

7. 避免WHERE子句的函数转换 #