MySQL面试题

1. 请说下你对 MySQL 架构的了解?

MySQL架构示意图

img

MySQL可以分为Server层和存储引擎层

Server层(涵盖大部分MySQL核心功能,以及所有内置函数,以及存储过程、触发器、视图等)

  • 连接器。负责连接客户端并校验权限。show processlist 查看连接状态。
  • 分析器。解析SQL语句的词法和语法。
  • 优化器。通过SQL选择一种最优的执行方式,生成执行计划,选择索引。
  • 执行器。操作引擎,返回执行结果。
  • 查询缓存。将查询的结果缓存,如果命中查询条件,直接返回结果。MySQL8.0之后弃用,因为对于一些经常更新的表,这个表的所有查询缓存会全部清除,效率低,一般用于配置表等不频繁更新的表。

存储引擎层

负责数据的存储和读取,提供读写接口。常见的存储引擎如:InnoDB、MyISAM、Memory等存储引擎。从MySQL5.5.5开始,InnoDB成为默认存储引擎。

参考:

https://blog.csdn.net/zhizhengguan/article/details/120816220

https://blog.csdn.net/Dyski/article/details/118946143

2. 一条 SQL 语句在数据库框架中的执行流程?

查询语句

  • 连接时,连接器校验用户密码,查询权限表
  • MySQL8.0之前,先查询缓存,在返回结果之前校验是否有权限
  • 如果缓存查询不到,执行分析器分析词法、语法
  • 执行优化器,根据优化算法确定执行方案。如:where name='张三' and age=18,根据优化算法确定哪个条件先执行。
  • 进行权限校验,如果没有权限就返回错误信息,有就调用存储引擎,返回执行结果。

update tb_student A set A.age='19' where name='张三';

MySQL自带的日志模块是 binlog(归档日志),所有存储引擎都可以使用,InnoDB引擎自带的日志模块是redo log(重做日志)

更新语句

  • 查询name为张三的数据,如果有缓存则查询缓存
  • 将数据更新,并调用存储引擎接口,写入数据
  • InnoDB将这一行数据保存到内存,并记录redo log,此时redo log进入prepare状态,然后告诉执行器执行完成
  • 执行器收到通知后,记录binlog,然后调用引擎接口,将redo log状态改成提交状态,更新完成

注意:

  • 使用binlog和redo log是为了crash-safe(数据库异常重启,之前提交的记录不会丢失)
  • redo log有两种状态是为了保证数据的一致性,如果直接提交redo log再写binlog,或者先写binlog再提交redo log,如果这个过程数据库异常,就会导致两个日志记录不一致
  • MySQL恢复数据的处理机制:1. 如果redo log处于提交状态,则立即提交。2. 如果binlog记录是完整的,则立即提交,否则回滚事务。

参考:

http://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485097&idx=1&sn=84c89da477b1338bdf3e9fcd65514ac1&chksm=cea24962f9d5c074d8d3ff1ab04ee8f0d6486e3d015cfd783503685986485c11738ccb542ba7#rd

3. 数据库的三范式是什么?

  • 1NF。字段不能再拆分。
  • 2NF。非主键字段依赖主键字段,一个表只能描述一件事,如:不能同时描述课程信息和学生信息。
  • 3NF。非主键字段直接不能互相依赖,每一列都和主键直接相关。

参考:

http://t.csdn.cn/wK39P

https://blog.csdn.net/CharlesYooSky/article/details/89204229

4. char和varchar的区别

char:长度固定,如果字符串长度小于指定长度,MySQL会使用空格字符填补。每次修改的数据长度相同,效率更高。存储初始预计字符串+记录字符串长度的字节。

varchar:长度可变,每次修改的数据长度不同,效率较低。存储实际字符串+记录字符串长度的字节。

5. varchar(10) 和 varchar(20) 的区别?

  • varchar(10)和varchar(20)占磁盘的空间是一样的
  • varchar(20)消耗的内存更多,因为MySQL会分配固定大小的内存块来保存内部值,一般推荐冗余10%的长度。

参考:

