MySQL索引详解
MySQL索引详解
索引介绍
索引本质上是排好序的能快速查找的数据结构，有效的使用索引可以高效的检索数据。在MySQL官网的介绍中，在查询中的一个列或多个列上创建索引是改善查询性能的最好方法。
简单来说，索引具有的优势分为两个。一是能提高检索效率，降低数据库的I/O成本。因为索引采用了特殊的数据结构，例如B树或者哈希表，能够快速的检索到所需的数据，而不需要大量的检索从而产生不必要的I/O成本。二是降低数据的排序成本。对于在查询语句中使用ORDER BY或者GROUP BY的情况，由于索引已经将数据按照索引键值进行排序后存放，故当查询语句的排序分组字段与索引字段正好相同，则就不需要进行额外的排序操作。
虽然索引的使用能极大的提高检索的效率，但是索引也会带来一些问题。一是索引本身也是一张保存了索引键值数据的表，并存有指向实体表的记录，故索引也需要占用磁盘空间。二是不必要的索引会增加增删改的成本，因为在增删改表的同时，必须更新收到影响的索引。
索引的原理
大多数的MySQL索引(PRIMARY KEY, UNIQUE, INDEX, FULLTEXT)存储在B树，但是表示空间数据类型的索引则使用R树，基于Memory引擎的表则支持哈希索引，InnoDB对FULLTEXT索引使用倒排表。
这里着重讨论大多数MySQL索引使用的B树。但是在此之前，我们先了解下为什么索引要采用B树这个数据结构。
现实世界中的数据是复杂而且基本无序的，要想能够高效的查找到需要的数据，比较简单的方法就是将数据变得有序，本质上就是将随机的事件变成顺序的事件。在数据库中，往往需要进行各种复杂的查询操作，如果简单的将数据进行分段排序并查找，在数据量大的情况下也是极其耗时的。正因为如此，平均复杂度为对数级别的查找树算法更适合与在数据库中进行查询操作。并且由于查找数据的过程中需要访问磁盘，而由于程序局部性原理，操作系统在实现访问磁盘的I/O操作中，往往是基于页进行访问，即对于访问的数据，将其相邻的数据也保存至内存中。
正是满足上述要求，每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级，故B树应运而生。
详解B树
如上图这是一个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+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项数量会大幅度下降，导致树增高。
2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果na me相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
索引的建立
什么情况需要创建索引：1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，即外键关系创建索引
什么情况不建议创建索引：1. 频繁更新的字段不适合创建索引
2. 表中记录太少
3. 经常需要增删改的表
4. 数据区分度不高的表
建立索引的原则
1.最左前缀匹配原则，非常重要的原则。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
4.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
索引的分类
1.单值索引：一个索引只包含单个列，一个表可以有多个单列索引
2.唯一索引：索引列的值唯一，可以是空值
3.复合索引：一个索引包含多个列，最多可以包含16列。在查询条件中要是想使用复合索引提高查询效率，查询条件字段必须满足最左前缀原则。例如有一索引(col1, col2, col3)，只有查询条件为(col1) , (col1, col2) , (col1, col2, col3)时经过索引查找，但是也有例外情况，如果col1,col2,col3包含除了主键，外键，唯一列的全部字段，则无论查询条件如何都会使用到该复合索引。除此之外，还有一种情况就是出现覆盖索引。覆盖索引就是指如果select查询的字段全部出现在索引中，则不需要对数据表进行查询，只需要查询索引即可，这就是覆盖索引。例如现有一个复合索引(col1, col2, col3)，如果执行select * from table where col2=xx，则不会使用索引，但是对于select col2 from table where col2=xx，则会使用到索引。
索引失效的情况
1. 违反最左前缀原则。例如表中有索引(col1, col2, col3), 在col1,col2,col3不是除主键和外键外全部字段的情况下，查询条件字段为(col2), (col3),(col2, col3)的情况下使用索引会因为违反了最左前缀原则而导致索引失效。
2. 对于复合索引而言，如果在查询条件的索引列中存在某一个字段进行范围查找，则该字段之后的索引列无法使用索引查询，从而导致索引失效。
参考文档：美团技术团队的文章《MySQL索引原理及慢查询优化》https://tech.meituan.com/2014/06/30/mysql-index.html
