MySQL面试题（最全、超详细）——定位慢查询、聚簇索引、覆盖索引、深分页优化、sql优化、并发事务问题、隔离级别、undo log与redo log、主从同步

文章目录

一、在MySQL中，如何定位慢查询

二、某条SQL语句执行很慢，如何分析呢

三、存储引擎

• 3.1 MySQL支持的存储引擎有哪些，有什么区别
• 3.2 MySQL支持的存储引擎有哪些, 有什么区别 ?

四、索引

• 4.1 索引在项目中的使用方式
• 4.2 了解过索引吗（什么是索引）
• 4.3 索引的底层数据结构了解过吗
• 4.5 B树和B+树的区别是什么呢
• 4.6 什么是聚簇索引、什么是二级索引（非聚簇索引），什么是回表查询
• 4.7 什么是覆盖索引
• 4.8 MySQL超大分页怎么处理
• 4.9 索引创建原则有哪些
• 4.10 什么情况下索引会失效

五、谈谈你对sql的优化经验

六、事务

• 6.1 事务的特性是什么
• 6.2 并发事务带来哪些问题
• 6.3 怎么解决这些问题——隔离级别

七、undo log 和 redo log 的区别

八、事务中的隔离性是如何保证的呢？

九、解释一下MVCC

十、MySQL主从同步原理

十一、你们项目用过分库分表吗

一、在MySQL中，如何定位慢查询

• 聚合查询
• 多表查询
• 表数据量过大查询
• 深度分页查询

表象：页面加载过慢、接口压测响应时间过长（超过1s）

方案一：开源工具

• 调试工具：Arthas
• 运维工具：Prometheus、Skywalking

方案二：MySQL自带慢日志

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

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

总结回答：如何定位慢查询

1. 介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）
2. 我们系统中当时采用了运维工具（ Skywalking ），可以监测出哪个接口，最终因为是sql的问题（监测出哪个接口比较慢、并且可以分析出这个接口哪部分比较慢，看到SQL的具体执行时间、可以定位是哪个sql出了问题）
3. 如果项目中没有这种运维的监控系统，在MySQL中页提供了慢日志查询的功能。在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段），我们便可在日志文件中找到执行比较慢的SQL

二、某条SQL语句执行很慢，如何分析呢

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

-- 直接在select语句之前加上关键字 explain/desc
explain select 字段列表 from 表名 where 条件;

总结回答：那这个SQL语句执行很慢，如何分析呢？

可以采用MySQL自带的分析工具 EXPLAIN 去查询这条sql的执行情况

• 通过key和key_len检查是否命中了索引（索引本身存在、是否有失效的情况）
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

三、存储引擎

详情可见 MySQL进阶 1：存储引擎、索引

3.1 MySQL支持的存储引擎有哪些，有什么区别

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

• MySQL体系结构

• InnoDB存储引擎的特点

1）介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

2）特点

• DML操作遵循ACID模型，支持事务；
• 行级锁，提高并发访问性能；
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

3）文件

• xxx.frm：xxx代表的是表名，与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等（MySQL8.0时，合并在表名.ibd中）
• xxx.ibd：InnoDB DATA，表数据和索引的文件，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

参数：innodb_file_per_table

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

在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory

• InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁
• MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多
• Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

详情可见 MySQL进阶 1：存储引擎、索引

四、索引

详情可见 MySQL进阶 1：存储引擎、索引

4.1 索引在项目中的使用方式

一是验证你的项目场景的真实性，二是为了作为深入发问的切入点。

• 缓存
• 分布式锁
• 消息队列、延迟队列
• ……

4.2 了解过索引吗（什么是索引）

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

• 索引是一种数据结构，用来帮助提升查询和检索数据速度。可以理解为一本书的目录，帮助定位数据位置。
• 索引是一个文件，它要占用物理空间。

4.3 索引的底层数据结构了解过吗

二叉树、红黑树、B-Tree、B+Tree、Hash

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

• 阶数更多，路径更短
• 磁盘读写代价
• B+树更低，非叶子节点只存储指针，叶子节点存储数据，B+树便于扫库和区间查询，叶子节点是一个双向链表

详情可见 MySQL进阶 1：存储引擎、索引

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

• 在B树中，非叶子节点和叶子节点都会存放数据，而B+树所有的数据都会出现在叶子节点，在查询的时候 B+树查找效率更加稳定
• 在进行范围查询的时候 B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

4.6 什么是聚簇索引、什么是二级索引（非聚簇索引），什么是回表查询

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

• 聚簇索引： InnoDB 引擎 要求必须有聚簇索引，也就是在主键字段建立聚簇索引。
• 非聚簇索引： 非聚簇索引就是以非主键创建的索引，在叶子节点存储的是表主键和索引列。 InnoDB 引擎

聚集索引之所以必须有是因为它是用来存储数据的，而二级索引更多地用来 更快更高效地查询数据。

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：和聚簇索引、非聚簇索引有关。这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。（先走二级索引找到主键值，再根据主键值到聚集索引中找到对应的行数据）

