###

背景

自从3月底女朋友被公司通知裁员到现在已经接近4个月了，找工作的这四个月我非常难受，我女朋友更加难受，特别是前两个月每天过的的非常煎熬，女朋友在上家公司呆了4年多了，我和她都没想到会发生这事，居然通知3天就要走人，当时毫无心理准备，一开始的时候心里天天想ma xx 千万遍。 ![截屏2023-08-11 23.00.28.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a452a5116f1f45bfaf24a292fc5ab1d3~tplv-k3u1fbpfcp-watermark.image#?w=1224\&h=498\&s=202073\&e=png\&b=fefefe) 女生在程序员行业面试机会相对较少，但是女朋友一直都没有放弃，中间不断调整心态，还好皇天不负有心人，经过最近两个月的精心准备，女朋友找好工作了，而且是头部上市医药公司，和之前大健康行业也匹配，薪资也上浮了一点。所以我们相信只要准备好了，机会一定会有的。 ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/91fdf8dd47fa416f8a88942a387d1248~tplv-k3u1fbpfcp-watermark.image#?w=1080\&h=1080\&s=438252\&e=png\&b=f9f5f5) 最近4个月，女朋友面试了杭州，上海，广州深圳等大大小小几十个公司，虽然也拿了大概10多个offer,但是整个过程还是非常心累的，基本每天都是在刷题中度过，由于女朋友之前公司呆了4年，期间没有任何去准面试相关的题目，特别是最近几年8股文越来越多，所以前面两个月主要是在复习和整理知识的阶段，到6月才陆续进入较好的面试状态了，通过这次也告诉我们，就算不打算看机会，也要时刻保持和市场接触，了解岗位相关的知识。接下来的文章，我将整理女朋友最近面试过的相关面试题，方便以后时刻能找着记录。今天就从Mysql专题开始，Mysql是Java后端工程师面试必备的一个知识，面试题也相对比较多。下面思维导图就是从女朋友面试的题目中整理出来的Mysql相关面试题，确实是非常高频的题目，有需要的朋友记得收藏起来哦。 ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/629109d420944a6cb5c08055d2a8d7fc~tplv-k3u1fbpfcp-watermark.image#?w=2552\&h=1284\&s=259741\&e=png\&b=fffdfd) ###

进入正题-Mysql面试题复盘

####

架构相关面试题

* Mysql架构简述这个题目一般是面试官开始进入Mysql面试的开场题，通过这个题目引入Mysql的话题，同时可以考察面试者对Mysql整体结构的认知情况。在Mysql官网有架构图，我们回答问题前可以自己在脑袋里回想一下这个图。 ![截屏2023-08-12 00.01.57.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b63ce165f87d47b1afc5f1fc42ab822f~tplv-k3u1fbpfcp-watermark.image#?w=1132\&h=1124\&s=393803\&e=png\&b=f9f8f8) 从官方图可以看出Mysql是一种C/S架构，mysql本质上是一个数据存取系统，连接磁盘和客户端，完成数据存储和查询。总体上采用了分层的架构模式，从上往下分别是连接层，服务层，还有最核心的一层就是可插拔式的存储引擎层。这里如果对各个层比较熟悉的话，可以和面试官聊一下各个层的作用。 > 连接层：负责客户端连接请求处理，认证，要使用mysql，一般通过账号密码先和mysql服务器经过tcp三次握手建立通信连接，然后就可以交换请求数据了。 > 服务层：服务层提供了存储数据的接口，sql解析器，查询优化器，缓存等组件 > 存储引擎层：负责存取数据，实现与磁盘等存储设备交互。常用的存储引擎有Innodb,MyISAM，有些面试官可能还会问不同存储引擎的区别。 * Mysql高可用架构这个问题还是比较考验知识的深度和广度的。什么是高可用？我们先看看高可用的定义： ![image.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/dfc3498e56144ebc868eb984b7794836~tplv-k3u1fbpfcp-watermark.image#?w=2492\&h=378\&s=121999\&e=png\&b=ffffff) 高可用性简称`HA`,简单的说就是一个系统尽可能提供更长的正常在线访问时长的能力。通过一些容错手段来实现高可用。通常用几个9来表示一个软件系统高可用的程度。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e4351035f3e74ebdbba3b1bb41807897~tplv-k3u1fbpfcp-watermark.image#?w=472\&h=444\&s=36889\&e=png\&b=f5f6f8) 如果能达到5个9，一年只能有5分钟15s不可用，我们的系统已经非常强悍了。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d7f5e23c013142fc8e6fb0a42676b7aa~tplv-k3u1fbpfcp-watermark.image#?w=300\&h=300\&s=34468\&e=png\&a=1\&b=fcfcfc) 为什么Mysql需要做高可用呢？ Mysql作为大部分企业业务数据的持久化存储介质，高可用能力是必须具备的。在大型的企业应用中，可能有成百上千的数据库，每个数据库可能都承担了非常重要的业务数据存储和查询功能，比如电商公司里的用户数据，商品数据，交易数据，优惠券数据，会员数据等等。高可用需要考虑哪些因素？一、需要集群部署模式，避免单点故障，如果是单机部署，那么单点故障就没法实现高可用了。二、需要主从同步数据机制，保证主从节点的数据具有一致性三、自动选主能力，需要具备自动故障转移failover的能力第一，二两点官方就提供支持，可以实现多个节点和主节点数据一致。 Mysql支持以binlog 偏移量和全局事务gtid两种形式的数据复制。 gtid代表全局事务id，每个数据库变更都会生成一个gtid，也是存在binlog文件中。 gtid数据格式: `GTID = source_id:transaction_id ` 例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:23 存储在mysql数据库的gtid\_executed表中。 ![image.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8c46ec848bae4ff993ff91730061282e~tplv-k3u1fbpfcp-watermark.image#?w=1284\&h=408\&s=118257\&e=png\&b=fcfcfc) 比如数据从数据库A同步到数据库B ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b4ebecd47ade4648ab084be1d97260be~tplv-k3u1fbpfcp-watermark.image#?w=314\&h=482\&s=17800\&e=png\&b=fdfdfd) 以全局事务id同步方式为例，实现数据同步的步骤如下：第一步：在A,B数据库配置文件中，开启打开全局事务id模式 gtid_mode=ON enforce-gtid-consistency=ON 第二步：开启副本节点自动定位 ```mysql mysql> CHANGE MASTER TO > MASTER_HOST = *host*, > MASTER_PORT = *port*, > MASTER_USER = *user*, > MASTER_PASSWORD = *password*, > MASTER_AUTO_POSITION = 1; ``` 第三步：开启slave节点 ```mysql mysql> START SLAVE; ``` 配置好了，数据同步就会正常进行了。后续主从同步通过binlog数据实现的，binlog格式有三种。 raw:记录的是被操作的数据行的主键id以及执行的server id statment:当binlog\_format=statement时，binlog里面记录的就是SQL语句的原文。 mix:折中statement和row。 ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/89f6a19eec3f430697706f00ca5a1e14~tplv-k3u1fbpfcp-watermark.image#?w=1498\&h=432\&s=140200\&e=png\&b=cfe8cc) 复制数据过程是通过多个线程处理的，分别是dump线程，io线程，sql线程，在官网有说明 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4c6b38d42bad4124abfe5d7155c18183~tplv-k3u1fbpfcp-watermark.image#?w=2060\&h=1064\&s=249006\&e=png\&b=fefefe) 复制数据的过程如下： ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8da762569bdf4e188ee63e0b3b278ae9~tplv-k3u1fbpfcp-watermark.image#?w=1990\&h=828\&s=119082\&e=png\&b=fdfdfd) 上面已经知道了主从复制的能力，具备了数据备份和避免单点故障的功能，但是如果主节点出现故障，还需要从节点选举新的主节点的能力。 Mysql Cluster是官方的实现的高可用方案，它提供了自动选举主节点的功能。在mysql 8.0之后推出了innodb ha，它是mysql官方提供的高可用方案。下面我们看看他的组成：

那么成熟的高可用方案还有有哪些？在博主从事的公司中，大多数还是买的云产品，比如腾xun云，阿li云，他们提供的Mysql数据库都是支持高可用的. 开源的产品有：MysqlCluster、 **MHAMHA**（Master High Availability）、Galera Cluster * Mysql分库分表方案这个问题考察的知识也比较多，随着公司业务的增长，分库分表是提升系统吞吐量和稳定性必须经历的一个过程，Mysql分库分表主要有垂直拆分和水平拆分两种形式。我们以单个业务领域内来说明，比如电商公司的会员域，优惠券域，在分库分表中，需要弄明白一些问题，这样就可以知道分库分表的方案了： 1、数据库需要分多少库，每个表需要分多少张表。这个点需要分析目前业务数据的总量，增量情况，同时还要知道目前数据库的总qps,tps。比如优惠券库，用户优惠券表数据存量在4亿，日增量500万，总qps是10000，如果我们拆8个库，每个库有8张用户券表，每个库需要存5000万数据，每个表需要存约600万行记录，每个库需要承担的qps约1200。假设4c8g的数据库支持该qps。我们接着分析数据增长情况，假如按这种模式分库分表，那么1个月增量是1.5亿，6个月是9亿数据，那么每个库需要存储1.1亿数据，每个表存1300万数据了。因为mysql是使用b+树作为存储结构，3层B+树支持1000万级别的数据存储，因此如果按这个方案存6个月数据，6个月之前的数据归档到冷数据库，保证6个月的数据在每个表存储量在千万级别，这样是可行的。 2、业务主要按什么条件查询，也就是选择哪个属性作为分片key。这个需要按照业务实际情况分析，比如用户优惠券，用户在C端都是通过userId查看自己的优惠券信息，因此我们可以使用userId作为分片key,但是在客服后台，用户也可能按时间按券模板等字段查看，这种情况最好是将用户优惠券数据同步到es这种快速检索存储系统上，可以丰富后台查询的使用场景。 3、第三个就是选择一个分库分表中间件了。分库分表数和分片key都确定好了，我们就可以选择一种分库分表的中间件来实现这个需求了。最常用的有mycat,shardingjdbc，有些公司会自研，有些公司也会使用云上的产品，比如阿里的drds,tidb等 ####

索引相关面试题

* 索引有哪些类型？这个问题我认为按如下方式回答会比较全面 1. 根据底层数据结构可分为：B+ tree 索引、Hash 索引、Full-Text 索引； 2. 根据物理存储方式可分为：聚簇索引、二级索引（辅助索引、非聚簇索引）； 3. 根据索引字段特性可分为：主键索引、普通索引、唯一索引、前缀索引； 4. 根据索引组成的字段个数可分为：单列索引、联合索引（复合索引、组合索引）。 * 索引实现原理 innodb底层使用B+树作为索引的存储数据结构，改良版的多路平衡二叉树，比B树的性能更好。特点如下： 1、关键字数和路数相等。B树存储更多关键字，路树更多，B+树也做到了。 2、只有叶子节点才存储数据，其他节点存关键字。 3、叶子节点保存了相邻节点的指针，形成了双向链表，更好的支持范围查找和排序。 * 一个3层的B+树，可以存储多少行记录呢？回答这个问题，需要明白在innodb中采用页来作为磁盘和内存的交换基本单位的。 1kb(千字节)=1024Byte（字节）=8192b（比特位）\ 16kb(千字节)=16384Byte(字节)=131072b（比特位）\ 前提：数据库最小单位1页=16kb千字节=16384字节，假如一个表主键索引（bigint类型）占有8Byte，同时假设页指针也是8Byte，共16Byte。那么1页16k=16384字节可用存约1000个关键字；假设1条记录占1k,那么1页16k可存约16条记录。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/61a6d802f16947948b260efd3eb74ab8~tplv-k3u1fbpfcp-watermark.image#?w=2570\&h=1076\&s=164852\&e=png\&b=fdfdfd) 所以三层的B+树就可以存储千万行记录了。 * 索引失效的场景这个题除了考虑一些经验以及对Mysql优化器的了解： 1、字符串不加引号，出现隐式转换 2、索引列使用函数 3、like查询，前面加%，这种情况可能用索引（索引条件下推），需要sql优化器来决定提示优化器使用索引的方式： 1、Use Index(index\_name) 查询优化器不一定使用 2、Force Index(index\_name) 查询优化器一定会使用，索引就是不生效，索引数据在更新数据后需要维护，可能维护不及时，可以使用analyze table table\_name; 操作，或者是mysql的sql优化器是基于开销的优化器，如果优化器认为走全表索引性能更高，则会走全表扫描，不会走指定的索引。 ####

锁相关面试题

* 什么是间隙锁，临键锁，记录锁在数据库中有表锁，行锁，间隙锁，临键锁，记录锁是指不同的行锁。首先说说记录锁，记录锁比较好理解，它是锁独立的记录，锁的是主键索引或者唯一索引，如果我们根据主键索引和唯一索引记录操作数据的时候，那么会加记录锁。间隙锁，锁住的是一个范围，比如下面user表，有3条记录 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/2997594ce76c4709a7c3d6e33914c252~tplv-k3u1fbpfcp-watermark.image#?w=712\&h=204\&s=8967\&e=png\&b=fefefe) 分为4个区间`（负无穷，1),(1,5),(5,9),(9,正无穷),也就是区间数=记录数+1`。那么插入一条记录主键为4的数据时候，这个时候会锁住区间(1,5)，不包含具体的记录,这就是间隙锁，这样可以解决幻读的问题。临键锁是记录锁+间隙锁的组合，它也叫next-key锁，它是左开右闭的区间。比如插入数据主键为4的记录，那么会锁住(1,5]区间的索引。我们可以通过系统参数查看锁的状态，验证加锁情况。 ```sql # 设置数据库系统参数，记录锁的状态信息 set global innodb_status_output = on; set global innodb_status_output_locks = on; # 可以查看加了有哪些锁？ show engine innodb status; ``` ####

事务相关面试题

* 事务隔离级别隔离级别是数据库的一个标准，分别有读未提交，读已提交，可重复读，序列化读四种级别，不同的隔离级别会产生不同的问题，比如脏读，不可重复读，幻读问题。 * 什么是脏读，幻读，Mysql是怎么解决的？脏读是指一个事务读到`其他事务`未提交的数据。mysql通过mvcc机制解决了脏读的问题。mvcc是多版本并发机制，通过在事务开始时创建最新的一个readview,使用read view数据结构，read view里面存储了当前数据库已经提交的事务，这样可以保证读取已提交的事务。幻读是指一个事务前后两次读取到其他事务新增的数据。mysql是通过间隙锁和临键锁来解决的。 ####

Mysql优化相关

* 慢查询如何排查与处理这个题目和实际工作非常接近，因为项目开发中避免不了写sql，在真实的线上环境也可能会遇到各种慢sql情况,你自己没发现,dba也会找上门来。如果有真实解决过慢sql的案例，建议使用自己解决案例的例子来给面试官讲解。 mysql慢日志检测默认是关闭的，可以通过命令`show variables like '%slow_query_log%';`查看是否开启慢日志。 ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8fa5a92ced8047128d209c410f77f60a~tplv-k3u1fbpfcp-watermark.image#?w=818\&h=168\&s=48661\&e=png\&b=fefefe) 并且默认执行了10s以上的sql才会认为是慢sql。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6a8aae180ef1456e93f7d858f1b0468d~tplv-k3u1fbpfcp-watermark.image#?w=590\&h=456\&s=61920\&e=png\&b=fefefe) 我们可以通过修改`slow_query_log`、`long_query_time`参数来开启记录我们的慢sql。 ```mysql [mysqlld] long_query_time=2 #5.5及以上版本配置如下选项 slow-query-log=On ``` 如果发现了慢sql我们怎么排查解决呢？下面分析一个工作中实际遇到的慢sql例子。在项目中有权限表和角色权限表： ```mysql # 权限表 CREATE TABLE `t_permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL COMMENT '名称', `parentId` bigint(20) NOT NULL DEFAULT '0' COMMENT '上级id', `url` varchar(256) DEFAULT NULL COMMENT '菜单为url', `sort` int(5) DEFAULT NULL COMMENT '排序(从小到大)', `code` varchar(64) DEFAULT NULL COMMENT '权限校验码', `icon` varchar(32) DEFAULT NULL COMMENT '图标', `sign` tinyint(4) DEFAULT NULL COMMENT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=321020301 DEFAULT CHARSET=utf8 COMMENT='权限表'; # 角色权限表 CREATE TABLE `t_role_permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `roleId` bigint(20) NOT NULL COMMENT '角色ID', `permissionId` bigint(20) NOT NULL COMMENT '权限表ID', `gmt_create` datetime DEFAULT NULL COMMENT '创建时间', `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间', PRIMARY KEY (`id`), KEY `idx_roleId` (`roleId`) ) ENGINE=InnoDB AUTO_INCREMENT=83608 DEFAULT CHARSET=utf8 COMMENT='角色表'; ``` 当时的功能是查询某个角色下的权限，sql如下 ```mysql SELECT b.NAME AS label, b.CODE AS VALUE, b.id FROM t_role_permission a JOIN t_permission b ON a.permissionId = b.id WHERE a.roleId = 36 AND b.parentId = 219000002 ORDER BY b.parentId, b.sort; ``` 首先是问题发现：这个例子是我们使用后台菜单功能时候发现的，菜单加载非常慢，根据链路发现查询权限接口非常慢，发现有时候需要1sd。由于这是业务系统的菜单和权限表，这两个表数据并不大。 > a表7855条记录，b表676条记录有了慢sql,我们就需要一个explain工具分析慢sql了。我们使用 `Explain ForMAT = JSON sql` 来执行sql,这样执行计划是一个json格式的： ```json { "query_block": { "select_id": 1, "ordering_operation": { "using_temporary_table": true, "using_filesort": true, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ref", "possible_keys": [ "idx_roleId" ], "key": "idx_roleId", "used_key_parts": [ "roleId" ], "key_length": "8", "ref": [ "const" ], "rows": 641, "filtered": 100 } }, { "table": { "table_name": "b", "access_type": "eq_ref", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "id" ], "key_length": "8", "ref": [ "notify_portal.a.permissionId" ], "rows": 1, "filtered": 100, "attached_condition": "(`notify_portal`.`b`.`parentId` = 219000002)" } } ] } } } ``` 从执行计划中，有几个关键的信息： > "using\_temporary\_table": true, > "using\_filesort": true, `using_temporary_table`代表使用了临时表，`using_filesort`代表使用了非索引列排序。 sql对`t_permission`表`parentId`、`sort`字段进行了排序，同时查询结果都是b表的数据，但是在b表中除了主键索引，其他索引都没有，这个时候对`t_permission`增加一个组合索引。 ```mysql ALTER TABLE `t_permission` ADD INDEX `id_parent_id_name_code_code`(`parentId`, `name`, `code`,`sort`); ``` 再看下执行计划 ```json { "query_block": { "select_id": 1, "ordering_operation": { "using_filesort": false, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ref", "possible_keys": [ "PRIMARY", "id_parent_id" ], "key": "id_parent_id", "used_key_parts": [ "parentId" ], "key_length": "8", "ref": [ "const" ], "rows": 1, "filtered": 100, "using_index": true, "attached_condition": "((`notify_portal`.`b`.`parentId` <=> 219000002))" } }, { "table": { "table_name": "a", "access_type": "ref", "possible_keys": [ "idx_roleId" ], "key": "idx_roleId", "used_key_parts": [ "roleId" ], "key_length": "8", "ref": [ "const" ], "rows": 641, "filtered": 100, "attached_condition": "(`notify_portal`.`a`.`permissionId` = `notify_portal`.`b`.`id`)" } } ] } } } ``` 这个时候看`using_temporary_table`和`using_filesort`提示都没有了。 **结论**：一开始查询时间极其不稳定，有几百毫秒的，有一秒多的通过新增联合索引后，优化后查询时间变得非常稳定，10几毫秒,去除了临时表，去除了非索引列排序。 * 数据库死锁如何排查处理这个问题线上也遇到过，从死锁日志看，主要是批量插入一张表和更新同一个表数据的两个sql相互引起的。这个问题凭自己目前的知识水平很难讲清楚,这里就不详细分析了，哈哈。 Mysql是Java面试里最容易面试到的专题，把女朋友面试到的题记录下来，希望以后可以派上用场。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/65333012392b45c8adb3983e6c281d41~tplv-k3u1fbpfcp-watermark.image#?w=562\&h=1000\&s=87396\&e=png\&b=ffffff)