explain用法和结果的意思,详细介绍

原标题:《叶问》第7期

mysql
explain用法和结果的意思
2012-11-23 17:18:13

Mysql Explain 详细介绍,mysqlexplain

Mysql Explain 那里做3个素材的巨细无遗整治。

一.语法

explain < table_name >

例如: explain select * from t3 where id=3952602;

二.explain输出解释

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

1.id

  作者的领悟是SQL实行的顺风的标记,SQL从大到小的推行.

例如:

mysql> explain select * from (select * from ( select * from t3
where id=3952602) a) b;
+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

很显眼那条SQL是从里向外的实行,便是从id=3 向上施行.

  1. select_type

纵使select类型,能够有以下

(1) SIMPLE

简言之SELECT(不利用UNION或子查询等) 比方:

mysql> explain select * from t3 where
id=3952602;

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     | const |    1 |       |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

(2). PRIMARY

本人的领悟是最外层的select.举例:

mysql> explain select * from (select *
from t3 where id=3952602) a ;

+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

(3).UNION

UNION中的第1个或前边的SELECT语句.比如

mysql> explain select * from t3 where
id=3952602 union all select * from t3 ;

+—-+————–+————+——-+——————-+———+———+——-+——+——-+
| id | select_type  | table      | type  | possible_keys     | key 
   | key_len | ref   | rows | Extra |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY
| 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    |
NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              |
NULL    | NULL    | NULL  | NULL |       |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+

(4).DEPENDENT UNION

UNION中的第3个或前面包车型大巴SELECT语句,取决于外面的询问

mysql> explain select * from t3 where
id in (select id from t3 where id=3952602 union all select id from
t3)  ;

+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
| id | select_type        | table      | type   | possible_keys     |
key     | key_len | ref   | rows | Extra                    |
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
|  1 | PRIMARY            | t3         | ALL    | NULL              |
NULL    | NULL    | NULL  | 1000 | Using where              |
|  2 | DEPENDENT SUBQUERY | t3         | const  | PRIMARY,idx_t3_id |
PRIMARY | 4       | const |    1 | Using index              |
|  3 | DEPENDENT UNION    | t3         | eq_ref | PRIMARY,idx_t3_id |
PRIMARY | 4       | func  |    1 | Using where; Using index |
|NULL | UNION RESULT       | <union2,3> | ALL    | NULL          
   | NULL    | NULL    | NULL  | NULL |                          |
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+

(4).UNION RESULT

UNION的结果。

mysql> explain select * from t3 where
id=3952602 union all select * from t3 ;

+—-+————–+————+——-+——————-+———+———+——-+——+——-+
| id | select_type  | table      | type  | possible_keys     | key 
   | key_len | ref   | rows | Extra |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY
| 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    |
NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              |
NULL    | NULL    | NULL  | NULL |       |
+—-+————–+————+——-+——————-+———+———+——-+——+——-+

(5).SUBQUERY

子查询中的第1个SELECT.

mysql> explain select * from t3 where
id = (select id from t3 where id=3952602 )  ;

+—-+————-+——-+——-+——————-+———+———+——-+——+————-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra       |
+—-+————-+——-+——-+——————-+———+———+——-+——+————-+
|  1 | PRIMARY     | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     | const |    1 |             |
|  2 | SUBQUERY    | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     |       |    1 | Using index |
+—-+————-+——-+——-+——————-+———+———+——-+——+————-+

(6).  DEPENDENT SUBQUERY

子查询中的第捌个SELECT,取决于外面包车型地铁查询

mysql> explain select id from t3 where
id in (select id from t3 where id=3952602 )  ;

+—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
| id | select_type        | table | type  | possible_keys     | key 
   | key_len | ref   | rows | Extra                    |
+—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
|  1 | PRIMARY            | t3    | index | NULL              | PRIMARY
| 4       | NULL  | 1000 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t3    | const | PRIMARY,idx_t3_id |
PRIMARY | 4       | const |    1 | Using index              |
+—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+

7).DERIVED

派生表的SELECT(FROM子句的子查询)

mysql> explain select * from (select *
from t3 where id=3952602) a ;

+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

3.table

突显那壹行的数目是有关哪张表的.

偶然不是实际的表名字,看到的是derivedx(x是个数字,小编的知情是第几步推行的结果)

mysql> explain select * from (select *
from ( select * from t3 where id=3952602) a) b;

+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

4.type

那列很要紧,突显了连年使用了哪类等级次序,有无使用索引.

从最棒到最差的连年类型为const、eq_reg、ref、range、indexhe和ALL

(1).system

那是const联接类型的三个特例。表仅有1行知足条件.如下(t3表上的id是
primary key)

mysql> explain select * from (select *
from t3 where id=3952602) a ;

+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key 
   | key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+

(2).const

表最多有四个相配行,它就要查询起首时被读取。因为仅有一行,在那行的列值可被优化器剩余部分认为是常数。const表相当慢,因为它们只读取一回!

const用于用常数值比较P奥迪Q5IMACRUISERY
KEY或UNIQUE索引的富有片段时。在底下的询问中,tbl_name能够用于const表:

SELECT * from tbl_name WHERE
primary_key=1;
SELECT * from tbl_name WHERE primary_key_part1=1和
primary_key_part2=2;

例如:

mysql> explain select * from t3 where
id=3952602;

explain用法和结果的意思,详细介绍。+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4 
     | const |    1 |       |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

(3). eq_ref

对此每一个来自于前方的表的行组合,从该表中读取一行。那恐怕是最棒的连通类型,除了const类型。它用在1个目录的全体片段被连接使用并且索引是UNIQUE或PRAV4IMA福睿斯Y
KEY。

eq_ref可以用来接纳=
操作符比较的带索引的列。相比较值可以为常量或叁个使用在该表前面所读取的表的列的表明式。

在下边包车型客车事例中,MySQL能够应用eq_ref联接来管理ref_tables:

SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column=other_table.column;


SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

例如

mysql> create unique index  idx_t3_id
on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> explain select * from t3,t4
where t3.id=t4.accountid;

+—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
| id | select_type | table | type   | possible_keys     | key       |
key_len | ref                  | rows | Extra |
+—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
|  1 | SIMPLE      | t4    | ALL    | NULL              | NULL      |
NULL    | NULL                 | 1000 |       |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,idx_t3_id | idx_t3_id
| 4       | dbatest.t4.accountid |    1 |       |
+—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+

(4).ref

