MySQL常见面试题总结

MySQL常见面试题总结

MySQL基础

什么是 SQL？

SQL,Structed Query Language 结构化查询语言

MySQL 有什么优点？

事务支持优秀,支持分库分表,读写分离,高可用

MySQL字段类型

• 数值类型: 整型(tinyint,smallint,mediumint,int,bigint) 浮点型(float double) 定点型 decimal
• 字符串类型: char,varchar,tinytext,text,mediumtext,longtext,tinyblog,blob,mediumblob,longblog
• 日期类型: year,time,date,datetime,timestamp

整数类型的unsigned类型有什么用

因为只有正数,范围翻了一倍

例如,普通的tinyint 范围是-128 ~127,unsigned tinyint 是0 ~255
直接翻一倍

CHAR 和 VARCHAR 的区别是什么？

• char是定长,varchar是变长
• char存的时候,不够长的化,会补空格在右边,检索的时候去除空格,varchar存的时候,会有1到2个字节存字符串长度,检索的时候,不需要处理
• char更适合长度短,长度差不多的,varchar更适合长度不一样的
• char(M) varchar(M) M表示都是最大的长度

VARCHAR(100)和 VARCHAR(10)的区别是什么？

• 第一,一个能存100,一个能存10,超过10,要改表结构
• 第二,存一样的字符,二者占用的空间是一样的
• 第三,varchar(100) 会占更多空间,因为是按照100分配空间的,排序的时候,还更会耗费内存

DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

• decimal是定点数,float/double是浮点数
• decimal能存精确的小数,float/double只能存近似的小数
• 所以,decimal,一般用来存货币相关的数据,避免由于浮点数带来的精度损失
• 在java中decimal对应的是BigDecimal

NULL 和 ‘’ 的区别是什么？

• ‘’ 占用空间为0,null占用空间
• null会影响聚合函数的结果,sum,avg,min,max会忽略null,count(列名)也会忽略null
• 查询null的时候,必须用is null 或is not null来查, 对于’'直接用 = != <=这些

Boolean 类型如何表示？

没有专门的表示,用tinyint(1)来表示,1就是true,0就是false

MySQL存储引擎

MySQL存储引擎是基于表的,而不是数据库,不同的表可以有不同的存储引擎

MyISAM 和 InnoDB 有什么区别？

5.5之前,默认引擎是MyISAM
MyISAM的优点就是性能不错,各种特性也还行(全文索引,压缩,空间函数)
但是MyISAM不支持事务,不支持行级锁,而且崩溃之后不能恢复

• MyISAM支持表级锁,InnoDB支持行级锁
• MyISAM不支持事务,InnoDB支持事务,事务有四个隔离级别,默认是repeatable-read 可重复读
• MyISAM不支持外键,InnoDB支持外键
• MyISAM不支持MVCC,InnoDB支持,MVCC可以看作是锁的升级
• 索引实现不一样,MyISAM的索引和数据是分开的,InnoDB索引就是数据
• InnoDB性能更强大

MySQL日志

常见的日志有哪些

• 错误日志(error log) 对MySQL的启动,运行,关闭过程进行了记录

• 二进制日志(binary log) 主要记录的是改变数据库的SQL,包括DDL + DML

• 一般查询日志(general query log) : 已经连接的客户端发给服务端的SQL记录,因为量比较大,默认关闭

• 慢查询日志(slow query log): 记录慢查询的日志

• 事务日志(redo log 和 undo log) redo log 重做日志,undo log 回滚日志

• 中继日志(relay log): 主从复制过程中产生的日志,类似于中继器,不过relay log针对的是主从复制中的从库

• DDL日志(metadata log) : 记录DDL语句执行的元数据操作

比较重要的是binlog,redo log,undo log

慢查询的作用

慢查询的作用就是为了优化
什么是慢查询,sql执行时间超过long_query_time的就是慢查询

对于long_query_time 默认是10s,

#查看慢查询标准时间
SHOW VARIABLES LIKE '%long_query_time%'

慢查询默认还是关闭的,我么通过下面的命令开启

