MySQL 性能调优：物联卡数据统计查询优化建议

本文将针对以下查询语句进行性能优化，并提供一些建议：

场景： 统计物联卡信息，包括累计卡数、当月卡数、流量使用情况等。

数据表：

CB_ICM_CARD_INFO：物联卡信息表
cb_icm_msisdn_data_usage：流量使用信息表
cp_user_customer：集团用户信息表（视图）

查询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 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

性能优化建议：

确保表的索引：
- CB_ICM_CARD_INFO：在 SUBS_ID、STATUS、OPEN_TIME 和 CUST_ID 列上创建索引。
- cb_icm_msisdn_data_usage：在 msisdn、query_date 和 cust_id 列上创建索引。
- cp_user_customer：在 cust_id 列上创建索引。
使用连接查询代替子查询：
- 将内层子查询中的 FROM 子句改为 JOIN 子句，并将子查询的结果作为连接条件之一。
使用 EXISTS 代替 COUNT(*)：
- 在子查询中，使用 EXISTS 关键字代替 COUNT(*) 来判断是否存在匹配的记录。
避免使用不必要的函数：
- 在 SELECT 子句中避免使用 TRUNCATE 函数，可以将计算逻辑移到应用程序中进行。
合理使用查询缓存：
- 根据实际情况，考虑是否启用查询缓存。
定期进行性能优化：
- 定期评估查询性能，监控数据库性能指标，并进行必要的调整和优化。

优化后的SQL示例：

SELECT
	ci.CUST_ID AS custId,
	ci.BE_ID AS beId,
	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,
	ci.location,
	du.lastMonthDataB,
	du.activity,
	du.duTotal,
	'2023-06-26 00:00:00'
FROM CB_ICM_CARD_INFO ci
LEFT JOIN cp_user_customer uc ON ci.CUST_ID = uc.cust_id
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
) du ON ci.CUST_ID = du.cust_id
WHERE ci.STATUS != '9'
GROUP BY ci.CUST_ID, ci.BE_ID, ci.location

注意：

以上只是示例，具体的优化方案需要根据实际情况进行调整。
优化前后的性能对比需要进行测试验证。
定期维护数据库，保持索引的有效性。

总结：

本文提供了一些关于物联卡数据统计查询的性能优化建议，希望能对您有所帮助。通过合理使用索引、连接查询、EXISTS 关键字等技术，可以有效提升查询效率，降低数据库负载。