MySQL数据库八股文

MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

聚簇索引：是将数据和索引放在一起，索引的叶子结点存储到就是完整的数据行，一个表只能有一个聚簇索引，一般就是主键索引

非聚簇索引：数据和索引是分开存储，索引的叶子结点存储对应数据的主键值和索引列，如果想要获取完整数据行，需要根据这个主键值，去聚簇索引中找到完整的数据，这个过程叫“回表”

比喻：

假设一本书的目录和索引是连在一起的，当你翻到目录的某一页，就能读到正文的内容，就是聚簇索引，而非聚簇索引就是，另外有一个关键字索引表，通过索引表，找到关键字对应的页码，然后翻到那页才能读到内容。

其他必备知识：

1. MySQL InnoDB 引擎中的索引结构均是B+树，而B+树的非叶子结点仅存储索引，叶子结点包含所有数据。
2. 聚簇索引直接就可以查询到完整数据，查询效率高
3. 非聚簇索引通过索引指向数据，更灵活

---

MySQL 的存储引擎有哪些？它们之间有什么区别？

MySQL的存储引擎常见的有三种，分别是InnoDB、MyISAM和Memory

其中InnoDB支持事务、行级锁和外键，使用聚簇索引，而且是MySQL5.5之后版本的默认存储引擎，它的适用场景为：需要事务支持的系统（如银行、电商等）以及数据一致性要求高的场景

MyISAM不支持事务和外键，而且采用的是表级锁，就会导致并发性能较差，每次写操作会锁住整个表，适用场景为：读多写少的场景（如博客、论坛）以及数据一致性要求低的场景

Memory数据存储在内存中，速度非常快，但是断电后数据会丢失，也是表级锁，适用场景为：临时存储或缓存数据

---

MySQL 的索引类型有哪些？

常见索引主要有：普通索引、唯一索引、主键索引和联合索引

基于InnoDB存储引擎的有：聚簇索引和非聚簇索引

从数据结构角度有：B+树索引、哈希索引、倒排索引和R-树索引

拓展知识：

1. 普通索引：对字段值没有任何限制，能够对查询频繁的字段进行优化

   CREATE INDEX index_name ON table_name(column_name);

2. 唯一索引：索引列的值必须唯一，允许有一个NULL值，（用户名，邮箱，身份证号等场景）

   CREATE UNIQUE INDEX index_name ON table_name(column_name);

3. 主键索引：特殊的唯一索引，不允许NULL值，每张表只能有一个主键，是表的唯一标识

   ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4. 联合索引：包含多个列的索引，减少单列索引的冗余，提高查询效率，但是需要遵循“最左前缀原则”，查询条件中的列必须按索引定义的顺序使用，从左到右依次匹配

   CREATE INDEX index_name ON table_name(column1, column2, column3);

---

MySQL 三层 B+ 树能存多少数据？

页大小： MySQL 默认每页大小为 16KB（InnoDB 存储引擎中的数据是按页（Page）进行存储和管理的，B+ 树的每个节点对应一个页）。

主键大小： 通常为 8字节（B+ 树的索引值存储的是主键值，每个主键值占用固定的存储空间）。

指针大小： 每个指针占 6字节 (在非叶子节点中，每个主键值后面会存储一个指针，指向下一层的子节点)。

叶子节点的数据行大小： 根据实际表结构计算，一般情况下假设每行数据约占 1KB。(B+ 树的叶子节点存储的是实际的数据行（即索引所对应的整行数据）)

非叶子节点可以存储到大小：页大小/（主键大小+指针大小）=（16*1024）/（8+6）≈1170

第一层：只有一个根节点最多存储 1170 个指针，指向第二层的节点。

第二层：每个节点存储 1170 个指针，总共可指向 1170 × 1170 = 1,368,900 个叶子节点。

第三层： 每个叶子节点存储实际的数据行指针。如果每行数据为 1KB，则每个叶子节点最多存储 16 行数据。