4.7 什么是覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列 在该索引中全部能够找到。

• 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

4.8 MySQL超大分页怎么处理

可以使用覆盖索引解决。

问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。

解决方案：覆盖索引+子查询。先分页查询数据的id字段，确定了id之后 再用子查询来过滤，只查询这个id列表中的数据就可以了。因为查询id的时候 走的覆盖索引，所以效率可以提升很多

select *
from tb_sku t,
	(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10; -- （可以加下时间条件到外面的主查询）

4.9 索引创建原则有哪些

先陈述自己在实际的工作中是怎么用的。

主键索引、唯一索引、根据业务创建的索引(复合索引)。

1）针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据（增加用户体验）

2）针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3）尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4）如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5）尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6）要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7）如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

4.10 什么情况下索引会失效

慢 SQL 是数据库使用中最长遇见的问题，当遇到慢 SQL 时，首先我们就要去看是不是索引失效。一般会有以下几种常见的情况：

1） > < 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。mysql 会一直向右匹配直到遇到索引搜索键使用>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询，那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列，因此如果查询条件中有两个>、<范围列则无法全用到索引。

例如，user表有一个联合索引 (profession, age, status)

-- 当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明前面两个字段profession、age查询是走索引的，范围查询右边的status字段没有走索引。
explain select * from user where profession = '软件工程' and age > 30 and status = '0';

-- 使用>= 或 <=，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。
explain select * from user where profession = '软件工程' and age >= 30 and status = '0';

在业务允许的情况下，尽可能地使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

2） like %xx 模糊查询

• 当使用LIKE操作符进行模糊查询，并且搜索键值以通配符%开头（如：like '%abc'），则索引失效，直接全表扫描。这是因为以%开头的模式匹配意味着匹配的字符串可以在任何位置，这使得索引无法有效定位数据
• 若只是以%结尾，索引不会失效

-- 索引生效
explain select * from user where profession like '软件%';
-- 失效
explain select * from user where profession like '%工程';
-- 失效
explain select * from user where profession like '%工%';

3）对索引列进行运算

当我们在查询条件中对索引列进行函数或表达式计算，会导致索引失效而进行全表扫描。比如：

select * from user where YEAR(birthday) < 1999;
explain select * from user where length(name)>2;

4）or 条件索引问题

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

当or连接的条件，左右两侧字段都有索引时，索引才会生效

-- age没有索引，or连接 索引失效、全表扫描
explain select * from user where id = 10 or age = 23;

5）数据类型不一致，隐式转换导致索引失效

当列是字符串类型，传入条件 必须用引号引起来，不然报错或索引失效

explain select * from t_user where id_no = 1002;

表里的 id_no 是 varchar 类型

6）!= 问题

普通索引使用 !=索引失效，主键索引没影响。

where语句中索引列使用了负向查询，可能会导致索引失效。负向查询包括：NOT、!=、<>、NOT IN、NOT LIKE等。

-- 索引生效
explain select * from deviceinfo where device_id = '0x719d7986';
-- 索引失效
explain select * from deviceinfo where device_id != '0x719d7986';

-- 索引生效
explain select * from deviceinfo where id = 32619;
-- 索引生效
explain select * from deviceinfo where id != 32619;

7）联合索引 违背 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

例如，user表有一个联合索引 (profession, age, status)。对于最左前缀法则指的是，查询时，最左边的列 也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

-- 索引生效，key_len=54
explain select * from user where profession = '软件工程' and age = 31 and status = '0';
-- 索引生效，key_len=49
explain select * from user where profession = '软件工程' and age = 31;
-- 索引生效，key_len=47
xplain select * from user where profession = '软件工程';

-- 索引失效，不满足最左前缀法则，联合索引最左边的列profession不存在
explain select * from user where age = 31 and status = '0';
-- 索引失效，原因同上
explain select * from tb_user where status = '0';

-- 索引生效，key_len=47。最左边的列是存在的，索引满足最左前缀法则的基本条件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索引的长度就是47
explain select * from user where profession = '软件工程' and status = '0';

-- 索引生效，key_len=54。最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关
explain select * from user where age = 31 and status = '0' and profession = '软件工程'；

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

8）order by问题

order by 对主键索引排序会用到索引，其他的索引失效

explain select * from user order by id;
explain select * from user order by age;

9）其他情况

出了这几种情况还有一些导致索引失效。 例如：

• 全表扫描效率更优：在某些情况下，MySQL 优化器可能认为全表扫描比使用索引更快。
• 数据分布不均：如果索引列的数据分布非常不均匀，MySQL 可能不会选择使用索引。
• 索引列包含 NULL 值：如果索引列包含 NULL 值，MySQL可能不会使用索引，因为 NULL 值的比较有特殊性。因为NULL值无法与其他值进行比较或匹配，所以无法使用索引。

详情可见 MySQL进阶 1：存储引擎、索引

五、谈谈你对sql的优化经验

表的设计优化；索引优化（参考优化创建原则和索引失效）；SQL语句优化；主从复制、读写分离；分库分表

• 表的设计优化——建表时（参考阿里开发手册《嵩山版》）
  • 比如设置合适的数值（tinyint int bigint），要根据实际情况选择
  • 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低
• SQL语句优化
  • SELECT语句务必指明字段名称（避免直接使用select * ）
  • SQL语句要避免造成索引失效的写法
  • 尽量用union all代替union union会多一次过滤，效率低
  • 避免在where子句中对字段进行表达式操作
  • Join优化 能用inner join 就不用left join、right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

select * from t_user where id > 2
union all | union
select * from t_user where id < 5;


for (int i = 0; i < 3; i++) {
	for (int j = 0; j < 1000; j++) {
	
    }
}

• 主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

总结回答：谈一谈你对sql的优化经验

从以下几个方面考虑：表的设计优化；索引优化（参考优化创建原则和索引失效）；SQL语句优化；主从复制、读写分离；分库分表

1. 表的设计优化，数据类型的选择
2. 索引优化，索引创建原则（考优化创建原则和索引失效）
3. sql语句优化，避免索引失效，避免使用select * ….
4. 主从复制、读写分离，不让数据的写入，影响读操作
5. 分库分表

六、事务

详情可见 事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读

6.1 事务的特性是什么

ACID

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 在事务开始和完成时，中间过程对其它事务是不可见的。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

6.2 并发事务带来哪些问题

并发事务问题：脏写、脏读、不可重复读、幻读

6.3 怎么解决这些问题——隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别，且不同级别的隔离可以规避不同严重程度的事务问题。主要有以下几种：

1. 读未提交（READ UNCOMMITTED），指一个事务还没提交，它做的修改就能被其他事务看到。
2. 读提交（READ COMMITTED），一个事务做的修改，只有提交之后，其他事务才能看到。
3. 可重复读（REPEATABLE READ），在整个事务过程中看到的数据，自始至终都是一致的。
4. 串行化（SERIALIZABLE），每个读写操作都会加锁，多个事务要访问同一条记录时，必须要进行排队，优先级低的事务必须等优先级高的事务完成以后才能进行。

从1到4，隔离级别依次变高，当然，性能也依次变差。那么这些隔离级别究竟都能防止哪些问题呢

注：事务隔离级别 事务隔离级别越高，数据越安全，但是性能越低。一般采用数据库的默认级别。

MySQL InnoDB引擎默认的隔离级别是可重复读（RR）。

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
SELECT @@tx_isolation;

# 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

1.MySQL InnoDB引擎默认的隔离级别是可重复读（RR）。为什么MySQL没有使用串行化这个级别？是不是意味着我们日常使用MySQL会有可能存在幻读的问题？

非也！隔离级别越高代价也是越高的，且性能也越差。从性能上来说，当然是隔离级别越低越好。

2.至于隔离级别是RR（可重复读）下的MySQL怎么避免幻读问题，InnoDB引擎有它自己的想法，以后单独抽一讲来说啦

详情可见 事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读

七、undo log 和 redo log 的区别

首先介绍下 缓冲池与数据页的概念

• 缓冲池（buffer pool）:主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
• 数据页（page）:是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

redo log

• 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
• 该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 ： 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。

• 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，
• 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

总结：undo log和redo log的区别

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

八、事务中的隔离性是如何保证的呢？

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

锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）

mvcc : 多版本并发控制

九、解释一下MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

• 隐藏字段（mysql给每个表都设置了隐藏字段）：
  • trx_id(事务id)，记录每一次操作的事务id，是自增的
  • roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址
• undo log：
  • 作用：记录回滚日志，存储老版本数据
  • 在内部会形成版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
• readView解决的是一个事务查询选择版本的问题
  • 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
  • 不同的隔离级别快照读是不一样的，最终的访问的结果不一样
    • RC ：每一次执行快照读时生成ReadView
    • RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

详情可见 MVCC 原理分析、MySQL是如何解决幻读的

十、MySQL主从同步原理

MySQL主从复制的核心就是二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

复制流程分成三步：

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
3. slave重做中继日志中的事件，将改变反映它自己的数据。

十一、你们项目用过分库分表吗

拆分策略

新的问题和新的技术

总结回答

你们项目用过分库分表吗？

• 业务介绍
  • 根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）
  • 达到了什么样的量级（单表1000万或超过20G）
• 具体拆分策略
  • 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
  • 水平分表，解决单表存储和性能的问题
  • 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
  • 垂直分表，冷热数据分离，多表互不影响

水平分库、水平分表的问题可通过sharding-sphere、macat技术解决。

参考 黑马程序员相关视频与文档、MySQL进阶 1：存储引擎、索引、事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读、MVCC 原理分析、MySQL是如何解决幻读的