【365bet官网】Mysql查看实行计划,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;

二.explain输出解释

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

1.id

【365bet官网】Mysql查看实行计划,explain用法和结果的含义。  笔者的敞亮是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中的第一个或前面包车型客车SELECT语句.举例

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

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

(4).DEPENDENT UNION

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

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

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

(4).UNION RESULT

UNION的结果。

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

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

(5).SUBQUERY

子查询中的第3个SELECT.

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

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

(6).  DEPENDENT SUBQUERY

子查询中的第1个SELECT,取决于外面包车型客车询问

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

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

7).DERIVED

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

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

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

3.table

体现这一行的多寡是有关哪张表的.

偶然不是真实的表名字,看到的是derivedx(x是个数字,笔者的通晓是第几步实施的结果)

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

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

4.type

那列很要紧,突显了连接使用了哪类档期的顺序,有无使用索引.

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

(1).system

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

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

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

(2).const

表最多有三个相称行,它就要询问开端时被读取。因为仅有1行,在那行的列值可被优化器剩余部分感到是常数。const表相当慢,因为它们只读取3次!

const用于用常数值比较PTiggoIMA奥迪Q五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奥德赛IMARubiconY
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牧马人IMAOdysseyY
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是2个目录查找函数,能够完全替换子查询,作用更加高。

(8).index_subquery

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

value IN (SELECT key_column FROM
single_table WHERE some_expr)

(9).range

只检索给定范围的行,使用2个索引来挑选行。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子句看是不是它引用有个别列或符合索引的列来提升你的查询质量。假若是这么,成立贰个方便的目录并且再次用EXPLAIN检查查询

  1. key

key列展现MySQL实际调节运用的键(索引)。假若未有选用索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中央银行使FO昂CoraCE
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

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

(2).Not exists

MYSQL优化了LEFT JOIN,一旦它找到了相称LEFT JOIN标准的行,

就不再找寻了

(3).Range checked for each

Record(index map:#)

不曾找到能够的目录,由此对于从日前表中来的每种行组合,MYSQL检查选择哪个索引,并用它来从表中再次回到行。那是行使索引的最慢的总是之1

(4).Using filesort

探望这些的时候,查询就需求优化了。MYSQL供给张开额外的步子来开采怎么对回到的行排序。它依照连年类型以及存款和储蓄排序键值和相配原则的1体行的行指针来排序全体行

(5).Using index

列数据是从仅仅使用了目录中的音讯而未有读取实际的行路的表重回的,那产生在对表的漫天的央求列都以同三个索引的一些的时候

(6).Using temporary

总的来看这一个的时候,查询要求优化了。这里,MYSQL需求创建多少个目前表来囤积结果,这一般产生在对两样的列集举办O奥迪Q5DER
BY上,而不是GROUP BY上

(7).Using where

选择了WHERE从句来限制哪些行将与下一张表匹配恐怕是回来给用户。假如不想回去表中的全套行,并且总是类型ALL或index,这就会发生,大概是询问不平常

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

Explain 详细介绍,mysqlexplain Mysql
Explain 那里做贰个素材的无微不至整治。 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;
 
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 向上执行.
 

叶问

分类: 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中的第2个或前边的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
     
    子查询中的第2个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联接类型的贰个特例。表仅有一行满足条件.如下(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表不慢,因为它们只读取2次!
     
    const用于用常数值相比较PRubiconIMARY
    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类型。它用在2个索引的有着片段被接通使用并且索引是UNIQUE或P汉兰达IMA中华VY
    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或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快,因为索引文件一般比数据文件小。
     
    当查问只利用作为单索引一局部的列时,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列中的索引,在询问中应用FO景逸SUVCE
    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:#)
    从未有过找到完美的目录,因而对于在此之前边表中来的各种行组合,MYSQL检查采用哪个索引,并用它来从表中再次来到行。那是采纳索引的最慢的总是之一
     
    (4).Using filesort www.2cto.com
    观望那一个的时候,查询就须要优化了。MYSQL需求进行额外的手续来发掘怎么对回到的行排序。它遵照一连类型以及存款和储蓄排序键值和合作原则的全体行的行指针来排序全体行
     
    (5).Using index
    列数据是从仅仅使用了目录中的音讯而并没有读取实际的走动的表再次来到的,那爆发在对表的整整的乞请列都以同四个目录的1对的时候
     
    (6).Using temporary
    观望那个的时候,查询必要优化了。这里,MYSQL须求创制一个一时表来囤积结果,这一般产生在对两样的列集进行O奥迪Q5DER
    BY上,而不是GROUP BY上
     
    (7).Using where
    动用了WHERE从句来限制哪些行将与下一张表相称可能是回到给用户。若是不想再次回到表中的整个行,并且连接类型ALL或index,这就会发生,或然是查询有标题
     
     
    —end—

《叶继问》是知数堂新规划的互动栏目,不按期给我们提供才能知识小贴士,情势不限,或咨询、或商量均可,并在同1天公告答案,让我们轻巧利用零散时间就足以学到最实用的知识点。

主借使第三种用法,需求深远的刺探。

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

有须要解释一下这几个漫长表格里每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的实行安插:

365bet官网 ,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: 1018二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状态。

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

在内部用dump &
restore命令,把多少迁移到目的节点,迁移甘休之后,移除migrating和importing状态。

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

在搬迁进程中1旦有多少访问,假使数据没迁移到目的节点,那么直接再次来到结果,假设搬迁到目的节点,那么给客户端重回ASK重定向。

4.DERIVED:

2018年8月27日,周一

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

在大表推行ddl的经过中,若权且中断,会生出什么样境况,须求特地管理吗 ?

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

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

二.type的说明

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

1.system,const

一.一、ctrl+c。在当前session中,手动推行ctrl+c。无影响,并且会自行删除产生的一时文件。

见上边四.DE路虎极光IVED的事例。在那之中第三行的type正是为system,第三行是const,那二种联接类型是最快的。

一.2、kill -九。在实践ddl的时候,服务器爆发意外宕机也许手动推行kill
-玖。待MySQL运营后,则会自行实施InnoDB
Recovered流程。并且不会删除发生的目前文件,须求手工管理。

2.eq_ref

二、增加/删除索引,选择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) 

2.2、kill -玖。不会去除一时文件,也不会奉行InnoDB Recovered流程并且报错
Operating system error number 二 in a file operation ….OS error: 7壹

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字段是三个可以为空的字段,并对该字段创设了四个索引。在底下的询问中能够看出联接类型为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列,在那之中第三行显示的是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

在有排序子句的情况下很常见的一种意况。此时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是1个寻觅范围,此时mysql会使用sort_union函数来开始展览索引的统1。而当user_id是3个固定值时,请参考上边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的数值,多行之间的数值是乘积的涉及,能够推测差不离要管理的行数,若是乘积非常的大,这就很有优化的要求了。

发表评论

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