如何优化运行缓慢的CREATE TABLE AS SELECT建表查询？

架构老林

2026-8-7

优化CREATE TABLE AS SELECT语句的方案

我编写了如下CREATE TABLE AS SELECT语句用于创建rechercherefppv3bis_tmp临时表，但该查询运行耗时过长，现寻求优化及简化方案：

CREATE TABLE rechercherefppv3bis_tmp
( `id` varchar(25) KEY,   `dateMaj` datetime DEFAULT NULL,   `type` varchar(9) DEFAULT NULL,   `nom` varchar(60) DEFAULT NULL,   `prenom` varchar(150) DEFAULT NULL,   `adresseLigne2` varchar(38) DEFAULT NULL,   `adresseLigne3` varchar(38) DEFAULT NULL,   `adresseLigne4` varchar(38) DEFAULT NULL,   `adresseLigne5` varchar(38) DEFAULT NULL,   `adresseLigne6` varchar(38) DEFAULT NULL,   `adresseLigne7` varchar(38) DEFAULT NULL,   `emailPrincipal` varchar(255) DEFAULT NULL,   `telephonePrincipal` varchar(50) DEFAULT NULL,   `lignes4` text,   `lignes6` text,   `numeros` text,   `adresses` text,   `numProcuration` text,   `numReexpedition` text, `cp` varchar(255), `isDGP` boolean NULL, `civilite` varchar(255) NULL,`codeCivilite` int(10) NULL , `coclico` varchar(8) NULL , `certificationStatut` varchar(255) NULL , `siret` varchar(14) NULL , `nomSociete` varchar(255) NULL )
AS (
    SELECT
                    u.id,
                    str_to_date(GREATEST(u.dateMaj, ifnull(a.dateMaj, '1970-01-01 08:00:00'), ifnull(t.dateMaj, '1970-01-01 08:00:00'), ifnull(m.dateMaj, '1970-01-01 08:00:00')),'%Y-%m-%d %H:%i:%s') as dateMaj,
                    u.type as type,
                    u.nom as nom,
                    u.prenom as prenom,
                    a.ligne1 as adresseLigne1,
                    a.ligne2 as adresseLigne2,
                    a.ligne3 as adresseLigne3,
                    a.ligne4 as adresseLigne4,
                    a.ligne5 as adresseLigne5,
                    a.ligne6 as adresseLigne6,
                    a.ligne7 as adresseLigne7,
                    m.adresse as emailPrincipal,
                    t.numero AS telephonePrincipal,
                    GROUP_CONCAT(DISTINCT ads.ligne4 SEPARATOR ';') as lignes4,
                    GROUP_CONCAT(DISTINCT ads.ligne6 SEPARATOR ';') as lignes6,
                    GROUP_CONCAT(DISTINCT ts.numero SEPARATOR ';') as numeros,
                    GROUP_CONCAT(DISTINCT ms.adresse SEPARATOR ';') as adresses,
                    GROUP_CONCAT(DISTINCT pp.idOrigine SEPARATOR ';') as numProcuration,
                    GROUP_CONCAT(DISTINCT rc.numero SEPARATOR ';') as numReexpedition,
                    SUBSTRING_INDEX(a.ligne6,' ',1) AS cp,
                    case when ctp1.idContratDGP is null then false else true end as isDGP,
                    u.niveauAdhesion,
                    u.civilite,
                    u.codeCivilite,
                    u.certificationStatus as certificationStatut,
                    u.coclico,
                    cr.nom as nomSociete,
                    cr.siret as siret
                FROM ccu_user_v3bis  u
                    LEFT OUTER JOIN ccu_adresse_v3bis a ON a.idUser = u.id AND a.principale = 1
                    LEFT OUTER JOIN ccu_email_v3bis m ON m.idUser = u.id AND m.principal = 1
                    LEFT OUTER JOIN ccu_telephone_v3bis t ON t.idUser = u.id AND t.principal = 1
                    LEFT OUTER JOIN ccu_adresse_v3bis ads ON ads.idUser = u.id
                    LEFT OUTER JOIN ccu_email_v3bis ms ON ms.idUser = u.id
                    LEFT OUTER JOIN ccu_telephone_v3bis ts ON ts.idUser = u.id
                    LEFT OUTER JOIN procuration_part pp ON pp.idClient = u.id
                    LEFT OUTER JOIN reexp_contrat rc ON rc.idCCU = u.id
                    LEFT OUTER JOIN client_refpm cr ON cr.id = u.coclico
                    LEFT OUTER JOIN
                        (SELECT ctp.idCCUSouscripteur , max(ctp.id) as idContratDGP
                        FROM contrats_part ctp
                        WHERE ctp.idCCUSouscripteur is not null and ctp.source = 'Digiposte' and ctp.statutContrat = '2' GROUP BY ctp.idCCUSouscripteur)
                        as ctp1 ON ctp1.idCCUSouscripteur = u.id
                group by u.id
);

