牛马跳槽面试篇第一期

本文最后更新于：几秒前

牛马跳槽面试篇第一期

一个人从一个“槽”跳到另一个“槽”的全过程。

590.MySQL 的存储引擎有哪些？它们之间有什么区别？【中等】

MySQL 支持多种存储引擎，每种引擎都针对特定的工作负载进行了优化，具有不同的特性、性能和限制。了解它们之间的区别对于设计高效、可靠的数据库至关重要。

以下是最常用和重要的存储引擎及其核心区别：

  1. InnoDB

    • **默认引擎 (MySQL 5.5.5 之后)**： 现在是新建表的默认选择。
    • 核心特性:
      • ACID 事务支持： 完全支持原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)，是处理需要严格数据一致性的应用（如金融系统、订单管理）的首选。
      • 行级锁定： 只锁定被访问的行，而不是整个表。这极大地提高了在高并发环境下的读写性能（尤其是写操作多的场景），减少了锁争用。
      • 外键约束： 支持关系数据库的参照完整性，确保表之间数据的一致性和有效性。
      • 崩溃恢复： 具有强大的崩溃后自动恢复能力，能最大程度地保证数据不丢失。
      • 聚簇索引： 主键索引（或第一个非空唯一索引）的叶子节点直接存储行数据。这通常使基于主键的查询非常快。
      • **MVCC (多版本并发控制)**： 通过保存数据在某个时间点的快照来实现非锁定读（一致性读），提高了并发读性能。
    • 适用场景： 需要事务、高并发读写、数据完整性要求高、需要外键约束的 OLTP (联机事务处理) 应用。绝大多数现代 Web 应用的首选引擎。

  2. MyISAM

    • **历史默认引擎 (MySQL 5.5.5 之前)**： 在 InnoDB 成为默认之前广泛使用。
    • 核心特性:
      • 表级锁定： 执行写操作（INSERT, UPDATE, DELETE）时会锁定整个表。这在高并发写入环境下会成为严重的性能瓶颈。
      • 无事务支持： 不支持 ACID 事务。崩溃后数据损坏风险相对较高，恢复可能更复杂。
      • 无外键约束： 不支持外键。
      • **全文索引 (FULLTEXT)**： 在较早版本中，MyISAM 是唯一支持 FULLTEXT 索引的引擎（现在 InnoDB 也支持）。
      • 高速读取： 对于只读或读多写少且不需要事务的场景，简单查询可能比 InnoDB 稍快（尤其是在 COUNT(*) 等操作上，因为 MyISAM 单独存储了行数）。
      • 压缩表： 支持只读压缩表，可以节省空间。
    • 适用场景： 主要读操作、不需要事务、对并发写要求不高、需要全文索引（旧版本）、数据仓库/报表的只读表、日志表。在现代应用中，除非有特定原因（如遗留系统或特定只读场景），否则通常不推荐使用 MyISAM。

  3. MEMORY (HEAP)

    • 核心特性:
      • 内存存储： 所有数据存储在 RAM 中。速度极快。
      • 表级锁定。
      • 无事务支持。
      • 无持久性： 服务器重启或崩溃后，表中的所有数据都会丢失。表结构会保留。
      • **哈希索引 (默认)**： 默认使用哈希索引，非常快于等值查询。也支持 B-Tree 索引。
    • 适用场景： 存储临时数据、会话数据、缓存、查找表或中间结果集。需要非常快的访问速度，且数据丢失可以接受或可重建的场景。不适合存储需要持久化的关键数据。

  4. ARCHIVE

    • 核心特性:
      • 高压缩比： 专门为存储和检索大量很少被引用的历史、归档数据而设计，压缩率非常高。
      • 仅支持 INSERT 和 SELECT： 不支持 UPDATE、DELETE、REPLACE（MySQL 5.0 后支持 DELETE）。不支持索引（MySQL 5.0 后支持 AUTO_INCREMENT 列上的索引）。
      • **行级锁定 (仅 INSERT)**。
      • 无事务支持。
    • 适用场景： 存储海量的、几乎只追加写入且很少查询的历史日志、审计记录、归档数据。目标是节省大量磁盘空间。

  5. CSV

    • 核心特性:
      • 纯文本存储： 将数据存储在服务器的 .CSV 格式的文本文件中。
      • 无索引： 没有索引支持，意味着全表扫描是唯一方式。
      • 所有列不能为 NULL。
    • 适用场景： 作为数据交换格式，方便外部脚本（如 Excel, Python, Perl）直接读写。用于简单的日志记录。不适合需要索引或高效查询的生产应用。

  6. BLACKHOLE

    • 核心特性:
      • “黑洞”： 接受写入的数据（INSERT, UPDATE），但不存储任何数据。SELECT 总是返回空集。
      • 会记录二进制日志： 写入操作会被记录到二进制日志中，因此可以复制到从库。
    • 适用场景： 用于验证转储文件语法、测量二进制日志开销、作为复杂的复制拓扑结构中的中继或过滤器（只复制日志不存数据）。

  7. MERGE (MRG_MYISAM)

    • 核心特性:
      • 逻辑表： 本身不存储数据。它将多个结构相同的 MyISAM 表逻辑地组合成一个表。
      • 依赖 MyISAM： 底层表必须是 MyISAM。
      • 表级锁定。
    • 适用场景： 用于分表管理（例如按时间分表），提供对一组表的统一视图。在现代 MySQL 中，分区功能通常是更好的替代方案。

