MySQL 面试-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_42217906/article/details/145707039

MySQL 常见的存储引擎以及它们之间的区别是什么？

MySQL 常见的存储引擎有 InnoDB、MyISAM、Memory 等。

InnoDB：支持事务，遵循 ACID 特性，具备行级锁，能有效降低锁冲突，提升并发性能；支持外键约束，保证数据完整性；采用聚簇索引，数据和索引存储在一起，适合处理大量并发写入和读取操作，是 MySQL 5.5 版本之后的默认存储引擎。
MyISAM：不支持事务和外键，表级锁，并发性能较差，适合读多写少的场景，如日志记录、数据统计等。其数据文件和索引文件是分开存储的。
Memory：数据存储在内存中，读写速度极快，但数据易丢失，因为重启 MySQL 服务后数据会消失。使用哈希索引，适合作为临时表，用于存储临时数据，如缓存查询结果。

MySQL事务隔离级别

MySQL支持四种事务隔离级别，分别为读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable）。

读未提交（Read Uncommitted）：这是最低的隔离级别，允许一个事务读取另一个事务未提交的数据。这种隔离级别可能导致脏读（读取到其他事务未提交的数据）、不可重复读（在一个事务内多次读取同一数据结果不同）和幻读（一个事务内多次查询返回的记录数不同）等问题。例如，事务A更新了一条记录但未提交，事务B此时读取该记录，就会读到事务A未提交的修改。在实际开发中，该级别基本不会使用。
读已提交（Read Committed）：一个事务只能读取其他事务已经提交的数据，解决了脏读问题，但仍可能出现不可重复读和幻读。比如，在一个事务中多次读取某条记录，在两次读取之间，另一个事务提交了对该记录的修改，那么两次读取的结果就会不同。这是Oracle数据库的默认隔离级别，适合大多数业务场景。
可重复读（Repeatable Read）：MySQL的默认隔离级别，确保一个事务在执行期间多次读取相同的行时，会看到相同的数据，可避免脏读和不可重复读。虽然理论上仍可能发生幻读，但InnoDB存储引擎在该隔离级别下使用Next - Key Lock锁算法，实际上可以避免幻读。例如，事务A在执行过程中多次查询某一范围内的数据，在事务A提交前，即使其他事务插入了符合查询条件的新数据，事务A查询到的结果也不会改变。
串行化（Serializable）：最高的隔离级别，通过强制事务串行执行来避免脏读、不可重复读和幻读的问题。在该级别下，一个事务在访问数据时会对数据加锁，其他事务必须等待该事务完成才能访问相同的数据。虽然它能保证数据的强一致性，但性能较差，一般在对数据一致性要求极高且并发量较低的场景下使用。

可以使用select @@transaction_isolation;命令查看当前的隔离级别，使用set session transaction isolation level [隔离级别名称];来设置不同的隔离级别，其中[隔离级别名称]可以是read uncommitted、read committed、repeatable read或serializable 。
在这里插入图片描述

MySQL事务的特性

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

什么是脏读、不可重复读、幻读？

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

MySQL 索引为什么使用 B + 树？

磁盘 I/O 次数少：B + 树的节点存储了多个键值和指针，相比于二叉查找树，它的高度更低，在查找数据时，磁盘 I/O 次数更少，提高了查询效率。
范围查询高效：B + 树的叶子节点通过双向链表相连，这使得范围查询时，只需要遍历叶子节点链表即可，而无需像 B 树那样多次回表查询，提升了范围查询的性能。
排序方便：由于叶子节点有序且相连，对数据进行排序时，直接遍历叶子节点即可。

MySQL支持的索引类型有哪些？

MySQL支持的索引类型主要有以下几种：B - Tree索引、哈希索引、全文本索引、空间索引，从约束角度还可分为：主键索引、唯一索引、普通索引。详细介绍。

MySQL 覆盖索引是什么？

覆盖索引指一个查询语句的执行只需要从索引中获取数据，而不需要回表查询数据行。当查询列都包含在索引中时，就会使用覆盖索引。例如，表中有id、name、age字段，创建了(id, name)索引，若查询语句为SELECT id, name FROM table WHERE id = 1，此时就可以使用覆盖索引，直接从索引中获取数据，避免了回表操作，提高查询效率。

MySQL 索引失效的条件，索引在什么时候不会起效果？

