MySQL基础-索引

DataBase MySQL

发布日期: 2025-05-08

更新日期: 2025-07-01

文章字数: 5.5k

阅读次数:

AI相关提问

基础概念类：

什么是数据库索引？为什么要使用索引？
- 解答： 数据库索引是一种特殊的数据结构（通常是 B-Tree 或 B+Tree），它存储着表中一列或多列的值，并关联着这些值对应的数据行的物理位置或主键信息。
- 使用索引的目的是为了加快从表中检索数据的速度。没有索引时，数据库可能需要扫描整个表（全表扫描）来找到匹配的数据，这在表很大时效率非常低。有了索引，数据库可以直接根据索引结构快速定位到目标数据行，就像书的目录一样。
索引的主要作用是什么？它解决了什么问题？
- 解答： 索引的主要作用是显著提升数据库查询（SELECT）的性能，特别是带有 WHERE 条件过滤、JOIN 表连接、ORDER BY 排序和 GROUP BY 分组的查询。
- 它解决了在大数据量表中进行数据查找效率低下的问题，避免了昂贵的全表扫描操作。
使用索引有什么优点和缺点？
- 解答：
  - 优点：
    - 大幅提高数据检索速度，减少数据库的查询响应时间。
    - 在进行数据排序和分组时，可以减少或避免额外的排序开销。
    - 在连接（JOIN）操作中，可以提高连接的效率。
  - 缺点：
    - 占用磁盘空间： 索引本身是需要存储的。
    - 降低写操作性能： 当表中数据进行 INSERT, UPDATE, DELETE 操作时，不仅需要修改表中的数据，还需要同步更新相关的索引结构，这增加了额外的开销，导致写操作变慢。
    - 创建和维护成本： 创建索引需要时间，并且随着数据的修改，索引可能需要定期维护（如重建）以保持其效率。

索引类型与原理类：

数据库中常用的索引数据结构有哪些？请简述它们的工作原理。
- 解答：
  - B-Tree（或 B+Tree）： 这是关系型数据库中最常用的索引结构。它是一种平衡树，能够保持数据有序，支持高效的查找、范围查询、插入和删除操作。其特点是树的每个节点可以存储多个关键字和子节点的指针，树的高度较低，适合磁盘存储（减少磁盘I/O）。
  - Hash Index： 基于哈希表实现。通过对索引列的值计算哈希码，并映射到哈希表中的桶，桶中存储指向数据行的指针。适用于等值查询（=），查找速度极快（理论上 O(1)），但不适用于范围查询、排序或模糊匹配。
B-Tree 和 B+Tree 有什么区别？数据库索引为什么多用 B+Tree？
- 解答：
  - 区别：
    - 数据存储位置： 在 B-Tree 中，数据（或指向数据的指针）可以存储在树的任何节点。在 B+Tree 中，数据（或指向数据的指针）只存储在叶子节点，非叶子节点只存储键值用于导航。
    - 叶子节点连接： B+Tree 的所有叶子节点构成一个有序链表，可以方便地进行范围扫描。B-Tree 的叶子节点之间通常没有链表连接。
  - 数据库索引多用 B+Tree 的原因：
    - 更适合磁盘 I/O： B+Tree 的非叶子节点只存储键，相同大小的节点可以存储更多的键，因此树的高度更低，减少了磁盘查找次数。
    - 范围查询效率高： 叶子节点的链表结构使得范围查询（如 WHERE col BETWEEN 10 AND 20）非常高效，只需找到范围的起点，然后沿着链表顺序遍历即可。
    - 全表扫描效率高： 如果需要进行全表扫描，可以直接遍历 B+Tree 叶子节点链表，比 B-Tree 方便。
什么是聚集索引（Clustered Index）？什么是非聚集索引（Non-clustered Index）？它们有什么区别？
- 解答：
  - 聚集索引： 定义了表中数据行的物理存储顺序。数据的物理存储顺序与索引的逻辑顺序一致。一个表最多只能有一个聚集索引，因为数据行只能按照一种物理顺序存放。通常主键会自动创建聚集索引（如果表没有其他聚集索引）。
  - 非聚集索引： 一个独立于数据行的结构（通常是 B+Tree）。它的叶子节点存储索引列的值以及指向实际数据行的“书签”（例如，数据行的物理地址或主键值，取决于数据库实现）。数据行的物理存储顺序与非聚集索引的逻辑顺序无关。一个表可以有多个非聚集索引。
  - 区别总结：
    - 数量：聚集索引最多一个，非聚集索引可以多个。
    - 物理顺序：聚集索引决定数据物理顺序，非聚集索引不决定。
    - 叶子节点内容：聚集索引叶子节点是数据行本身，非聚集索引叶子节点是索引值 + 指向数据行的指针。
    - 数据访问：通过聚集索引查找数据，直接定位到数据行。通过非聚集索引查找数据，首先通过索引找到指向数据行的指针，然后再去查找数据行（称为“回表查询”）。
