【MySQL】八股总结二：索引

Peanut
149 阅读12分钟

概念理解

联合索引 及 最左匹配原则联合索引详解

联合索引

  • 多个字段一起创建索引，且如果按 (age, score)这样建立联合索引，那么数据库会 首先按age排序，age相同的按score排序，意思就是说联合索引先按左边的字段排序，左边字段相同的值再按后一个字段排序。

最左匹配原则

  • 在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件(where)中去匹配，如果where条件中存在与 联合索引中左侧匹配的字段，则会过滤数据，直至联合索引中 全部字段匹配。（为什么呢，以上面的例子为例，age是全局有序的，score是全局无序的，score仅在age相同时才有序，所以得先匹配左边的呀）
  • 但是，where条件中遇到范围查询（< >）会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。 （索引失效）

主键索引 及 二级索引（非主键索引）

主键索引： 按主键列建立的索引，表中只能有一个主键。

二级索引（辅助索引） ：二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

聚簇索引 和 非聚簇索引

聚簇索引： 索引和数据一起存放，叶子节点存储索引和对应的数据。InnoDB主键索引就是聚簇索引，按主键来聚簇数据的。

非聚簇索引：索引和数据不存一起，叶子节点存数据的指针/主键 。 InnoDB中二级/辅助索引就是非聚簇索引，且存的时叶子节点中存的是主键。

覆盖索引 与 回表查询

覆盖索引： 如果要查询select的列刚好是索引列（前提是where要命中索引），那么直接根据这个二级/辅助索引查到数据了（例如对age，name建索引，select age, name from tb where age = 2），这样就在辅助索引中获取了age，name，无需回表查询（去主键索引中查） 所有列数据。

回表查询：去主键索引表中查询全部列的数据。

索引下推， 理解减少回表次数，重在理解，有点难理解

执行一条 select 语句，期间发生了什么？（这里面详细介绍了索引下推）

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率

如何减少回表次数的呢？

  • 索引下推总结：在有联合索引的情况下 （a, b） ，查询where a > 20 and b = xx。
  • 虽然字段b用不到联合索引，但是b=20 这条记录的判断是在回表之前进行判断的，如果b!=xx, 那么不会回表查询，减少回表次数。
  • 在没有索引下推的时候，a>20 利用了索引之后，【如果select * 则将后面所有的数据】不会立即判断b=xx，而是将所有记录全部回表，回表之后再进行b字段判断。
  • 注：索引下推仍然没有使用失效的索引字段。

图源：小林coding的文章。

覆盖索引 和 索引下推（美团）

百度安全验证

覆盖索引：查二级索引时，select的数据字段在二级索引中有，不需要回主键表查所有字段。

索引下推：执行部分where判断在 二级索引表过滤掉一些字段（通常是在有联合索引的情况下没有完全利用联合索引），减少回表的次数。

总结：覆盖是不需要回表，二级索引表里有数据。下推是减少回表，利用二级索引过滤掉一些数据。

面试问题

讲一下索引，索引是什么？（美团）

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

然后按数据结构分有：xxxxx索引

按组织结构分有：xxxxxx索引

按索引类型分有：xxxxxxx索引

建立索引的原则？（美团）

  1. 频繁更新的字段慎重建索引，只为用于搜索、排序或分组的列建索引。（因为索引用来查的，更新需要维护）
  2. 每个表索引不要建太多。 （同样有维护成本）
  3. 尽可能考虑建立联合索引。（如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间）
  4. 选择区分度高的字段建立索引。

sql优化及索引使用事项，包含索引建立原则（重要，在工作中体会）

  1. 频繁更新的字段慎重建索引，只为用于搜索、排序或分组的列建索引。（因为索引用来查的，更新需要维护）
  2. 每个表索引不要建太多。 （同样有维护成本）
  3. 尽可能考虑建立联合索引。（如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间）
  4. 选择区分度高的字段建立索引，例如性别这种不适合建索引。
  5. 索引字段使用了函数，可能会导致索引失效。
  6. 查询时不用select * , 很多时候能避免回表扫描。
  7. limit进行分页查询的时候注意深分页问题。

为什么使用 B+树而不使用其他结构（其他结构的缺点） ？各种结构索引的优缺点！！！

详解：为什么 MySQL索引要用 B+tree

  • hash：Hash索引对于顺序和范围查询劣势很大。（SELECT * FROM tb1 WHERE id < 500;）
  • 二叉查找树：二叉查找树可能出现不平衡的情况，性能非常依赖于它的平衡程度。
  • 红黑树：红黑树是二叉树，表数据很多时会导致树的高度太高，那么IO次数会更多。
  • B树：它缺点主要是和B+树的区别
    • B树非叶子节点也存数据（数据索引都存）。所以B+树能存更多的索引。
    • B树叶子节点没有用双向链表连接。不适合范围查询，B+树范围查询顺着叶子节点链表走就行，

B树和B+树的区别 及 MySQL为什么选择B+树而不选择B树

两个树的区别：

  1. B树非叶子节点也会存储数据，B+树数据都在叶子节点。
  2. B+树的叶子节点会采用双向链表连接，B树不会。

MySQL为什么选择B+树而不选择B树：

  1. （因为B+树数据都在叶子节点，B数非叶子节点存数据导致占用了索引的空间）所以B+树的索引页节点能存更多的索引，树就更宽，树的高度尽可能低，所以B+树具有更少的IO次数。
  2. （B+树的叶子节点会采用双向链表连接）所以遇到范围查询时，B+树顺着叶子节点链表检索，而B树需要一个一个查，

