MySQL索引

目录

1.索引是啥，解决啥问题的？

2.索引付出了什么代价？

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

4.索引背后的数据结构

---

1.索引是啥，解决啥问题的？

官方概念：索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

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

所谓的“索引”就相当于是在数据库中构建一个特殊的“目录”（一系列特定的数据结构，硬盘）通过这样的数据结构加快查询的速度，尽可能避免针对表数据的遍历操作。

2.索引付出了什么代价？

a) 引入索引需要消耗额外的存储空间。（相当于给一本书添加目录肯定会耗费纸张）

b) 引入索引之后确实能提高查询的效率，但是可能会影响到（不是一定会影响）增删改的效率。

这里的可能会影响是指有时候引入索引会导致增删改更慢：增删改的时候需要同步的更新维护索引（相当于修改书中正文部分的内容后还需要修改书的目录）；有时候引入索引会导致增删改更快：比如通过条件判断的方式来删除delete from student where id = 5;背后就有“查找”操作；有的时候没啥变化。

整体来说，索引利大于弊，日常开发还是会经常使用的。

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

a) show index from 表名;    查看索引  

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

b) create index 索引名 on 表名(列名);  给指定列创建索引

创建索引是一个“危险操作”。如果针对空表或者表中的数据比较少（几千、几万......）创建索引就谈不上危险不危险。一旦表中的数据比较大（千万级别），此时创建索引操作就可能会触发大量的硬盘IO，直接把机器就搞的卡死住了......

在最初建表的时候要提前规划好要创建哪些索引。

c) drop index 索引名 on 表名;     删除索引

只能删除咱们自己创建的索引，不能删除自动生成的。删除索引也是一个“危险操作”。

索引是针对列来创建的，后续查询的时候，查询条件使用的列和索引列匹配，才能索引生效才能提高查询效率。

针对一个比较大的表，创建/删除索引是非常危险的，可能会触发大量的硬盘IO，把机器搞挂了。

4.索引背后的数据结构

其实就是谈一谈B+树特点和优势。

B+树不同于B树（B树也可以写作B-树，这里 - 不是减号而是连接符），B树是有N个key，划分出N+1个区间。B+树是有N个key，划分出N个区间。

特点：

a) N叉搜索树（树的度是N），每个节点上包含N个Key，划分出N个区间。

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

c) 叶子结点这一层就构成了数据集合的全集。

d) 使用类似于链表这样的结构把叶子结点串起来。

优势：

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

此时高度降低了，但是每个节点的比较次数变多了，真的能比二叉树有优势吗？

优势是很大的！每个节点访问的时候，一次硬盘IO就可以了。

和某个节点进行比较的时候，是先进行一次硬盘IO，把所有的这个节点上的内容都读取出来，接下来的比较都是在内存中进行了。这里主要的目的不是为了减少比较的次数，而是减少硬盘IO的次数。

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

c) 所有的查询都落到叶子结点上，开销非常稳定，容易预估成本。

d) 叶子结点存储数据行，非叶子节点只存储索引列的key值，非叶子节点占据空间小，可以加载到内存中进一步的减少查询时IO的访问次数。

为啥二叉搜索树和哈希表都不适合给数据库做索引？

a) 二叉搜索树最大的问题在于“二叉”，当要保存的元素多的时候，就会使整个树的高度变的比较高。一旦高度高了，比较的次数就会变多，由于数据在硬盘上，每比较一次都要将一个节点的数据从硬盘读取到内存中，消耗的时间是很可观的。

b) 哈希表最大的问题在于只能进行相等查询。无法进行> <这样的范围查询，也无法进行like模糊查询。哈希表是要通过哈希函数把查询的key映射成数组下标，不是说key1 < key2 —> hash(key1) < hash(key2)。