mysql面试题 Day4

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

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

覆盖索引的定义

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

覆盖索引的要求

1. 查询条件列（WHERE 子句中的列）：这些列可以帮助MySQL快速定位匹配的行。
2. 选择列（SELECT 子句中的列）：这些列是查询返回的列。
3. 排序列（ORDER BY 子句中的列）：如果查询包括排序，这些列应该包含在索引中，以利用索引进行排序。

举个例子

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

• id（主键）
• name
• age
• grade

假设我们有以下查询：

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

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

CREATE INDEX idx_name_age ON students (name, age);

覆盖索引的好处

• 提高查询性能：由于所有查询需要的数据都在索引中，MySQL不需要回表查找，减少了I/O操作。
• 减少数据页访问：访问索引通常比访问数据页更快，特别是在表较大的情况下。
• 优化磁盘使用：使用覆盖索引可以减少磁盘读取操作，提高缓存命中率。

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

1. 索引扫描：MySQL使用 idx_name_age 索引找到所有 name LIKE '张%' 的记录。
2. 索引下推（ICP）：在索引扫描过程中，直接在索引中检查 age > 20 的条件。
3. 返回结果：由于 name 和 age 都包含在索引中，MySQL可以直接从索引返回结果，而不需要访问实际表数据。

总结

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

2 索引有什么缺点？给全部字段都加上索引行不行？

1. 空间占用：索引会占用额外的存储空间，特别是对于大型表来说，索引可能会占用大量的磁盘空间。

2. 写操作性能下降：当对表进行插入、更新、删除等写操作时，索引也需要被更新，这可能会导致写操作的性能下降。

3. 查询优化器的选择问题：当给表的所有字段都加上索引时，查询优化器可能会难以选择最优的索引，从而影响查询性能。

4. 缓存失效率增加： MySQL会根据查询的使用频率和访问模式来决定哪些索引放入内存中。如果全部索引的总大小超过了可用内存，MySQL将无法完全缓存所有索引，导致更多的磁盘IO操作，从而降低查询性能。

5. 内存压力增加： 当MySQL尝试将大量索引存储在内存中时，系统的内存压力会增加，可能影响到其他系统和进程的性能。

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

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

1. 减少索引大小： 前缀索引只对索引列的前缀部分进行索引，而不是对整个列进行索引。这意味着索引的大小会减小，尤其是当索引列的值比较长的情况下，使用前缀索引可以大大减少索引占用的存储空间。

2. 提高查询性能： 由于前缀索引减少了索引的大小，因此在进行查询时，MySQL需要读取的索引页数也会减少，这会提高查询的性能。特别是在涉及大量数据的查询场景下，前缀索引可以显著减少磁盘IO操作和内存消耗，从而加快查询速度。

3. 降低索引维护成本： 由于前缀索引的大小较小，因此在进行索引维护操作（如插入、更新、删除）时，需要的时间和资源也会减少。这意味着使用前缀索引可以降低索引维护的成本，提高系统的整体性能和稳定性。

4. 支持特定的查询模式： 在某些情况下，只需要对索引列的前几个字符进行匹配即可满足查询需求，这时使用前缀索引可以更好地支持这种查询模式。例如，对于字符串类型的列，可能只需要匹配前几个字符就能满足查询条件，这时使用前缀索引就能够更有效地支持这种查询。

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

3 mysql为什么使用B+树？

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

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

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

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

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

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

4 B+树为什么矮？

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

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

1. 内部节点只存储键值的引用：在B+树中，所有的数据记录都存储在叶子节点中，而内部节点只存储键值及其对应的子节点的指针（引用）。这样做的好处是能够在内部节点中存储更多的键值引用，因为不需要为每个键值都分配额外的存储空间。

2. 叶子节点形成链表：叶子节点之间通过指针形成链表，这样可以方便地进行范围查询和顺序访问。

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

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

5 什么是慢查询？出现了慢查询该如何排查？

慢查询是指在MySQL数据库中执行时间超过一定阈值（默认10秒）的SQL查询。慢查询可能会导致数据库性能下降，影响系统的正常运行。

处理慢查询的方法主要有以下几种：

1. 开启慢查询日志：MySQL提供了慢查询日志功能，可以记录下所有执行时间超过设定阈值的SQL语句。通过分析慢查询日志，可以找出问题SQL。

2. 优化SQL语句：对于执行时间较长的SQL语句，可以尝试进行优化，比如添加索引、改写SQL语句、减少查询的数据量等。

3. 数据库设计优化：如果数据库设计不合理，也可能导致查询效率低下。可以考虑对数据库结构进行优化，比如进行数据分区、数据分片等。

4. 硬件升级：如果数据库服务器的硬件性能不足，也可能导致查询效率低下。可以考虑升级硬件，比如增加内存、提高磁盘IO性能等。

5. 使用性能分析工具：有很多数据库性能分析工具，如MySQL的Performance Schema、Explain等，可以帮助我们更好地理解和优化SQL查询。

6. 使用缓存：对于一些查询结果不经常变化，但计算量大的查询，可以考虑使用缓存来提高查询效率。