一. 索引简介
1. 什么是索引
索引是排序好的快速查找的数据结构，用来帮助MySQL高效获取需要的数据结果。索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！理解索引工作的方式最好的办法就是把索引比喻成书的目录，当需要查看特定的章节时通过查看目录的方式往往要比查看整个书的内容要有效很多。
补充：
(1). 除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
(2). 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
2. 索引的分类
我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。
A. 分类1
单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但是允许空值。
复合索引：一个索引包含多个字段。
B. 分类2
覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
C. 分类3
聚集索引(也叫主键索引)：
辅助索引(非聚集索引)：
3. 索引的优缺点
A. 优点：
查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。
(2) 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗。
(3) 帮助服务器避免排序和临时表。
(4) 可以将随机IO变成顺序IO。
B. 缺点：
(1) 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
(2) 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行 INSERT 、UPDATE 和 DELETE 。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
(3) 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。
4. 相关SQL语句
-----索引相关语句 --1. 查询索引 show index from TableName; --如：查看article表的索引 show index from article; --2. 删除索引 DROP INDEX [indexName] ON TableName; --如：删除acticle表上的 idx_article_cv 索引 drop index idx_article_cv on article; --3. 创建索引 CREATE [UNIQUE] INDEX indexName ON TableName(columnName(length)); --如：在article表上创建category_id的单个索引，名为：idx_article_c CREATE INDEX idx_article_c ON article(category_id); --如：在article表上创建category_id,views的联合索引，名为：idx_article_cv CREATE INDEX idx_article_cv ON article(category_id,views); ---ALTER相关用法 /* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */ ALTER TABLE TableName ADD PRIMARY KEY(column_list); /* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */ ALTER TABLE TableName ADD UNIQUE indexName(column_list); /* 3、该语句创建普通索引，索引值可以出现多次 */ ALTER TABLE TableName ADD INDEX indexName(column_list); --如：在article表中对views列添加索引，名为idx_article_v alter table article add index idx_article_v(views); /* 4、该语句指定了索引为FULLTEXT，用于全文检索 */ ALTER TABLE TableName ADD FULLTEXT indexName(column_list);
二. 执行计划
1. 什么是执行计划？
SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，Explain语句对select，delete，update，insert，replace语句有效。
2. 剖析执行计划
运行下面语句：
--userId为非聚集索引 EXPLAIN select * from T_UserAddress where userId='0002';
(1). id
分三种请情况：
id 相同，执行顺序由上至下。
id 不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id 相同和不相同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。
(2). select_type
数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。
SIMPLE ：简单的 SELECT 查询，查询中不包含子查询或者 UNION 。
PRIMARY ：查询中如果包含任何复杂的子部分，最外层查询则被标记为 PRIMARY 。
SUBQUERY ：在 SELECT 或者 WHERE 子句中包含了子查询。
DERIVED ：在 FROM 子句中包含的子查询被标记为 DERIVED(衍生) ，MySQL会递归执行这些子查询，把结果放在临时表中。
UNION ：如果第二个 SELECT 出现在 UNION 之后，则被标记为 UNION ；若 UNION 包含在 FROM子句的子查询中，外层 SELECT 将被标记为 DERIVED 。
UNION RESULT ：从 UNION 表获取结果的 SELECT 。
(3). type
访问类型排列。从最好到最差依次是： system > const > eq_ref > ref > range > index > ALL 。除了 ALL 没有用到索引，其他级别都用到索引了。
system ：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，这个也可以忽略不计。
const ：表示通过索引一次就找到了， const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL就能将该查询转化为一个常量。
eq_ref ：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除 了 system 和 const 类型之外, 这是最好的联接类型。
ref ：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。
range ：只检索给定范围的行，一般就是在 WHERE 语句中出现了 BETWEEN 、 < > 、 in 等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
index ： Full Index Scan ，全索引扫描， index 和 ALL 的区别为 index 类型只遍历索引树。也就是说虽然 ALL 和 index 都是读全表，但是 index 是从索引中读的， ALL 是从磁盘中读取的。
ALL ： Full Table Scan ，没有用到索引，全表扫描。
PS: 一般来说，得保证查询至少达到 range 级别，最好达到 ref 。
(4). possible key 和 key
possible_keys ：显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key ：实际使用的索引。如果为 NULL ，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅仅出现在 key 列表中。
(5). key_len （重点）
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。
计算规则可参考： https://blog.csdn.net/qq_34930488/article/details/102931490
影响因素有：
A. 列长度
B. 列是否为空：NULL(+1)，NOT NULL(+0)
C. 字符集：utf8mb4=4，utf8=3，gbk=2，latin1=1
D. 列类型为字符，varchar +2 ， char +0
char和varchar最终计算公式 key_len = (表字符集长度) * 列长度 +1 (null) + 2（varchar）
举例：
(6). ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
(7). rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。
(8). Extra
包含不适合在其他列中显示但十分重要的额外信息。
Using filesort ：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，对于这种MySQL中无法利用索引完成的排序操作成为"文件内排序"。
Using temporary ：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序 order by 和分组查询 group by 。临时表対系统性能损耗很大。
Using index ：表示相应的 SELECT 操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现 Using where ，表示索引被用来执行索引键值的查找；如果没有同时出现Using where ，表明索引用来读取数据而非执行查找动作。
Using where：表明使用了 WHERE 过滤。
PS:
using index ：使用覆盖索引的时候就会出现 （我们所喜欢的）
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据 (mysql5.6以后的新特性)
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 （我们所喜欢的）
Using join buffer ：使用了连接缓存。
impossible where ： WHERE 子句的值总是false，不能用来获取任何元组。
!
- 作 者 : Yaopengfei(姚鹏飞)
- 博客地址 : http://www.cnblogs.com/yaopengfei/
- 声 明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
- 声 明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。