使用explain分析语句查询效率。

in型子查询陷阱

in型子查询和连接查询执行计划比较。

需求

查询ecs_shop库下的6号栏目下的商品。

使用in型子查询进行查询

select goods_id,goods_name
from goods 
where cat_id in (select cat_id from category where parent_id = 6);

我们使用explain对上述语句进行分析。如下:

explain select goods_id,goods_name from goods where cat_id in(select cat_id from category where parent_id = 6)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: goods
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: category
         type: unique_subquery
possible_keys: PRIMARY,parent_id
          key: PRIMARY
      key_len: 2
          ref: func
         rows: 1
        Extra: Using where

我们可以看到在goods表上进行了全表扫描,并逐行与category表进行对照,看 parent_id = 6 是否成立。

原因: MySQL的查询优化器,针对in型做了优化,被改成了exists的执行效果,当goods表越大,查询速度越慢。

使用连接查询替代子查询优化

explain select goods_id,goods_name from goods inner join (select cat_id from category where parent_id = 6) as tmp on goods.cat_id = tmp.cat_id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: goods
         type: ref
possible_keys: cat_id
          key: cat_id
      key_len: 2
          ref: tmp.cat_id
         rows: 6
        Extra:
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: category
         type: ref
possible_keys: parent_id
          key: parent_id
      key_len: 3
          ref:
         rows: 4
        Extra: Using where

通过上述使用explain分析执行计划,可以看出减少了goods表的全表扫描,这将在goods表数据大的情况下有非常好的效果。


最后编辑: 于 3年前

标签

评论列表(0)

    暂无评论