MySQL 八股

1. MySQL 常用的存储引擎有哪些？InnoDB 和 MyISAM 的区别？

常用存储引擎：

• InnoDB: MySQL 5.5 及以上版本的默认引擎。支持事务、行级锁、外键，具有崩溃恢复能力（ACID）。
• MyISAM: MySQL 5.5 之前的默认引擎。不支持事务，使用表级锁，读取速度快，但不支持行级锁和外键。
• Memory: 将数据存储在内存中，访问速度极快，但一旦服务重启，数据会丢失。适合用于临时表或查找速度要求极高的表。
• Archive: 用于存储大量归档数据，只支持 INSERT 和 SELECT，使用 zlib 压缩数据，占用空间小。
• NDB (Cluster): 用于 MySQL Cluster 集群环境，实现数据分布式存储和高可用。

InnoDB 和 MyISAM 的核心区别：

2. 什么是索引？索引有什么优缺点？

定义：
索引是数据库中一种用于快速查询和检索数据的数据结构。它就像一本书的目录，通过目录可以快速找到对应章节的页码，而无需翻阅整本书。数据库索引通过存储特定列（或多个列）的值和其对应数据行的指针，来实现快速定位。

优点：

1. 大大加快数据检索速度：这是索引最主要的优点，可以显著减少查询的磁盘 I/O 次数。
2. 保证数据唯一性：通过创建唯一性索引或主键索引，可以保证数据库表中某列（或几列组合）的唯一性。
3. 加速表与表之间的连接：对用于连接的列创建索引，可以显著提高 JOIN 操作的速度。
4. 减少排序和分组的时间：如果查询中的 ORDER BY 或 GROUP BY 子句的列有索引，数据库可以利用索引的有序性，避免额外的排序操作。

缺点：

1. 占用磁盘空间：索引本身也是一个文件，需要占用物理存储空间。数据量越大，索引占用的空间也越大。
2. 降低写操作的速度：当对表中的数据进行增（INSERT）、删（DELETE）、改（UPDATE）时，不仅要操作数据行，还要同时更新对应的索引，这会增加写操作的时间开销。
3. 创建和维护耗时：创建索引和维护索引都需要时间，特别是当数据量很大时。

3. MySQL 索引为什么用 B+ 树，而不是 B 树或红黑树？

核心原因在于 减少磁盘 I/O 次数 和 提高范围查询效率。

为什么不选红黑树？

• 红黑树是二叉树。对于海量数据，树的深度会非常大。查询一次数据可能需要多次磁盘 I/O（每访问一个节点都可能是一次 I/O），性能极差。数据库是存储在磁盘上的，I/O 是主要性能瓶颈。

为什么不选 B 树？

• B 树是多路搜索树，相比红黑树，树的高度大大降低，减少了 I/O 次数。
• 但 B 树的 每个节点都存储了数据（键值和完整数据）。这导致每个节点能存储的键值数量变少，使得树的高度相对更高。同时，范围查询时需要进行中序遍历，效率不高。

为什么选择 B+ 树？
B+ 树是 B 树的优化版本，更适合数据库索引：

1. 更低的树高，更少的 I/O：
   • B+ 树的 非叶子节点只存储键值，不存储数据。这使得每个非叶子节点可以存储更多的键值，树的阶数更大，树的高度更矮。
   • 查询数据时，从根节点到叶子节点的路径更短，磁盘 I/O 次数更少。
2. 范围查询效率极高：
   • B+ 树的 所有数据都存储在叶子节点，并且 所有叶子节点形成一个有序的双向链表。
   • 当进行范围查询（如 BETWEEN, >, <）时，只需定位到起始的叶子节点，然后沿着链表顺序遍历即可，无需回溯或中序遍历，效率非常高。
3. 查询性能稳定：
   • 任何数据的查询都必须从根节点走到叶子节点，查询路径长度稳定，而 B 树可能在非叶子节点就找到数据，导致查询性能不稳定。