https://www.cnblogs.com/chinaxieshuai/p/12703714.html

6. 谈谈你对索引的理解?

索引的功能:加速查找,约束功能。

索引的原理是通过不断缩小数据的范围来筛选结果,同时把随机事件变成顺序事件。

索引两大类型:

  • hash索引:查询单条快,范围查询慢
  • btree索引:b+树,层树越多,数据量指数级增长(innodb默认支持btree)

适合建索引的字段:

  • 经常被查询的字段(where)
  • 在分组的字段(group by)
  • 存在依赖关系的子表和父表之间的关联查询(主键或外键字段)
  • 设置唯一完整性约束的字段

不适合建索引的字段:

  • 查询中很少被使用的字段
  • 有许多重复值的字段

普通索引index:加速查找

主键索引primary key:加速查找+约束(不为空、不能重复)

唯一索引unique:加速查找+约束(不能重复)

联合索引:primary key(id, name),unique(id,name),index(id,name)

最左前缀原则:创建联合索引时,查询最频繁的一列放在最左侧

联合索引本质:创建(a,b,c)联合索引时,相对于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引

参考:

https://blog.csdn.net/piaochong2006/article/details/117666451

7. 索引的底层使用的是什么数据结构?

底层使用树的结构

二叉树—>平衡二叉树—>B-树—>B+树

二叉树(Binary Search Tree),对于有序的数组会变成链表,因此可以使用平衡二叉树(AVL Tree),让左右子树的高度差不超过1,对于每次查找都会消耗一次I/O,可以使用B-Tree减少I/O次数

img

img

8. 谈谈你对 B+ 树的理解?

B+树非叶子节点用来索引,不保存数据(可以减少树的高度,减少I/O次数),叶子节点保存数据(InnoDB,MyISAM叶子节点保存数据的物理地址)

img

9. 为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢?

B树,每个节点存储键值、指针和数据

img

B+树每个非叶子节点不保存数据,可以存放更多的索引,降低树的高度,减少I/O,B+树比B树查询性能稳定,因为B树最好的情况是查询根节点,最坏是查询叶子节点,而B+树每一次都查询叶子节点,所有的叶子节点包含了全部元素信息,并且有序,因此增删节点效率更高

10. 谈谈你对聚簇索引的理解?

聚簇索引:数据和索引放在一块

非聚簇索引:数据和索引分开,索引结构的叶子节点仍是索引文件,包含指向对应数据块的指针

一个表只能有一个聚簇索引(因为真实数据的物理顺序只有一种),而非聚簇索引可以有多个

聚簇索引存储记录在物理上是连续的,非聚簇索引在逻辑上是连续的,物理上不连续

InnoDB中,在聚簇索引上创建的索引叫辅助索引,非聚簇索引都是辅助索引

InnoDB中,定义一个主键,MySQL将主键作为聚簇索引,没有定义就将第一个not null的唯一索引作为聚簇索引,没有就自动创建一个隐藏名称为 GEN_CLUST_INDEX 的聚簇索引

在这里插入图片描述

假如普通索引k为非唯一索引,要查询k=3的数据。步骤如下:

需要在k索引查找k=3得到id=30。
然后在左侧的ID索引树查找ID=30对应的记录R3。
然后K索引树继续向右查找,发现下一个是k=5不满足(非唯一索引后面有可能有相等的值,因此向右查找到第一个不等于3的地方),停止。
整个过程从K索引树到主键索引树的过程叫做“回表”

在这里插入图片描述

参考:

https://www.csdn.net/tags/Ntjakg4sNjgyODUtYmxvZwO0O0OO0O0O.html

https://blog.csdn.net/zhizhengguan/article/details/120834883

11. 谈谈你对哈希索引的理解?

HASH索引是基于哈希表实现的,插入数据时根据索引列计算出哈希值,保存到哈希表,同时保存一个指向数据行的指针(rowIndex),如果计算的哈希值相同(哈希碰撞),一个哈希值有多个行指针,这时行索引以链表的结构存储。

