# mysql索引
**Repository Path**: lin-sauce/mysql-index
## Basic Information
- **Project Name**: mysql索引
- **Description**: 索引优化
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-04-30
- **Last Updated**: 2024-04-30
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
# **1.什么是Mysql**
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。
特点：
- Mysql 是开源的，可以定制的，采用了 GPL 协议，可以修改源码来开发自己的 Mysql 系统。
- MySQL 使用标准的 SQL 数据语言形式。
- Mysql 可以允许于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、PHP、
Ruby 和 Tcl 等。
- MySQL 支持大型数据库，可以在不断增长的数据上实现高性能，并且可以处理非常大的数据。
- MySQL支持多用户，它可以同时处理多用户的请求。
# 2.MySql架构图
## 2.1连接器：
负责与客户端建立连接、获取权限、维持和管理连接。
从服务端jdbc到底层mysql，mysql连接器（比如Navicat dbeaver这样图像化界面就可以连接上mysql，cmd命令也可以连接上mysql）
## 2.2服务层：
主要包括查询缓存、分析器、优化器、执行器等，可以说此层涵盖了MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
1.查询缓存（Cache 和 Buffer）。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等。MySQL 8.0版本后移除了查询缓存。
2.解析器（Parser）。 SQL 命令传递到解析器的时候会被解析器验证和解析。
3.查询优化器（Optimizer ）。 （索引优化+多表关联查询优化）
SQL 语句在查询之前会使用查询优化器对查询进行优化。
4.执行器。
先判断一下客户端对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误；如果有权限，就打开表继续执行。
调用存储引擎的接口拿到数据（存储引擎再到文件管理系统(内存和磁盘)里面拿到数据），将数据放一份到query cache里面，然后返回给客户端。
## 2.3存储引擎层：
存储引擎层主要是负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。目前最常用的存储引擎是InnoDB，也是MySQL（5.5.5版本及以后版本）默认存储引擎。也就是说我们在创建表时如果不指定表的存储引擎类型，则默认设置为InnoDB。
**InnoDB与MyISAM存储引擎的选择**
对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整个表， 行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
不适合高并发的操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，内存大小对性能有 决定性影响
业务 适合读频繁的业务 并发写，事务（崩溃安全恢复）
# 3.索引概念
## 3.1索引定义
MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。
通俗来说：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
准备工作：
```sql
CREATE TABLE student (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
age tinyint(4) DEFAULT NULL,
id_card varchar(20) DEFAULT NULL,
sex tinyint(1) DEFAULT '0',
address varchar(100) DEFAULT NULL,
phone varchar(20) DEFAULT NULL,
create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
remark varchar(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE ordinary_student (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
age tinyint(4) DEFAULT NULL,
id_card varchar(20) DEFAULT NULL,
sex tinyint(1) DEFAULT '0',
address varchar(100) DEFAULT NULL,
phone varchar(20) DEFAULT NULL,
create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
remark varchar(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE PROCEDURE insert_student_data()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE continue HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
WHILE i < 1000000 DO
INSERT INTO student(name,age,id_card,sex,address,phone,remark)
VALUES(CONCAT('姓名_',i), FLOOR(RAND()*100),
FLOOR(RAND()*10000000000),FLOOR(RAND()*2),
CONCAT('地址_',i), CONCAT('12937742',i),
CONCAT('备注_',i));
SET i = i + 1;
IF MOD(i,10000) = 0 THEN
COMMIT;
START TRANSACTION;
END IF;
END WHILE;
COMMIT;
END
CALL insert_student_data();
CREATE PROCEDURE insert_ordinary_student_data()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE continue HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
WHILE i < 1000000 DO
INSERT INTO ordinary_student(name,age,id_card,sex,address,phone,remark)
VALUES(CONCAT('姓名_',i), FLOOR(RAND()*100),
FLOOR(RAND()*10000000000),FLOOR(RAND()*2),
CONCAT('地址_',i), CONCAT('12937742',i),
CONCAT('备注_',i));
SET i = i + 1;
IF MOD(i,10000) = 0 THEN
COMMIT;
START TRANSACTION;
END IF;
END WHILE;
COMMIT;
END
CALL insert_ordinary_student_data();
CREATE INDEX idx_name_age_card ON student(`name`,`age`,`id_card`); -- 普通索引
SELECT count(*) from student
SELECT count(*) from ordinary_student
```
### 索引优缺点
**优势**：
1.提高数据检索的效率，降低数据库的IO成本。
2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
ordinary_student表
student表
**有索引：**
```sql
SELECT * from student where name = '姓名_0'
```
**没索引：**
```sql
SELECT * from ordinary_student where name = '姓名_0'
```
**劣势**：
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
## 3.2索引语法
1. 创建索引：
```sql
CREATE [UNIQUE] INDEX indexName ON table_name(columnname);
ALTER mytable ADD [UNIQUE] INDEX [indexName] (columnname); -- 添加普通索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) ;-- 该语句添加一个主键，这意味着索引值必须是唯一
的，且不能为 NULL。
```
2. 查看索引
```sql
SHOW INDEX FROM table_name
```
3. 删除索引
```sql
DROP INDEX [indexName] ON table_name;
```
## 3.3建索引场景
> 1.哪些情况下适合建立索引
1. 主键自动建立唯一索引
2. 频繁作为查询的条件的字段应该创建索引
3. 频繁更新的字段不适合创建索引
4. Where 条件里用不到的字段不创建索引
5. 单值/组合索引的选择问题（在高并发下倾向创建组合索引）
6. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
7. 查询中为经常需要排序、分组和联合操作的字段建立索引
> 2.哪些情况不要创建索引
1. 表记录太少
2. 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
一万数据：
有索引（new_small_student表）
无索引（new_small_ordinary_student表）
五万数据
有索引（new_student表）
无索引（new_ordinary_student表）
## 3.4mysql索引分类
### 3.4.1根据索引的数据结构分类：
- Hash索引（Memory存储引擎、Innodb引擎自适应哈希索引）
1、Hash是k,v形式，通过一个散列函数，能够根据key快速找到value
2、哈希索引就是采用一定的hash算法，把键值换成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需要一次hash算法即可立即定位到相应的位置，速度非常快。
**缺点**: 因为底层数据结构是散列的，无法进行比较大小，不能进行范围查找
- B-Tree索引（常用于存储引擎MyISAM和InnoDB）
- Full-text索引（用于全文搜索）
```sql
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); -- 该语句指定了索引为 FULLTEXT ，用于全文索引。
```
**B-Tree索引与哈希索引区别：**
1、如果是等值查询，那么hash索引有明显的优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个键值是唯一的，如果不唯一，则需要先找到下标位置再链式查找。
2、hash索引无法支持**范围查询**，因为原先是有序的键值，但是经过hash算法后，有可能变成不连续的，就没有办法利用索引完成范围查询检索数据。
3、同样，hash索引也没办法利用索引完成**排序**，以及like `xxx%`这样的**模糊查询**（范围查询）。
4、hash索引也不支持多列联合索引的**最左前缀匹配规则**。
5、在有大量重复键的情况下，hash索引的效率也是极低的，因为存在**hash碰撞**问题。
### 3.4.2根据索引的字段数分类：
- 单列索引（单个字段上的索引：即一个索引只包含单个列，一个表可以有多个单列索引）
```sql
create index idx_name on student(name)
```
- 组合索引（多个字段的索引：即一个索引包含多个列）
```sql
CREATE INDEX idx_name_age_card ON student(`name`,`age`,`id_card`); -- 普通索引
```
**为什么使用组合索引**：
- 减少开销。
建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
- 效率高。
索引列越多，通过索引筛选出的数据越少（执行查询时检查的行数越少）。
- 覆盖索引。
对联合索引(col1,col2,col3)，如果有如下的sql: `select col1,col2,col3 from test where col1=1 and col2=2`。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
> 简单来说，覆盖就是select后面的列都在索引中，这样就不需要继续扫描数据行了
**如何创建组合索引**：
1.顺序
要注意符合最左匹配原则。
最左匹配原则：在通过组合索引检索数据时，从左向右依次使用生效，如果中间某个索引没有使用（如果遇到范围查询(>、<、between、like 等)），那么断点前面的索引部分起作用，断点后面的索引没有起作用；创建索引时要从索引的最左前列开始并且不跳过索引中的列
```sql
EXPLAIN SELECT * from student where name = '姓名_0'
```
```sql
EXPLAIN SELECT * from student where name = '姓名_0' and age = 40
```
```sql
EXPLAIN SELECT * from student where name = '姓名_0' and age = 40 and id_card = '7138207570' -- 全值匹配
```
```sql
EXPLAIN SELECT * from student where name = '姓名_0' and id_card = '7138207570';-- 只用到name
```
```sql
EXPLAIN SELECT * from student where age = 40; -- 去首
```
```sql
EXPLAIN SELECT * from student where name = '姓名_0' and id_card = '7138207570' and age = 40;-- 全值匹配，优化器会在不影响 SQL 执行结果的前提下，自动优化。
```
![]https://gitee.com/lin-sauce/mysql-index/raw/master/screenshots/2.jpg)
注意事项：
- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效
```sql
EXPLAIN SELECT * FROM student WHERE LEFT(name,3) = 'abc'; -- 索引失效
```
- 存储引擎不能使用索引中范围条件右边的列,索引列不要有范围查询
```sql
EXPLAIN SELECT * from student where name = '姓名_0' and age < 40 and id_card = '7138207570'; -- 索引失效
```
- mysql在where后的条件表达式中使用不等于（!=或者<>）、in、or的时候无法使用索引会导致全表扫描
```sql
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name <> 'abc' ; -- 索引失效
```
- is not null 也无法使用索引
```sql
EXPLAIN SELECT * FROM student WHERE name IS NULL;
EXPLAIN SELECT * FROM student WHERE name IS NOT NULL; -- 索引失效
```
- like以通配符开头，mysql索引失效会变成全表扫描操作
```sql
EXPLAIN SELECT * FROM student WHERE name LIKE 'abc%';
EXPLAIN SELECT * FROM student WHERE name LIKE '%ab%'; -- 索引失效
```
- 类型转换导致索引失效
```sql
EXPLAIN SELECT * FROM student WHERE name='123';
EXPLAIN SELECT * FROM student WHERE name= 123; -- 索引失效
```
2.使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少使用select*
优点：
- 不包含行数据的所有信息，故其大小远小于聚簇索引，因此可以减少大量的IO操作。
- 索引覆盖只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询要高。
- 索引中列值是按顺序存储的，对磁盘的访问是顺序IO的，索引覆盖能避免范围查询回表带来的大量随机IO操作。
```sql
EXPLAIN SELECT name,age,id_card from student where name = '姓名_0' and age = 40 and id_card = '7138207570'
```
3.排序分组优化（group by 和 order by）
order by :
有 where 作为过滤条件，且在过滤条件中用到索引，多个排序字段方向一致（desc、asc）order by可以使用索引
```sql
explain select * from student where name='45' order by age,id_card;
```
group by:
group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 groupby 即使没有过滤条件用到索引，也可以直
接使用索引。
```sql
explain select * from student order by name;
```
```sql
explain select * from student group by name;
```
### 3.4.3按索引是否包含记录数据分类：
- 聚簇索引：
将数据存储与索引放到了一块，找到索引也就找到了数据，不需要根据主键或行号去进行回表查询。
- 非聚簇索引：
非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中。
**InnoDB与MyISAM存储引擎的区别**
`MyISAM的索引方式都是非聚簇的，与InnoDB包含1个聚簇索引是不同的。`
<主键索引>在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录 ，而 MyISAM 主键索引的叶子节点只是存储了数据文件的地址，它在执行查询操作时，需要进行一次回表操作，需要先查询出数据文件的地址，然后再根据地址拿到数据记录，所以 MyISAM 需要查询两次，意味着MyISAM中建立的索引相当于全部都是二级索引。
<辅助索引>MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB叶子节点存储的是主键聚簇索引，因此它需要先查询到聚簇索引，然后再进行回表查询，去聚簇索引中拿到数据记录，比MyISAM慢。
InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段（rowid）作为主键，这个字段长度为6个字节，类型为长整型。
MyISAM 中的索引存储结构图：
InnoDB 中的索引存储结构图：
因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以如果主键定义的比较大，其他索引也将很大。InnoDB 不会压缩索引。
**聚簇索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。**
### 3.4.4根据索引的功能分类：
主键索引（唯一标识表中的每行数据，一个表只能有一个主键）< InnoDB引擎支持聚簇索引，MyISAM引擎不支持聚簇索引。 所以主键索引不一定是聚簇索引。>
外键索引（用于保证数据的完整性和一致性，一个表中的字段是另一个表的主键）
普通索引（用于加速查询）
唯一索引（确保索引列的值是唯一的，可以包含NULL值）
全文索引（用于全文搜索，MyISAM存储引擎支持）
# 4.索引优化分析
## 4.1sql性能问题原因分析
- 查询语句问题
- 索引失效
- 服务器调优及各个参数设置(缓冲、线程数等)
- 数据量太大（分区分表）
## 4.2sql执行顺序
手写顺序：
执行顺序：
查询优化策略：
1. 选择正确的索引。
2. 避免SELECT *，只选取需要的列。
4. 避免使用子查询(增加查询复杂度和开销，导致性能下降)，尽量用JOIN代替。
5. 使用WHERE代替HAVING。
6. 使用EXPLAIN来查看查询执行计划
## 4.3EXPLAIN
### EXPLAIN是什么
使用EXPLAIN关键字可以`模拟优化器执行SQL查询语句`，从而知道MySQL是如何处理你的SQL语句的。`分析查询语句或是表结构的性能瓶颈`。
### EXPLAIN的用法
**用法：**
```sql
EXPLAIN + SQL语句
```
**数据准备：**
```sql
CREATE TABLE t1(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT, content1 VARCHAR(100) NULL, content2 VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t_emp(id INT(10) AUTO_INCREMENT, name VARCHAR(27) NOT NULL,age int(4) null,deptId int(4) null, empno VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE INDEX idx_content1 ON t4(content1); -- 普通索引
CREATE INDEX idx_deptid ON t_emp(content1);
# 以下新增sql多执行几次，以便演示
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content1, content2) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)), CONCAT('t4_',FLOOR(1+RAND()*1000)));
```
### 各字段解释
#### table
- **单表：**显示这一行的数据是关于哪张表的
```sql
EXPLAIN SELECT * FROM t1;
```
- **多表关联：**t1为驱动表，t2为被驱动表。
`注意：`内连接时，MySQL性能优化器会自动判断哪个表是驱动表，哪个表示被驱动表，和书写的顺序无关
```sql
EXPLAIN SELECT * FROM t1 INNER JOIN t2;
```
#### id
表示查询中执行select子句或操作表的顺序
- **id相同：**执行顺序由上至下
```sql
EXPLAIN SELECT * FROM t1, t2, t3;
```
- **id不同：**如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
```sql
EXPLAIN SELECT t1.id FROM t1 WHERE t1.id =(
SELECT t2.id FROM t2 WHERE t2.id =(
SELECT t3.id FROM t3 WHERE t3.content = 't3_434'
)
);
```
- **id为NULL：**最后执行
```sql
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
```
**小结：**
- id如果相同，从上往下顺序执行
- id值越大，优先级越高，越先执行
- 关注点：每个id号，表示一趟独立的查询, 一个sql的查询趟数越少越好
#### select_type
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
- **SIMPLE：**简单查询。查询中不包含子查询或者UNION。
```sql
EXPLAIN SELECT * FROM t1;
```
- **PRIMARY：**主查询。查询中若包含子查询，则最外层查询被标记为PRIMARY。
- **SUBQUERY：**子查询。在SELECT或WHERE列表中包含了子查询。
```sql
EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content= 'a');
```
- **DEPENDENT SUBQUREY：**如果包含了子查询，并且查询语句不能被优化器转换为连接查询，并且子查询是`相关子查询（子查询基于外部数据列）`，则子查询就是DEPENDENT SUBQUREY。
```sql
EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);
```
- **UNCACHEABLE SUBQUREY：**表示这个subquery的查询要受到外部系统变量的影响
```sql
EXPLAIN SELECT * FROM t3
WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);
```
- **UNION：**对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查询都是UNION。
- **UNION RESULT：**UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询的去重工作，针对这个临时表的查询就是"UNION RESULT"。
```sql
EXPLAIN
SELECT * FROM t3 WHERE id = 1
UNION
SELECT * FROM t2 WHERE id = 1;
```
- **DEPENDENT UNION：**子查询（where 后）中的UNION或者UNION ALL，除了最左边的查询是DEPENDENT SUBQUREY，其余的查询都是DEPENDENT UNION。
```sql
EXPLAIN SELECT * FROM t1 WHERE content IN
(
SELECT content FROM t2
UNION
SELECT content FROM t3
);
```
- **DERIVED：**在包含`派生表（子查询在from子句中）`的查询中，MySQL会递归执行这些子查询，把结果放在临时表里。
```sql
EXPLAIN SELECT * FROM (
SELECT content, COUNT(*) AS c FROM t1 GROUP BY content
) AS derived_t1 WHERE c > 1;
```
这里的``就是在id为2的查询中产生的派生表。
#### partitions
代表分区表中的命中情况，非分区表，该项为NULL
#### 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`
>
> SQL 性能优化的目标：至少要达到 `range` 级别，较好是 `ref` 级别，最好是 `consts`级别。
- **ALL：**全表扫描。Full Table Scan，将遍历全表以找到匹配的行
```sql
EXPLAIN SELECT * FROM t1;
```
- **index：**sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
`覆盖索引：`如果能通过读取索引就可以得到想要的数据，那就不需要读取用户记录，或者不用再做回表操作了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
```sql
-- 只需要读取聚簇索引部分的非叶子节点，就可以得到id的值，不需要查询叶子节点（主建索引）
EXPLAIN SELECT id FROM t1;
```
```sql
-- 只需要读取二级索引，就可以在二级索引中获取到想要的数据，不需要再根据叶子节点中的id做回表操作（辅助索引）
EXPLAIN SELECT id, deptId FROM t_emp;
```
- **range：**只检索给定范围的行，使用一个索引来选择行。一般就是在where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
```sql
EXPLAIN SELECT * FROM t1 WHERE id IN (1, 2, 3);
```
- **ref：**通过普通二级索引列与常量进行等值匹配时
```sql
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;
```
- **eq_ref：**唯一性索引扫描，连接查询时通过主键或不允许NULL值的唯一二级索引列进行等值匹配时
```sql
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
```
- **const：**根据`主键`或者`唯一二级索引`列与`常数`进行匹配时
```sql
EXPLAIN SELECT * FROM t1 WHERE id = 1;
```
- **system：**MyISAM引擎中，当表中只有一条记录时。`（这是所有type的值中性能最高的场景）`
```sql
CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;
```
#### possible_keys 和 keys **☆**
- `possible_keys`表示执行查询时可能用到的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，**但不一定被查询实际使用**。
- `keys`表示实际使用的索引。如果为NULL，则没有使用索引。
```sql
EXPLAIN SELECT id FROM t1 WHERE id = 1;
```
#### key_len **☆**
表示索引使用的字节数，根据这个值可以判断索引的使用情况，`检查是否充分利用了索引，针对联合索引值越大越好。`
**如何计算：**
1. 先看索引上字段的类型+长度。比如：int=4 ; varchar(20) =20 ; char(20) =20
2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf8要乘 3，如果是utf8mb4要乘4，GBK要乘2
3. varchar这种动态字符串要加2个字节
4. 允许为空的字段要加1个字节
```sql
-- 创建索引
CREATE INDEX idx_age_name ON t_emp(age, `name`);
-- 测试1
EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND `name` = 'ab%';
-- 测试2
EXPLAIN SELECT * FROM t_emp WHERE age = 30;
```
第一组：key_len=age 的字节长度+name 的字节长度=4+1 + ( 27*3+2)=5+83=88
第二组：key_len=age 的字节长度=4+1=5
#### ref
显示与key中的索引进行比较的列或常量。
```sql
-- ref=book.t1.id 关联查询时出现，t2表和t1表的哪一列进行关联
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
-- ref=const 与索引列进行等值比较的东西，const表示一个常数
EXPLAIN SELECT * FROM t_emp WHERE age = 30;
```
#### rows **☆**
MySQL认为它执行查询时必须检查的行数。值越小越好。
```sql
-- 如果是全表扫描，rows的值就是表中数据的行数
EXPLAIN SELECT * FROM t_emp WHERE empno = '10001';
-- 如果是使用索引查询，rows的值就是预计扫描索引记录行数
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;
```
#### filtered
最后查询出来的数据占所有服务器端检查行数（rows）的`百分比`。值越大越好。
#### Extra **☆**
包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来`理解MySQL到底将如何执行当前的查询语句`。MySQL提供的额外信息有好几十个，这里只挑介绍比较重要的介绍。
- **Impossible WHERE**：where子句的值总是false
```sql
EXPLAIN SELECT * FROM t_emp WHERE 1 != 1;
```
- **Using where：**使用了where，但在where上有字段没有创建索引
```sql
EXPLAIN SELECT * FROM t_emp WHERE `name` = '风清扬';
```
- **Using temporary：**使了用临时表保存中间结果
```sql
EXPLAIN SELECT DISTINCT content FROM t1;
```
- **Using filesort：**
在对查询结果中的记录进行排序时，是可以使用索引的，如下所示：
```sql
EXPLAIN SELECT * FROM t1 ORDER BY id;
```
如果排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多时）进行排序（filesort），如下所示：
```sql
EXPLAIN SELECT * FROM t1 ORDER BY content;
```
- **Using index：**使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表
```sql
EXPLAIN SELECT id, content1 FROM t4;
```
```sql
EXPLAIN SELECT id FROM t1;
```
- **Using join buffer：**在连接查询时，当被驱动表不能有效的利用索引时，MySQL会为其分配一块名为连接缓冲区（join buffer）的内存来加快查询速度
```sql
EXPLAIN SELECT * FROM t1, t2 WHERE t1.content = t2.content;
```
## 4.4索引失效
索引失效准则
1. 最左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
2. 不在索引列上做任何操作（计算、函数），会导致索引失效
3. 存储引擎不能使用索引中范围条件右边的列
4. mysql在where后的条件表达式中使用不等于（!=或者<>）、in、or的时候无法使用索引会导致全表扫描
5. is not null 也无法使用索引
6. like以通配符开头，mysql索引失效会变成全表扫描操作
7. 类型转换导致索引失效
8. join操作中，on关键词后面字段类型要保持一致，否则索引失效
### 结论：
- 对于单值索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
- 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的