• 日期: 2025-06-10
  • 标签: 常规

MySQL 性能优化：联合查询两张表并进行数据统计

本文提供 MySQL 性能优化建议，针对联合查询两张表并进行数据统计的场景，通过优化 SQL 语句、创建索引、减少函数使用等方法，提升查询效率。

问题描述：

有两张数据库中的表，需要联合两张表进行一个查询，并对数据进行统计分析。

表结构：

  • CB_ICM_CARD_INFO
`PK_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`SUBS_ID` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID',
`BE_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省ID',
`MSISDN` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户号码',
`IMSI` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'IMSI号码',
`ICCID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ICCID号码',
`SERVICE_NETTYPE` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '通信服务网络类型 01:5G SA   02:5G NSA',
`RES_TYPE` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源类型 01：SIM卡  02：USIM卡',
`STATUS` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态 1：待激活 2：已激活 4：停机  6：可测试  7：库存  8：预销户  9：已销户',
`OPEN_TIME` datetime NULL DEFAULT NULL COMMENT '开户时间 格式：YYYYMMDDHHMMSS',
`ACTIVE_TIME` datetime NULL DEFAULT NULL COMMENT '激活时间 格式：YYYYMMDDHHMMSS',
`CUST_ID` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客户ID',
`ACCT_ID` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '账号ID',
`OPER_TYPE` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作类型 01：新加  02：删除  03：修改  04：保留',
`UPDATE_TIME` datetime NULL DEFAULT NULL COMMENT '修改时间 格式：YYYYMMDDHHMMSS',
`GROUP_NAME` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组信息',
PRIMARY KEY (`PK_ID`) USING BTREE,
UNIQUE INDEX `SUB_INDEX`(`SUBS_ID`) USING BTREE,
INDEX `INDEX_CUSTID_BEID`(`CUST_ID`, `BE_ID`) USING BTREE,
INDEX `index_imsi`(`IMSI`) USING BTREE,
INDEX `index_open_time`(`OPEN_TIME`) USING BTREE
  • cp_user_customer
`pk_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '用户ID',
`cust_code` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团客户编码',
`cust_name` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '集团客户名称',
`cust_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团客户ID',
`be_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '省份ID',
`account_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'CTID',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`tel` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号码',
`location` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '地市信息，同osms_platform中的CB_ODC_AREA表',
PRIMARY KEY (`pk_id`) USING BTREE,
INDEX `index_user_id`(`user_id`) USING BTREE COMMENT '用户ID索引',
INDEX `index_cust_id`(`cust_id`) USING BTREE COMMENT 'custid索引',
INDEX `index_cust_code`(`cust_code`) USING BTREE COMMENT 'custCode索引'

关联关系：

CB_ICM_CARD_INFO 的 CUST_ID 和 cp_user_customer 的 cust_id 数据相同。

原始 SQL 语句：

SELECT
    ci.CUST_ID AS custId,
    ci.BE_ID AS beId,
    ci.accumulateCard,
    ci.onTimeCard,
    (ci.addedSAcard + ci.addedNSAcard) AS lastMonthCard,
    ci.thisMonthCard,
    ci.saCard,
    ci.saCardWeek,
    ci.addedSAcard,
    ci.addedSAcardWeek,
    ci.nsaCard,
    ci.nsaCardWeek,
    ci.addedNSAcard,
    ci.addedNSAcardWeek,
    TRUNCATE(du.lastMonthDataB / 1024 / 1024 / 1024,2) AS lastMonthData,
    IFNULL(du.activity,0),
    '202305',
    ci.location,
    IFNULL(du.duTotal,0) as du_total,
    '2023-06-26 00:00:00'
FROM (
    SELECT
        ci.CUST_ID,
        ci.BE_ID,
        SUM(CASE WHEN OPEN_TIME <= '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS accumulateCard,
        SUM(CASE WHEN OPEN_TIME <= '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS onTimeCard,
        SUM(CASE WHEN OPEN_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS thisMonthCard,
        SUM(CASE WHEN SERVICE_NETTYPE = '01' AND OPEN_TIME <= '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS saCard,
        SUM(CASE WHEN SERVICE_NETTYPE = '01' AND OPEN_TIME <= '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS saCardWeek,
        SUM(CASE WHEN SERVICE_NETTYPE = '01' AND OPEN_TIME BETWEEN '2023-05-01 00:00:00' AND '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS addedSAcard,
        SUM(CASE WHEN SERVICE_NETTYPE = '01' AND OPEN_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS addedSAcardWeek,
        SUM(CASE WHEN SERVICE_NETTYPE = '02' AND OPEN_TIME <= '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS nsaCard,
        SUM(CASE WHEN SERVICE_NETTYPE = '02' AND OPEN_TIME <= '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS nsaCardWeek,
        SUM(CASE WHEN SERVICE_NETTYPE = '02' AND OPEN_TIME BETWEEN '2023-05-01 00:00:00' AND '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS addedNSAcard,
        SUM(CASE WHEN SERVICE_NETTYPE = '02' AND OPEN_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS addedNSAcardWeek,
        uc.location
    FROM CB_ICM_CARD_INFO ci 
    INNER JOIN cp_user_customer uc ON ci.CUST_ID = uc.cust_id
    WHERE ci.STATUS != '9' AND ci.OPEN_TIME <= '2023-06-26 00:00:00'
    GROUP BY ci.CUST_ID, ci.BE_ID, uc.location
) AS ci
LEFT JOIN (
    SELECT
        cust_id,
        SUM(CASE WHEN data_usage != '' THEN 1 ELSE 0 END) AS useNum,
        count(*) AS duTotal,
        SUM(data_usage) AS lastMonthDataB,
        TRUNCATE(SUM(CASE WHEN data_usage != '' THEN 1 ELSE 0 END) * 100 / count(*), 2) AS activity
    FROM cb_icm_msisdn_data_usage
    WHERE query_date = '202305'
    GROUP BY cust_id
) AS du ON ci.CUST_ID = du.cust_id

性能优化建议：

  1. 优化 WHERE 条件： 将 WHERE 条件中的 ci.STATUS != '9' 改为 ci.STATUS <> '9'，避免使用函数，提高查询效率。

  2. 创建联合索引： 在 CB_ICM_CARD_INFO 表的 CUST_ID 和 BE_ID 字段上创建联合索引，以加快 JOIN 操作的速度。

  3. 创建索引： 在 cp_user_customer 表的 cust_id 字段上创建索引，以加快 JOIN 操作的速度。

  4. 移动子查询字段： 将子查询中的 uc.location 移动到最外层的 SELECT 语句中，避免子查询 GROUP BY 的开销。

  5. 合并数据库： 考虑将 CB_ICM_CARD_INFO 表和 cp_user_customer 表合并到同一个数据库中，避免跨数据库 JOIN 的开销。

  6. 优化日期类型： 考虑将 cb_icm_msisdn_data_usage 表中的 query_date 字段改为日期类型，以避免使用函数转换日期类型的开销。

  7. 创建索引： 考虑在 cb_icm_msisdn_data_usage 表的 cust_id 字段上创建索引，以加快子查询中的 JOIN 操作的速度。

  8. 优化 SUM 函数： 考虑将子查询中的 SUM 函数改为 COUNT 函数，以避免使用 SUM 函数的开销。

优化后的 SQL 语句：

SELECT
    ci.CUST_ID AS custId,
    ci.BE_ID AS beId,
    ci.accumulateCard,
    ci.onTimeCard,
    (ci.addedSAcard + ci.addedNSAcard) AS lastMonthCard,
    ci.thisMonthCard,
    ci.saCard,
    ci.saCardWeek,
    ci.addedSAcard,
    ci.addedSAcardWeek,
    ci.nsaCard,
    ci.nsaCardWeek,
    ci.addedNSAcard,
    ci.addedNSAcardWeek,
    TRUNCATE(du.lastMonthDataB / 1024 / 1024 / 1024, 2) AS lastMonthData,
    IFNULL(du.activity, 0),
    '202305',
    uc.location,
    IFNULL(du.duTotal, 0) AS du_total,
    '2023-06-26 00:00:00'
FROM (
    SELECT
        ci.CUST_ID,
        ci.BE_ID,
        SUM(CASE WHEN ci.OPEN_TIME <= '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS accumulateCard,
        SUM(CASE WHEN ci.OPEN_TIME <= '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS onTimeCard,
        SUM(CASE WHEN ci.OPEN_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS thisMonthCard,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '01' AND ci.OPEN_TIME <= '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS saCard,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '01' AND ci.OPEN_TIME <= '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS saCardWeek,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '01' AND ci.OPEN_TIME BETWEEN '2023-05-01 00:00:00' AND '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS addedSAcard,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '01' AND ci.OPEN_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS addedSAcardWeek,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '02' AND ci.OPEN_TIME <= '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS nsaCard,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '02' AND ci.OPEN_TIME <= '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS nsaCardWeek,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '02' AND ci.OPEN_TIME BETWEEN '2023-05-01 00:00:00' AND '2023-05-31 23:59:59' THEN 1 ELSE 0 END) AS addedNSAcard,
        SUM(CASE WHEN ci.SERVICE_NETTYPE = '02' AND ci.OPEN_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-26 00:00:00' THEN 1 ELSE 0 END) AS addedNSAcardWeek
    FROM CB_ICM_CARD_INFO ci
    INNER JOIN cp_user_customer uc ON ci.CUST_ID = uc.cust_id
    WHERE ci.STATUS <> '9' AND ci.OPEN_TIME <= '2023-06-26 00:00:00'
    GROUP BY ci.CUST_ID, ci.BE_ID
) AS ci
LEFT JOIN (
    SELECT
        cust_id,
        COUNT(CASE WHEN data_usage != '' THEN 1 ELSE 0 END) AS useNum,
        COUNT(*) AS duTotal,
        SUM(data_usage) AS lastMonthDataB,
        TRUNCATE(COUNT(CASE WHEN data_usage != '' THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) AS activity
    FROM cb_icm_msisdn_data_usage
    WHERE query_date = '202305'
    GROUP BY cust_id
) AS du ON ci.CUST_ID = du.cust_id

注意：

  • 以上优化建议并非所有情况都适用，需要根据实际情况进行调整。
  • 优化后的 SQL 语句需要进行测试，以确保其正确性和性能提升。
  • 建议在进行性能优化之前，先对数据库进行备份。

希望本文能对您优化 MySQL 查询语句有所帮助！

MySQL 性能优化：联合查询两张表并进行数据统计

原文地址: https://www.cveoy.top/t/topic/oZuh 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录