SET GLOBAL slow_query_log = ON

实际项目中,不直接看慢查询,而是用官方的工具mysqldumpslow

binlog主要记录什么?

主要记录DDL + DML,都是更改数据库的操作

binlog有一个重要场景,就是主从复制

binlog通过追加的方式写入,大小没有限制,所以可以直接通过max_binlog_size参数设置每一个binlog文件的最大容量,binlog大小达到这个最大值,之后会生成新的binlog文件来保存日志

关于主从复制的具体步骤和原理,有如下的文章

redo log如何保证事务的持久性?

我们都知道,再innodb中是以页为单位来存储数据的,我们插入的数据最终存在页中,为了减少磁盘IO,会有一个Buffer Pool 缓存池,再内存中,当我们的页不在Buffer Pool的时候,MySQL会将磁盘上的页 存到Buffer Pool中,我们直接操作就是Buffer Pool中的数据

一个事务提交之后,我们Buffer Pool中对应的页修改可能还没有持久化到磁盘中去,此时MySQL宕机,就会丢失修改,所以我们需要redo log的帮助.

总的来说,就是事务提交之前,先把redo log写好,这样就算修改没有持久化到磁盘中去,MySQL宕机,我们也可以通过redo log来重新修改.

redo log到底记录的是什么呢?肯定是记录的修改,包括某个页面某个地方修改了几个字节,以及修改的内容是什么,redo log中每一条记录包含了表空间好,数据页号,偏移量,具体修改的数据,甚至可能会记录修改的数据的长度

在事务提交的时候,我们会将redo log按照刷盘策略刷到磁盘上去,这样MySQL宕机了,重启之后,也可以恢复没有写入磁盘的数据,从而保证事务的持久性,redo log让MySQL具备了崩溃恢复的能力

刷盘策略indodb_flush_log_at_trx_commit 的默认值是1,设置为1的时候,才不会丢失任何数据,为了保证事务的持久性,我们必须设置为1

redo log采用循环写的方式写入,大小固定,当写到结尾的时候,会回到开头循环写日志,会出现写的日志被覆盖的情况

基于redo log & undo log的崩溃恢复

基于Redo log & Undo log的MySQL的崩溃恢复

binlog和redolog有什么区别

• binlog主要用于数据还原,属于是数据库级别的数据恢复,它的主要的应用场景是主从复制,redo log主要是为了保证事务的持久性,属于是事务界别的数据恢复
• redo log是InnoDB独有的,binlog是MySQL的Server层实现的
• redo log是物理日志,主要记录的是页的修改,binlog 是逻辑日志,主要记录的是,DDL,DML
• binlog是追加写方式,redo log是循环写

undo log如何保证事务的原子性

undo log是怎么记录修改之前的数据呢?其实很简单,就是说反话,比如你delete了一条数据,那undo log记录的就是,insert这条数据.
这也是为什么它是逻辑日志.

MySQL事务

什么是事务

事务就是逻辑上的一组操作,要么都执行,要么都不执行

数据库事务

要了解数据库事务我们必须知道ACID原则

• 原子性(Atomicity): 事务是最小的执行单位,不能分割.说人话就是,要么都执行,一旦出错,就全都都不执行
• 一致性(Consistency): 执行事务的前后,数据保持一致. 需要注意,这里的保持一致,一般是业务上的一致. 举个例子,A给B转200块,A的账户得少200,B的账户得多200.
• 隔离性(Isolation): 事务和事务之间是独立的
• 持久性(Duration): 事务被提交之后,对数据的更改是永久的.也就是说,你提交之后,本地存储的实际数据就更改了

需要知道的是,ACID,A I D是手段,C是目的

并发事务带来的问题

脏读

假设有两个事务,1,2,他们都要操作数据A

事务1,对A进行了修改

对于事务2来说,事务1对A的修改是可见的,所以事务2读取了修改之后的A

此时,事务1因为其他原因,事务回滚了

那么此时事务2读取到的就是脏数据,也就是脏读

一句话概括,脏读就是读取到其他事务修改之后没有提交的数据

