索引的分类

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引、二级索引。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引（复合索引）。

MySQL有哪些索引

索引类型（按数据结构）

MySQL支持B+树索引、哈希索引（只用Memory支持）、全文索引这三种索引类型。我比较常用的是B+树索引，因为它是InnodB引擎默认使用的索引类型，支持排序、分组、范围查询、模糊查询等功能。

索引（按字段）

MySQL有主键索引、唯一索引、普通索引、前缀索引、联合索引这几种索引。

Innodb引擎会要求每一张数据库表都必须要有一个主键索引，比如表里的id字段就是主键索引。

然后针对查询比较频繁的字段，我们可以对这个字段建立普通索引，如果是多个字段的话，可以考虑建立联合索引，利用索引覆盖的特性提高查询效率。

对于长文本、字符串等类型的字段，比如文章标题、商品名称等，我们可以只对这些字段的前缀部分建立索引，也就是建立前缀索引，这样可以减少索引的存储空间。

普通索引和唯一索引怎么选

普通索引列的值是可以重复的，而唯一索引列的值是必须唯一的，当我们对唯一索引插入了一条重复的值，会因为唯一性约束而报错。

查询过程：

• 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

更新过程：

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

但，这不是我们关注的重点。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。（因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好）

为什么InnoDB 选择 B+tree 作为索引的数据结构？

1、B+Tree vs B Tree

• B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点，并且非叶子节点存储数据，导致内存可能无法放下所有非叶子节点，查询非叶子节点还可能进行I/O。

• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

• 另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

2、B+Tree vs 二叉树（平衡二叉树、红黑树）

• B+树的节点相对较大，能够存储更多的索引项，从而减少了树的高度，降低了访问磁盘的次数(矮胖)
• B+树的节点相对较大，一个节点可以存储多个关键字，这意味着在进行磁盘IO时，可以一次性读取更多的索引项到内存中，利用了局部性原理，减少了IO的开销（局部性）
• B+树的节点相对较大，树的高度低，节点分裂和合并等平衡操作相对较少，在插入和删除操作时，B+树通常能够更加高效地维持树的平衡性，而不需要频繁地进行平衡调整操作
• B+树的叶子节点形成了一个有序链表，便于范围查询

3、B+Tree vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

4、B+Tree vs 跳表

• B+ 树是一种磁盘友好的数据结构,它将数据组织成块状结构,方便在磁盘上进行顺序读写。跳表虽然在内存中表现良好,但它的节点分散在内存中,不利于磁盘 I/O 操作。
• 跳表和B+树相比，跳表在极端情况下会退化为链表（删除后一层只剩一个节点，随机生成层级失败），平衡性差，而数据库查询需要一个可预期的查询时间，并且跳表需要更多的内存。

聚集索引和⾮聚集索引的区别？⾮聚集索引⼀定回表查询吗?

聚簇索引和非聚簇索（二级索引）最主要的区别是B+树叶子节点存放的内容不同：

• 聚簇索引的B+树叶子节点存放的是主键值+完整的记录；
• 非聚簇索引的B+树叶子节点存放的是索引值+主键值；

在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到（主键值，二级索引值），那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表

insert操作对B+树结构的改变是怎么样的/为什么主键最好是自增的

B+树的数据都是有序的，所以：

• 如果我们使用主键是顺序递增，那么每次插入的新数据就会顺序插入到叶子节点最右边的节点里，如果该页面满了，就会自动开辟一个新页面，将新数据插入到新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
• 如果我们使用主键不是顺序递增，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这时候为了保证B+树的有序性，要移动其它数据来满足新数据的插入。如果该页面满了，就发生页分裂，这时候要从一个页面复制数据到另外一个页面，目的是保证后一个数据页中的所有行主键值比前一个数据页中主键值大，页分裂可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

所以，我们在设计主键的时候，最好采用自增的方式，或者顺序递增主键值。

假如一张表有两干万的数据，B+树的高度是多少？怎么算的

• 如果一行记录的数据大小为1KB，数据页大小为16KB，去掉头部信息大概15KB存储数据，那么单个叶子节点可以存的记录数 =15KB/1KB =15

• 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，，15KB/14B = 1071，也就是1071叉树
• 如果是2层B+树，可以存储1071 * 15= 16,065，如果是3层B+树可以存储1071 * 1071 * 15 = 17,205,615大概两千万数据

