Mysql官方对索引的定义为：索引（Index）是帮助Mysql高效获取数据的数据结构。本质：索引就是数据结构索引是一种数据结构，用于提高查找效率，类比字典。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据之上实现高级查找算法，这种数据结构就是索引。

一、索引分类

主键索引
- 主键
唯一索引
- 必须唯一，不能为null
- alter table test_index add unique index_c1234(c1,c2,c3,c4);
普通索引
- 值可以为空
- alter table test_index add index index_c1234(c1);
- create index xxx on table xxxx
组合索引
- 多列值组成一个索引，专门用于组合搜索
- alter table test_index add index index_c1234(c1,c2,c3,c4);
全文索引
- 索引类型为FullTEXT，可以在varchar、char、text列上创建
```
alter table test_index add index index_c1234(c1,c2,c3,c4);
alter table test_index add unique index_c1234(c1,c2,c3,c4); （不能是null）
alter table test_index add primary key(c1,c2,c3,c4); （不能是null）
create index xxx on table xxxx
Drop index xxxx on table..
show index from tablename
```
  二、聚集索引和非聚集索引
  1、聚集索引
  1.1 解释
  
  聚集（clustered）索引，也叫聚簇索引。会建立一棵B+树，叶子存储行记录。定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

为什么只有一个聚集索引，因为物理排列方式与聚集索引的顺序相同。聚集索引是索引和数据的集合体，不但决定了数据的顺序，在叶节点还存储数据行。如果有两个或者多个，数据行会重复存储，浪费磁盘空间，也没那个必要。
聚集索引通常是表的主键，若无主键则为表中第一个非空的唯一索引，还是没有就采用innodb存储引擎为每行数据内置的ROWID作为聚集索引。

注意：聚集索引最好是在建表的时候就指定，由于聚集索引的物理顺序的特殊性（物理排列方式与聚集索引的顺序相同），因此等有数据的时候再添加聚集索引后，会对表中的数据进行排序，非常的耗时和耗性能。

2、非聚集索引

会建立一棵B+树，叶子节点存储pk值定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

2.1 解释

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

非聚集索引的叶子节点仍然是索引节点，只是有一个指针指向了对应的数据块，次如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列（没有使用到覆盖索引），那么它还要进行第二次的查询，查询节点上对应的数据行的数据。
所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！

2.2 回表（二次查询）

如下表：
以及聚集索引clustered index(id), 非聚集索引index(username)。

id	username	score
1	小明	90
2	小红	80
3	小华	92
..	..	..
256	小英	70

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

2.3 如何解决非聚集索引的二次查询问题

建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句。

select col1, col2 from t1 where col1 = '213';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

3、总结