对此每一个来自于前方的表的行组合,全部有相称索引值的将要从那张表中读取。假诺连接只使用键的最左侧的前缀,或如若键不是UNIQUE或P昂CoraIMA奥迪Q三Y
KEY(换句话说,要是连接无法依附关键字选拔单个行的话),则利用ref。如若使用的键仅仅相称少许行,该联接类型是天经地义的。
ref可以用来接纳=或<=>操作符的带索引的列。

在下边包车型客车例证中,MySQL能够行使ref联接来管理ref_tables:

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
 WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

例如:

mysql> drop index idx_t3_id on t3;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> create index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.04 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from t3,t4
where t3.id=t4.accountid;

+—-+————-+——-+——+——————-+———–+———+———————-+——+——-+
| id | select_type | table | type | possible_keys     | key       |
key_len | ref                  | rows | Extra |
+—-+————-+——-+——+——————-+———–+———+———————-+——+——-+
|  1 | SIMPLE      | t4    | ALL  | NULL              | NULL      |
NULL    | NULL                 | 1000 |       |
|  1 | SIMPLE      | t3    | ref  | PRIMARY,idx_t3_id | idx_t3_id |
4       | dbatest.t4.accountid |    1 |       |
+—-+————-+——-+——+——————-+———–+———+———————-+——+——-+

2 rows in set (0.00 sec)

(5).  ref_or_null

该联接类型就像是ref,可是增添了MySQL能够专门搜寻包罗NULL值的行。在化解子查询中常常利用该联接类型的优化。

在底下的例证中,MySQL可以动用ref_or_null联接来管理ref_tables:

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

(6). index_merge

美高梅4858官方网站 ,该联接类型表示使用了目录合并优化措施。在那种状态下,key列包罗了动用的目录的清单,key_len包括了使用的目录的最长的最首要要素。

例如:

mysql> explain select * from t4 where
id=3952602 or accountid=31754306 ;

+—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
| id | select_type | table | type        | possible_keys          
   | key                        | key_len | ref  | rows | Extra       
                                        |
+—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
|  1 | SIMPLE      | t4    | index_merge |
idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid | 4,4 
   | NULL |    2 | Using union(idx_t4_id,idx_t4_accountid); Using
where |
+—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+

1 row in set (0.00 sec)

(7). unique_subquery

该品种替换了下边情势的IN子查询的ref:

value IN (SELECT primary_key FROM
single_table WHERE some_expr)

unique_subquery是1个索引查找函数,能够完全替换子查询,功能更加高。

(8).index_subquery

该联接类型类似于unique_subquery。能够替换IN子查询,但只适合下列情势的子查询中的非唯一索引:

value IN (SELECT key_column FROM
single_table WHERE some_expr)

(9).range

只检索给定范围的行,使用三个索引来摘取行。key列突显应用了哪些索引。key_len包罗所运用索引的最长入眼要素。在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、IS
NULL、<=>、BETWEEN大概IN操作符,用常量比较主要字列时,能够利用range

mysql> explain select * from t3 where
id=3952602 or id=3952603 ;

+—-+————-+——-+——-+——————-+———–+———+——+——+————-+
| id | select_type | table | type  | possible_keys     | key       |
key_len | ref  | rows | Extra       |
+—-+————-+——-+——-+——————-+———–+———+——+——+————-+
|  1 | SIMPLE      | t3    | range | PRIMARY,idx_t3_id | idx_t3_id |
4       | NULL |    2 | Using where |
+—-+————-+——-+——-+——————-+———–+———+——+——+————-+

1 row in set (0.02 sec)

(10).index

该联接类型与ALL一样,除了唯有索引树被围观。这一般比ALL快,因为索引文件一般比数据文件小。

当查问只行使作为单索引一局地的列时,MySQL能够选拔该联接类型。

(11). ALL

对此每种来自于从前的表的行组合,进行全部的表扫描。假使表是首先个没标志const的表,这一般倒霉,并且普通在它意况下很差。平常能够追加更多的目录而毫无使用ALL,使得行能基于前边的表中的常数值或列值被搜寻出。

5.possible_keys

possible_keys列建议MySQL能利用哪个索引在该表中找到行。注意,该列完全部独用立于EXPLAIN输出所示的表的次第。那意味在possible_keys中的某个键实际上不可能按生成的表次序使用。

借使该列是NULL,则并未有有关的目录。在那种状态下,能够通过检查WHERE子句看是还是不是它引用有个别列或符合索引的列来升高你的查询性能。假若是如此,创立2个体面的目录并且再一次用EXPLAIN检查查询

  1. key

key列展现MySQL实际调整选拔的键(索引)。借使未有选取索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在询问中采纳FO牧马人CE
INDEX、USE INDEX或然IGNORE INDEX。

7.key_len

key_len列突显MySQL决定利用的键长度。若是键是NULL,则长度为NULL。
动用的目录的尺寸。在不损失正确性的气象下,长度越短越好

  1. ref

ref列呈现应用哪个列或常数与key一同从表中甄选行。

  1. rows

rows列呈现MySQL以为它试行查询时务必检查的行数。

  1. Extra

该列包涵MySQL化解查询的详细音讯,上边详细.

(1).Distinct

要是MYSQL找到了与行相联合相称的行,就不再搜索了

(2).Not exists

MYSQL优化了LEFT JOIN,一旦它找到了相配LEFT JOIN规范的行,

就不再寻觅了

(3).Range checked for each

