MYSQL教程优化篇:如何检测和提高查询语句的效率和性能

作者 : IT 大叔 本文共7944个字,预计阅读时间需要20分钟 发布时间: 2020-11-5

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代表这个该查询衍生来自第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.1秒,而外层查询又是根据主键id和索引tid查的,所以也是飞快。

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

这两句的条件都是用主键进行范围查询,扫描的条数也相同,但前者花了3.5秒,后者花了0.1秒而已,只因为查询的字段后者是主键。
同样说明不一定扫描行数不一定是越少就越快

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; 用了1秒多

所以尽可能使用索引排序

PS: Explain不会告诉你关于触发器,存储过程的信息或者用户自定义函数对查询的性能影响情况
Explain不会考虑缓存因素
Explain不会显示mysql在查询中所做的优化
部分统计信息是估算的,不准确
如果有子查询或者使用了临时表的视图,使用explain的开销会很大

免责声明:
1. 本站资源转自互联网,源码资源分享仅供交流学习,下载后切勿用于商业用途,否则开发者追究责任与本站无关!
2. 本站使用「署名 4.0 国际」创作协议,可自由转载、引用,但需署名原版权作者且注明文章出处
3. 未登录无法下载,登录使用金币下载所有资源。
IT小站 » MYSQL教程优化篇:如何检测和提高查询语句的效率和性能

常见问题FAQ

没有金币/金币不足 怎么办?
本站已开通每日签到送金币,每日签到赠送五枚金币,金币可累积。
所有资源普通会员都能下载吗?
本站所有资源普通会员都可以下载,需要消耗金币下载的白金会员资源,通过每日签到,即可获取免费金币,金币可累积使用。

发表评论