MySQL之索引

介绍

一、什么是索引？

　　对于业务系统，我们一般的更新操作要比查询操作少的多。对于系统经常优化的点也就是查询操作，因为在查询的时候很浪费时间，有的查询就是全表扫描，查询时间就特别的慢。这个时候我们就要考虑索引，索引可以大大加快我们的查询速度。

二、为什么要用索引？

　　对于大量的查询，有了索引可以将查询的性能提高好几个等级。比如我们在图书馆找书一样，我们先找到该图书的楼层，再找到图书的分类编码，最后找到图书。这样找图书就和索引找数据是一样的道理。

索引的原理

一、基本原理

　　通过缩小我们查询范围来筛选出我们想要的结果。同时把随机时间变成顺序事件，也就是说有了这种索引机制，我们总是可以用同一种方式来查找数据。就像图书馆找书一样，我们可以把找书的原理适用于任何的查找。

　　数据库也是一样的。但是这里的查询要复杂的多，这里不仅面临等值查询，还有范围查询（between、or、and、in、>、<等）、模糊查询（like）等等。数据库对于这些的查询应该怎么做到查询的速度快？我来回想之前的图书查找，我们把图书查找联想到新华字典的查找，新华字典将字按照首字母进行分割。我们也可以考虑将查询的数据进行分割，比如现在有1000条数据，我们分成十份，1-100分下去。现在我们要查找33，这个数据就是在1-100之间我们直接去从查找，直接过滤掉一些不符合规则的数据。但是数据库的实现一方面将数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本要比访问内存高得多。我们说的上面的思路是难以实现的。

二、索引的数据结构

上面图所画结构就是索引的数据结构图（B+树），浅蓝色代表磁盘，红色代表是数据项，黄色代表指针。真实的数据这存在叶节点（最底层）。例如我们的1,3,9,10,14,18,25,38,45,50,60,80,101,123。非叶子节点只存指引搜索方向的数据项。

B+树的查找过程：比如现在我们查找数据项14，首先我们将磁盘1加载到内存中，这是发生第一次IO，在内存中利用二分查找法找到数据项14在小于15的区间，这是锁定磁盘1的P1指针，这个操作在内存中发生可以忽略不计。这个时候我们通过P2指针将磁盘2加载到内存中，这是又发生了第二次IO。我们在内存通过二分查找算法找到数据项14在大于13的区间，这是通过磁盘2的指针P3指向将磁盘7加载到内存当中，发生了第三次IO，这个时候通过二分查找就找到了数据项14。我们只通过了产生三次IO就找到了数据，如果没有索引的话，每个数据项的都要差生一次IO，显然查询的速度很慢、成本也好高。

B+树的性质：

1、索引的字段要尽量小：通过上面的分析，我们知道的IO的次数取决于树的高度。数据量的大小 = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2、索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。