Record(index map:#)

尚无找到优良的目录,因而对此以前面表中来的每3个行组合,MYSQL检查选用哪个索引,并用它来从表中重回行。那是使用索引的最慢的接连之一

(4).Using filesort

来看这一个的时候,查询就供给优化了。MYSQL需求张开额外的步调来发现怎么对回到的行排序。它遵照连年类型以及存款和储蓄排序键值和包容原则的漫天行的行指针来排序全体行

(5).Using index

列数据是从仅仅使用了目录中的新闻而从不读取实际的行走的表再次回到的,那爆发在对表的壹切的恳求列都以同三个索引的一些的时候

(6).Using temporary

看样子这些的时候,查询供给优化了。那里,MYSQL供给创制贰个权且表来储存结果,那经常产生在对分裂的列集举行O本田CR-VDER
BY上,而不是GROUP BY上

(7).Using where

选用了WHERE从句来界定哪些行将与下一张表相配大概是再次回到给用户。如若不想回来表中的全方位行,并且总是类型ALL或index,这就会生出,也许是查询有毛病

多谢阅读,希望能扶助到我们,感谢大家对本站的支撑!

Explain 详细介绍,mysqlexplain Mysql
Explain 那里做3个素材的周密整治。 一.语法 explain table_name 例如:
explain select * from t3 where id=3952602; 二…

author:skate
time:2012/02/17
 
Mysql查看推行安排
 
 
一.语法
 
explain <sql语句>
 
例如: explain select * from t3 where id=3952602;
 
二.explain输出解释
 
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     |
key_len | ref   | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
 
1.id
  笔者的接头是SQL推行的得手的标识,SQL从大到小的试行.
 
例如:
mysql> explain select * from (select * from ( select * from t3
where id=3952602) a) b;
+—-+————-+————+——–+——————-+———+———+——+——+——-+
| id | select_type | table      | type   | possible_keys     | key    
| key_len | ref  | rows | Extra |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
|  1 | PRIMARY     | <derived2> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              |
NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
| 4       |      |    1 |       |
+—-+————-+————+——–+——————-+———+———+——+——+——-+
 
很显然那条SQL是从里向外的举行,正是从id=三 向上试行.
 

叶问

分类: Mysql/postgreSQL

  1. select_type
     
    不畏select类型,能够有以下二种
     
    (1) SIMPLE
    简短SELECT(不使用UNION或子查询等) 举例:
    mysql> explain select * from t3 where id=3952602;
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
    | id | select_type | table | type  | possible_keys     | key     |
    key_len | ref   | rows | Extra |
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
    |  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY |
    4       | const |    1 |       |
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
     
    (2). PRIMARY
     
    自家的接头是最外层的select.比方:
     
    mysql> explain select * from (select * from t3 where id=3952602) a
    ;
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    | id | select_type | table      | type   | possible_keys     | key    
    | key_len | ref  | rows | Extra |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    |  1 | PRIMARY     | <derived2> | system | NULL              |
    NULL    | NULL    | NULL |    1 |       |
    |  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
    | 4       |      |    1 |       |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
     
    (3).UNION
     
    UNION中的第贰个或前面包车型地铁SELECT语句.举个例子
    mysql> explain select * from t3 where id=3952602 union all select *
    from t3 ;
    +—-+————–+————+——-+——————-+———+———+——-+——+——-+
    | id | select_type  | table      | type  | possible_keys     | key    
    | key_len | ref   | rows | Extra |
    +—-+————–+————+——-+——————-+———+———+——-+——+——-+
    |  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY
    | 4       | const |    1 |       |
    |  2 | UNION        | t3         | ALL   | NULL              | NULL    |
    NULL    | NULL  | 1000 |       |
    |NULL | UNION RESULT | <union1,2> | ALL   | NULL              |
    NULL    | NULL    | NULL  | NULL |       |
    +—-+————–+————+——-+——————-+———+———+——-+——+——-+
     
    (4).DEPENDENT UNION
     
    UNION中的首个或前面包车型客车SELECT语句,取决于外面包车型地铁询问
     
    mysql> explain select * from t3 where id in (select id from t3 where
    id=3952602 union all select id from t3)  ;
    +—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
    | id | select_type        | table      | type   | possible_keys     |
    key     | key_len | ref   | rows | Extra                    |
    +—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
    |  1 | PRIMARY            | t3         | ALL    | NULL              |
    NULL    | NULL    | NULL  | 1000 | Using where              |
    |  2 | DEPENDENT SUBQUERY | t3         | const  | PRIMARY,idx_t3_id |
    PRIMARY | 4       | const |    1 | Using index              |
    |  3 | DEPENDENT UNION    | t3         | eq_ref | PRIMARY,idx_t3_id |
    PRIMARY | 4       | func  |    1 | Using where; Using index |
    |NULL | UNION RESULT       | <union2,3> | ALL    |
    NULL              | NULL    | NULL    | NULL  | NULL
    |                          |
    +—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
     
    (4).UNION RESULT
     
    UNION的结果。
     
    mysql> explain select * from t3 where id=3952602 union all select *
    from t3 ;
    +—-+————–+————+——-+——————-+———+———+——-+——+——-+
    | id | select_type  | table      | type  | possible_keys     | key    
    | key_len | ref   | rows | Extra |
    +—-+————–+————+——-+——————-+———+———+——-+——+——-+
    |  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY
    | 4       | const |    1 |       |
    |  2 | UNION        | t3         | ALL   | NULL              | NULL    |
    NULL    | NULL  | 1000 |       |
    |NULL | UNION RESULT | <union1,2> | ALL   | NULL              |
    NULL    | NULL    | NULL  | NULL |       |
    +—-+————–+————+——-+——————-+———+———+——-+——+——-+
     
    (5).SUBQUERY
     
    子查询中的第3个SELECT.
     
    mysql> explain select * from t3 where id = (select id from t3 where
    id=3952602 )  ;
    +—-+————-+——-+——-+——————-+———+———+——-+——+————-+
    | id | select_type | table | type  | possible_keys     | key     |
    key_len | ref   | rows | Extra       |
    +—-+————-+——-+——-+——————-+———+———+——-+——+————-+
    |  1 | PRIMARY     | t3    | const | PRIMARY,idx_t3_id | PRIMARY |
    4       | const |    1 |             |
    |  2 | SUBQUERY    | t3    | const | PRIMARY,idx_t3_id | PRIMARY |
    4       |       |    1 | Using index |
    +—-+————-+——-+——-+——————-+———+———+——-+——+————-+
     
    (6).  DEPENDENT SUBQUERY
     
    子查询中的第一个SELECT,取决于外面包车型大巴询问
     
    mysql> explain select id from t3 where id in (select id from t3 where
    id=3952602 )  ;
    +—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
    | id | select_type        | table | type  | possible_keys     |
    key     | key_len | ref   | rows | Extra                    |
    +—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
    |  1 | PRIMARY            | t3    | index | NULL              | PRIMARY
    | 4       | NULL  | 1000 | Using where; Using index |
    |  2 | DEPENDENT SUBQUERY | t3    | const | PRIMARY,idx_t3_id |
    PRIMARY | 4       | const |    1 | Using index              |
    +—-+——————–+——-+——-+——————-+———+———+——-+——+————————–+
     
     
    (7).DERIVED
     
    派生表的SELECT(FROM子句的子查询)
     
    mysql> explain select * from (select * from t3 where id=3952602) a
    ;
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    | id | select_type | table      | type   | possible_keys     | key    
    | key_len | ref  | rows | Extra |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    |  1 | PRIMARY     | <derived2> | system | NULL              |
    NULL    | NULL    | NULL |    1 |       |
    |  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
    | 4       |      |    1 |       |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
     
     
    3.table
     
    展现这一行的数额是关于哪张表的.
    奇迹不是实在的表名字,看到的是derivedx(x是个数字,小编的理解是第几步实践的结果)
     
    mysql> explain select * from (select * from ( select * from t3
    where id=3952602) a) b;
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    | id | select_type | table      | type   | possible_keys     | key    
    | key_len | ref  | rows | Extra |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    |  1 | PRIMARY     | <derived2> | system | NULL              |
    NULL    | NULL    | NULL |    1 |       |
    |  2 | DERIVED     | <derived3> | system | NULL              |
    NULL    | NULL    | NULL |    1 |       |
    |  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
    | 4       |      |    1 |       |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
     
    4.type
     
    那列很重大,呈现了连年使用了哪系列型,有无使用索引.
    从最好到最差的接连类型为const、eq_reg、ref、range、indexhe和ALL
     
    (1).system
     
    那是const联接类型的1个特例。表仅有一行知足条件.如下(t三表上的id是primary
    key)
     
    mysql> explain select * from (select * from t3 where id=3952602) a
    ;
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    | id | select_type | table      | type   | possible_keys     | key   
     | key_len | ref  | rows | Extra |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
    |  1 | PRIMARY     | <derived2> | system | NULL              |
    NULL    | NULL    | NULL |    1 |       |
    |  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY
    | 4       |      |    1 |       |
    +—-+————-+————+——–+——————-+———+———+——+——+——-+
     
    (2).const
     
    表最多有一个匹配行,它将要询问起首时被读取。因为仅有1行,在那行的列值可被优化器剩余部分认为是常数。const表异常快,因为它们只读取一回!
     
    const用于用常数值比较P奇骏IMA奇骏Y
    KEY或UNIQUE索引的全体片段时。在底下的询问中,tbl_name能够用来const表:
    SELECT * from tbl_name WHERE primary_key=1;
    SELECT * from tbl_name WHERE
    primary_key_part1=1和primary_key_part2=2;
     
    例如:
    mysql> explain select * from t3 where id=3952602;
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
    | id | select_type | table | type  | possible_keys     | key     |
    key_len | ref   | rows | Extra |
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
    |  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY |
    4       | const |    1 |       |
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
     
     
    (3). eq_ref
     
    对此每一个来自于前方的表的行组合,从该表中读取一行。那或者是最佳的过渡类型,除了const类型。它用在3个索引的具有片段被连接使用并且索引是UNIQUE或PRubiconIMA昂科拉Y
    KEY。
     
    eq_ref能够用于选拔=
    操作符相比的带索引的列。相比值可认为常量或三个使用在该表前面所读取的表的列的表明式。
     
    在底下的事例中,MySQL能够行使eq_ref联接来处理ref_tables:
     
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
     
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
        AND ref_table.key_column_part2=1;
     
    例如
    mysql> create unique index  idx_t3_id on t3(id) ;
    Query OK, 1000 rows affected (0.03 sec)
    Records: 1000  Duplicates: 0  Warnings: 0
     
    mysql> explain select * from t3,t4 where t3.id=t4.accountid;
    +—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
    | id | select_type | table | type   | possible_keys     | key       |
    key_len | ref                  | rows | Extra |
    +—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
    |  1 | SIMPLE      | t4    | ALL    | NULL              | NULL      |
    NULL    | NULL                 | 1000 |       |
    |  1 | SIMPLE      | t3    | eq_ref | PRIMARY,idx_t3_id | idx_t3_id
    | 4       | dbatest.t4.accountid |    1 |       |
    +—-+————-+——-+——–+——————-+———–+———+———————-+——+——-+
     
    (4).ref
     
    对于每一个来自于前方的表的行组合,全数有相配索引值的将要从那张表中读取。假设连接只使用键的最左侧的前缀,或假诺键不是UNIQUE或P帕杰罗IMARAV四Y
    KEY(换句话说,假使连接无法根据关键字选取单个行的话),则运用ref。若是选择的键仅仅相配一些些行,该联接类型是天经地义的。
     
    ref能够用来选用=或<=>操作符的带索引的列。
     
    在上面包车型大巴例证中,MySQL可以应用ref联接来管理ref_tables:
     
    SELECT * FROM ref_table WHERE key_column=expr;
     
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
     
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
        AND ref_table.key_column_part2=1;
     
    例如:
     
    mysql> drop index idx_t3_id on t3;
    Query OK, 1000 rows affected (0.03 sec)
    Records: 1000  Duplicates: 0  Warnings: 0
     
    mysql> create index idx_t3_id on t3(id) ;
    Query OK, 1000 rows affected (0.04 sec)
    Records: 1000  Duplicates: 0  Warnings: 0
     
    mysql> explain select * from t3,t4 where t3.id=t4.accountid;
    +—-+————-+——-+——+——————-+———–+———+———————-+——+——-+
    | id | select_type | table | type | possible_keys     | key       |
    key_len | ref                  | rows | Extra |
    +—-+————-+——-+——+——————-+———–+———+———————-+——+——-+
    |  1 | SIMPLE      | t4    | ALL  | NULL              | NULL      |
    NULL    | NULL                 | 1000 |       |
    |  1 | SIMPLE      | t3    | ref  | PRIMARY,idx_t3_id | idx_t3_id |
    4       | dbatest.t4.accountid |    1 |       |
    +—-+————-+——-+——+——————-+———–+———+———————-+——+——-+
    2 rows in set (0.00 sec)
     
    (5).  ref_or_null
     
    该联接类型就如ref,可是增多了MySQL可以特意搜寻包涵NULL值的行。在化解子查询中平时利用该联接类型的优化。
     
    在底下的例子中,MySQL能够利用ref_or_null联接来管理ref_tables:
     
    SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;
     
    (6). index_merge
     
    该联接类型表示使用了目录合并优化措施。在那种景色下,key列包罗了选取的目录的清单,key_len包括了应用的目录的最长的要首要素。
     
    例如:
    mysql> explain select * from t4 where id=3952602 or
    accountid=31754306 ;
    +—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
    | id | select_type | table | type        | possible_keys             
    | key                        | key_len | ref  | rows |
    Extra                                                |
    +—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
    |  1 | SIMPLE      | t4    | index_merge |
    idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid |
    4,4     | NULL |    2 | Using union(idx_t4_id,idx_t4_accountid);
    Using where |
    +—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
    1 row in set (0.00 sec)
     
    (7). unique_subquery
     
    该项目替换了上面情势的IN子查询的ref:
     
    value IN (SELECT primary_key FROM single_table WHERE some_expr)
    unique_subquery是二个目录查找函数,能够完全替换子查询,效能越来越高。
     
    (8).index_subquery
     
    该联接类型类似于unique_subquery。能够轮换IN子查询,但只适合下列方式的子查询中的非唯一索引:
     
    value IN (SELECT key_column FROM single_table WHERE some_expr)
     
    (9).range
     
    只检索给定范围的行,使用3个目录来摘取行。key列显示选拔了哪个索引。key_len包罗所选拔索引的最长着重要素。在该项目中ref列为NULL。
     
    当使用=、<>、>、>=、<、<=、IS
    NULL、<=>、BETWEEN大概IN操作符,用常量比较关键字列时,能够动用range
     
    mysql> explain select * from t3 where id=3952602 or id=3952603 ;
    +—-+————-+——-+——-+——————-+———–+———+——+——+————-+
    | id | select_type | table | type  | possible_keys     | key       |
    key_len | ref  | rows | Extra       |
    +—-+————-+——-+——-+——————-+———–+———+——+——+————-+
    |  1 | SIMPLE      | t3    | range | PRIMARY,idx_t3_id | idx_t3_id |
    4       | NULL |    2 | Using where |
    +—-+————-+——-+——-+——————-+———–+———+——+——+————-+
    1 row in set (0.02 sec)
     
    (10).index
     
    该联接类型与ALL一样,除了唯有索引树被扫描。这一般比ALL快,因为索引文件一般比数据文件小。
     
    当查问只使用作为单索引一局地的列时,MySQL能够动用该联接类型。
     
    (11). ALL
     
    对此每一个来自于之前的表的行组合,举办完全的表扫描。若是表是第3个没标志const的表,这一般倒霉,并且普通在它情状下很差。常常能够追加更加多的目录而毫不采用ALL,使得行能基于前面包车型大巴表中的常数值或列值被搜寻出。
     
     
    5.possible_keys
     
    possible_keys列建议MySQL能选拔哪个索引在该表中找到行。注意,该列完全部独用立于EXPLAIN输出所示的表的顺序。那意味在possible_keys中的某个键实际上无法按生成的表次序使用。
     
    假诺该列是NULL,则尚没有关的目录。在那种景观下,能够由此检查WHERE子句看是或不是它引用有些列或符合索引的列来升高你的询问品质。若是是如此,创制1个恰如其分的目录并且再一次用EXPLAIN检查查询
     
  2. key
     
    key列展现MySQL实际决定利用的键(索引)。就算未有选择索引,键是NULL。要想强制MySQL使用或马虎possible_keys列中的索引,在询问中采纳FO奥迪Q5CE
    INDEX、USE INDEX或然IGNORE INDEX。
     
    7.key_len
     
    key_len列展现MySQL决定采取的键长度。假若键是NULL,则长度为NULL。
    利用的目录的长度。在不损失准确性的景况下,长度越短越好
     
  3. ref
     
    ref列呈现采纳哪个列或常数与key一齐从表中精选行。
     
  4. rows
     
    rows列彰显MySQL感到它实施查询时务必检查的行数。
     
  5. Extra
     
    该列包括MySQL化解查询的详细音讯,上面详细.
     
    (1).Distinct
    假定MYSQL找到了与行相联合匹配的行,就不再搜索了
     
    (2).Not exists
    MYSQL优化了LEFT JOIN,壹旦它找到了相称LEFT JOIN标准的行,
     
    就不再寻觅了
     
    (3).Range checked for each
     
    Record(index map:#)
    从不找到能够的目录,由此对于之前方表中来的每二个行组合,MYSQL检查采用哪个索引,并用它来从表中再次回到行。那是使用索引的最慢的连接之1
     
    (4).Using filesort www.2cto.com
    探望这一个的时候,查询就需要优化了。MYSQL须求进行额外的步骤来发掘什么对回到的行排序。它依据一而再类型以及存款和储蓄排序键值和极度原则的满贯行的行指针来排序全体行
     
    (5).Using index
    列数据是从仅仅使用了目录中的消息而没有读取实际的行动的表重临的,那发生在对表的全体的央浼列都是同二个目录的有的的时候
     
    (6).Using temporary
    见到那么些的时候,查询必要优化了。那里,MYSQL供给创设三个临时表来累积结果,这一般产生在对区别的列集举行O中华VDER
    BY上,而不是GROUP BY上
     
    (7).Using where
    行使了WHERE从句来界定哪些行将与下一张表相称可能是回去给用户。假如不想回到表中的满贯行,并且连接类型ALL或index,那就会产生,或许是询问不正常
     
     
    —end—

《黄锡祥》是知数堂新设计的互相栏目,不按期给我们提供才能知识小贴士,格局不限,或咨询、或探究均可,并在当天公布答案,让我们轻巧利用零散时间就足以学到最实用的知识点。

主假使第二种用法,需求深刻的询问。

time:二零一二/02/17 Mysql查看实践计划1.语法 explain sql语句 举个例子: explain select * from t三 where
id=3952602; 2.explain输出解释 +—-+————-+—-…

2018年8月9日,周四

先看一个事例:

MySQL的表中有唯一索引,设置unique_checks为0时,还可以否写入重复值?

  1. mysql> explain select * from t_order; 
  2. +—-+————-+———+——+—————+——+———+——+——–+——-+ 
  3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra | 
  4. +—-+————-+———+——+—————+——+———+——+——–+——-+ 
  5. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100453 |       | 
  6. +—-+————-+———+——+—————+——+———+——+——–+——-+ 
  7. 1 row in set (0.03 sec) 

首先,即使设置unique_checks=0,也无能为力往唯一索引中写入重复值。

加上extended后之后:

其次,设置unique_checks=0的功用在于,批量导入数据(举个例子load
data)时,在保管导入数据中无重复值时,无需重新检查其唯一性,加速导入速度。

  1. mysql> explain extended select * from t_order; 
  2. +—-+————-+———+——+—————+——+———+——+——–+———-+——-+ 
  3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra | 
  4. +—-+————-+———+——+—————+——+———+——+——–+———-+——-+ 
  5. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100453 |   100.00 |       | 
  6. +—-+————-+———+——+—————+——+———+——+——–+———-+——-+ 
  7. 1 row in set, 1 warning (0.00 sec) 

所以,unique_checks=0并不是同意唯一约束失效,而是再批量导数据时不再逐行检查唯一性。

有要求解释一下这么些漫长表格里每壹列的意义:

2018年8月15日,周六

id SELECT识别符。这是SELECT的查询序列号
select_type

SELECT类型,可以为以下任何一种:

  • SIMPLE:简单SELECT(不使用UNION或子查询)
  • PRIMARY:最外面的SELECT
  • UNION:UNION中的第二个或后面的SELECT语句
  • DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
  • UNION RESULT:UNION 的结果
  • SUBQUERY:子查询中的第一个SELECT
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
  • DERIVED:导出表的SELECT(FROM子句的子查询)
table

输出的行所引用的表

type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

  • system:表仅有一行(=系统表)。这是const联接类型的一个特例。
  • const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
  • eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
  • ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
  • ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
  • index_merge:该联接类型表示使用了索引合并优化方法。
  • unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
  • index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range:只检索给定范围的行,使用一个索引来选择行。
  • index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
  • ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys

指出MySQL能使用哪个索引在该表中找到行

key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref 显示使用哪个列或常数与key一起从表中选择行。
rows 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered 显示了通过条件过滤出的行数的百分比估计值。
Extra

该列包含MySQL解决查询的详细信息

  • Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
  • Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
  • Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
  • Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
  • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
  • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

某人曰,在数据检索的口径中使用!=操作符时,存款和储蓄引擎会遗弃使用索引。
理由:因为检索的范围无法明确,所以使用索引作用不高,会被引擎自动改为全表扫描。

 

你承认他的布道吗?

一.select_type的说明

答:平日状态下,那个说法是精确的。当然,也有特殊景况,话不可能说纯属了。

1.UNION:

有3个测试表共80万条数据,个中type列唯有壹、2八个值,分别占比九7%和三%。

当通过union来连接三个查询结果时,第三个之后的select其select_type为UNION。

那种景色下,查询条件 WHERE type != 壹,是有极大大概也足以走索引的。

  1. mysql> explain select * from t_order where order_id=100 union select * from t_order where order_id=200; 
  2. +—-+————–+————+——-+—————+———+———+——-+——+——-+ 
  3. | id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra | 
  4. +—-+————–+————+——-+—————+———+———+——-+——+——-+ 
  5. |  1 | PRIMARY      | t_order    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
  6. |  2 | UNION        | t_order    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
  7. | NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |       | 
  8. +—-+————–+————+——-+—————+———+———+——-+——+——-+ 
  9. 3 rows in set (0.34 sec) 

上面是四个SQL的实施布置:

2.DEPENDENT UNION与DEPENDENT
SUBQUERY:

mysql> desc select * from t1 where
type = 1G ************ 1. row ************ id: 1
select_type: SIMPLE table: t1 partitions: NULL type: ref
possible_keys: type key: type key_len: 4 ref: const rows: 399731
filtered: 100.00 Extra: NULL mysql> desc select * from t1 where type
!= 1G ************ 1. row ************ id: 1
select_type: SIMPLE table: t1 partitions: NULL type: ref
possible_keys: type key: type key_len: 4 ref: const rows: 十182filtered: 拾0.00 Extra: NULL type数据布满 mysql> select type,
count(*) as cnt from t1 group by type order by cnt; +——+——–+ |
type | cnt | +——+——–+ | 2 | 38304 | | 1 | 761690 |
+——+——–+

当union作为子查询时,其中第三个union的select_type就是DEPENDENT
UNION。
率先身形查询的select_type则是DEPENDENT SUBQUERY。

2018年8月17日,周一

  1. mysql> explain select * from t_order where order_id in (select order_id from t_order where order_id=100 union select order_id from t_order where order_id=200); 
  2. +—-+——————–+————+——-+—————+———+———+——-+——–+————-+ 
  3. | id | select_type        | table      | type  | possible_keys | key     | key_len | ref   | rows   | Extra       | 
  4. +—-+——————–+————+——-+—————+———+———+——-+——–+————-+ 
  5. |  1 | PRIMARY            | t_order    | ALL   | NULL          | NULL    | NULL    | NULL  | 100453 | Using where | 
  6. |  2 | DEPENDENT SUBQUERY | t_order    | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index | 
  7. |  3 | DEPENDENT UNION    | t_order    | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index | 
  8. | NULL | UNION RESULT       | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL  |   NULL |             | 
  9. +—-+——————–+————+——-+—————+———+———+——-+——–+————-+ 
  10. 4 rows in set (0.03 sec) 

Redis集群的slot迁移是如何落到实处的?

3.SUBQUERY:

答:迁移源slot设置为migrating 状态,迁移目的slot设置为importing状态。

子查询中的第二个select其select_type为SUBQUERY。

在内部用dump &
restore命令,把数量迁移到对象节点,迁移结束之后,移除migrating和importing状态。

  1. mysql> explain select * from t_order where order_id=(select order_id from t_order where order_id=100); 
  2. +—-+————-+———+——-+—————+———+———+——-+——+————-+ 
  3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       | 
  4. +—-+————-+———+——-+—————+———+———+——-+——+————-+ 
  5. |  1 | PRIMARY     | t_order | const | PRIMARY       | PRIMARY | 4       | const |    1 |             | 
  6. |  2 | SUBQUERY    | t_order | const | PRIMARY       | PRIMARY | 4       |       |    1 | Using index | 
  7. +—-+————-+———+——-+—————+———+———+——-+——+————-+ 
  8. 2 rows in set (0.03 sec) 

在搬迁进程中1旦有数据访问,借使数据没迁移到对象节点,那么直接重回结果,假诺搬迁到目的节点,那么给客户端重临ASK重定向。

4.DERIVED:

2018年8月27日,周一

当子查询是from子句时,其select_type为DERIVED。

在大表实行ddl的进程中,若一时中断,会发生哪些景况,需求特地管理吗 ?

  1. mysql> explain select * from (select order_id from t_order where order_id=100) a; 
  2. +—-+————-+————+——–+—————+———+———+——+——+————-+ 
  3. | id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       | 
  4. +—-+————-+————+——–+—————+———+———+——+——+————-+ 
  5. |  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |             | 
  6. |  2 | DERIVED     | t_order    | const  | PRIMARY       | PRIMARY | 4       |      |    1 | Using index | 
  7. +—-+————-+————+——–+—————+———+———+——+——+————-+ 
  8. 2 rows in set (0.03 sec) 

前提表达:MySQL伍.七.二三、innodb表、“双一”

二.type的说明

1、添加/删除列,采用copy的方式

1.system,const

①.一、ctrl+c。在时下session中,手动执行ctrl+c。无影响,并且会活动删除产生的临时文件。

见下边四.DEEscortIVED的例证。在那之中第1行的type正是为system,第1行是const,那三种联接类型是最快的。

1.二、kill -九。在奉行ddl的时候,服务器爆发意外宕机或然手动实行kill
-玖。待MySQL运维后,则会自动推行InnoDB
Recovered流程。并且不会删除发生的目前文件,需求手工业管理。

2.eq_ref

2、增添/删除索引,选用INPLACE方式

在t_order表中的order_id是主键,t_order_ext表中的order_id也是主键,该表能够认为是订单表的互补新闻表,他们的关系是一对一,在上面包车型地铁例子中得以见到b表的连接类型是eq_ref,这是相当的慢的接入类型。

2.1、ctrl+c,同1.1

  1. mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id; 
  2. +—-+————-+——-+——–+—————+———+———+—————–+——+————-+ 
  3. | id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows | Extra       | 
  4. +—-+————-+——-+——–+—————+———+———+—————–+——+————-+ 
  5. |  1 | SIMPLE      | b     | ALL    | order_id      | NULL    | NULL    | NULL            |    1 |             | 
  6. |  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.order_id |    1 | Using where | 
  7. +—-+————-+——-+——–+—————+———+———+—————–+——+————-+ 
  8. 2 rows in set (0.00 sec) 

贰.二、kill -九。不会去除一时文件,也不会实践InnoDB Recovered流程并且报错
Operating system error number 2 in a file operation ….OS error: 71

3.ref

在初阶施行alter
table的进度中,在未有终止的时候,并不会写入到binglog文件中。回去微博,查看越来越多

下边包车型大巴例证在上边的事例上略作了修改,加上了准星。此时b表的连通类型产生了ref。因为具有与a表中order_id=100的相当记录都将会从b表获取。那是相比较广泛的接入类型。

主编:

  1. mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id and a.order_id=100; 
  2. +—-+————-+——-+——-+—————+———-+———+——-+——+——-+ 
  3. | id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows | Extra | 
  4. +—-+————-+——-+——-+—————+———-+———+——-+——+——-+ 
  5. |  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY  | 4       | const |    1 |       | 
  6. |  1 | SIMPLE      | b     | ref   | order_id      | order_id | 4       | const |    1 |       | 
  7. +—-+————-+——-+——-+—————+———-+———+——-+——+——-+ 
  8. 2 rows in set (0.00 sec) 

4.ref_or_null

user_id字段是二个足以为空的字段,并对该字段创立了二个索引。在下边包车型地铁查询中能够看出联接类型为ref_or_null,那是mysql为涵盖null的字段专门做的管理。在大家的表设计中应该尽量防止索引字段为NULL,因为那会额外的消耗mysql的管理时间来做优化。

  1. mysql> explain select * from t_order where user_id=100 or user_id is null; 
  2. +—-+————-+———+————-+—————+———+———+——-+——-+————-+ 
  3. | id | select_type | table   | type        | possible_keys | key     | key_len | ref   | rows  | Extra       | 
  4. +—-+————-+———+————-+—————+———+———+——-+——-+————-+ 
  5. |  1 | SIMPLE      | t_order | ref_or_null | user_id       | user_id | 5       | const | 50325 | Using where | 
  6. +—-+————-+———+————-+—————+———+———+——-+——-+————-+ 
  7. 1 row in set (0.00 sec) 

5.index_merge

日常出现在接纳一张表中的多个目录时。mysql会将三个目录合并在联合,如下例:

  1. mysql> explain select * from t_order where order_id=100 or user_id=10; 
  2. +—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+ 
  3. | id | select_type | table   | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                     | 
  4. +—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+ 
  5. |  1 | SIMPLE      | t_order | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5     | NULL |    2 | Using union(PRIMARY,user_id); Using where | 
  6. +—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+ 
  7. 1 row in set (0.09 sec) 

6.unique_subquery

该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE
some_expr)那样的子查询的ref。注意ref列,当中第2行展现的是func,注脚unique_subquery是二个函数,而不是三个一般的ref。

  1. mysql> explain select * from t_order where order_id in (select order_id from t_order where user_id=10); 
  2. +—-+——————–+———+—————–+—————–+———+———+——+——–+————-+ 
  3. | id | select_type        | table   | type            | possible_keys   | key     | key_len | ref  | rows   | Extra       | 
  4. +—-+——————–+———+—————–+—————–+———+———+——+——–+————-+ 
  5. |  1 | PRIMARY            | t_order | ALL             | NULL            | NULL    | NULL    | NULL | 100649 | Using where | 
  6. |  2 | DEPENDENT SUBQUERY | t_order | unique_subquery | PRIMARY,user_id | PRIMARY | 4       | func |      1 | Using where | 
  7. +—-+——————–+———+—————–+—————–+———+———+——+——–+————-+ 
  8. 2 rows in set (0.00 sec) 

7.index_subquery

该联接类型与地点的太像了,唯1的距离便是子查询查的不是主键而是非唯一索引。

  1. mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10); 
  2. +—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+ 
  3. | id | select_type        | table   | type           | possible_keys   | key     | key_len | ref  | rows   | Extra                    | 
  4. +—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+ 
  5. |  1 | PRIMARY            | t_order | ALL            | NULL            | NULL    | NULL    | NULL | 100649 | Using where              | 
  6. |  2 | DEPENDENT SUBQUERY | t_order | index_subquery | PRIMARY,user_id | user_id | 5       | func |  50324 | Using index; Using where | 
  7. +—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+ 
  8. 2 rows in set (0.00 sec) 