查询—>根据索引列的条件值计算哈希值—>根据哈希值找到索引行—>返回匹配的行

HASH索引只支持精准匹配,不支持范围查找

参考:

https://zhuanlan.zhihu.com/p/384845190

12. 谈谈你对覆盖索引的认识?

select查询的字段和条件刚好都是同一个索引,此时因为索引树保存有这个索引的值,因此不需要回表。如:select name from tb where name>'aaa'

覆盖索引可以减少树的搜索次数(避免回表),显著提高性能,是一个常用的优化手段,但不适应于任意类型的索引,只能使用B-Tree做覆盖查询,不支持select *,因为所有字段做索引会导致索引文件过大,查询性能下降。

覆盖索引的实现可以是将被查询的字段建立到联合索引中

参考:

https://blog.csdn.net/zhizhengguan/article/details/120854035

13. 索引的分类?

普通索引:最基本的索引,没有任何约束限制。
唯一索引:和普通索引类似,但是具有唯一性约束,可以有 null
主键索引:特殊的唯一索引,不允许有 null,一张表最多一个主键索引
联合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并
全文索引:对文本的内容进行分词、搜索
覆盖索引:查询列要被所建的索引覆盖,不必读取数据行

参考:

https://blog.csdn.net/meism5/article/details/104205961

14. 谈谈你对最左前缀原则的理解?

查询时索引字段使用联合索引最左边的字段

如:联合索引 (name, city)

select * from user where name=xx and city=xx ; //可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引    

由于最左前缀原则,在创建索引时,索引字段的顺序需要考虑索引值去重后的个数,个数多的放在前面。

参考:

https://blog.csdn.net/qq_43386944/article/details/118599013

15. 怎么知道创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

explain select name from tb where name='aa'

通过Extra查看索引是否被用到

在这里插入图片描述

在这里插入图片描述

16. 什么情况下索引会失效?即查询不走索引?

  • 对索引使用左/右模糊匹配(like %xx 或 like %xx%)
  • 对索引使用函数,因为索引保存的是索引字段的原始值,不是经过函数计算后的值
  • 对索引进行表达式计算,如:where id + 1 = 10,改成id = 10 - 1就会走索引
  • 对索引隐式类型转换,如:索引字段是字符串类型,但条件值是整型,这是会转换
  • 联合索引非最左匹配
  • where子句中的or,or前后有一列不是索引就会走全表

参考:

https://www.cnblogs.com/xiaolincoding/p/15839040.html

17. 查询性能的优化方法?

  • select 返回必要的列,代替*
  • limit返回必要的行
  • 缓存检查查询的数据
  • 使用索引来覆盖查询,避免回表
  • where 和 order by使用索引
  • 避免使用!=和null(可以设为0)和or,否则会全表扫描
  • 使用exists代替in,对于连续的值,使用between代替in
  • 避免使用参数,如:where num=@num

参考:

https://blog.csdn.net/zhizhengguan/article/details/120857261

18. InnoDB 和 MyISAM 的比较?

  • InnoDB支持事务,MyISAM不支持,MyISAM有全文索引,查询效率高
  • InnoDB有行级锁(默认)和表级锁,MyISAM只有表级锁
  • InnoDB支持使用一个表的外键去检索另一个表的主键,MyISAM不支持
  • InnoDB支持MVCC
  • InnoDB的主键索引是聚簇索引,叶子节点存放整行数据,MyISAM是非聚簇索引,叶子节点存放数据的磁盘地址

参考:

https://blog.csdn.net/Dyski/article/details/118946143

19. 谈谈你对水平切分和垂直切分的理解?

水平切分:将一个表的数据分成多个表的数据

优点:

  • 解决单表大数据量性能瓶颈问题
  • 事务处理简单

缺点:

  • 数据维护难
  • 分片事务难解决

垂直切分:将一个表的字段拆分成多个表

优点:

  • 表结构简单,容易维护
  • 列数据变小,减少I/O次数

缺点:

  • 事务更复杂
  • 部分表的关联不能在数据库完成,需要在程序完成
  • 单表数据量大时存在性能瓶颈

