索引学习思路导向（基于MySQL）

索引 学习思路导向

很久没有写过博客，最近正好在做MySQL相关的工作，而索引便是数据库中非常重要知识点，今天就在这里给大家建议一些索引的学习思路，在面试的时候也会很有用处，面试的时候数据库会是面试的一大重点，可以说是“逢面必问”，而索引是数据库的一大重点，在数据库优化过程中起到很大的作用。所以在面试的时候如果问到索引，很多面试官会按照我给的思路来向你提问，当然，如果没有这样，你也可以将话题向这些问题上面转移。话不多说，咱们进入主题。

1.索引是什么？即索引的本质？

当面试官叫你谈谈索引的时候，第一点你当然要讲索引是什么，本质是什么。
索引的本质：通过不断地缩小想要获取数据地饭为来筛选出最终想要的结果，同时把随机的事件变成顺序的事件。也就相当于我们日常经常说的把索引比作一本书的“目录”。

2.索引的底层实现是什么？即数据结构？

当你简单讲完索引的本质概念，面试官肯定会问道索引的数据结构，下面开始吧！
　　MySQL数据库中索引数据结构主要是两种，一种是Hash索引，另一种是B+Tree索引。Hash索引的底层是Hash表，是key–value形式的键值对，允许多个key对应相同的value，但不允许一个key对应多个value。
　　B+Tree索引底层是B+Tree，B+Tree是一种经典的数据结构，由平衡树和二叉查找树结合产生，它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有的记录节点都是按键值大小顺序存放（左子节点小于父节点、父节点小于右子节点）在同一层的叶节点中，叶节点间用指针相连，构成双向循环链表，非叶节点（根节点、枝节点）只存放键值，不存放实际数据。
说到这里往往面试官会问二者的区别：
Hash索引是key–value键值对的形式，适合等值查询，查询单条快。
特点如下：
1.范围查询效率极低，需要进行全表扫描；
2.没办法利用索引完成排序；
3.不支持多列联合索引的最左匹配原则；（如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题）
B+Tree索引是一种多路平衡查询树，所以他的节点都是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。适合范围查询。

3.存储引擎的索引结构

面试官极有可能将数据库的两大知识点在这里连接起来，问你数据库的存储引擎采用哪些索引？
InnoDB引擎的默认采用的索引结构就是B+Tree索引
　　不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 Btree、Hash 等索引，不支持Full-text 索引；
MyISAM 不支持事务，支持表级别锁定，支持 Btree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 Btree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 Btree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 Btree、Hash、Full-text 等索引；

4.索引的分类

这里可以有两种分法：
一种是分为主索引和辅助索引（物理分类）
顾名思义，主键索引就是主索引，其他索引就是辅助索引。结构基本相同，但主索引要求key是唯一的，辅助索引的key可以重复。
另一种是分为主键索引，唯一索引，普通索引，联合索引，全文索引。

5.聚集索引和非聚集索引

当讲到B+Tree的时候聚集索引和非聚集索引就会被引导而出，下面也来说一说。
面试官可能会问你B+Tree树的叶子节点上可以存放哪些东西？到这里你基本可以认为他想问你的就是聚集索引和非聚集索引了。
聚集索引：
　　叶子节点存储数据（这里存的是整行的数据，注意是所有列都存了），对应就是主键索引。聚集索引按主键搜索一次即可，因为聚集索引的key–value存储结构中value中存的就是数据，直接一次就能找到，这样就使得按主键的搜索是十分高效的。
非聚集索引：
　　叶子节点存储主键的值，对应的是辅助索引。存储主键的值就会“回表”查询得到数据，这里经过了两次查询。这里的两次是首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
面试官可能会问非聚集索引一定会查询两次吗？
　　当然不是，这里就是为了引出覆盖索引的概念。覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

6.创建索引的考虑因素

查询效率较高的字段，经常作为where条件的字段设置为索引。

7.联合索引

讲到联合索引目的就是为了引出联合索引的最左前缀匹配原则
最左前缀匹配原则：
　　最左优先，在检索数据时从联合索引的最左边开始匹配。例如创建了联合索引（key1,key2,key3）就相当于创建了三个索引，（key1），（key1,key2），（key1,key2,key3）。
注意：（key1,key3）这种形式的索引也是用到复合索引的，不过用到的是（key1）。

8.索引下推（Index Condition Pushdown）

面试官极有可能通过询问你线上MySQL版本来引出索引下推。因为MySQL5.6对索引做出了优化，而这个就是索引下推。
　　默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

9.怎样排查索引有没有效

