定义
什么是索引？ 在学习一样事情之前,要先知道自己学的什么。
官方解释：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
让我们咀嚼一下这些加粗的关键词。 当我们查询条件没有索引的时候,mysql就只能顺序io一条条比对,就像你只能一片片去翻你想翻到的书页,如果有1000条数据,你查询的数据在什么位置,他就要io多少次,可能就是1000次,我们知道磁盘的io效率是相当之慢的，怎么解决,上索引,索引本质就是一种数据结构,它存在的目的就是为了让我们避免大量的顺序io,用特定的数据结构和查找方法尽可能的减少io次数。
索引不是mysql特有的,涉及到任何文件、数据查找的系统都有,比如windows操作系统也有索引的存在,帮助我们更加快速的找到对应的文件。
为了可以让查找的速度提升,那树就是一种很好数据结构。mysql在索引的设计上采用的就是采用B+树的结构来对数据进行排序存储,不管是聚簇索引 辅助索引 联合索引,本质上都是在b+树的结构上进行查找。
既然提到了B+树,还是要拓展一下,为啥B+树会作为主流数据结构。树的类型有这么多,为啥用B+树呢？那下面列举几种常见的🌲树🌲。
数据结构
普通二叉树
它是一颗普普通通的树，特点
1:左边的字节点一定小于右边的子节点。
2:每个节点最多有2个叶子结点。
用了这种结构,再也不用顺序io了,可以通过折半查找来查询我们想要的数据,插入和查找的时间复杂度变成了O(log(N))，确实一定程度上减缓了io次数。
缺点:但是容易失衡，就像这样。
动图版:
随着数据量越来越大,树阶越来越高,查询次数和io大幅提升啊。所以这种 肯定不适合mysql。那就需要优化一下,来个让它需要自动平衡,每次写入的时候判断左右两个节点的层级差不能超过一层,否则就左旋或者右旋自动平衡。
平衡二叉树
下图
动图版
看起来是平衡了,但是如果节点增多了,树阶还是会增多,io次数又要上去了，所以这也不适合mysql。
缺点:树阶还是高。
B树
基于平衡二叉树继续优化一下,让每个节点下可以放的叶子节点多一些，树高降低,io也就降下来了。
这个时候就需要B树(也叫平衡多路查找树)，下图
动图版写入：
动图版查询:
这样看来树已经平衡,而且多路也可以降低树的高度。你可能有个大胆的想法,把数据都存在一个叶子节点上,是不是1亿数据最多也只要io三次就够了。
这时候要先引出一下mysql是如何于磁盘交互来读取数据的。
上面提到的存储引擎(innodb、myIsam),它的基础存储单位是页。 当我们发送查询指令,mysql会从磁盘读取数据,但是磁盘io效率极差,所以这里有一些小的优化措施,mysql在做一次读取的时候除了会加载需要读取数据,还会将地址附近的数据一起加载出来,这个动作叫预读。而mysql一次可以加载出来的数据就是16KB**,所以一页的大小就是16K(注意看我上图画的,每一个大方块代表一页,一页里面的内容就是一次io读取出来的数据)，这样就可以根据你每个key的大小推算出来一个节点能放多少路的数据了。 但是除了key，B树的行数据也是存在每节点上的,看看一条行数据的大小,就能推算出一个节点的数据有多大了，数据大了,那一页上能存放的数据就少了，那节点就会多，节点多了，树又高了(套娃警告)。
B+树
所以为了让每个节点上尽可能的多存放数据,就要缩衣节食。B+树就在节点的数据存储上继续优化,他们唯一的区别就是，B+树在非叶子节点上不再存在数据,只存放指针地址。如下图
动图版写入:
从B树和B+树的结构图可以看出,两者在树结构上的差别不大,都是多路平衡查找树，区别在于以下两点:
1:B+树所有的非叶子节点上就只有key了,而data只存放在叶子节点,每个非页子节点上的数据占用的空间就少了,再算下16KB能放多少数据了。(B+树三层的高度就可以存放2000万的数据,每条数据如果在1kb左右)。
首先我们算出非叶子节点能放多少指针,一个指针的大小是 6bit ,id如果是integer类型是8bit,一共14bit,一页是16kb,也就是16384bit,所以一层非叶子节点最多可以存在16384/14=1170个指针,三阶的树这一层有16个叶子节点,就可以存放1770 * 16个指针,每个指针又指向一页,刚刚算出来一页放1170个指针,那就再乘以1770,所以最终可以存放1170 * 1170 * 16=21902400条记录。也就是说在一棵三阶的B+树就可以放2000万的数据量级以内,3次io即可查询到。B树同理,只不过B树非叶子节点不止有id,还有数据,一页就不止是8+6=14了,可能会更大,所以相同树高存的东西也就少了。 拓展点: 如果超过这个数量,io次数又会增加,所以我们在设计表的时候需要清楚的知道每一行的数据有多大,来保证每张表在三层树高的情况下最多能存放多少数据,超过这个量级,可能就需要引入一些分表、或者数据迁移的工作,这也是大表优化的一些手段。
2:同时B树还存在一个缺点,范围查询效率不高,因为当你查到子节点的时候发现没路了,那需要重新返回根节点再次遍历,而在B+树中,可以看到每个叶子节点之间用双向指针连接,这样当进行范围查询,就可以通过叶子节点的双向指针来进行快速的查找了。
拓展点:为啥平时我们都要要求主键是自增的呢？(不管是数据库主键自增还是分布式算法的自增)。这也和树的结构有关。看下下面这个动图。
这颗B+树的数据按顺序写入,但是跳过了4,如果后面插进来一个4的key,那树为了维持节点的平衡,就会进行左旋或者右旋,在数据量大或者写入密集的情况下,自平衡的动作是非常消耗资源的,所以为了避免这种情况,我们就要让写入的key按顺序自增!(知其然知其所以然哟)
索引的基础结构B+树以及B+树的好处介绍完了,索引快的原因就大致清楚了吧。接下来再来看看具体的索引类型。
拓展点:除了树形结构,索引比较常见的还有hash表结构,和java中的hashMap类似,以键值对的方式存储数据,key存放索引字段,value就存储行数据或地址。hash结构通过index获取地址的查询效率是非常之高的,时间复杂度是O(1),但是缺点就是无法支持范围查询,所以适用的场景不了,了解一下就好了。
索引类型
首先索引分为两大块
- 聚集索引(也叫主键索引或聚簇索引)
- 非聚集索引(辅助索引、联合索引、全文索引等都是非聚集索引)
为啥这么这么分,且往下看
innodb的索引
聚集索引：
聚集索引就是我们所说的主键索引,如果没有主键,innodb就会自动创建一个rowId来构建聚簇索引,平时也许我们都知道主键索引查询效率最高,但是这又是为啥呢？首先innodb才有聚集索引(myisam没有真正意义上的聚集索引，具体后面会说到)。看这张图。
当我们要查询36的时候,首先进行两次io，查找到了这个id所在的位置,B+树的叶子节点可以直接存放数据,所以就直接定位到id所对应的行数据。
IO总次数：3次
辅助索引
这次用非主键字段来查,可以看到辅助索引的叶子节点只会存放id,然后根据指针再去主键索引中查到对应的行数据(这里的步骤和上面的主键索引查询步骤一样了)。
IO次数:最多6次
拓展点: 这个重新根据主键id去查询行数据的行为被称为回表查询。所以知道为啥叫辅助了,辅助就是先找到大哥(主键),再根据主键去查到最终想要的数据。(不准抢人头)。当然这个也有优化的方案,后面会说。
说到这里可以再点一下题，上面这样分的原因。方便大家记住，innodb下主键索引和非主键索引的主要区别就在于这里，主键索引直接可以拿到行数据,而非主键索引都需要回表,所以会增加Io次数,这就说明了为什么主键索引的速度最快。
联合索引
当我们的where语句后面跟着的条件有多个的时候,就要用到联合索引了,多个查询字段组合在一起的索引被称为联合索引,先看下联合索引的结构和查询路径
IO次数:最多6次。
拓展点:最左匹配原则。 联合索引是怎么进行排序的呢？根据你联合索引的数量,比如abc三个索引,先根据a排序，在a相等的情况下,再根据b排序,a、b都相等的情况下,再根据c排序,当你建立联合索引(a、b、c)的同时,等价于建立了(a)(a、b)(a、b、c)三个索引,所以当where后面的条件要使联合索引生效,条件必须根据这个顺序来,即必须要有a，再有b，再有c，如果哪个索引漏了,那后面的条件就都失效了。为什么直接来个b、c就不生效了呢?你想啊,我的排序是要从a开始的,只有保障了a的有序才能查b,单看b的排序是杂乱无章,那你直接给我塞个我应该从哪里走？只能全表扫描了呀。所以索引也就失效了。 但是你说我a、b换下位置可不可以,比如select * from table where a=xx and b=xx或者select * from table where b=xx and a=xx,这样是可以的,mysql没这么傻这样就认不出来了,查询优化器会帮你把位置调整过来的.
以上,索引的基本内容就介绍完了,下面再说一下和索引有密切关系的一个内容。
myisam的索引
刚刚在说主键索引的时候,提到了innodb和myiSam。
它们的索引区别就在于myIsam可以理解为只有辅助索引,因为它的叶子节点是不存放数据的,而是只存放数据的指针。
看图:
IO次数:4次。
这里和innodb的辅助索引还是有一丢丢区别!它拿到指针后直接去磁盘拿到对应的数据,没有回表了,但是还是多一次io。所以查询的时候效率会相对来说低一些。(但是他们都是B+树，有些人会搞混,觉得myiSam不存在数据就不是B+树，是不是B+树取决于你的叶子节点是否只存放指针)。
它的其他索引就不画了,因为不管是主键索引还是辅助索引。查询路径和这个一样,都是找到地址后再去查一遍。
myisam不支持主键索引不代表它就不能设置主键索引了！myisam的也有主键索引,但是主要是用来标示字段唯一,刚刚也提到,它没有真正意义上的聚集索引,是因为它除了唯一性以外查询路径和结构基本一样,不要搞浑说它没主键索。
存储引擎
myIsam和innodb是mysql提供的两种数据库引擎,在建表的时候指定。它们的文件结构不同，应对的使用场景也不一样,先看下他们的区别
| innodb | myisam |
|---|---|
| 存储文件区别 | 存储文件区别 |
| 支持事务 | 不支持事务 |
| 支持外键 | 不支持外键 |
| 不保存表的具体行数(select count需要全表扫描) | 保存了整个表的行数 |
| 不支持全文索引(5.8版本后支持) | 支持 |
| 支持表、行(默认)级锁 | 只有表锁 |
本期内容我们只关注存储文件区别,这和我们的上面说到的他们的索引区别有关系。
先说myIsam和innodb索引文件上的区别。 myIsam的表有三个文件:
- tableName.frm：表结构文件
- tableName.MYD：数据文件（MyISAM Data）
- tableName.MYI：索引文件（MyISAM Index）
Innodb的表有两个文件:
- tableName.frm：表结构文件
- tableName.ibd：索引和数据文件（InnoDB Data）。
从这个物理文件的区别也就可以看出为什么myIsam的没有主键索引,因为它的索引和数据物理文件就是分开的，拿到地址必须再去数据文件中获取具体的行数据内容。
索引的优化
上面的索引基本都介绍完了，那是不是无脑上索引就可以了,万事大吉。万万不可！ 首先索引会带来两个缺陷： 1:刚刚提到不同存储引擎的索引文件不一样的,但是他们都是需要占用物磁盘理空间的,每一条数据的增加都是增加一个索引,随意的添加索引导致磁盘空间的过度占用。 2:因为每次的写入和删除都会调整索引,所以在删写频繁的时候也会影响到吞吐量,提升db的负荷。
第一条优化建议就是 1:对于那些大量重复字段(性别、状态)没必要建立索引。 2:对于经常查询的热点字段建立索引,如果有多条件查询,尽量建立组合索引,这样也会节约空间成本。
好钢用在刀刃上
覆盖索引 上面说到,非主键索引的查询都会有一次回表，那有办法让他不回表吗,可以,那就是查询的同时将索引的值带入select后面的字段中，这样你查询的行数据就直接再你的条件中了呀,还回啥表。
注意,这种场景其实不常用,很多查询场景下其实我们需要很多字段的,不可能将所有的字段都设置索引，所以还是要分场景,不要为了覆盖而搞了一大堆索引，索引也是有成本的,有写入成本,存储成本。只有所需字段比较少且是热点数据的情况下,才可能需要这么设置。
其次:索引也会存在很多建立了之后不生效的情况，除了上述说到的最左匹配,还有诸如以下
- 隐式转换-查询条件的数据类型和字段的数据类型不匹配。
- 查询条件有is null、is not null 不走索引。
- 查询条件是用函数或计算操作。比如concat(‘jingxi’,1)不走索引;
- or条件,任意一个or连接的条件没有索引,就会失效。
- 查询优化器的成本计算导致不走索引。
- 这些原因有部分通过上面的分析应该可以推断出原因，这里就不细说了比较固定,可以通过explan去查看这条sql是否走了索引,走了什么索引。
你需要了解这些隐患，才能避免写出有风险的sql,db挂了的压力可想而知，千万别忽视哦。
总结:
这片文章主要围绕的是索引来讲,以索引为引展开的, 索引的本质是一种数据结构,B+树,基于B+树的多种索引类型,聚集索引、辅助索引、联合索引他们的概念和区别,顺便带了一下mysql和磁盘的交互。然后根据聚集索引引出了innoDB和myIsam这两个存储引擎,他们在索引上处理方式的不同，最后提了一下索引场景和索引失效的风险,可以在后期进行sql优化的时候注意到。 再列下索引方面的区别:
| 存储引擎 | 数据结构 | 支持索引 |
|---|---|---|
| innodb | B+树(叶子节点可以存数据和id) | 主键(必须)、辅助索引、联合索引 |
| myisam | B+树 (叶子节点只存指针) | 主键(非必须,除了唯一性和其他索引没区别)、辅助索引、联合索引 |
(sql不优化，老板优化你)。
不过上面提到的有一些概念还是可以延伸出去,比如
-
页具体的分配方式(可以回去看看我的页码是随机写的,它就是随机分配的,很细吧)。
-
上面讲的到各种树可以再去了解,什么树的前中后序遍历啊,红黑树啊。
-
sql优化的细节,查询优化器的判定逻辑,大表分页怎么做呀,explain的各种字段含义、mysql5.7后的优化、引下推技术(面试高频)。两种数据引擎之间更多的区别,使用场景。
看到了就顺便查一下,通过一些成熟的商业工具也可以学习到这些技术人家是怎么样去使用的。学习嘛,就是不断拓展,形成自己的知识海。
下一篇会讲mysql的锁,也是一堆概念,线上容易出问题,面试官爱盘,继续拆解之！
码字画图不易,如果看官老爷觉得有收获或者说的不对的地方请留言交流哟。我是饭饭,从业4年,目前供职于桔厂任服务端开发,将自己所看所想沉淀下来,希望带给大家不一样的思路！下次见，冲～
