MySql 数据库“索引”初体验

    作为程序猿我们不仅要能编写正确的SQL 语句，也要编写高性能的SQL 语句，下面我们就来共同了解一下MySql 数据库的索引，它可以帮助MySql 高效获取我们想要读取的数据。

索引是什么：

    MySql 官方对索引的定义为：索引(index) 是帮助MySql 高效获取数据的数据结构。因此我们可以知道索引的本质是一种数据结构。我们也可以理解为“排好序的快速查找的数据结构”。除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
    一般来说索引本身也很大，因此不可能全部存储在内存中，索引往往以索引文件的形式存储在磁盘上。我们平时所说的索引，如果在没有特定说明的情况下，指的都是B 树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，符合索引，前缀索引，唯一索引默认使用的都是B+ 树索引。当然除了B+ 树这种索引之外，还有哈希索引(hash index)等。

---

索引的优缺点：

    优点：对于需要大量查找而增删改需求很少的数据，通过建立索引可以提高检索的效率，降低数据库的IO 成本；通过索引可以对数据进行排序，降低数据排序的成本，降低了CPU 的消耗。
    缺点：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也需要占用空间；虽然索引可以大大提高查询的速度，但是却会降低更新表(增删改)的速度，因为在更新数据表时，MySql 不仅要保存数据，还要保存索引文件每次添加的索引列的字段。

---

索引分类：

    1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
    2. 唯一索引：索引列的值必须唯一，但允许有空值
    3. 复合索引：即一个索引包含多个列

---

索引结构：

    MySql 有多种索引结构，有B+Tree 索引，Hash 索引，full-text 索引，R-Tree 索引等。这里主要介绍B+Tree 索引：原B+Tree 介绍博客地址传送门~

    如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程
    如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质
    1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如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的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

---

创建索引的技巧：

     1. 主键自动建立唯一索引
     2.频繁作为查询条件的字段可以创建索引
     3.查询中与其他表关联的字段，外键关系建立索引
     4.查询中排序的字段，排序的字段若建立索引将会大大提高排序速度
     5.WHERE 条件里用不到的字段不适合创建索引
     6.频繁更新(增删改)的字段不适合创建索引
     7.表记录很少的情况下不适合创建索引
     8.数据重复且分布平均的字段

---

下一篇：MySql 之EXPLAIN 关键字

                                                                                                                             参考文献：
                                                                                                                                    无所不能的互联网
                                                                                                                                    美团技术点评团队