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

原标题:《叶问》第7期

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

Mysql Explain 详细介绍,mysqlexplain

Mysql Explain 那里做叁个素材的总总林林整治。

一.语法

explain < table_name >

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

2.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中的第四个或后边的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

子查询中的第三个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联接类型的3个特例。表仅有一行满足条件.如下(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福睿斯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类型。它用在2个目录的具有片段被接通使用并且索引是UNIQUE或P牧马人IMA帕杰罗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君越IMA卡宴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

只检索给定范围的行,使用多少个索引来摘取行。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快,因为索引文件一般比数据文件小。

当查问只使用作为单索引1有的的列时,MySQL能够选取该联接类型。

(11). ALL

对于每一个来自于从前的表的行组合,进行全部的表扫描。若是表是率先个没标识const的表,这一般不佳,并且普通在它意况下很差。平日能够增添更加多的目录而毫不选拔ALL,使得行能基于前边的表中的常数值或列值被搜寻出。

5.possible_keys

possible_keys列提议MySQL能运用哪个索引在该表中找到行。注意,该列完全部独用立于EXPLAIN输出所示的表的主次。那意味在possible_keys中的有个别键实际上无法按生成的表次序使用。

要是该列是NULL,则未有相关的目录。在那种情景下,能够经过检查WHERE子句看是或不是它引用有个别列或符合索引的列来提升你的询问质量。倘使是那般,制造1个得体的目录并且再次用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:#)

从未找到理想的目录,由此对于从眼下表中来的每二个行组合,MYSQL检查接纳哪个索引,并用它来从表中再次来到行。那是利用索引的最慢的连天之1

(4).Using filesort

观看那几个的时候,查询就须要优化了。MYSQL供给开始展览额外的手续来开掘什么样对回到的行排序。它依照一连类型以及存款和储蓄排序键值和相配原则的全体行的行指针来排序全体行

(5).Using index

列数据是从仅仅使用了目录中的音信而从不读取实际的走动的表再次来到的,那产生在对表的整套的伏乞列都以同贰个目录的部分的时候

(6).Using temporary

来看这几个的时候,查询要求优化了。那里,MYSQL须求创设1个临时表来存款和储蓄结果,这一般发生在对两样的列集实行O奥迪Q5DER
BY上,而不是GROUP BY上

(7).Using where

运用了WHERE从句来限制哪些行将与下一张表相配只怕是回来给用户。如若不想回去表中的1切行,并且再三再四类型ALL或index,那就会生出,也许是询问有毛病

谢谢阅读,希望能帮助到大家,多谢大家对本站的扶助!

Explain 详细介绍,mysqlexplain Mysql
Explain 那里做1个材料的两全整治。 1.语法 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=3 向上推行.详细介绍,explain用法和结果的意思。
 

叶问

分类: 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
     
    子查询中的第三个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联接类型的贰个特例。表仅有一行满足条件.如下(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个相配行,它就要询问伊始时被读取。因为仅有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类型。它用在三个索引的富有片段被连接使用并且索引是UNIQUE或P逍客IMA奥德赛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瑞鹰IMA中华VY
    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是1个目录查找函数,能够完全替换子查询,功能更加高。
     
    (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
     
    对此各类来自于从前的表的行组合,实行1体化的表扫描。即便表是第多个没标识const的表,这一般倒霉,并且普通在它处境下很差。常常能够追加愈多的目录而不要选择ALL,使得行能基于前面包车型大巴表中的常数值或列值被搜寻出。
     
     
    5.possible_keys
     
    possible_keys列提出MySQL能使用哪个索引在该表中找到行。注意,该列完全部独用立于EXPLAIN输出所示的表的顺序。那象征在possible_keys中的有个别键实际上无法按生成的表次序使用。
     
    万1该列是NULL,则从未有关的目录。在那种境况下,能够透过检查WHERE子句看是或不是它引用有个别列或符合索引的列来提升你的查询品质。借使是这么,创设多个老少咸宜的目录并且再度用EXPLAIN检查查询
     
  2. key
     
    key列呈现MySQL实际调节利用的键(索引)。纵然没有采用索引,键是NULL。要想强制MySQL使用或不经意possible_keys列中的索引,在查询中动用FO中华VCE
    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,1旦它找到了相配LEFT JOIN标准的行,
     
    就不再寻觅了
     
    (3).Range checked for each
     
    Record(index map:#)
    未曾找到能够的目录,由此对此从目前表中来的每3个行组合,MYSQL检查采纳哪个索引,并用它来从表中重回行。那是运用索引的最慢的连年之一
     
    (4).Using filesort www.2cto.com
    看样子那么些的时候,查询就须求优化了。MYSQL需求开始展览额外的手续来开采什么样对回到的行排序。它根据连年类型以及存款和储蓄排序键值和同盟原则的一切行的行指针来排序全部行
     
    (5).Using index
    列数据是从仅仅使用了目录中的音讯而从未读取实际的行路的表再次来到的,那爆发在对表的一体的呼吁列都以同四个索引的一部分的时候
     
    (6).Using temporary
    观察这些的时候,查询须要优化了。那里,MYSQL须要成立贰个权且表来囤积结果,那平日发生在对两样的列集举办O帕杰罗DER
    BY上,而不是GROUP BY上
     
    (7).Using where
    使用了WHERE从句来界定哪些行将与下一张表相称或许是回去给用户。借使不想回去表中的壹切行,并且接二连三类型ALL或index,那就会生出,恐怕是查询有标题
     
     
    —end—

《黄锡祥》是知数堂新规划的竞相栏目,不按期给大家提供本领知识小贴士,方式不限,或咨询、或探讨均可,并在同壹天发布答案,让我们轻巧利用零散时间就能够学到最实用的知识点。

最首假如第三种用法,必要深远的垂询。

time:二零一一/02/一7 Mysql查看推行陈设壹.语法 explain sql语句 举例: explain select * from t三 where
id=395260二; 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并不是允许唯壹约束失效,而是再批量导数据时不再逐行检查唯1性。

有须要解释一下那一个漫长表格里每一列的含义:

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:

有一个测试表共80万条数据,个中type列唯有一、二多个值,分别占比玖柒%和三%。

当通过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: 四 ref: const rows: 101八二filtered: 十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) 

在搬迁进度中只要有数量访问,假若数额没迁移到目的节点,那么直接重回结果,借使搬迁到目标节点,那么给客户端重回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⑤.7.二三、innodb表、“双一”

二.type的说明

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

1.system,const

1.1、ctrl+c。在目前session中,手动推行ctrl+c。无影响,并且会自动删除爆发的暂且文件。

见下面四.DE安德拉IVED的事例。个中第三行的type正是为system,第三行是const,那三种联接类型是最快的。

一.贰、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 二 in a file operation ….OS error: 7一

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列,在这之中第1行展现的是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. 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.Not exists

因为b表中的order_id是主键,不恐怕为NULL,所以mysql在用a表的order_id扫描t_order表,并搜求b表的行时,假设在b表发掘贰个合营的行就不再继续扫描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

在有排序子句的状态下很常见的1种情状。此时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是3个固定值时,请参见下边type表达5.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地图