8.range

按钦点的范围拓展搜寻,很遍布。

  1. mysql> explain select * from t_order where user_id in (100,200,300); 
  2. +—-+————-+———+——-+—————+———+———+——+——+————-+ 
  3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       | 
  4. +—-+————-+———+——-+—————+———+———+——+——+————-+ 
  5. |  1 | SIMPLE      | t_order | range | user_id       | user_id | 5       | NULL |    3 | Using where | 
  6. +—-+————-+———+——-+—————+———+———+——+——+————-+ 
  7. 1 row in set (0.00 sec) 

9.index

在进展计算时相当广泛,此联接类型实际上会扫描索引树,仅比ALL快些。

  1. mysql> explain select count(*) from t_order; 
  2. +—-+————-+———+——-+—————+———+———+——+——–+————-+ 
  3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra       | 
  4. +—-+————-+———+——-+—————+———+———+——+——–+————-+ 
  5. |  1 | SIMPLE      | t_order | index | NULL          | user_id | 5       | NULL | 100649 | Using index | 
  6. +—-+————-+———+——-+—————+———+———+——+——–+————-+ 
  7. 1 row in set (0.00 sec) 

10.ALL

一体化的扫视全表,最慢的连接类型,尽恐怕的幸免。

  1. mysql> explain select * from t_order; 
  2. +—-+————-+———+——+—————+——+———+——+——–+——-+ 
  3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra | 
  4. +—-+————-+———+——+—————+——+———+——+——–+——-+ 
  5. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100649 |       | 
  6. +—-+————-+———+——+—————+——+———+——+——–+——-+ 
  7. 1 row in set (0.00 sec) 

