小滴课堂MySQL相关面试题总结

个人博客文章地址：小滴课堂MySQL相关面试题总结

1. MySQL事务的ACID是什么？

考察点：事务的4个特征

• 原子性Atomicity：
  • 一个事务必须是不可分割的最小工作单元，整个事务操作要么全部成功，要么全部失败，一般就是通过commit和rollback来控制；
• 一致性Consistency：
  • 事务必须是使数据库从一个一致性状态变到另一个一致性状态。通俗的来说就是，事务的结果必须和预期相符（参考文章：什么是事务的一致性？）
• 隔离性Isolation：
  • 一个事务相对于另一个事务是隔离的，一个事务所做的修改是在最终提交以前，对其他事务是不可见的，一个事务对一个数据进行操作时，其他事务不允许对同一个数据进行操作；
• 持久性Durability：
  • 事务一旦提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失；

2. MySQL中的脏读、幻读、不可重复读度你知道多少？

考察点：事务的隔离级别以及导致的相关问题

• 脏读：如果某个事务对数据的修改尚未提交时，其他某个事务也能读取到该数据，导致一个事务可以读到另一个事务未提交的数据内容称为脏读！
• 幻读：如果当前事务读取某一个范围内的记录时，另一个事务又在该范围内插入新记录，导致当前事务再次读取该范围的记录时，两次结果不一样，称为幻读！
• 不可重复读：如果同一个事务前后多次读取某个数据内容，不能读取到相同的结果，(中间有另一个事务也操作了该数据)，这种情况称为不可成复读！

幻读和不可重复度的区别：

• 前者是一个范围，后者是本身数据内容，从总的结果来看，两者都表现为两次读取的结果不一致！

3. 事务隔离级别由低到高有哪几种？MySQL默认是哪种？

考察点：事务的隔离级别、MySQL的事务隔离级别

• 事务的隔离级别：由低到高(未提交读 => 提交读 => 可重复读 => 可串行化)

  • 未提交读(Read Uncommitted)：读取未提交内容，事务中的修改即使没有提交，其他事务也能读取，事务可以读到为提交的数据称为脏读， 也存在不可重复读、幻读问题！

    -- 脏读例子:
    -- 运营小姐姐配置了一个付费课程活动，原价500元的课程，配置成50元，但是事务没提交。这时，你刚好看到这个课程那么便宜准备购买，但是运营小姐姐马上回滚了事务，重新配置并提交了事务，你准备下单的时候发现价格变回了500元
    

  • 提交读(Read Committed)：读取提交内容，一个事务开始后只能看见已经提交的事务所做的修改，在事务中执行两次同样的查询可能得到不一样的结果，也叫做不可重复读(前后多次读取，不能读到相同的数据内容)，也存在幻读问题！

    -- 不可重复度例子：
    -- 老王在小滴课堂有1000积分，准备去兑换《面试专题课程》，查询数据库确实有1000积分，但是老王的女友同时也在别的地方登录，把1000积分兑换了《SpringCloud微服务专题课程》，且在老王之前提交事务；当系统帮老王兑换《面试专题课程》时发现积分预计没了，兑换失败。
    
    -- 老王事务A事先读取了数据，他女友事务B紧接了更新了数据且提交了事务，事务A再次读取该数据时，数据已经发生了改变！
    

  • 可重复读(Repeatable Read)：mysql默认的事务隔离级别，解决脏读、不可重复读的问题，存在幻读问题。

    幻读问题：MySQL的InnoDB引擎通过MVCC自动帮我们解决，即多版本并发控制！

    -- 幻读例子：
    -- 老王在小滴课堂有1000积分，准备去兑换《面试专题课程》，查询数据库确实有1000积分，老王的女友同时也在别的地方登录先兑换了这个《面试专题课程》，老王的事务提交的时候发现提示购买的课程已经存在了，之前读取的没用了，像是幻觉。
    

  • 可串行化(Serializable)：解决脏读、不可重复读、幻读，可保证事务安全，但强制所有事务串行执行(即，一个事务执行，其他事务需要排队等待)，所以并发效率低！

4. MySQL如何解决不可重复度和幻读问题？

• 不可重复读：针对于修改同一条数据，会出现前后不一致的情况。解决方式为添加行锁

• 幻读：针对于一批数据，主要体现在新增和删除操作。解决幻读需要锁整张表

参考文章1：https://blog.csdn.net/nhlbengbeng/article/details/84951613

参考文章2：https://blog.csdn.net/sanyuesan0000/article/details/90235335

对于MVCC，在《高性能MySQL》中有如下解释：

4.1 MySQL解决不可重复读问题

-- MySQL中，默认使用的事务隔离界别是可重复读，为了解决不可重复读问题，InnoDB采用了MVCC(多版本并发控制)【基于乐观锁】来解决！
-- MVCC(多版本并发控制)是利用在每条数据后面加了隐藏的两列（创建版本号和删除版本号），每个事务在开始的时候都会有一个**递增的当前事务版本号**！

