mysql面试题 Day4

1 什么是覆盖索引？对要查询的列和查询条件中的列有什么要求

覆盖索引（Covering Index）是指一个索引包含了一次查询所需的全部列，因此可以完全满足查询需求，而无需访问实际的表行数据。（即避免回表操作）。

覆盖索引的定义

覆盖索引是一个包含查询中的所有列（包括查询条件列、选择列和排序列）的索引。覆盖索引使得查询可以完全通过索引来执行，而不需要读取表的实际数据行。

覆盖索引的要求

举个例子

假设有一个表 students，包含以下字段：

假设我们有以下查询：

SELECT name, age FROM students WHERE name LIKE '张%' AND age > 20;

为了使查询可以使用覆盖索引，我们需要创建一个包含 name 和 age 列的复合索引：

CREATE INDEX idx_name_age ON students (name, age);

覆盖索引的好处

有了覆盖索引 idx_name_age 后，查询过程如下：

总结

覆盖索引通过包含查询所需的所有列，显著提高了查询性能，尤其是对大表的查询。为了充分利用覆盖索引，建议在设计索引时，综合考虑查询条件列、选择列和排序列，并在一个索引中包含这些列。

空间占用：索引会占用额外的存储空间，特别是对于大型表来说，索引可能会占用大量的磁盘空间。
写操作性能下降：当对表进行插入、更新、删除等写操作时，索引也需要被更新，这可能会导致写操作的性能下降。
查询优化器的选择问题：当给表的所有字段都加上索引时，查询优化器可能会难以选择最优的索引，从而影响查询性能。
缓存失效率增加： MySQL会根据查询的使用频率和访问模式来决定哪些索引放入内存中。如果全部索引的总大小超过了可用内存，MySQL将无法完全缓存所有索引，导致更多的磁盘IO操作，从而降低查询性能。
内存压力增加： 当MySQL尝试将大量索引存储在内存中时，系统的内存压力会增加，可能影响到其他系统和进程的性能。

所以索引不是越多越好。⽐如为了避免数据量过⼤，某些时候我们会使⽤前缀索引。

使用前缀索引可以带来以下好处，特别是在面对大量数据时：

减少索引大小： 前缀索引只对索引列的前缀部分进行索引，而不是对整个列进行索引。这意味着索引的大小会减小，尤其是当索引列的值比较长的情况下，使用前缀索引可以大大减少索引占用的存储空间。
提高查询性能： 由于前缀索引减少了索引的大小，因此在进行查询时，MySQL需要读取的索引页数也会减少，这会提高查询的性能。特别是在涉及大量数据的查询场景下，前缀索引可以显著减少磁盘IO操作和内存消耗，从而加快查询速度。
降低索引维护成本： 由于前缀索引的大小较小，因此在进行索引维护操作（如插入、更新、删除）时，需要的时间和资源也会减少。这意味着使用前缀索引可以降低索引维护的成本，提高系统的整体性能和稳定性。
支持特定的查询模式： 在某些情况下，只需要对索引列的前几个字符进行匹配即可满足查询需求，这时使用前缀索引可以更好地支持这种查询模式。例如，对于字符串类型的列，可能只需要匹配前几个字符就能满足查询条件，这时使用前缀索引就能够更有效地支持这种查询。

总的来说，使用前缀索引可以在面对大量数据时带来存储空间的节省、查询性能的提升、索引维护成本的降低以及对特定查询模式的支持等好处。因此，在设计索引时，根据实际情况考虑是否可以使用前缀索引来优化数据库的性能。

关键点：⾼度低，叶⼦节点是链表，查询时间可预测性，节点⼤⼩等于⻚⼤⼩

MySQL使⽤B+树主要就是考虑三个⻆度：

1. 和⼆叉树，如平衡⼆叉树，红⿊树⽐起来，B+树是多叉树，⽐如MySQL默认是1200叉树，同样数据量，⾼度要⽐⼆叉树低；

2. 和B树⽐起来，B+树的叶⼦节点被连接起来，形成了⼀个链表，这意味着，当我们执⾏范围查询的时候，MySQL可以利⽤这个特性，沿着叶⼦节点前进。⽽之所以NoSQL数据库会使⽤B树作为索引，也是因为它们不像关系型数据库那般⼤量查询都是范围查询；

3. B+树只在叶⼦节点存放数据，因此和B树⽐起来，查询时间稳定可预测。（注：这是⼀个⾼级观点，就是在⼯程实践中，我们可能倾向于追求⼀种稳定可预测，⽽不是某些数据贼快，某些数据唰⼀下贼慢）

4. B+树和跳表⽐起来，MySQL将B+树节点⼤⼩设置为磁盘⻚⼤⼩，这样可以充分利⽤ MySQL的预加载机制，减少磁盘IO

MySQL中的B+树相对于其他类型的树（如B树）可能会显得“矮”的主要原因是其内部节点存储的是键值的引用（或者说是指针），而不是整个键值本身。

具体来说，B+树的特点是：

内部节点只存储键值的引用：在B+树中，所有的数据记录都存储在叶子节点中，而内部节点只存储键值及其对应的子节点的指针（引用）。这样做的好处是能够在内部节点中存储更多的键值引用，因为不需要为每个键值都分配额外的存储空间。
叶子节点形成链表：叶子节点之间通过指针形成链表，这样可以方便地进行范围查询和顺序访问。

由于B+树内部节点只存储了键值的引用，并且叶子节点形成了链表，因此整体高度相对较矮。相比之下，B树通常需要在内部节点存储完整的键值信息，这可能导致树的高度相对更高一些，尤其是在数据量大的情况下。

总结来说，MySQL中B+树“矮”的特性是因为它在设计上优化了内部节点的存储方式，使得树的高度相对较低，从而提高了查询效率。