什么是哈希索引（Hash Index）？它的优缺点是什么？适用于什么场景？
- 解答：
  - 概念： 基于哈希表实现，将索引列值通过哈希函数计算哈希码，并存储在一个哈希表结构中，每个哈希值对应一个或多个数据行的指针。
  - 优点： 在进行等值查询（=）时，查找速度非常快，平均时间复杂度 O(1)。
  - 缺点：
    - 不支持范围查询、排序、模糊匹配。
    - 哈希冲突可能影响性能。
    - 不支持索引列的函数计算。
    - 通常只支持等值比较。
  - 适用场景： 仅需要执行等值查询，数据不需要排序的场景。例如，某些 NoSQL 数据库或特定存储引擎（如 MySQL 的 MEMORY 引擎）会使用哈希索引。
什么是全文索引（Full-text Index）？
- 解答： 全文索引是为大型文本数据（如文章内容、评论等）设计的特殊索引。它对文本进行分词、过滤停用词、词干提取等处理后建立索引，用于高效地在文本内容中进行关键词搜索、短语搜索、模糊搜索等。
什么是覆盖索引（Covering Index）？它的好处是什么？
- 解答： 覆盖索引是指一个非聚集索引，它包含了查询语句中 SELECT 列表和 WHERE 子句等条件中所有需要的列。
- 好处： 当查询可以使用覆盖索引时，数据库只需要遍历这个索引结构就可以获取所有需要的数据，而无需再回到表中去查找实际的数据行（避免了“回表查询”）。这极大地减少了磁盘 I/O 操作，从而显著提高查询性能。

索引设计与使用类：

应该在哪些列上创建索引？有哪些选择索引列的原则？
- 解答：
  - 原则：
    - 经常出现在 WHERE 子句中作为过滤条件的列。
    - 经常出现在 JOIN 连接条件中的列。
    - 经常出现在 ORDER BY 或 GROUP BY 子句中用于排序或分组的列。
    - 具有较高基数（列中不重复值的数量占总行数的比例高）的列，这样索引才能有效地过滤数据。
    - 作为表的主键（Primary Key）或唯一键（Unique Key）的列，数据库会自动创建索引（通常是聚集索引或唯一非聚集索引）。
不应该在哪些列上创建索引？
- 解答：
  - 低基数（Low Cardinality）的列： 例如性别、状态、布尔值等，这些列的不同值很少。在这种情况下，使用索引进行过滤可能只能排除很少一部分数据，优化器可能认为全表扫描更快。
  - 更新非常频繁的列： 每次更新都需要维护索引，增加写操作的开销。
  - 大型文本（BLOB/TEXT）列的全部内容： 如果需要对文本内容搜索，应考虑使用全文索引，而不是普通索引。对这些列建立索引通常只对列的前缀有效。
什么是联合索引（Composite Index 或 Multi-column Index）？如何选择联合索引中列的顺序？
- 解答：
  - 联合索引： 在表的多个列上创建的索引。例如，CREATE INDEX idx_name ON table_name (col1, col2, col3);
  - 列的顺序选择： 联合索引的列顺序非常重要，因为它遵循最左前缀原则。通常将最常用于过滤、范围查询或排序的列放在前面。如果 col1 经常单独使用，并且 (col1, col2) 经常一起使用，那么将 col1 放在 col2 前面是一个好的选择。
解释最左前缀原则。
- 解答： 对于联合索引 (col1, col2, col3)，索引会按照 col1 先排序，在 col1 相同的情况下再按照 col2 排序，以此类推。最左前缀原则意味着数据库可以利用这个索引来支持基于索引最左边连续的列的查询。
- 例如，索引 (a, b, c) 可以有效地用于以下查询条件：
  - WHERE a = 1
  - WHERE a = 1 AND b = 2
  - WHERE a = 1 AND b = 2 AND c = 3
  - WHERE a > 1 (范围查询，可能会扫描部分索引)
  - WHERE a = 1 ORDER BY b (如果索引覆盖了 b)
- 但它不能直接用于以下查询条件（或者只能使用索引的一部分，甚至完全不使用索引）：
  - WHERE b = 2 (没有最左边的 a)
  - WHERE c = 3 (没有最左边的 a 和 b)
  - WHERE b = 2 AND c = 3
