跳至主要內容

MySQL面试题

MySQL面试题

基础

什么是 MySQL？

MySQL 是一种开源的关系型数据库管理系统，它使用 SQL 语言进行数据管理。

什么是关系数据库的三范式？

关系数据库的三范式是指在关系模型中，通过一系列规范化过程，将数据分解为更小、更规范的关系表，以提高数据的一致性和可靠性。

  1. 第一范式（1NF）要求每个数据项都是原子性的，即不可再分解。也就是说，每个属性只能包含一个值，不能包含多个值或多个属性。
  2. 第二范式（2NF）要求每个非主属性都完全依赖于主键，即一个关系表中不能存在部分依赖关系。如果一个关系表中存在部分依赖关系，需要将其拆分成多个关系表，每个表都包含一个主键和非主属性。
  3. 第三范式（3NF）要求在2NF的基础上，消除传递依赖。也就是说，一个关系表中的非主属性不能依赖于其他非主属性，而应该直接依赖于主键。如果一个关系表中存在传递依赖关系，需要将其拆分为多个关系表。

通过遵循三范式，可以使关系数据更加规范化、高效、可靠，减少数据冗余和数据不一致的情况，提高数据的质量和可维护性。

Mysql有哪些存储引擎？

MySQL 支持多种存储引擎，以下是一些常见的存储引擎及其简单介绍：

  1. InnoDB：默认的 MySQL 存储引擎，支持事务和外键约束，具有较好的数据完整性和一致性，适用于高并发的 OLTP 应用。
  2. MyISAM：较早的 MySQL 存储引擎，不支持事务和外键约束，但具有较高的性能和较小的存储空间占用，在读密集型应用中表现优异。
  3. Memory：将数据存储在内存中，适用于对速度要求较高的临时表和缓存表。
  4. CSV：将数据以 CSV 格式存储在文件中，适用于导入/导出数据。
  5. Archive：压缩存储数据，适用于存储历史数据和日志。
  6. Blackhole：存储数据但不实际保存数据，适用于复制和分析操作。
  7. NDB Cluster：MySQL 集群存储引擎，适用于分布式环境下的高可用性和高性能应用。
  8. TokuDB：支持事务和压缩存储的存储引擎，适用于大数据量的 OLTP 和 OLAP 应用。

事务

什么是事务？

事务就是对一系列的数据库操作（比如插入多条数据）进行统一的提交或回滚操作，如果插入成功，那么一起成功，如果中间有一条出现异常，那么回滚之前的所有操作。

这样可以防止出现脏数据，防止数据库数据出现问题。

事务的特性？

  1. 原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
  2. 一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  3. 隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的隔离级别？

事务定义了4种事务隔离级别。

并发问题
隔离级别		脏读
Dirty Read		不可重复读
NonRepeatable Read		幻读
Phantom Read
未提交读
Read uncommitted		可能可能可能
已提交读
Read Committed		不可能可能可能
可重复读
Repeatable Read		不可能不可能可能
可串行化
Serializable		不可能不可能不可能

并发事务带来的问题？

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但是可能会导致以下问题：

  1. 脏读： 事务A读取了一条记录的值，然后基于这个值做业务逻辑，在事务A提交之前，事务B读取了该记录，导致事务B读到的这条记录是一个脏数据。（在事务B读取该记录之后，事务A提交之前，事务A可能会回滚等）
  2. 不可重复读： 在同一个事务里面，两次读取同一行记录，但结果不一样。因为另外一个事务在对此记录进行update操作。
  3. 幻读： 在同一个事务里面，同样的select语句，执行两次，返回的记录条数不一样。因为另外一个事务在进行insert/delete操作。
  4. 更新丢失：两个事务同时修改同一条记录，事务A的修改被事务B覆盖了。

什么是MVCC？

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL 中实现事务隔离级别的一种技术。

在 MVCC 中，每个事务在开始时都会获得一个唯一的事务 ID（Transaction ID，简称 XID），并且每个数据行也都有一个版本号（或时间戳）。当事务对数据行进行修改时，会将修改后的数据行保存为一个新版本，并将旧版本标记为已删除。这样，每个事务都可以看到自己启动时的数据行版本，而不会被其他并发事务所修改的版本所干扰。

在 MVCC 中，读操作和写操作的并发性都得到了保障。读操作可以读取旧版本或新版本的数据行，而写操作则可以并发进行，因为每个事务都在自己的版本中对数据行进行修改，不会相互干扰。

