MySql相关面试题总结

MySql相关面试题总结

1、MysQL事务的ACID是什么？

考察点：事务的4个特征

• 原子性Atomicity：
  • 一个事务必须是不可分割的最小工作单元，整个事务操作要么全部成功，要么全部失败，一般就是通过commit和rollback来控制。
• 一致性Consistency：
  • 事务必须使数据库从一个一致性状态变换到另一个一致性状态。以转账为例子：假设转账之前这两个用户的钱加起来总共是2000，A向B转账，不管两个账户怎么转账，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。
• 隔离性Isolation：
  • 一个事务相对于另一个事务来说是隔离的，一个事务所作的修改是在最终提交以前，对其他事务是不可见的，一个事务对一个数据进行操作的时候，其他事务不允许对同一个数据进行操作；
• 持久性Durability：
  • 事务一旦提交，则其所作的修改就会永久保存到数据库中。此时即时系统崩溃，修改的数据也不会丢失。

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

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

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

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

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

3、事务隔离级别由低到高有几种？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如何解决不可重复读和幻读问题？

• 不可重复读：针对于修改同一条数据，会出现前后不一致的情况。解决方式为添加行锁。
• 幻读：针对于一批数据，主要体现在新增和删除操作。解决幻读需要锁整张表。

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

MVCC即多版本并发控制，通过读取指定版本的历史记录，并通过一些手段保证读取的记录值符合事务所处的隔离级别，在不加锁的情况下解决读写冲突

• InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或者删除时间）。当然存储的并不是实际的时间值，而是系统版本号（System Version Number）。每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPTABLE READ隔离级别下，MVCC具体是如何操作的。

• SELECT：

  • InnODB会根据以下两个条件来检查每行记录：

    • InnoDB只查找早于当前事务版本的数据行（也就是，行的系统版本号小于或者等于事务的系统版本号），这样就可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    • 行的删除要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

    只有符合上述两个条件的记录，才能返回作为查询结果。

• INSERT：

  InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

• DELETE：

  InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

• UPDATE：

  InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

• 保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。这样涉及使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行数据。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

• MVCC只在REPLATED READ和READ COMMITED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

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
3	张三	12	10	NULL

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

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

• 查询的时候当前事务的版本号需要大于或者等于查询行数据的创建版本号
• 查询的时候当前事务的版本号需要小于删除的版本号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开启了事务，然后没有执行任何操作，这时候Binsert了一条数据然后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

关于MVCC也可以去读读这篇文章可以更好的理解：https://www.cnblogs.com/xuwc/p/13873293.html

什么情况下MVCC也会出现幻读？

事务1	事务2
select * from user where class_id=1
id name class_id
1 Joe 1
2 Jill 1
	insert into user values(3,‘jack’,1) COMMIT;
select * from user where class_id=1;
id name class_id
1 Joe 1
2 Jill 1
update user set name=‘Jony’;
select * from user where class_id=1;
id name class_id
1 Joe 1
2 Jill 1
3 Jack 1

• a事务先select，b事务insert确实会加一个next-key lock锁，但是如果b事务commit，这个next-key lock就会释放（释放后a事务可以随意操作）
• a事务再select出来的结果再MVCC下还和第一次select一样
• 接着a事务不加条件的update，这个update就作用再所有行上（即执行当前读）
• a事务再次select救会出现b事务中的新行，并且这个新行已经被update修改过了。
• 上面这样，b事务提交过后，a事务再次执行uodate，因为这个是当前读，它会读取最新的数据，包括别的事物已经提交的，所以就会导致此时前后读取的数据不一致，出现幻读。

5、MySQL常见的存储引擎

• MySQL5.5之前使用的是MyISAM引擎，5.5以上版本用的是InnoDB引擎
• 二者区别：

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

6、数据库设计三大范式

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

7、MySQL查询的指令顺序为？

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

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

8、MySQL中字段类型CHAR和VARCHAR的区别？

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

9、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次方减一，结果是：
2147483647

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

10、Mybaits中#和$的区别？

• #可以防止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注入！

11、MySQL大数据量sql分页优化思路？

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

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

解决思路：

-- 1、可以使用后端缓存Redis、前端缓存LocalStorage
-- 2、使用ElasticSearch分页搜索
-- 3、合理使用MySQL索引
-- 比如title、category被设置为该表的复合索引，可以提高查询效率
select title,category from product limit 10000000,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值，这样就相当于跳过扫描前1000000行数据，
-- 直接从第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秒 

12、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 普通日志
- 作用：记录数据库操作明细，默认关闭，开启会降低数据库性能

13、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的三大特性，才有可能实现一致性。

参考文章：https://csp1999.blog.csdn.net/article/details/113801545