-- MVCC新增
begin; -- 假设获取的 当前事务版本号=1
insert into user (id,name,age) values (1,"张三",10); -- 新增，当前事务版本号是1
insert into user (id,name,age) values (2,"李四",12); -- 新增，当前事务版本号是1
commit; -- 提交事务

id	name	age	create_version	delete_version
1	张三	10	1	NULL
2	李四	12	1	NULL

-- 上表可以看到，插入的过程中会把当前事务版本号记录到列 create_version 中去！

-- MVCC删除：删除操作是直接将行数据的删除版本号更新为当前事务的版本号
begin； --假设获取的 当前事务版本号=3
delete from user where id = 2;
commit; -- 提交事务

id	name	age	create_version	delete_version
1	张三	10	1	NULL
2	李四	12	1	3

-- MVCC更新操作:采用 delete + add 的方式来实现，首先将当前数据标志为删除，然后再新增一条新的数据
begin;-- 假设获取的 当前事务版本号=10
update user set age = 11 where id = 1; -- 更新，当前事务版本号是10
commit; -- 提交事务

id	name	age	create_version	delete_version
1	张三	10	1	10
2	李四	12	1	3
1	张三	11	10	NULL

-- MVCC查询操作：
begin;-- 假设拿到的系统事务ID为 12
select * from user where id = 1;
commit; -- 提交事务

查询操作为了避免查询到旧数据或已经被其他事务更改过的数据，需要满足如下条件：

1、查询时当前事务的版本号需要大于或等于创建版本号create_version

2、查询时当前事务的版本号需要小于删除的版本号delete_version，或者当前删除版本号delete_version=NULL

即：(create_version <= current_version < delete_version) || (create_version <= current_version && delete_version-=NULL) ，这样就可以避免查询到其他事务修改的数据，同一个事务中，实现了可重复读！

执行结果应该是：

id	name	age	create_version	delete_version
1	张三	11	10	NULL

4.2 MySQL解决幻读问题

什么是幻读，如下：

InnoDB实现的RR通过mvcc机制避免了这种幻读现象

快照读和当前读

让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

快照读介绍

当执行select操作时，innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了也不会影响当前select的数据，这就实现了可重复读了。

快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit，这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的版本号记录，写操作后把版本号改为了当前事务的版本号，所以即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致幻读。

• 在快照读情况下，mysql通过mvcc来避免幻读。
• 在当前读情况下，mysql通过X锁或next-key来避免其他事务修改：
  • 使用串行化读的隔离级别
  • (update、delete)当where条件为主键时，通过对主键索引加record locks(索引加锁/行锁)处理幻读
  • (update、delete)当where条件为非主键时，通过next-key锁处理。next-key是record locks(索引加锁/行锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)的结合

Next-Key Lock即在事务中select时使用如下方法加锁，这样在另一个事务对范围内的数据进行修改时就会阻塞（为什么有共享锁会阻塞？不能在有共享锁的记录上加X锁）

select * from table where id<6 lock in share mode; --共享锁
select * from table where id<6 for update; --排他锁

关于next-key locks请参考https://www.cnblogs.com/zhoujinyi/p/3435982.html

5. MySQL常见的存储引擎?

• MySQL 5.5之前使用的是MYISAM引擎，5.5以上版本用的是InnoDB引擎
• 二者区别：

区别项	InnoDB	MYISAM
事务	支持	不支持
锁粒度	行锁，适合高并发	表锁，不适合高并发
是否默认	默认	非默认
支持外键	支持外键	不支持
适用场景	读写均衡，写多读少场景，需要事务	读多写少场景，不需要事务
全文索引	不支持(可以借助插件或者使用ElasticSearch)	支持

6. MySQL的行锁与表锁，乐观锁悲观锁问题？

• 锁粒度越小，并发支持度越高！

参考我的博客MySQL锁相关问题

7. MySQL索引相关问题？

参考我的博客MySQL索引分析以及相关面试题

8. 数据库设计三大范式?

• 第一范式(确保每列保持原子性)
• 第二范式(确保表中的每列都和主键相关)
• 第三范式(确保每列都和主键列直接相关,而不是间接相关)：
  • 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

文章参考：https://blog.csdn.net/huangyaa729/article/details/89924358

9. MySQL查询的指令顺序为？

• 查询指令的顺序为：SELECT=> FROM=> WHERE=> GROUP BY => HAVING => ORDER BY

select -- 查看哪些结果字段
from -- 从哪个表查询
where -- 初步过滤条件
group by -- 过滤后进行分组[重点]
having -- 对分组后的数据进行二次过滤[重点]
order by -- 按照怎样的顺序进行排序返回[重点]

SELECT * num FROM chapter GROUP BY video_id  HAVING  num >10 ORDER BY video_id DESC

10. MySQL中字段类型CHAR 和 VARCHA 的区别？

