图片 18

浅谈mysql实行陈设之type,条件数据中

Mysql 查询出某列字段 被含有于 条件数据中,mysql字段

咱俩常见是采纳  某条件  是或不是包罗于 某列中   ,轻巧点 正是:select *
from 表名 where  字段名 like ‘%条件数据%’;

前日说下   某列 被含有于 条件数据中

图片 1

 

  接下去看查询结果

图片 2

 

可以看来  过滤掉330302,440一数据

查询出某列字段 被含有于
条件数据中,mysql字段 大家平时是利用 某条件 是或不是带有于 某列中 ,简单题正是:select * from 表名 where 字段…

mysql试行安插作为深入分析一条sql的实施功用的工具11分管用,通过explain关键字便可查阅select语句的切实可行实践陈设,深入分析其是不是按我们设计的推行,是不是采纳了目录,是还是不是全表扫描等等。可是有那么些开销同学对explain重临的执行铺排不是不行领悟,这里本人通过有个别简单易行的例子,为大家做个入门,希望能够投石问路,让大家在平常支付中看懂explain的推行安排,并且优化sql。

Explain简介

正文首要描述如何通过 explain 命令获取
select 语句的实行布署,通过 explain
我们得以知晓以下音信:表的读取顺序,数据读取操作的品种,哪些索引能够接纳,哪些索引实际应用了,表之间的引用,每张表有稍许行被优化器查询等消息。

下边是选取 explain 的事例: 

在 select
语句在此之前扩充 explain 关键字,MySQL
会在询问上设置二个标识,施行查询时,会回去推行安插的新闻,而不是实行那条SQL(假设from 中包括子查询,仍会进行该子查询,将结果放入不时表中)。

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

在询问中的每一种表会输出一行,要是有多个表经过
join 连接查询,那么会输出两行。表的意义极度分布:能够是子查询、一个union 结果等。

explain 有三个变种:

1)explain
extended
:会在 explain 
的根基上附加提供一些询问优化的音讯。紧随其后通过 show warnings 命令能够得到优化后的询问语句,从而观望优化器优化了怎么着。额外还会有 filtered
列,是一个半分比的值,rows * filtered/100 能够臆度出就要和 explain
中前叁个表张开一连的行数(前3个表指 explain
中的id值比方今表id值小的表)。

mysql> explain extended select * from film where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------+
| Level | Code | Message                                                                        |
+-------+------+--------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `test`.`film` where 1 |
+-------+------+--------------------------------------------------------------------------------+

2)explain
partitions
:相比 explain 多了个 partitions
字段,如若查询是遵照分区表的话,会显得查询将拜访的分区。

首先要显明的一点是,explain只可以表明select语句,所以不用试图实行explain
update之类的口舌。

explain 中的列

接下去大家将突显 explain
中每种列的消息。

在对一条select举办explain之后,大家会看出再次来到结果中有如下多少个字段:

1. id列

id列的数码是 select 的行列号,有多少个select 就有多少个id,并且id的逐条是按 select 出现的依次增加的。MySQL将
select
查询分为轻松询问和复杂查询。复杂查询分为叁类:轻松子查询、派生表(from语句中的子查询)、union
查询。

一)简单子查询

mysql> explain select (select 1 from actor limit 1) from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | PRIMARY     | film  | index | NULL          | idx_name | 32      | NULL |    1 | Using index |
|  2 | SUBQUERY    | actor | index | NULL          | PRIMARY  | 4       | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 

二)from子句中的子查询

mysql> explain select id from (select id from film) as der;
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL     | NULL    | NULL |    2 | NULL        |
|  2 | DERIVED     | film       | index | NULL          | idx_name | 32      | NULL |    1 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+

其壹查询实施时有个偶尔表别称叫der,外部
select 查询引用了那几个一时表

3)union查询

mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

union结果延续放在二个无名氏偶然表中,不经常表不在SQL总出现,由此它的id是NULL。

图片 3

2. select_type列

select_type
表示对应行是是回顾仍旧复杂的询问,借使是头眼昏花的查询,又是上述三种复杂查询中的哪1种。

1)simple:简单询问。查询不包括子查询和union

mysql> explain select * from film where id = 2;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