丢失修改
丢失修改,只要你学过并发,就一下就能搞懂了

同样是事务1,和事务2,对数据A = 20进行修改

---

先来正常的流程,事务1,对A–,事务2,对A–
那么正常的结果应该是A = 18

---

那么为什么会出现问题呢?我们要抓住一个瞬间,事务1和事务2同时读取到A为20

事务1,读取到A = 20,对A进行–操作,此时A = 19

事务2,读取到A = 20,对A进行–操作,此时A = 19

所以结果变成了19

这个操作就是丢失修改的意思

问题就出现在,事务2不知道此时事务1已经修改了数据A,还以为数据A是20

这也是为什么,一般解决这类问题,要用锁了.

不可重复读

不可重复读的意思,就是它的字面意思

同样是事务1,和事务2,读取数据A = 20
事务1,先读取数据A,此时读取到的A = 20

紧随其后,事务2,对A进行了修改(- 1操作),此时A 实际 = 19

在这之后,事务1,再次读取数据A,此时A = 19

简单来说,就是一个事务,多次读同样的数据不一致,所以就是不可以重复读,重复读就出问题

幻读
幻读和不可重复读类似,但是又有区别,我们要区分好

同样是事务1,和事务2,读取表A,假设表A只有5行数据

事务1,select * from A ,读取到5行数据

事务2,往表A,插入了5条数据

事务1,再次select * from A,读取到10行数据

真是见了鬼了,多了数据,感觉像是幻觉一样,所以这就叫做幻读

需要注意的是,幻读,一定是多了数据,而如果读取的时候,少了数据行,那么就是不可重复读

不可重复读和幻读的区别

不可重复读的重点在于读到的数据少了或者被修改了

幻读的重点是,查到的记录多了

我们只要记住,幻读就是出了幻觉多了数据,不可重复读就是它的反面

并发控制的方式有哪些?

一共就两种,锁和MVCC,MVCC可以看作是锁的升级版本
锁是悲观锁,MVCC是乐观锁控制的

---

锁
MySQL主要是通过读写锁来实现并发控制的

• 共享锁(S锁): 又叫读锁,我猜测S锁的名字的由来是Share的意思,共享. 事务在读取之后获取锁,允许多个事务同时读
• 排他锁(X锁): 又叫写锁,或者独占锁,不允许多个事务同时获取

对于读锁来说,可以做到读读并行,并且释放时机是读取完之后

对于写锁来说,就不行,不能读写并行,更不能写写并行,释放时机是事务提交之后释放

根据锁的粒度来分,还分表级锁和行级锁,InnoDB支持表级锁,和行级锁

其他存储引擎没有行级锁,可能有例外,但是基本没有,所以InnoDB性能高之处就在于此

无论是表级锁还是行级锁,都要读锁 + 写锁

InnoDB存储引擎对MVCC的实现

InnoDB存储引擎对MVCC的实现

SQL标准定于了哪些事务隔离级别?

一共四个
所有事务隔离级别都解决了脏写问题

• Read-Uncommited: 读未提交,什么都没解决
• Read-Commited: 读已提交 解决了脏读
• Repeatable-Read: 可重复度, 解决了脏读 + 不可重复读
• Serializable: 可串行化,解决了脏读 + 不可重复读 + 幻读

这四个都很好记,看名字都能知道到底解决了什么,我们只要记住这四个顺序就行

对于读未提交,可以读到读没有提交的数据,那就是脏读了,既然脏读都没解决,后边肯定都没解决

对于读已提交,对于哪些没有提交的数据读不到,说明,脏读已经解决,但是只解决了脏读

对于可重复读,它的名字就叫做可重复读,那肯定是解决了不可重复读的问题.

对于可串行化,它的意思是串行的去做并发,也就是说,读加锁,写加锁,那意思不就是连幻读都解决了?所以,我们只用记名字就好了!

MySQL的隔离级别是基于锁实现的吗?

MySQL的隔离级别是基于锁和MVCC机制共同实现的
可串行化,是基于锁实现的
读已提交和可重复读是基于MVCC实现的,但是可串行化之外的其他隔离级别可能也需要用到锁机制,就比如Repeatable-Read 在当前读的情况下,需要加锁读来保证不会出现幻读