4. 什么是聚簇索引和非聚簇索引？

聚簇索引：

• 定义：数据行的物理存储顺序与索引的逻辑顺序相同。索引的叶子节点直接包含了完整的行数据。

• 特点：

  • 一个表只能有一个聚簇索引。
  • 在 InnoDB 中，主键就是聚簇索引。如果没有显式定义主键，MySQL 会选择第一个唯一且非空的索引作为聚簇索引。如果都没有，InnoDB 会自动创建一个隐藏的 6 字节主键作为聚簇索引。
  • 数据本身就是索引的一部分，因此查询速度非常快。

非聚簇索引：

• 定义：索引的逻辑顺序与数据行的物理存储顺序无关。索引的叶子节点存储的是 主键的值（作为指向数据行的指针），而不是完整的行数据。

• 特点：

  • 一个表可以有多个非聚簇索引（也叫二级索引）。
  • 当通过非聚簇索引查询数据时，需要先在非聚簇索引中找到主键值，然后再用这个主键值去聚簇索引中查找完整的行数据。这个过程称为 “回表查询”。

5. 什么是覆盖索引？什么是回表查询？

回表查询：
当使用非聚簇索引进行查询时，如果查询的列（SELECT 后面的列）不完全包含在该索引中，数据库就需要先通过非聚簇索引找到主键值，然后再用主键值去聚簇索引中查找完整的行数据。这个“二次查找”的过程就是回表查询。回表查询会增加 I/O 开销，影响性能。

覆盖索引：
如果一个索引（无论是聚簇还是非聚簇）包含了查询所需的所有字段（SELECT, WHERE, ORDER BY, GROUP BY 子句中的列），那么数据库只需要扫描这个索引就可以获取到所有需要的数据，而无需进行回表查询。这种索引就是覆盖索引。

优点：

• 避免回表：极大地减少了 I/O 操作，是重要的 SQL 优化手段。
• 索引通常比数据行小：扫描索引比扫描数据表更快。

示例：
假设表 user 有 (id, name, age) 三个字段，id 是主键，在 name 上创建了二级索引。

• SELECT id, name FROM user WHERE name = '张三'; --> 覆盖索引。因为 name 索引的叶子节点存储了 id 和 name，查询所需字段都在索引中，无需回表。
• SELECT * FROM user WHERE name = '张三'; --> 回表查询。因为 name 索引没有 age 字段，需要回表获取完整数据。

6. 什么情况下索引会失效？

索引失效意味着查询优化器决定不走索引，而是进行全表扫描。常见情况有：

1. 违反最左前缀原则：对于联合索引 (a, b, c)，如果查询条件中没有 a，则后面的索引 b, c 都会失效。
2. 在索引列上进行了计算或函数操作：如 WHERE YEAR(create_time) = 2023 或 WHERE id - 1 = 10。这会导致索引列不再是原始值，无法使用索引。
3. 使用了 LIKE 以通配符开头：如 WHERE name LIKE '%三'。数据库无法确定匹配的开头，只能全表扫描。但 LIKE '三%' 可以使用索引。
4. 类型不匹配：索引列是字符串类型，但查询时用了数字。如 name 是 varchar，查询条件是 WHERE name = 123（应为 WHERE name = '123'）。MySQL 会进行隐式类型转换，可能导致索引失效。
5. 使用 OR 连接：如果 OR 两边的条件中，有一个列没有索引，那么另一个列的索引也可能失效。
6. 使用 <>, !=, NOT IN：这些操作符通常会导致索引失效。
7. WHERE 子句中对索引列使用 IS NULL 或 IS NOT NULL：在某些情况下可能失效，但现代 MySQL 版本对此有优化，通常可以使用索引。
8. 优化器判断全表扫描更快：当查询结果集占表数据的比例非常大时（如超过 20%-30%），优化器可能认为全表扫描比回表查询更高效。

7. 数据库事务的 ACID 特性是什么？

ACID 是数据库事务必须具备的四个特性，保证了事务的可靠性。