2)primary:复杂查询中最外层的
select

3)subquery:包罗在 select 中的子查询(不在
from 子句中)

4)derived:包蕴在 from
子句中的子查询。MySQL会将结果存放在3个不时表中,也称为派生表(derived的英文含义)

用那么些事例来打听 primary、subquery 和
derived 类型

mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | <derived3> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL        |
|  3 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  2 | SUBQUERY    | actor      | const  | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+ 

5)union:在 union 中的第二个和随之的
select

6)union
result
:从 union 不常表检索结果的 select

用那些事例来打听 union 和 union result
类型:

mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
  1. id 表示实行的逐条,id越大越先实行,id同样的从上往下实践。
  2. select_type 表示查询类型,平常有:
    • simple:表示无需union操作还是不包罗子查询的简要询问。
    • primary:表示最外层查询。
    • union:union操作中第一个及以往的询问。
    • dependent
      union
      :union操作中第三个及其后的查询,并且该查询重视于外部查询。
    • subquery:子查询中的第二个查询。
    • dependent
      subquery
      :子查询中的第3个查询,并且该查询正视于表面查询。
    • derived:派生表查询,既from字句中的子查询。
    • materialized:物化查询。
    • uncacheable
      subquery
      :不能够被缓存的子查询,对外表查询的每壹行都须要再一次进行询问。
    • uncacheable
      union
      :union操作中第二个及今后的查询,并且该查询属于uncacheable
      subquery。
  3. table 表名或许表的别称。
  4. partitions 分区新闻,非分区表为null。
  5. type
    访问类型,表示找到所查询数据的办法,也是本文重视介绍的习性。该属性的常见值如下,品质从好到差:

    • NULL:没有须求访问表大概索引,举例获取八个索引列的最大值或纤维值。
    • system/const:当查问最多相配壹行时,常出现于where条件是=的情状。system是const的一种独特情状,既表本人唯有一行数据的情景。
    • eq_ref:多表关联合检查询时,依据唯1非空索引进行查询的景色。
    • ref:多表查询时,依照非唯1非空索引举办询问的意况。
    • range:在一个目录上开展限定查找。
    • index:遍历索引树查询,平日发生在询问结果只包括索引字段时。
    • ALL:全表扫描,未有任何索引能够行使时。那是最差的景色,应该幸免。
  6. possible_keys 表示mysql这次查询中或许选择的目录。
  7. key 表示mysql实际在此次查询中运用的目录。
  8. key_len 表示mysql使用的目录的长度。该值越小越好。
  9. ref 表示连接查询的连天条件。
  10. rows 表示mysql揣度此番查询所需读取的行数。该值越小越好。
  11. extra
    表示mysql消除查询的此外音信,有几十种不相同的值,该音讯也是我们优化sql能够小心的一个值。关于那些extra消息作者也许会再下一篇中牵线,这里先略过。

3. table列

那1列表示 explain
的一行正在访问哪个表。

当 from 子句中有子查询时,table列是 <derivenN> 格式,表示如今查询依赖id=N 的询问,于是先实行 id=N 的询问。当有 union 时,UNION RESULT 的
table 列的值为 <union1,二>,壹和二意味涉足 union 的
select 行id。

到最近甘休,大家基本已经通晓了explain语句的输出的含义,结下来就进去实战,借助例子来介绍一下每一个type的比不上。首先大家计划一下本次演示中需求采纳的数量。

4. type列

那一列表示关联类型或访问类型,即MySQL决定怎样查找表中的行。

各个从最优到最差分别为:system > const
> eq_ref > ref > fulltext > ref_or_null > index_merge
> unique_subquery > index_subquery > range > index >
ALL

NULL:mysql能够在优化阶段分解查询语句,在试行等第用不着再访问表或索引。比如:在索引列中甄选最小值,能够独自查找索引来完毕,没有须要在施行时访问表

