经典面试题-MySQL

布响丸辣大约 20 分钟

索引

1.数据库三大范式

第一范式（1NF）：确保每个属性都是原子性的，即每个属性都不能再分割为更小的数据单元。同时，每个属性都要具有唯一的名称，不允许重复。
第二范式（2NF）：保证非主键属性完全依赖于主键，即非主键属性不能只依赖于主键的部分属性。如果存在这样的情况，需要将表进行拆分，使每个表只包含一个实体类型。
第三范式（3NF）：保证非主键属性之间不存在供给依赖关系，即非主键属性不能依赖于其他非主键属性。如果存在这样的情况，需要将表进行拆分，使每个表只包含一个实体类型。

通过遵循这三个规范化级别，可以减少数据和数据的不一致性，提高数据库的数据的完整性和可靠性。

2.MySQL存储引擎MyIsam和InnoDB有哪些区别。

题解：很直接，就是问两个存储引擎的区别？从5个方面来对它们进行对比。

事务支持：MyIsam不支持事务；InnoDB支持事务，拥有ACID四大特性(原子性、一致性、隔离性、持久性)，InnoDB在高并发环境下可以更好地处理数据一致性和完整性问题(可以很好解决常见并发问题：1.脏读、2.幻读、3.不可重复读)。
锁的粒度：InnoDB支持行级锁定，可以提高并发性能；而MyIsam则只支持表级锁定，因此在高并发环境下性能较差。
外键约束：InnoDB支持外键约束，MyIsam不支持，所以InnoDB可以确保数据一致性和完整性。
索引方式：InnoDB使用B+树索引来管理数据，可以高效地处理大量数据，而MyISAM使用B树索引，不如B+树索引高效(B+树与B树有区别)。
磁盘空间：InnoDB的数据存储方式比MyISAM更为复杂，因此在相同的数据集下，InnoDB需要更多的磁盘空间。

——引申：还有什么其他的存储引擎？

答：Memory ：由于将数据存到内存中，所以速度快，适用于临时表和高速缓存

CSV：将数据存成以逗号分隔形式的文本文件，便于和外部程序交换数据

BLACKHOLE：写入的数据都会被丢弃，常用于测试或日志记录

3.什么是索引？都有哪些类型？有什么优点和缺点？

索引是一种帮助MySql高效获取数据的数据结构，在InnoDB中使用的是B+树，MyIsam使用的是B树。

索引可分为主键索引与普通索引。其中普通索引也叫二级索引，类型上包括唯一索引，单列索引，联合索引，全文索引等。

优点:可以加快查询的效率。

缺点:维护起来成本很高且需要占用很多的存储空间。

4. B树和B+树的区别。

B树和B+树都是一种自平衡的多叉树，特点是树高较低，整体呈现一种矮胖的形态，它们有以下区别：

数据存放位置不同：

B树：B树的叶子节点和非叶子节点都可以存放数据。每个节点存的是数据和指针。

B+树：B+树的所有数据都存放在叶子节点上，非叶子节点存放的是索引，优化了磁盘I/O性能。

B+树更适合范围查询：

B+树的叶子节点是一个有序链表，B树的叶子节点是无序的。所以B+树更适合范围查询。

MySQL对B+树有优化：

经过MySQL的优化B+树的叶子节点上存的是数据页，数据页上面缀的是有序链表，数据页之间使用双向指针连接。每个数据页不超过16kb的数据，使用二分查找的方式查询数据页。

5. 为什么使用B+树不使用红黑树、二叉树？

红黑树和二叉树是每个节点最多有两个子节点的树结构，红黑树是二叉树的优化，增加了颜色属性；为什么使用B+树,那是因为：

查询效率上：当数据量足够大时，B+树的树高比红黑树和二叉树低，需要更少的磁盘I/O操作，查询效率更高。
查询场景上：红黑树作为自平衡的二叉搜索树，无论树的大小如何，操作的时间复杂度都是logn，适合单个元素的快速查询；而B+树的叶子节点是一个有序链表，且所有数据都存在叶子节点，更适合适合范围查询。
索引方式上：红黑树实现的是非聚集索引，而B+树实现了聚集索引，数据和索引放在一起，减少了磁盘I/O操作次数，查询效率高。

6. 聚集索引和非聚集索引的区别？

聚集索引和非聚集索引并不是一种实际的数据结构,而是对一类索引的定义.