水平拆分最好使用分库,因为分表仅解决单表大数据的问题,但还是在同一台机器,无法提升MySQL并发能力。

参考:

https://www.cnblogs.com/selinamee/p/6873969.html

20. 主从复制中涉及到哪三个线程?

主从复制:将主库备份到从库,如果主库出现问题,可以快速切换到从库。

主:binlog线程

从:io线程

从:sql执行线程

主库更新事件(update、insert、delete)被写到binlog(二进制日志文件)

从库开启I/O线程作为MySQL客户端,请求主库的binlog

主库启动应该Dump线程,检查binlog中的事件,和对方请求的位置对比,如果没有请求位置参数就发送第一个日志的第一个事件

从库将收到的数据放入relay log(中继日志),并刚刚请求的记录主库binlog的位置

从服务器启动SQL线程,读取relay log并执行

参考:

https://cuowu.com/214899.html

21. 主从同步的延迟原因及解决办法?

主库更新事件记录到binlog产生的延迟

解决方法:控制主库事务大小,分割大事务

binlog数据传输时间产生的延迟

解决方法:使用MIXED日志格式或设置 set binlog_row_image=minimal

relay log的执行(默认只有一个SQL线程)产生的延迟

解决方法:使用多线程

常用的解决方法:对于必须获取最新数据的读请求,可以直接读主库

延迟读取:如果主从同步延迟为0.5s,那么设置在1s后读取数据,如:支付成功后跳转到支持成功的界面,点击返回再查看账户金额。

参考:

https://www.cnblogs.com/yudidi/p/12602240.html

https://blog.csdn.net/leeta521/article/details/119416420

22. 谈谈你对数据库读写分离和分库分表的理解?

读写分离:主要解决读数据的问题,将数据库的读写操作分散到不同的数据库节点上,这样可以小幅度提高写性能,大幅度提高读性能。

一般使用一主多从,主负责写,从负责读,这样也符合写少读多(1:10)

蚂蚁一面:谈谈你对读写分离以及分库分表的理解

读写分离的问题:

主从同步延迟

分库分表:主要解决数据量大的问题,包括水平切分和垂直切分

单表数据量达到千万级别考虑分表。

数据库数据占用空间大考虑分库。

应用并发量大考虑分库。

分库分表的问题:

join操作,不同库的表无法使用join

事务问题,不同库的表事务处理复杂

分布式id,自增主键不能确保唯一

参考:

https://blog.csdn.net/leeta521/article/details/119416420

23. 请你描述下事务的特性?

ACID

原子性(Atomicity),事务中的操作要么全部成功,要么全部失败。

一致性(Consistency),事务开启前后的完整性约束没有被破坏,数据的变化是一致的,如:张三向李四转100元,张三必须减少100元,李四必须增加100元。

隔离性(Isolation),数据库的事务之间互不干扰。

持久性(Durability),事务一旦被提交,数据的变化是永久的,不能回滚。

参考:

https://zhuanlan.zhihu.com/p/98465611

24. 谈谈你对事务隔离级别的理解?

  • 未提交读(READ UNCOMMITED,产生脏读)
  • 提交读(READ COMMITED,产生不可重复读)
  • 可重复读(REPEATABLE READ,产生幻读)
  • 串行化(SERIALIZABLE)

参考:

https://blog.csdn.net/weixin_41949328/article/details/104422829

25. 解释下什么叫脏读、不可重复读和幻读?

脏读:一个事务可以读到另一个事务未提交的数据,如果事务回滚,另一个事务读到的就是不存在的数据(对应事务是读未提交,使用读提交解决,通过行锁解决)

不可重复读:事务A多次读取同一数据,但事务B多次修改这些数据,导致事务A每次读取的结果不同(使用可重复读解决)

可重复读:事务A每次读到的结果都相同,即使事务B多次修改(对应可重复读事务)

幻读:查询时查不到数据,插入时显示该数据已存在(使用串行读解决,读数据时加表级共享锁,直到事务结束释放,更新时加表级排他锁,直到事务结束释放)