mysql> explain select min(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

const, system:mysql能对查询的某有个别开展优化并将其转化成2个常量(可以看show
warnings 的结果)。用于 primary key 或 unique key
的兼具列与常数相比时,所以表最多有二个相配行,读取1次,速度相当慢。

mysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+

eq_ref:primary key 或 unique key
索引的富有片段被接连使用 ,最三只会回到一条符合条件的笔录。那可能是在
const 之外最棒的对接类型了,简单的 select 查询不会并发这种 type。

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key               | key_len | ref                     | rows | Extra       |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | index  | NULL          | idx_film_actor_id | 8       | NULL                    |    3 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY       | PRIMARY           | 4       | test.film_actor.film_id |    1 | NULL        |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+

ref:相比 eq_ref,不应用唯一索引,而是利用普通索引可能唯一性索引的壹对前缀,索引要和某些值相比较,大概会找到多个符合条件的行。

1. 简单 select 查询,name是普通索引(非唯一索引)
mysql> explain select * from film where name = "film1";
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref  | idx_name      | idx_name | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+

2.关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分。
mysql> explain select * from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra       |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
|  1 | SIMPLE      | film       | index | NULL              | idx_name          | 33      | NULL         |    3 | Using index |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+

ref_or_null:类似ref,可是能够寻找值为NULL的行。

mysql> explain select * from film where name = "film1" or name is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref_or_null | idx_name      | idx_name | 33      | const |    2 | Using where; Using index |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+

index_merge:表示使用了目录合并的优化措施。 举例下表:id是主键,tenant_id是普通索引。or
的时候从不用 primary key,而是利用了 primary key(id) 和 tenant_id
索引

mysql> explain select * from role where id = 11011 or tenant_id = 8888;
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | role  | index_merge | PRIMARY,idx_tenant_id | PRIMARY,idx_tenant_id | 4,4     | NULL |  134 | Using union(PRIMARY,idx_tenant_id); Using where |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

range:范围扫描平时出现在 in(),
between ,> ,<, >=
等操作中。使用二个索引来搜寻给定范围的行。

mysql> explain select * from actor where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

index:和ALL同样,不一致正是mysql只需扫描索引树,那平常比ALL快一些。

mysql> explain select count(*) from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

ALL:即全表扫描,意味着mysql要求开首到尾去查找所急需的行。平常景况下那要求充实索引来开展优化了

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
  • DDL:

5. possible_keys列

那壹列展现查询恐怕利用什么索引来查找。 

explain
时只怕出现 possible_keys 有列,而 key 彰显 NULL
的景况,这种景况是因为表中数据不多,mysql感到索引对此询问扶助相当小,选取了全表查询。 

若是该列是NULL,则未有相关的目录。在这种意况下,能够经过检查
where 子句看是或不是能够创制二个正好的目录来加强查询品质,然后用 explain
查看效果。

CREATE TABLE class (id int unsigned NOT NULL
AUTO_INCREMENT,grade int NOT NULL,PRIMARY KEY (id));

CREATE TABLE student (id int unsigned NOT NULL
AUTO_INCREMENT,name char NOT NULL,age tinyint NOT NULL,score
tinyint NOT NULL,class_id int NOT NULL,PRIMARY KEY (id),KEY
IDX_CLASS_ID (class_id),KEY IDX_AGE (age));

6. key列

那1列呈现mysql实际选拔哪个索引来优化对该表的走访。

若果未有利用索引,则该列是
NULL。假使想强制mysql使用或不经意possible_keys列中的索引,在查询中动用
force index、ignore index。

  • DML:

7. key_len列

这一列呈现了mysql在目录里选取的字节数,通过这些值能够算出切实可行应用了目录中的哪些列。 

比喻来讲,film_actor的联合索引
idx_film_actor_id 由 film_id 和 actor_id
多个int列组成,并且每一种int是四字节。通过结果中的key_len=四可估摸出查询利用了第3个列:film_id列来实践索引查找。

mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | film_actor | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    1 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

key_len总结规则如下:

  • 字符串
    • char(n):n字节长度
    • varchar(n):贰字节囤积字符串长度,假如是utf-八,则长度 三n + 二
  • 数值类型
    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节  
  • 时刻档期的顺序 
    • date:3字节
    • timestamp:4字节
    • datetime:8字节
  • 一经字段允许为 NULL,供给一字节记下是或不是为 NULL

目录最大尺寸是76八字节,当字符串过长时,mysql会做二个近似左前缀索引的拍卖,将前半某些的字符提抽取来做索引。

INSERT INTO class(grade) VALUES,,;INSERT INTO student(name,
age, score, class_id)VALUES (‘Sawyer’, 29, 88, 1), (‘Del Piero’,
22, 91, 1),(‘Mitsunari’, 44, 99, 2), (‘Natalie’, 41, 95, 2);

8. ref列

那壹列显示了在key列记录的目录中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名(例:film.id)

运营之后的结果:

9. rows列

那壹列是mysql推断要读取并检查测试的行数,注意这几个不是结果集里的行数。

图片 4class图片 5student

10. Extra列

这一列体现的是额外音讯。常见的首要值如下: 

distinct:
壹旦mysql找到了与行相联合相称的行,就不再搜索了

mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra                        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
|  1 | SIMPLE      | film       | index | idx_name          | idx_name          | 33      | NULL         |    3 | Using index; Using temporary |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index; Distinct        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+

Using index:这产生在对表的请求列都以同一索引的有的的时候,重返的列数据只利用了目录中的音信,而从未再去做客表中的行记录。是性质高的突显。

mysql> explain select id from film order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 

Using where:mysql服务器将要积存引擎检索行后再开始展览过滤。正是先读取整行数据,再按
where 条件进行反省,符合就留给,不符合就撇下。

mysql> explain select * from film where id > 1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | film  | index | PRIMARY       | idx_name | 33      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

Using temporary:mysql须要创立一张有时表来拍卖查询。出现这种景色一般是要拓展优化的,首先是想开用索引来优化。

1. actor.name没有索引,此时创建了张临时表来distinct
mysql> explain select distinct name from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+

2. film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表
mysql> explain select distinct name from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | idx_name      | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

Using filesort:mysql
会对结果运用三个外部索引排序,而不是按索引次序从表里读取行。此时mysql会依赖联接类型浏览全体符合条件的笔录,并保存排序关键字和行指针,然后排序关键字并按梯次检索行新闻。这种景况下一般也是要思量使用索引来优化的。

1. actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录
mysql> explain select * from actor order by name;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

2. film.name建立了idx_name索引,此时查询时extra是using index
mysql> explain select * from film order by name;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

迄今截止,希图事业成就,接下去大家将两个3个地分析广泛的type类型,从最功用的到最不功能的。

利用的表

上述全体sql使用的表和数据:

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

NULL:没有要求访问表恐怕索引,比方获取1个索引列的最大值或纤维值。由于innodb选用B+树最为索引的情理构造,而B+树的叶子节点是顺序排列的,所以当查询索引的最大或纤维值时,不要求遍历叶子节点,只须要获得叶子节点头只怕尾就可以。看上面包车型大巴事例:

参考

  • 《高性能MySQL》: 附录D
  • mysql官方文书档案-explain

select max from student;

图片 6NULL

system/const:当查问最多相配1行时,常并发于where条件是=的情景。system是const的一种新鲜景况,既表自个儿唯有壹行数据的场合。大家这里演示一下const的动静:

select * from student where id = 1;

图片 7const

不过要专注的是,并不是富有的where=都是const,只有=的左手是常量的时候才会走const。举个例子:

select * from class where id = grade;

图片 8not
const

鉴于=的左手并不是常量,且grade上从不建索引,所以该查询走了频率最差的ALL全表扫描。经常,我们会将查询条件里的具备字段都创建目录,下边包车型地铁sql优化后会产生使用index目录查询:

图片 9

eq_ref:多表关联合检查询时,依照唯一非空索引举办询问的事态。那几个只会产出在关乎查询中,并且是依据唯1非空键(主键或唯一非空索引)查询的状态。如下:

select * from class c join student son c.id = s.class_idwhere s.name
= ‘Sawyer’;

图片 10eq_ref

意识了么,这里的实施结果出现了两条数据,id同为1,记忆一下大家一开头说的,id代表了实施的次第,当id一样期,顺序自上而下。所以分析那一个实行结果可见,mysql首先在s表也正是student表中查询name字段为索耶的值,由于name字段上并从未索引,所以利用了全表扫描,该表一共有4条记下,所以扫描了4行,rows为四。然后c表相当于class表使用主键和后边的结果通过s.class_id关联,由于是涉及查询,并且是经过唯一键举办询问,所以选用了eq_ref的花色。这里也足以经过创立name字段的目录来优化全表扫描的主题素材,这里就不再演示。

ref:多表查询时,依照非唯一非空索引进行查询的情景。那些跟eq_ref的无与伦比差距正是关联合检查询是依靠非唯一非空索引举办的。例如:

select * from class c join student son c.id = s.class_idwhere c.id =
1;

图片 11ref

经过这么些实践布置能够解析出,由于查询条件是c.id=一,是3个常数查询,mysql首先选取const对c表实行查询,之后s表使用索引IDX_CLASS_ID对结果开始展览关联,由于索引IDX_CLASS_ID是非唯一非空索引,所以这里的查询形式为ref。

range:在2个索引上海展览中心开限制查找。既只使用几个目录,查询条件满足八个时。比方:

select * from student where age between 20 and 30;

图片 12range

一般说来景况下,where in 也是range查询,不过上面的例证却展现ALL:

select * from student where age in ;

图片 13ALL
instead of range

这是mysql的优化器(optimizer)的优化结果。mysql通过深入分析发掘此询问走全表扫描的代价比走索引的代价要小,所以选择了走全表扫描而非索引。大意的案由是由此索引读取一条数据至少要通过三回节点检索(聚簇索引的莫斯中国科学技术大学学为3,数据存在叶子节点上),而全表扫描是二回读取多个page中的多条记下,当查问结果超过总的数量量分明比重的时候(在这一个事例中有6分之三的数码满意了询问条件),走索引的查询支付反而比全表扫描要大,那时mysql则会放任索引而选择实行全表扫描。优化器不止能在目录和全表扫描中精选,乃至在不影响查询结果的前提下改造查询顺序等黑科学技术,这里就不再举行。对于我们的例证,有二种方法让mysql使用索引查询。壹种是选拔语法强制mysql使用索引:

select * from student force index where age in ;

图片 14force
index

当然,实际付出中在您未有丰富的握住时,最佳不用采取那样的语法,究竟mysql优化器依然相比较准的。

另一种格局是增扩展少,使得查询条件命中的数据占总额据的占比相当的小。那也是索引最能发挥成效的状态(既唯有少部分数量满意查询条件)。

INSERT INTO student(name, age, score, class_id)VALUES (‘Tom’, 61, 84,
3), (‘Obama’, 56, 100, 4),(‘Nedved’, 43, 86, 5), (‘Buffon’, 49, 86,
5);

本条时候我们再实施以前的sql就发现这一次走的是索引的range了,原因是此次条件命中的数据占全数数据的占比比异常的小。

图片 15image.png

index:遍历索引树查询,平常爆发在询问结果只含有索引字段时。相比较好掌握,既只select索引字段,并且未有where条件,如:

select id from student;

图片 16index

ALL:全表扫描,未有其余索引能够运用时。那是最差的气象,应该幸免。举个例子:

select * from student where score = 100;

图片 17ALL

有的时候候固然是通过索引的字段查询,也会现出全表扫描的景况,最普及的意况正是对字段举办了函数管理,如:

explain select * from student where coalesce > 20;

图片 18目录失效

此间即便age字段上建有目录,可是由于大家对age进行了函数管理,所以就无奈再利用索引了,在写sql时必然要幸免这种场所,即使实际有亟待,可以动用mysql
5.⑦的总计字段(generated
column),并确立索引来实现,这些自家在《oracle迁移mysql总括》最终有涉及。

关于explain的常见type类型就先讲这么多,在介绍的进度中其实也波及了有个别sql优化的方案及优化器方面的事物,那些下次有机会再写壹篇来具体介绍sql优化。
type其实还应该有其余类型如fulltext, ref_or_null, index_merge,
unique_subqueryindex_subquery,这一个不太常见这里也先略过,下一次有机遇再补上。包涵explain的extra新闻也值得单独用一篇来说,本文就权且开个头,希望能对我们的sql才具和sql优化有个别推搡,活用explain,不要让sql成为程序的瓶颈。