• 原子性：一个事务中的所有操作，要么 全部成功，要么 全部失败。不会出现只执行一部分的情况。通过 Undo Log 实现。
• 一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。数据从一个合法状态转变为另一个合法状态。这是事务的最终目标，由原子性、隔离性、持久性共同保证。
• 隔离性：多个并发事务之间是相互隔离的，一个事务的执行不应被其他事务干扰。即使多个事务同时操作同一数据，每个事务都感觉不到其他事务的存在。通过 锁 和 MVCC 实现。
• 持久性：一旦事务提交，它对数据库的修改就是 永久性 的。即使系统发生崩溃（如断电、宕机），修改的数据也不会丢失。通过 Redo Log 实现。

8. 数据库的隔离级别有哪些？分别解决了什么问题？

SQL 标准定义了四种隔离级别，级别越高，隔离性越强，但并发性能越低。

问题解释：

• 脏读：读到了另一个事务 未提交 的数据。如果那个事务回滚了，读到的数据就是无效的。
• 不可重复读：在同一个事务内，多次读取同一数据，得到了 不同的结果（因为另一个事务在两次读取之间修改并提交了该数据）。
• 幻读：在同一个事务内，多次执行相同的查询，返回的 行数不同（因为另一个事务在两次查询之间插入或删除了数据）。

9. MySQL 默认的隔离级别是什么？

可重复读。

在 InnoDB 存储引擎中，REPEATABLE-READ 隔离级别通过 MVCC（多版本并发控制） 和 间隙锁 的机制，不仅避免了脏读和不可重复读，还在很大程度上避免了幻读。

10. 什么是 MVCC（多版本并发控制）？它是如何实现的？

定义：
MVCC (Multi-Version Concurrency Control) 是一种并发控制的方法，它通过保存数据在某个时间点的 快照版，而不是加锁，来实现“读-写”和“写-读”冲突的并发执行。这使得读操作不加锁，提高了并发性能。

实现原理（以 InnoDB 为例）：
InnoDB 的每行记录背后，除了我们看到的字段，还有几个隐藏的列：

• DB_TRX_ID：最近一次修改（插入或更新）该行的事务 ID。
• DB_ROLL_PTR：一个回滚指针，指向该行数据上一个版本的 undo log 记录。
• DB_ROW_ID：隐藏的行 ID（当表没有主键时，InnoDB 会用它生成聚簇索引）。

MVCC 的核心是 Read View（读视图）：

1. 当一个事务启动时（在 REPEATABLE-READ 隔离级别下，是事务中第一次执行 SELECT 时），InnoDB 会为该事务创建一个 Read View。

2. Read View 包含了当前系统中所有 活跃（未提交） 的事务 ID 列表，以及一个创建该视图时系统最大的事务 ID。

3. 当该事务读取某一行数据时，会将该行的 DB_TRX_ID 与 Read View

进行比较，判断该行版本对当前事务是否可见：

• 如果 DB_TRX_ID 小于 Read View 中的最小事务 ID，说明修改该行的事务在当前事务启动前已提交，可见。

• 如果 DB_TRX_ID 大于 Read View 中的最大事务 ID，说明修改该行的事务在当前事务启动后才开启，不可见。

• 如果
  DB_TRX_ID 在 Read View的事务 ID 列表范围内，则需要进一步判断：

  • 如果 DB_TRX_ID 在活跃列表中，说明修改该行的事务还未提交，不可见。
  • 如果 DB_TRX_ID 不在活跃列表中，说明修改该行的事务已提交，可见。

4. 如果当前行版本不可见，InnoDB 就会通过 DB_ROLL_PTR 指针去 undo log 中查找上一个版本的数据，重复上述可见性判断，直到找到一个可见的版本为止。

通过这种机制，每个事务看到的数据都是它在启动时刻的一个快照，从而实现了不加锁的读，避免了脏读和不可重复读。

11. 数据库锁有哪些？行锁、表锁、间隙锁的区别？