因此，三层 B+ 树能存储的总数据量为：1170 × 1170 × 16 = 21902400行（大约2190万行数据）。

---

MySQL 索引的最左前缀匹配原则是什么？

MySQL索引的最左前缀匹配原则就是在使用联合索引时，查迅条件会按照索引中从左到右的列顺序进行匹配，只有满足从左开始的连续匹配，才能使用到联合索引。

主要是MySQL 在创建复合索引时，会按照索引定义的列顺序存储数据，所以匹配时也会按照顺序依次匹配

代码：

INDEX idx_name_age_city (name, age, city)
SELECT * FROM users WHERE name = 'zhangsan';  //第一列匹配，可以使用索引
SELECT * FROM users WHERE name = 'zhangsan' AND age = 25; //前两列匹配，可以使用索引
SELECT * FROM users WHERE age = 25 AND city = 'beijing'; //没有匹配第一列，无法使用索引

注意：一旦遇到范围查询（如 <、>、BETWEEN、LIKE 等），后续的列无法利用索引。

设计联合索引时，最好将常用的条件放在索引的最左侧。

---

为什么 MySQL 选择使用 B+ 树作为索引结构？

可以减少磁盘I/O、动态平衡、支持范围查询和排序

MySQL使用B+树作为索引结构

首先，是因为它的树高度增长不会过快，三层结构的B+树就能存放2190万行数据，所以就使得查询磁盘的I/O次数减少，而I/O操作正是数据库操作中最耗时的部分。

其次，B+ 树通过自动分裂和合并节点，保持树的平衡，从而保证增删改操作不会影响查询效率。

最后，B+ 树的叶子节点存储了所有数据，并通过链表将叶子节点按顺序连接，使得范围查询可以通过顺序访问高效完成。

扩展：

为什么不选择B树？

B树的每个节点存储索引和数据，因此每个节点存储到索引数量较少，会导致树的高度较高，导致需要更多的I/O操作。

B树在查询时，因为目标数据可能会在任意节点，就导致需要反复回溯到父节点去查询，增加I/O。

B树没有叶子节点的链表结构，进行范围查询时需要从多个节点逐层遍历，效率低。

---

MySQL 中的回表是什么？

回表就是在使用非聚簇索引查询时，如果需要被查询的字段不是索引列或者主键时，就需要通过主键回到聚簇索引中查找完整数据行的过程。

扩展：

代码示例：

SELECT name FROM users WHERE age = 25;
SELECT * FROM users WHERE age = 25;

假如age是非聚簇索引，而name不包含在这个索引中，那么就会通过age索引找到主键值，然后通过回表到聚簇索引中找到对应的name（或者是整行数据）。

SELECT age FROM users WHERE age = 25;

这种情况就不需要回表，age索引包含了age本身，可以直接从索引返回结果。

影响：

回表会导致进行两次查找，所以会产生额外的磁盘I/O操作，导致查询速度变慢。

如何减少回表操作：

1. 查询时覆盖索引，查询的字段能够直接从索引中获取。
2. 选择合理的索引字段，将常用的查询字段加入索引，多个字段可以创建联合索引

---

MySQL 中使用索引一定有效吗？如何排查索引效果？

使用索引并不一定有效，以下是不一定有效的情况：

1. 表的数据量较少：只有几十行的数据，全表扫描的成本比索引更低
2. 索引列的值重复率较高：比如性别字段
3. 联合索引没有满足最左前缀匹配：不会使用索引
4. 使用了OR条件：如果OR条件中有一个字段没有索引，索引可能整体失效
5. 模糊查询%开头：以%开头，索引无法使用
6. 排序字段和索引不匹配：索引无法被使用

排查索引效果可以使用EXPLAIN分析查询：查询前添加EXPLAIN关键字，可以查看查询的执行计划，判断是否使用了索引。

示例：

EXPLAIN SELECT * FROM users WHERE age = 25;

