当前位置:天才代写 > tutorial > 数据库教程 > Mysql优化技巧 explain工具分析查询语句

Mysql优化技巧 explain工具分析查询语句

2021-02-11 12:46 星期四 所属: 数据库教程 浏览:18

explain是mysql的执行计划查询专用工具,用以剖析一个查看句子的特性

 

其三种文件格式以下:

explain select …

explain extended select …   # 该指令将执行计划反汇编成select句子,运作 show warnings 能够获得被mysql优化后的查看句子

explain partitions select…    # 该指令用以分区表的explain指令

 

explain 指令的輸出有下列字段名
id| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra    

 

下边详尽描述explain輸出的各类內容:

 

id 
表明查看中实行select子句或实际操作表的次序

id为几就意味着是第几个select

假如id同样,则实行的顺序由上升下,比如 

mysql> explain select t.id,t.name,count(*) as arts_count from arts a join type t on a.tid=t.id where is_send=0 group by t.id\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: tid
          key: tid
      key_len: 5
          ref: art.t.id
         rows: 978
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

 

她们的id同样,因此 实行次序是第一行中的表再是第二行中的表

假如id不一样,id大的优先高,先强制执行

select_type  
表明每一个查看的类 型,有一下几类种类: 
simple,primary,subquery,derived,union unoim result

simple : 查看中不包括子查询或union查看的种类,简易种类
primary: 查看中若包括一切繁杂的子一部分,则最表层查看被标识为primary
subquery:select或where后包括了子查询,则标识为subquery
derived: from后包括了子查询则标识为derived

从 union 表格中获得結果的select标识为 union result

第二个select出現在union以后被标识为union ,也就是union以后的select是Union种类,但union以前的那一个select并不是union只是primary,全部union句子是一个union result
如 :

  select * from a union select * from b         #这也是最基本的 union 联合查询

若union包括在from子句的子查询中,则表层select被标识为derived

比如:

explain select id,title from arts where id<1500  union select id,title from arts where id>70000 \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: arts
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 394
     filtered: 100.00
        Extra: Using index condition
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: arts
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2612
     filtered: 100.00
        Extra: Using index condition
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
3 rows in set, 1 warning (0.00 sec)

又比如:

explain select d1.name,(select id from t3) d2 from (select id,name from t1 where other_column="") d1 union (select name,id from t2);

获得結果以下

id  select_type     table
1   primary         <derived3>
3   derived         t1
2   subquery        t3
4   union           t2
null union result   <union1,4>

第一行:id为1,表明它是第一个select,select_type为primary表明该查看是一个表层查看,table列是<drived3>表明查看結果来自于一个衍化表,在其中3意味着这一该查看衍化来源于第三个select,即id为3的select

第二行:id为3,是第三个select,由于查看句子是在from当中,因此 是derived

第三行:id为2,第二个select是做为子查询

第四行:id为4,查询第四个select,在union关键词后边,它是union种类且它是最开始实行的

第五行:id为null 表明它是一个临时表,沒有select,它意味着的是全部句子,table列的<union1,4>表明它是对第一个和第四个select的結果的union实际操作

type 
在表格中寻找所需行的方法,又叫浏览方法:
有以下种类:
all/index/range/ref/eq_ref/const,system/null 

all 全表扫描仪,当不应用where标准开展查看或是应用where可是做为标准的字段名并不是数据库索引字段名则为全表扫描仪

index 查看的字段名仅有主键,并且是获得全部行的主键的状况,不可以有where,如 select id from t1;

range 数据库索引范畴扫描仪,where标准是数据库索引并且是范畴检索的状况,如 select * from t1 where id>10;  也有in,between and ;假如检索标准并不是数据库索引为标准,便会变为全表扫描仪ref 非唯一性数据库索引扫描仪 即应用一般数据库索引或是唯一索引的非唯一作为前缀做为where标准检索
比如:

# 我设定了一个文章内容表的tid字段名(归类id)为一般数据库索引
explain select * from arts where tid=6;

 ---- ------------- ------- ------------ ------ --------------- ------ ---------
 ------- ------ ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len
| ref   | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ------ ---------
 ------- ------ ---------- ------- 
|  1 | SIMPLE      | arts  | NULL       | ref  | tid           | tid  | 5
| const | 2189 |   100.00 | NULL  |
 ---- ------------- ------- ------------ ------ --------------- ------ ---------
 ------- ------ ---------- ------- 





explain select * from arts where title="文章标题";          #这时的 type 是 all 全表扫描仪





explain select t.name,arts.*  from arts join type t on arts.tid=t.id;       #对文章内容表和归类表开展多表联查

 ---- ------------- ------- ------------ ------ --------------- ------ ---------
 ---------- ------- ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len
| ref      | rows  | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ------ ---------
 ---------- ------- ---------- ------- 
|  1 | SIMPLE      | t     | NULL       | ALL  | PRIMARY       | NULL | NULL
| NULL     |     9 |   100.00 | NULL  |
|  1 | SIMPLE      | arts  | NULL       | ref  | tid           | tid  | 5
| art.t.id | 24064 |   100.00 | NULL  |
 ---- ------------- ------- ------------ ------ --------------- ------ ---------
 ---------- ------- ---------- ------- 

# 結果对归类表有全表扫描仪,对文章内容表有数据库索引扫描仪