我对B+树优点的理解

三个优点

  1. Innodb使用B+树作为索引 最最最主要的原因 还是为了降低查找数据时 的磁盘IO（磁盘IO是比较耗时的） ，因为是按数据页查找，每个页都需要IO，所以尽量查找较少的页找到数据。
  2. 第二个原因主要原因，数据都在叶子节点上，非叶子节点只有索引，所以树的高度很低可以减少IO。
  3. 叶子节点采用双向链表，方便范围查找。

为什么不对每个列都建索引？

每个索引对应一颗B+树，如果字段多，每个字段键索引，那么

  1. 索引 占用的空间 其实很多，
  2. 并且修改数据修改索引时耗费的时间也多。

使用索引一定能提高查询性能吗?

不一定，使用二级/辅助索引时，执行回表查询的记录越多（当对某个范围查询），索引查询效率越低，有时甚至不如全表扫描。

（解释，例如我使用二级索引查100条记录，每条记录都要回主键表查，且可能这些记录的主键分散， 那么每次回表查询都可能要磁盘IO，最坏情况IO 100次，可能比我全表扫描的磁盘IO次数还多）

什么情况下不建索引？

  1. 数据量小，且要回表查询的语句，不如全表扫的效率高
  2. 区分度不高的字段，性别这种
  3. 更新频繁的字段，维护成本高

联合索引停止匹配的场景？

where条件中遇到范围查询（< >）会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

索引失效的几种情况？（小林coding有说）

谁还没碰过索引失效呢

25. 进阶-索引-使用规则-索引失效情况二_哔哩哔哩_bilibili

记前三点四点即可

  1. 模糊查询时，左模糊和左右模糊查询 索引会失效。也就是 like %xx 或者 like %xx%
  2. 对索引列做了表达式计算、函数、类型转换操作，这些情况下都会造成索引失效。（因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了）
  3. 不遵循最左匹配原则，联合索引会失效。
  4. 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列

select * 有时候会导致索引失效，为什么？

  1. select * 查多个字段，如果where匹配的不是主键索引，而是二级索引。这时候需要回表查询。
  2. 在数据行较少或者某些情况 ，优化器可能会考虑全表扫描而不是走二级索引回表。导致了索引失效。

索引优缺点。（字节）

优点：

  1. 查找快，提供检索效率，减少查询时间。
  2. 帮数据库 快速排序和分组

缺点：

  1. 占存储空间
  2. 维护成本高：数据增删改查，索引也要更新。
  3. 降低写入性能：每次写入都需要更新索引。

所以一般对读多写少的字段建索引

如何查看某条sql是否用了索引？

explain看type扫描方式或者看key和key_len

explain命令分析sql执行计划。explain不会执行语句，而是通过优化器的分析查出的方案。

  • type：扫描方式（索引或者全表扫描）
  • key：用到的索引名
  • key_len: 索引长度
  • Extra：额外信息。Using index表示覆盖索引。

key_len怎么看？用来判断什么？

联合索引失效的情况

通常用来判断联合索引 使用了多少字段，判断索引长度

int 4字节/5字节（如果字段允许为 NULL，就在字段类型占用的字节数上加 1，也就是 5 字节）

索引和原始数据是耦合的吗？答聚簇索引和非聚簇索引

聚集索引和非聚集索引的区别？非聚集索引一定回表查询吗?(简单)

在上面概念理解有提到，一个是索引和数据一起存放，一个是索引和数据分开存（索引中叶子节点存数据的指针或主键id），非聚集索引不一定回表查询，查询覆盖索引时不会回表查询。

为什么索引能提高查询速度？

索引查询不需要全表扫描，记录一般是存在数据页中的，没有索引需要遍历每个数据页。

索引一般为B+树结构， 并且数据按树有序排列，它是一个多叉查找树，索引列的值按树结构排序，在innoDB中一个索引页有4KB，通常查找的页不超过四层。

Mysql中没有定义主键怎么办？

在 MySQL 的 InnoDB 的表中

  1. 当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键
  2. 否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

计算一下高为3或4的B+ 树索引能存多少数据？进阶

为什么 MySQL索引要用 B+tree

前置知识，主键bigint 8字节，索引占6字节。

结论，假如单条数据1KB

三层2000w

四层，2000w * 1170

InnoDB，1000w数据，主键是长整型，B+树大概有多少层？（搜狐）

为什么 MySQL索引要用 B+tree

前置知识，主键bigint 8字节，索引占6字节。

额外条件，单条数据的大小，假设单条数据1KB，那么叶子页可以存16条数据。

16384 Byte / (8+6) Byte ≈ 1170，也就是说一个节点设置 16KB 大小的话可以放 1170个索引。

假设高度为3，那么可以存1170 × 1170 × 16 = 21902400，大约2000w数据。

单行数据大小会 影响树的高度吗？进阶

4024年了，还有人不懂mysql（innodb）索引树的高度由什么决定吗？_哔哩哔哩_bilibili

会，因为数据其实是挂在叶子节点的页面上的。

单行数据越大，导致叶子节点能存的数据越少。导致上层索引节点需要更多索引值，引发页分裂，然后逐层向上分裂，最终导致树的高度增加。

索引类型？（面经）

按照数据结构维度划分：b+、hash、full-text

按存储：聚集索引（主键索引）、非聚集索引（二级索引、辅助索引）

按照应用：主键索引、唯一索引、联合索引、覆盖索引

mysql使用的什么索引？

跟存储引擎有关

InnoDB：

MyISAM：

avatar
文章
阅读
粉丝