MySQL面试题

听、、

已于 2025-01-22 10:40:19 修改

阅读量185

点赞数 3

分类专栏： MySQL 文章标签： mysql 面试数据库

于 2024-12-31 18:56:21 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_46744191/article/details/144855772

版权

MySQL 专栏收录该内容

1 篇文章

订阅专栏

什么是数据库事务？它有哪些特性

答案：数据库事务是作为单个逻辑工作单元执行的一系列操作，这些操作要么全部成功提交，要么全部失败回滚，以确保数据库的一致性。它具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四个特性，即 ACID 特性。

MySQL 有哪些存储引擎，它们的区别是什么

答案：常见的存储引擎有 InnoDB、MyISAM、Memory 等。InnoDB 支持事务、行级锁，具有外键约束，适合处理大量并发事务的场景；MyISAM 不支持事务和行级锁，表级锁效率较高，适合以读为主的应用；Memory 将数据存储在内存中，读写速度快，但数据易丢失，常用于临时数据存储。

什么是索引？它有什么作用

索引是一种数据结构，它存储在数据库中，由数据库引擎创建和维护。

索引的作用：

提高查询速度
- 这是索引最主要的作用。在大型数据库表中，如果没有索引，查询操作可能需要遍历整个表来查找满足条件的记录，这会消耗大量的时间和系统资源。例如，一个有百万行数据的电商订单表，若要查询某个特定用户的订单，没有索引的情况下可能需要扫描整个表；而如果在用户 ID 列上建立了索引，数据库可以通过索引快速定位到该用户的订单记录，大大缩短查询时间。
保证数据的唯一性
- 通过创建唯一索引可以确保表中某一列或几列组合的值具有唯一性。例如，在用户表中的 “手机号码” 列上创建唯一索引，当尝试插入一条违反唯一性约束的记录（即插入一个已存在的手机号码）时，数据库会抛出错误，从而保证数据的准确性和一致性。
支持数据的排序和分组操作
- 索引本身是一种有序的数据结构。在执行 ORDER BY 或 GROUP BY 操作时，如果查询的列上有索引，数据库可以利用索引的有序性来更高效地完成这些操作。例如，对一个销售记录表，在 “销售日期” 列上有索引，当需要按照销售日期对销售记录进行排序时，数据库可以直接利用索引的顺序来返回排序后的结果，而不需要额外的排序步骤，从而提高操作效率。
加快表之间的连接操作
- 在涉及多个表的连接查询中，索引可以帮助快速定位到相关表中的匹配记录。例如，在一个订单表和一个客户表的连接查询中，如果订单表的 “客户 ID” 列和客户表的 “客户 ID” 列都有索引，那么数据库在执行连接操作时可以更高效地找到匹配的订单和客户记录，提高查询的整体性能。

如何优化 MySQL 查询性能

创建合适的索引：
- 根据查询的条件和业务逻辑来创建索引。对于经常在 WHERE 子句中出现的列，如查询用户表中 “性别为男” 的用户，若 “性别” 列经常被查询，就在 “性别” 列创建索引。对于多列查询，可以创建复合索引。例如，在订单表中经常通过 “用户 ID” 和 “订单日期” 来查询订单，可创建一个包含 “用户 ID” 和 “订单日期” 的复合索引。但要注意避免创建过多无用的索引，因为索引会占用额外的存储空间，并且在插入、更新和删除数据时也需要维护索引，增加了操作的时间成本。
- 索引的区分度要大，区分度小的没必要创建索引。区分度小可能会导致全表扫描。（一个索引上不同的值的个数，称之为“基数”（cardinality），也就是说，这个基数越大，索引的区分度越好。）
优化索引的使用：
- 检查查询是否正确使用了索引。可以通过 EXPLAIN 命令来分析查询语句的执行计划，查看是否使用了索引以及索引的使用方式是否高效。例如，对于范围查询（如 “WHERE age> 30”），如果在 “age” 列上有索引，MySQL 会使用索引，但如果在索引列上使用了函数（如 “WHERE YEAR (birth_date) = 1990”），可能会导致索引失效。
- 利用索引，避免回表：
  - 覆盖索引。主键索引的叶子节点存的是整行数据。非主键索引的叶子节点内容是主键的值。如果执行的语句是 select ID, year from T able where year = 2025，year是一般索引，这时只需要查 ID 的值，而 ID 的值已经在 year 索引树上了，因此可以直接提供查询结果，不需要回表。
  - 联合索引。一定要查询效率高频才创建，因为索引字段的维护是要付出代价的。
    - 时间代价：因为联合索引意味着索引的值会变得更细更多，那么每增加一条数据进行的索引调整就会更加频繁
    - 空间代价：因为每一个数据块的大小是固定的，每一条索引占用的空间越大，每个数据块可容纳的索引条数就更小，带来的结果就是 M 变小，B+ 树的高度就会变大，进一步降低查询效率
