索引的相关知识详解和慢查询优化

储备知识

1、索引的得根本原理就是将硬盘IO降下来
为表中的一行行记录创建索引其实就是为索引与记录创建一种对应关系
有了目录结构，以后查询都应该通过目录去查询

2、一次磁盘IO带来的影响：
以一个7200转/m的磁盘为例，一次IO的延迟时间=平均寻道时间（大概5ms) + 平均延迟时间（4ms）故一次IO延迟大概9ms左右

一台500-MIPS(Mi11ion Instructions Per Second)的机器每秒可以执行5亿条指令，因为指 令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

3、磁盘预读
#考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化:
当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次I0读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次Io，这个理论对于索引的数据结构设计非常有帮助。

4、索引原理精髓提炼
索引的目的在于提高查询效率，本质都是： 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也 就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

一、索引概述

什么是索引？

索引是存储引擎中的一种数据结构，或者说数据的组织方式，又称之为键key，为数据建立索引就好比为书建目录，目的是为了缩小查询范围，提升查询速度

为何要用索引？

为了优化查询效率
ps:创建完索引后会降低增删改的效率（文件不是很大时，影响效果不会很强烈）不过好就好在一般数据的读写比例是10:1，所以适当的建一些索引还是很有必要的。

如何正确的看待索引？

开发人员最懂业务，任何一个程序都有吸引用户的亮点，亮点背后对应的是热数据，这一点开发人员是最清楚的，开发人员最了解热数据对应的数据库表字段有哪些，所以开发程序过程中就应该提前为相应字段加上索引，而不是等软件上线以后由DBA发现慢查询sql后再做处理，因为：
1、一个软件慢会影响用户体验，但是慢的原因有很多，你不能立刻确定是不是sql的问题所以等定位到sql问题可能已经过去很久了，问题已经被拖了很久

2、因为大多数DBA都是管理型DBA而非开发型，所以即便是DBA从日志文件中看到了慢查询SQL，也会因为其不懂业务而很难分析出慢的原因，最后这顶锅还是得扣在你开发的脑袋上，躲得过初一躲不过十五

是不是索引越多越好？

因为索引太多，虽然加快了查询，但太多索引可能会将ibd文件变得非常大，占硬盘空间，且在写数据的时候会非常花时间，因为哪怕加入一条非常简单地数据，所有索引都得跟着变，这样会造成非常大的硬盘io

是不是有索引就可以优化查询效率

不是，只有查询语句中带有条件即（等值查询，范围查询等），命中索引才能优化查询效率

二、索引分类：

1、hash索引：更适合等值查询，不适合范围查询（不支持多列联合索引的最左匹配规则 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题）它是将数据打散之后再去查询

2、B+树索引；（innodb默认支持的索引）
二叉树->平衡二叉树->B树->B+树

3、FULLTEXT：全文索引 （只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配 like + %在文本比较少时是合适的 但是对于大量的文本数据检索会非常的慢 全文索引在大量的数据面前能比like快得多，但是准确度很低 百度在搜索文章的时候使用的就是全文索引。

4、RTREE：R树索引
RTREE在mysql很少使用，仅支持geometry数据类型，geometry数据类型一般填写经纬度那样的数据，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。RTREE范围查找很强，但Btree也不弱.

不同的存储引擎支持的索引类型也不一样

InnoDB存储引擎
支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索 引

MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引

Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引

因为mysql默认的存储引擎是innodb，而innodb存储引擎的索引模型/结构是B+树，所以我们着重介绍

三、索引的数据结构

3.1创建索引的两个步骤

1、提取索引字段的值当做key，value就是对应的本行记录
2、以key为基础比较大小，生成树形结构

以后的查询条件中使用了该字段，则会命中索引结构
例：

# 1、为user表的id字段创建索引，会以每条记录的id字段值为基础生成索引结构 
create index 索引名 on user(id); 

使用索引 
select * from user where id = xxx; 

# 2、为user表的name字段创建索引，会以每条记录的name字段值为基础生成索引结构 
create index 索引名 on user(id); 

使用索引 
select * from user where name = xxx;

innodb存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的，那么我们来看看其中的区别吧

3.2 二叉查找树

