• const， system

mysql对数据查询时做了优化，对primary key或unique key的所有列与常数比较，所以表最多可以匹配到一条数据，效率非常高。sysytem是const的一个特例，当表中只有一条数据匹配时为system

mysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+-------+------------+-------+------------> ---+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------> ---+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

• ref_eq

• primary key 或 unique key 的所有部分被用来连接使用时，最多只会返回一条符合条件的记录(个人理解一般只在表连接时才会出现这种类型，欢迎纠错哈)
• mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
  ±—±------------±-----------±-----------±-------±--------------±--------±--------±-------------------------±-----±---------±------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±-----------±-----------±-------±--------------±--------±--------±-------------------------±-----±---------±------+
  | 1 | SIMPLE | film_actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
  | 1 | SIMPLE | film | NULL | eq_ref | PRIMARY | PRIMARY | 4 | nacos.film_actor.film_id | 1 | 100.00 | NULL |
  ±—±------------±-----------±-----------±-------±--------------±--------±--------±-------------------------±-----±---------±------+
  2 rows in set, 1 warning (0.02 sec)

• ref

• 使用普通索引或者唯一索引的部分前缀，索引要和某个值比较，一般或有多条记录符合条件
• 1、简单查询,name不是唯一索引
• mysql> explain select * from film where name = ‘film1’;
  ±—±------------±------±-----------±-----±--------------±---------±--------±------±-----±---------±------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±------±-----------±-----±--------------±---------±--------±------±-----±---------±------------+
  | 1 | SIMPLE | film | NULL | ref | idx_name | idx_name | 33 | const | 1 | 100.00 | Using index |
  ±—±------------±------±-----------±-----±--------------±---------±--------±------±-----±---------±------------+
  1 row in set, 1 warning (0.02 sec)
• 2、表连接查询，联合索引：KEY idx_film_actor_id (film_id,actor_id)
• mysql> explain select film_id from film left join film_actor on film.id = film_actor.film_id;
  ±—±------------±-----------±-----------±------±------------------±------------------±--------±--------------±-----±---------±------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±-----------±-----------±------±------------------±------------------±--------±--------------±-----±---------±------------+
  | 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
  | 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | nacos.film.id | 1 | 100.00 | Using index |
  ±—±------------±-----------±-----------±------±------------------±------------------±--------±--------------±-----±---------±------------+
  2 rows in set, 1 warning (0.00 sec)

• range

• range 使用到索引且是个范围查询，包括：>,<, >=,<=,between,in
• mysql> explain select * from actor where id > 1;
  ±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
  | 1 | SIMPLE | actor | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
  ±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
  1 row in set, 1 warning (0.01 sec)

• index

• 使用到索引，但不是从索引的根节点开始检索，而是扫描二级索引树的叶子节点，性能少略好于全表扫描（二级索引的叶子节点只包含索引字段和主键id，数据块相对小，而全表扫描本质上是扫描主键索引的叶子节点，包括主键id和整行数据块，数据块相对大，在io传输时数据块小的性能块一些）
• 案例1： mysql> explain select * from film;
  ±—±------------±------±-----------±------±--------------±---------±--------±-----±-----±---------±------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±------±-----------±------±--------------±---------±--------±-----±-----±---------±------------+
  | 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
  ±—±------------±------±-----------±------±--------------±---------±--------±-----±-----±---------±------------+
  1 row in set, 1 warning (0.00 sec)
• 案例2：

表结构:

• mysql> show create table people;
  ±-------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table |
  ±-------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | people | CREATE TABLE people (
  last_name varchar(255) NOT NULL,
  first_name varchar(255) NOT NULL,
  bob date NOT NULL,
  KEY index_union (last_name,first_name,bob)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  ±-------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

• mysql> show create table people2;
  ±--------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table |
  ±--------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | people2 | CREATE TABLE people2 (
  last_name varchar(255) NOT NULL,
  first_name varchar(255) NOT NULL,
  bob date NOT NULL,
  KEY index_union (last_name,bob)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  ±--------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

people和people2的区别在于联合索引字段的不同

• mysql> explain select * from people where bob = ‘2020-01-01’;
  ±—±------------±-------±-----------±------±--------------±------------±--------±-----±-----±---------±-------------------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±-------±-----------±------±--------------±------------±--------±-----±-----±---------±-------------------------+
  | 1 | SIMPLE | people | NULL | index | NULL | index_union | 517 | NULL | 1 | 100.00 | Using where; Using index |
  ±—±------------±-------±-----------±------±--------------±------------±--------±-----±-----±---------±-------------------------+
  1 row in set, 1 warning (0.01 sec)

• mysql> explain select * from people2 where bob = ‘2020-01-01’;
  ±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
  | 1 | SIMPLE | people2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
  ±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
  1 row in set, 1 warning (0.00 sec)

• people表不符合最左前缀，但是走了索引，type类型是index，extra类型是using index；people2表没有走索引，原因是什么呢？
• mysql查询优化器计算cost成本后，发现全表扫描不如扫描索引树成本小，所以people表直接扫描了索引树的叶子节点，type类型为index。people2，因为不是覆盖索引，无法通过扫描索引树完成查询，索引只能扫描全表，typel类型All

• all

• 完全没有用到索引，全表扫描（扫描主键索引的所有叶子节点），一般需要增加索引来优化
• mysql> explain select * from actor;
  ±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  ±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
  | 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
  ±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
  1 row in set, 1 warning (0.00 sec)