优化建议

消除笛卡尔积，提前聚合子表数据
原查询同时关联了同一表的主记录和全量记录（比如ccu_adresse_v3bis的a和ads），会产生大量笛卡尔积，导致GROUP BY前的数据量暴增。建议把全量地址、邮箱、电话的聚合逻辑提前做成子查询，再和主表关联，避免数据膨胀。
优化子查询索引
针对contrats_part的子查询，创建联合索引idx_contrats_part_dgp (idCCUSouscripteur, source, statutContrat)，可以大幅提升分组查询的速度，减少子查询的执行时间。
移除不必要的DISTINCT
如果单用户下的ads.ligne4、ts.numero等字段不会重复，直接去掉GROUP_CONCAT里的DISTINCT，减少去重计算的开销。
简化日期格式转换
先确认u.dateMaj、a.dateMaj等字段是否为datetime类型：
- 如果是，直接用GREATEST(u.dateMaj, IFNULL(a.dateMaj, '1970-01-01 08:00:00'), ...)即可，无需str_to_date转换；
- 如果是字符串类型，建议在源表中修正字段类型，或在子查询中提前转换为datetime，避免在主查询中重复转换。
添加关联字段索引
确保所有关联字段都有索引，加速JOIN操作：
- ccu_adresse_v3bis：联合索引idx_adresse_user_principale (idUser, principale)
- ccu_email_v3bis：联合索引idx_email_user_principal (idUser, principal)
- ccu_telephone_v3bis：联合索引idx_telephone_user_principal (idUser, principal)
- procuration_part：索引idx_procuration_client (idClient)
- reexp_contrat：索引idx_reexp_ccu (idCCU)
- client_refpm：索引idx_refpm_id (id)
拆分表创建与数据插入
先单独创建表结构，再用INSERT INTO ... SELECT插入数据，这样可以分步优化查询逻辑，也便于排查问题。

优化后示例SQL

CREATE TABLE rechercherefppv3bis_tmp
( `id` varchar(25) KEY,   `dateMaj` datetime DEFAULT NULL,   `type` varchar(9) DEFAULT NULL,   `nom` varchar(60) DEFAULT NULL,   `prenom` varchar(150) DEFAULT NULL,   `adresseLigne1` varchar(38) DEFAULT NULL,   `adresseLigne2` varchar(38) DEFAULT NULL,   `adresseLigne3` varchar(38) DEFAULT NULL,   `adresseLigne4` varchar(38) DEFAULT NULL,   `adresseLigne5` varchar(38) DEFAULT NULL,   `adresseLigne6` varchar(38) DEFAULT NULL,   `adresseLigne7` varchar(38) DEFAULT NULL,   `emailPrincipal` varchar(255) DEFAULT NULL,   `telephonePrincipal` varchar(50) DEFAULT NULL,   `lignes4` text,   `lignes6` text,   `numeros` text,   `adresses` text,   `numProcuration` text,   `numReexpedition` text, `cp` varchar(255), `isDGP` boolean NULL, `civilite` varchar(255) NULL,`codeCivilite` int(10) NULL , `coclico` varchar(8) NULL , `certificationStatut` varchar(255) NULL , `siret` varchar(14) NULL , `nomSociete` varchar(255) NULL );

