MySQL:in与join的优化

曾经终败给现在 2022-09-22 11:47 162阅读 0赞

原sql：

mysql> select count(*) as num from gp as p where p.goods_id in (select goods_id from gg where gg.category_id = 62) and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
    +-----+
    | num |
    +-----+
    |  40 |
    +-----+
    1 row in set (0.70 sec)
    
    mysql> explain select count(*) as num from gp as p where p.goods_id in (select goods_id from gg where gg.category_id = 62) and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
    +----+--------------------+----------+-----------------+------------------------------+---------+---------+-------+--------+-------------+
    | id | select_type        | table    | type            | possible_keys                | key     | key_len | ref   | rows   | Extra       |
    +----+--------------------+----------+-----------------+------------------------------+---------+---------+-------+--------+-------------+
    |  1 | PRIMARY            | p        | ref             | is_stop,is_check_2           | is_stop | 1       | const | 134703 | Using where |
    |  2 | DEPENDENT SUBQUERY | gg       | unique_subquery | PRIMARY,category_id,goods_id | PRIMARY | 4       | func  |      1 | Using where |
    +----+--------------------+----------+-----------------+------------------------------+---------+---------+-------+--------+-------------+
    2 rows in set (0.00 sec)

in改为join后：

mysql> select count(*) as num from gp as p,(select goods_id from gg where gg.category_id = 62) g where p.goods_id = g.goods_id  and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
    +-----+
    | num |
    +-----+
    |  40 |
    +-----+
    1 row in set (0.01 sec)
    
    mysql> explain select count(*) as num from gp as p,(select goods_id from gg where gg.category_id = 62) g where p.goods_id = g.goods_id  and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
    +----+-------------+------------+------+-----------------------------+-------------+---------+------------+------+-------------+
    | id | select_type | table      | type | possible_keys               | key         | key_len | ref        | rows | Extra       |
    +----+-------------+------------+------+-----------------------------+-------------+---------+------------+------+-------------+
    |  1 | PRIMARY     | <derived2> | ALL  | NULL                        | NULL        | NULL    | NULL       |  116 |             |
    |  1 | PRIMARY     | p          | ref  | goods_id,is_stop,is_check_2 | goods_id    | 4       | g.goods_id |    1 | Using where |
    |  2 | DERIVED     | gg         | ref  | category_id                 | category_id | 4       |            |  116 | Using index |
    +----+-------------+------------+------+-----------------------------+-------------+---------+------------+------+-------------+
    3 rows in set (0.00 sec)

MySQL中，可以用join的就不要用子查询