(三) 征服MySQL面试：30+高频核心问题深度剖析与实战指南

一、为什么MySQL是面试的"必答题"？

数据库领域占比：MySQL占据全球关系型数据库市场份额Top 3，阿里、腾讯、美团等大厂核心系统深度依赖
技术栈深度检验：通过MySQL问题可考察候选人的数据结构理解、系统设计能力、性能优化思维三大维度
薪资挂钩指标：P6+/35k+岗位面试中，90%会涉及MySQL底层原理与高并发场景解决方案

MySQL中，如何定位慢查询? （先找到哪个sql查询的慢）

慢查询常用工具

1部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，可以分析这个接口哪部分比较慢，可以看到SQL的具体的执行时间，可以定位是哪个sql出了问题

2.MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置；

SQL语句执行很慢, 如何分析呢？（再分析为什么慢）

采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息

mysql 的 explain 执行结果

用mysql自动的执行计划 explain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复；

possible_key：当前sql可能会使用到的索引

key ：当前sql实际命中的索引

key_len：索引占用的大小

Extra ：额外的优化建议

type：这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all；

system：查询系统中的表

const：根据主键查询

eq_ref：主键索引查询或唯一索引查询

ref：索引查询

range：范围查询

index：索引树扫描

all：全盘扫描

什么是索引？

主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗‘

MYSQL支持的存储引擎有哪些, 有什么区别 ?

搜索引擎区别

InnoDB：mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁

MyISAM：它不支持事务、只有表级锁、也没有外键，用的不多

Memory：主要把数据存储在内存，支持表级锁，没有外键和事务，用的不多

索引的底层数据结构 ?

MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：第一阶数更多，路径更短，第二个磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表；、

B树和B+树的区别是什么呢？

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的

所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存

储，并且叶子节点是一个双向链表

B树与B+树对比:

①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询

B树：B树每个节点可以有多个分支，即多叉

B+树

为什么MySQL默认使用B+树而不是B树或哈希表？

核心差异：
- B树：每个节点存储键值和数据，导致单个节点存储的键值少，树的高度较高，查询时磁盘I/O次数多。
- B+树：数据只存储在叶子节点，非叶子节点仅存键值，单个节点可存储更多键值，树的高度更低；叶子节点形成链表，范围查询效率提升5倍以上。
- 哈希表：适合等值查询（O(1)），但无法支持范围查询和排序，且哈希冲突处理代价高。
延伸问题：

-- 验证索引页大小（默认16KB）
SHOW VARIABLES LIKE 'innodb_page_size';

页大小设计：16KB平衡了内存利用率与磁盘I/O效率，B+树高度为3时可存储约 2000万行数据（假设主键为BIGINT）；

什么是聚簇索引什么是非聚簇索引 ?

聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的，

非聚簇索引值的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引；

聚集索引选取规则:

1.如果存在主键，主键索引就是聚集索引。

2.如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

3.如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

什么叫覆盖索引 ?

覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段；

什么是回表查询嘛 ?

和聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表；

MYSQL超大分页怎么处理 ?

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的覆盖索引，所以效率可以提升很多

索引创建原则有哪些？

1.选择合适的字段

高选择性字段：选择性是指字段中不重复值的比例。高选择性的字段（如身份证号、订单号等）更适合创建索引，因为它们能更有效地过滤数据，减少扫描的行数。
频繁用于查询的字段：如果某个字段经常出现在 WHERE 子句、JOIN 条件或 ORDER BY 子句中，那么为该字段创建索引可以显著提高查询性能。
避免为低基数字段创建索引：低基数字段（如性别、状态码等）的选择性较低，创建索引可能不会带来太多性能提升，反而会增加存储和维护成本。

2.考虑查询模式

覆盖索引：创建的索引应尽量覆盖查询中涉及的所有字段。如果一个查询可以通过索引直接获取所有需要的数据，而无需回表查询，那么查询效率会更高。
前缀索引：对于字符串类型的字段，如果字段值较长，可以考虑创建前缀索引。前缀索引只对字段的前几个字符建立索引，可以减少索引的大小，但可能会降低索引的选择性。
组合索引：如果查询中经常涉及多个字段的组合条件，可以考虑创建组合索引。组合索引的顺序也很重要，应将选择性最高的字段放在前面。

3.控制索引数量

避免过多索引：每个索引都会占用额外的存储空间，并且在数据插入、更新和删除时需要维护索引，这会增加操作的开销。因此，应尽量避免为每个字段都创建索引。
定期评估和清理索引：随着数据库的发展和业务的变化，某些索引可能不再被使用或不再有效。定期评估索引的使用情况，并删除无用的索引，可以优化数据库的性能。

