mysql最佳化工具针对一笔叙述根据成本与规则制定一个具体计画，包含了执行叙述的方式、连接表的顺序、使用的存取方式等，explain就是可以让我们看具体计画细节的好工具，也是我们优化查询的好帮手。
mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.05 sec)
可以看到有这些栏位，接下来分别用更清楚的查询来一一说明唷！
方便说明把之前的表再贴一次
mysql> create table single_table(
-> id int not null auto_increment,
-> key1 varchar(10),
-> key2 int,
-> key3 varchar(100),
-> key_part1 varchar(100),
-> key_part2 varchar(100),
-> key_part3 varchar(100),
-> common_field varchar(100),
-> primary key (id),
-> key idx_key1 (key1),
-> unique key uk_key2 (key2),
-> key idx_key3 (key3),
-> key idx_key_part(key_part1, key_part2, key_part3)
-> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)
为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引
而single_table2跟single_table一样。
一个select就会是一个id，除非查询内包含子查询或是Union子句。
单一select
mysql> explain select * from single_table;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | single_table | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)
包含子查询
mysql> explain select * from single_table where key1 in (select key1 from single_table2) or key3 = 'a';
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | single_table | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10146 | 100.00 | Using where |
| 2 | SUBQUERY | single_table2 | NULL | index | idx_key1 | idx_key1 | 33 | NULL | 1 | 100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)
一个select一个id，所以这有两个id。
但要注意的是当子查询被转为半连接的时候会多一个materiakized
mysql> explain select * from single_table where key1 in (select key3 from single_table2 where common_field = 'a');
+----+--------------+---------------+------------+------+---------------+----------+---------+------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+---------------+------------+------+---------------+----------+---------+------------------+------+----------+-----------------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |
| 1 | SIMPLE | single_table | NULL | ref | idx_key1 | idx_key1 | 33 | <subquery2>.key3 | 1 | 100.00 | Using index condition |
| 2 | MATERIALIZED | single_table2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+--------------+---------------+------------+------+---------------+----------+---------+------------------+------+----------+-----------------------+
3 rows in set, 1 warning (0.00 sec)
Union子句
mysql> explain select * from single_table union select * from single_table2;
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| 1 | PRIMARY | single_table | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | NULL |
| 2 | UNION | single_table2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)
这边有一笔奇怪的纪录(id:NULL)，大家还记得Union叙述是为了把两表的结果去重，所以这只是个临时表存放s1与s2合并结果的。
mysql> explain select * from single_table union all select * from single_table2;
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | PRIMARY | single_table | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | NULL |
| 2 | UNION | single_table2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
而要注意的是，union all不需要对最终结果去重，没有临时表，因此像以上那样没有NULL纪录。
simple
查询叙述不包含子查询或union都算作simple。
mysql> explain select * from single_table;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | single_table | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)
primary
对包含union or union all or 子查询的的大查询，由几个小查询组成的，最左边的就会是primary，
而右边就依照小查询的类型显示。
**子查询**
mysql> explain select * from single_table where key1 in (select key1 from single_table2) or key3 = 'a';
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | single_table | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10146 | 100.00 | Using where |
| 2 | SUBQUERY | single_table2 | NULL | index | idx_key1 | idx_key1 | 33 | NULL | 1 | 100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)
**Union**
mysql> explain select * from single_table union select * from single_table2;
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| 1 | PRIMARY | single_table | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | NULL |
| 2 | UNION | single_table2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)
**dependent subquery**：子查询不能够转为半连接的
mysql> explain select * from single_table s1 where key1 in (select key1 from single_table2 s2 where s1.key2 = s2.key2) or key3 = 'a';
+----+--------------------+-------+------------+--------+------------------+---------+---------+----------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+------------------+---------+---------+----------------------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10146 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | eq_ref | uk_key2,idx_key1 | uk_key2 | 5 | ryan_demo_db.s1.key2 | 1 | 100.00 | Using where |
+----+--------------------+-------+------------+--------+------------------+---------+---------+----------------------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)
**derived**：衍生表
mysql> explain select * from (select key1, count(*) as c from single_table group by key1) as derived_s1 where c >1;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | NULL |
| 2 | DERIVED | single_table | NULL | index | idx_key1 | idx_key1 | 33 | NULL | 10146 | 100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)
顾名思义就是查询的表
mysql> explain select * from single_table inner join single_table2;
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------------------------+
| 1 | SIMPLE | single_table2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | single_table | NULL | ALL | NULL | NULL | NULL | NULL | 10146 | 100.00 | Using join buffer (hash join) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------------------------+
2 rows in set, 1 warning (0.01 sec)
先略过，只要知道到目前为止看到的叙述内的partiotion都是NULL。
就是我们之前讲到的存取方法类型。
但之前提到的不够完整，今天在完整的说明其他的类型。
总共有system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。
system
当表中只有一笔纪录并且该表使用的储存引擎统计资料是精确的(MyISAM、Memory)。
mysql> create table t(i int) engine=MyISAM;
Query OK, 0 rows affected (0.47 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.07 sec)
mysql> explain select * from t;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
const
根据主键或为唯一二级索引来比对的话就是const。
mysql> explain select * from single_table where id=10000;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | single_table | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
eq_ref
表连接查询时，被驱动表是透过主键或不允许NULL的唯一二级索引来相等匹配的话就eq_ref。
mysql> explain select * from single_table s1 inner join single_table2 s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| 1 | SIMPLE | s2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | s1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ryan_demo_db.s2.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
ref
当透过普通的二级索引来判断就会是ref，或是执行连接查询用普通的二级索引去相等匹配
当透过普通的二级索引来判断就会是ref
mysql> explain select * from single_table where key1='key110000';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | single_table | NULL | ref | idx_key1 | idx_key1 | 33 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from single_table where key3='key_part39999';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | single_table | NULL | ref | idx_key3 | idx_key3 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
或是执行连接查询用普通的二级索引去相等匹配
mysql> explain select * from single_table s1 inner join single_table2 s2 on s1.key1 = s2.key1;
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | s2 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 33 | ryan_demo_db.s2.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
fulltext
全文索引，略过。
ref_or_null
当执行连接查询用普通的二级索引去相等匹配且可以是NULL
mysql> explain select * from single_table where key1 = 'key19905' or key1 is null;
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | single_table | NULL | ref_or_null | idx_key1 | idx_key1 | 33 | const | 2 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)
index_merge
多个索引合并(interseciotn、union、sort-union)，觉得陌生请复习。
mysql> explain select * from single_table where key1='key19999' or key3='key39999';
+----+-------------+--------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | single_table | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 33,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where |
+----+-------------+--------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.01 sec)
unique_subquery
这是针对包含in语法的查询，当查询最佳化工具决定要将in子查询转为exist查询，且使用主键或唯一二级索引相等匹配。
mysql> explain select * from single_table s1 where id in (select id from single_table2 s2 where s1.key2 = s2.key2) or key3 = 'key310000';
+----+--------------------+-------+------------+-----------------+-----------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-----------------+-----------------+---------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10146 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,uk_key2 | PRIMARY | 4 | func | 1 | 100.00 | Using where |
+----+--------------------+-------+------------+-----------------+-----------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
index_subquery
与unique_subquery类似，差别在於使用普通二级索引相等匹配。
mysql> explain select * from single_table s1 where key3 in (select key3 from single_table2 s2 where s1.common_field = s2.common_field) or key1 = 'key110000';
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 10146 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key3 | idx_key3 | 303 | func | 1 | 100.00 | Using where |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
range
不赘述。
mysql> explain select * from single_table where key1 in ('key11','key12','key13');
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | single_table | NULL | range | idx_key1 | idx_key1 | 33 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)
index
当使用索引，但要扫遍所有索引资料
mysql> explain select key_part2 from single_table where key_part3 = 'key_part31';
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | single_table | NULL | index | idx_key_part | idx_key_part | 909 | NULL | 10146 | 10.00 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
像这个不需要扫遍所有索引就不会是index
mysql> explain select key_part2 from single_table where key_part1 = 'key_part11';
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | single_table | NULL | ref | idx_key_part | idx_key_part | 303 | const | 1 | 100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
<<: [Day25] Angular 的 Module 与 Component
前言 因为公司前端资料已经处理成单层结构，所以都没注意到浅拷贝、深拷贝的实际差别。 在读完高手文章後...
今天要来补充 Pointfree 的概念， 这也同样和 pipe 有关。 function 运算的过...
「DOM API定义了JavaScript存取、改变HTML架构、内容、样式的方法，甚至对节点绑定...
有的人MacBook或Mac上有时会显示显示一条讯息：“您的系统已耗尽应用程序内存"/“s...
JavaScript 变数可以转换为新变数或其他资料类型，就目前我所知道的大概可以分成两种： 1.通...