mysql子查询复杂操作,mysql 联表操作及子查询操作注意点

背景：写了一个左联表修改表的sql语句，中间包含了子查询；让同事帮忙评审后，做了些修改，并给了我很重要的建议

1.能不用子查询的尽量不用子查询，将sql结构调整成不含子查询的

2.有些情况可以考虑自连接

3.在使用左右连接的时候，一定要考虑到左右表的区别，决定是应用左联还是右联

修改前sql

UPDATE ecm_goods_status AS egs

LEFT JOIN (

SELECT goods_id,ONLINE,ma_type

FROM

ecm_market_activity_apply

WHERE

goods_id IN (SELECT goods_id FROM ecm_goods_status WHERE if_stock = 1)

AND STATUS = 2

AND ma_type IN (21)

) AS emaa ON egs.goods_id = emaa.goods_id

SET egs.if_stock = emaa.ONLINE修改后sql

UPDATE ecm_goods_status AS egs,ecm_market_activity_apply AS emaa

SET egs.if_stock = emaa.ONLINE

WHERE egs.goods_id=emaa.goods_id AND egs.goods_id=egs.goods_id

AND emaa.STATUS=2 AND emaa.ma_type IN(21) AND egs.if_stock=1

修改前的sql存在问题：

1.选择左联接是错误的，这样会修改ecm_goods_status表的所有if_stock字段值(左联数据以左表为准，没有结果的以null填充)。应该改成右联，这样修改的字段以右临时表的数据记录数为准。

2.存在字查询和临时表，如果转成select语句，explain一下可以如

EXPLAIN SELECT * FROM ecm_goods_status AS egs

-- UPDATE ecm_goods_status AS egs

RIGHT JOIN (

SELECT goods_id,ONLINE,ma_type

FROM ecm_market_activity_apply

WHERE

goods_id IN (SELECT goods_id FROM ecm_goods_status WHERE if_stock = 1)

AND STATUS = 2

AND ma_type IN (21)

) AS emaa ON egs.goods_id = emaa.goods_id

-- SET egs.if_stock = emaa.ONLINE

修改后的sql变成select语句，explain后

EXPLAIN SELECT * FROM ecm_goods_status AS egs,ecm_market_activity_apply AS emaa

WHERE egs.goods_id=emaa.goods_id AND egs.goods_id=egs.goods_id AND emaa.STATUS=2

AND emaa.ma_type IN(21) AND egs.if_stock=1;

可以发现运行的步骤变得更少，sql语句也更简洁了。

总结：

1.在以后遇到联表操作时需要考虑到左右联时考虑表位置的放置

2.遇到子查询的场景时，尽量想办法避免(子查询内的语句是不会走索引的，当子查询涉及的表数据量很大时，没有索引很可能会让差些的sql服务器宕掉)