MySQL面试篇

MySQL面试题主要内容

面试官在面试时主要会问以下这些方面的问题

SQL语句书写

UNION与UNION ALL的区别

UNION 和 UNION ALL 都是SQL语言中用来合并两个或多个查询结果集的集合操作符

• UNION：

  • 去重：执行UNION操作时会自动去除结果集中的重复行。
  • 性能消耗：因为要去重，所以UNION通常执行得比UNION ALL慢，尤其是在处理大量数据时。
  • 结果集：返回两个结果集合并后去掉重复行的所有行。

• UNION ALL：

  • 不去重：执行UNION ALL操作时不会去除结果集中的重复行。
  • 性能消耗：由于不需要去重，UNION ALL通常执行得比UNION快。
  • 结果集：返回两个结果集合并后的所有行，包括重复的行。

mysql中内连接左连接右连接外连接的区别

在MySQL中，连接（JOIN）是用来结合两个或多个数据库表中的行的操作

内连接（INNER JOIN）：

内连接返回两个表中都有匹配的行。内连接是默认的连接类型，如果不指定连接类型，MySQL会默认使用内连接。

左连接（LEFT JOIN）或左外连接（LEFT OUTER JOIN）：

左连接返回左表（表A）的所有行，即使在右表（表B）中没有匹配。

如果左表的行在右表中没有匹配，那么结果集中右表相关的列会包含NULL值。

右连接（RIGHT JOIN）或右外连接（RIGHT OUTER JOIN）：

右连接返回右表（表B）的所有行，即使在左表（表A）中没有匹配。

右连接通常用于确保右表的所有行都被包含在结果集中。

外连接（FULL OUTER JOIN）：

MySQL不支持标准SQL中的全外连接。全外连接返回左表和右表中的所有行。如果左表的行在右表中没有匹配，或者右表的行在左表中没有匹配，那么这些行也会被包含在结果集中，不匹配的列会包含NULL值。

在MySQL中，可以通过联合左连接和右连接来模拟全外连接。首先执行一个左连接，然后执行一个右连接，并将结果通过UNION操作合并在一起，以排除重复的行。

JOIN 的使用

假设有两个表，一个是 employees（员工表），另一个是 departments（部门表）。

employees 表结构如下：

• employee_id（员工ID）
• name（员工姓名）
• department_id（部门ID）

departments 表结构如下：

• department_id（部门ID）
• department_name（部门名称）

以下是一些使用 JOIN 的示例：

INNER JOIN

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

这个查询返回了所有有对应部门的员工的姓名和部门名称。

LEFT JOIN

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

这个查询返回了所有员工的姓名和部门名称，包括那些没有分配到部门的员工（这些员工的部门名称将是 NULL）。

RIGHT JOIN

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

这个查询返回了所有部门的名称以及属于这些部门的员工姓名，如果某个部门没有员工，则员工姓名将是 NULL。

FULL JOIN

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

这个查询返回了所有员工和所有部门的信息，包括那些没有员工或没有部门的记录。

UNION 的使用

以下是一些使用 UNION 的示例：

UNION

SELECT name FROM employees
UNION
SELECT department_name FROM departments;

这个查询返回了员工姓名和部门名称的合集，并且自动去除了重复项。

UNION ALL

SELECT name FROM employees
UNION ALL
SELECT department_name FROM departments;

这个查询返回了员工姓名和部门名称的合集，包括所有重复项。

在使用 UNION 和 UNION ALL 时，请注意以下几点：

• 所有 SELECT 语句中选择的列数必须相同。
• 对应列的数据类型必须兼容。
• 默认情况下，UNION 会去除重复的行，而 UNION ALL 不会。

在使用 JOIN 时，可以根据实际需求选择合适的 JOIN 类型，并注意连接条件是否正确。

MySQL-优化

定位慢查询

方案一：开源工具

调试工具：Arthas

运维工具：Prometheus 、Skywalking

方案二：MySQL自带慢日志

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

分析慢查询

