• 日期: 2028-12-09
  • 标签: 常规

MySQL联合查询性能优化：CB_ICM_CARD_INFO和cp_user_customer表

本文介绍了如何优化MySQL联合查询的性能，以CB_ICM_CARD_INFO和cp_user_customer表为例，通过索引优化、子查询优化和函数优化等方法提升查询效率。

表结构

第一张表为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数据相同

CB_ICM_CARD_INFO和cp_user_customer位于两个不同的数据库中，cp_user_customer为CB_ICM_CARD_INFO这张表数据库中的一个视图

原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

性能优化建议

针对这段SQL进行性能调优，可以从以下几个方面入手：

1.索引优化

  • CB_ICM_CARD_INFO表的索引优化：

    • CUST_ID字段和BE_ID字段在联合查询时被使用，可以在这两个字段上建立联合索引。
    • OPEN_TIME字段在WHERE条件中被使用，可以建立单独索引。
    • STATUS字段在WHERE条件中被使用，可以建立单独索引。
    • SERVICE_NETTYPE字段在多个SUM函数中被使用，可以建立单独索引。

  • cp_user_customer表的索引优化：

    • cust_id字段在JOIN条件中被使用，可以建立单独索引。

  • cb_icm_msisdn_data_usage表的索引优化：

    • query_date字段在WHERE条件中被使用，可以建立单独索引。
    • cust_id字段在GROUP BY和JOIN条件中被使用，可以建立单独索引。

2.子查询优化

子查询的效率通常较低，可以考虑将子查询改写为JOIN查询。例如，可以将ci子查询中的cp_user_customer表改为JOIN查询，如下所示：

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 AND ci.BE_ID = uc.be_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

3.函数优化

TRUNCATE函数会对每一行数据进行一次计算，效率较低。可以考虑将计算结果存储在表中，在查询时直接读取结果。例如，可以将ci子查询中的TRUNCATE函数计算结果存储在一个新表中，然后在主查询中读取。

此外，还可以考虑以下优化方法：

  • 使用EXPLAIN语句分析SQL执行计划，找到性能瓶颈，针对性优化。
  • 使用FORCE INDEX语句强制使用特定索引。
  • 避免使用SELECT *，只查询需要的字段。
  • 尽量减少数据读取量，例如使用LIMIT语句限制查询结果数量。
  • 使用UNION ALL代替UNION，避免重复数据检查。
  • 使用JOIN连接代替子查询，提高查询效率。
  • 使用索引提示优化索引选择。
  • 优化数据库配置参数。

通过以上优化方法，可以有效提升MySQL联合查询的性能，提高查询效率。

MySQL联合查询性能优化：CB_ICM_CARD_INFO和cp_user_customer表

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

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