“手撕”MySQL的索引

目录

二、索引的作用

三、索引的缺点 

四、如何使用索引

查看索引：

创建索引： 

 ​编辑

删除索引：

五、索引的底层原理

那什么是B树，什么是B+树呢？ 

B+树的好处：

 总结：

---

一、什么是索引

索引（index），在其他语言中，我们管索引叫下标，但是在MySQL中有点不一样，我们可以理解为目录！这些索引保存着我们的数据地址。

就像我们新华字典一样，如果我们想找一个字，我们可以通过拼音目录，部首目录等等，非常的快！

二、索引的作用

 就是来加快查询速度，为什么需要加快查询的速度呢？

答：比如我们在select中where一个条件，我们有大量的数据需要查询，如果一条一条遍历着去查询，那么时间复杂度将是O（N）。

O（N）看起来不大，但是它和我们数据结构中的O（N）不大一样，为什么呢？

因为在数据结构中是利用内存来遍历的，但是MySQL却是利用硬盘来读的。而硬盘的速度非常慢，甚至比内存慢个几千倍。这就非常浪费时间，非常消耗咱们数据库的资源

 这时候我们就需要索引来帮忙了。例如我们select * from stduent where id=5：

无索引：我从最开始第一个开始遍历，一个一个遍历，不是的话就pass掉，不打印，直到我遇到了id=5，才打印出来。

有索引：我通过一种数据结构叫B+树，直接找到id=5，直接打印！

所以我们发现：索引可以大大减少我们的时间！ 

三、索引的缺点 

好东西肯定是需要付出一些代价的，不可能十全十美~~

1）加索引需要耗费一些空间

但是对于后端开发来讲，这甚至不算什么，存储空间不够加硬盘就好啦~~若加硬盘话不够的话，还可以加机器~~

2） 加了索引，可能会影响”增删改“的效率

对于增删改可能会变快，也可能会变慢，也可能不变~~

比如 delete * from student where id=5；来说，确实会变快，因为少了很多遍历，

但是同时，”增删改“是需要同步更新维护索引的，需要调整。

在业务中，查询的频率比“增删改”高很多，所以这不算什么问题~~

但，上述两个问题不算什么问题，总的来说：利大于弊。

四、如何使用索引

值得注意的是：

创建主键约束（primary key）、唯一约束（unique）、外键约束（foreign key）时，会自动创建 对应列的索引。

假设我有student这个表：student（id,sn,name,qq_mail,classes_id）

查看索引：

show index from 表名;

创建索引： 

create index 索引名 on 表名(字段名);

 

创造索引其实是一个危险的操作！

对于数据量比较小（几万，几十万）创造索引，没什么危险的，

但是对于数据量特别大的（上千万等）创建索引，会触发大量的硬盘OI，就可能会卡死。

删除索引：

只能删除咱自己创造的索引，不能删除系统自动生成的索引。 

drop index 索引名 on 表名;

 

五、索引的底层原理

 索引用到的数据结构，其实是B+树（念做B加树）。它相当于B树，也可以叫做B-树（念做B树！不叫B减树）的plus版本。

B+树甚至可以说是为了数据库量身定做的数据结构，因为除了数据库甚至想不到B+树的其他用法~~

为什么不用其他数据结构呢，比如二叉搜索树，或者哈希表？ 

1）二叉搜索树，最大的问题在于“二叉”当腰保存的元素多的时候，就会使整个树的高度变得比较高，它会有很多个节点，数据库是由硬盘读取的，每比较一次，都是很费时间的。

2）哈希表，最大的问题在于，只能进行“相等‘查询，无法进行>  <这样”范围查询“，也无法进行like这样的模糊查询。

那什么是B树，什么是B+树呢？ 

B树：就是一个节点里面有多个数（二叉树一个节点只有一个数），假设一个节点有N个key值，它会划分出N+1个区间。这样就可以降低树的高度，从而减少节点的个数。这样就可以节省很多时间了。

问题：既然每个节点的数量多了，比较的次数不就多了吗，总次数还是不变，为什么会减少时间呢？

答：注意的是，这里是硬盘读取，硬盘一次读取一个节点（一个节点有多个数）！所以只要节点数量少，硬盘读取的次数就能减少，自然而然时间也会减少了。接下来的比较是在内存中比较的，也会比较快~~

B+树：B树的plus版本。 区别是：N叉搜索树，每个节点上包含N个key值，划分N个区间，上一个节点里面的  每一个数  都会  “下沉”  成为下一个节点的最后一个数。直到最后一层，他们会前后串起来，形成一个链表，就是一个数据全集，里面会存储数据库里面的数据，就像柳树的柳一样。

查询思路：

比如，上述的图是id的B+树，我要查id=3的数据，MySQL会查3在8和15的哪里，在8的左边，再查在2和5和8的哪里，在2和5的中间，到第3层，就查到id=3的数据了。最后一层就像柳树的柳枝一样，数据都挂在树枝上面。

 那重复出现的数据不会太占内存吗？

答：不会，简直是微乎其微。也就一个数字下沉了而已，假设我有1000w个数字下沉，一个数字int为4个字节，也就是4000w个。

记住：kb->thousand（千），mb->million（百万），gb->billion（十亿），也才40个million，40MB而已。

而且这只是个数字，并不像最后一层一样挂数据。但是好处却是非常大的。

B+树的好处：

1）N叉搜索树，高度比较低，节点少，此时硬盘IO次数就比较少

2）叶子节点是全集，并且用链表结构链接，非常便于范围查询

3）B+树，所有的查询都是要落到叶子节点上完成的，任何一次查询，经理的IO次数和比较次数都是差不多的，查询的开销稳定

4）叶子节点存储key值和数据，非叶子节点只存储key值，空间耗费小

 总结：

1）索引是啥，解决啥问题

索引相当于书的目录，能够提高查询的速度

2）索引付出了什么代价

a）需要更多的存储空间

b）肯会影响增删改的效率（不一定会影响）整体来说，索引的利大于弊

3）如何使用sql操作索引，是否有注意事项

a）show index from 表名；查看索引（主键，外键，unique会自动生成索引）

b）create index 索引名 on 表名（列名）：给指定列创建索引

c）drop index 索引名 on 表名：删除索引

索引是针对列来常见的，后续查询的时候，查询条件使用的列和索引列匹配，才能索引生效，提高效率。针对一个比较大的表，创建/删除索引，是非常危险，可能会出发大量的硬盘IO，八机器搞挂了。

4）索引背后的数据结构->B+树特点和优势

特点：

a）N叉搜索树，每个节点上包含N个key值，划分N个区间

b）每个父节点中的元素，都会下沉到子节点中，作为该子节点中，作为该节点中最大值的角色来存在

c）叶子接待你这一层就构成了数据集合的全集

d）使用类似于链表职责与的结构，把叶子节点串起来

优势：

a）N叉搜索树，高度比较低，降低了硬盘IO次数

b）范围查询非常方便&高效

c）所有查询都洛到叶子节点上，开销非常稳定，容易预估成本

d）叶子节点存储key值和数据，非叶子节点只存储key值，空间耗费小（也可以加载在内存中进一步减少时间）