create table people (
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(20),
PRIMARY KEY(`id`),
KEY(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、该表主键id就是该表的聚集索引，name就是非聚集索引；表中每行数据都是按id排序存储的；
2、比如要查找名字是’Aa’和’Ab’这两个人，他们在name索引表中的位置可能是相邻的，但实际的存储位置则不然。
3、select * from people where name = “xxx” 通过name索引最终只能查出数据的主键，然后再按主键捞出来。
4、使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
5、非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
6、不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

三、覆盖索引

什么是覆盖索引？参考下面的图片，possible_keys是null，表示用不到索引，但是key确实有值，表示实际用了索引，为什么？因为我们的索引是 (col1,col2)，而我们的查询语句是select col1,col2 from t1，查询顺序和索引顺序一致，那结果就会从索引中取，避免了全表扫描，而是全index扫描当select * 的时候就是全表扫描。

四、Explain 执行计划

explain 由于查看sql的执行计划

表的读取顺序数据读取的操作类型哪些索引被使用，哪些被实际使用表之间的引用每张表有多少行被优化器查询主要字段：id、type、key、rows、extra

1、字段 id

SELECT识别符。这是SELECT的查询序列号，当好多个表在一起的时候，显示表的读取顺序。

id相同时，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
2、字段 select_type
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
3、字段 type

mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref,const,system。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右

system：表中只有一行记录，平时不会出现
const：索引一次就找到了，只需要匹配一行数据，所以很快，如根据主键查询，mysql就能将该查询转化为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
ref：非唯一性索引扫描，返回某个匹配值的所有行，例如：索引字段name，where name = king，返回三个叫King的字段。
range：只检索给定范围的行，常出现与between，<，>，in等查询，比全表扫描强
index：Full index scan，index与All的区别就是遍历索引树，通常比All快，因为索引文件通常比数据文件小（也就是说index和all都是全表扫描，但是index是从索引中读取的，而all是从硬盘中读取的。
all：全表扫描，最差。
4、字段 possible_keys
表示查询时，可能使用的索引，但不一定被实际使用到
5、字段 key
实际使用到的索引，如果为null，则没有使用索引。
查询中若使用了覆盖索引，那该索引仅出现在key列表中。
6、字段 key_len
表示索引中使用的字节数，即使用的索引长度，理论上是越短越好的。
key_len的值是可能长度，并不是实际使用的精确长度，即key_len是根据表定义计算得出，而不是表内检索得出。
如下，定义char（4），一个条件时，是13，两个条件时是26。

7、字段 ref
显示索引的哪一列被使用了，如何可能的话，是一个常数
如下：在查询语句用t1表用到了t2.col1，和’ac’即常量 const。

8、字段 rows
估算找到所需记录要读取得行数，越小越好。
9、字段 Extra

包含不适合在其他列中显示，但也是很重要的信息。

9.1 Using filesort

这种情况很差
说明mysql会使用一个外部索引排序，而不是按照表内索引顺序进行读取。MySql中无法利用索引完成的排序操作称之为”文件排序“，这个需要另外排序，比较麻烦和啃爹。如下图片，查询使用到了index，但是排序是用的filesort

观察下面这个图片，这次没有using filesort，为什么？因为 where col1=’’ order by col2,col3，完整的用到了组合索引。

9.2 Using temporary

这种情况最差
使用了临时表保存中间结果，mysql在查询结果排序的时候使用了临时表。常见于order by，group by
如下，group by col2没走索引。于是 using temporary

当换种写法之后，group by col1,col2，就完美了。

9.3 using index

这种情况，最好
表示select操作使用了覆盖索引（covering index），避免了访问表的数据行，效率不错。
如果同时出现了using where，表示索引被用来执行索引键值查找。
如果没有同时出现using where，表明索引别用来读取数据而非执行查找动作。

什么是覆盖索引？上面有讲过，这里再强调一下 select 后面的字段刚好是索引的那几个字段，则会使用覆盖索引，查询的结果直接从索引中获得，不需要查询数据化。 mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖

9.4 using where

使用了where过滤

9.5 using join buffer

使用了连接缓存

9.6 impossible where

where子句总是false，不能用来获取任何元组，如下图片 where name=’aaa‘ and name=’bbb’，name不可能同时等于两个值。

9.7 select tables optimized away

在没有groupby字句的情况下，基于索引优化，对与myisam存储引擎，优化count（*）操作。

9.8 distinct

优化distinct操作，在找到第一个匹配的元组后立即停止查找同样的值。

10、复习

五、索引相关常识

1、优劣

优势
- 提高检索效率，降低数据库的IO成本。
- 通过索引对数据排序，降低数据排序成本，降低CPU的消耗。
劣势
- 虽然索引大大提高了查询速度，但同时会降低更新表的速度，比如对表进行insert、update、delete时，MySql不仅需要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
- 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表记录，所以索引列也要占用空间的。
  2、建索引的时机
主键自动创建索引
常作为查询条件的字段
与其他表关联的字段
单键\组合索引——>组合索引性价比高
排序或者分组的字段（分组更加消耗性能，因为分组已经做过一次排序了）

3、不适合建索引
经常增删改的字段（因为修改操作也要修改索引）
where条件里用不到的字段
无意义的字段，区分度不高的字段。
4、覆盖索引（索引覆盖）
https://www.yuque.com/wangchao-volk4/whmpo0/qldrh6#fuZlw
六、索引进阶理解
1、简单理解
下图是一种可能的索引方式实例：

为了加快Col2的查抄速度，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值（例如91）和一个指向对应数据记录物理地址的指针（例如91对应的0xF3），这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索到符合条件的记录。
2、BTree和B+ Tree
2.1 BTree

B树，也叫B- 树，大家都叫B树。

每个节点都存储数据，每个data的存储数量有限，如果数据多的话，整个树的的高度就会增加，意味着IO次数会增加。

2.2 B+ Tree

B+ Tree是在B树上的一种优化，如下： 1、B+树的每个节点可以包含更多的节点，这样做的原因有两个：一是为了降低树的高度，二是将数据范围变为多个区间，区间越多，检索速度越快。 2、非叶子节点存储key，叶子节点存储key和数据 3、叶子节点两两指针相互连接（符合磁盘的预读连续性），顺序查询性能更高。

假如要找10，找到磁盘1，10<28，p1指向磁盘2，10<=10，再指向磁盘四，结果找到。
真实情况，三层的B+树可以存储上百万的数据，上百万的数据只需要三次IO，性能将是巨大的提升，如果没有索引，每个数据量都会发生一次IO，那么总计百万次IO，线程成本很大。

3、MySql 常见的瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候。
服务器硬件性能。

4、常见的优化方式

4.1 单表

4.2 两表 join

当使用left join的时候，一定要给右边的条件使用索引，这是由左连接的特性决定的，left join 用于确定如何从右表开始搜索，因为左边一定有。
当然，right join 同理。
可以回忆下
Mysql入门

4.3 三表 join

4.5 小表驱动大表

优先拿小表的结果去匹配大表的结果。
下面这个图，之前有 https://www.yuque.com/wangchao-volk4/whmpo0/onuimk#fnnwb

4.6 order by，group by 关键字优化

尽量使用index方式排序，避免使用FileSort方式排序
下列方式同样适用group by

5、索引失效场景

联合索引，没有使用最左边的索引字段。即 index(name,age,address) ，而where后只使用了age或者address或（age和address）则会导致索引失效。
不在索引列上做任何操作（计算、函数、自动or手动的类型转化），会导致索引失效而转向全表扫描。例如：select * from tb_user where left(name,4) = ‘king’ 则会使用全表扫描。
存储引擎不能使用索引中范围条件右边的列。
1. 例如：索引：index（name,age,address）select * from tb_user where name = ‘king’ and age > 18 and address = ‘china’ 上述sql，从age开始索引失效，即返回条件之后，address索引失效。type是range，但也有机会变成ref，就是使用覆盖索引，select name,age,address from tb_user where name = ‘king’ and age > 18 and address = ‘china’;select name from tb_user where name = ‘king’ and age > 18 and address = ‘china’;都可以
尽量使用覆盖索引，减少select *原因如上介绍：select name,age,address from tb_user where name = ‘king’ and age > 18 and address = ‘china’;select name from tb_user where name = ‘king’ and age > 18 and address = ‘china’;
使用 !=、<>、is null、is not null 时，无法使用索引，导致全表扫描。
like以通配符开头（’%abc’）（’%abc%’）mysql索引会失效变成全表扫描的操作失效：select * from tb_user where name like ‘%aaa%’ 使用了覆盖索引：select name from tb_user where name like ‘%aaa%’
字符串不加单引号，导致索引失效。
少用or，它也会导致索引失效。

mysql索引入门-2

一、索引分类

二、聚集索引和非聚集索引

1、聚集索引

1.1 解释

2、非聚集索引

2.1 解释

2.2 回表（二次查询）

2.3 如何解决非聚集索引的二次查询问题

3、总结

三、覆盖索引

四、Explain 执行计划

1、字段 id

2、字段 select_type

3、字段 type

4、字段 possible_keys

5、字段 key

6、字段 key_len

7、字段 ref

8、字段 rows

9、字段 Extra