原标题:《叶问》第7期

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

Mysql Explain 详细介绍,mysqlexplain

Mysql Explain 那里做二个资料的两全整治。

一.语法

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=三 向上实践.

  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中的第二个或后边的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类型。它用在2个索引的具有片段被连接使用并且索引是UNIQUE或PLX570IMAEscortY
KEY。

eq_ref可以用于选拔=
操作符对比的带索引的列。比较值可以为常量或2个行使在该表后边所读取的表的列的表明式。

在底下的例子中,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或PQashqaiIMAPRADOY
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

只检索给定范围的行,使用1个索引来采用行。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子句看是或不是它引用有个别列或符合索引的列来提升你的查询质量。要是是那般,创设2个相宜的目录并且再一次用EXPLAIN检查查询

  1. key

key列显示MySQL实际决定选用的键(索引)。假使未有选拔索引,键是NULL。要想强制MySQL使用或不经意possible_keys列中的索引,在询问中应用FO景逸SUVCE
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:#)

尚未找到能够的目录,因而对此从眼下表中来的每1个行组合,MYSQL检查选取哪个索引,并用它来从表中重返行。那是应用索引的最慢的一而再之壹

(4).Using filesort

总的来看这些的时候,查询就须要优化了。MYSQL供给实行额外的步调来发掘什么对回到的行排序。它依照连年类型以及存款和储蓄排序键值和相称原则的漫天行的行指针来排序全体行

(5).Using index

列数据是从仅仅使用了目录中的消息而从未读取实际的行进的表再次回到的,那产生在对表的整个的伸手列都以同八个目录的有的的时候

(6).Using temporary

看到那么些的时候,查询要求优化了。那里,MYSQL须要创立三个一时半刻表来囤积结果,这平时产生在对分歧的列集举办OHavalDER
BY上,而不是GROUP BY上

(7).Using where

选拔了WHERE从句来限制哪些行将与下一张表匹配可能是回去给用户。倘诺不想回到表中的整套行,并且连接类型ALL或index,那就会时有产生,恐怕是询问非凡

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

Explain 详细介绍,mysqlexplain Mysql
Explain 那里做三个质感的八面后珑整治。 壹.语法 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 向上执行.
 

叶问

分类: Mysql/postgreSQL

  1. select_type
     
    不畏select类型,能够有以下三种
     
    (1) SIMPLE
    粗略SELECT(不使用UNION或子查询等) 举例:
    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 |       |
    +—-+————-+——-+——-+——————-+———+———+——-+——+——-+
     
    (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
     
    来得那1行的数量是关于哪张表的.
    有时候不是实事求是的表名字,看到的是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
     
    表最多有2个相配行,它将在查询起初时被读取。因为仅有一行,在那行的列值可被优化器剩余部分认为是常数。const表异常快,因为它们只读取一回!
     
    const用于用常数值相比较PHummerH二IMAHummerH二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
     
    对于各个来自于前方的表的行组合,从该表中读取1行。那大概是最棒的联网类型,除了const类型。它用在1个目录的具有片段被接通使用并且索引是UNIQUE或P科雷傲IMAKugaY
    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或PRubiconIMA锐界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是3个目录查找函数,能够完全替换子查询,效能更加高。
     
    (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子句看是还是不是它引用有个别列或符合索引的列来升高你的询问质量。借使是如此,创建一个适合的目录并且再度用EXPLAIN检查查询
     
  2. key
     
    key列展现MySQL实际调整选拔的键(索引)。假使未有选拔索引,键是NULL。要想强制MySQL使用或忽略possible_keys列中的索引,在询问中应用FOOdysseyCE
    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检查选用哪个索引,并用它来从表中重回行。那是使用索引的最慢的接连之一
     
    (4).Using filesort www.2cto.com
    观望那么些的时候,查询就需求优化了。MYSQL供给进行额外的步骤来开采怎么对回到的行排序。它依据连年类型以及存款和储蓄排序键值和卓殊原则的全套行的行指针来排序全体行
     
    (5).Using index
    列数据是从仅仅使用了目录中的音信而并未有读取实际的行路的表重返的,那爆发在对表的整整的哀求列都是同1个目录的有的的时候
     
    (6).Using temporary
    看来那些的时候,查询要求优化了。那里,MYSQL要求创设2个一时半刻表来储存结果,这一般发生在对分歧的列集举行OLX570DER
    BY上,而不是GROUP BY上
     
    (7).Using where
    使用了WHERE从句来界定哪些行将与下一张表相配可能是回去给用户。假使不想回去表中的全体行,并且连接类型ALL或index,那就会时有发生,或然是查询有标题
     
     
    —end—

《叶溢》是知数堂新布署的并行栏目,不定时给大家提供手艺知识小贴士,方式不限,或咨询、或探讨均可,并在当天发布答案,让大家轻巧利用零散时间就足以学到最实用的知识点。

重中之重是第二种用法,必要深远的打听。

time:二零一二/02/一七 Mysql查看实行安排一.语法 explain sql语句 比如: explain select * from t3 where
id=3952602; 二.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性,加快导入速度。

  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:

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

当通过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: 拾18二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。
第2身长查询的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表、“双1”

二.type的说明

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

1.system,const

壹.1、ctrl+c。在脚下session中,手动实行ctrl+c。无影响,并且会自行删除发生的权且文件。

见下边四.DELANDIVED的例子。在那之中第二行的type就是为system,第二行是const,那二种联接类型是最快的。

1.二、kill -玖。在施行ddl的时候,服务器发生意外宕机大概手动试行kill
-玖。待MySQL运营后,则会自动实行InnoDB
Recovered流程。并且不会去除爆发的目前文件,需求手工业处理。

2.eq_ref

2、增加/删除索引,采取INPLACE格局

在t_order表中的order_id是主键,t_order_ext表中的order_id也是主键,该表能够感觉是订单表的补给消息表,他们的涉及是1对一,在上边包车型客车例证中得以见到b表的连接类型是eq_澳门金沙4787.com官网 ,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=十0的协作记录都将会从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字段是3个足以为空的字段,并对该字段创立了1个索引。在底下的询问中得以观望联接类型为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列,个中第3行展现的是func,表明unique_subquery是3个函数,而不是二个惯常的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

在进展计算时10分广泛,此联接类型实际上会扫描索引树,仅比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表发掘2个相配的行就不再接续扫描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的数值,多行之间的数值是乘积的关系,能够估量大致要拍卖的行数,借使乘积非常的大,那就很有优化的画龙点睛了。

相关文章