MySQL索引

索引是数据库中帮助快速查询数据的一种数据结构，它将数据表中的数据放在索引文件中，以便于快速查询。索引存在磁盘中，会占据物理空间，因此不恰当的索引会影响性能。MySQL索引存储在存储引擎层。

为什么要使用索引：

1. 大大减少存储引擎需要扫描的数据量：索引的大小通常远小于数据的大小，以InnoDB为例，引擎发生一次I/O最小的存取单位是页。 页内存储信息越多则读取效率越快，默认一页是16K。 一页内存放很多索引，通过索引查找则读取页的数量就越少.

2. 索引可以帮助我们进行排序以避免使用临时表：B-Tree索引按键值的顺序存放，可以排序。避免了使用临时表排序的I/O消耗同时提供MySQL 处理能力.

3. 索引可以把随机I/O变为顺序I/O：B-Tree索引按键值的顺序存放，可以排序。而数据行的物理地址是随机分布的，使用索引可以 将随机I/O变为顺序I/O。

索引是否越多越好吗？

1. 索引会增加写操作的成本：数据增删改时，需要同时对相关索引进行维护，索引越多修改数据所需要时间就越长。InnoDB针对这种情况，引入了插入缓存，将多次插入合并为一次。
2. 太多的索引会增加查询优化器的选择时间：MySQL查询优化器会根据索引的统计信息和查询的条件来为查询选择合适的索引，如果对一个查询有很多个索引可以使用，则会增加查询优化器对于查询的分析时间.

B-Tree：

平衡多路查找树，B-Tree是为磁盘等外存储设备设计的一种平衡查找树，学习前需先了解磁盘相关知识：

• 系统从磁盘读取数据到内存是以磁盘块（block）为基本单位，位于同一个磁盘块的数据会被一次性读取出来
• InnoDB存储引擎中其磁盘管理的最小单位是页（Page），默认大小16KB，通过参数innodb_page_size设置（4K/8K/16K），通过show variables like 'innodb_page_size'进行查看。
• 一页包含多个块：系统中一个block的存储空间没有这么大，因此InnoDB每次申请磁盘空间都会是若干的地址连续的block来达到页的大小，InnoDB把磁盘数据读入内存是以页为单位。
• 查询数据时，通过索引来定位数据页，再将数据所在页读入内存（如果索引定位数据为6，则把6所在页都读入到内存），MySQL通过条件在内存中筛选数据。

B-Tree的结构可以让系统高效的找到数据所在的磁盘块。B-Tree的数据存储方式是一个二元数组[key,data]。key为表中的主键值，data为一行记录中除主键外的数据。

B+Tree：

InnoDB引擎的索引实现利用了B+Tree，B+Tree是在B-Tree基础上的优化，更适用于外存储索引结构。B-Tree的每个节点不仅包含key，还有data值，但每个页的大小有限，如果data数据较大将会导致每个节点（一个页）能存储的key数量很小，当存储数量大时会导致B-Tree深度较大，增大磁盘I/O次数，影响查询效率.

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key，这样可以大大加大每个节点存储的key值数，降低B-Tree的深度.

B+树的这种结构不仅能减少深度，最大的一个好处是：无论哪个数据，查询的效率都比较稳定，一定会查到最底下的叶子节点；而B树有些关键词可能查到第一层就返回，有些查到最后一层叶子节点才返回，这带来的不稳定性会增加sql优化时的难度。

哈希索引：

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，如果多个列哈希码一致，则以链表的方式存放多个记录指针到同一个哈希条目中，哈希索引会将所有的哈希码存在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引示意图：

示意图解释：

• keys：代表创建索引的列值A、B、C、D
• buckets：哈希结构，计算出来的hash值和对应指针存放的链表
• entries：具体的数据行
• 创建哈希索引后，通过特定算法为每个键值计算出一个哈希码，有哈希冲突时，如A/C计算出来的都是101，找到101在hash表中存储的位置，然后找到对应存储数据的物理位置，这个位置对应着两条数据A和C，然后再次遍历这两条数据，找到需要的数据。这就是发生冲突导致索引效率低的原因。

最左前缀原则：B+树的索引结构，可以利用索引的最左前缀，来定位记录。索引项会按照索引定义里出现的字段顺序排序。索引示意图如下：

需要找张三时，快速定位到ID4，并向后遍历得到所有需要的结果。只要满足最左前缀，就可以利用索引来加速检索。

使用查询时遵循mysql联合索引的"最左前缀"：

• 按索引最左列开始查询（多列索引），例如index（'c1','c2','c3），当where c2 = 'aaa'时不会使用索引，当where c2 ='aaa' and c3 ='bbb'时不会使用索引。
• 查询中某个列有范围查询，则其右边所有的列都无法使用查询（多列查询），当where c1 ='aaa' and c2 like 'aa%' and c3 = 'bbb' ，该查询只会使用索引中的前两列，因为like是范围查询。　　
• 不能跳过某个字段来查询，这样利用不到索引。比如 where c1 > 'aaa' and c2 = 'bbb' and c3= 'ccc'，这样不会使用组合索引，因为第一个索引字段出现了范围查找。

聚簇索引：数据库中的B+Tree索引可以分为聚集索引（主键索引）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储对应主键值和索引键的值。当通过辅助索引来查询非索引列时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。（回表）

索引覆盖：覆盖索引是指一个查询的所有字段都包含在索引中，数据库引擎可以直接从索引中获取数据，而无需回表（即不需要再去主键索引或数据行中查找）

索引下推：

• 允许存储引擎在索引遍历时提前过滤不符合条件的记录，减少回表次数。
• 最左前缀可以用于索引中定位记录，那么不符合最左前缀的部分会怎么处理呢。以上述索引为例，查询表中名字第一个字是张，年龄是10岁的所有人。
• 搜索索引树时，会找到满足第一个条件的记录ID3：索引下推优化，通过索引拿到的数据，先在内存里通过索引里面的其他字段过滤掉一部分，再回表，减少回表次数。比如会滤掉（张六,30）。然后只需要回表ID4和ID5。 执行计划表现：Using index condition