4.考虑数据更新频率

静态数据优先：对于数据更新频繁的表，创建过多索引可能会导致性能下降。相反，对于数据相对静态的表（如历史数据表），可以适当增加索引以提高查询效率。
平衡读写性能：在创建索引时，需要权衡读操作和写操作的性能。如果表的读操作远多于写操作，可以适当增加索引；反之，则需要谨慎使用索引。

5.索引类型选择

B树索引：这是最常见的索引类型，适用于大多数场景，尤其是范围查询和精确查询。
哈希索引：适用于精确查询，但不支持范围查询。哈希索引的查询速度非常快，但在数据更新时可能会有性能问题。
全文索引：用于文本字段的全文搜索，适用于需要进行复杂文本查询的场景。

6.其他注意事项

索引的唯一性：如果业务逻辑要求字段值唯一，可以创建唯一索引。唯一索引不仅可以提高查询性能，还可以防止数据重复。
索引的维护：定期对索引进行维护，如重建索引或重新组织索引，以确保索引的性能。在数据库中，索引可能会因为数据的频繁更新而变得碎片化，影响查询效率。
避免冗余索引：如果已经有一个组合索引可以覆盖查询需求，就不要再创建多余的单列索引。

总之，索引的创建需要根据具体的业务需求、数据特点和查询模式进行综合考虑。合理的索引可以显著提升数据库的性能，但不合理的索引可能会带来负面影响。

什么情况下索引会失效 ?

隐式类型转换：WHERE name = 123（name为字符串类型）。
函数操作：WHERE YEAR(create_time) = 2023 → 改为范围查询。
前导模糊查询：WHERE name LIKE '%abc%'。
OR条件未全覆盖：WHERE a=1 OR b=2（若只有a有索引）。
联合索引未遵循最左前缀：索引(a,b,c)，查询条件无a。
数据倾斜：索引列90%的值相同，优化器可能放弃索引。

通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析；

sql的优化的方向？

建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表；

创建表的时候，你们是如何优化的呢？

在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int 、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型；

对sql语句做了如何优化呢？

1.不要直接使用select * ，还有就是要注意SQL语句避免造成索引失效的写法

2.如果是聚合查询，尽量用union all代替union ，union会多一次过滤，效率比较低；

3.如果是表关联的话，尽量使用innerjoin ，不要使用用left join right join，如必须使用一定要以小表为驱动；

事务的特性是什么？ACID

ACID，分别指的是：原子性、一致性、隔离性、持久性；

原子性（Atomicity）：依赖undo log，回滚时反向执行日志中的操作。
持久性（Durability）：依赖redo log，事务提交前先写日志（WAL机制）。
隔离性（Isolation）：通过MVCC（多版本并发控制）和锁机制实现。
一致性（Consistency）：由应用层和数据库共同保证。
关键问题：
- 为什么需要双写缓冲区（Doublewrite Buffer）？
  防止页断裂（Partial Page Write）问题，确保数据页写入磁盘的原子性。

并发事务带来哪些问题？

1.脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

2.不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

3.幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

怎么解决这些问题呢？MySQL的默认隔离级别是？

MySQL支持四种隔离级别，分别有：

未提交读（read uncommitted）不能解决所有问题，一般项目中也不用这个。
读已提交（read committed）能解决脏读的问题的，但是解决不了不可重复读和幻读。
可重复读（repeatable read）能解决脏读和不可重复读，但是解决不了幻读；
串行化（serializable）可解决所有问题，但是事务串行执行的，性能比较低。

mysql默认的隔离级别:可重复读

undo log和redo log的区别？

redo log记录的是数据页的物理变化，服务宕机可用来同步数据;而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作；redo log保证了事务的持久性，undo log保证了事务的原子性和一致性;

事务中的隔离性是如何保证的呢？(你解释一下MVCC)

事务的隔离性是由锁和mvcc实现的。

MVCC 呢，意思就是多版本并发控制。这指的是维护一个数据的好多版本，能让读和写的操作不会有冲突。它底层的实现主要分成了三个部分。第一个部分是隐藏字段，第二个是 undo log 日志，第三个是 readView 读视图。

隐藏字段是这样的：在 MySQL 里，给每个表都设置了隐藏字段。其中一个是 trx_id（事务 id），每次操作都会记录这个事务 id，而且还是自动增加的；另一个字段是 roll_pointer（回滚指针），它能指向上一个版本的事务版本记录地址。