聚集索引的数据和索引放在一起，查询效率高；非聚集索引的索引和主键id放在一起，可能会导致回表，查询效率低。
聚集索引数据行的物理顺序和索引的逻辑顺序一样，查询效率高，但是当进行写操作时，数据行的物理顺序需要重新排列，导致性能下降；非聚集索引和它完全相反（数据行的物理顺序和索引的逻辑顺序不一样，查询效率较低；进行写操作时，数据行的物理顺序不需要重新排序，性能较好）

主键ID使用的是聚集索引，聚集索引的数据和索引放在一起，在查询的时候根据索引查询即可；而二级索引使用的是非聚集索引：非聚集索引的索引和主键ID放在一起，在使用二级索引查询时，如果索引中没有我们需要的字段时，它就会根据主键ID到原表中查询，这个过程就是回表。如果没有主键ID，那么MySQL就会自动给我们设置一个隐藏的主键ID：rowID，它看不见，摸不着，为了方便我们对数据库进行操作，所以我们需要自行设置一个主键ID

8. 什么是回表，怎么减少回表？

回表就是使用非聚集索引时,索引中没有需要的字段,于是需要根据指针去原表中找其他列值

尽量多的使用覆盖索引，将常用字段设置为聚集索引

9.为什么会有索引失效的情况？

索引失效的情况：

不符合最左前缀原则
使用了范围查询条件中有or（若想让索引生效，只能将or条件中的每个列都加上索引），或者是like查询以%开头、!=操作符， null值查询，
字符串索引，在使用时，数据一定要使用引号引用起来。若是int类型的索引，数据使不使用引号都可以。使用函数、计算操作也会导致索引失效（涉及到隐式转换）
当mysql估计使用去全表扫描比使用索引快时，如表中数据量较少时
SQL中使用了函数
索引列类型不匹配：如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL可能无法使用索引

10. 如何进行索引调优？

索引调优：

分析查询语句和数据模式：仔细分析查询语句和数据模式，了解查询的特点和使用频率，确保索引的创建和选择符合实际需求。
选择合适的索引策略：根据查询的特点选择合适的索引策略，包括覆盖索引、联合索引、前缀索引等，以提高查询性能。
优化查询语句：优化查询语句的结构和条件，避免使用不适合索引的操作符或函数操作，尽量使查询条件能够命中索引。
避免过度索引：避免创建过多的索引，因为过多的索引会增加数据维护的开销，并可能导致索引失效或选择不正确的索引。
使用索引提示：在需要的情况下，使用索引提示（Index Hint）来强制MySQL使用指定的索引，以避免错误的索引选择。
性能测试和监测：通过性能测试和监测工具，如EXPLAIN、 Slow Query Log等，评估索引的效果，并进行必要的调整和优化

11.索引下推是什么？

索引下推是可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

锁

12.MySQL中的锁有哪些？

问题延伸：既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

全局锁：
1. 对整个数据库实例加锁。
命令是Flush table with read lock(FTWRL)
阻塞数据的增删改、数据定义（建表、改表）和更新类事物的提交。
使用场景：全库逻辑备份。
表锁：分为表锁和元数据锁（MDL）。
1. 表锁
1. 1. 表锁语法：lock tables … read/write
表锁解锁：unlock tables
表锁释放时间：客户端断开连接的时候自动释放
1. 元数据锁
如何使用：MDL 不需要显式使用，在访问一个表的时候会被自动加上,已保证读写的正确性
什么时候加什么锁：当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
加锁、解锁时机：在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
行锁：
1. 相比于表锁：开销大、加锁慢。但表锁不会死锁,行锁会死锁。
两阶段协议：行锁是在需要的时候加上锁，在事务结束时释放锁
Record Locks 行记录锁：
1. 1. 是什么：最基本的锁，锁会加在索引上；没有主键索引则会加载row_id上。查询条件是二级索引时，会回到主键索引加锁。
需要注意什么：当查询没有索引时，会走全表，把查到的每一行都加锁，在RC（读提交）下，加锁的语句执行完成后，就会直接释放掉不符合要求的行锁。因此，如果一条更新语句没有走索引，会花费极大的开销。
1. Gap Locks 间隙锁：
1. 1. 解决幻读的问题：在RR（可重复读）的隔离级别下，解决方法就是间隙锁。
关于幻读的问题:出现幻读也不是非常严重的问题，可以把隔离级别降到RC（读提交）这样可以提高并发性。间隙锁虽然彼此不冲突，本身也是花费一些开销，而且会和写入操作发生冲突，影响并发。
功能：锁住的是两个行之间的数据，不允许其他人向中间写入一个数据。
1. Next-Key Locks：
1. 1. 是什么：可以认为是记录锁和间隙锁的组合。
加锁后会发生什么：无论主键索引还是二级索引，都会加上间隙锁。 Next-Key Locks 因为包含行锁，因此会出现冲突。
1. Insert Intention Locks 插入意向锁：
1. 1. 只有在insert的时候会使用，和间隙锁冲突，但是彼此不冲突。