创建索引后，如何判断查询是否使用了索引？如何查看查询的执行计划？
- 解答： 可以通过数据库提供的执行计划（Execution Plan）工具来分析查询是否使用了索引以及如何使用。
- 不同的数据库有不同的命令或工具：
  - MySQL: 在查询语句前加上 EXPLAIN 关键字，例如 EXPLAIN SELECT * FROM users WHERE username = 'test'; 查看输出结果，关注 type (如 ref, range, index, ALL - 全表扫描), possible_keys, key (实际使用的索引), key_len, rows, Extra (如 Using index, Using where, Using filesort) 等信息。
  - SQL Server: 在查询分析器中执行查询后，选择“显示实际执行计划”或“显示估计执行计划”。图形化的执行计划会直观地显示操作类型（如 Index Seek, Index Scan, Table Scan）以及使用的索引。
- 通过分析执行计划，可以确认索引是否生效，以及索引的使用效率。
什么是索引扫描（Index Scan）和全表扫描（Table Scan）？什么是索引查找（Index Seek）？
- 解答：
  - 全表扫描 (Table Scan): 数据库逐行读取整个表的数据来寻找符合条件的记录。效率最低，尤其是在大表上。
  - 索引扫描 (Index Scan): 数据库遍历索引中的一部分或全部条目来找到匹配的记录。通常比全表扫描快，尤其当索引是覆盖索引时。范围查询 (BETWEEN, >) 经常会用到索引扫描。
  - 索引查找 (Index Seek): 数据库通过索引结构（如 B+Tree 的导航）直接精确地定位到索引中的一个或一小部分条目，然后根据这些条目找到对应的数据行。这是最高效的索引使用方式，通常用于等值查询或小范围查询。
在哪些情况下索引可能不会被使用？
- 解答：
  - 查询没有使用联合索引的最左前缀。
  - 在索引列上使用了函数或进行了计算（如 WHERE YEAR(date_col) = 2023）。
  - LIKE 语句使用了前导通配符（如 WHERE name LIKE '%keyword'），这使得无法利用索引的有序性。
  - 使用了否定条件，如 !=, NOT IN （优化器可能会选择全表扫描）。
  - 数据库统计信息过期，优化器未能选择最佳执行计划。
  - 表数据量太小，优化器判断全表扫描比走索引更快。
  - 使用了强制全表扫描的提示（Hint）。
索引对 INSERT, UPDATE, DELETE 操作有什么影响？
- 解答： 索引会降低 INSERT, UPDATE, DELETE 操作的性能。
- 每次进行这些写操作时，数据库不仅需要修改表中的实际数据行，还需要相应地更新、插入或删除相关索引中的条目，以保证索引与表数据的一致性。索引越多，写操作的开销越大。
什么是索引维护？索引碎片（Index Fragmentation）是什么？如何处理？
- 解答：
  - 索引维护： 指的是通过重建或重组织等操作来优化索引的物理存储结构，以保持其查找效率。
  - 索引碎片： 当表中频繁发生插入、更新、删除操作时，数据行的物理存储顺序与索引的逻辑顺序可能变得不一致，或者索引页之间出现空隙、乱序。这种物理上的不连续性就是索引碎片。索引碎片会导致数据库在遍历索引时需要进行更多的随机磁盘 I/O，降低性能。
  - 处理：
    - 重建索引 (Rebuild): 丢弃原有的索引结构，然后重新创建一个全新的索引。这是最彻底的碎片清理方式，通常能显著提高性能，但可能需要锁定表（取决于数据库和版本），是一个开销较大的操作。
    - 重组织索引 (Reorganize): 对索引的叶子节点进行整理和压缩，使其物理顺序更接近逻辑顺序。通常是在线操作，开销比重建小，适用于碎片程度较低的情况。
有一个慢查询，你首先会考虑哪些方面来优化，索引是其中的一步吗？
- 解答： 优化慢查询是一个系统性的过程。首先会考虑以下方面：
  1. 理解查询： 明确查询的目的、涉及的表以及数据量。
  2. 分析执行计划： 使用 EXPLAIN 或其他工具查看查询的执行计划，了解数据库是如何执行查询的（例如，是否使用了索引，使用了哪个索引，扫描了多少行，是否有文件排序等）。这是找到问题根源的关键一步。
  3. 考虑索引： 是的，索引是优化慢查询非常重要的一步。 根据执行计划分析，判断是否缺少合适的索引，或者现有索引是否被有效利用。考虑在 WHERE, JOIN, ORDER BY, GROUP BY 涉及的列上创建单列或联合索引。判断是否可以创建覆盖索引来避免回表。
  4. 优化 SQL 语句本身： 检查 SQL 语句写法是否有问题，例如避免在索引列上使用函数，简化复杂的子查询或 JOIN，减少不必要的列查询（SELECT *），优化 IN 子句等。
  5. 数据库统计信息： 确保数据库的统计信息是最新的，这有助于优化器做出正确的索引选择。
  6. 数据库设计： 考虑表结构是否合理，是否存在范式问题，是否需要反范式优化（慎用）。
  7. 硬件和配置： 如果是普遍性的性能问题，可能需要检查数据库服务器的硬件资源（CPU、内存、磁盘 I/O）和数据库的配置参数。
