MYSQL 面试常见问题汇总

参考博客：

https://blog.csdn.net/weinierzui/article/details/71054964

Q1：主键，外键，超键，候选键；

主键	对表中数据进行唯一标识的数据列的组合；不能缺失；不能空值；
外键	该列为另一表的主键；
超键	关系中能唯一标识数据的属性；
候选键	不含多余属性的超键；

Q2：数据库事务的四个特征及含义；

原子性	要么全部完成，要么不完成，若发生错误会进行回滚操作；
一致性	开始到结束后，数据库完整性约束没收到破坏；（实体完整性，参照完整性，用户定义的完整性）
隔离性	事务与事务之间相隔离，串行化执行；
持久性	事务完成对数据的影响是永久的；

Q3：视图的作用，可以更改吗；

视图是虚拟的表；只包含动态检索数据的查询，不包含数据；简化操作，隐藏细节，保护数据；对视图的更新会作用于基表，一般不更新；

Q4：drop,delete和truncate ;

drop	表级的删除；不能回滚；
truncate	清空表；不记录单行删除日志；无法恢复；只能对于TABLE操作；不能在带FOREIGN KEY约束的表（被引用的表）中使用；计数从头开始；
delete	配合where删除数据；会记录日志用于回归；会触发触发器；不减少索引和表的空间；

Q5：索引的工作原理和其种类；

索引的实现通常采用B树或B+树，加快查询速度也消耗更多空间；

唯一索引	不允许任何两行具相同值
主键索引	唯一索引的一种
聚集索引	行的物理顺序和键值的索引顺序相同

普通索引	无限制
全文索引	针对较大的数据生成全文索引很耗时间空间
组合索引	最左前缀原则：若对多列建立组合索引，若第二列未使用索引，则第三列也不会使用

InnoDB	主索引：InnoDB的数据文件本身；辅助索引：相应记录主键的值
MyISAM	索引与数据分离；辅助索引：与主索引无区别；

Q6：连接的种类

内部联接（等值联接）	INNER JOIN
外部联接（包含在相关表中没有关联行的行）	LEFT OUTER JOIN
	RIGHT OUTER JOIN

Q7：数据库范式

1NF	每一列都是不可分割的基本数据项，同一列无二值；无重复的域；
2NF	实例依赖于主键部分；
3NF	属性不依赖于其他非主属性；

Q8：存储过程与触发器的区别

存储过程和触发器都是SQL语句集；触发器不可用CALL调用，而是在用户执行某些语句后自动调用；

Q9：分表与分区

分表	真正的分表，每张表对应三个文件；提高MYSQL的并发能力；
分区	表中的数据分成多个区块；突破磁盘的读写能力；

Q10：数据库隔离级别

脏读	一个事务读取了另一个事务未提交的数据
不可重复读	在一次事务范围内，读取同一数据产生了不同的值
虚读	读取整体的数据后，因其他事务对数据的更新，再次查询时结果不同

串行化	3种均可避免
可重复读（默认）	避免1,2
读已提交	1
读未提交	无

虚读 / 幻读解决方案：为了防止虚读，可以采取以下措施：

- 使用更高的事务隔离级别，如可串行化。

- 使用乐观锁或悲观锁机制。

- 通过应用程序逻辑来确保数据的一致性。

Q11：MYSQL的两种存储引擎

MYISAM	不支持事务，不支持外键，表锁；插入数据时锁定整个表，查行数时无需整表扫描
INNODB	支持事务，外键，行锁，查表总行数时，全表扫描；

Q12：MYSQL索引算法

HASH	适合等值查找，不适合范围，不能排序
BTREE	适合范围查找，无hash冲突

Q13：聚集索引和非聚集索引

聚集索引	数据按索引顺序存储，节点存储的是真实数据
非聚集索引	节点存储的是指向真正数据的指针

Q14：索引的优缺点

优点	提高查询效率
缺点	降低了更新效率

Q15：两种存储引擎索引的区别

Innodb	主索引的数据文件本身就是索引文件；辅助索引记录主键的值；
MyISAM	主索引数据文件和索引文件分离；与主索引无区别；

Q16：数据库的主从复制

一个服务器作为主服务器，一个或多个服务器作为从服务器，主服务器将更新写到二进制日志，当一个从服务器连接到主服务器时，通知主服务器读取日志，接收从那时起发生的所有更新。解决：数据分布，负载平衡，备份，高可用性和容错性

基于语句	在主服务器上执行的语句，在从服务器上也执行
基于行	将改变的内容复制过去
混合类型	语句复制失败时采用行的形式

Q17：数据库连接池

为数据库连接建立一个缓冲池，防止过于大量的连接的建立与管理；

Q18：存储过程

存储过程是一些预编译的SQL语句，执行效率较高

Q19：乐观锁和悲观锁

乐观锁	假定不会发生并发冲突，只在提交时检查，若有其他数据更新了数据，则回滚；使用数据版本标示数据（时间戳，版本号）
悲观锁	假定会发生并发冲突，屏蔽一切破坏数据库一致性的操作，主要用于数据争用激烈的环境，以及锁成本低于回滚成本时；排他锁；

Q20：MVCC

MySQL中的MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用于提高数据库并发性能的技术。它允许在不使用锁的情况下，通过维护数据的多个版本来处理并发读写操作，从而减少读写冲突。

MVCC的实现主要依赖以下几个关键组件：

1. Undo日志：它是MVCC能够实现的核心，每当数据被修改时，旧版本的数据会被保存到undo日志中。Undo日志分为两种：insert undo日志和update undo日志。insert undo日志在事务提交后可以被丢弃，而update undo日志则需要在purge线程中被清理 103105。

2. Read View：当一个事务进行快照读操作时，会生成一个read view，它记录了当前事务可以看到的所有数据版本的信息。Read view只针对Read Committed（RC）和Repeatable Read（RR）隔离级别。

3. 隐式字段：InnoDB为每一行记录添加了三个隐藏字段，包括：

   • DB_TRX_ID：记录最后一次修改该行的事务ID。
   • DB_ROLL_PTR：回滚指针，指向该行的undo日志，用于MVCC。
   • DB_ROW_ID：隐含的自增ID，如果没有主键，InnoDB会使用这个字段作为聚簇索引 104106。

MVCC可以解决脏读、不可重复读和幻读问题，但不能解决写写冲突中的更新丢失问题。在RR隔离级别下，MVCC通过read view来保证一个事务在整个过程中读取到的数据是一致的，解决了不可重复读问题。而在RC隔离级别下，MVCC通过undo日志来实现非锁定读取，提高了并发性能，但可能遇到不可重复读问题 。

MVCC是解决什么问题？

• 并发事务对数据的读操作不会产生并发问题，所以不用解决；
• 并发事务对数据的读+写，常规操作一般会对要操作的数据加锁来解决并发读+写可能产生的问题，MySQL的InnoDB实现了MVCC来更好地处理读写冲突，可以做到即使存在并发读写，也可以不用加锁，实现"非阻塞并发读"。
• 并发事务对数据的写操作，只能通过加锁(乐观锁/悲观锁)来解决。

具体如何解决？

参考博客：

https://juejin.cn/post/7066633257781035045