12.什么是MDL锁？

MDL（Metadata Locking）是数据库管理系统（DBMS）中的一种机制，用于管理和控制对数据库对象（如表、索引、视图等）的元数据的并发访问。

13. MDL什么时候加锁？

事务修改数据：当一个事务对数据库中的数据进行修改（插入、更新、删除）时，会获取相应的锁来确保事务的原子性和一致性。这样可以防止其他并发事务同时修改相同的数据，避免数据冲突和不一致性。
并发事务访问同一数据：当多个事务同时访问同一数据时，可能会存在读写冲突或数据不一致的问题。为了保证数据的隔离性，需要使用锁来协调并发事务之间的访问。例如，读操作可能会获取共享锁（Shared Lock），而写操作则需要获取排它锁（Exclusive Lock）。
数据库对象的元数据访问：当多个事务同时访问数据库对象的元数据时（如表、索引、视图的定义），需要采用元数据锁（MDL）来管理并发访问。元数据锁用于保护元数据的一致性，防止并发事务之间的冲突。

14. MDL有什么危害？

并发性能下降：过多的元数据锁操作可能导致并发性能下降。当多个事务需要访问相同的元数据对象时，如果存在过多的元数据锁竞争，会引发锁冲突和阻塞，从而导致事务等待和执行时间延长，对并发性能产生负面影响。
阻塞和长时间等待：如果一个事务持有元数据锁并长时间不释放，其他事务需要等待该锁的释放。这可能导致其他事务阻塞和长时间等待，从而影响系统的响应性能和并发能力。
死锁：如果事务之间的元数据锁请求存在循环依赖，可能引发死锁。当多个事务相互等待对方所持有的元数据锁时，系统无法继续执行，需要通过死锁检测和解决机制来解决。
数据库对象的不一致性：如果对数据库对象的元数据访问没有正确加锁或使用不恰当的隔离级别，可能导致数据库对象的不一致性。例如，一个事务正在修改表的结构或索引定义时，其他事务可能读取到不一致的元数据，导致数据库对象定义的不一致性。

15. 如何避免出问题?

应该在设计数据库架构时合理规划和管理元数据锁的使用。可以考虑优化元数据的访问频率和方式，减少对元数据的并发访问冲突，使用合适的隔离级别，以及定期进行性能监控和调优。同时，合理并发控制和锁管理策略也是确保元数据锁正常运行和性能的重要措施。

16. 行锁在什么时候加锁？

行锁在事务需要的时候进行加锁，在事务结束的时候再释放锁。什么时候需要是指当进行读操作时进行手动加锁或者是快照读升级为当前读的时候进行加锁。

17. next-key lock锁的两优化是什么？

索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

18. 乐观锁与悲观锁。

悲观锁

悲观锁是一种比较保守的锁机制，它认为在整个事务过程中，数据很有可能会被其他事务修改，因此在对数据进行操作时，必须先对其进行加锁，以防止其他事务的干扰。悲观锁通常使用数据库的锁机制实现，如行锁、表锁等，可以有效地控制并发访问，但会对系统性能产生影响。

乐观锁

乐观锁是一种比较乐观的锁机制，它认为在整个事务过程中，数据很少会被其他事务修改，因此在对数据进行操作时，并不对其进行加锁，而是在事务提交之前检查数据是否被其他事务修改过，如果被修改过，则回滚事务，重新进行操作。乐观锁通常使用版本号或时间戳等机制实现，可以减少锁的使用，提高系统性能，但需要增加一些额外的开销来实现数据版本控制。

事务

19.Mysql的事务隔离级别有哪些？

读未提交：一个事务还未提交时可以被别的事务读取数据
读提交：一个事务提交之后，所做的变更可以被别的事务看到
可重复读：事务在执行中用到的数据与最开始所看到的数据是一样的
串行化：加锁出现冲突时事务必须要等到前一个事务执行完成隔离级别越高，性能就会下降。