- 因此，分析执行计划后，考虑和优化索引是优化慢查询中必不可少且通常是首要的步骤之一。
你在实际项目中遇到过哪些索引相关的性能问题？是如何解决的？
- 解答： （这需要根据个人实际经验来回答。以下是一个示例性的回答结构，您可以填充自己的具体案例。）
  - 示例问题类型：
    - 缺少索引： 某个关键查询（例如，用于某个报表或列表页）非常慢，分析执行计划发现是全表扫描。
    - 索引选择不当： 存在索引，但优化器没有选择最高效的那个，或者选择了错误的索引。
    - 联合索引列顺序问题： 创建了联合索引，但查询没有遵循最左前缀原则，导致索引无法充分利用。
    - 过多索引： 对表创建了大量的索引，导致写操作（插入、更新）变得非常慢。
    - 索引失效： 在查询条件中对索引列使用了函数，导致索引失效。
  - 解决思路（结合上述问题19的步骤）：
    - 首先，复现问题并获取慢查询日志或直接执行查询获取执行计划。
    - 分析执行计划，定位问题是由于全表扫描、索引扫描效率低下、回表过多还是其他原因。
    - 根据分析结果制定索引优化方案：
      - 如果缺少索引，根据查询的 WHERE, JOIN, ORDER BY, GROUP BY 条件创建合适的单列或联合索引。
      - 如果索引选择不当，可能需要调整索引结构，或者考虑使用查询提示（Hint，慎用）来强制使用某个索引。
      - 如果是联合索引问题，检查查询条件是否符合最左前缀，或者考虑调整联合索引的列顺序。
      - 如果写操作慢，评估索引的必要性，删除不常用的或低效的索引。
      - 如果索引失效，尝试重写 SQL 语句，避免在索引列上使用函数。
    - 在测试环境验证优化效果，对比修改前后的执行计划和查询响应时间。
    - 谨慎部署到生产环境，并持续监控数据库性能。

面试官出题

常考题目

索引是什么结构

B树
B+树

什么时候索引会失效？

在 MySQL 数据库中，即使为表创建了索引，查询优化器也可能在某些情况下选择不使用这些索引，从而导致索引“失效”（即没有被用于加速查询）。以下是一些常见的导致 MySQL 索引失效的情况：
核心要点（可以按类别总结）：

对索引列进行操作或计算：
- “首先，最常见的是在索引列上使用了函数或者进行了表达式计算。比如 WHERE YEAR(date_col) = 2023 或者 WHERE indexed_col + 1 = 10。这样做会导致 MySQL 无法直接利用索引的有序性。”
不恰当的 LIKE 查询：
- “其次，使用 LIKE 查询时，如果通配符 % 用在了模式的开头，例如 WHERE name LIKE '%keyword'，索引也会失效，因为它无法确定搜索的起始点。”
违反最左前缀原则（针对复合索引）：
- “对于复合索引（多列索引），查询条件没有遵循最左前缀原则。例如，如果索引是 (col1, col2, col3)，但查询条件是 WHERE col2 = 'value' 或者 WHERE col3 = 'value'，索引将不会被使用，或者部分使用。”
类型不匹配导致隐式转换：
- “如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL 可能会进行隐式类型转换，这通常会导致索引失效。比如，索引列是字符串，但查询时用了数字，反之亦然。”
OR 条件的使用不当：
- “当 OR 连接的条件中，有任何一方的列没有索引，或者优化器认为全表扫描的成本更低时，整个 OR 条件可能导致索引失效。即使两边都有索引，也可能出现这种情况。”
优化器的选择与数据特性：
- “还有一些情况是 MySQL 优化器基于成本的判断。例如：
  - 表数据量过小，全表扫描可能比走索引更快。
  - 索引列的选择性过低（比如性别列），通过索引筛选不出多少数据，优化器可能放弃索引。
  - 统计信息不准确或过时，导致优化器做出错误的判断。”
其他可能影响的情况（可选，如果时间允许或被追问）：
- “某些情况下 NOT IN、!= (不等于) 操作符也可能导致索引失效。”
- “如果 ORDER BY 的列和索引顺序不完全一致或混合升降序（特定版本和情况下）。”

结尾与诊断方法：

“总的来说，索引是否生效最终是由 MySQL 的查询优化器决定的。在实际工作中，我会使用 EXPLAIN 命令来分析查询执行计划，查看 key 列是否使用了预期的索引，以及 Extra 列是否有 Using filesort 或 Using temporary 等提示，来诊断索引是否失效以及具体原因。”

回答技巧：