undo log 主要的用处是记录回滚的日志，存着老版本的数据。在内部会形成一个版本链，要是多个事务一起对某一行记录进行操作，就会记录不同事务修改数据的版本，通过 roll_pointer 指针形成一个链表。

readView 解决的是一个事务查询该选哪个版本的问题，在内部规定了一些匹配的规则，还有当前的一些事务 id，用来判断该访问哪个版本的数据。不同的隔离级别，快照读是不一样的，最终访问的结果也不同。要是 rc 隔离级别，每次进行快照读的时候都会生成 ReadView。要是 rr 隔离级别，只在事务里第一次进行快照读的时候生成 ReadView，后面就接着用。

MySQL主从同步原理?

MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：

第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志Relay Log 。

第三：从库重做中继日志中的事件，将改变反映它自己的数据;

MySQL的分库分表

MySQL 的分库分表是一种常见的优化策略，用于解决单表数据量过大或单库性能瓶颈的问题。以下是关于 MySQL 分库分表的详细说明，包括其基本概念、拆分方式、实施步骤及注意事项：

一、分库分表的基本概念

只分表：

单表数据量大，读写出现瓶颈，但数据库整体性能尚可，此时可以只对表进行拆分。

只分库：

数据库连接数达到上限（如 MySQL 默认最大连接数为150），或并发量过高导致单库无法满足需求，此时可以只对数据库进行拆分。

分库分表：

单表数据量大且数据库整体性能也出现瓶颈时，需要同时进行分库和分表。

垂直拆分：

将表中的字段拆分到不同的表中，适用于字段较多且某些字段访问频率较低的情况。

水平拆分：

将表中的记录按一定规则拆分到多个表中，适用于数据量较大的表。

二、分库分表的拆分方式

业务层面拆分：

混合业务拆分：将不同业务模块的数据存储到不同的数据库中。
冷热分离：将高频访问的“热数据”和低频访问的“冷数据”分别存储在不同的表或库中。

数据层面拆分：

水平分片（Sharding）：按行拆分数据，常见的分片策略包括按范围分片和按哈希分片。
垂直分片：按列拆分数据，将宽表拆分成多个窄表。

三、分库分表的实施步骤

评估需求：

根据业务需求和数据特点，评估是否需要分库分表，以及选择合适的拆分策略。

选择分表字段（Sharding Key）：

确定分片键，用于决定数据如何分配到不同的表或库中。

代码改造：

修改应用程序代码，使其能够正确处理分库分表后的数据访问逻辑。

数据迁移：

将现有数据迁移到新的分库分表结构中，包括全量同步和增量同步。

数据一致性校验和补偿：

在数据迁移过程中，确保数据的一致性和完整性。

灰度切读：

在新旧系统切换过程中，逐步将流量切换到新系统，确保系统的稳定性。

停旧库、写新库：

在确认新系统稳定运行后，停止使用旧库，全面切换到新库。

四、分库分表的注意事项

分库分表维度：

根据业务逻辑和数据特点选择合适的分库分表维度，如按时间、用户 ID 等。

联合查询问题：

分库分表后，跨表或跨库的联合查询会变得更加复杂，需要通过中间件或应用层逻辑来解决。

避免跨库事务：

尽量减少跨库事务，因为跨库事务的管理和维护成本较高。

数据分布均匀：

确保数据在各个分片中均匀分布，避免数据热点。

五、分库分表的工具和中间件

Amoeba：一个 MySQL 集群中间件，可以实现分库分表，对上层应用透明。
ShardingSphere：一个开源的分布式数据库中间件，支持分库分表、读写分离等功能，提供灵活的分片策略和数据一致性保障。
Mycat：一个开源的分布式数据库系统，核心功能是分库分表，支持多种后端数据库。

CHAR(30) vs VARCHAR(30) 的存储与性能差异

存储方式：
- CHAR(30)：固定分配30字节，不足部分用空格填充。
- VARCHAR(30)：1字节（长度≤255）或2字节长度前缀 + 实际数据。
性能对比：
- 存储"abc"时，CHAR浪费27字节，但读取速度快0.3μs（无需计算长度）。
- 使用场景：定长字段（如MD5值）用CHAR，不定长字段用VARCHAR。

十亿级数据分页优化：为什么LIMIT 100000,10会导致性能灾难？

问题根源：

SELECT * FROM table LIMIT 100000, 10; 
-- 需要先扫描前100010行，再丢弃前100000行

优化方案：

游标分页（Cursor-based Pagination）：

SELECT * FROM table WHERE id > 上一页最大ID ORDER BY id LIMIT 10;