三.extra的说明

1.Distinct

MySQL开采第2个相配行后,结束为目前的行组合搜索更加多的行。对于此项尚未找到适合的事例,求辅导。

2.Not exists

因为b表中的order_id是主键,不大概为NULL,所以mysql在用a表的order_id扫描t_order表,并物色b表的行时,假设在b表开掘1个合营的行就不再接续扫描b了,因为b表中的order_id字段不容许为NULL。那样防止了对b表的高频扫描。

  1. mysql> explain select count(1) from t_order a left join t_order_ext b on a.order_id=b.order_id where b.order_id is null;  
  2. +—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+ 
  3. | id | select_type | table | type  | possible_keys | key          | key_len | ref             | rows   | Extra                                | 
  4. +—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+ 
  5. |  1 | SIMPLE      | a     | index | NULL          | express_type | 1       | NULL            | 100395 | Using index                          | 
  6. |  1 | SIMPLE      | b     | ref   | order_id      | order_id     | 4       | test.a.order_id |      1 | Using where; Using index; Not exists | 
  7. +—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+ 
  8. 2 rows in set (0.01 sec) 

3.Range
checked for each record

那种景况是mysql未有察觉好的目录可用,速度比未有索引要快得多。

  1. mysql> explain select * from t_order t, t_order_ext s where s.order_id>=t.order_id and s.order_id<=t.order_id and t.express_type>5; 
  2. +—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+ 
  3. | id | select_type | table | type  | possible_keys        | key          | key_len | ref  | rows | Extra                                          | 
  4. +—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+ 
  5. |  1 | SIMPLE      | t     | range | PRIMARY,express_type | express_type | 1       | NULL |    1 | Using where                                    | 
  6. |  1 | SIMPLE      | s     | ALL   | order_id             | NULL         | NULL    | NULL |    1 | Range checked for each record (index map: 0x1) | 
  7. +—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+ 
  8. 2 rows in set (0.00 sec)