对比项	char(16)	varchar(16)
长度特点	长度固定，存储字符	长度可变，存储字符
长度不足情况	插入的长度小于定义长度时，则用空格填充	小于定义长度时，按实际插入长度存储
性能	存取速度比varchar快得多	存取速度比char慢得多
使用场景	适合存储很短的，固定长度的字符串，如手机号，MD5值等	适合用在长度不固定场景，如收货地址，邮箱地址等

11. MySQL中字段类型DATETIME 和 TIMESTA的区别？

类型	占据字节	范围	时区问题
datetime	8 字节	1000-01-01 00:00:00到 9999-12-31 23:59:59	存储与时区无关，不会发生改变
timestamp	4 字节	1970-01-01 00:00:01 到 2038-01-19 11:14:07	存储的是与时区有关，随数据库的时区而发生改变

• 为什么timestamp只能到2038年

-- MySQL的timestamp类型是4个字节，最大值是2的31次方减1，结果是：
2147483647

-- 转换成北京时间就是: 
2038-01-19 11:14:07

12. Mybatis中 # 和 $的区别？

• # 可以防止SQL 注入，它会将所有传入的参数作为一个字符串来处理。# 防止SQL注入底层相当于是在操作JDBC时，使用PreparedStatement预编译SQL语句来防止SQL注入
• $ 则将传入的参数拼接到SQL上去执行，一般用于表名和字段名参数，$ 所对应的参数应该由服务器端提供。
• JDBC中的SQL注入案例：https://blog.csdn.net/ashleyjun/article/details/100558518
• SQL 注入案例：

SELECT * FROM users WHERE `username` = ''OR' 1=1'

有图看到，我虽然没有输入对正确的用户名，但是一次性查出了很多用户信息，这就是SQL注入！

13. MySQL大数据量sql分页优化思路？

问题：线上数据库的一个商品表数据量过千万，做深度分页的时候性能很慢，有什么优化思路？

- 现象：千万级别数据很正常，比如数据流水、日志记录等，数据库正常的深度分页会很慢
- 慢的原因：select * from product limit N,M
- MySQL执行此类SQL时需要先扫描到N行，然后再去取M行,N越大,MySQL扫描的记录数越多，SQL的性能就会越差

解决思路：

-- 1、可以使用后端缓存Redis、前端缓存localstorage
-- 2、使用ElasticSearch分页搜索
-- 3、合理使用 mysql 索引
-- 比如title,cateory被设置为该表的复合索引，可以提高查询效率
select title,cateory from product limit 1000000,100
  
-- 4、如果id是自增且不存在中间删除数据，使用子查询优化，定位偏移位置的 id
-- 这种方式比较耗时，因为需要先检索前1000000行数据，再检索1000000-1000500的目标数据
select * from oper_log where type='BUY' limit 1000000,100; -- 5秒
-- 因为id是主键索引，查询速度快，先检索前1000000行记录的id值，并找到第1000000行记录的id值
select id from oper_log where type='BUY' limit 1000000,1; -- 0.4秒 
-- 再做一个子查询,因为是主键递增，所以id>=第1000000行记录的id值，这样就相当于跳过扫描前100000行数据，直接从第1000000开始往后检索100条数据
select * from oper_log where type='BUY' and  id>=(select id from oper_log where type='BUY' limit 1000000,1) limit 100; -- 0.8秒 

14. MySQL常见日志种类和作用？

- 1. redo 重做日志:
- 作用：确保事务的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，到达事务一致性
  
- 2. undo 回滚日志
- 作用：保证数据的原子性，记录事务发生之前的数据的一个版本，用于回滚。innodb事务的可重复读和读取已提交 隔离级别就是通过mvcc+undo实现
  
- 3. errorlog 错误日志
- 作用：Mysql本身启动、停止、运行期间发生的错误信息
  
- 4. slow query log 慢查询日志
- 作用：记录执行时间过长的sql，时间阈值可以配置，只记录执行成功

- 5. binlog 二进制日志
- 作用：用于主从复制，实现主从同步
  
- 6. relay log 中继日志
- 作用：用于数据库主从同步，将主库发送来的binlog先保存在本地，然后从库进行回放
  
- 7. general log 普通日志
- 作用：记录数据库操作明细，默认关闭，开启会降低数据库性能

15. MySQL事务的特性是通过什么实现的？

• 隔离性借助锁来实现
• 持久性通过redo log重做日志实现
• 原子性通过undo log回滚日志实现：MySQL数据库在InnoDB存储引擎中，还使用Undo Log来实现多版本并发控制
  • 当delete一条记录时，undo log中会记录一条对应的 insert记录；
  • 当insert一条记录时，undo log中会记录一条对应的delete记录；
  • 当update一条记录时，undo log中会记录一条对应的update记录；
• MySQL通过原子性、隔离性、持久性来保证一致性。C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。

MySQL锁相关请参考：MySQL锁相关问题学习