mysql left join 很慢的问题

发表于 分类于 ，

mysql left join 很慢的问题

本地用的mysql 8 线上由于使用的云数据库 是mysql5.7

上一个SQL 本地 mysql 8 就0.0x秒 线上要 30+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT
	a.item_id as itemId,
	e.category_name as firCat,
	f.category_name as secCat,
	c.item_barcode as itemBarcode,
	c.cost_price as costPrice,
	c.item_original_price as itemOriginalPrice,
	c.item_name as itemName,
	b.quantity as quantity,
	a.cost_price as groupCostPrice,
	a.item_original_price as groupItemOriginalPrice,
	a.item_sell_price as groupItemSellPrice
FROM
	mall_app_item a
left JOIN mall_app_item_relevancy b ON (a.item_id = b.app_id)
left JOIN mall_item c ON (b.item_id = c.item_id)
left JOIN (
	SELECT
		b.item_id,
		a.category_name
	FROM
		mall_item_category a
	LEFT JOIN mall_item_category_relevancy b ON (
		a.category_id = b.category_id
	)
	WHERE
		a.parent_id = 0
		and a.category_type = '1'
) e on (a.item_id = e.item_id)
left JOIN (
	SELECT
		b.item_id,
		a.category_name
	FROM
		mall_item_category a
	LEFT JOIN mall_item_category_relevancy b ON (
		a.category_id = b.category_id
	)
	WHERE
		a.parent_id != 0
		and a.category_type = '1'
) f on (a.item_id = f.item_id)

WHERE
	1 = 1
AND a.item_store = 48
and a.item_type = '0'

↑↑↑↑↑↑这个sql在 mysql 8.x的版本里面执行是正常的 0.0x的耗时 在mysql 5.7 要 30多秒 本机i5-10400 要60秒 数据库整库备份下来的 数据量 商品就400+ 分类也只有400+ 全站只有2000+的数据量

修改为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SELECT
	a.item_id as itemId,
	e.category_name as firCat,
	f.category_name as secCat,
	c.item_barcode as itemBarcode,
	c.cost_price as costPrice,
	c.item_original_price as itemOriginalPrice,
	c.item_name as itemName,
	b.quantity as quantity,
	a.cost_price as groupCostPrice,
	a.item_original_price as groupItemOriginalPrice,
	a.item_sell_price as groupItemSellPrice
FROM
	mall_app_item a,
mall_app_item_relevancy b,
mall_item c,
(
	SELECT
		b.item_id,
		a.category_name
	FROM
		mall_item_category a
	LEFT JOIN mall_item_category_relevancy b ON (
		a.category_id = b.category_id
	)
	WHERE
		a.parent_id = 0
		and a.category_type = '1'
) e,
(
	SELECT
		b.item_id,
		a.category_name
	FROM
		mall_item_category a
	LEFT JOIN mall_item_category_relevancy b ON (
		a.category_id = b.category_id
	)
	WHERE
		a.parent_id != 0
		and a.category_type = '1'
) f
WHERE
	1 = 1
and a.item_id = b.app_id
and b.item_id = c.item_id
and a.item_id = e.item_id
and a.item_id = f.item_id
AND a.item_store = 48
and a.item_type = '0'

去掉所有的join 修改为多表查询多个结果集 速度就正常了

但是就有问题了 讲道理 Sql join的效率应该比多表查更高一点的

在上面语句中，实际上是创建了多张表的笛卡尔积，所有可能的组合都会被创建出来。在笛卡尔连接中，在上面的例子中，如果有1000商品和1000条商品分类记录，这个查询会先产生1000000个结果，然后通过正确的 ID过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。

为了避免创建笛卡尔积，应该使用INNER JOIN ：