SQL查询语句慢的分析方向，一般SQL语句执行慢的原因有以下几点：

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

采用MySQL自带的分析工具 EXPLAIN或者DESC，通过EXPLAIN 或者 DESC命令可以获取 MySQL 如何执行 SELECT 语句的信息。

语法：

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

下面举一个例子：

在得到的执行SQL语句的信息中，我们应该了解以下字段的意义（比较重要的索引）：

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

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

key_len: 索引占用的大小（通过key和key_len两个字段可以查看是否命中索引）

Extra: 额外的优化建议 

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

• NULL:在查询的时候没有使用到表
• system：查询系统中的表，MySql系统自带的表
• const：根据主键查询
• eq_ref：主键索引查询或唯一索引查询
• ref：索引查询
• range：范围查询
• index：索引树扫描
• all：全盘扫描

一般的sql连接类型要保证range以上。

存储引擎

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

MYSQL支持的存储引擎有MyISAM、InnoDB、MEMORY

下面是三种存储引擎的对比

MySQL体系结构

索引

介绍

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

索引的底层数据结构

下面我会依次分析二叉树、红黑树、B树、B+树，将这些数据结构进行对比，分析为什么最终索引的底层数据结构要采用B+树。

二叉树搜索树

相信有些许数据结构基础的小伙伴们对二叉树搜索树都有了解，其具体结构如下：

当采用二叉树作为索引时，并且把id作为索引，当id自增长时一个二叉树变成了一个单枝树，树就退化为了一个链表，相当于链表查询，查询时间为O(n)，因此相当于没有建立索引，查询效率非常低。当查询9这个元素时的过程如下图所示：

红黑树

红黑树其实本质上也是一种二叉树，它可以把二叉树优化成平衡二叉树，如下图所示：

红黑树的创建过程：

从上图可以看出每当当前节点的左节点或者右节点超过两个时，红黑树会将其平衡。当使用这种数据结构存储MySQL索引时，会存在一个缺陷就是树的高度太高，导致读取速度很慢，比如查找上图中的9这个节点，就需要查询5、8、9三次，由于MySQL数据是存储在磁盘上的，因此需要三次磁盘IO读取，当数据表数据量很大时，这就显得力不从心了，最糟糕要进行树的高度h次磁盘IO读取。

B树

由上面知道，既然数据量大，从而导致树的高度不断增大，有什么方法可以限制树的高度呢？比如1000万条记录我能否将它浓缩成H<=4这样比较小的数呢？既然不能不能往纵向发展那就往横向发展，B树诞生了，如下图所示：

B树具有以下特点：
①叶子节点具有相同的深度，叶子节点的指针为空。
②所有索引元素不重复。
③节点中的数据索引从左到右递增排列。
它的存储结构如下图所示，每个节点的索引和数据都存储在一起。

原理：比如我们查找49这个元素，首先从根节点查找，一次性把所有的根节点node放到内存RAM中(MySQL的限制大小为16KB)，然后在内存中查找49结果在15—56之间，因此找到指向15—56之间的指针，根据这个指针找到20----49这个大节点，然后将这个大节点放Node到RAM中，然后在内存中找49，这样查询速度比红黑树大大提升。

B+树

MySQL索引并没有采用B树而是采用B+树，B+树相对于B树具有以下特点：
①非叶子节点不存储data,只存储索引(指针)，可以放更多的索引。
②叶子节点包含所有索引字段。
③叶子节点用双向指针连接，提高区间访问的性能。

在Mysql中，索引一般为bigint类型占8个字节，对于索引后面的指针占用6个字节，而Mysql给第一层大节点的限制内存大小为16KB，因此大概可以存储16KB/(6+8)B=1170个索引，同样第二层也可以存储1170个，而对于高度为3的B+树来说，假设第三层的索引和数据占用1KB大小，可以存放16KB/1KB=16个索引(加数据)，因此总共可以存储1170 x 1170 x 16 = 21,902,400个数据，2000多万条数据，令人诧异！！

B树与B+树对比：