优化查询语句本身
- 避免全表扫描：
  - 尽量通过添加合适的查询条件来缩小查询范围。例如，不要使用 “SELECT * FROM users;” 这种没有条件限制的查询，除非确实需要获取全部数据。如果只需要查询部分列，就明确指定列名，如 “SELECT name, age FROM users;”。
  - 对于关联查询，确保关联条件的准确性和高效性。例如，在连接两个表时，使用正确的关联字段，并且如果可能的话，在关联字段上创建索引。
  - 最左前缀原则：最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
- 简化查询逻辑：
  - 减少子查询的使用，因为子查询可能会导致性能下降。如果可以用连接查询或者其他方式替代子查询，尽量选择更高效的方式。例如，将一个复杂的嵌套子查询转换为连接查询。
  - 合理使用临时表。在某些情况下，将复杂的查询结果存储到临时表中，然后对临时表进行进一步的查询操作可能会提高性能，但也要注意临时表的使用场景和大小限制。
优化数据库表结构
- 范式化与反范式化结合：
  - 通常情况下，遵循数据库范式可以减少数据冗余和更新异常等问题。例如，第一范式要求每个列都是不可再分的原子值，第三范式要求非主键列不传递依赖于主键。但在某些性能敏感的场景下，可以适当进行反范式化。例如，为了减少关联查询的次数，将经常一起查询的列放在一个表中，通过增加数据冗余来提高查询性能。
- 合理选择数据类型：
  - 根据数据的实际范围和性质选择合适的数据类型。例如，如果一个列存储的整数范围在 0 - 255 之间，选择 TINYINT 类型而不是 INT 类型，可以节省存储空间。对于字符串类型，根据预估的长度选择合适的类型，如 VARCHAR 或 CHAR，避免浪费空间。
数据库配置优化
- 调整缓存设置：
  - MySQL 有多种缓存机制，如查询缓存（Query Cache，不过在较新的 MySQL 版本中已经逐渐被废弃）、InnoDB 缓冲池（InnoDB Buffer Pool）等。合理设置缓冲池的大小可以提高数据的读取性能。如果服务器内存充足，可以适当增大缓冲池大小，让更多的数据和索引能够缓存在内存中，减少磁盘 I/O 操作。
- 调整参数设置：
  - 根据服务器的硬件资源和应用的性能需求，调整一些关键的 MySQL 参数。例如，设置合适的 max_connections 参数来控制最大连接数，避免连接数过多导致服务器性能下降；调整 innodb_flush_log_at_trx_commit 参数来平衡数据的安全性和性能，当设置为 2 时，可以减少磁盘写入的频率，提高性能，但会稍微降低数据的安全性。
定期维护数据库
- 数据清理和归档：
  - 定期删除无用的数据，如已经过期的日志记录、被标记为删除的记录等。对于一些历史数据，可以考虑将其归档到其他存储介质或者数据库中，以减少主数据库的负担。
