mysql基础面经之二：索引、分区

xxx_520s

已于 2023-09-17 20:59:42 修改

阅读量558

点赞数

文章标签： mysql 数据库事务

于 2023-08-02 11:57:12 首次发布

本文链接：https://blog.csdn.net/yxg520s/article/details/132058116

版权

1 聚簇索引

1 和主键索引的关系
2 和非聚簇索引的关系，其叶子节点存储的是聚簇索引中的主键
3 索引覆盖机制使得非聚簇索引不用回表二次查询

2 举一个使用索引覆盖的例子

我的项目中没有使用到覆盖索引，但是可以举一个例子，比如我直接为年龄建立覆盖索引，现在我查询年龄为21的人，查到了叶子节点后，就可以直接取到这些记录，而不是像非覆盖索引时查找到主键，再去聚簇索引中取得数据

3 范围查询是怎么实现的

B+树的所有叶子节点都是双向链表连接起来的，查找到左边第一个叶子节点，然后依次向右查询直至范围右端点就行。因为链表链接起来的本身是有序的

4 索引失效

4.1 索引失效的情况

1 组合索引使用不当会导致索引失效
2 使用 !=这种情况会失效

真实情况：

数据库索引可以提高查询效率，但在一些情况下，索引可能会失效，也就是无法使用。以下是一些常见的索引失效的情况：
使用了不等于（<>）、NOT、IS NULL、<、>、!= 操作符的查询语句，数据库无法使用索引进行优化。
对于组合索引，如果查询条件不包含索引的最左侧部分，索引可能无法使用。比如有一个包含(a,b,c)的组合索引，查询条件只有b或者c的话，索引就不能被利用。
在列上使用函数或者表达式时，索引无法使用。例如，
SELECT * FROM table WHERE YEAR(column) = 2023;这样的查询无法使用索引。
当使用 LIKE 操作符进行模糊查询时，如果通配符在前面，例如 LIKE ‘%xx’，那么索引无法使用。如果通配符在后面，例如 LIKE ‘xx%’，那么索引仍然可以使用。
当OR操作符连接的条件分别在不同的索引列时，索引无法使用。
如果数据类型不一致，比如将字符串类型的列与数字类型进行比较，这样的查询也无法使用索引。
如果MySQL估计全表扫描比使用索引更快时（比如在非常小的表中），MySQL也不会使用索引。

4.2 分别讲讲各个失效的点的原因

因为查询的条件是不在索引树的节点中，无法匹配，也就查找不到具体的主键或者记录，自然就失效了

使用了不等于（<>）、NOT、IS NULL、<、>、!= 操作符的查询语句，数据库无法使用索引进行优化。

数据库索引，比如常见的B树索引，是基于值的有序排列的，当你使用等于（=）或者在（IN）操作符时，数据库能通过索引直接找到对应的值。但如果你使用了不等于或者NOT等操作符，数据库需要检查索引中的每一个项来确定哪些值不满足条件，性能上等同于全表扫描，这就失去了使用索引的意义。至于 “<” 或 “>” 操作符，如果它们作用在范围较大时，索引优化的效果也会大大降低。
对于组合索引，如果查询条件不包含索引的最左侧部分，索引可能无法使用。

对于组合索引，比如 (a, b, c)，数据库在建立索引时，会首先对a进行排序，然后在a的值相同的情况下对b进行排序，以此类推。如果查询条件不包含索引的最左侧部分，数据库就无法有效地利用索引进行查找。
在列上使用函数或者表达式时，索引无法使用。

如果你在查询条件中对一个列应用了函数或者表达式，数据库就无法直接使用索引找到对应的值，而需要对每一个值应用同样的函数或者表达式然后利用表达式的返回值再进行比较，因为返回值大多不是主键或者索引字段，比如count(),sum()函数，这就无法利用索引的优势了。
当使用 LIKE 操作符进行模糊查询时，如果通配符在前面，例如 LIKE ‘%xx’，那么索引无法使用。

基于B树的索引是按照列的值的顺序建立的，当你使用LIKE 'xx%'这样的查询时，数据库可以直接定位到以’xx’开头的部分，然后返回所有符合条件的值。但如果通配符在前面，数据库则需要检查所有的值来找出符合条件的部分，这样索引就无法发挥作用了，这跟使用<>字段导致索引失效的原因是一样的。
当OR操作符连接的条件分别在不同的索引列时，索引无法使用。

这是因为，当OR操作符连接的条件分别在不同的索引列时，数据库无法同时在两个索引列上进行查找，这样就无法利用索引了。
如果数据类型不一致，比如将字符串类型的列与数字类型进行比较，这样的查询也无法使用索引。