有user表，我们以id字段值为基础创建索引
1、提取每一条记录的id值作为key值，value为本行完整记录，即

key                            value 
10--------------------------->(10,zs) 
7---------------------------->(7,ls) 
13--------------------------->(13,ls) 
5----------------------------->(5,ls) 
8----------------------------->(8,ls) 
12--------------------------->(12,ls) 
17--------------------------->(17,ls)

2、以key值的大小为基础构建二叉树，如上图所示
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找id=12的用户信息
select * from user where id=12;

利用我们创建的二叉查找树索引，查找流程如下：
1、将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。
2、继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
3、把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即 id=1>2,name=xm。
利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

3.3 平衡二叉树

基于5.2所示的二叉树，我们确实可以快速地找到数据。
但是让我们回到二叉查找树地特点上，只论二叉查找树，它的特点只是
任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
所以，依据二叉查找树的特点，二叉树可以是这样构造的

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。 导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。 为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。

平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度不能超过1。
下面是平衡二叉树和非平衡二叉树的对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

3.4 B树

那么直接用平衡二叉树这种数据结构来构建索引有什么问题？
1、首先，因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。

2、另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

3、所以，如果我们单纯用平衡二叉树这种数据结构作为索引的数据结构，即每个磁盘块只放一个节点，每个节点中只存放一组键值对，此时如果数据量过大，二叉树的节点则会非常多，树的高度也随即变高，我们查找数据的也会进行很多次磁盘IO，查找数据的效率也会变得极低！

综上，如果我们能够在平衡二叉的树的基础上，把更多的节点放入一个磁盘块中，那么平衡二叉树的弊端也就解决了。即构建一个单节点可以存储多个键值对的平衡树，这就是B树。
B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。

注意：
– 1、图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。

– 2、图中的每个节点里面放入了多组键值对，一个节点也称为一页，一页即一个磁盘块，在mysql中数据读取的基本单位都是页，即一次io读取一个页的数据，所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：
1、先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
2、将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
3、将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

注意：
1、B树的构造是有一些规定的，但这不是本文的关注点，有兴趣的同学可以另行了解。
2、B树也是平衡的，当增加或删除数据而导致B树不平衡时，也是需要进行节点调整的。
那么B树是否就是索引的最终结构了呢？答案是no，B树只擅长做等值查询，而对于范围查询（范围查询的本质就是n次等值查询），或者说排序操作，B树也帮不了我们

select * from user where id=3; -- 擅长 
select * from user where id>3; -- 不擅长

3.5 B+树

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

根据上图我们来看下B+树和B树有什么不同。
1、B+树非叶子节点non-leaf node上是不存储数据的，仅存储键，而B树的非叶子节点中不仅存储键，也会存储数据。B+树之所以这么做的意义在于：树一个节点就是一个页，而数据库中页的大小是固定的，innodb存储引擎默认一页为16KB，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数（节点的子节点树）就会更大，那么树的高度必然更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2、B+树的阶数是等于键的数量的，例如上图，我们的B+树中每个节点可以存储3个键，3层B+树存可以存储 333 =9个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。而一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO，真是屌炸天的设计。

3、**因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。且叶子结点的数据，也有那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。**而B树因为数据分散在各个节点，要实现这一点是很不容易的。

而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。
MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

总结：

树的高度等于查询的次数
B+树存放的数据量如：一个节点有3个key，B+树的高度为3，则这个B+树所存放的数据为3的3次方为27个数据量
故B+树存放数据量的公式为：
假设一个节点有m个key，这棵树高度为n则：
这棵树存放的数据量为m的n次方

b+树：
在二叉树、平衡二叉树、b树的基础上做了进一步优化
只有叶子结点放真正的数据，这意味着在等量数据的前提下，B+树的高度是二叉树、平衡二叉树、b树中最低的，而树的高度越低查询速度越快，查询步数越少

b+树的性质：
1、索引字段要尽量的小，
2、索引的最左匹配原则（B+树是按照从筛选条件左至右的顺序来搜索树的，若条件中第一个命令不在索引中，那么B+树就不知道下一个节点该去哪查）

2、B+树的叶子节点都是排好序的，这意味着在范围查询上，B+树比b树更快，快就快在一旦找到了一个树叶节点，就不需要再从树根查起了（即不用回表查询）