• 磁盘读写代价B+树更低（B+树非叶子节点只存储指针，不存储数据，存储压力较小，在B树中查询某个数据时，它会把路径上的所有数据都获取到，因此磁盘读写代价更大）
• 查询效率B+树更加稳定（B+树的所有数据都存储到了叶子节点上，查询数据时，都要从根节点到叶子节点，因此查询路径长度都差不多，查询效率稳定）
• B+树便于扫库和区间查询（B+树节点之间采用双向指针，进行范围查询时更加方便）

聚簇索引和非聚簇索引（聚集索引和二级索引）

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，表记录的物理存储顺序与索引顺序一致。	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，表记录的物理存储顺序与索引顺序无关	可以存在多个

聚集索引选取规则:

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

下面举一个例子帮助理解

在下面的表中，id作为主键，因此id就是聚集索引，索引结构的叶子节点上存储了对应id这一行的所有数据。表中还为name字段设置了索引，因此name的索引就是二级索引，该索引结构的叶子节点只存储了与name字段关联的主键和name字段上的数据。

回表查询

回表查询是指在数据库查询过程中，如果需要获取的数据不在查询所使用的索引上，就需要通过回表操作去主表或者其他表中查找相应的数据。

下面举个例子

例如我们对上面的表进行下面这样的查询

select * from user where name = ‘Arm’;

这个查询就涉及到了回表查询，由于查询是根据name字段进行的，所以索引会先进入到name的索引树中进行查询，查询到叶子节点时发现只得到了主键数据，为了得到完整的数据，则会继续根据主键到主键的索引树中继续查询。

覆盖索引

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

id	name	gender	createdata
2	Arm	1	2021-01-01
3	Lily	0	2021-05-01
5	Rose	0	2021-02-14
6	Zoo	1	2021-06-01
8	Doc	1	2021-03-08
11	Lee	1	2020-12-03

select * from tb_user where id = 1          覆盖索引

select id，name from tb_user where name = ‘Arm’          覆盖索引

select id，name，gender from tb_user where name = ‘Arm’        非覆盖索引(需要回表查询)

MYSQL超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

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

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

例如下面这条sql语句

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

创建索引的原则

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

什么情况下索引会失效

给tb_seller创建联合索引，字段顺序：name，status，address

要如何快速判断索引是否失效了呢？执行计划explain

下面是索引失效的情况

1). 违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：

违法最左前缀法则 ， 索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

2). 范围查询右边的列，不能使用索引 

根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

3). 不要在索引列上进行运算操作， 索引将失效

4). 字符串不加单引号，造成索引失效

 MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

5).以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

sql的优化经验

• 表的设计优化（参考阿里开发手册《嵩山版》）

1. 比如设置合适的数值（tinyint   int   bigint），要根据实际情况选择
2. 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低
3. 对外键设计的考量：开发规范中为什么要禁用外键约束-阿里云开发者社区 (aliyun.com)
4. 在不使用外键的情况下如何关联表呢？使用中间表；创建触发器；在应用层解决外键的概念

• SQL语句优化

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

• 主从复制、读写分离

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

事务

事务特性

什么是事务

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

事务特性：ACID

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

并发事务问题  隔离级别

并发事务问题

脏读

一个事务读到另外一个事务还没有提交的数据。

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读

一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

怎么解决并发事务的问题呢？

解决方案：对事务进行隔离

隔离级别

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

注意：事务隔离级别越高，数据越安全，但是性能越低。

undo log和redo log的区别

缓冲池（buffer pool）:主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

数据页（page）:是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据。一个表中的数据有可能由好几个数据页组成。

可能出现的问题：当数据在缓冲池中被修改后，但是还没有同步到磁盘中时mysql挂了，这时候之前内存中变化的数据就丢失了，这就违背了事务持久化的特性了。

解决方法

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。