从锁的粒度划分：

• 表锁：锁定整个表。开销小，加锁快，但并发度低。
• 行锁：锁定一行数据。开销大，加锁慢，但并发度高。
• 页锁：锁定一页数据（一页包含多行）。开销和加锁速度介于表锁和行锁之间。

从锁的模式划分：

• 共享锁：又称读锁。一个事务获取了 S 锁后，其他事务可以继续获取 S 锁，但不能获取 X 锁。多个事务可以并发读取同一数据。
• 排他锁：又称写锁。一个事务获取了 X 锁后，其他事务既不能获取 S 锁，也不能获取 X 锁。保证了写操作的互斥。
• 意向锁：InnoDB 特有的表级锁，用于表明事务打算在表的某个行上获取共享锁或排他锁。意向锁之间是兼容的，但与表级 S/X 锁互斥。它的存在是为了让表锁和行锁可以共存，提高效率。

行锁、表锁、间隙锁的区别：

• 表锁：
  • 作用范围：整个表。
  • 开销：低。
  • 并发性：差。一个事务对表加锁，其他事务对整个表的操作都会被阻塞。
  • 引擎：MyISAM 只支持表锁。InnoDB 在特定情况下也会使用表锁（如 ALTER TABLE）。
• 行锁：
  • 作用范围：表中的单行数据。
  • 开销：高。
  • 并发性：好。不同事务可以并发修改同一张表中的不同行。
  • 引擎：InnoDB 的主要特性。
• 间隙锁：
  • 作用范围：索引记录之间的 “间隙”，或者第一条记录之前或最后一条记录之后的间隙。它锁定的是一个范围，而不是具体的记录。
  • 目的：为了 防止幻读。在 REPEATABLE-READ 隔离级别下，InnoDB 会使用间隙锁。例如，一个事务查询 id > 100 的数据并加锁，另一个事务就无法插入 id = 101 的新记录，因为 100 和下一个存在的 id 之间的间隙被锁定了。
  • 特性：间隙锁之间不冲突，多个事务可以同时持有同一个间隙的间隙锁。

12. 如何进行 SQL 优化？EXPLAIN 命令的结果字段（如 type, key, Extra）分别代表什么？

SQL 优化策略：

1. 使用 EXPLAIN 分析查询：这是优化的第一步，查看 SQL 的执行计划。

2. 避免 SELECT \*：只查询需要的列，可以减少网络传输和内存消耗，并更容易利用覆盖索引。

3. 合理创建索引 ：

   • 为 WHERE, JOIN, ORDER BY, GROUP BY 的列创建索引。
   • 遵循最左前缀原则创建联合索引。
   • 尽量使用覆盖索引。

4. 避免索引失效：参考第 6 题，避免在索引列上做计算、使用函数、LIKE '%xx' 等。

5. 优化 JOIN 查询：

   • 确保连接字段类型相同且都有索引。
   • 小表驱动大表（用结果集小的表去驱动结果集大的表）。

6. 优化子查询：尽量将子查询转换为 JOIN，因为 JOIN 的优化器通常比子查询更智能。

7. 使用 LIMIT 分页：避免一次性查询大量数据。

8. 数据类型优化：选择合适的数据类型，能用 TINYINT 就不用 INT，能用 VARCHAR 就不用 CHAR。

EXPLAIN 关键字段解释：

• id：查询的执行顺序，id 越大优先级越高，id 相同则从上往下执行。

• select_type：查询类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、UNION（联合查询）。

• type：

  最重要的字段之一，表示访问类型，性能从好到差依次为：

  • system > const > eq_ref > ref > range > index > ALL
  • ALL：全表扫描，最差。
  • index：索引全扫描，比 ALL 好，但依然很差。
  • range：索引范围扫描，如 BETWEEN, >, <。
  • ref：非唯一索引扫描，返回匹配单个值的所有行。
  • eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配（常见于主键或唯一索引的 JOIN）。
  • const：通过主键或唯一索引命中，最多返回一行，优化器将其视为常量。
  • system：表只有一行（系统表），是 const 的特例。

