一、什么是索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

二、索引的优缺点

（1）优点
1.创建唯一性索引，保证数据库表中每一行数据的唯一性
2.大大加快数据的检索速度，这也是创建索引的最主要的原因
　3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
　5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。
（2）缺点
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
　2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
　3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

三、索引原理

（1）本质是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
（2） 磁盘IO与预读：当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO。
（3）InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K。

在MySQL中可通过如下命令查看页的大小

mysql> show variables like 'innodb_page_size';

四、索引的数据结构

1、平衡多路查找树（B-Tree）
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

（1）根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
（2）比较关键字29在区间（17,35），找到磁盘块1的指针P2。
（3）根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
（4）比较关键字29在区间（26,30），找到磁盘块3的指针P2。
（5）根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
（6）在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

2.B+Tree
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：
（1）非叶子节点只存储键值信息。
（2）所有叶子节点之间都有一个链指针。
（3）数据记录都存放在叶子节点中。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2–4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

五、索引的分类

1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.组合索引
-primary key(id,name):组合主键索引
-unique(id,name):组合唯一索引
-index(id,name):组合普通索引
4.全文索引fulltext :对文本的内容进行分词，进行搜索
5.空间索引spatial :了解就好，几乎不用

六、索引相关技术名词

（1）回表
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

（2）索引覆盖
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
应用场景：可以避免回表
1.全表count查询优化
2.列查询回表优化
3.分页查询

（3）最左匹配
只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合，所以在建立联合索引的时候查询最频繁的条件要放在左边
假设我们创建（a，b，c）这样的一个组合索引，那么相当于对a列进行排序，也就是我们创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引

（4）索引下推

select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
总结

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

（5）聚簇索引和非聚簇索引
聚簇索引：不是单独的索引类型，而是一种数据存储方式，将索引与数据存储在同一个叶子节点中。
非聚簇索引：数据文件和非数据文件分开存放。

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。
一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引，为它新创建聚簇索引时，就需要对已有数据重新进行排序，所以对表进行修改速度较慢是聚簇索引的缺点，对于经常更新的列不宜建立聚簇索引。
聚簇索引性能最好，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理地紧跟其后。一般要根据这个表最常用的SQL查询方式选择某个（或多个）字段作为聚簇索引（或复合聚簇索引）。

聚簇索引优点：
1、把相关数据保存在一起，因为mysql数据库读取数据是按照页读取的，当读取某一个用户数据时，相邻的数据也会加载到内存中。根据用户读取一个id的数据时，相邻数据被读取的可能性会非常高，这种按页加载就减少了IO操作
2、数据访问更快
3、使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

聚簇索引缺点：
1、聚簇索引最大限度提高了IO密集型应用性能，但是当数据都在内存中时，聚簇索引优势就没有了
2、插入速度严重依赖插入顺序。如果不是按照顺序插入，可能导致数据的移动设置页分裂，从而影响性能
3、更新聚簇索引的代价非常高，因为会强制INNODB将每个给跟新的行移动到新的位置上去
4、聚簇索引插入新列或者更新聚簇索引的时候可能导致页分裂
5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂导致数据存储不连续
6、二级索引需要的存储空间更大，因为二级索引中包含了主键列，同时二级索引需要两次查询才能查询到行数据
（6）覆盖索引

七、索引的优化

（1）尽量使用主键查询而不是其他索引查询，因为其他索引查询会触发回表查询。
（2）使用前缀索引
（3）使用索引扫描来排序
（4）union all,in,or 都能使用索引，推荐使用in
(5) 范围列可以用到索引。条件>,>=,<,<=,between，但是范围列后面的列无法用到索引，索引最多用于一个范围列。
（6）强制类型转换会全表扫描
（7）更新十分频繁，数据区分度不高的字段不宜建立索引。更新会变更B+Tree,会大大降低数据库性能。类似性别，数据区分度不大，不能有效过滤数据。区分度在80%以上的可以建立。可以用count(distinct(列名))/count(*)计算。
（8）创建索引列，不允许为null，可能会得到不符合预期的结果。
（9）当需要表连接时，最好不要超过三张表，因为需要join字段，数据类型必须一致。
（10）能使用limit尽量使用limit。

参考内容如下：
来源：简书
原文链接：https://www.jianshu.com/p/d0d3de6832b9
来源：CSDN
原文链接：https://blog.csdn.net/u013235478/article/details/50625677