一、准备
1.创建表
-- 班级表：cid 主键，用于 const / eq_ref
CREATE TABLE clazz (
cid INT PRIMARY KEY,
cname VARCHAR(20) NOT NULL
) ENGINE=InnoDB;
-- 学生表：sid 主键；cid、age 普通索引
CREATE TABLE student (
sid INT PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
cid INT NOT NULL,
age INT NOT NULL,
info VARCHAR(100),
KEY idx_cid (cid),
KEY idx_age (age)
) ENGINE=InnoDB;
-- 单行表：用于 system
CREATE TABLE single_row (
id INT PRIMARY KEY,
name VARCHAR(20)
) ENGINE=MyISAM;2.插入数据
INSERT INTO clazz VALUES
(1,'一班'),(2,'二班'),(3,'三班');
INSERT INTO student VALUES
(1,'张三',1,18,'a'),
(2,'李四',1,19,'b'),
(3,'王五',2,20,'c'),
(4,'赵六',2,21,'d'),
(5,'钱七',3,22,'e');
INSERT INTO single_row VALUES (1,'only');三、type类型
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL常见的有：
system>const>eq_ref>ref>range>index>ALL一般来说，得保证查询至少达到 range 级别，最好能达到 ref
1.system
system：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计
触发条件：表只有一行，优化器直接知道结果只有一行
预期：type = system
mysql> EXPLAIN SELECT * FROM single_row;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | single_row | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)2.const
const：查找条件是主键或者唯一，并且只匹配一行数据,意味着不用往下扫描了
触发条件：通过主键或唯一索引进行等值查询，最多返回一行
预期：type = const，key = PRIMARY
mysql> EXPLAIN SELECT * FROM student WHERE sid = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM clazz WHERE cid = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | clazz | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)3.eq_ref
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
触发条件：多表关联时，被驱动表通过主键或唯一索引等值查找，前表每一行只匹配后表一行。
预期：clazz 这一行 type = eq_ref，key = PRIMARY。
-- STRAIGHT_JOIN：强制按书写顺序连接，左表 student 一定是驱动表，右表 clazz 一定是被驱动表
mysql> EXPLAIN SELECT s.sid, s.sname, c.cname FROM student s STRAIGHT_JOIN clazz c ON s.cid = c.cid;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | s | NULL | ALL | idx_cid | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test1.s.cid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)4.ref
ref：查找条件使用了索引，并且不是主键或唯一
触发条件：使用非唯一索引进行等值查询，可能返回多行
预期：type = ref，key = idx_cid
mysql> EXPLAIN SELECT * FROM student WHERE cid = 1;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_cid | idx_cid | 4 | const | 2 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)5.range
range：查找条件使用了索引，值是个范围（beween、>等）
触发条件：使用索引进行范围扫描，如 >、<、BETWEEN、IN 等
预期：type = range，key = PRIMARY 或 idx_age
mysql> EXPLAIN SELECT * FROM student WHERE sid BETWEEN 2 AND 4;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM student WHERE age > 18;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_age | idx_age | 4 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)6.index
index：这种连接类型只是另外一种形式的全表扫描，只不过它的扫描是按照索引的顺序,all是沿着磁盘扫描，index是沿着索引扫描
索引覆盖：查询的值就是索引的值，不用扫描表
触发条件：扫描整个索引树，而不是全表。常见于覆盖索引查询
预期：type = index，key = idx_cid，Extra = Using index
mysql> EXPLAIN SELECT cid FROM student;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_cid | 4 | NULL | 5 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)7.ALL
ALL：Full Table Scan，将遍历全表以找到匹配的行
触发条件：没有可用索引，或索引失效，进行全表扫描
预期：type = ALL，key = NULL
mysql> EXPLAIN SELECT * FROM student WHERE sname = '张三';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)