左链接查询优化一例

August 16, 2016

大部分时候,两个大表做join的时候,where条件前置不但可以节省扫描行数,还能避免创建磁盘临时文件:
优化前：
```
SELECT g.goods_id, g.add_time, g.goods_name, g.market_price, g.is_new
	, g.is_best, g.is_hot, g.shop_price AS org_price
	, IFNULL(mp.user_price, g.shop_price * '1.00') AS shop_price
	, g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img
	, g.goods_brief, g.goods_type
FROM `ecshop`.`fp_goods` g
	LEFT JOIN `ecshop`.`fp_member_price` mp
	ON mp.goods_id = g.goods_id
		AND mp.user_rank = '1'
WHERE (g.is_delete = 0
	AND g.is_on_sale = 1
	AND g.is_alone_sale = 1
	AND 1)
ORDER BY goods_id DESC
LIMIT 200, 40
```

优化后
```
SELECT g.goods_id, g.add_time, g.goods_name, g.market_price, g.is_new
	, g.is_best, g.is_hot, g.shop_price AS org_price
	, IFNULL(mp.user_price, g.shop_price * '1.00') AS shop_price
	, g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img
	, g.goods_brief, g.goods_type
FROM (
	SELECT *
	FROM `ecshop`.`fp_goods`
	ORDER BY goods_id DESC
	LIMIT 200, 40
) g
	LEFT JOIN `ecshop`.`fp_member_price` mp
	ON mp.goods_id = g.goods_id
		AND mp.user_rank = '1'
```

优化前：
```
SELECT g.goods_id, g.goods_name, g.market_price, g.is_new, g.is_best
	, g.is_hot, g.shop_price AS org_price
	, IFNULL(mp.user_price, g.shop_price * '1') AS shop_price
	, g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img
	, g.goods_brief, g.goods_type
FROM `ecshop`.`fp_goods` g
	LEFT JOIN `ecshop`.`ecs_member_price` mp
	ON mp.goods_id = g.goods_id
		AND mp.user_rank = '0'
	LEFT JOIN `ecshop`.`fp_brand` b ON b.brand_id = g.brand_id
WHERE (b.brand_id NOT IN ('406', '13', '7')
	AND g.is_delete = 0
	AND g.is_on_sale = 1
	AND g.is_alone_sale = 1
	AND (1
		AND (((goods_name LIKE '%Phillip%'
			OR goods_sn LIKE '%Phillip%'
			OR keywords LIKE '%Phillip%'))
		OR ((goods_name LIKE '%plein%'
			OR goods_sn LIKE '%plein%'
			OR keywords LIKE '%plein%'))
		OR ((goods_name LIKE '%womens%'
			OR goods_sn LIKE '%womens%'
			OR keywords LIKE '%womens%'))
		OR ((goods_name LIKE '%tshirt%'
			OR goods_sn LIKE '%tshirt%'
			OR keywords LIKE '%tshirt%')))))
ORDER BY goods_number ASC
LIMIT 32, 32
```
优化后
```
SELECT g.goods_id, g.goods_name, g.market_price, g.is_new, g.is_best
	, g.is_hot, g.shop_price AS org_price
	, IFNULL(mp.user_price, g.shop_price * '1') AS shop_price
	, g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img
	, g.goods_brief, g.goods_type
FROM (
	SELECT *
	FROM `ecshop`.`fp_goods`
	WHERE MATCH (goods_sn, goods_name, keywords) AGAINST ('Phillip plein womens tshirt')
		AND (is_delete = 0
		AND is_on_sale = 1
		AND is_alone_sale = 1) ORDER BY goods_number ASC
LIMIT 32, 32
) g
	LEFT JOIN `ecshop`.`ecs_member_price` mp
	ON mp.goods_id = g.goods_id
		AND mp.user_rank = 0
	LEFT JOIN `ecshop`.`fp_brand` b ON b.brand_id = g.brand_id
WHERE b.brand_id NOT IN (406, 13, 7)
```

添加新评论