复盘女朋友面试4个月的Mysql面试题(1万字)
背景
自从3月底女朋友被公司奉告裁人到现在现已挨近4个月了,找作业的这四个月我非常伤心,我女朋友愈加伤心,特别是前两个月每天过的的非常摧残,女朋友在上家公司呆了4年多了,我和她都没想到会产生这事,居然奉告3天就要走人,当时毫无心理准备,一初步的时分心里天天想ma xx 千万遍。
女生在程序员职业面试机遇相对较少,但是女朋友一直都没有扔掉,中心不断调整心态,还好皇天不负有心人,通过最近两个月的精心准备,女朋友找好作业了,而且是头部上市医药公司,和之前大健康职业也匹配,薪资也上浮了一点。所以我们信任只需准备好了,机遇一定会有的。
最近4个月,女朋友面试了杭州,上海,广州深圳等大大小小几十个公司,虽然也拿了大概10多个offer,但是整个进程仍是非常心累的,底子每天都是在刷题中度过,由于女朋友之前公司呆了4年,期间没有任何去准面试相关的标题,特别是最近几年8股文越来越多,所以前面两个月主要是在温习和拾掇知识的阶段,到6月才陆续进入较好的面试情况了,通过这次也奉告我们,就算不方案看机遇,也要时间坚持和商场接触,了解岗位相关的知识。
接下来的文章,我将拾掇女朋友最近面试过的相关面试题,便利今后时间能找着记载。今天就从Mysql专题初步,Mysql是Java后端工程师面试必备的一个知识,面试题也相对比较多。
下面思想导图就是从女朋友面试的标题中拾掇出来的Mysql相关面试题,确实是非常高频的标题,有需求的朋友记得保藏起来哦。
进入正题-Mysql面试题复盘
架构相关面试题
-
Mysql架构简述
这个标题一般是面试官初步进入Mysql面试的开场题,通过这个标题引进Mysql的论题,一同可以查询面试者对Mysql全体结构的认知情况。
在Mysql官网有架构图,我们答复问题前可以自己在脑袋里回想一下这个图。 dev.mysql.com/doc/refman/…
从官方图可以看出Mysql是一种C/S架构,mysql本质上是一个数据存取系统,联接磁盘和客户端,完结数据存储和查询。总体上选用了分层的架构办法,从上往下分别是联接层,服务层,还有最中心的一层就是可插拔式的存储引擎层。
这儿假定对各个层比较了解的话,可以和面试官聊一下各个层的作用。
联接层:担任客户端联接恳求处理,认证,要运用mysql,一般通过账号密码先和mysql服务器通过tcp三次握手树立通讯联接,然后就可以交流恳求数据了。
服务层:服务层供应了存储数据的接口,sql解析器,查询优化器,缓存等组件
存储引擎层:担任存取数据,完结与磁盘等存储设备交互。常用的存储引擎有Innodb,MyISAM,有些面试官或许还会问不同存储引擎的差异。
- Mysql高可用架构
这个问题仍是比较考验知识的深度和广度的。什么是高可用?我们先看看高可用的定义:
高可用性简称HA
,简略的说就是一个系统尽或许供应更长的正常在线拜访时长的才干。通过一些容错手法来完结高可用。通常用几个9来表明一个软件系统高可用的程度。
假定能到达5个9,一年只能有5分钟15s不可用,我们的系统现已非常强悍了。
为什么Mysql需求做高可用呢?
Mysql作为大部分企业事务数据的耐久化存储介质,高可用才干是必须具有的。
在大型的企业应用中,或许有成百上千的数据库,每个数据库或许都承担了非常重要的事务数据存储和查询功用,比方电商公司里的用户数据,产品数据,买卖数据,优惠券数据,会员数据等等。
高可用需求考虑哪些因素?
一、需求集群安置办法,防止单点缺点,假定是单机安置,那么单点缺点就无法完结高可用了。
二、需求主从同步数据机制,保证主从节点的数据具有一致性
三、主动选主才干,需求具有主动缺点搬运failover的才干
第一,二两点官方就供应支撑,可以完结多个节点和主节点数据一致。 dev.mysql.com/doc/refman/…
Mysql支撑以binlog 偏移量和全局事务gtid两种办法的数据仿制。
gtid代表全局事务id,每个数据库改动都会生成一个gtid,也是存在binlog文件中。
gtid数据格局:
GTID = source_id:transaction_id
例如:3E11FA47-71CA-11E1-9E33-C80AA9429562:23
存储在mysql数据库的gtid_executed表中。
比方数据从数据库A同步到数据库B
以全局事务id同步办法为例,完结数据同步的进程如下:
第一步:在A,B数据库配备文件中,打开翻开全局事务id办法
gtid_mode=ON
enforce-gtid-consistency=ON
第二步:打开副本节点主动定位
mysql> CHANGE MASTER TO
> MASTER_HOST = *host*,
> MASTER_PORT = *port*,
> MASTER_USER = *user*,
> MASTER_PASSWORD = *password*,
> MASTER_AUTO_POSITION = 1;
第三步:打开slave节点
mysql> START SLAVE;
配备好了,数据同步就会正常进行了。后续主从同步通过binlog数据完结的,binlog格局有三种。
raw:记载的是被操作的数据行的主键id以及实行的server id
statment:当binlog_format=statement时,binlog里边记载的就是SQL语句的原文。
mix:折中statement和row。
仿制数据进程是通过多个线程处理的,分别是dump线程,io线程,sql线程,在官网有说明 dev.mysql.com/doc/refman/…
仿制数据的进程如下:
上面现已知道了主从仿制的才干,具有了数据备份和防止单点缺点的功用,但是假定主节点出现缺点,还需求从节点推举新的主节点的才干。
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等
索引相关面试题
- 索引有哪些类型?
这个问题我以为按如下办法答复会比较全面
- 依据底层数据结构可分为:B+ tree 索引、Hash 索引、Full-Text 索引;
- 依据物理存储办法可分为:聚簇索引、二级索引(辅佐索引、非聚簇索引);
- 依据索引字段特性可分为:主键索引、普通索引、仅有索引、前缀索引;
- 依据索引组成的字段个数可分为:单列索引、联合索引(复合索引、组合索引)。
- 索引完结原理
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条记载。
所以三层的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条记载
分为4个区间(负无量,1),(1,5),(5,9),(9,正无量),也就是区间数=记载数+1
。
那么刺进一条记载主键为4的数据时分,这个时分会锁住区间(1,5),不包括详细的记载,这就是空地锁,这样可以处理幻读的问题。
临键锁是记载锁+空地锁的组合,它也叫next-key锁,它是左开右闭的区间。比方刺进数据主键为4的记载,那么会锁住(1,5]区间的索引。
我们可以通过系统参数查看锁的情况,验证加锁情况。
# 设置数据库系统参数,记载锁的情况信息
set global innodb_status_output = on;
set global innodb_status_output_locks = on;
# 可以查看加了有哪些锁?
show engin innodb status;
事务相关面试题
- 事务阻隔等级
阻隔等级是数据库的一个规范,分别有读未提交,读已提交,可重复读,序列化读四种等级,不同的阻隔等级会产生不同的问题,比方脏读,不可重复读,幻读问题。
- 什么是脏读,幻读,Mysql是怎样处理的?
脏读是指一个事务读到其他事务未提交的数据。mysql通过mvcc机制处理了脏读的问题。mvcc是多版别并发机制,通过在事务初步时创建最新的一个readview,运用read view数据结构,read view里边存储了当时数据库现已提交的事务,这样可以保证读取已提交的事务。
幻读是指一个事务前后两次读取到其他事务新增的数据。mysql是通过空地锁和临键锁来处理的。
Mysql优化相关
- 慢查询怎么排查与处理
这个标题和实践作业非常挨近,由于项目开发中防止不了写sql,在真实的线上环境也或许会遇到各种慢sql情况,你自己没发现,dba也会找上门来。
假定有真实处理过慢sql的案例,主张运用自己处理案例的比方来给面试官解说。
mysql慢日志检测默许是封闭的,可以通过指令show variables like '%slow_query_log%';
查看是否打开慢日志。
而且默许实行了10s以上的sql才会以为是慢sql。
我们可以通过修改slow_query_log
、long_query_time
参数来打开记载我们的慢sql。
[mysqlld]
long_query_time=2
#5.5及以上版别配备如下选项
slow-query-log=On
假定发现了慢sql我们怎样排查处理呢?
下面分析一个作业中实践遇到的慢sql比方。在项目中有权限表和人物权限表:
# 权限表
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如下
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格局的:
{
"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
添加一个组合索引。
ALTER TABLE `t_permission`
ADD INDEX `id_parent_id_name_code_code`(`parentId`, `name`, `code`,`sort`);
再看下实行方案
{
"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面试里最简单面试到的专题,把女朋友面试到的题记载下来,期望今后可以派上用场。