-- 提前聚合子表数据
WITH user_adresses_all AS (
    SELECT idUser,
           GROUP_CONCAT(DISTINCT ligne4 SEPARATOR ';') as lignes4,
           GROUP_CONCAT(DISTINCT ligne6 SEPARATOR ';') as lignes6
    FROM ccu_adresse_v3bis
    GROUP BY idUser
),
user_emails_all AS (
    SELECT idUser,
           GROUP_CONCAT(DISTINCT adresse SEPARATOR ';') as adresses
    FROM ccu_email_v3bis
    GROUP BY idUser
),
user_telephones_all AS (
    SELECT idUser,
           GROUP_CONCAT(DISTINCT numero SEPARATOR ';') as numeros
    FROM ccu_telephone_v3bis
    GROUP BY idUser
),
user_procurations AS (
    SELECT idClient,
           GROUP_CONCAT(DISTINCT idOrigine SEPARATOR ';') as numProcuration
    FROM procuration_part
    GROUP BY idClient
),
user_reexps AS (
    SELECT idCCU,
           GROUP_CONCAT(DISTINCT numero SEPARATOR ';') as numReexpedition
    FROM reexp_contrat
    GROUP BY idCCU
),
user_dgp AS (
    SELECT ctp.idCCUSouscripteur , max(ctp.id) as idContratDGP
    FROM contrats_part ctp
    WHERE ctp.idCCUSouscripteur is not null and ctp.source = 'Digiposte' and ctp.statutContrat = '2'
    GROUP BY ctp.idCCUSouscripteur
)
INSERT INTO rechercherefppv3bis_tmp
SELECT
    u.id,
    GREATEST(u.dateMaj, IFNULL(a.dateMaj, '1970-01-01 08:00:00'), IFNULL(t.dateMaj, '1970-01-01 08:00:00'), IFNULL(m.dateMaj, '1970-01-01 08:00:00')) as dateMaj,
    u.type as type,
    u.nom as nom,
    u.prenom as prenom,
    a.ligne1 as adresseLigne1,
    a.ligne2 as adresseLigne2,
    a.ligne3 as adresseLigne3,
    a.ligne4 as adresseLigne4,
    a.ligne5 as adresseLigne5,
    a.ligne6 as adresseLigne6,
    a.ligne7 as adresseLigne7,
    m.adresse as emailPrincipal,
    t.numero AS telephonePrincipal,
    COALESCE(ua.lignes4, '') as lignes4,
    COALESCE(ua.lignes6, '') as lignes6,
    COALESCE(ut.numeros, '') as numeros,
    COALESCE(ue.adresses, '') as adresses,
    COALESCE(up.numProcuration, '') as numProcuration,
    COALESCE(ur.numReexpedition, '') as numReexpedition,
    SUBSTRING_INDEX(a.ligne6,' ',1) AS cp,
    CASE WHEN ud.idContratDGP IS NULL THEN FALSE ELSE TRUE END as isDGP,
    u.niveauAdhesion,
    u.civilite,
    u.codeCivilite,
    u.certificationStatus as certificationStatut,
    u.coclico,
    cr.nom as nomSociete,
    cr.siret as siret
FROM ccu_user_v3bis u
LEFT JOIN ccu_adresse_v3bis a ON a.idUser = u.id AND a.principale = 1
LEFT JOIN ccu_email_v3bis m ON m.idUser = u.id AND m.principal = 1
LEFT JOIN ccu_telephone_v3bis t ON t.idUser = u.id AND t.principal = 1
LEFT JOIN user_adresses_all ua ON ua.idUser = u.id
LEFT JOIN user_emails_all ue ON ue.idUser = u.id
LEFT JOIN user_telephones_all ut ON ut.idUser = u.id
LEFT JOIN user_procurations up ON up.idClient = u.id
LEFT JOIN user_reexps ur ON ur.idCCU = u.id
LEFT JOIN client_refpm cr ON cr.id = u.coclico
LEFT JOIN user_dgp ud ON ud.idCCUSouscripteur = u.id;

内容的提问来源于stack exchange，提问作者BTECH-EXPERT

如何优化运行缓慢的CREATE TABLE AS SELECT建表查询？

优化建议 #

优化后示例SQL #

优化建议

优化后示例SQL