可以通过explain查看sql语句的执行计划，通过执行计划来分析索引使用情况
explain的使用可以参考我的另一篇文章《SQL优化-explain的用法（实例解析）》

疑问解答！！！！！

1.问：为什么索引结构默认使用B+Tree，而不是hash，二叉树，红黑树？
（1）hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
（2）二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
（3）红黑树：树的高度随着数据量增加而增加，IO代价高。（每个节点有专门的存储位存储表示节点颜色，是自平衡变种二叉查找树，参照2-3树理解。）
（4）B Tree：B树的特点是不仅叶子节点能存储数据，非叶子节点也能存储数据，这样就压缩了指针的空间位置，导致指针位置可能不够用，然后就是存储的数据量有限，所以不用B树。

2.问：为什么官方建议使用自增长主键作为索引？
　　结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。
　　
3.问：为什么使用B+tree这种结构?
　　B+tree是B树的一个变种，B树特点是所有节点有data域，而B+树只有最底层叶子节点有data域。
　　一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
　　为了减少查找过程中磁盘I/O的存取次数，磁盘按需读取，要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。并把B-tree中的m值设的非常大，就会让树的高度降低，有利于一次完全载入。

4.问：一颗B+树有几层呢？
答：1-3层，约两千万行数据。

（1）首先需要了解各种存储单元大小：
1）在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512 字节；
2）文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k；
3）InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K（源码中默认的值，可通过参数设置）；
图形对比如下：

假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题，因为我们不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。所以人们想了一个办法，用 B+树的方式组织这些数据。（这也是为什么会采用B+树这种数据结构当索引结构了，因为他是多路平衡树（一个节点对应多个节点，并且节点之间有序（左节点小于根节点，根节点小于右节点），同时只有叶子节点才能存放数据，非叶子节点有足够空间存放指针等））

（2）通常一棵 B+ 树可以存放多少行数据？
这里我们先假设 B+ 树高为 2，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：根节点指针数 * 单个叶子节点记录行数。
上文我们已经说明单个叶子节点（页）中的记录数 =16K/1K=16。（这里假设一行记录的数据大小为 1k，实际上现在很多互联网业务数据记录大小通常就是 1K 左右）。
那么现在我们需要计算出非叶子节点能存放多少指针？
其实这也很好算，我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170。（2^14=16384）
那么可以算出一棵高度为 2 的 B+ 树，能存放 117016=18720 条这样的数据记录。
根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 11701170*16=21902400 条这样的记录（2000万条左右）。
所以在 InnoDB 中 B+ 树高度一般为 1-3 层，它就能满足千万级的数据存储。
在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

（3）为什么是1-3层？怎么不能是四层呢？
暂时不是很理解，后续补充。

5.问：聚集索引以及非聚集索引？
这部分内容参考自：
https://blog.csdn.net/riemann_/article/details/90324846

区别：
　　聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。
1.聚集索引:
　　物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
2.非聚集索引:
　　物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
　　索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
优势与缺点：
　　聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

（1）聚集索引的约束是唯一性，是否要求字段也是唯一的呢？
　　分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。
　　结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。

（2）为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？　
　　粗一看，这还真是和聚集索引的约束相背，但实际情况真可以创建聚集索引。
分析其原因是：如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

（3）是不是聚集索引就一定要比非聚集索引性能优呢？　
　　如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？
　　答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。

（4）在数据库中通过什么描述聚集索引与非聚集索引的？　
　　索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

（5）在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
　　有了上面第四点的认识，我们分析这个问题就有把握了，在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况：聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行（索引的存储空间比实际数据要少），这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

6问：为什么MySQL要用B+树而不是用跳表？
（1）B+树比跳表的检索效率更高，数据分布更均匀；
（2）跳表是通过二路分治的方式实现logN,B+树是通过多路分治的方式实现logN;
（3）当数据表的数据足够多的时候，B+树的根节点到任何一块叶子节点的路径是固定的，而跳表的头节点到目标节点的路径是不固定的。所以检索的value越大，跳表的路劲就越深，磁盘的IO次数就越多；
（4）B+树的所有叶子节点构成了一个双向循环链表，每一块叶子节点可以存储一条或者多条数据。这种结构不管是一条记录、还是多条记录查询都能节省磁盘IO；跳表的每一个节点只存储一条记录，对于一条记录的查询是比较节省磁盘io，对于多条记录的查询，跳表的磁盘IO次数会比B+树要多。
拓展：redis为何单线程效率还这么高？为何使用跳表不使用B+树做索引？

梧高凤必至，花香蝶自来！