查询条件中使用函数或表达式：如SELECT * FROM table WHERE YEAR(date) = 2025，YEAR(date)是函数，会导致索引失效。
使用LIKE进行左模糊匹配：如SELECT * FROM table WHERE name LIKE '%abc'，因为无法利用索引快速定位，索引失效。
数据类型不匹配：如字段定义为INT类型，查询时传入字符串'1'，可能导致索引失效。
在OR条件中，其中一个条件字段没有索引：若SELECT * FROM table WHERE id = 1 OR name = 'test'，name字段无索引，那么id字段的索引也可能失效。

MySQL 如何进行优化的？

索引优化：合理创建索引，避免索引冗余和索引失效，使用覆盖索引提高查询效率。
查询语句优化：避免全表扫描，减少子查询，使用JOIN优化关联查询，分析EXPLAIN结果调整查询语句。
存储引擎选择：根据业务需求选择合适的存储引擎，如 InnoDB 适合高并发读写，MyISAM 适合读多写少场景。
服务器配置优化：调整 MySQL 配置参数，如innodb_buffer_pool_size（InnoDB 存储引擎的缓冲池大小）、max_connections（最大连接数）等，以适应服务器硬件和业务负载。
分区表：对于大表，可进行分区，如按时间分区，提高查询和维护效率。

什么是 explain 命令？它的作用是什么？

EXPLAIN命令用于分析 SQL 查询语句的执行计划。它会展示查询语句如何执行，包括表的读取顺序、使用的索引、如何连接表等信息。通过分析EXPLAIN的输出结果，可以了解查询语句的性能瓶颈，从而优化查询语句。例如，通过查看type字段判断查询类型（如ALL表示全表扫描，index表示索引扫描等），通过key字段查看实际使用的索引，通过rows字段预估扫描的行数，以此来评估查询效率并进行优化。

MySQL 遇到的死锁问题、如何排查与解决？

死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

排查：查看 MySQL 的错误日志，其中会记录死锁相关信息；使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 引擎状态，能获取详细的死锁信息，包括死锁发生的时间、涉及的事务、锁的类型和对象等。

解决：

设置死锁检测和超时参数：如innodb_deadlock_detect（默认为开启，可检测死锁）和innodb_lock_wait_timeout（设置锁等待超时时间，超时后事务会自动回滚），可根据业务情况调整这些参数。
事务重试：应用程序捕获死锁异常后，自动重试事务。
优化事务逻辑：减少事务持有锁的时间，避免事务中长时间占用资源，合理安排事务操作顺序，降低死锁发生概率。

常见的数据库优化方案有哪些，在你的项目中数据库如何进行优化的？

常见优化方案：在设计表时，采用反三范式设计（不经常修改的字段可以冗余进来）、索引优化、查询语句优化、存储引擎选择、服务器配置优化、分区表等。
在项目中，首先对业务进行分析，确定频繁查询和更新的字段，创建合适的索引；对复杂查询语句，使用EXPLAIN分析执行计划，调整查询逻辑；根据业务读写特点，选择合适的存储引擎；在服务器配置方面，根据服务器硬件资源和业务负载，调整 MySQL 配置参数；对于大表，根据数据特点进行分区，如按时间或业务主键分区，提高数据管理和查询效率。同时，定期对数据库进行性能监控和优化，及时发现和解决潜在问题。

什么是 redo 日志，什么是 undo 日志？

redo 日志：重做日志，用于记录事务对数据页的修改操作，是物理日志。当事务提交时，先将 redo 日志写入磁盘，之后再将数据更新操作应用到数据页。它的作用是保证事务的持久性，即事务提交后，即使发生系统崩溃，重启后也能通过 redo 日志将数据恢复到事务提交后的状态。
undo 日志：回滚日志，用于记录事务修改前的数据，是逻辑日志。在事务执行过程中，若发生错误或需要回滚事务，可通过 undo 日志将数据恢复到事务开始前的状态，保证事务的原子性和一致性。同时，undo 日志还用于 MVCC（多版本并发控制），为事务提供旧版本的数据，实现并发事务之间的隔离。

乐观锁和悲观锁是什么？

乐观锁：是一种假设数据库操作不会发生冲突的锁定机制。在执行数据更新操作时，它并不会立刻加锁，而是先允许所有事务继续执行，并在提交时检查数据是否发生了变化。如果数据在读取后被其他事务修改了，那么当前事务就会被阻止，并给出一个冲突的提示。
悲观锁：是一种假设数据库操作会发生冲突的锁定机制。在执行数据更新操作时，事务会先加锁，其他事务在锁释放之前不能访问被锁定的数据，从而保证数据一致性。悲观锁通常是在读取数据时就加锁，直到事务结束才释放锁。