- 表优化和碎片整理：
  - 对于频繁进行插入、更新和删除操作的表，可能会产生碎片，影响查询和写入性能。可以使用 OPTIMIZE TABLE 命令来优化表结构，减少碎片，提高性能。例如，对于 MyISAM 表，OPTIMIZE TABLE 会重新组织表的存储结构，对于 InnoDB 表，它会重建表的索引并更新统计信息。

什么是死锁？如何避免死锁

答案：死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。避免死锁的方法包括：合理设计数据库结构和事务逻辑，尽量减少事务的并发度；按照相同的顺序访问资源；设置合理的锁超时时间；使用索引来减少锁的粒度等。

MySQL 的事务隔离级别有哪些？分别有什么特点

答案：MySQL 的事务隔离级别有读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。读未提交会出现脏读问题；读已提交可以避免脏读，但可能出现不可重复读；可重复读可以避免脏读和不可重复读，是 MySQL 的默认隔离级别；串行化通过强制事务串行执行，避免了所有并发问题，但性能较低。

设计数据库表时，如何考虑范式

答案：数据库范式是为了消除数据冗余、更新异常、插入异常和删除异常等问题而设计的规则。在设计表时，通常要遵循第一范式（1NF），确保每个字段都是不可再分的原子值；尽量满足第二范式（2NF），在满足 1NF 的基础上，非主键字段完全依赖于主键；以及第三范式（3NF），在满足 2NF 的基础上，非主键字段不传递依赖于主键。但在实际设计中，也需要根据业务需求和性能考虑，适度反范式化。

如何备份和恢复 MySQL 数据库

答案：可以使用 MySQL 自带的工具如mysqldump命令进行备份，例如mysqldump -u username -p password database_name > backup.sql，将数据库备份到一个 SQL 文件中。恢复时，可以使用mysql命令，如mysql -u username -p password database_name < backup.sql。也可以使用一些图形化工具如 Navicat 等进行备份和恢复操作，还可以通过设置数据库的主从复制等方式实现数据的备份和恢复。

常用的聚合函数有哪些

count(),sum(),avg(),max(),min()

drop、delete、truncate:三者的区别

Delete 用来删除表的全部或者一部分数据行

Truncate 删除表中的所有数据，并且清空表中的历史水位

Drop 命令从数据库中删除整张表以及表中的数据

char 和 varchar 的区别是什么

存储方式

char是定长存储，存储的是固定长度的字符，例如，char(10) 会始终占用 10 个字符的空间，无论存储的实际字符数量是多少，不足部分会用空格填充。
varchar是变长存储，存储的是实际字符长度加上 1 或 2 个字节的长度前缀（用于存储实际字符的长度）

性能方面

char在存储和检索数据时，由于其定长的特性，处理速度可能会更快，尤其是对于长度固定的短字符串，因为数据库引擎可以快速定位和处理数据。
varchar由于需要存储长度前缀，并且在存储长度变化大的数据时，可能会导致数据存储位置的调整，性能可能稍逊一筹。

数据存储限制

char：
- 最大长度为 255 个字符。
varchar：
- 在 MySQL 5.0.3 及以前，最大长度为 255 个字符；在 MySQL 5.0.3 之后，最大长度可以达到 65535 个字符，但受行大小限制，实际可存储的字符数会有所不同。

in与 exist的区别

IN：MySQL 会先执行子查询，将子查询的结果集缓存起来，然后对于主查询中的每一行数据，逐一检查其指定列的值是否在子查询的结果集中。如果子查询结果集很大，可能会导致性能问题，因为它需要将整个结果集加载到内存中进行比较。
EXISTS：MySQL 会针对主查询的每一行数据，去执行子查询，检查子查询是否能找到匹配的行。子查询只要找到一条匹配的记录，就会返回TRUE，主查询就会返回对应的行。它并不需要将子查询的所有结果都查询出来，只要找到满足条件的记录就可以停止查询。