主要区别总结表：

特性 InnoDB MyISAM MEMORY (HEAP) ARCHIVE CSV BLACKHOLE
默认引擎 (>=5.5.5) ✅ ❌ ❌ ❌ ❌ ❌
事务 (ACID) ✅ ❌ ❌ ❌ ❌ ❌
锁定粒度 行级锁 表级锁 表级锁 行级锁 (仅 INSERT) 表级锁 表级锁
外键约束 ✅ ❌ ❌ ❌ ❌ ❌
崩溃恢复 ✅ (强) ❌ (弱) ❌ (数据丢失) ❌ (弱) ❌ (弱) N/A
MVCC ✅ ❌ ❌ ❌ ❌ ❌
存储位置 磁盘 (数据文件 + 日志) 磁盘 (.MYD, .MYI) 内存 (RAM) 磁盘 (高压缩) 磁盘 (.CSV 文件) 不存储
持久性 ✅ ✅ ❌ (重启丢失) ✅ (但仅追加) ✅ ❌ (不存数据)
索引类型 B+Tree (聚簇) B+Tree (非聚簇) Hash (默认) / B-Tree 有限 (仅 AUTO_INCREMENT) ❌ ❌
全文索引 (FULLTEXT) ✅ (>=5.6) ✅ ❌ ❌ ❌ ❌
空间索引 (GIS) ✅ (>=5.7) ✅ ❌ ❌ ❌ ❌
压缩 表压缩 (可选) 行压缩 / 只读压缩表 ❌ ✅ (极高压缩) ❌ ❌
主要优势 事务、并发、数据完整性、恢复 简单读快、全文(旧版)、COUNT(*) 内存速度 极高压缩率 文本格式交换 日志记录/复制中继
主要劣势 相对更复杂、占用空间稍大 表锁、无事务、崩溃恢复差 数据易失、表锁、容量受限 仅 INSERT/SELECT、无索引 无索引、慢查询 不存储数据
典型场景 OLTP, 通用应用 只读/读多写少(不推荐新项目) 缓存、临时表 海量历史归档 数据导入导出 复制拓扑、日志测试

如何选择？

  1. 需要事务、数据完整性、高并发写入？ => **InnoDB (绝大多数情况的首选)**。
  2. 纯只读或读多写少且不需要事务，且对简单 COUNT(*) 有极致要求？ => 可考虑 MyISAM (但务必权衡崩溃风险，通常仍推荐 InnoDB)。
  3. 需要极高压缩比存储海量历史归档数据，只追加几乎不查询？ => ARCHIVE。
  4. 需要极快访问速度，数据可重建或临时？ => MEMORY。
  5. 需要直接操作 CSV 文件进行数据交换？ => CSV。
  6. 在复制链中做特殊中转？ => BLACKHOLE。

结论：

  • InnoDB 是 MySQL 现代版本的默认和推荐引擎，因为它提供了事务安全性、行级锁定、外键支持和强大的崩溃恢复能力，满足了绝大多数应用程序的需求。
  • 其他引擎在特定、狭窄的用例中可能有价值（如 MEMORY 用于临时表、ARCHIVE 用于极致压缩归档），但选择它们需要充分理解其限制。
  • 强烈建议新项目默认使用 InnoDB，仅在经过严格评估并确认其他引擎能带来显著且必要的优势时，才考虑使用替代引擎。避免使用 MyISAM 作为新表的默认选择。

592.MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？【中等】