1. 重做日志缓冲（redo log buffer）：

   • 这是一块在内存中的区域，用于暂时保存事务对数据页所做的修改（例如，页面的添加、更新或删除操作）。
   • 当事务开始执行时，InnoDB会记录对这些数据页的修改，但这些修改并不会立即写入磁盘上的数据文件，而是首先写入重做日志缓冲。
   • 重做日志缓冲是易失的，也就是说，如果系统发生故障，缓冲中的内容会丢失。因此，需要定期将缓冲中的内容刷新到磁盘上。

2. 重做日志文件（redo log file）：

   • 这是一组在磁盘上的文件，用于持久化存储重做日志缓冲中的内容。
   • 当事务提交时，InnoDB会将重做日志缓冲中的内容写入重做日志文件。这个过程称为日志刷新（log flush）。
   • 重做日志文件是持久的，即使在系统故障后，这些文件中的内容仍然存在，可以用于系统恢复。

3. 脏页刷新（dirty page flush）：

   • InnoDB使用一种称为“脏页”机制来管理内存中的数据页。当数据页被修改后，它就被标记为“脏页”。
   • 后台线程会定期将这些脏页刷新到磁盘上的数据文件中，这个过程称为“检查点”（checkpoint）。
   • 在刷新脏页之前，InnoDB确保对应的重做日志已经写入磁盘，这样即使在刷新过程中发生故障，系统也可以使用重做日志来恢复未刷新的数据。

4. 数据恢复（recovery）：

   • 当MySQL启动时，InnoDB会检查重做日志文件，确定是否有未提交的事务。
   • 如果有未提交的事务，InnoDB会应用重做日志中的记录，将事务的修改应用到数据文件中，以确保数据的持久性和一致性。

当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

undo log

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

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

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

区别

• redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
• undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
• redo 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 版本链

对于事务2：

对于事务3：

对于事务4：

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readView

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

当前读

读取的是记录的最新版本，读取时还要保证其他事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。

快照读

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

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

ReadView中包含了四个核心字段：

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

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

READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。

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

RC级别下readview的生成

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

RR级别下readview的生成

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

主从同步

介绍

MySQL 主从同步是一种典型的高可用解决方案，它可以实现数据的自动复制和多副本备份。在主从同步中，一个 MySQL 实例（称为主库）会将其数据更改记录在二进制日志文件中，这些更改会被发送到多个 MySQL 从库中，并在从库上执行以保持与主库的数据同步。

原理

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和数据量连接数。

垂直分表

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

拆分规则：

• 把不常用的字段单独放在一张表。
• 把text，blob等大字段拆分出来放在附表中。

特点：

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

水平分库

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

路由规则：

• 根据id节点取模
• 按id也就是范围路由，节点1(1-100万 ),节点2(100万-200万)

特点：

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

水平分表

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

特点：

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

产生的问题以及如何解决

分库之后的问题

• 分布式事务一致性问题
• 跨节点关联查询
• 跨节点分页、排序函数
• 主键避重

通过分库分表中间件

• sharding-sphere
• mycat

mysql分布式主键应该如何选择（主键避重）

UUID

UUID（Universally Unique Identifier）是一种由128位数字组成的标识符，能够在全球范围内保证唯一性。UUID的生成方式通常基于MAC地址和时间戳等信息。

优点：

• 唯一性：UUID能够在全球范围内保证唯一性，不同节点生成的UUID不会发生冲突。
• 不依赖数据库：UUID的生成不依赖于数据库，可以在客户端生成，降低数据库的负担。

缺点：

• 可排序性：UUID的生成方式导致无法具备良好的可排序性，不适合进行范围查询和排序操作。
• 存储空间：UUID占用的存储空间较大，会增加数据库的存储成本。

适用场景：

• 高并发场景：UUID的唯一性保证了高并发场景下数据的正确性。
• 数据分散场景：UUID能够保证分布式数据库中数据的均匀分散。

雪花算法
雪花算法（Snowflake）是Twitter开源的一种分布式ID生成算法。它将一个64位的整数划分为不同的部分，分别表示时间戳、数据中心ID和机器ID等信息。

优点：