PS:MySQL的默认隔离级别是可重复读

20. MVCC是如何实现的？

MVCC又叫基于版本号的多版本并发控制，主要解决隔离性的问题；mvcc工作在读已提交和可重复读的隔离级别下，两者最大的不同是创建read view的时机不同，采用read view和undo log版本链解决，undo log是事务链表，记录有(事务)trx_i和rool_pointer(指针)，在read view 读视图中有min_trx_id(最小事务id)、max_trx_id(最大事务id)、create_trx_id(创建事务id)和m_trx_id(活跃事务id);工作原理是查看当前事务是否在当前read view中，如果在，会寻找上一个版本的事务id，如果不在，该事务就是查找的事务。

21.脏读，幻读，不可重复读是什么？

脏读、幻读和不可重复读是数据库并发控制中的三个问题。

脏读（Dirty Read）指一个事务读取了另一个事务尚未提交的数据。如果后续的事务回滚，那么前面事务读取到的数据就是无效的。

幻读（Phantom Read）指一个事务在两次查询之间，另一个事务插入了新的数据行，导致前一次查询和后一次查询的结果不一致。

不可重复读（Non-repeatable Read）指一个事务多次读取同一数据，在这个过程中，另一个事务对该数据进行了修改或删除，导致前一次读取和后一次读取的结果不一致。

22. 脏读、幻读、不可重复读时如何解决的？

读提交解决脏读问题

可重复读和串行化解决幻读和不可重复读的问题

23. 数据量大的时候如何切分？

数据切分：
1. 按位置切分：国家、省市城市
1. 1. 时间切分：月份、季度、年份。
垂直切分：如user表可以切分为账号基本信息user_basic和个人资料user_profile。
水平切分：如订单数据量较大，按照日期切分成order_2020、order_2021、order_2022。

24.快照读、当前读是什么？

快照读（Snapshot Read）和当前读（Current Read）是数据库中两种常见的读取数据的方式。
快照读（Snapshot Read）：快照读是指读取数据库中某个时间点的数据快照。当进行快照读取时，读取的数据是在事务开始之前的一个一致的时间点的数据。这意味着无论事务中的其他并发操作是否已提交，快照读取都会返回事务开始之前的数据版本。快照读取不会受到正在进行的并发事务的修改的影响，能够提供一致性和可重复性的读取。
当前读（Current Read）：当前读是指读取数据库中最新的数据，即读取已经提交的事务所做的修改。当前读取获取的是实时的、最新的数据状态。如果存在并发事务对读取的数据进行修改但尚未提交，当前读取将能够看到这些未提交的修改。当前读取适用于需要读取最新状态的场景，但可能会受到并发事务的影响，因为读取到的数据可能会在事务提交之后发生变化。
在数据库中，快照读取通常用于读取一致性要求较高的数据，例如读取历史记录、生成报表等。而当前读取则适用于需要读取最新数据状态的场景，例如在线交易、实时查询等。

日志

25. bin_log 和redo_log的区别。

RedoLog 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
RedoLog 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
RedoLog 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。最后，简单说下：如果没有了RedoLog，数据就无法做到crash-safe，因为 binlog不具备保存事务的性质，最重要的是它并不具备记录数据页上究竟发生了什么。如果没有 binlog的话，RedoLog是个循环写入的，不存在归档这个功能。

26.什么是WAL机制？

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

27. 什么是两阶段提交？

两阶段提交是分布式系统中比较常见的一种事务提交算法。在Innodb的日志提交时用到了这个协议，用来保证事务提交时，redolog和binlog 都处于完成状态。具体而言：

操作完更新语句，把数据保存到内存
写入RedoLog，处于prepare阶段
写入BinLog
提交事务，BinLog和RedoLog都处于完成状态。

28. MySQL什么时候刷脏页？

RedoLog写满时，需要暂停更新操作。此时会停止所有的写入操作！
机器的物理内存满了的时候
Mysql处于空闲状态时
Mysql重启时

29. RedoLog和BinLog是如何配合工作的？

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 RedoLog：

如果碰到既有 prepare、又有 commit 的 RedoLog，就直接提交；
如果碰到只有 prepare、而没有 commit 的RedoLog，就拿着 XID 去BinLog 找对应的事务。