MySQL面试

定位慢查询

慢查询

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

原因：

1. 聚合查询
2. 多表查询
3. 表数据量过大查询
4. 深度分页查询

解决方案

解决方案一

开源工具：

• 调试工具：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

日志位置：/var/lib/mysql/localhost-slow.log

面试题

面试官： MySQL中，如何定位慢查询？

回答：

1. 嗯，我们当时在做压力测试时发现有些接口响应时间非常慢，超过了2秒。
2. 因为我们的系统部署了运维监控系统Skywalking，在它的报表展示中可以看到哪个接口慢，并且能分析出接口中哪部分耗时较多，包括具体的SQL执行时间，这样就能定位到出现问题的SQL。
3. 如果没有这种监控系统，MySQL本身也提供了慢查询日志功能。
4. 可以在MySQL的系统配置文件中开启慢查询日志，并设置SQL执行时间超过多少就记录到日志文件，比如我们之前项目设置的是2秒，超过这个时间的SQL就会记录在日志文件中，我们就可以在那里找到执行慢的SQL。

分析慢查询

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

语法

在 SELECT 语句前加上 EXPLAIN/DESC

参数

1. possible_key 当前sql可能会使用到的索引
2. key 当前sql实际命中的索引
3. key_len 索引占用的大小
4. Extra 额外的优化建议
   1. Using where、Using Index--查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
   2. Using index condition--查找使用了索引，但是需要回表查询数据
5. type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
   1. system：查询系统中的表
   2. const：根据主键查询
   3. eq_ref：主键索引查询或唯一索引查询
   4. ref：索引查询
   5. range：范围查询
   6. index：索引树扫描
   7. all：全盘扫描

面试题

面试官： 那这个SQL语句执行很慢，如何分析呢？

回答：

1. 如果一条SQL执行很慢，我们通常会使用MySQL的 EXPLAIN命令来分析这条SQL的执行情况。
2. 通过 key和 key_len可以检查是否命中了索引，如果已经添加了索引，也可以判断索引是否有效。
3. 通过 type字段可以查看SQL是否有优化空间，比如是否存在全索引扫描或全表扫描。
4. 通过 extra建议可以判断是否出现回表情况，如果出现，可以尝试添加索引或修改返回字段来优化。

存储引擎

概念

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

特性	MyISAM	InnoDB	MEMORY
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
外键	不支持	支持	不支持

MySql体系结构

InnoDB特点

概念

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

特点

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

文件

• xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
• xxx.frm：存储表结构（MySQL8.0时，合并在表名.ibd中）

面试题

面试官： MYSQL支持的存储引擎有哪些，有什么区别？

回答：

1. 在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory
2. InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁
3. MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多
4. Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

索引

概念

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

索引的数据结构

B+Tree：优化BTree（非叶子节点：索引+指针、叶子节点：索引+数据[地址]）

B+Tree的好处

1. 降低树的高度
2. 叶子节点按照索引排好序，支持范围查找，速度会很快
3. 千万条数据，只有2次磁盘IO

聚簇索引和非聚簇索引

特点

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚簇索引选取规则

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

回表查询

通过二级索引找到对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表

索引覆盖

概念

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

MySql超大分页处理

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

SELECT * FROM t,(SELECT id FROM t ORDER BY id LIMIT 9000000,10) a where t.id = a.id;

索引创建原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效

1. 违反最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引。
2. 范围查询右边的列，不能使用索引 。
3. 不要在索引列上进行运算操作，索引将失效。
4. 字符串不加单引号，造成索引失效。
5. 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

面试题

面试官： 了解过索引吗？（什么是索引）

回答：

嗯，索引在项目中非常常见，它是一种帮助MySQL高效获取数据的数据结构，主要用来提高数据检索效率，降低数据库的I/O成本。同时，索引列可以对数据进行排序，降低数据排序的成本，也能减少CPU的消耗。

面试官： 索引的底层数据结构了解过吗？

回答：

1. MySQL的默认存储引擎InnoDB使用的是B+树作为索引的存储结构。
2. 选择B+树的原因包括：节点可以有更多子节点，路径更短；磁盘读写代价更低，非叶子节点只存储键值和指针，叶子节点存储数据；B+树适合范围查询和扫描，因为叶子节点形成了一个双向链表。

面试官： B树和B+树的区别是什么呢？

回答：

B树和B+树的主要区别在于：