MySQL 的默认隔离级别是什么?

可重复读Repeatable-Read

MySQL锁

表级锁和行级锁的区别

• 表级锁是锁住整个表,并且和存储引擎是无关的,MyISAM和InnoDB都可以用,表级锁的粒度大,并且针对的是非索引的字段
• 行级锁是锁住某一行记录,InnoDB独有,并发性好,粒度小,并且需要注意的是,行级锁是针对索引字段加的锁

行级锁的使用注意事项

因为行级锁是针对索引字段的,所以说如果我们写的SQL语句没有命中索引的化,可能会退化成表级锁,损失性能,所以要注意这个问题

InnoDB有哪几类行级锁?

• 记录锁(Record Lock): 锁定是单个存在的行记录
• 间隙锁(Gap Lock): 锁定是一个范围,不包括记录本身
• 临键锁(Next-Key Lock): Record Lock + Gap Lock,锁定一个范围,并且包含记录本身,主要目的是为了解决幻读问题

在Innodb中,默认的隔离级别是RR(可重复读),行锁默认是临键锁
但是如果操作的索引是唯一索引或主键,Innodb会Next-Key Lock进行优化,降级成Record Lock

这里我想不通,这里留一个问题,为什么会降级?

共享锁和排他锁

• 共享锁 S锁,或者叫读锁
• 排他锁 X锁,或者叫写锁

S锁和X锁的兼容情况,只要记住一个就行,也就是只能读读并发
涉及到X锁的,都不能一起

当然这是针对于当前读的情况,如果加上MVCC的化,读-写并发是可以的

意向锁有什么作用

意向锁是表级锁,它是为了快速判断是否可以对某个表使用表锁

分为两种

• 意向共享锁(IS锁,Intention Shared Lock): 事务有意向对表中的某些记录加共享锁,加共享锁之前必须先取得该表的IS锁
• 意向排他锁(IX锁,Intention Exclusive Lock): 事务有意向对表中的某些记录加排他锁,加排他锁之前必须先取得该表的IX锁

意向锁是数据引擎自己维护的,用户无法手动操作,在为数据行加S锁或者X锁之前,要鲜活的对应的数据表的意向锁

兼容情况

锁之间是互相兼容的

意向锁和表级别的共享锁和排他锁互斥,不会和行级的共享锁和排他锁互斥

快照读和当前读

快照读,我的理解是历史版本读,它一般是单纯的Select语句
但是不包括,一下Select语句

select ... for update #select的时候加排他锁
select ... for share #select的时候加共享锁
select ... lock in share mode #select的时候加共享锁

当前读就是给行记录加S或者X锁

select... for update # 读的时候,加X锁
select ... lock in share mode #读的时候,加S锁
select ... for share #读的时候,加S锁
insert ...
update ...
delete ... #这几个都是加X锁

自增锁

自增锁是表级锁,只要涉及到Auto_Increment就会有这个锁
如果一个事务正在插入的数据列由自增列,会先获取自增锁,拿不到的化,有可能会被阻塞住,这里的阻塞行为,只是自增锁行为的其中一种

Next-Key lock加锁范围

再MySQL45讲种,丁奇老师的结论
两个原则,两个优化

• 原则1: 加锁的基本单位是next-lock key,它的区间是前开后闭的
• 原则2: 查找过程中访问到的对象才会加锁
• 优化1: 索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁
• 优化2: 索引上的等值查询,向右遍历的时候最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁

结论一:

• 加锁的时候,都会先给表加意向锁
• 加锁如果是多个范围,是分开加了多个锁,每个范围都有锁
• 主键等值查询,数据存在,会对该主键索引的值加行锁X,REC_NOT_GAP
• 主键等值查询,数据不存在,会对查询条件主键值所在的间隙添加间隙锁
• 主键等值查询,范围查询的时候比较复杂
  - 8.0.17版本是前开后闭的,而8.0.17之后,进行了优化,不会锁住后边不等的区间