Mysql之sql语句优化：explain

╰+攻爆jí腚メ 2022-07-12 07:59 332阅读 0赞

*  explain的列分析
    
     *   *   *  select\_type 查询类型
             *  table 查询针对的表
             *  possible\_key 可能用到的索引
             *  key 最终用的索引
             *  key\_len 使用的索引的最大长度
             *  type列 是指查询的方式 非常重要是分析查数据过程的重要依据
             *  ref列 指连接查询时 表之间的字段引用关系
             *  rows 是指估计要扫描多少行
             *  extra

# explain的列分析 #

id: 代表select 语句的编号, 如果是连接查询,表之间是平等关系, select 编号都是1,从1开始. 如果某select中有子查询,则编号递增.

mysql> explain select goods_id,goods_name from  goods where goods_id in (sele
    ct goods_id from goods where cat_id=4) \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: goods
     type: unique_subquery
    possible_keys: PRIMARY,cat_id
     key: PRIMARY
     key_len: 3
     ref: func
     rows: 1
     Extra: Using where
    2 rows in set (0.00 sec)

#### select\_type: 查询类型 ####

1.  simple (不含子查询)
2.  primary (含子查询或派生查询)
3.  subquery (非from子查询)
4.  derived (from型子查询)
5.  union
6.  union result

#### table: 查询针对的表 ####

有可能是  
实际的表名 如select \* from t1;  
表的别名 如 select \* from t2 as tmp;  
derived 如from型子查询时  
null 直接计算得结果,不用走表

#### possible\_key: 可能用到的索引 ####

注意: 系统估计可能用的几个索引,但最终,只能用1个.

#### key : 最终用的索引. ####

#### key\_len: 使用的索引的最大长度 ####

#### type列: 是指查询的方式, 非常重要,是分析”查数据过程”的重要依据 ####

可能的值  
\- all: 意味着从表的第1行,往后,逐行做全表扫描.,运气不好扫描到最后一行.

例: 把goods\_name列上的索引去掉, 并根据goods\_name来查询

mysql> explain select goods_name from goods where goods_name='诺基亚N85' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 31
            Extra: Using where
    1 row in set (0.00 sec)

*  index: 比all性能稍好一点，  
    
    
    > 通俗的说: all 扫描所有的数据行,相当于data\_all index 扫描所有的索引节点,相当于index\_all

２种情况可能出现：  
１：索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描.

mysql> explain select goods_id from goods where goods_id=1 or goods_id+1>20
    \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: index
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 3
     ref: NULL
     rows: 31
     Extra: Using where; Using index
    1 row in set (0.00 sec)
    
    mysql> explain select goods_id,click_count from  goods where goods_id=1 or go
    ods_id+1>20 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: ALL
    possible_keys: PRIMARY
     key: NULL
     key_len: NULL
     ref: NULL
     rows: 31
     Extra: Using where
    1 row in set (0.00 sec)

2: 是利用索引来进行排序,但取出所有的节点 select goods\_id from goods order by goods\_id desc; 分析: 没有加where条件, 就得取所有索引节点,同时,又没有回行,只取索引节点. 再排序,经过所有索引节点.

mysql> explain select goods_id from goods order by goods_id asc\G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: index
    possible_keys: NULL
     key: PRIMARY
     key_len: 3
     ref: NULL
     rows: 31
     Extra: Using index
    1 row in set (0.00 sec)

*  range: 意思是查询时,能根据索引做范围的扫描

mysql> explain select goods_id,goods_name,shop_price from  goods where goods
    id >25 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: range
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 3
     ref: NULL
     rows: 8
     Extra: Using where
    1 row in set (0.00 sec)

*  ref 意思是指 通过索引列,可以直接引用到某些数据行

mysql> explain select goods_id,goods_name from  goods where cat_id=4 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: ref
    possible_keys: cat_id
     key: cat_id
     key_len: 2
     ref: const
     rows: 3
     Extra:
    1 row in set (0.00 sec)

在这个例子中,通过cat\_id索引 指向N行goods数据,来查得结果.

*  eq\_ref 是指,通过索引列,直接引用某1行数据  
    常见于连接查询中

mysql> explain select goods_id,shop_price from  goods innert join ecs_catego
    y using(cat_id) where goods_id> 25 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: innert
     type: range
    possible_keys: PRIMARY,cat_id
     key: PRIMARY
     key_len: 3
     ref: NULL
     rows: 8
     Extra: Using where
    *************************** 2. row ***************************
     id: 1
      select_type: SIMPLE
     table: ecs_category
     type: eq_ref
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 2
     ref: shop.innert.cat_id
     rows: 1
     Extra: Using index
    2 rows in set (0.00 sec)

*  const, system, null 这3个分别指查询优化到常量级别, 甚至不需要查找时间.

一般按照主键来查询时,易出现const,system  
或者直接查询某个表达式,不经过表时, 出现NULL

mysql> explain select goods_id,goods_name,click_count from  goods wher
    _id=4 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: const
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 3
     ref: const
     rows: 1
     Extra:
    1 row in set (0.00 sec)
    
    mysql> explain select max(goods_id) from  goods \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: NULL
     type: NULL myisam表的max,min,count在表中优化过,不需要\真正查找,为NULL
    possible_keys: NULL
     key: NULL
     key_len: NULL
     ref: NULL
     rows: NULL
     Extra: Select tables optimized away
    1 row in set (0.00 sec)

#### ref列 指连接查询时, 表之间的字段引用关系. ####

mysql> explain select goods_id,cat_name,goods_name from  goods inner join ec
    _category using(cat_id) where ecs_category.cat_name='' \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: ALL
    possible_keys: cat_id
     key: NULL
     key_len: NULL
     ref: NULL
     rows: 31
     Extra:
    *************************** 2. row ***************************
     id: 1
      select_type: SIMPLE
     table: ecs_category
     type: eq_ref
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 2
     ref: shop. goods.cat_id
     rows: 1
     Extra: Using where
    2 rows in set (0.00 sec)

#### rows : 是指估计要扫描多少行. ####

#### extra: ####

1.  index: 是指用到了索引覆盖,效率非常高
2.  using where 是指光靠索引定位不了,还得where判断一下
3.  using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
4.  using filesort : 文件排序(文件可能在磁盘,也可能在内存), (?????

select sum(shop\_price) from goods group by cat\_id(???? 这句话,用到了临时表和文件排序)