在 MySQL InnoDB 引擎中，聚簇索引 (Clustered Index) 和 非聚簇索引 (Non-clustered Index / Secondary Index / 二级索引) 是两种核心的索引结构，它们在数据存储方式、查找效率和使用场景上有本质区别：

核心区别总结：

特性 聚簇索引 (Clustered Index) 非聚簇索引 (Secondary Index)
本质 表数据存储的方式。索引即数据。 指向聚簇索引键的指针。独立于数据存储。
数量 每个表有且仅有一个 (由主键或唯一约束隐式创建) 每个表可以有多个。
存储内容 叶子节点存储完整的行数据。 叶子节点存储该索引的列值 + 对应行的主键值。
物理顺序 行数据在磁盘上按聚簇索引键值排序存储。 索引自身按键值排序，但数据行不按此排序。
查找流程 直接通过索引找到数据行。 1. 在二级索引中找到主键值。
2. 用主键值去聚簇索引中查找完整数据行 (回表)。
访问速度 非常快 (只需一次索引查找)。 相对较慢 (通常需要两次查找：二级索引 + 回表)。
依赖关系 是基础，表数据按此组织。 依赖聚簇索引 来定位完整数据行。
典型代表 **主键索引 (PRIMARY KEY)**。 普通索引 (INDEX/KEY)、唯一索引 (UNIQUE KEY)。

深入解析：

  1. 聚簇索引 (Clustered Index)

    • 定义与创建：
      • 每个 InnoDB 表必须有且只有一个聚簇索引。
      • 如果你定义了 PRIMARY KEY，它自动成为聚簇索引。
      • 如果没有定义主键，InnoDB 会选择一个第一个所有列都非空的 UNIQUE KEY 作为聚簇索引。
      • 如果既没有主键也没有合适的唯一键，InnoDB 会在内部生成一个隐藏的、名为 GEN_CLUST_INDEX 的 6 字节行 ID (DB_ROW_ID) 作为聚簇索引。
    • 数据结构：
      • 是一棵 B+ 树。
      • 叶子节点 (Leaf Pages)：直接存储完整的行数据。找到叶子节点就找到了该行的所有列。
      • **非叶子节点 (Non-Leaf Pages)**：存储索引键值（主键值或行 ID）和指向子节点（下层页）的指针。
    • 数据存储：
      • 表的所有数据行都物理地存储在聚簇索引的叶子节点上，并按聚簇索引键（主键）的顺序进行排序存储。相邻的主键值对应的行在磁盘上很可能也是相邻的（尤其是在顺序插入时）。
    • 查找过程：给定一个主键值，InnoDB 直接在聚簇索引的 B+ 树中进行查找，定位到叶子节点即可获得整行数据。**只需一次磁盘 I/O (理想情况下)**。
    • 优点：
      • 基于主键的查询速度极快（只需查找一次 B+ 树）。
      • 范围查询 (BETWEEN, >, < 等) 在主键列上效率高，因为相邻的数据在物理上也是相邻的。
      • 数据访问局部性好。

  2. 非聚簇索引 / 二级索引 (Secondary Index)

    • 定义与创建：开发者显式创建的索引 (CREATE INDEX ..., CREATE UNIQUE INDEX ..., 定义 UNIQUE 约束时自动创建的索引)。
    • 数据结构：
      • 同样是一棵 B+ 树。
      • 叶子节点 (Leaf Pages)：存储的是 该二级索引的列值 + 对应行的主键值。不存储完整的行数据。
      • **非叶子节点 (Non-Leaf Pages)**：存储索引键值（二级索引的列值）和指向子节点（下层页）的指针。
    • 数据存储：二级索引本身独立于表数据存储。它的叶子节点只包含索引列和主键值。
    • **查找过程 (回表 - Bookmark Lookup)**：
      1. 在二级索引的 B+ 树中查找给定的索引列值。
      2. 找到对应的叶子节点，获取该行记录的主键值。
      3. 拿着这个主键值，回到聚簇索引的 B+ 树中再查找一次。
      4. 在聚簇索引中找到对应的叶子节点，获取完整的行数据。
      • 这个过程第二步的“回到聚簇索引查找”就是 回表 (Bookmark Lookup)。通常需要两次磁盘 I/O（一次查二级索引，一次查聚簇索引）。
    • 优点：
      • 加速基于非主键列的查询 (WHERE, ORDER BY, GROUP BY, JOIN)。
      • 可以创建多个以满足不同的查询需求。
    • 关键点：
      • 依赖主键：二级索引的效率高度依赖于主键的大小和类型。因为二级索引叶子节点存储的是主键值：
        • 主键过大（如很长的字符串）会导致二级索引占用空间显著增大。
        • 主键最好选择短小、有序的类型（如自增整数）。
      • 覆盖索引 (Covering Index)：如果一个查询 所需的所有列 都包含在某个二级索引的键值中（包括叶子节点存储的主键值），那么 InnoDB 就不需要回表去聚簇索引查数据，直接从二级索引的叶子节点就能获取结果。这能极大提升查询速度。例如：
        1
        2
        3
        4
        -- 假设在 (name) 上有一个二级索引
        SELECT id, name FROM users WHERE name = 'Alice'; -- 需要回表 (因为 SELECT id, name, 二级索引叶子节点有 name 和 id(主键))
        -- ✅ 覆盖索引生效！
        SELECT id FROM users WHERE name = 'Alice'; -- 不需要回表 (id 在二级索引叶子节点)