数据库在建立索引时会按照列的数据类型来排序，如果查询条件中的数据类型与列的数据类型不匹配，那么数据库就无法正确地使用索引了。
如果MySQL估计全表扫描比使用索引更快时（比如在非常小的表中），MySQL也不会使用索引。

对于非常小的表，全表扫描的代价可能比通过索引查找的代价更低。在这种情况下，数据库可能会选择全表扫描而不是使用索引。这是因为索引虽然能提高查找速度，但它也需要额外的存储空间，并且在插入和删除时需要额外的时间来更新索引。因此，如果表的大小很小，使用索引的代价可能会大于它的收益。

5 讲讲索引下推

Mysql性能优化：什么是索引下推？
索引下推（Index Condition Pushdown，ICP）是MySQL 5.6版本以后引入的一种优化策略，用于提高带有复杂WHERE子句的查询性能。

在没有ICP的情况下，MySQL服务器会从存储引擎检索完整的行数据，然后在服务器层面对WHERE子句中的条件进行评估。这意味着存储引擎可能会检索出大量最终不满足查询条件的行。
然而，当开启ICP特性后，一部分的WHERE子句条件会被"下推"到存储引擎层面。在存储引擎进行索引查找的时候，就可以及早地过滤掉不满足这些条件的索引项，避免了无用的行数据检索。这对于某些查询可以带来明显的性能提升。

注意，不是所有的查询都可以从ICP中受益。MySQL优化器会根据查询的具体情况决定是否使用ICP。而且，目前ICP特性只有在InnoDB和MyISAM存储引擎中被支持。

6 索引合并

6.1 什么是 Index Merge

Index Merge是MySQL中的一种优化策略，用于合并多个索引以改善查询性能。在这种策略中，优化器会使用多个索引来检索数据，并对结果进行合并。这通常发生在一个查询有多个独立的或条件（OR）时。

例如，假设有如下查询：

SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2';

如果col1和col2都有单独的索引，那么MySQL可以分别对这两个条件进行索引扫描，并将结果合并。

Index Merge有几种类型：

Union: 对多个索引扫描的结果进行并集操作。
Intersection: 对多个索引扫描的结果进行交集操作。
Sort-Union: 针对已排序的索引，进行更高效的合并。

使用Index Merge通常比只使用单个索引要快，但也不总是最优的。优化器会基于统计信息和查询成本来决定是否使用Index Merge。

总体来说，索引是数据库优化中不可或缺的一部分，而Index Merge是复杂查询优化中的一个有用工具。

6.2 SELECT * FROM table WHERE col1 = ‘value1’ OR col2 = ‘value2’; 如果是or，应该是取并集操作，如果or改成and，应该是取交集操作对吧

是的，您的理解是准确的。

当查询条件使用 OR 连接时，通常会对每个单独的条件进行索引扫描，然后取并集（Union）操作。这样，任何满足 col1 = 'value1' 或 col2 = 'value2' 的行都会被选中。
```
SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2';
```
在这种情况下，如果 col1 和 col2 都有索引，一个有效的优化方式就是使用 Index Merge 的 Union 类型来合并这两个索引的结果。
当查询条件使用 AND 连接时，会进行**交集（Intersection）**操作，选中那些同时满足所有条件的行。
```
SELECT * FROM table WHERE col1 = 'value1' AND col2 = 'value2';
```
在这种情况下，如果 col1 和 col2 都有索引，一个有效的优化方式是使用 Index Merge 的 Intersection 类型来合并这两个索引的结果。

这两种策略大大提高了查询效率，特别是在涉及多个独立条件或多个表的复杂查询中。

6.3 mysql在什么时机下会使用这种索引合并技术

MySQL 在查询优化阶段可能会决定使用索引合并（Index Merge）技术。具体使用的时机取决于多个因素，包括但不限于：

多个独立条件: 当一个查询包含多个与（AND）或或（OR）连接的条件，并且这些条件涉及到多个索引时。
- 对于 OR 连接的多个条件，优化器可能使用 Index Merge 的 Union 类型。
- 对于 AND 连接的多个条件，优化器可能使用 Index Merge 的 Intersection 类型。
统计信息: MySQL 会收集有关表和索引使用的统计信息。这些统计信息有助于优化器做出决策。
查询成本: 优化器会基于查询成本模型来决定是否使用索引合并。只有当使用 Index Merge 能带来成本上的优势时，优化器才会选择这种策略。
系统和硬件限制: 有时，由于系统或硬件限制（如可用内存），即使 Index Merge 在理论上是有益的，优化器也可能决定不使用它。
版本和设置: 不同版本的 MySQL 可能具有不同的优化算法或设置，这也会影响到是否使用索引合并。

通常，用户无法直接控制是否使用索引合并，这是由查询优化器自动决定的。但是，通过分析查询执行计划（通常通过 EXPLAIN 命令），用户可以了解优化器是否选择了这种策略，并据此进行相应的优化。