1. **id**：表示查询中执行计划的步骤 ID。1` 说明这是一个简单查询，没有子查询或复杂的联合查询。

2. **select_type**：指定查询类型：SIMPLE：表示简单查询，不包含子查询或 UNION。这里是一个单表查询，所以是 SIMPLE。

3. table:表示查询涉及的表。这里查询的表是 user。

4. **partitions**显示查询涉及的分区。如果表未分区，则显示为 NULL。

5. **type**表示访问类型，反映查询的效率：

• ref：表示使用索引进行查询，通过索引找到符合条件的记录。

• ref 的效率较高，仅次于 const。

• 这里 type 为 ref，说明 MySQL 使用了索引来查找符合条件的记录。

6. **possible_keys**显示查询中可能使用的索引。index_age 是查询可能用到的索引。

7. **key**实际使用的索引。这里使用了索引 index_age。

8. **key_len**显示索引的长度（字节数）。这里 key_len 为 5，表示索引字段的存储长度是 5 个字节。如果 age 是 INT 类型，占用 4 字节，额外可能有 1 字节的辅助信息。

9. **ref**表示索引过滤条件的比较值。const 表示查询条件是一个常量（age = 20），可以直接通过索引快速定位。

10. **rows**预估扫描的行数。这里显示为 99，说明 MySQL 估计会扫描 99 行记录以返回结果。

11. **filtered**表示查询条件过滤后的记录百分比。100.00` 表示所有扫描到的记录都符合条件。

12. **Extra**显示额外的信息。这里为 NULL，说明没有额外操作，比如排序、临时表等。

---

在 MySQL 中建索引时需要注意哪些事项？

1. 大量重复不建索引： 如性别字段
2. 数据量较少不建索引： 数据量少全表扫描的成本比索引更低
3. 长字段不建索引： text、longtext占据内容大，扫描时耗时
4. 修改频率远大于查询的不建索引： 建立索引会减慢修改的效率，因为数据修改后，索引也会相应修改
5. 建立索引时选择合适的索引类型： 主键索引（一般就是主键，聚簇索引）、唯一索引（保证字段唯一性，邮箱、身份证号）、普通索引、全文索引（适合文本字段，varchar或text）、联合索引（多个字段组合成一个索引，适合多条件查询，注意最左前缀原则）
6. 索引个数不能过多： 索引也会占用内存空间、不要建立冗余索引，如(a，b)和(a)可以保留前者

---

MySQL 中的索引数量是否越多越好？为什么？

索引数量不是越多越好，

从时间上来说，如果对表中的数据进行频繁的增删改，那么对应的索引也要进行更新，这就会增大写入开销。

从空间上来说，创建索引也会占用空间。

还有就是MySQL有查询优化器，它可以分析当前的查询，去选择最优的计划，如果索引过多，就会导致在选择最佳索引时变得复制，可能选错索引，反而降低查询性能。

---

如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

使用方法：查询语句前加EXPLAIN

EXPLAIN SELECT * FROM users WHERE age = 20;

重点关注 type，

访问方式性能排名（从优到差）：

1. system：只有一行记录，性能最佳。
2. const：主键或唯一索引精确匹配。
3. eq_ref：唯一索引的关联查询，最多返回一条记录。
4. ref：非唯一索引的查询。
5. range：索引范围扫描（例如 BETWEEN 或 < 操作）。
6. index：索引全表扫描。
7. ALL：全表扫描，性能最差。

尽量避免 ALL 和 index 类型，优先使用 const 或 eq_ref。

其他字段解析：

字段解析：

