mysql 多表更新弊端 mysql如何多表关联更新?

转载

mob64ca14150f43 2024-06-27 22:48:47

文章标签 mysql 多表更新弊端 mysql 数据库 SQL 字段 文章分类 MySQL 数据库

最近发现服务器里mysql对CPU的占用明显提高了，昨天晚上把慢查询日志打开，今天过来看到了一个反复出现的慢查询，贴上原SQL：

SELECT
 c.id,
 c.vin,
 c.license_plate,
 c.owner_name,
 c.model,
 c.file_id,
 c.path,
 count( w.id ) AS count,
 count( IF ( w.type = 1, TRUE, NULL ) ) AS type_1,
 count( IF ( w.type = 2, TRUE, NULL ) ) AS type_2,
 count( IF ( w.type = 3, TRUE, NULL ) ) AS type_3,
 count( IF ( w.type = 4, TRUE, NULL ) ) AS type_4,
 count( IF ( w.type = 5, TRUE, NULL ) ) AS type_5,
 count( IF ( w.type = 6, TRUE, NULL ) ) AS type_6,
 count( IF ( w.type = 7, TRUE, NULL ) ) AS type_7,
 count( IF ( w.type = 8, TRUE, NULL ) ) AS type_8,
 count( IF ( w.type = 9, TRUE, NULL ) ) AS type_9,
 count( IF ( w.type = 10, TRUE, NULL ) ) AS type_10,
 count( IF ( w.type = 11, TRUE, NULL ) ) AS type_11,
 count( IF ( w.type = 12, TRUE, NULL ) ) AS type_12,
 count( IF ( w.type = 13, TRUE, NULL ) ) AS type_13,
 count( IF ( w.type = 14, TRUE, NULL ) ) AS type_14,
 count( IF ( w.type = 15, TRUE, NULL ) ) AS type_15,
 count( IF ( w.type = 16, TRUE, NULL ) ) AS type_16,
 count( IF ( w.type = 17, TRUE, NULL ) ) AS type_17,
 count( IF ( w.type = 18, TRUE, NULL ) ) AS type_18,
 count( IF ( w.type = 19, TRUE, NULL ) ) AS type_19,
 count( IF ( w.type = 20, TRUE, NULL ) ) AS type_20,
 count( IF ( w.type = 21, TRUE, NULL ) ) AS type_21 
FROM
 car AS c
 LEFT JOIN warning_message AS w ON w.car_id = c.id 
 AND w.create_time BETWEEN '2021-08-26 00:00:00' 
 AND '2021-08-26 23:59:59' 
WHERE
 c.path LIKE '/1/%' 
 AND c.deleted = 0 
GROUP BY
 c.id,
 c.vin,
 c.license_plate,
 c.owner_name,
 c.model,
 c.path,
 c.file_id 
ORDER BY
 count DESC,
 c.create_time DESC 
 LIMIT 0,
 20;

这段查询主要是展示一段时间内车辆报警汇总并根据报警总数进行排序，查询时长1.2秒。

最终我们目的是获得一个这样的信息：

车辆信息1	车辆信息2	报警总数	第一种报警的数量	第二种报警的数量
粤A12345	本田	99	98	1
沪A12345	丰田	32	2	30

这里面涉及两个表，一个是车辆信息表，一个是根据车辆ID关联到的报警明细表，逻辑很简单，两个表关联后，将需要展示的车辆信息 group by 后得到以车辆为维度的车辆信息，并且使用一系列的 count(IF) 计算出每一种类型的报警数量，并且计算一个 count(报警ID) 作为报警总数，然后对报警总数和车辆的创建时间进行倒序以获得最终的数据。

显然，问题主要是出在了那一系列的 group by 和对报警总数的排序这两个点上，让我们逐个分析解决。

首先是那一系列的group by，实际上我们只需要 c.id 这个聚组就足够达到按车聚组的目的，其它的聚组只是为了这个字段可以在select中使用，我们完全可以通过再联一次car表的形式，将其它的聚组字段去掉：

SELECT
 c.id,
-- 这部分的SQL从 c表 换成了 c1表，c1表在下面left join
 c1.vin,
 c1.license_plate,
 c1.owner_name,
 c1.model,
 c1.file_id,
 c1.path,
 count( w.id ) AS count,
 count( IF ( w.type = 1, TRUE, NULL ) ) AS type_1,
 count( IF ( w.type = 2, TRUE, NULL ) ) AS type_2,
 count( IF ( w.type = 3, TRUE, NULL ) ) AS type_3,
 count( IF ( w.type = 4, TRUE, NULL ) ) AS type_4,
 count( IF ( w.type = 5, TRUE, NULL ) ) AS type_5,
 count( IF ( w.type = 6, TRUE, NULL ) ) AS type_6,
 count( IF ( w.type = 7, TRUE, NULL ) ) AS type_7,
 count( IF ( w.type = 8, TRUE, NULL ) ) AS type_8,
 count( IF ( w.type = 9, TRUE, NULL ) ) AS type_9,
 count( IF ( w.type = 10, TRUE, NULL ) ) AS type_10,
 count( IF ( w.type = 11, TRUE, NULL ) ) AS type_11,
 count( IF ( w.type = 12, TRUE, NULL ) ) AS type_12,
 count( IF ( w.type = 13, TRUE, NULL ) ) AS type_13,
 count( IF ( w.type = 14, TRUE, NULL ) ) AS type_14,
 count( IF ( w.type = 15, TRUE, NULL ) ) AS type_15,
 count( IF ( w.type = 16, TRUE, NULL ) ) AS type_16,
 count( IF ( w.type = 17, TRUE, NULL ) ) AS type_17,
 count( IF ( w.type = 18, TRUE, NULL ) ) AS type_18,
 count( IF ( w.type = 19, TRUE, NULL ) ) AS type_19,
 count( IF ( w.type = 20, TRUE, NULL ) ) AS type_20,
 count( IF ( w.type = 21, TRUE, NULL ) ) AS type_21 