6.4 通过分析查询执行计划（通常通过 EXPLAIN 命令），用户可以了解优化器是否选择了这种策略，并据此进行相应的优化。我如何得知

在MySQL中，你可以使用EXPLAIN命令来查看查询的执行计划。这将提供有关MySQL如何执行SQL查询的详细信息。特别地，EXPLAIN会展示：

使用了哪些索引
表的连接顺序
每个表的数据行数估算
等等

为了查看是否使用了索引合并，你可以运行EXPLAIN命令，并查看输出结果中的key和Extra列。

key列：显示了MySQL决定使用的索引。如果这里列出了多个索引，那可能就是使用了索引合并。
Extra列：这一列会提供其他有用信息，包括是否进行了索引合并。如果你看到“Using index for merge”，那么就是使用了索引合并。

例子：

EXPLAIN SELECT * FROM table_name WHERE col1 = 'value1' OR col2 = 'value2';

在结果中，你可能会看到与索引合并相关的信息。

请注意，查询计划可能会因为统计数据的变化、表结构的变化或者MySQL版本的不同而改变。因此，最好是在生产环境的数据上运行EXPLAIN来获取最准确的信息。

6.5 Sort-Union: 针对已排序的索引，进行更高效的合并。B+树索引不都是有序的吗

你的观察是对的：B-tree 类型的索引和一些其他索引类型通常都是有序的。但"Sort-Union"的名称可能有点令人误解。实际上，这并不意味着它会对索引进行排序，因为索引本身通常已经是有序的。"Sort-Union"是一种优化策略，用于合并两个或更多有序的索引列表。

在 “Sort-Union” 策略中，MySQL 会并行地遍历多个有序的索引列表。由于这些列表是有序的，MySQL 可以非常高效地合并它们，通常只需要一次遍历。

这与没有预先排序的列表合并相对，预先排序的合并通常需要更多的计算和内存资源。

所以，当我们说 “Sort-Union”，实际上是指这种优化合并策略，而不是指它会对索引进行排序。这种方法充分利用了索引已经排序的这一特性，以更高效地执行合并操作。

7 如果磁盘满了，删除表中的一半数据能不能解决问题（度小满金融一面）

7.1 如果磁盘满了，删除表中业务上不用的数据会解决问题吗（重要）

答：

我的答案：可能会也可能不会，因为可能会存在页内碎片问题，如果innodb没有对删除数据后的数据页进行相关的合并操作，则即使删除了其中的数据也不会解决问题，也就是说这张表所占用的数据页数不变，索引表大小也自然不会变

gpt4：

如果MySQL的磁盘已满，删除数据是一种方式来释放空间。但仅仅从应用层面删除数据并不意味着磁盘空间会立即得到释放。这取决于MySQL的存储引擎，例如InnoDB。如果使用InnoDB引擎，空间不会立即返回给操作系统，而是被标记为可用并由MySQL重新使用。为了真正释放空间，你可能需要进行一些其他操作，如优化表(OPTIMIZE TABLE)或者重新导出、导入数据库。

因此，删除一年的记录可能可以解决问题，但并不是所有情况都可以。另外，如果数据增长的速度迅速，你很快可能会再次遇到空间问题。最佳做法是监控磁盘空间并根据需要增加。

7.2 面试官给我的提示是更新索引会不会导致这种即使删除了磁盘一半的数据，仍然无法解决磁盘满的问题？

答：面试官的提示指向了一个很重要的方面：索引。

在MySQL中，当数据被删除时，相关的索引项通常不会立即被删除或收缩。这意味着，即使你从表中删除了很多数据，由于相关的索引结构没有变小，磁盘空间的实际使用可能不会显著减少。

具体来说，如果一个InnoDB表有很多的索引，这些索引可能占据的空间与数据本身同样多，甚至更多。当你删除表中的数据时，虽然数据页可能被标记为可重新使用，但这并不意味着相关的索引页也是这样。你可能需要重新构建或优化这些索引来真正释放空间。

这里有几种方法可以解决这个问题：

OPTIMIZE TABLE: 这是最直接的方法。此命令将重建表和索引，释放未使用的空间。但是这个操作对于大型表可能非常慢。
DROP INDEX and CREATE INDEX: 如果你知道哪些索引占据了大量空间，你可以选择删除并重新创建它们。
重导表: 导出表的数据，删除表，然后重新导入数据。这在某种程度上与OPTIMIZE TABLE相似，但你会有更多的控制，例如可以选择不重新导入某些不再需要的数据或索引。

不论选择哪种方法，都应当在低峰时间进行，因为这些操作可能会消耗大量的I/O，影响到其他运行中的查询或操作。