1. **id**：表示查询中执行计划的步骤 ID。1` 说明这是一个简单查询，没有子查询或复杂的联合查询。

2. **select_type**：指定查询类型：SIMPLE：表示简单查询，不包含子查询或 UNION。这里是一个单表查询，所以是 SIMPLE。

3. table:表示查询涉及的表。这里查询的表是 user。

4. **partitions**显示查询涉及的分区。如果表未分区，则显示为 NULL

5. **possible_keys**显示查询中可能使用的索引。index_age 是查询可能用到的索引。

6. **key**实际使用的索引。这里使用了索引 index_age。

7. **key_len**显示索引的长度（字节数）。这里 key_len 为 5，表示索引字段的存储长度是 5 个字节。如果 age 是 INT 类型，占用 4 字节，额外可能有 1 字节的辅助信息。

8. **ref**表示索引过滤条件的比较值。const 表示查询条件是一个常量（age = 20），可以直接通过索引快速定位。

9. **rows**预估扫描的行数。这里显示为 99，说明 MySQL 估计会扫描 99 行记录以返回结果。

10. **filtered**表示查询条件过滤后的记录百分比。100.00 表示所有扫描到的记录都符合条件。

11. **Extra**显示额外的信息。这里为 NULL，说明没有额外操作，比如排序、临时表等。

---

MySQL 中如何进行 SQL 调优？

首先是对查询语句进行优化，通过观察慢SQL，使用explain分析查询语句

1. 避免 SELECT *，应该明确指定需要查询的列
2. 建立合适的索引，避免回表，遵循最左匹配原则
3. 避免使用函数计算操作，导致索引无法命中
4. 避免使用%LIKE，导致全表扫描

其次，可以通过业务逻辑进行优化

最后，可以利用缓存，讲一下频繁访问的数据放到缓存中，提高查询效率。

---

MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

count(*): 统计结果中的所有行的数量，包括NULL值。（扫描时不需要扫描列的内容，仅需要扫描表中的每一行，因此通常是最常用都行数统计方式）

count(1): 与count(*)基本等效，会统计每一行的存在性，1被视为常量，对每一行都会返回1

count(字段名)： 统计指定列中非NULL的行数。

count(*)和count(1)性能差异很小，count(字段名)需要检查列内容，需要消耗更多时间。

---

MySQL 中 varchar 和 char 有什么区别？

char(n) 是定长字符类型，无论存储多少个字符都会分配固定长度的空间。

varchar(n) 是变长字符类型，表示最大可以存储n个字符，但实际存储的空间根据字符串长度来分配（除了存数实际数据外，还会额外占用1-2字节来存储该字段的长度）

char 更适合存储定长字符串（如固定编码或常用短字段），因为知道字段固定长度，所以可以提高性能，但如果字段长度变化大，就会浪费大量存储空间。

varchar 适合存储变长字符串，可以节省空间

---

请详细描述 MySQL 的 B+ 树中查询数据的全过程

从根节点开始：查询会从 B+ 树的根节点开始。根节点包含键值，根据查询条件判断去哪个子树查找。

逐级向下查找：从根节点到叶子节点，每个内节点只存储键值，判断查询值应该去哪个子树。如果是范围查询，则继续向下查找。

到达叶子节点：查询最终会到达叶子节点，叶子节点存储了实际的数据（或指向数据的指针）。

找到分组： 在确定了该数据页后，通过页目录做二分查找，定位记录分组，在这个分组通过链表遍历找到指定记录行

---

MySQL 是如何实现事务的？

MySQL主要利用锁、Redo Log（重做日志）、Undo Log（回滚日志）、MVCC（多版本并发控制）来实现事务的。

利用锁机制，可以保证并发修改的控制，满足事务隔离性，

重做日志会记录事务对数据库的所有修改，在数据库出问题时，可以恢复未提交的更改，满足事务的持久性，

回滚日志会记录事务的反向操作，如果事务中的某些操作失败，会回滚到事务开始之前的状态，满足事务原子性和隔离性

MVCC满足了非锁定读的需求，提高了并发度，满足事务隔离性

---

MySQL 中的 MVCC 是什么？

MVCC即多版本并发控制，是一个并发控制机制，允许多个事务同时对数据库进行读写操作，无需互相等待，提高数据库的并发性。利用为每行数据维护多个版本，通过快照读和当前读机制，实现高并发事务控制，保证数据的隔离性和一致性。

在MVCC中，每个事务都会创建一个数据快照，且有版本号，写操作时，就会创建一个新的数据版本，只有当事务提交后，新版本才会对其他事务可见，而读操作可以同时去读历史版本，这样就减少锁的使用，避免了阻塞和死锁

---

MySQL 中的日志类型有哪些？binlog、redo log 和 undo log 的作用和区别是什么？

MySQL主要有三种日志类型：**Binlog、Redo Log和Undo Log**

1. Binlog: 是MySQL的二进制日志，用于记录所有更改数据库状态的操作。主要用途包括复制 和数据恢复 。

   记录的是逻辑事件 ，即SQL语句本身，不保存具体的行数据，只记录导致数据变化的事件。

2. Redo Log : 是用来保证事务持久性 和奔溃恢复 的日志，当事务提交时，InnoDB 会将已修改的数据写入 redo log 中，并确保这些操作在系统崩溃后能够被恢复。

   是物理日志，它记录了 数据页的修改内容（例如，某个数据页中的某个字段值的修改）

3. Undo Log ： 主要用于实现事务回滚 ，每当事务执行修改操作时，InnoDB 会在 undo log 中记录事务修改前的数据副本，以便事务回滚时能够恢复数据。

   记录的是事务对数据的撤销操作，通常是物理日志 ，即记录了数据的原始状态。

---

MySQL 中的事务隔离级别有哪些？

MySQL有四种隔离级别，读未提交、读已提交、可重复读和串行化。

读未提交： 事务可以读取到其他事务未提交的数据，会产生脏读 问题。

读已提交： 事务只能读取到其他事务已经提交的数据，避免了脏读问题，但会产生**不可重复读 **问题，即同一个事务中，相同的查询可能返回不同的结果。

可重复读： 确保了在一个事务中的多个查询结果是一致的，避免了不可重复读问题，但会产生幻读 问题，即同一个事务中，多次查询可能返回不同数量的行。（MySQL默认隔离级别）

串行化： 最高隔离级别，强制事务按顺序执行，性能较低，适用于极高一致性事务场景，如银行转账

---

MySQL 默认的事务隔离级别是什么？为什么选择这个级别？

MySQL默认的事务隔离级别是可重复读，在大多数应用中提供了一个很好的折衷，它避免了脏读和不可重复读问题，同时仍然保持良好的并发性。

还有一点是为了兼容早期binlog的statement格式问题，如果使用读已提交、读未提交等隔离级别，使用了statement格式的binlog会导致主从数据库数据不一致问题。

---

数据库的脏读、不可重复读和幻读分别是什么？

脏读： 一个事务读取到另一个事务未提交的数据。如果另一个事务最终回滚，读到的数据就变成了“脏数据”。

不可重复读： 在同一个事务中，读取同一条数据的两次结果不一致，即一个事务在读取数据时，另一个事务修改了数据并提交了更改，导致再次读时得到不同的值。

幻读： 在同一个事务中，查询的结果集在两次读取之间发生了变化，出现了新增、删除或修改的记录

---

MySQL 中有哪些锁类型？

MySQL中有行级锁、表级锁、共享锁、排他锁、意向锁、自增锁

行级锁： 仅对特定的行加锁，其他事务可以访问其他行，适合高并发

表级索： 对整个表加锁，其他事务不能对该表有任何操作，适合需要保证完整性的小型表

共享锁： 允许事务读取数据，但不允许修改。

排他锁： 只允许一个事务对资源进行读写。

意向锁： 一种表级锁，用于表示事务对某个行级锁的意图。意向共享锁 ** 表示事务计划对某行加共享锁，意向排他锁 ** 表示事务计划对某行加排他锁。意向锁的作用是确保多个事务在对表加行级锁时，不会发生冲突或错误。

自增锁： 在插入自增列时，加锁以保证自增值的唯一性，确保多个事务不会同时插入相同的自增值。

---

MySQL 事务的二阶段提交是什么？

事务的二阶段提交是一种确保分布式事务一致性的协议，通常用于跨多个数据库或分布式系统中。它的目的是确保事务在多个节点（或数据库实例）之间一致地提交或回滚，避免出现“部分提交”的情况，从而保持数据的一致性。

流程：

阶段一：准备阶段

• 事务协调者向所有参与者（数据库节点）发送“准备提交”请求，要求它们准备好提交事务。
• 每个参与者会检查事务是否可以提交（例如锁定资源、检查数据完整性等），如果一切正常，参与者返回“准备好提交”的确认。

**阶段二：提交阶段 **

• 如果所有参与者都返回成功，协调者向所有参与者发送“提交”命令，指示它们正式提交事务。
• 如果任何参与者返回失败，协调者向所有参与者发送“回滚”命令，要求它们撤销事务。

---

MySQL 中如果发生死锁应该如何解决？

MySQL自带死锁检测机制，当检测到死锁时，会牺牲掉一个事务以解出死锁，一般选择资源少的那个。

如果锁带有等待超时的参数，当等待时间超时时，就会释放锁进行回滚

MySQL也可以进行手动Kill死锁的语句

---

MySQL 中如何解决深度分页的问题？

通过子查询方式优化查询效率：避免使用 OFFSET 跳过大量数据，而是通过唯一索引字段（如 id）快速定位起始记录

优化后：

还可以通过记录ID优化：

每次分页都返回当前最大id，然后下次查询时，带上这个id，就可以利用 id > maxid 过滤了。

适合连续查询的情况，不适合跳页查询。

扩展：

什么是深度分页？

深度分页是指查询结果集的页数很深，比如有一个数据量很大的表，当要访问比较靠后的数据时，就要扫描前面大量数据，才能得到后面数据，导致占用大量计算资源，降低性能。

SELECT * FROM table_name ORDER BY id LIMIT offset, page_size;

**offset**：表示跳过的记录数。例如，LIMIT 10000, 10 表示跳过前 10,000 条记录，查询接下来的 10 条。

---

什么是 MySQL 的主从同步机制？它是如何实现的？

主存同步机制是用来将数据从主数据库（Master）复制到一个或多个从数据库（Slave）的技术。

主存同步原理是通过二进制日志实现的。主数据库在执行写操作时，会将这些操作记录在binlog中，然后推送给从数据库，从数据库重放对应的日志即可完成复制。

扩展：

实现步骤：

1.配置主库

开启二进制日志： 在主库的配置文件 my.cnf 中添加：

[mysqld]
server-id=1  # 唯一的服务器 ID
log-bin=/var/lib/mysql/mysql-bin  # 二进制日志文件

创建用于同步的用户：

CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

2. 配置从库

修改从库配置： 在从库的配置文件 my.cnf 中添加：

[mysqld]
server-id=2  # 从库的服务器 ID，必须与主库不同
relay-log=/var/lib/mysql/mysql-relay-bin  # 中继日志文件

连接主库： 使用 CHANGE MASTER TO 命令设置主库信息：

CHANGE MASTER TO
   MASTER_HOST='主库IP',
   MASTER_USER='replicator',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000001',  # 从主库复制的起点
   MASTER_LOG_POS=120;                 # 二进制日志的偏移量

启动从库同步：

START SLAVE;

3. 查看同步状态

在从库上检查同步状态：

SHOW SLAVE STATUS\G;

关键字段解释：

• Slave_IO_Running 和 Slave_SQL_Running：确保这两个状态都为 Yes。
• Seconds_Behind_Master：表示从库与主库的延迟时间（秒）。

---

如何处理 MySQL 的主从同步延迟？

由于是从主数据库复制到从数据库的，所以延迟是无法避免的。

1. 二次查询： 做兜底策略，即从数据库查不到的数据，去主数据库再查一遍。
2. 强制将写之后立马读的操作转移到主库上： 将写入之后立马查询的操作写死走主库。
3. 关键业务逻辑读写都走主库： 减轻延迟对关键业务的影响，非关键业务可以走读写分离
4. 使用缓存： 主库写入后同步到缓存中，查询时先查缓存。

---