FROM
 car AS c
 LEFT JOIN warning_message AS w ON w.car_id = c.id 
 AND w.create_time BETWEEN '2021-08-26 00:00:00' 
 AND '2021-08-26 23:59:59' 
-- 这里再left join一个c1表进来
 LEFT JOIN car AS c1 ON c1.id = c.id 
WHERE
 c.path LIKE '/1/%' 
 AND c.deleted = 0 
GROUP BY
 c.id
-- 聚组这里就可以删掉原本那一大串的字段，只留一个 c.id
ORDER BY
 count DESC,
 c.create_time DESC 
 LIMIT 0,
 20;

这样我们就只 group by c.id 了

接下来，我们看看排序的问题，显然这里使用c.create_time的排序，完全可以换成c.id的排序，因为c.id是自增主键，自增本身就代表了时间，做以下修改：

-- 原本的排序
ORDER BY
    count DESC,
    c.create_time DESC 
-- 改成
ORDER BY
    count DESC,
    c.id DESC

最大的问题是，这里需要根据一个 count 出来的字段排序，这个排序会引起扫全表，逻辑很简单，只有扫了全表计算出来所有车辆的报警总数，才有办法进行排序

我们可以分成两步执行，先通过一个查询获取车辆报警的20条（根据原limit来），获取到20个car_id再在 where 里使用c.id in (car id list) 的形式查询到这部分的排序:

SELECT
	car_id,
	count(*) AS warning_count 
FROM
	warning_message AS w 
LEFT JOIN car AS c ON c.id = w.car_id
WHERE
	w.create_time BETWEEN '2021-08-26 00:00:00' 
	AND '2021-08-26 23:59:59' 
    -- 数据车辆筛选的一些条件在这里面进行筛选，以得出最终所需的ID
GROUP BY
	car_id
ORDER BY
	warning_count DESC 
	LIMIT 0,20;

这段SQL执行只需要0.01秒，在代码里执行这段sql，取到20个需要的car_id，最终添加到原本的where里：

SELECT
	c.id,
	c1.vin,
	c1.license_plate,
	c1.owner_name,
	c1.model,
	c1.file_id,
	c1.path,
	count( w.id ) AS count,
	count( IF ( w.type = 1, TRUE, NULL ) ) AS type_1,
	count( IF ( w.type = 2, TRUE, NULL ) ) AS type_2,
	count( IF ( w.type = 3, TRUE, NULL ) ) AS type_3,
	count( IF ( w.type = 4, TRUE, NULL ) ) AS type_4,
	count( IF ( w.type = 5, TRUE, NULL ) ) AS type_5,
	count( IF ( w.type = 6, TRUE, NULL ) ) AS type_6,
	count( IF ( w.type = 7, TRUE, NULL ) ) AS type_7,
	count( IF ( w.type = 8, TRUE, NULL ) ) AS type_8,
	count( IF ( w.type = 9, TRUE, NULL ) ) AS type_9,
	count( IF ( w.type = 10, TRUE, NULL ) ) AS type_10,
	count( IF ( w.type = 11, TRUE, NULL ) ) AS type_11,
	count( IF ( w.type = 12, TRUE, NULL ) ) AS type_12,
	count( IF ( w.type = 13, TRUE, NULL ) ) AS type_13,
	count( IF ( w.type = 14, TRUE, NULL ) ) AS type_14,
	count( IF ( w.type = 15, TRUE, NULL ) ) AS type_15,
	count( IF ( w.type = 16, TRUE, NULL ) ) AS type_16,
	count( IF ( w.type = 17, TRUE, NULL ) ) AS type_17,
	count( IF ( w.type = 18, TRUE, NULL ) ) AS type_18,
	count( IF ( w.type = 19, TRUE, NULL ) ) AS type_19,
	count( IF ( w.type = 20, TRUE, NULL ) ) AS type_20,
	count( IF ( w.type = 21, TRUE, NULL ) ) AS type_21 
FROM
	car AS c
	LEFT JOIN warning_message AS w ON w.car_id = c.id 
	AND w.create_time BETWEEN '2021-06-29 00:00:00' 
	AND '2021-08-27 23:59:59'
	LEFT JOIN car AS c1 ON c1.id = c.id 
WHERE
	c.path LIKE '/1/%' 
	AND c.deleted = 0 
	AND c.id IN (
		166963, 167547, 256976, 166964, 167523,
		168357, 167000, 168018, 167992, 256727,
		257354, 257150, 166820, 168373, 167866,
		168765, 167065, 168093, 257297，257298
	) 
GROUP BY
	c.id 
ORDER BY
	count desc,
	c.id DESC 
	LIMIT 0,
	20

执行结果显示，最终的时间是0.06秒，加上获取车辆ID列表的SQL，最终我们以不到0.1秒的时间完成原本1.2秒的工作。附上前后Explain：

优化前：

mysql 多表更新弊端 mysql如何多表关联更新?_mysql