Mysql数据库索引（面试常问）

索引类型

一. 聚簇索引

索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别），可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行回表查询。因此在查询方面，聚集索引的速度往往会更占优势

二. 非聚簇索引（二级索引，辅助索引）

叶子节点仍为索引节点，只是多了一个指针（主键值）指向对应数据节点，需要回表。非聚簇索引按不同条件又可分为普通索引，唯一索引，单列索引，联合索引，前缀索引等

回表

由于非聚簇索引只存储了索引列的值和聚簇索引值（主键值），所以需要先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据的过程叫做回表，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。比如在user表的name字段上面简历了索引，非聚簇索引叶子节点存储的就是name和id的值，当我们执行sql: select age, name from user where name="张三"的时候，因为我们需要拿到age和name的值，但是叶子指点只有name和id的值，所以我们就要拿着id通过聚簇索引找到对应的数据行以获取age的值。这个过程就叫做回表。

覆盖索引

上面说到当我们执行sql: select age, name from user where name="张三"的时候，因为叶子节点中只有name,id的值，所以需要进行回表，这时如果我们给name,age建立了一个联合索引呢？这时候叶子节点存储的就是name,age,id的值，需要查询的name和age的值都有了，自然就不需要回表了。这就是覆盖索引

尽量建立联合索引而非多个单列索引

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率

最左匹配原则

MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。a = 1 and b = 2 and c > 3 and d = 4顺序可以任意

索引下推

mysql5.6推出，默认开启

索引下推简而言之就是在复合索引由于某些条件(比如 like %aa)失效的情况下，当存在失效的过滤字段在索引覆盖范围内，使用比较的方式在不回表的情况下进一步缩小查询的范围。

其实就是对索引失效的进一步修复，属于最左前缀索引原则的一个意外情况。

前缀索引

我们存在邮箱作为用户名的情况，每个人的邮箱都是不一样的，那我们是不是可以在邮箱上建立索引，但是邮箱这么长，我们怎么去建立索引呢？

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串

覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是www.aobing@mogu.com一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

索引下推简而言之就是在复合索引由于某些条件(比如 like %aa)失效的情况下，当存在失效的过滤字段在索引覆盖范围内，使用比较的方式在不回表的情况下进一步缩小查询的范围。

其实就是对索引失效的进一步修复，属于最左前缀索引原则的一个意外情况。

普通索引和唯一索引如何选择？（谈谈change buffer）

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，这里先介绍一下 change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。

而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

了解完change buffer，结合我们的场景，假设现在正准备插入一条(4, ‘李四’),我们大致可以分为以下两种情况：

1.这个记录要更新的目标页在内存中：

对于唯一索引，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。
此时，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。可以忽略

2.这个记录要更新的目标页不在内存中

对于唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引，则是将更新记录在 change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

有个 DBA 的同学反馈说，他负责的某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。探究其原因后，发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。

change buffer 的使用场景

通过上面的分析，我们已经清楚了使用 change buffer 对更新过程的加速作用，也清楚了 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。那么，现在另一个问题就是：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，这种业务模式下，change buffer 反而起到了副作用。

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

小结：

1.从查询过程来说，唯一索引和普通索引几乎无差别
2.从更新过程来说，由于change buffer的加成，当要更新的目标页不在内存中时，普通索引因为减少随机 IO 的访问，对更新性能的提升很明显
3.大部分场景普通索引都优于唯一索引（在业务代码保证唯一性的前提下），但如果业务模式是更新完后马上查询，此时普通索引+change buffer会起副作用

索引一定是最优的吗（索引会走错吗？）

会! MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。

至于MySQL索引可能走错也很好理解，如果走A索引要扫描100行，B所有只要20行，但是他可能选择走A索引，你可能会想MySQL是不是有病啊，其实不是的。

一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。

他发现走A索引不需要回表，没有额外的开销，所有他选错了。

如果是上面的统计信息错了，那简单，我们用analyze table tablename 就可以重新统计索引信息了，所以在实践中，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

还有一个方法就是force index强制走正确的索引，或者优化SQL，最后实在不行，可以新建索引，或者删掉错误的索引。

Mysql索引为什么要用B+树，相比B树有什么优势？

Hash不支持范围查询（mysql也支持hash索引），二叉树树高很高，只有B树跟B+有的一比。

B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。

而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

提高了的原因也无非是会有指针指向下一个节点的叶子节点。即叶子节点是有序的。

b+树相比于b树的查询优势：

• b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
• b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
• 对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历