关系型数据库相关的大部分问题，都要往如何减少IO次数以及如何减少随机IO次数上想，这也是关系数据库设计时考虑的一个目标。

为什么索引使用B+树

实际上不管你使用什么结构，B树、B+树、二叉树、AVL、跳表啥的，只要是为快速检索设计的数据结构，你都可以建立索引。问题是，关系型数据库设计面临的一个挑战就是，它要将数据落盘，持久化到硬盘中，落盘就会产生IO，有IO就会拖慢效率，所以选择B+树是为了尽量减少维护索引过程中的IO访问次数。记住，关系数据库设计的一个目标就是尽最大努力在符合RDBMS要求的情况下减少IO次数。

二叉树的一个节点只保存一个值和两个到其它节点的链接，假如我们把一次节点访问看作一次IO操作，那么二叉树会带来挺多IO，即使是平衡二叉树，最好也需要以2为底的对数级别次IO。所以，二叉树深度太大，所带来的IO次数太多了

B树和B+树差不多，它们一个节点存储很多个数据，所以十分扁平，深度低，所以IO次数相对较少，那为啥还是选择了B+树？

1. B+树的叶子节点连成有序链表，应对范围查询时可以直接找到范围的边界，之后向后遍历链表
2. 从工程的角度来讲，我们更倾向于一个稳定的效率，而不是某一次贼快，某一次贼慢，B+树因为所有数据都存在叶子节点，所以无论你访问哪条数据，它们的深度都相同，也就是访问它们要经历相同次IO，效率都差不多，而B树的非叶子节点也是数据，这就导致不同层次的数据访问时间有差异。

同时还有一个问题就是，读取磁盘信息都是按照块来读取的，一次读取一块，使用B树或B+树可以很方便的让一个块作为一个节点，而二叉树就很难这样做。

关键：通过更低的树高减少IO、支持范围查询、方便以块结构组织

聚簇索引和非聚簇索引的区别

聚簇索引就是索引保存了完整的行数据，而非聚簇索引保存的是索引列还有主键id。使用聚簇索引的表又叫索引组织表。

对于聚簇索引的查询，查到了之后就没有其它动作了，因为索引即完整数据，而对于非聚簇索引的查询，还要拿到主键id去回原表再查，这会带来多次额外的IO。

有一种使用索引进行检索时比较好的行为，叫“覆盖索引”，就是说你的查询语句，不管是条件还是返回列都完整的包含在索引数据中，那么此时就无需再回原表查询了，因为要的数据都有了。比如索引列是(A, B, C)，此时，如果你整条查询中只用到这三列，那么无需回原表，还有一种情况是索引列是(A, B, C, id)，这种情况也一样，因为覆盖索引中包含主键id。

关键：聚簇索引即行，非聚簇回原表，带出覆盖索引

索引什么时候会失效

索引不会失效，只能是由于方法不对导致没用上。

索引不过是为了快速查询数据建立的一种树状结构嘛，你可以把它想成书籍的目录，目录即索引。如果你来查询第一章第二节中的第一分节是啥，你可以用1.2.1来快速的在目录中定位，而如果你查询的时候把你要查的字符串给截断了，变成.1，那索引就用不上了，谁知道你要查的是哪章哪节里的哪小节呢？

如下情况会导致索引用不上：

1. 对索引列进行一些计算，并且这种计算编译器无法优化掉，比如截取字串
2. 不符合最左前缀原则，比如搜索like '%.1'

关键：错误的使用导致索引结构对查询不起作用

最左前缀匹配原则是什么？

说到最左前缀匹配原则，就要说数据库的索引结构。索引无非就是B+树，我们假设现在索引是人的姓名字段，这个索引该如何构建呢？肯定是要有一个字符串的比较方法，能比较出两个字符串谁大谁小，比如说字典序，然后大的往左放，小的往右放，最后，B+树的所有叶子节点连成姓名的有序链表。

这时，你能不能查姓李的？当然能！在查询B+树时，只对字符串的第一个字符进行比较，你就能得到第一个姓李的人所在的叶子节点的位置，然后向后遍历链表即可。

你能不能查叫李明x的？当然能！和上面一样，只不过这次比较头两个字符了。

你能不能查x华？抱歉不能了，因为B+树构建时是以完整字符串的字典序排序的，可能有叫李华的，有叫王华的，它们散落在B+树底层链表的不同位置，所以B+树帮不上忙。

这就是最左前缀原则，说白了就是你不能从索引列字段的中间开始比较，你必须从左边开始，因为索引构建时就是按照这个顺序构建的。除了字符串，多列索引时也是一样的道理。

关键：从B+树结构解析

索引下推了解吗？

索引下推即ICP优化机制，是由MySQL特殊的服务器-存储引擎层次架构所带来的问题而引出的一种应对办法。

举个例子吧，比如查询条件为a = 12 AND b like '%abc'，当前有索引(a, b)，MySQL会认为b不符合最左前缀原则，也用不上，就不用它进行索引，然后它这样执行：

1. 查找到第一个a=12的
2. 返回到主键id到MySQL服务器层
3. 根据主键id去fetch原表 返回整行到服务器层
4. 根据原表中的b字段应用WHERE条件
5. 如果条件满足，放到结果集，否则直接扔掉

这里有两个问题：

1. 首先，索引中有足够的数据不用fetch原表
2. 其次，索引中有足够的数据直接在索引中过滤掉不符合条件的行，甚至不用返回应用层

所以ICP机制就是解决这种问题，当搜索条件完全覆盖索引时，即使在快速检索上该条件可能没什么帮助，但为了避免服务器和存储引擎层的多次无用数据交互和fetch原表，把索引条件下放到存储引擎层，从引擎这就过滤掉不符合规则的数据。

关键：MySQL架构设计的锅，解决额外fetch原表以及服务器层和存储引擎层的数据交互

为什么建议使用自增主键

还是从索引的原理上来考虑。索引是一个树形结构，在B+树中，节点中的内容有序并且所有叶子节点连成一个有序链表。

假如一个节点只能容纳4个数据，这时插入6会发生什么问题？

7会向后面的节点移动，并且后面节点的8和11都要向后移动，这就是额外的IO操作！

而如果主键是自增的，那插入的新值永远比所有旧值大，就不会出现上面的频繁移动的问题了。

我看网上有的帖子写什么可以避免节点分裂，也许他们想表达的是上面的这个意思，但表达的不准确。其实不管你怎么怎么插入，顺序还是不顺序，只要B+树满了，它就会分裂。平衡树都是靠分裂行为来增加高度的，如果使用自增主键B+树就不会分裂了，那索引就成一个链表了...

再有就是自增主键就一个整数值啊，足够小，普通索引的索引项也小了，因为普通索引要带一个主键id嘛。

同时也有些大厂的设计规范中要求不用自增主键的，甚至论坛上总有人因为这个吵起来，这就和吵哪个语言世界第一一样，没必要，非要争就php就好了。技术是为业务服务的，如果你的业务具有什么分布式、高并发写的特性，那么可以考虑不使用自增主键，避免所有写入都在争用这个生成主键时的锁，这个id变成了热数据。

关键：规避数据移动，整数值较小，有些时候也会禁止使用

索引有什么缺点

索引本质上来说是一个加快查询的，对我们的性能有正向作用的东西，但是维护索引是有开销的，你插入一条数据时，所有索引都要发生改变，所以索引可不是越多越好，最主要的是找到一个平衡点。

其实对于一些查询需求，我们完全可以使用其它中间件来实现，比如ES搜索引擎，而对于关系数据库的索引，只在必要字段上建立就行了。

关键：索引维护有开销