# MySQL索引和执行计划
# 一、索引入门
# 1、索引是什么
# (1) 生活中的索引
MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
上面的理解比较抽象，举一个例子，平时看任何一本书，首先看到的都是目录，通过目录去查询书籍里面的内容会非常的迅速。
另外通过目录（索引），可以快速查询到目录里面的内容，它能高效获取数据，通过这个简单的案例可以理解索引就是高效获取数据的数据结构。
再来看一个复杂的情况：
我们要去图书馆找一本书，这图书馆的书肯定不是线性存放的，它对不同的书籍内容进行了分类存放，整索引由于一个个节点组成，根节点有中间节点，中间节点下面又由子节点，最 后一层是叶子节点，可见，整个索引结构是一棵倒挂着的树，其实它就是一种数据结构，这种数据结构比前面讲到的线性目录更好的增加了查询的速度。
# (2) MySql 中的索引
MySql 中的索引其实也是这么一回事，我们可以在数据库中建立一系列的索引，比如创建主键的时候默认会创建主键索引。
上图是一种 BTREE 的索引，左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。每一个节点都是主键的 ID，当我们通过 ID 来查询内容的时候，首先去查索引库，在到索引库后能快速的定位索引的具体位置。
mysql 默认存储引擎 innodb 只显式支持 B-Tree(从技术上来说是 B+Tree)索引。
# 2、索引的分类
# (1) 普通索引
即一个索引只包含单个列，一个表可以有多个单列索引
# (2) 唯一索引
索引列的值必须唯一，但允许有空值
# (3) 复合索引
即一个索引包含多个列
# (4) 聚簇索引(聚集索引)
并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB 的聚簇索引其实就是在同一个结构中保存了 B-Tree 索引(技术上来说是 B+Tree)和数据行。
# (5) 非聚簇索引
不是聚簇索引，就是非聚簇索引
# 3、基础语法
- 查看索引
SHOW INDEX FROM table_name
- 创建索引
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
- 删除索引
DROP INDEX [indexName] ON mytable;
# 二、执行计划
# 1、什么是执行计划
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
# 2、执行计划的作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
# 3、执行计划的语法
执行计划的语法其实非常简单： 在 SQL 查询的前面加上 EXPLAIN 关键字就行。
比如：EXPLAIN select * from table1
** 重点的就是 EXPLAIN 后面你要分析的 SQL 语句 **
# 4、执行计划详解
通过 EXPLAIN 关键分析的结果由以下列组成，接下来挨个分析每一个列
| id | select_type | table | positions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
# (1) id
描述 select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序，根据 id 的数值结果可以分成一下三种情况：
- id 相同
执行顺序由上至下
- id 不同
如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
- id 相同不同
先按照 id 不同的由大到小执行，id 相同的由上至下
# (2) select_type
查询的类型，用于区别：普通查询、联合查询、子查询等的复杂查询，类型如下：
| 类型 | 描述 |
|---|---|
| SIMPLE | 简单的 select 查询,查询中不包含子查询或者 UNION |
| PRIMARY | 查询中若包含任何复杂的子部分，最外层查询则被标记为 |
| SUBQUERY | 在 SELECT 或 WHERE 列表中包含了子查询 |
| DERIVED | 在 FROM 列表中包含的子查询被标记为 DERIVED (衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。 |
| UNION | 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION； 若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为：DERIVED |
| UNION RESULT | 从 UNION 表获取结果的 SELECT |
# (3) table
显示这一行的数据是关于哪张表的
# (4) 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。
# system 与 const
- system
表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计
- const
表示通过索引一次就找到了
const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
# eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
# ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
# range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。
一般就是在你的 where 语句中出现了 between、<、>、in 等的查询。
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
# index
当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。
# all
Full Table Scan，将遍历全表以找到匹配的行
# (5) possible_keys 与 Key
- possible_keys
可能使用的索引
- key
实际使用的索引。如果为 NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠
# (6) key_len
key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的
充分用了索引的 key_len 比没有充分用到索引的 key_len 要大。
key_len表示索引使用的字节数，- 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
char和varchar跟字符编码也有密切的联系,latin1占用 1 个字节，gbk占用 2 个字节，utf8占用 3 个字节。（不同字符编码占用的存储空间不同）
对于 char 和 varchar 类型的计算：
- 允许为
null会使key_len的计算加1 varchar会使key_len的计算加2
对于数值类型的计算：
按照类型本身所占有的字节数来计算，如果允许 null 再加1
# (7) ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
# (8) rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
# (9) extra
包含不适合在其他列中显示但十分重要的额外信息。
| 值 | 描述 |
|---|---|
| using filesort | 说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序” |
| using temporary | 使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 |
| using index | 是否用了覆盖索引 |
| using where | 使用了 where 过滤 |
| using join buffer | 使用了连接缓存 |
| impossible where | where 字句的值总是 false，不能用来获取任何元祖 |
← MySQL慢查询 MySQL的SQL优化 →