所以，面试官的提示是很有道理的。即使删除了大量数据，仍然需要考虑索引占据的空间，特别是在一个索引重的数据库或表中。

7.3 首先如果是主键索引，叶子节点指向的是行记录是真实行记录的数据副本吗，还是指同一份数据？

在InnoDB中，表的数据实际上是按照主键索引的顺序存储的，这种结构被称为聚簇索引。
聚簇索引的叶子节点包含了数据行本身，而非数据的副本。也就是说，当你查询基于主键的数据时，你直接获取到的是数据行，没有额外的跳转。
这也意味着在InnoDB表中，每个表只能有一个聚簇索引（即主键索引）。

7.4 主键索引（聚簇索引）的叶子节点直接指向数据行本身，而非数据的副本，覆盖索引的叶子节点呢？

当我们谈到覆盖索引时，实际上我们是在描述一个查询使用的索引，而不是描述索引本身的类型。覆盖索引查询是指查询所需的所有数据都包含在索引中，无需访问数据表的实际行。对于InnoDB，非聚簇索引（普通索引）的叶子节点通常存储主键的值，但如果一个查询只需要索引中的列，那么叶子节点就已经包含了所有必要的信息，使得该查询成为一个覆盖索引查询。

7.5 在MySQL中，当数据被删除时，相关的索引项通常不会立即被删除或收缩。这意味着，即使你从表中删除了很多数据，由于相关的索引结构没有变小，磁盘空间的实际使用可能不会显著减少。为什么索引项不会立即收缩，这里的索引项是指非叶节点的主键吗？还是叶子节点的记录不会收缩？如果不立即对查找、新增或者删除难道没有影响吗？

为什么索引项不会立即收缩:
- 索引结构，如B+树，被设计为在数据插入和删除时自平衡。当数据从一个B+树结构的节点中删除时，节点可能不会立即被回收，但它可能在后续的插入操作中被重新使用。
- 如果立即收缩或重新平衡索引，那么每次删除操作都可能导致大量的磁盘I/O操作，这将大大降低性能。因此，为了效率，很多数据库管理系统选择在一定程度上延迟索引的收缩和重建。
- “索引项”可以指叶子节点中的条目，也可以指非叶节点中的条目。在大多数情况下，当我们谈论索引结构不收缩时，我们是指整个索引，包括叶子节点和非叶节点。
- 当然，长期不进行索引的维护可能会影响查询、插入和删除的性能，因为索引可能变得过于膨胀，有很多未使用的或半满的页面。这就是为什么定期进行数据库维护，如优化表和重建索引，是很重要的。

在实践中，数据库管理员通常会监视索引的健康状况，并根据需要进行维护，以确保性能处于最佳状态。

7.6 索引项不会立即收缩，那对于聚簇索引来说，叶子节点中的真实的记录呢，会不会被物理删除掉？

对于InnoDB的聚簇索引（即主键索引），叶子节点中直接包含了表的数据行。当你从表中删除一行数据时，对应的索引条目确实会从聚簇索引中删除。但这并不意味着磁盘上对应的空间会立即被释放回操作系统。

具体行为如下：

逻辑删除: 当你执行DELETE操作时，InnoDB首先会逻辑地删除记录，这意味着它会标记记录为已删除，但实际上记录还留在页中。
空间利用: 虽然这些标记为已删除的记录仍然占据物理空间，但当新的记录插入到这个页时，这些空间可以被重新利用。
物理空间释放: 即使一个数据页变得完全空闲（例如，你删除了该页上的所有行），它也不会立即被释放回文件系统或操作系统。相反，它会留在表空间文件中并被标记为可重新使用。只有在特定的维护操作（如表的OPTIMIZE操作）中，这些空页才可能被物理地释放。

这种策略的一个主要原因是性能。频繁地进行磁盘空间的回收和再分配可能会导致大量的磁盘I/O和碎片化，这对性能不利。相反，通过重用已分配的空间，InnoDB可以更有效地处理新的数据插入操作。

8 什么是mysql的分区表，在治理慢查询上可以发挥什么作用

答：分区相当于是按照某一个字段给大表进行逻辑划分，本来mysql是为一张表只建立一个B+树聚簇索引的，有了分区后，每一个分区内都有对应的聚簇索引，比如现在一张表每一年会新增100w的量，五年就是500w，现在按照年份进行分区，则新建5个分区，每一个分区内都有一个索引了这一年份的聚簇索引树，当一个sql语句按照日期查询时，首先会判断自己在哪一个分区中，然后拿到对应分区内的B+树索引，再进行查找。这样做的好处是，原本不分区时的查找复杂度是log(x,500w)，现在是log(x,100w),如果分区再多一些，则查找的复杂度会进一步降低。