为什么理解这个区别至关重要？

  1. 主键设计：主键的选择（类型、大小）不仅影响聚簇索引本身，还深刻影响所有二级索引的大小和效率。优先选择短小、有序、不可变的主键（如 BIGINT AUTO_INCREMENT）。
  2. 查询性能优化：
    • 尽量使用主键进行查询（最快）。
    • 为频繁查询的 WHERE、JOIN、ORDER BY、GROUP BY 列创建合适的二级索引。
    • 善用覆盖索引：通过精心设计索引包含查询需要的所有列，避免昂贵的回表操作，这是提升查询性能的关键手段之一。
    • 避免 SELECT *，只查询需要的列，增加覆盖索引生效的机会。
  3. 索引维护成本：二级索引需要存储额外的数据（索引列+主键值），并在数据插入、更新、删除时需要维护（更新 B+ 树结构）。创建索引需权衡读写性能。
  4. 理解执行计划：查看 EXPLAIN 输出时，如果看到 Using index 通常表示使用了覆盖索引（性能好），而如果看到 Using index condition 或没有 Using index 则可能需要回表或全表扫描（可能需要优化）。

总结：

  • 聚簇索引是 InnoDB 表数据的物理组织形式，决定了数据行在磁盘上的存储顺序，其叶子节点包含完整行数据。主键即聚簇索引。
  • 二级索引是辅助查找结构，其叶子节点只存储索引列值和对应的主键值，必须通过主键值回表到聚簇索引才能获取完整行数据。
  • 回表操作是二级索引查询的主要开销，覆盖索引是避免回表、提升性能的核心优化技术。
  • 合理设计主键和二级索引（尤其是利用覆盖索引）是优化 InnoDB 性能的关键。

591.MySQL 的索引类型有哪些？【简单】

MySQL 支持多种索引类型，每种类型针对不同的查询场景和数据特性进行优化。以下是主要的索引类型及其核心特性和适用场景：

一、按数据结构分类（底层实现）

  1. B+Tree 索引

    • 最常用的索引类型（InnoDB/MyISAM 默认）。
    • 特点：
      • 多路平衡搜索树，适合范围查询和排序。
      • 数据存储在叶子节点，非叶子节点只存键值和指针。
      • 叶子节点通过指针串联，支持高效的范围扫描。
    • 适用场景：
      • WHERE 条件（=, >, <, BETWEEN）。
      • ORDER BY 和 GROUP BY 操作。
      • 覆盖索引查询。

  2. 哈希索引 (Hash Index)

    • 特点：
      • 基于哈希表实现，仅支持精确等值查询（= 或 IN）。
      • 查询时间复杂度接近 O(1)。
      • 不支持范围查询、排序或部分匹配。
    • 适用场景：
      • 内存表（MEMORY 引擎默认索引）。
      • InnoDB 的自适应哈希索引（内部自动优化频繁访问的索引页）。
    • 限制：
      • 仅适用于等值查询，无法优化 LIKE '%abc' 或范围查询。

  3. 全文索引 (FULLTEXT Index)

    • 特点：
      • 专为文本搜索设计，支持自然语言搜索和关键词匹配。
      • 基于倒排索引（存储单词到文档的映射）。
    • 适用场景：
      • 对大文本字段（如 TEXT、VARCHAR）进行关键词搜索。
      • 支持布尔搜索模式（MATCH() AGAINST('+apple -banana' IN BOOLEAN MODE)）。
    • 引擎支持：
      • InnoDB（MySQL 5.6+）、MyISAM。

  4. 空间索引 (R-Tree / SPATIAL Index)

    • 特点：
      • 用于地理空间数据（如经纬度、多边形）。
      • 基于 R-Tree 数据结构。
    • 适用场景：
      • 地理位置查询（ST_Distance()、ST_Contains()）。
    • 引擎支持：
      • MyISAM（历史支持）、InnoDB（MySQL 5.7+）。