主键怎么设置？假如不设置会怎么样

• 在创建表的时候，对id列设置为PRIMARY KEY,那么id列就是主键索引了；
• 如果没有主键，就选泽第一个不包含NULL值的唯一列作为聚簇索引的索引键；
• 如果这个条件也没有达成的话InnoDB将自动生成一个隐式rowid列作为聚簇索引的索引键。

为什么要建索引

索引是提高查询效率的一种数据结构，索引是数据的目录

如果没有建立索引，我们查询数据的话，搜索时间复杂度是O(n)，这样的查询效率还是比较低的，为了提高查询效率，我们可以建立索引。

建立了索引后数据都会按照顺序存储，这时候我们可以利用类似二分查找的方式快速查找数据，B+树索引是多叉树，磁盘IO少，搜索时间复杂度是O(logdN),这样就提高了查询速度，除此之外还可以避免外部排序（order by a，如果a不是索引就得重新排序）等问题，以及将随机I/O变为顺序I/O。

索引这么多优点，为什么不对表中的每⼀个列创建⼀个索引呢？(使⽤索引⼀定能提⾼查 询性能吗?)

索引最大的好处是提高查询速度，但是索引也是有缺点（空间、时间、维护）的，比如：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候适用索引？

唯一、常查、排序

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的

什么时候不需要创建索引？

不查不排、重复数据、数据量小、常更新

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据；
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

有什么优化索引的方法？

几种常见优化索引的方法：

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 索引最好设置为 NOT NULL；
• 防止索引失效；

前缀索引优化

使用某个字段中字符串的前几个字符建立索引，减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。

不过，前缀索引有一定的局限性，例如：

• order by 就无法使用前缀索引；
• 无法把前缀索引用作覆盖索引；

覆盖索引优化

对于只需要查询几个字段数据的SQL来说，我们可以对这些字段建立联合索引，这样查询方式就变成了覆盖索引，查询的数据能在二级索引里查询的到，不需要通过主键索引查询获得，可以避免回表的操作

主键索引最好是自增的

见(insert操作对B+树结构的改变是怎么样的/为什么主键最好是自增的))

索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：（优化器选择复杂，NULL有NULL值表占用空间）

• 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
• 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表，如下图的紫色部分

防止索引失效

发生索引失效的情况：

左或者左右模糊匹配、联合没有左匹配，函数，or

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效；
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

建立了索引，查询的时候一定会用到索引吗

• 索引失效：左或者左右模糊匹配、联合没有左匹配，函数，or

• 优化器是基于成本考虑来选择查询的方式，在使用二级索引进行查询的时候，优化器会计算回表的成本和全表扫描的成本，如果回表的代价太高，优化器会选择不走索引，而是走全表扫描。

  • Cost = Server Cost + Engine Cost = CPU Cost + IO Cost
  • 其中，CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成；
  • IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。

如果我定义了一个varchar类型的日期字段，并且有一个数据是'20230922'，如果这个日期字段上有索引，那如果我查询的where条件是where time=20230922不加单引号，还会命中索引吗？为什么？

不会命中

因为MySQL在遇到字符串与数字进行比较的时候，会发生隐式类型转换，会将字符串的对象转为数字，这个转换的过程实际上会涉及到函数。这个查询因为没有加单引号，日期字段是字符串，会发生隐式转换，那么发生隐式类型转换的时候，就会作用在日期这个索引字段上，对索引进行函数计算的话，是会发生索引失效的。

select * from t_user where CAST(date AS signed int) = 20230922;

MySQL最新版本解决了索引失效的哪些情况了吗

• MySQL8.0可以给字段增加函数索引，这个新特性可以解决对索引使用函数的时候，索引失效的问题。

• 还有一个新特性是索引跳跃式扫描，5.7版本之前，使用联合索引的时候，如果不满足最左匹配原测，就会发生索 引失效，而8.0出了索引跳跃式扫描特性之后，即使没有遵循最左匹配原则，依然可以使用联合索引。

最左前缀匹配原则

假设有一个(a,b,c)联合索引，它的存储顺序是先按a排序，在a相同的情况再按b排序，在b相同的情况再按c排序。由于这个的特性，在使用联合索引时，存在最左匹配原则，具体的规则：

