007--Explain实战详解：type分析

最新推荐文章于 2021-11-21 14:04:39 发布

enzoism

最新推荐文章于 2021-11-21 14:04:39 发布

阅读量132

点赞数

本文链接：https://blog.csdn.net/weixin_42288943/article/details/88401520

版权

1.type常见类型展示

type 类型	说明
system	表仅有一行(=系统表)。这是 const 连接类型的一个特例。
const	const用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用 System。
eq_ref	const用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用 System。
ref	连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值。
ref_or_null	如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。
index_merge	说明索引合并优化被使用了。
unique_subquery	在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery	在某些 IN 查询中使用此种类型 , 与 unique_subquery 类似,但是查询的是非唯一性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range	只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range。
index	全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
all	最坏的情况,从头到尾全表扫描。

2.type常见类型案例

ALL：全表扫描

 EXPLAIN SELECT * from t2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

index：全表扫描（index和all的区别在于index类型只遍历索引树，就是说：两个都是全表扫描，index从索引树种挨个查询，all从硬盘上挨个查询）

mysql> EXPLAIN SELECT t3.* from t2,t3 where  t3.id = t2.id;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
|  1 | SIMPLE      | t2    | index  | PRIMARY       | PRIMARY | 4       | NULL         |    1 | Using index |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | mysql1.t2.id |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

mysql> EXPLAIN SELECT t2.id from t2  where  t2.id in (1,2,3);
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t2    | index | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

range

mysql> EXPLAIN SELECT t3.id from t3 where  t3.age in (10.20) and t3.id in (1,2,3);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

mysql> EXPLAIN SELECT t3.id from t3 where  t3.id in (1,2,3) ;
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t3    | index | PRIMARY       | index_name | 33      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT t3.id from t3 where  t3.age in (10.20);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

ref：非唯一性索引，对于每个索引键，返回匹配的所有行（扫描和查找的综合体）

mysql> EXPLAIN SELECT t3.* from t3 where  t3.`name`='downeyjr_1';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | ALL  | index_name    | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于在primary key或者unique索引扫描（唯一性扫描）

mysql> EXPLAIN SELECT * from t1,t2 where t1.id = t2.id;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
|  1 | SIMPLE      | t2    | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    1 |       |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | mysql1.t2.id |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+

const：在primary key或者unique索引比较中，指定单条查询结果(system是const特例，代表系统表中只有一条数据)

mysql> EXPLAIN SELECT t1.id from t1 WHERE t1.id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

mysql> EXPLAIN SELECT * from (SELECT id from t1 where id=1)t2;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |             |
|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 4       |      |    1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+

NULL

mysql> EXPLAIN SELECT 1+1 from dual;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

mysql> EXPLAIN SELECT SYSDATE() from dual;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

3.type使用总结

区分概念：查找和扫描：
查找：进行所有东西的全匹配，从中间选取我们需要的东西
扫描：有过滤和筛选功能

4.sql执行文件

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('1', 'downeyjr_1');
INSERT INTO `t1` VALUES ('2', 'downeyjr_2');
INSERT INTO `t1` VALUES ('3', 'downeyjr_3');

-- ----------------------------
-- Table structure for t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES ('1', 'downeyjr_1');

-- ----------------------------
-- Table structure for t3
-- ----------------------------
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(5) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t3
-- ----------------------------
INSERT INTO `t3` VALUES ('1', 'downeyjr_1', '10');
INSERT INTO `t3` VALUES ('2', 'downeyjr_1', '20');
INSERT INTO `t3` VALUES ('3', 'downeyjr_1', '30');