覆盖索引优化：

SELECT * FROM table
JOIN (SELECT id FROM table ORDER BY id LIMIT 100000, 10) AS tmp
USING (id);

彻底理解MVCC：Read View与版本链如何避免幻读？

核心机制：
- 版本链：每行数据包含隐藏字段DB_TRX_ID（事务ID）和DB_ROLL_PTR（回滚指针）。
- Read View：事务启动时生成，包含活跃事务ID列表，用于判断数据可见性。
幻读解决方案：
- 快照读（Snapshot Read）：通过MVCC避免读取未提交数据。
- 当前读（Current Read）：通过Next-Key Lock（记录锁+间隙锁）阻止其他事务插入。

死锁分析与预防：如何从日志定位死锁根源？

复现死锁：

-- 事务1
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 事务2（反向操作）
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;

排查步骤：

查看死锁日志：

SHOW ENGINE INNODB STATUS;

分析LATEST DETECTED DEADLOCK段中的等待关系。

预防策略：按固定顺序访问资源，或使用SELECT ... FOR UPDATE提前加锁。

Explain执行计划深度解读

关键字段：
- type：const（主键） > ref（索引） > range（范围） > index（全索引扫描） > ALL（全表扫描）。
- Extra：
  - Using filesort：需要优化排序字段索引。
  - Using temporary：需避免临时表（如GROUP BY无索引）。

亿级数据分库分表方案选型

垂直拆分：按业务模块拆分（如用户表、订单表）。
水平拆分：
- Range分片：按时间范围，易导致热点问题。
- Hash分片：数据均匀分布，但扩容复杂。
- 基因法：通过user_id末尾2位决定分片（如00~99对应100个表）。
全局ID生成：
- 雪花算法：64位ID（时间戳+机器ID+序列号），存在时钟回拨问题。
- Leaf-Segment：基于数据库号段，美团开源方案。

主从复制延迟解决方案

半同步复制：主库提交事务时，至少一个从库确认收到binlog。
并行复制：基于组提交的MTS（Multi-Threaded Slave）模式。
读写分离策略：
- 写后读主库：更新后设置标记，后续查询强制走主库。
- 中间件路由：ShardingSphere根据SQL类型选择数据源。

高可用架构：MHA vs 组复制（Group Replication）

MHA（Master High Availability）：
- 基于binlog日志的故障转移，需额外Manager节点。
- 优点：支持异步复制，对网络要求低。
组复制（Group Replication）：
- 基于Paxos协议，数据强一致。
- 缺点：网络延迟敏感，至少3节点。

设计一个支撑百万QPS的评论系统

存储设计：
- 主表存储评论元数据（comment_id, user_id, content）。
- 嵌套关系表（parent_id实现父子评论）。
读写分离：写主库，读从库。
缓存策略：使用Redis缓存热评（按点赞数排序）。
分库分表：按comment_id哈希分16个库，每个库分64张表。

如何实现秒杀场景下的库存防超卖？

数据库方案：

UPDATE stock SET count = count - 1 WHERE product_id = 100 AND count > 0;

Redis方案：
- 预扣库存：DECR原子操作减少库存。
- 异步落库：通过消息队列同步到数据库。

MySQL 的主从复制？

主从复制的⽤途：

实时灾备，⽤于故障切换

读写分离，提供查询服务

备份，避免影响业务

主从部署必要条件：

主库开启binlog⽇志（设置log-bin参数）

主从server-id不同

从库服务器能连通主库

主从复制的原理：

Mysql 中有⼀种⽇志叫做 bin ⽇志（⼆进制⽇志）。这个⽇志会记录下

所有修改了数据库的SQL 语句

（insert,update,delete,create/alter/drop table, grant 等等）。

主从复制的原理其实就是把主服务器上的 bin ⽇志复制到从服务器上执

⾏⼀遍，这样从服务器上的数据就和主服务器上的数据相同了。

主从复制原理

1. 主库db的更新事件(update、insert、delete)被写到binlog

2. 主库创建⼀个binlog dump thread，把binlog的内容发送到从库

3. 从库启动并发起连接，连接到主库

4. 从库启动之后，创建⼀个I/O线程，读取主库传过来的binlog内容并写⼊到relay log

5. 从库启动之后，创建⼀个SQL线程，从relay log⾥⾯读取内容，执⾏读取到的更新事件，将更新内容写⼊到slave的db

原文章地址: 欢迎大家关注https://www.toutiao.com/article/7467739044408017458/https://www.toutiao.com/article/7467739044408017458/