MySQL索引的类型与实现

索引

本文将对索引的知识点进行总结归纳

---

1. 索引的概念

索引是一种数据结构，能够帮助我们快速地检索数据库中的数据

2. 索引具体采用哪种数据结构

常见的MySQL主要有两种索引的数据结构，Hash索引和B+ Tree索引，我们能使用的InnoDB引擎，默认的是B+ Tree。
当然了，MyISAM也是使用B+ Tree作为索引的数据结构，至于Hash索引，HEAP/MEMORY引擎才支持。

3. 采用B+ Tree索引和Hash索引对比起来有什么优缺点

1. 如果是等值查询，那么哈希索引会有明显优势，因为只需要经过一次算法就可以找到相应的键值；当然了，前提是键值都是唯一的。如果键值不唯一，就需要找到该键的所在位置，然后再根据链表(或其他数据结构)往后扫描，直到找到相应的数据；
2. 如果是范围查询检索的话，Hash索引就毫无用武之地了，因为原先有序的键值，经过Hash算法后，有可能会编程不连续的了，就没法进行范围查询检索。而B+ Tree(B Tree进行少许改进，每个叶子节点都有指针连着)，由于结构的改进，不仅能够进行范围查询，而且比B Tree查询效率更高；
3. 同理，Hash索引也没办法利用索引完成排序，以及 like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询，本质上也是范围查询)；
4. Hash索引也不支持多列联合索引的最左匹配规则；
5. B+ Tree索引的关键字检索效率比较平均，不像B Tree那样波动幅度大，在有大量重复键值的情况下，Hash索引的效率也是很低的，因为存在所谓的Hash冲突问题。

4. 最左匹配原则

在MySQL建立联合索引时会遵循最左前缀匹配规则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建立联合索引

alter table `table_name` add index (`col1`,`col2`,`col3`);

联合索引实际建立了 (col1)、(col1,col2)、(col1,col2,col3) 三个索引

5. MyISAM存储引擎与InnoDB存储引擎索引实现的不同(存储结构)

1. MyISAM引擎——主键索引
   使用B+ Tree作为索引结构，叶节点的data域存放的是数据记录的地址。原理图：
   
2. MyISAM引擎——辅助(非主键)索引
   以col2列建立索引，得到的辅助索引结构跟上面的主键索引的结构是相同的。原理图：
   
3. InnoDB引擎——主键索引
   我们已经知道InnoDB索引是聚簇索引，它的索引和数据是存放在同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图所示最底层的叶子节点有三行数据，对应于数据表中的col1 col2 col3数据项：
   
4. InnoDB引擎——辅助(非主键)索引
   这次我们以数据表中的col3列的字符串数据建立辅助索引，它的索引结构与主键索引的结构有很大差别，我们来看下面的图：
   在最底层的叶子节点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值：
   

6. 选择UUID还是整型自增作为主键

UUID 是 通用唯一识别码（Universally Unique Identifier）的缩写，是一种软件建构的标准，亦为开放软件基金会组织在分布式计算环境领域的一部分。其目的，是让分布式系统中的所有元素，都能有唯一的辨识信息，而不需要通过中央控制端来做辨识信息的指定。如此一来，每个人都可以创建不与其它人冲突的UUID。在这样的情况下，就不需考虑数据库创建时的名称重复问题。目前最广泛应用的UUID，是微软公司的全局唯一标识符（GUID），而其他重要的应用，则有Linux ext2/ext3文件系统、LUKS加密分区、GNOME、KDE、Mac OS X等等。另外我们也可以在e2fsprogs包中的UUID库找到实现。
InnoDB推荐使用整型自增主键而不是UUID

1. UUID是字符串，比整型消耗更多的存储空间
2. 在B+ Tree中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速
3. 自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续的；UUID是随机产生的，读取的上下两行数据是分散的，不适合执行范围查询的条件语句
4. 在插入或删除数据时，整型自增主键会在叶子节点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID很容易会出现这样的情况，B+ Tree本质上是平衡树，为了维持平衡，可能会进行结构的重构，消耗更多的时间

推荐使用UUID而不是整型自增作为主键的场景

1. UUID作为主键在分库分表、合并数据时比较有用
2. 但在海量数据面前，UUID性能比整型自增主键性能差

7. 聚集索引和非聚集索引在查询数据的时候有什么区别

1. 聚集索引树的叶子节点就是我们要查找的整行数据了
2. 非聚集索引树的叶子节点是当前索引和域值为主键的值组成的，查找过程中可能存在回表(当需要查找的数据不在节点上，需要通过主键的值，在聚集索引树再查找一遍)操作
3. 并非所有非聚集索引都需要回表，这是因为，如果要查找数据在索引上就能找到，就不需要回表，而是直接得到数据

8. 在创建联合索引的时候，联合索引多个字段的顺序我们该如何选择

1. 根据最左前缀匹配原则，我们需要把查询概率较高的字段摆在最左端

9. 查询优化器

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪一种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1. 根据搜索条件，找出所有可能使用的所有
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个