1. B树的非叶子节点和叶子节点都存放数据，而B+树的所有数据只出现在叶子节点，这使得B+树在查询时效率更稳定。
2. B+树在进行范围查询时效率更高，因为所有数据都在叶子节点，并且叶子节点之间形成了双向链表。

面试官： 什么是聚簇索引什么是非聚簇索引？

回答：

聚簇索引是指数据与索引放在一起，B+树的叶子节点保存了整行数据，通常只有一个聚簇索引，一般是由主键构成。

非聚簇索引则是数据与索引分开存储，B+树的叶子节点保存的是主键值，可以有多个非聚簇索引，通常我们自定义的索引都是非聚簇索引。

面试官： 知道什么是回表查询吗？

回答：

回表查询是指通过二级索引找到对应的主键值，然后再通过主键值查询聚簇索引中对应的整行数据的过程。

面试官： 知道什么叫覆盖索引吗？

回答：

覆盖索引是指在SELECT查询中，返回的列全部能在索引中找到，避免了回表查询，提高了性能。使用覆盖索引可以减少对主键索引的查询次数，提高查询效率。

面试官： MySQL超大分页怎么处理？

回答：

超大分页通常发生在数据量大的情况下，使用 LIMIT分页查询且需要排序时效率较低。

可以通过覆盖索引和子查询来解决。

首先查询数据的ID字段进行分页，然后根据ID列表用子查询来过滤只查询这些ID的数据，因为查询ID时使用的是覆盖索引，所以效率可以提升。

面试官： 索引创建原则有哪些？

回答：

创建索引的原则包括：

• 表中的数据量超过10万以上时考虑创建索引。
• 选择查询频繁的字段作为索引，如查询条件、排序字段或分组字段。
• 尽量使用复合索引，覆盖SQL的返回值。
• 如果字段区分度不高，可以将其放在组合索引的后面。
• 对于内容较长的字段，考虑使用前缀索引。
• 控制索引数量，因为索引虽然可以提高查询速度，但也会影响插入、更新的速度。

面试官： 什么情况下索引会失效？

回答：

索引可能在以下情况下失效：

• 没有遵循最左匹配原则。
• 使用了模糊查询且 %号在前面。
• 在索引字段上进行了运算或类型转换。
• 使用了复合索引但在中间使用了范围查询，导致右边的条件索引失效。

SQL优化

表设计优化

参考阿里开发手册

比如设置合适的数值（tinyint int bigint），要根据实际情况选择

比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

SQL语句优化

1. SELECT语句务必指明字段名称（避免直接使用select * ）
2. SQL语句要避免造成索引失效的写法
3. 尽量用union all代替union union会多一次过滤，效率低
4. 避免在where子句中对字段进行表达式操作
5. Join优化：能用inner join就不用left join、right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序。

主从复制、读写分离

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

读写分离解决的是，数据库的写入，影响了查询的效率。

面试题

面试官： SQL的优化经验有哪些？

回答：

SQL优化可以从以下几个方面考虑：

1. 建表时选择合适的字段类型。
2. 使用索引，遵循创建索引的原则。
3. 编写高效的SQL语句，比如避免使用 SELECT *，尽量使用 UNION ALL代替 UNION，以及在表关联时使用 INNER JOIN。
4. 采用主从复制和读写分离提高性能。
5. 在数据量大时考虑分库分表。

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

回答：

创建表时，我们主要参考《嵩山版》开发手册，选择字段类型时结合字段内容，比如数值类型选择 TINYINT、INT、BIGINT等，字符串类型选择 CHAR、VARCHAR或 TEXT。

面试官： 在使用索引的时候，是如何优化呢？

回答：

在使用索引时，我们遵循索引创建原则，确保索引字段是查询频繁的，使用复合索引覆盖SQL返回值，避免在索引字段上进行运算或类型转换，以及控制索引数量。

面试官： 你平时对SQL语句做了哪些优化呢？

回答：

我对SQL语句的优化包括指明字段名称而不是使用 SELECT *，避免造成索引失效的写法，聚合查询时使用 UNION ALL代替 UNION，表关联时优先使用 INNER JOIN，以及在必须使用 LEFT JOIN或 RIGHT JOIN时，确保小表作为驱动表。

事务

概念

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

ACID

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

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted 未提交读	√	√	√
Read committed 读已提交	×	√	√
Repeatable Read(默认) 可重复读	×	×	√
Serializable 串行化	×	×	×

面试题

面试官： 事务的特性是什么？可以详细说一下吗？

回答：

