• 日期: 2025-11-03
  • 标签: 常规

MySQL 性能优化：物联卡信息表查询 SQL 优化示例

本文将展示如何优化一个复杂的 MySQL 查询语句，以提升查询性能。该查询涉及三张表：CB_ICM_CARD_INFO、cb_icm_msisdn_data_usage 和 cp_user_customer，其中 CB_ICM_CARD_INFO 和 cb_icm_msisdn_data_usage 位于同一个数据库内，cp_user_customer 作为该数据库的一个视图。

表结构

CREATE TABLE `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
) ENGINE = InnoDB AUTO_INCREMENT = 11515817 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '物联卡信息表' ROW_FORMAT = DYNAMIC;

CREATE TABLE `cb_icm_msisdn_data_usage`  (
  `pk_id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `msisdn` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '号码',
  `job_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '同步任务id',
  `query_date` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '同步月份',
  `data_usage` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '流量使用量',
  `cust_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cust_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `be_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `service_nettype` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '卡类型01 SA
02 NSA',
  PRIMARY KEY (`pk_id`) USING BTREE,
  INDEX `INDEX_MSISDN_QUERYDATE`(`msisdn`, `query_date`) USING BTREE,
  INDEX `INDEX_CUSTID_QUERYDATE`(`cust_id`, `query_date`) USING BTREE COMMENT '集团客户id和月份的索引'
) ENGINE = InnoDB AUTO_INCREMENT = 12672494 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '物联卡信息表' ROW_FORMAT = Dynamic;

CREATE TABLE `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索引'
) ENGINE = InnoDB AUTO_INCREMENT = 196162 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '集团用户信息表' ROW_FORMAT = Dynamic;

原查询语句

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 LEFT JOIN cp_user_customer uc on ci.CUST_ID = uc.cust_id
		WHERE STATUS != '9'
		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. 索引优化:

    • 在 CB_ICM_CARD_INFO 表上创建索引，以加快查询速度。可以考虑创建以下索引：
      • INDEX index_open_time_status (OPEN_TIME, STATUS)
      • INDEX index_cust_id_be_id (CUST_ID, BE_ID)
    • 在 cb_icm_msisdn_data_usage 表上创建索引，以加快查询速度。可以考虑创建以下索引：
      • INDEX index_query_date (query_date)
      • INDEX index_cust_id_query_date (cust_id, query_date)

  2. 子查询优化:

    • 将子查询的结果存储在临时表中，然后在外部查询中使用该临时表。
    • 可以使用 WITH 语句创建临时表，将子查询的结果存储在其中，然后在外部查询中引用该临时表。

  3. 避免使用函数:

    • 在 SELECT 子句中避免使用函数，尤其是在 GROUP BY 子句中使用。函数的使用会导致无法使用索引，影响查询性能。
    • 可以在查询之前将日期字符串转换为日期类型，并将其存储在变量中，然后在查询中使用变量。

  4. 调整 WHERE 子句:

    • 在 WHERE 子句中使用索引列，以提高过滤条件的效率。
    • 可以将 STATUS != '9' 改为 STATUS <> '9'，以避免使用函数操作符。

优化后的查询语句

WITH
  ci AS (
    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,
      uc.location
    FROM CB_ICM_CARD_INFO ci
    LEFT 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
  ),
  du AS (
    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
  )
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 ci
LEFT JOIN du ON ci.CUST_ID = du.cust_id;

注意事项

  • 在实际使用中，还需要根据具体情况对数据库的配置进行调整，并根据实际数据量和硬件配置进行测试和优化。
  • 以上优化方法仅供参考，具体的优化方案需要根据实际情况进行调整。
MySQL 性能优化：物联卡信息表查询 SQL 优化示例

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

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