explain select * from arts where tid>6;         # 这时的type就变成range了

eq_ref   唯一性数据库索引扫描仪,针对每一个数据库索引键,表格中仅有一条纪录与之配对。常见于主键和唯一索引扫描仪。

const、system   当mysql对查看开展提升并转换为一个变量定义的情况下type便是const种类,比如应用主键或是唯一键做为where的标准查看,这时mysql会将这一查看結果转换为一个变量定义。system是const的一个充分必要条件,当查看的表仅有一行数据信息的情况下,type便是system种类

比如:
explain select * from arts where id=1300;    #type 为const

null     Mysql 在提升全过程中溶解句子,查看时乃至无需浏览表或是数据库索引
比如:
explain select max(id) from arts;
explain select count(*) from arts;

possible_keys

强调mysql能够应用哪一个数据库索引在表格中寻找行,牵涉到若字段名上存有数据库索引则数据库索引会被列举,但不一定会被查看采用

 

key

表明在查看中具体应用到的数据库索引
如:
explain select title from arts where id=750000;

假如 要查看的字段名就是这个数据库索引则 key 为该数据库索引字段,但possible_keys为null
select id from arts

 

ref

多表联查时的联接配对标准

 

rows

是估计实行这条句子必须扫描仪的纪录数
比如 我的arts表有60多万条数据信息,我的tid字段名有创建数据库索引的

explain select * from arts where tid=6;

結果 rows为325186 ,要扫描仪三十多万条

当我们删掉tid数据库索引的情况下再实行:

explain select * from arts where tid=6;

rows: 614436   变为扫描仪60多万元跳,变成了全表扫描仪

因此 查询特性 rows是很重要的一个指标值,而创建数据库索引和应用where的范畴查看能够降低扫描仪的个数

假如用主键去查,发觉rows只有1罢了

但实际上,不一定扫描仪的个数越低就查的越来越快,比如
我这个表有60万 的数据信息
select title from arts where tid=6 and id>500000
select title from arts where id>500000 and tid=6;
select title from arts where id in (select id from arts where id>500000) and tid=6;

这前两根表明的rows全是311430,第三条表明的是203329和1,可是第一个用了40s,第二个用了16s,第三个仅用了1.3秒,她们的結果是一样的

这是由于子查询查的字段名是主键id,也是依据主键范畴为标准查的,因此 会十分快,只花了0.一秒,而表层查看也是依据主键id和数据库索引tid查的,因此 也是很快。

这儿说一下
select title from arts where id>500000
select id from arts where id>500000

这几句的标准全是用主键开展范畴查看,扫描仪的总数也同样,但前面一种花了3.5秒,后面一种花了0.一秒罢了,只由于查看的字段名后面一种是主键。
一样表明不一定扫描仪个数不一定是越低就越来越快

Extra

包括不宜在别的列表明但十分关键的信息内容,有下列4中:

Using Index 表明相对的select查看应用了覆盖索引;覆盖索引便是包括考虑查看必须的全部数据信息的数据库索引

 

Using where 表明mysql在储存模块接到纪录后开展"后过虑",并不是说查看句子中应用了where便会有Using where
explain select id from arts where id>500000;   # 表明了Using where 
explain select * from arts where id>500000;   # 沒有表明Using where 

Using temporary 表明应用了临时表来储存結果集,常见于排列和排序查看;假如另外出現了Using temporary和Using filesort 则特性不是佳的,这类状况出現在应用非数据库索引字段名排序的状况

explain select title from arts where id>600000 group by is_send desc;
 ------ ------------- ------- ------- --------------- --------- --------- ------ -------- -------------------------------------------------------- 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                                  |
 ------ ------------- ------- ------- --------------- --------- --------- ------ -------- -------------------------------------------------------- 
|    1 | SIMPLE      | arts  | range | PRIMARY       | PRIMARY | 4       | NULL | 122818 | Using index condition; Using temporary; Using filesort |
 ------ ------------- ------- ------- --------------- --------- --------- ------ -------- -------------------------------------------------------- 


explain select title from arts group by id desc;
 ------ ------------- ------- ------ --------------- ------ --------- ------ -------- ---------------- 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
 ------ ------------- ------- ------ --------------- ------ --------- ------ -------- ---------------- 
|    1 | SIMPLE      | arts  | ALL  | NULL          | NULL | NULL    | NULL | 614436 | Using filesort |
 ------ ------------- ------- ------ --------------- ------ --------- ------ -------- ---------------- 

Using filesort 文件排序,mysql将没法运用到数据库索引的排列实际操作变成文件排序,因此 出現Using filesort比不出現Using filesort的特性差

select sql_no_cache title from arts where id>500000 order by create_time desc;      # Using index condition; Using filesort 用了3秒多
explain select sql_no_cache title from arts where id>700000 order by id desc;       # Using where; 用了一秒多

因此 尽量应用数据库索引排列

PS: Explain不容易对你说有关触发器原理,sql语句的信息内容或是客户自定义函数对查看的特性危害状况
    Explain不容易考虑到缓存文件要素
    Explain不容易表明mysql在查看中常做的提升
    一部分统计数据是估计的,不精确
    如果有子查询或是应用了临时表的主视图,应用explain的花销会非常大

   

 

    关键字:

天才代写-代写联系方式