1. 事务的特性是ACID，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
2. 例如，A向B转账500元，这个操作要么都成功，要么都失败，体现了原子性。
3. 转账过程中数据要保持一致，A扣除了500元，B必须增加500元。
4. 隔离性体现在A向B转账时，不受其他事务干扰。
5. 持久性体现在事务提交后，数据要被持久化存储。

面试官： 并发事务带来哪些问题？

回答：

1. 并发事务可能导致脏读、不可重复读和幻读。
2. 脏读是指一个事务读到了另一个事务未提交的“脏数据”。
3. 不可重复读是指在一个事务内多次读取同一数据，由于其他事务的修改导致数据不一致。
4. 幻读是指一个事务读取到了其他事务插入的“幻行”。

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

回答：

解决这些问题的方法是使用事务隔离。

MySQL支持四种隔离级别：

1. 未提交读（READ UNCOMMITTED）：解决不了所有问题。
2. 读已提交（READ COMMITTED）：能解决脏读，但不能解决不可重复读和幻读。
3. 可重复读（REPEATABLE READ）：能解决脏读和不可重复读，但不能解决幻读，这也是MySQL的默认隔离级别。
4. 串行化（SERIALIZABLE）：可以解决所有问题，但性能较低。

日志

缓冲池（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可以实现事务的一致性和原子性。

MVCC

概念

全称Multi-Version Concurrency Control，多版本并发控制。

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

原理

记录中的隐藏字段

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

undo log版本链

readview

概念

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

核心字段

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	ReadView创建者的事务ID

不同的隔离级别，生成ReadView的时机不同：

• READ COMMITTED：在事务中每一次执行快照读时生成ReadView。
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。

快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed：每次select，都生成一个快照读。
• Repeatable Read：开启事务后第一个select语句才是快照读的地方。

面试题

面试官： undo log和redo log的区别是什么？

回答：

redo log记录的是数据页的物理变化，用于服务宕机后的恢复，保证事务的持久性。

undo log记录的是逻辑日志，用于事务回滚时恢复原始数据，保证事务的原子性和一致性。

面试官： 事务中的隔离性是如何保证的呢？（你解释一下MVCC）

回答：

1. 事务的隔离性通过锁和多版本并发控制（MVCC）来保证。
2. MVCC通过维护数据的多个版本来避免读写冲突。
3. 底层实现包括隐藏字段、undo log和read view。隐藏字段包括trx_id和roll_pointer。undo log记录了不同版本的数据，通过roll_pointer形成版本链。
4. read view定义了不同隔离级别下的快照读，决定了事务访问哪个版本的数据。

主从同步、分库分表

主从同步

概念

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

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

步骤

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

分库分表

目的

• 分担访问压力
• 解决存储压力

时机

1. 单表的数据量达1000W或20G以后。
2. 优化已解决不了性能问题。
3. IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）。

垂直拆分

垂直分库

概念

以表为依据，根据业务将不同表拆分到不同库中。

特点

• 按业务对数据分级管理、维护、监控、扩展
• 在高并发下，提高磁盘IO和数据量连接数

垂直分表

概念

以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点

1. 冷热数据分离。
2. 减少IO过渡争抢，两表互不影响。

水平拆分

水平分库

概念

将一个库的数据拆分到多个库中。

特点

• 解决了单库大数量，高并发的性能瓶颈问题。
• 提高了系统的稳定性和可用性。

水平分表

概念

将一个表的数据拆分到多个表中(可以在同一个库内)。

特点

• 优化单一表数据量过大而产生的性能问题。
• 避免IO争抢并减少锁表的几率。

面试题

面试官： MySQL主从同步原理是什么？

回答：

MySQL主从复制的核心是二进制日志（Binlog）。

步骤如下：

1. 主库在事务提交时记录数据变更到Binlog。
2. 从库读取主库的Binlog并写入中继日志（Relay Log）。
3. 从库重做中继日志中的事件，反映到自己的数据中。

面试官： 你们项目用过MySQL的分库分表吗？

回答：

我们采用微服务架构，每个微服务对应一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

面试官： 那你之前使用过水平分库吗？

回答：

使用过。

当时业务发展迅速，某个表数据量超过1000万，单库优化后性能仍然很慢，因此采用了水平分库。

我们首先部署了3台服务器和3个数据库，使用mycat进行数据分片。

旧数据也按照ID取模规则迁移到了各个数据库中，这样各个数据库可以分摊存储和读取压力，解决了性能问题。