• possible_keys：可能用到的索引。

• key：最重要的字段之一，实际使用的索引。如果为 NULL，表示没有使用索引。

• key_len：使用的索引字节数，可以判断联合索引使用了多少列。

• rows：预估需要扫描的行数，越少越好。

• Extra：额外信息，非常重要：

  • Using filesort：坏。ORDER BY 没有用到索引，需要额外排序。
  • Using temporary：坏。使用了临时表（常见于 GROUP BY, DISTINCT）。
  • Using index：好。使用了覆盖索引，避免了回表。
  • Using where：使用了 WHERE 过滤，但没用到索引或索引过滤效果不佳。
  • Using index condition：使用了索引下推优化（ICP），在存储引擎层进行索引过滤。

13. 什么是主从复制？它是如何实现的？

定义：
主从复制是一种数据同步技术，将一台 MySQL 服务器的数据（主库，Master）实时复制到一台或多台其他服务器（从库，Slave）。

作用：

• 读写分离：主库负责写操作，从库负责读操作，分摊服务器压力。
• 数据备份与容灾：从库是主库的实时备份，当主库宕机时，可以快速切换到从库提供服务。
• 高可用：配合哨兵或集群方案，实现故障自动转移。

实现原理（基于二进制日志 binlog 的异步复制）：

1. 主库操作：
   • 主库上的所有数据变更操作（INSERT, UPDATE, DELETE 等）都会被记录到 二进制日志 中。
2. 从库连接主库：
   • 从库启动一个 I/O 线程，连接到主库。
   • I/O 线程向主库请求从指定位置（或从最新的位置）开始的 binlog 日志。
3. 主库发送日志：
   • 主库接收到请求后，通过一个 Binlog Dump 线程 读取本地的 binlog，并将其发送给从库的 I/O 线程。
4. 从库接收并写入日志：
   • 从库的 I/O 线程接收到 binlog 后，将其写入到自己的 中继日志 中。
5. 从库重放日志：
   • 从库同时启动一个 SQL 线程。
   • SQL 线程读取中继日志中的事件，并在本地重新执行一遍，从而使得从库的数据与主库保持一致。

这个过程是 异步 的，即主库不等待从库确认就提交事务，所以存在主从数据延迟的可能。

14. 什么是分库分表？

定义：
分库分表是一种将单个大数据库（或大表）拆分成多个小数据库（或小表）的技术，用于解决单机数据库的性能瓶颈和存储上限问题。

为什么要分库分表？

• 性能瓶颈：单机数据库的 CPU、内存、I/O、网络连接数达到上限。
• 存储瓶颈：单机磁盘空间无法容纳海量数据。
• 高并发瓶颈：单机数据库无法支撑极高的并发请求。

分类：

1. 分库：将一个数据库中的不同表拆分到多个数据库（通常部署在不同服务器上）。
   • 垂直分库：按业务模块拆分。例如，将用户相关的表（user, user_profile）放到 user_db，将订单相关的表（order, order_item）放到 order_db。解决业务耦合和单机性能问题。
   • 水平分库：将同一个表的数据按某种规则（如 user_id 的哈希值）拆分到不同的数据库中。解决单表数据量过大的问题。
2. 分表：将一张大表拆分成多张小表。
   • 垂直分表：将一张表的 列 拆分。例如，将 user 表中不常用的大字段（如 resume）拆分到 user_extend 表中。减少单行数据大小，提高缓存命中率。
   • 水平分表：将一张表的 行 按某种规则（如 id 范围、id 取模）拆分到多张结构相同的表中。这是最常用的分表方式，直接解决单表数据量过大的问题。

核心概念：分片键
分片键是决定数据路由到哪个库或哪个表的字段（如 user_id, order_id）。选择一个好的分片键至关重要，它需要保证数据均匀分布，并且便于查询。