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常见类型案例
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 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
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 |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
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');