• MySQL会从联合索引从最左边的索引列开始匹配查询条件，然后依次从从左到右的顺序匹配，如果查询条件没有使用到某个列，那么该列右边的所有列都无法使用走索引。
• 当查询条件中使用了某个列，但是该列的值包含范围查询，范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。所以，我们在使用联合索引的时候，要遵守最左匹配原则，否则可能会出现部分索引字段走不了索引。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；
• where a >= 1 and b = 2（a＝1时用到了）

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

• where b=2；

• where b=2 and c=3；

• where a > 1 and b = 2 (a用到，b没有用到)

建立联合索引有什么需要注意的

符合最左匹配原则

最好把区分度比较大的字段放在联合索引最左侧，有助于提高索引的过滤效果，比如UUID这类字段就比较适合做索引或排在联合索引列的靠前的位置。

如果区分度很低的字段放在了联合索引最左侧，有可能会导致查询优化器会选择全表扫描，而不走索引了。

联合索引(a,b,c)，下面的查询语句会不会走索引？

• select from T where a=1 and b=2 and c=3; abc
• select from T where a=1 and b>2 and c=3; ab
• select from T where c=1 and a=2 and b=3; abc
• select from T where a=2 and c=3; a
• select from T where b=2 and c=3; nulll
• select (a,b)from T where a=1 and b>2 ab，覆盖查询

where a>1 and b=2 and c<3怎么建立索引

创建(bac)联合索引或者(bca)联合索引，因为这两种联合索引都可以有2个字段走索引。剩下一个字段可以索引下推

where a=? and b=? order by c怎么建立索引

这里有order by排序，尽量要用索引来避免额外排序的操作，可以考虑建立(a,b,c)联合索引，因为c有序的前提是建立在a=？and b=?的场景下，刚好符合这个查询条件，这样c就不需要额外排序了，避免using filesort，天然利用了索的有序性。

where a>100 and b=100 and c=123 order by d怎么建立联合索引

我觉得建立bcda顺序的联合索引比较好，b和c字段都能走索引，而且d能利用索引有序性，避免filesort，a可以使用索引下推

select id,name from XX where age>10 and name like'xx%',有联合索引(name,age),说一下查询过程

联合索引的顺序是先name，再age，结构上是先根据name排序，name相等的情况下再根据age排序。

所以优化器需要先匹配name，name这时候是右模糊查询，并不会发生索引失效，所以这条sql是能走联合索引的，具体的话，只有name能走索引，这是因为由于name右模糊查询后，age字段的值并不是有序的，因此age无法走索引，但是age可以进行索引下推。

最后查询的字段是id和name，这两个字段都能在联合索引上查找到，所以不需要回表，是索引覆盖查询。

where id NOT IN(?,?,?)会走索引吗？

要看查询成本，如果走某个索引花费的随机I/O从聚簇索引顺序查（顺序I/0)的成本都还要高，那还不如直接去全表扫描。

举例：id字段只包含3个值，1、2、3, 3只有几行，而1、2各有100w行，如果查询条件是N0T IN(1,2)会走索引，如果查询条件是N0T IN(3)不会走索引。

如果查询条件中包含索引列和非索引列，MySQL的具体查询流程是什么样的

查询过程先按索引去二级索引B+树查，然后拿到主键id，回表到主键索引B+树再过滤非索引列，查询过程会查2个B+树，涉及回表的过程。

索引下推

索引下推能够减少二级索引在查询时的回表操作，提高查间的效率，因为它将Sever层部分负责的事情，交给存储引擎层去处理了。

举个例子，联合索引(a,b,c)，查询条件为a=? and c=?的时候，由于联合索引的最左匹配原则，c是无法走索引的，在没有索引下推机制之前，查询语句走二级索引的时候，需要回表读取c的值，然后在server层进行过滤，有了索引下推机制后，即使c无法走索引，但是由于c在二级索引里，那么将过滤c的工作从server层下推到存储引擎层，这样直接在二级索引里过滤满足c条件的记录，减少了回表的次数。

如何查看某条 SQL 语句是否⽤到了索引

在这个查询前加上 EXPLAIN 来查看执行计划：

EXPLAIN SELECT * FROM my_table WHERE column_name = 'value';

这将会返回一个执行计划，其中包含了MySQL执行这个查询时使用的索引信息。如果在 Extra 列中看到 Using index 或者 Using where; Using index，那么表示查询使用了索引。如果没有使用索引，可能会看到 Using where 或者 Using filesort 等