4.Using
filesort

在有排序子句的景况下很分布的一种意况。此时mysql会基于联接类型浏览全体符合条件的笔录,并保留排序关键字和行指针,然后排序关键字并按顺序检索行。

  1. mysql> explain select * from t_order order by express_type; 
  2. +—-+————-+———+——+—————+——+———+——+——–+—————-+ 
  3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra          | 
  4. +—-+————-+———+——+—————+——+———+——+——–+—————-+ 
  5. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100395 | Using filesort | 
  6. +—-+————-+———+——+—————+——+———+——+——–+—————-+ 
  7. 1 row in set (0.00 sec) 

5.Using index

那是性质非常高的一种状态。当查问所需的数目足以从来从索引树中寻找到时,就会冒出。上边包车型地铁例证中有大多如此的例证,不再多举例了。

6.Using
temporary

发生那种状态一般都以供给开始展览优化的。mysql须求创建一张目前表用来管理此类查询。

  1. mysql> explain select * from t_order a left join t_order_ext b on a.order_id=b.order_id group by b.order_id; 
  2. +—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+ 
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref             | rows   | Extra                           | 
  4. +—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+ 
  5. |  1 | SIMPLE      | a     | ALL  | NULL          | NULL     | NULL    | NULL            | 100395 | Using temporary; Using filesort | 
  6. |  1 | SIMPLE      | b     | ref  | order_id      | order_id | 4       | test.a.order_id |      1 |                                 | 
  7. +—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+ 
  8. 2 rows in set (0.00 sec) 