需要注意的是，MVCC 只在使用了支持事务的存储引擎（如 InnoDB）时才可用。在使用 MVCC 时，需要注意的一些问题，例如长事务可能会导致版本链过长，从而影响性能，因此需要谨慎设计应用逻辑和事务管理。

锁相关

MySQL中有哪些类型的锁？

  1. 共享锁（Shared Lock）：也称为读锁，允许多个事务同时持有该锁，用于读取数据。
  2. 排他锁（Exclusive Lock）：也称为写锁，只允许单个事务持有该锁，用于修改或删除数据。
  3. 记录锁（Record Lock）：也称为行锁，用于保护表中特定行的数据。
  4. 间隙锁（Gap Lock）：用于保护一个范围的数据，但不包括记录本身，防止其他事务插入新的记录或者删除已有的记录。
  5. Next-Key锁（Next-Key Lock）：结合了记录锁和间隙锁，用于保护一个范围的数据，包括记录本身。

什么是死锁？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

如何定位死锁？

死锁（Deadlock）是指两个或多个事务相互等待对方所持有的锁而陷入无限等待的状态，从而无法继续执行下去。MySQL 提供了一些工具和方法来定位死锁。

  1. 锁冲突日志（InnoDB Lock Monitor）：在 InnoDB 存储引擎中，可以通过开启锁冲突日志来记录锁冲突和死锁的详细信息。可以在 MySQL 配置文件中设置 innodb_print_all_deadlocks 参数来启用该功能。当发生死锁时，InnoDB 会在错误日志中记录相关信息，包括死锁的事务 ID、锁定的对象和等待的锁等信息。
  2. Performance Schema：MySQL 的性能监控工具 Performance Schema 也提供了一些死锁相关的监控指标，例如 wait/synch/mutex/innodb/lock_mutex 等。可以通过查询这些指标来了解当前是否存在死锁情况，以及哪些事务或锁定对象可能存在问题。
  3. SHOW ENGINE INNODB STATUS：在 MySQL 命令行客户端中执行 SHOW ENGINE INNODB STATUS 命令，可以查看当前 InnoDB 存储引擎的状态信息，包括锁等待情况、死锁检测等信息。该命令输出的信息较为详细，需要对输出内容进行解读。
  4. 手动分析：在无法通过上述工具和方法定位死锁时，也可以通过手动分析 SQL 语句和事务逻辑来定位死锁。可以查看当前正在执行的 SQL 语句、事务的锁定情况、等待的锁等信息，从而找出可能导致死锁的原因。

如何解决死锁？

  1. 超时机制（Timeout）：设置一个超时时间，在等待一段时间后，如果检测到死锁，则终止其中一个事务并回滚。
  2. 死锁检测与回滚（Deadlock Detection and Rollback）：周期性地检测系统中是否存在死锁，如果检测到死锁，则选择一个事务进行回滚，解除死锁。
  3. 死锁避免（Deadlock Avoidance）：通过事务的预先申请资源来避免可能导致死锁的操作序列，需要系统能够预测事务可能需要的资源并进行资源分配的安全性检查。
  4. 死锁预防（Deadlock Prevention）：通过限制事务对资源的访问顺序，避免产生循环等待，从而预防死锁的发生。

InnoDB和MyISAM之间的锁机制有何区别？

InnoDB和MyISAM是MySQL中两种常见的存储引擎，它们的锁机制有以下区别：

  1. InnoDB支持行级锁，而MyISAM只支持表级锁。这意味着在并发环境下，InnoDB可以更好地支持多个事务同时访问和修改数据，而MyISAM只能以表为单位进行锁定。
  2. InnoDB的锁是自动处理的，当一个事务需要修改或读取某一行时，仅锁定该行，而不是整个表，从而减少了锁的竞争和冲突。而MyISAM在写操作时会锁定整个表，因此在并发写入时可能会出现锁冲突。
  3. InnoDB通过多版本并发控制（MVCC）来实现行级锁，允许读取操作不会被写操作所阻塞，提高了并发性能。而MyISAM没有实现这种机制，在读写冲突时会导致阻塞。
  4. InnoDB支持外键约束，而MyISAM不支持。外键约束需要对相关的行进行加锁，以确保数据的完整性。

