一、MySQL优化之explain执行计划介绍及id和table以及select_type属性

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，通过执行计划explain分析select查询语句和表结构的性能

执行计划explain可以分析的信息

• 表的读取顺序（id,table）
• 数据读取操作类型（select_type）
• 哪些索引可能被使用（possible_keys）
• 哪些索引实际被使用（key）
• 每张表有多少行被优化器查询（rows）

执行计划explain使用方式

• explain + select 查询语句
• 执行计划包含的属性字段

           id: select查询书号，可判断顺序，与table一起使用
  select_type: 查询类型
        table: 查询使用的table名称，与id一起使用
         type: 访问类型
possible_keys: 可能使用到的索引
          key: 实际使用到的索引
      key_len: 索引大小
          ref: 访问类型type的具体值
         rows: 查询数据影响的行数
        Extra: 扩展字段信息

explain之id与table

id:select查询的序列号，包含一组数字，表示查询中执行select子句或者操作的顺序

table:使用到的数据库表名称，包含自动创建的临时表

id与table两个属性可以判断表的读取顺序

1. id相同时，执行顺序从上到下

mysql> explain select * from t1,t2,t3 where t1.t2_id = t2.id and t1.t3_id = t3.id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t3    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | NULL                                               |
|  1 | SIMPLE      | t2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using join buffer (Block Nested Loop)              |
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

id相同，执行顺序由上至下，table执行顺序是 t3 t2 t1
三张表数据量不同，影响执行计划的顺序，以执行结果为准

2. id:不相同，值大的先执行

mysql> explain select * from t2 
    where t2.id= (select t1.t2_id from t1 
        where t1.id = (select t3.id from t3 where t3.id=3));
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | t2    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  2 | SUBQUERY    | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  3 | SUBQUERY    | t3    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

如果是子查询，id的顺序会递增，id值越大，越先执行 t3 t1 t2
这里只演示执行结果，查看顺序，不管外键关联的准确性

3. id有相同值也有不同的值：

mysql> explain select t1.* from (select * from t2 where t2.id = 4) tmp ,t1 where tmp.id = t1.t2_id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL        |
|  1 | PRIMARY     | t1         | ALL    | NULL          | NULL    | NULL    | NULL  |    4 | Using where |
|  2 | DERIVED     | t2         | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+

*id值为 1 1 2 *
table值为 derived2 t1 t2
执行顺序：t2 derived2 t1
先读取最大值的表 t2，再从上至下读取 derived2 和 t1

explain之select_type

simple:简单查询,简单的select查询，查询中不包含子查询或者union查询

primary：主键查询,查询中若包含任何复杂的子部分，最外层查询则被标记为primary

subquery:子查询,在select或者where列表中包含子查询

derived:临时表,在from表中包含的子查询被标记为derived(衍生)，MySQL会递归执行这些子查询，把结果放在零时表中

union：联合查询,如果第二个select出现在union之后，则被标记为union查询.如果包含在from字句的查询中，外层select将被标记为derived

union result:联合查询中查询的结果,从union表获取结果的select查询

写在最后-使用到的数据库表

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t2_id` int(11) NOT NULL,
  `t3_id` int(11) NOT NULL,
  `id_card` varchar(18) DEFAULT NULL,
  `tel` varchar(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_idcard_tel` (`id_card`,`tel`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;