7.Using where

当有where子句时,extra都会有认证。

8.Using sort_union(…)/Using
union(…)/Using intersect(…)

上面包车型地铁例子中user_id是三个查找范围,此时mysql会选择sort_union函数来进展索引的汇合。而当user_id是一个固定值时,请参见下面type表达五.index_merge的例证,此时会采纳union函数实行索引合并。

  1. mysql> explain select * from t_order where order_id=100 or user_id>10; 
  2. +—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+ 
  3. | id | select_type | table   | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                          | 
  4. +—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+ 
  5. |  1 | SIMPLE      | t_order | index_merge | PRIMARY,user_id | user_id,PRIMARY | 5,4     | NULL |    2 | Using sort_union(user_id,PRIMARY); Using where | 
  6. +—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+ 
  7. 1 row in set (0.00 sec) 

对此Using
intersect的例证能够参考下例,user_id与express_type发生了目录交叉合并。

  1. mysql> explain select * from t_order where express_type=1 and user_id=100; 
  2. +—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+ 
  3. | id | select_type | table   | type        | possible_keys        | key                  | key_len | ref  | rows | Extra                                              | 
  4. +—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+ 
  5. |  1 | SIMPLE      | t_order | index_merge | user_id,express_type | user_id,express_type | 5,1     | NULL |    1 | Using intersect(user_id,express_type); Using where | 
  6. +—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+ 
  7. 1 row in set (0.00 sec) 

9.Using index for group-by

标明能够在目录中找到分组所需的富有数据,不须求查询实际的表。

  1. mysql> explain select user_id from t_order group by user_id; 
  2. +—-+————-+———+——-+—————+———+———+——+——+————————–+ 
  3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    | 
  4. +—-+————-+———+——-+—————+———+———+——+——+————————–+ 
  5. |  1 | SIMPLE      | t_order | range | NULL          | user_id | 5       | NULL |    3 | Using index for group-by | 
  6. +—-+————-+———+——-+—————+———+———+——+——+————————–+ 
  7. 1 row in set (0.00 sec) 

除开上边的多个验证,还索要小心rows的数值,多行之间的数值是乘积的涉嫌,能够估摸大约要拍卖的行数,若是乘积相当的大,那就很有优化的必需了。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图