二、按逻辑功能分类（应用场景）

  1. 主键索引 (Primary Key Index)

    • 特殊的唯一索引，不允许 NULL 值。
    • InnoDB 中即聚簇索引，直接关联行数据存储。
    • 每个表只能有一个主键索引。

  2. 唯一索引 (Unique Index)

    • 确保索引列的值唯一（允许 NULL 值，但最多一个 NULL）。
    • 查询效率高，常用于业务唯一性约束（如用户名、邮箱）。

  3. 普通索引 (Standard / Non-Unique Index)

    • 最基本的索引类型，无唯一性约束。
    • 单纯加速查询，可重复创建多个。

  4. 组合索引 (Composite Index / Multi-Column Index)

    • 在多个列上建立的索引（最多 16 列）。
    • 核心规则：最左前缀匹配原则：
      • 索引 (col1, col2, col3) 可优化：
        • WHERE col1 = ?
        • WHERE col1 = ? AND col2 = ?
        • WHERE col1 = ? AND col2 = ? AND col3 = ?
      • 无法优化：
        • WHERE col2 = ?（跳过第一列）
        • WHERE col1 LIKE '%abc'（模糊匹配前缀）

  5. 前缀索引 (Prefix Index)

    • 对文本列前 N 个字符创建索引（如 INDEX(column(10))）。
    • 适用场景：
      • 超长文本（如 VARCHAR(2000)）节省索引空间。
    • 缺点：
      • 无法用于 ORDER BY 或覆盖索引。

三、特殊索引类型

  1. 覆盖索引 (Covering Index)

    • 不是独立的索引类型，而是一种查询优化技术。
    • 当查询的所有字段都包含在某个索引中时，直接返回索引数据，避免回表。
    • 示例：
      1
      2
      3
      -- 表结构: id (PK), name, age
      -- 索引: INDEX(name, age)
      SELECT name, age FROM users WHERE name = 'Alice'; -- ✅ 覆盖索引生效

  2. 自适应哈希索引 (Adaptive Hash Index)

    • InnoDB 内部自动创建的哈希索引（无需用户干预）。
    • 对频繁访问的 B+Tree 索引页构建哈希缓存，加速等值查询。

四、索引选择与注意事项

  1. 索引失效的常见场景：

    • 对索引列进行函数操作（WHERE UPPER(name) = 'ALICE'）。
    • 使用 !=、NOT IN 或 OR（部分优化器可能失效）。
    • 隐式类型转换（如字符串列用数字查询）。
    • 未遵循最左前缀原则（组合索引）。

  2. 索引设计原则：

    • 高频查询字段优先建索引。
    • 区分度高的列（如用户ID）比区分度低的列（如性别）更适合索引。
    • 避免过度索引：索引增加写操作开销（插入/更新需维护索引）。

  3. 查看索引使用情况：

    1
    2
    EXPLAIN SELECT * FROM table WHERE condition; -- 分析查询计划
    SHOW INDEX FROM table_name;                  -- 查看表索引信息

总结：索引类型对比表

索引类型 **数据结构 支持查询 典型场景
B+Tree B+Tree 等值、范围、排序 99% 的常规查询
哈希索引 哈希表 仅等值查询 内存表、自适应哈希优化
全文索引 倒排索引 文本关键词搜索 文章内容搜索
空间索引 R-Tree 地理位置关系 GIS 系统
主键/唯一索引 B+Tree 等值查询 + 唯一约束 主键、业务唯一字段
组合索引 B+Tree 多列条件 + 最左前缀 复合条件查询（如 WHERE a AND b）
前缀索引 B+Tree 前缀匹配（牺牲精度） 超长文本字段

合理选择索引类型是数据库性能优化的核心，需结合数据特征、查询模式和存储引擎特性综合设计。

牛马跳槽
#MySQL
牛马跳槽面试篇第一期
http://example.com/2025/06/25/牛马跳槽面试篇第一期/
作者
crush
发布于
2025年6月25日
更新于
2025年6月25日
许可协议