四、聚集索引与非聚集索引

4.1什么是聚集索引，什么是非聚集索引？

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。这里我们主要介绍innodb存储引擎中的聚集索引和非聚集索引。

1、聚集索引（又称聚簇索引、主键索引，一张表必须有且只有一个）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键用的就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

2、非聚集索引（又称非聚簇索引、辅助索引，一张表可以创建多个辅助索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

4.2 利用聚集索引和非聚集索引查找数据

前面我们讲解B+树索引的时候并没有去说怎么在B+树中进行数据的查找，主要就是因为还没有引出聚集索引和非聚集索引的概念。下面我们通过讲解如何通过聚集索引以及非聚集索引查找数据表中数据的方式介绍一下B+树索引查找数据方法。

4.2.1 利用聚集索引查找数据

还是这张B+树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。现在假设我们要查找id>=18并且id<40的用户数据。对应的sql语句为
select * from user where id>=18 and id <40

其中id为主键。具体的查找过程如下：
1、一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。

2、要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。

3、同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。

4、因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。那么查找到此终止。最终我们找到满足条件的所有数据为：(18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。总共12条记录。

下面看下具体的查找流程图：

4.2.2 利用非聚集索引查找数据

读者看到这张图的时候可能会蒙，这是啥东西啊？怎么都是数字。如果有这种感觉，请仔细看上图中红字的解释。什么？还看不懂？那我再来解释下吧。首先，这个非聚集索引表示的是用户幸运数字的索引（为什么是幸运数字？一时兴起想起来的），此时表结构是这样的。

在叶子节点中，不再存储所有的数据了，存储的是键和主键。对于叶子节点中的x-y，比如10-23。左边的10表示的是索引的键值，右边的23表示的其对应的主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为
select * from user where luckNum=33;

查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，因为我们select要的是，即所有字段值，在辅助索引中并不存在，所以找到主键后我们需要再到聚集索引中查找具体对应的数据信息*，此时又回到了聚集索引的查找流程，下面看下具体的查找流程图：

在MyISAM中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。

4.2.3 覆盖索引与回表操作

命中了辅助索引，然后要找的字段值不存在与辅助索引的叶子节点上，则需要根据拿到的主键值再去聚集索引中查询一遍，然后再到聚集索引的叶子节点找到你想要的内容，这就叫回表操作
例如
create index xxx on user(name);
下述语句，命中了辅助索引，但是select需要查询出的除了辅助索引叶子节点有的name字段值外还想要age字段的值，那么需要进行回表操作
select name,age from user where name=“egon”;

命中了某棵索引树，然后在其叶子节点就找到了你想要的值，即不需要回表操作，就是覆盖了索引
例1：
create index xxx on user(name);
下述语句，覆盖了索引
select name from user where name=“egon”;

例2：
使用主键字段当作条件，百分百覆盖了索引，效率极高，推荐使用
如果id字段是主键，那么下述语句也覆盖了索引
select * from user where id=3;

总结

回表查询：通过辅助索引拿到主键值，然后再回到聚集索引从根再查一下
覆盖索引：不需要回表就能拿到你要的全部数据（最优方案，速度很快）

拓展内容

联合索引：将多个字段，联合在一起创建是一组索引，可以又多个key来命中
组合索引最左前缀
如果组合索引为：(name, email)
name and email - - 命中索引
name - - 命中索引
email - - 未命中索引
作用：
创建的是一组索引，但可以由多个键来命中，既节省了磁盘空间，又简化了创建索引的步骤，但必须带上左边的key
联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理

250W条记录的文件大小为167M（几百兆）
300W条记录的文件大小为240M（几百兆）

命中索引也未必会起到很好的提速效果：

1、对区分度高并且占用空间小的字段建立索引
2、针对范围查询命中了索引，如果范围很大，查询效率依旧很低，如何解决
（1）要么把范围缩小
（2）要么分段取值，一段一段的取最终把大范围给取完
（3）索引下推技术（默认开启）有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。（在MYSQL5.6之后的版本引入的技术）
（4）不要把查询字段放到函数或者参与运算

慢查询的优化步骤：
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

查询优化器 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个