如何查看当前MySQL中的锁信息？

  1. 使用命令行工具：可以使用命令SHOW ENGINE INNODB STATUS\G查看InnoDB引擎的状态信息，其中包含了当前的锁信息。
  2. 查询信息_schema数据库：可以查询INNODB_LOCKS和INNODB_LOCK_WAITS表来获取当前的锁信息和等待锁的情况。
  3. 使用性能监控工具：例如Percona Toolkit、MySQL Enterprise Monitor等，这些工具提供了更详细和可视化的锁信息。

如何优化MySQL中的锁定性能？

  1. 尽量使用行级锁：在设计数据库时，尽量使用InnoDB引擎并使用行级锁，以减少锁冲突和提高并发性能。
  2. 减少事务持有锁的时间：尽量缩短事务的执行时间，避免长时间持有锁，以减少锁冲突的可能性。
  3. 适当使用事务隔离级别：根据应用需求选择合适的事务隔离级别，不要盲目使用高级别的隔离级别，以减少锁的竞争。
  4. 合理使用索引：通过合理地创建索引，可以减少数据检索时的锁定范围，提高并发性能。
  5. 分批处理大量数据：对于需要修改大量数据的操作，可以分批进行，减少单次操作持有锁的时间和锁冲突的可能性。
  6. 使用锁粒度更细的操作：对于一些只需要读取数据而不需要修改数据的操作，可以使用SELECT ... FOR UPDATE语句进行行级锁定，而不是直接使用UPDATE语句锁定整个表。
  7. 定期进行性能调优和监控：通过定期的性能调优和监控，可以及时发现和解决锁相关的性能问题，提高系统的稳定性和性能。

索引

什么是索引？

索引，类似于书籍的目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。

MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

索引的优缺点？

  • 优点：
    • 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
    • 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。
  • 缺点：
    • 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
    • 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引的类型？

索引，都是实现在存储引擎层的。主要有六种类型：

  1. 普通索引：最基本的索引，没有任何约束。
  2. 唯一索引：与普通索引类似，但具有唯一性约束。
  3. 主键索引：特殊的唯一索引，不允许有空值。
  4. 复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
  5. 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
  6. 全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

索引创建的原则

  1. 最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
  2. 索引列的基数越大，索引效果越好。
  3. 根据情况创建复合索引，复合索引可以提高查询效率。
  4. 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
  5. 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
  6. 对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

使用索引的注意事项

  1. 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
  2. 应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。
  3. 应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
  4. 应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  5. 不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  6. 复合索引遵循前缀原则。
  7. 列类型是字符串类型，查询时一定要给值加引号，否则索引失效。
  8. LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

覆盖索引和回表

覆盖索引和回表是在数据库中优化查询性能的两种常见技术。覆盖索引通过包含查询所需的所有列来避免回表操作，从而提高查询性能。而回表则是在索引无法满足查询需求时，通过再次访问数据表来获取完整的数据行。这两种技术在具体应用中可以根据查询的特点和数据表的结构来选择使用。

  1. 覆盖索引（Covering Index）： 覆盖索引是一种索引结构，它包含了查询所需要的所有列，从而避免了查询时需要访问数据行的操作。当一个查询可以完全通过索引就能得到所需的数据结果时，就称之为覆盖索引。通过使用覆盖索引，可以减少磁盘I/O操作和数据传输，提高查询性能。

例如，假设有一个包含姓名、年龄和地址的表。如果针对姓名和年龄的查询创建了一个覆盖索引，那么查询只需要扫描索引而不需要读取实际的数据行。这样可以显著减少查询所需的时间和资源。

覆盖索引的缺点是索引本身会占用更多的存储空间，并且在更新数据时需要同时更新索引。

  1. 回表（Lookup）： 当一个查询需要从索引中获取数据行时，如果索引无法满足查询的全部需求，就需要进行回表操作。回表是指根据索引中的引用，再次访问数据表来获取完整的数据行。

例如，假设有一个包含姓名、年龄和地址的表，并且为姓名列创建了索引。如果一个查询需要获取所有满足条件的年龄，那么首先会通过索引定位到满足条件的数据行的位置，然后再回到数据表中获取完整的数据行。这个过程就称为回表。

回表的缺点是它需要额外的I/O操作和数据传输，因为需要访问数据表来获取完整的数据行。这可能会导致查询性能的下降，特别是当查询结果包含大量数据行时。

推荐与反馈

上次编辑于:
贡献者: Neil