• 唯一性：雪花算法能够在分布式系统中保证生成的ID的唯一性。
• 可排序性：雪花算法生成的ID中包含时间戳信息，具备良好的可排序性。
• 高性能：雪花算法生成ID的性能较高，适用于高并发场景。

缺点：

• 数据中心ID和机器ID的限制：雪花算法需要预先分配数据中心ID和机器ID，限制了系统的可扩展性。
• 时钟回拨问题：如果系统时钟发生回拨，可能导致生成的ID不唯一。

适用场景：

• 高性能要求：雪花算法适用于高并发场景，能够满足快速生成唯一ID的需求。
• 有限数据中心和机器数量：雪花算法适用于规模较小的分布式系统，不适用于大规模的数据中心和机器数量不固定的场景。

数据库自增主键
数据库自增主键是传统的主键生成方式，通过数据库的自增机制来生成唯一的主键值。

问题：

• 单点故障：数据库自增主键依赖于单个数据库节点，如果节点发生故障，会导致主键生成的停止。
• 数据一致性：在分布式环境下，多个节点同时生成自增主键可能导致主键冲突。
• 可扩展性：数据库自增主键不便于水平扩展，限制了系统的可扩展性。

适用场景：

• 中小规模应用：对于中小规模的应用，数据库自增主键可以满足需求。
• 业务逻辑简单：如果业务逻辑简单，不需要考虑分片和数据一致性等问题，可以使用数据库自增主键。

场景题

要存储五千万条数据，如何设计库表结构

mysql数据库故障如何检测

面试题问答示例

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

候选人：

嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题

如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

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

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

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

候选人：嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗

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

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

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

候选人：第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

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

候选人：

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

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

面试官：知道什么是回表查询嘛 ?

候选人：嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表

【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

面试官：知道什么叫覆盖索引嘛 ?

候选人：嗯~，清楚的

覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段

面试官：MYSQL超大分页怎么处理 ?

候选人：嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了

因为查询id的时候，走的覆盖索引，所以效率可以提升很多

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

候选人：嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。

还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。

如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

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

候选人：嗯，这个情况比较多，我说一些自己的经验，以前遇到过的

比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。

我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效

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

面试官：sql的优化的经验

候选人：嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如

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

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

候选人：这个我们主要参考的阿里出的那个开发手册《嵩山版》，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int 、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型

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

候选人：【参考索引创建原则 进行描述】

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

候选人：嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select * ，还有就是要注意SQL语句避免造成索引失效的写法；如果是聚合查询，尽量用union all代替union ，union会多一次过滤，效率比较低；如果是表关联的话，尽量使用innerjoin ，不要使用用left join right join，如必须使用 一定要以小表为驱动

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

候选人：嗯，这个比较清楚，ACID，分别指的是：原子性、一致性、隔离性、持久性；我举个例子：

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰

在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）

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

候选人：

我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题

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

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

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

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

候选人：解决方案是对事务进行隔离

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

第一个是，未提交读（read uncommitted）它解决不了刚才提出的所有问题，一般项目中也不用这个。第二个是读已提交（read committed）它能解决脏读的问题的，但是解决不了不可重复读和幻读。第三个是可重复读（repeatable read）它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。第四个是串行化（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的分库分表吗？

候选人：

嗯，因为我们都是微服务开发，每个微服务对应了一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

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

候选人：

嗯，这个是使用过的，我们当时的业务是(xxx)，一开始，我们也是单库，后来这个业务逐渐发展，业务量上来的很迅速，其中(xx)表已经存放了超过1000万的数据，我们做了很多优化也不好使，性能依然很慢，所以当时就使用了水平分库。

我们一开始先做了3台服务器对应了3个数据库，由于库多了，需要分片，我们当时采用的mycat来作为数据库的中间件。数据都是按照id（自增）取模的方式来存取的。

当然一开始的时候，那些旧数据，我们做了一些清洗的工作，我们也是按照id取模规则分别存储到了各个数据库中，好处就是可以让各个数据库分摊存储和读取的压力，解决了我们当时性能的问题