img

参考:

https://zhuanlan.zhihu.com/p/377477888

https://blog.csdn.net/qq_44969643/article/details/115875681

https://www.cnblogs.com/qdhxhz/p/15560986.html

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

可重复读

27. 谈谈你对MVCC 的了解?

Multi-Version Concurrency Control(多版本并发控制),数据存在多个版本时,需要查看是哪个版本的。

读提交和可重复读都是基于MVCC实现的,相对加锁可以更好处理读写冲突,提高数据库并发性能。

事务版本号。事务每次开启时都会获取一个自增id

隐式字段。InnoDB中,每一行记录有两个隐藏列trx_id、roll_pointer,如果表没有主键和非空唯一键,还会有隐藏主键row_id

undo log。回滚日志,记录数据被修改之前的信息,用来还原数据

版本链。多个事务并行修改一行数据时会产生多个版本,通过roll_pointer(回滚指针)连成一个链表

img

快照读:读取的数据有旧的版本

当前读:读取的记录时最新版本

Read View:事务执行SQL时产生读视图,用来判断当前事务可见哪个版本的数据

查询一条记录的MVCC执行过程:

  • 获取事务版本号
  • 获取Read View
  • 查询数据,和Read View的事务版本号比较
  • 如果不符合Read View可见性规则,从undo log获取
  • 返回符合规则的数据

参考:

https://zhuanlan.zhihu.com/p/421769708

28. 说一下 MySQL 的行锁和表锁?

InnoDB默认使用行锁,只有通过索引条件查数据时使用行锁,其他使用表锁

行锁:

开销大,加锁慢,会出现死锁

锁的粒度小,不容易出现锁冲突,并发处理强

对于update、delete、insert会自动加排他锁,对于普通select不会加锁,可以使用for update加锁

间隙锁(Next-Key锁):

InnoDB会给符合条件的数据的索引项加锁,对于条件范围内不存在的记录叫做间隙(GAP),也会加锁

排他锁:

写锁、独占锁,写操作没完成之前会阻断其他写锁和读锁

共享锁:

读锁,如果事务对读锁进行修改,可能会导致死锁

优化:

检索时尽可能通过索引避免行锁升级为表锁

尽可能避免长时间锁定大量资源,来减少锁的竞争

尽可能降低事务级别,级别越高,并发处理能力越低

表锁:

开销小,加锁快,无死锁

锁粒度大,锁冲突概率高,并发处理能力低

select时给所有涉及的表加读锁,update、delete、insert给所有涉及表加写锁

共享读锁

对MyISAM表加读锁,不会阻塞其他进程对同一表的读操作,会阻塞写操作

独占写锁

阻塞其他进程对同一张表的读、写操作

全表更新时使用表锁,因为行锁导致事务效率低,造成其他事务长时间锁等待

多表查询使用表锁,因为行锁可能导致死锁,进而导致大量回滚

参考:

https://blog.csdn.net/kuyuyingzi/article/details/87710259

29. InnoDB 存储引擎的锁的算法有哪些?

三种行级锁算法

Record Lock:记录锁,锁住一行

Gap Lock:间隙锁,锁定一个范围,不包括记录本身

Next-Key Lock:临键锁,record+gap锁定一个范围,包括本身

参考:

https://www.jianshu.com/p/b5f7a1352cd3

30. MySQL 问题排查都有哪些手段?

  • 使用 show processlist 命令查看当前所有连接信息。
  • 使用 explain 命令查询 SQL 语句执行计划。
  • 开启慢查询日志,查看慢查询的 SQL。

参考:

https://www.yukx.com/jing/article/details/1933.html

31. MySQL 数据库 CPU 飙升到 500% 的话他怎么处理?

top查看是不是mysqld导致的

show processlist查看session情况,看看是不是有高消耗的sql

kill掉这些线程,重新跑sql

除了高消耗sql,可能是因为大量session连接导致的,可以限制连接数

通过慢查询找出sql,再通过explain分析是否走索引

参考:

https://www.bilibili.com/read/cv16074200