一、了解索引
当我们使用汉语字典查找某个字时，我们会先通过拼音目录查到那个字所在的页码，然后直接翻到字典的那一页，找到我们要查的字，通过拼音目录查找比我们拿起字典从头一页一页翻找要快的多，数据库索引也一样，索引就像书的目录，通过索引能极大提高数据查询的效率。
索引的实现方式
在数据库中，常见的索引实现方式有哈希表、有序数组、搜索树
- 哈希表
哈希表是通过键值对（key-value）存储数据的索引实现方式，可以将哈希表想象成是一个数组，将索引通过哈希函数计算得到该行数据在数组中的位置，然后将数据存到数组中，容易发现一个问题，如果两个索引通过哈希函数计算后得到的数组位置相同要怎么办？在这里，数组的每个value都是一个链表，链表上的每个元素都是一个数据，新数据直接添加到链表尾部。
哈希表.png
- 所以数据库查询过程为：索引通过哈希函数计算数据所在位置--> 遍历指定位置的链表，找到满足条件的数据。
要注意的是，链表上的数据元素不是有序的，每次有新数据加入时，新数据时直接添加到链表尾部，这样做的好处是添加数据时很方便。
哈希表不擅长进行区间查询，一般都用于等值查询
1、两个相邻索引通过hash函数后计算得到的数组位置不一定还保持相邻
2、链表上的数据是无序的 - 有序数组
顾名思义，有序数组是按索引大小将数据保存在一个数组上，因为该数组是有序的，可以通过二分法很容易查到位置，找到第一个位置后，通过向左/向右遍历很容易得到所求区间的数据。因此，无论是等值查询还是区间查询，效率都极高。
但缺陷也是显而易见的，当向数组中间n位置插入一条数据时，需将n后面的数据全部往后移动，所以，这种索引一般用于静态存储引擎。 - 搜索树
二叉搜索树：一棵空树，或者是具有下列性质的二叉树： 若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值； 若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值； 二叉搜索树的左、右子树也分别为二叉搜索树。
平衡二叉树：平衡二叉树是在二叉搜索树的基础上引入的，指的是结点的左子树和右子树的深度差不超过1.
多叉树：每个结点可以有多个子结点，子节点的大小从左到右依次递增。
当使用平衡二叉实现索引时，结构如下图
图片来自课程文章
从图中可发现，每次查询最多需要访问4个节点必能得到所要数据。例如查询user2时，查询过程为：userA-->userC-->userF-->user2。
所以查询速度很高，同时，因为搜索树的特性（左子树小于右子树），区间查询也很方便。
如果搜索树存于内存中，与多叉树相比，二叉树的搜索速率是最高的，但实际上数据库使用的是n叉树而不是二叉树。
1、索引不仅存于内存，还是写到磁盘上
2、搜索树上的每个结点在磁盘上表现为一个数据块
3、多叉树每个结点下可以有多个子节点，所以存储相同数据量时多叉树的树高比二叉树小，查询一个数据需要访问的结点数更少，即查询过程访问更少的数据块。查询速度较高。
innodb的索引模型
innodb使用B+树作为索引结构。
在B+树中，我们将节点分为叶子结点和非叶子结点，非叶子结点上保存的是索引，而且一个节点可以保存多个索引；数据全部存于叶子结点上,根据叶子结点的内容不同，innodb索引分为主键索引和非主键索引。非主键索引也称为二级索引。
主键索引的叶子结点中保存的数据为整行数据，而非主键索引叶子节点保存的是主键的值。
主键索引图
非主键索引图
通过主键索引查询数据时，我们只需查找主键索引树便可以获取数据；通过非主键索引查询数据时，我们先通过非主键索引树查找到主键值，然后再在主键索引树搜索一次，这个过程称为回表，也就是说非主键索引查询会比主键查询多搜索一棵树。所以我们应尽可能使用主键查询。
索引维护
添加新行时，将会在索引表上添加一条记录，如果是索引递增插入时，数据都是追加在当前最大索引之后，不会对树中其他数据造成影响；如果新加入的数据的索引值位于节点的中间，需要挪动部分节点的位置，从而保持索引树的有序性。
而且，相邻多个节点是存储在同一个数据页上的，此时，如果是在已经存储满状态的数据页中插入节点，会申请新的数据页，将部分数据挪动到新的数据页，这个过程称为页分裂，页分裂除了会影响性能，还会降低磁盘空间利用率。不规则数据插入时,会造成频繁的页分裂。
当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并
所以，一般情况下会采用递增主键，使新数据递增插入。
使用业务逻辑字段做主键有什么优缺点？
1、业务逻辑字段不容易保证索引树结点有序插入，这样写入成本较高。
2、innodb默认使用整数类型作为主键，主键长度较小，二级索引的叶子结点中保存的是主键值，主键长度越小，二级索引的叶子结点占用空间也就越小。
3、当然，使用业务逻辑字段做主键也有好处，可以避免回表，每次只需扫描一次主键索引树即可
综上，从性能和存储空间方面考量，自增主键往往是更合理的选择,当业务场景有且只有一个索引，而且该索引为唯一索引时，此时更适合使用业务逻辑字段作为主键。
因为数据修改/删除、页分裂等原因，会导致数据页空间利用率降低，此时，可以考虑重建索引，将数据按顺序插入，提高磁盘空间利用率。但重建主键索引和普通索引会有不同影响，重建普通索引，可以达到提高空间利用率的目的，且不会对其他索引造成影响，但如果重建主键索引就不合理了，会影响所有普通索引，性能影响较大，而且无论是新建/删除主键，都会重建整张表。这时我们可以使用alter table T engine=InnoDB这个语句代替。
二、索引采用哪种数据结构？
Hash索引和B+ Tree索引，mysql默认的是InnoDB引擎，默认的是B+树。
优缺点：B+ Tree索引和Hash索引区别 哈希索引适合等值查询，但是不无法进行范围查询 哈希索引没办法利用索引完成排序 哈希索引不支持多列联合索引的最左匹配规则 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
三、索引类型
索引包括：
1.普通索引(NORMAL)
2.唯一索引(UNIQUE)
3.主键索引(PRIMARY)
4.组合索引(多个字段的普通索引)
5.组合唯一索引(多个字段的唯一索引)
5.全文索引(FULLTEXT)
1、聚簇索引(聚集索引 clustered index)、非聚簇索引(普通索引 secondary index)
InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值。索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引；而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。
区别：聚簇索引会更快，涉及到回表查询
InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
画外音：所以PK查询非常快，直接定位行记录。
InnoDB普通索引的叶子节点存储主键值。
画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。
举个栗子，不妨设有表：
t(id PK, name KEY, sex, flag);
画外音：id是聚集索引，name是普通索引。
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
image
两个B+树索引分别如上图：
（1）id为PK，聚集索引，叶子节点存储行记录；
（2）name为KEY，普通索引，叶子节点存储PK值，即id；
既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？
通常情况下，需要扫码两遍索引树。
例如：
select * from t where name='lisi';是如何执行的呢？
image
如粉红色路径，需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
2、什么是索引覆盖****(Covering index)****？
额，楼主并没有在MySQL的官网找到这个概念。
画外音：治学严谨吧？
借用一下SQL-Server官网的说法。
image
MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
image
不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
3、如何实现索引覆盖？
常见的方法是：将被查询的字段，建立到联合索引里去。
仍是《迅猛定位低效SQL？》中的例子：
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
第一个SQL语句：
image
select id,name from user where name='shenjian';
能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。
画外音，Extra：Using index。
第二个SQL语句：
image
select id,name,sex* from user where name='shenjian';*
能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。
画外音，Extra：Using index condition。
如果把(name)单列索引升级为联合索引(name, sex)就不同了。
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
image
可以看到：
select id,name ... where name='shenjian';
select id,name,sex* ... where name='shenjian';*
都能够命中索引覆盖，无需回表。
画外音，Extra：Using index。
4、哪些场景可以利用索引覆盖来优化SQL？
场景1：全表count查询优化
image
原表为：
user(PK id, name, sex)；
直接：
select count(name) from user;
不能利用索引覆盖。
添加索引：
alter table user add key(name);
就能够利用索引覆盖提效。
场景2：列查询回表优化
select id,name,sex ... where name='shenjian';
这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。
场景3：分页查询
select id,name,sex ... order by name limit 500,100;
将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。
InnoDB聚集索引普通索引，回表，索引覆盖，希望这1分钟大家有收获。
5、索引下推
对于user_table表，我们现在有（username,age）联合索引
如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，语句C如下："select * from user_table where username like '张%' and age > 10".
语句C有两种执行可能：
1）、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。过程如下图。
2）、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。过程如下图。
明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制
SET optimizer_switch = 'index_condition_pushdown=off';- 注意点：
1、innodb引擎的表，索引下推只能用于二级索引。
就像之前提到的，innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
- 2、索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。
假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率
select * from t where a > 2 and b > 10;
参考：https://www.jianshu.com/p/bdc9e57ccf8b、https://www.jianshu.com/p/8991cbca3854
