MySQL面试题

苏三的开发日记
104 阅读19分钟
1.SQL执行顺序

FROM 子句：选定数据来源的表。

WHERE 子句：筛选出满足条件的行。

GROUP BY 子句：对数据进行分组。

HAVING 子句：筛选分组后满足条件的组。

SELECT 子句：选择最终展示的列。

ORDER BY 子句：对结果进行排序。

LIMIT 子句：限制返回的行数。

2.SQL查询优化
  1. 使用索引：为频繁查询的列创建索引。
  2. 避免SELECT *：只选择所需的列减少数据传输。
  3. 使用JOIN而非子查询：在许多情况下，JOIN比子查询更高效。
  4. 分析查询：使用EXPLAIN命令查看查询的执行计划。
  5. 限制结果集：通过LIMIT减少返回的数据量
3.EXPLAIN 查询指标分析

访问类型（Access Type）：全表扫描（ALL）/索引扫描（index）/覆盖索引（Using index）

连接类型（Join Type）：嵌套循环（Nested Loop）/哈希连接（Hash Join）/排序合并连接（Sort Merge Join）

排序与分组（Sort/Group）:文件排序（Using filesort）/临时表（Using temporary）

预估行数（Rows）

问题类型优化方案
全表扫描添加合适索引，或通过WHERE条件限制范围。
文件排序为排序字段添加索引，或增加sort_buffer_size。
临时表拆分复杂查询，或优化关联条件。
预估行数不准确定期更新统计信息（如ANALYZE TABLE）。
连接效率低确保关联字段有索引，或调整连接顺序（小表驱动大表）。
覆盖索引缺失创建包含查询字段的复合索引。
4.常用的聚合函数

聚合函数用于计算从多个行中生成单一值，常用于数据汇总分析

  • COUNT()：计算行数。
  • SUM()：计算总和。
  • AVG()：计算平均值。
  • MIN()：获取最小值。
  • MAX()：获取最大值
5.数据库事务

数据库事务用于确保一组操作作为一个原子单元被执行，要么全部成功，要么全部失败，以保持数据的一致性。

6.ACID

事务的ACID特性确保了数据库的一致性和可靠性。

  • 原子性 (Atomicity)：事务中的所有操作要么全部完成，要么全部不做。
  • 一致性 (Consistency)：事务执行前后，数据库的完整性约束需得到满足。
  • 隔离性 (Isolation)：并发执行的事务互不干扰，每个事务的执行结果对其他事务不可见，直到该事务提交。
  • 持久性 (Durability)：一旦事务被提交，对数据库的修改是永久的，即使系统崩溃，数据也不会丢失
7.视图

视图并不存储数据，而是提供一种从一个或多个表中查询数据的方式。视图可以定义时计算复杂的查询，之后像普通表一样使用

8.MySQL中使用LIMIT子句进行分页

select * from tb_name limit #{page-1}*pageSize,#{pageSize}

select * from tb_name limit #{pageSize} offset #{page-1}*pageSize

9.MySQL中InnoDB与MyISAM的区别

事务支持： InnoDB支持ACID事务。 MyISAM不支持事务。

锁机制： InnoDB使用行级锁，提高了并发性能。 MyISAM使用表级锁，可能导致性能瓶颈。

外键支持： InnoDB支持外键约束，用于数据完整性。 MyISAM不支持外键。

性能： MyISAM在读取较多的场景下性能更优。 InnoDB在高并发和大型事务中性能表现更好

10.MySQL中的逻辑备份与物理备份
  1. 逻辑备份：使用SQL文件导出数据库结构和数据（如mysqldump）。备份较小，易于维护，但恢复速度可能较慢。
mysqldump -u root -p mydatabase > mydatabase_backup.sql
  1. 物理备份：复制数据库文件（如datadir目录），可快速恢复，但需确保正确的数据库状态进行备份
cp -r /var/lib/mysql/mydatabase /backup/mydatabase_backup
11.如何保证在高并发情况下安全地修改同一行数据

可以使用锁机制，如行级锁、乐观锁和悲观锁。

  • 行级锁：只锁定被修改的行，使其他操作可以继续。
  • 乐观锁：基于版本号或时间戳，检测数据是否被修改，进行冲突检测。
  • 悲观锁：在操作前获取锁，直到操作完成，避免其他事务访问。
12.MySQL中如何处理和优化重复数据
  • 使用DISTINCT关键字查询去重的结果。
  • 利用GROUP BY及聚合函数统计重复的行。
  • 创建唯一约束(UNIQUE)防止后续插入重复数据。
13.外键约束的作用

外键约束规定某列或多列的值必须在另一个表的主键或唯一列中存在，确保数据的一致性和完整性

14.数据库的三大范式
  • 第一范式 (1NF) ：确保表中每个列只保存原子值，避免重复列和多值属性。
  • 第二范式 (2NF) ：在满足1NF的基础上，每个非主键列必须完全依赖于主键。
  • 第三范式 (3NF) ：在满足2NF的基础上，非主键列不能依赖于其他非主键列
15.什么是MySQL中的分布式事务

分布式事务用于保证跨多个数据库的操作要么全部成功，要么全部回滚，以保持数据的一致性。

实现分布式事务通常使用两阶段提交协议（2PC）或更复杂的分布式事务管理器（如XA）来协调参与方的成功或失败。

16.查询优化

索引是一种数据结构，MySQL通过它减少了需要扫描的行数，从而加速了查询。使用合适的索引，可以使查询时间从O(n)降低到O(log n)或更快。索引可以是单列索引或多列索引，且必须在WHERE子句、JOIN条件和ORDER BY等场景中使用

假设有一个员工表employees，其中包含id、name和salary列。一个常见的查询需要查找所有薪水大于4000的员工，为了优化这个查询，我们可以在salary列上创建一个索引。

SELECT * FROM employees WHERE salary > 4000;
CREATE INDEX idx_salary ON employees (salary);
17.优化count()

优化 COUNT() 查询可提升统计操作的效率，尤其是在大型数据集中的表现。

  • 使用索引: COUNT(*) 通过索引可以显著快于全表扫描。
  • 避免使用*字段: COUNT(field_name) 如果列含有NULL，则可能低估计行数。
  • 使用预先计算的结果: 将统计结果存储在缓存表中，尤其是对于频繁查询的统计
18.优化insert数据

批量插入数据可以显著提高数据写入速度，减少事务开销。

  • 使用INSERT…VALUES多值插入: 一个查询插入多行，减少网络往返。
  • 关闭自动提交: 收集多个插入操作再一起提交，减少每次插入的开销。
  • LOAD DATA INFILE: 用于大量数据的快速加载。
19.优化order by

优化ORDER BY查询可以减少排序耗时，提高查询效率。

  • 使用索引: 确保ORDER BY字段有索引，特别是当字段用于排序和过滤时。
  • 限制结果集大小: 使用LIMIT限制返回结果数量。
  • 使用合适的数据类型: 确保数据类型合理（如对整数进行排序时无需用到字符类型）
20.如何处理和优化DISTINCT查询？

优化DISTINCT查询可以提高数据去重处理的效率，避免不必要的CPU消耗。

  • 使用索引: 确保DISTINCT字段有索引。
  • 只选择必要列: 精简SELECT中需要的数据列。
  • 考虑表的结构: 对于小表，可能不需DISTINCT。
21.如何处理和优化大型报告查询？

优化大型报告查询减少报告生成时间，提高用户体验。

  • 使用索引: 为涉及的过滤条件、分组以及排序的列创建适当索引。
  • 简化查询: 减少不必要的JOINs和复杂的聚合函数。
  • 缓存结果: 对于频繁生成的报告，可以考虑使用缓存策略。
  • 分区表: 对于非常大的表，将表按照某些条件进行分区可以提高查询性能
22.解释MySQL中的事务隔离级别以及它们如何影响并发

事务隔离级别定义了事务之间的可见性，直接影响并发性能和数据一致性。

MySQL支持四种事务隔离级别：

  • READ UNCOMMITTED: 最低级别，事务可以读取未提交的数据，可能导致脏读。
  • READ COMMITTED: 提高了数据一致性，事务只能读取已提交的数据，但仍可能导致不可重复读。
  • REPEATABLE READ: 默认级别，确保在同一事务中多次查询同一数据的结果相同，避免不可重复读，但可能导致幻读。
  • SERIALIZABLE: 最高级别，强制事务串行执行，完全避免脏读、不可重复读与幻读，但相应地降低并发性
23.死锁是如何产生的，如何预防和解决？

死锁是指两个或多个事务在执行过程中，各自持有对方所需的锁资源，导致所有事务无法继续执行。死锁通常发生于两个事务分别锁定了对方需要的资源。

预防措施：

  • 尽量按相同顺序获取锁。
  • 使用较低的事务隔离级别。
  • 对事务进行重试。
25.悲观锁与乐观锁

优化并发控制策略以提高数据一致性以及系统性能。

悲观锁: 在数据操作开始时就对数据加锁，认为数据会经常发生冲突，因此会阻止其他事务访问。多用于大多数读和写场景（如使用SELECT ... FOR UPDATE）。

乐观锁: 假设数据冲突不常发生，允许事务在进行时不使用锁，只有在提交时才检查数据是否发生变化。通常用于读多写少的场景。用版本号或时间戳控制版本。

26.MVCC是什么

MVCC通过使用多个数据版本，实现高并发下的事务一致性。

MVCC是一种并发控制机制，允许多个事务并发执行，在不加锁的方式下确保事务一致性。在更新数据的同时，保留数据的多个版本以供读取。这使得读取操作不讲被写入阻塞，从而提高性能。

27.表锁与行锁

数据库锁和表锁是MySQL中用于管理并发访问的重要机制，这些锁的目的是保证数据的完整性和一致性。

数据库锁：用于在数据库级别锁定整个数据库，使得在锁定期间，其他用户无法对该数据库的任何表进行操作。适用于需要对整库操作的场景，但会影响其他用户的访问效率。

表锁：锁定指定的表以防止其他用户对该表的读写。表锁比数据库锁更细粒度，可以减少对其他表操作的影响，但在高并发场景下，可能导致等待时间增加。

28.MySQL中的锁升级是什么？

锁升级是为了减少锁的数量，从而提高性能和避免死锁的机制。

在MySQL中，如果多个行被锁定，且锁的数量达到一定量，MySQL系统可能会自动将行锁（更细粒度的锁）升级为表锁（更粗粒度的锁）。这是为了优化性能和减少锁的管理开销。但是，这种操作也可能在高并发环境下引发的问题，比如延迟或阻塞。

具体案例: 假设两个用户在多个行上同时进行操作，若锁定的行数超过了MySQL预设的阈值，系统可能会将这些行锁转换为更大的表锁，导致其他用户对该表的所有操作都不能进行，造成性能瓶颈。

29.什么是索引

索引是数据库中用于加速查询的一种数据结构，它允许快速查找数据而不是对整个表进行扫描。

索引通过创建指向数据行的指针来实现，当数据库执行查询时，可以使用索引来快速定位需要的数据，而无需全表检索。它大大提高了查询的效率，尤其是在处理大数据集时

30.二级索引

二级索引是在数据库表中对非主键列建立的索引，它允许快速访问基于非主键的查询。

二级索引不会包含主键，而是包含索引列及指向主键的指针。这样能在执行查询时提高效率，但也会增加索引的维护开销

31.MySQL的B树索引和哈希索引有什么区别？

B树索引和哈希索引是两种不同的索引结构，各自在特定情况下有优劣之分。

  • B树索引：支持范围查询，适合于多种查询条件，维护过程中比较平衡，查询性能优异。
  • 哈希索引：只支持等值查询，检索速度快，但在范围查询时表现不佳。哈希索引最好用于查找单一值
32.MySQL中IN与JOIN操作有什么性能差异？

IN和JOIN操作都用于从多个表中检索数据，但它们的性能表现有差异，依赖于使用场景。

  • IN操作：通常用于子查询中，将一个结果集用作当前查询的过滤条件，直接从结果集中匹配数据。
  • JOIN操作：用于将两个或多个表结合起来，基于共享的列进行比对
33.exists优化

EXISTS用于测试子查询的结果是否存在，有助于避免不必要的数据处理。

使用EXISTS可以更高效地进行数据过滤，尤其在处理子查询结果时，EXISTS会在找到满足条件的第一条记录后立即返回，避免了不必要的计算。

34.解释MySQL中的联合索引，如何正确使用？

联合索引允许在一个索引中包含多个列，以优化多列查询的性能。

当创建联合索引时，MySQL会考虑索引的列顺序，查询条件必须遵循最左前缀规则

35.MySQL查询到数据库数据的过程

SQL→网络传输连接器→权限检查解析器→语法/语义检查优化器→执行计划执行器→Handler API存储引擎\text{SQL} \xrightarrow{\text{网络传输}} \text{连接器} \xrightarrow{\text{权限检查}} \text{解析器} \xrightarrow{\text{语法/语义检查}} \text{优化器} \xrightarrow{\text{执行计划}} \text{执行器} \xrightarrow{\text{Handler API}} \text{存储引擎}

36.聚簇索引与聚簇索引
特点聚簇索引 (Clustered Index)非聚簇索引 (Non-Clustered Index/二级索引)
数据与索引关系索引即数据：索引的叶子节点直接存储了整行数据。索引与数据分离：索引的叶子节点存储的是索引键值和指向数据行记录的指针（对于 InnoDB 来说，这个“指针”就是主键值）。
物理存储顺序决定数据物理顺序：数据行在磁盘上的物理存储顺序与聚簇索引的顺序一致。不决定数据物理顺序：数据行的物理存储顺序与非聚簇索引的顺序无关。
数量限制一个表只能有 1\mathbf{1} 个：因为数据行只能按一种顺序物理存储。一个表可以有多个：可以根据不同的列创建多个辅助索引。
默认创建主键默认创建：在 InnoDB 中，如果你定义了 PRIMARY KEY，它就会成为聚簇索引。如果没有主键，InnoDB 会选择一个唯一的非空索引代替；如果都没有，会隐式创建一个行 ID 作为聚簇索引。除聚簇索引外的所有索引（包括普通索引、唯一索引、外键索引等）都是非聚簇索引。
数据访问一步到位：通过聚簇索引查询时，找到索引叶子节点即找到数据，访问速度最快。可能需要“回表查询” ：首先通过非聚簇索引找到主键值，然后再次使用这个主键值去聚簇索引中查找完整的行数据。
适用场景主键查找、范围查询（因为物理顺序连续，顺序读取效率高）。非主键列的查找和过滤。
37.索引的底层数据结构

MySQL（特指最常用的 InnoDB 存储引擎）的索引底层数据结构主要使用B+ 树（B+ Tree） MySQL（特指最常用的 InnoDB 存储引擎）的索引底层数据结构主要使用B+ 树（B+ Tree） 。

为什么选择 B+ 树？

B+ 树是为磁盘存储系统专门优化设计的多路平衡查找树，其优势在于：

  1. 减少磁盘 I/O 次数：

    • 数据库的数据和索引都是存储在磁盘上的“数据块”（或“页”）中。每次访问磁盘块（I/O）耗时远高于内存操作。
    • B+ 树是“多路”的，每个内部节点可以存储非常多的索引键值。这意味着树的高度非常低（通常只有 3 到 4 层），从根节点到叶子节点，只需要进行极少数次的磁盘 I/O 即可定位数据。

  2. 适合范围查询：

    • B+ 树的所有叶子节点都用有序的链表连接起来。
    • 这使得范围查询（如 WHERE salary BETWEEN 3000 AND 5000）非常高效。数据库只需要找到范围的起始点，然后沿着叶子节点的链表顺序遍历即可，避免了重复查找。

  3. 便于全表扫描：

    • 如果需要进行全表扫描（在聚簇索引中），可以通过遍历叶子节点链表来快速完成
38.MySQL如何处理大量的并发连接

  • 应用端连接池 (Connection Pooling) ：

    • 作用： 在应用程序中维护一个固定数量的数据库连接，请求时借用，使用后归还，避免频繁地创建和关闭连接，这是最高效的优化手段。

  • MySQL 线程池 (Thread Pool) ：

    • 作用： 将大量的客户端连接（Connections）与少量的执行线程（Threads）分离。
    • 效果： 极大地减少了操作系统线程数量，从而降低了 CPU 上下文切换的开销，保持高吞吐量。

  • 合理配置：

    • 设置合理的 max_connections 限制连接总数，防止资源耗尽。
    • 优化 innodb_buffer_pool_size 等缓存，让查询尽可能在内存中完成，减少磁盘I/O

  • 架构优化（横向扩展）：

    • 读写分离 (Read/Write Splitting) ：
      • 将主数据库用于写操作（INSERT/UPDATE/DELETE）。
      • 将从数据库（Read Replicas，只读副本）用于读操作（SELECT）。
      • 由于大多数应用都是读多写少，这可以极大地分担主库的压力。
    • 分库分表 (Sharding) ：
      • 将巨大的数据表和数据库拆分成多个更小、更易管理的部分，并分布到多台服务器上。
      • 彻底解决了单机容量和处理能力的瓶颈，但增加了架构和应用逻辑的复杂性
39.如何在MySQL中优化大表的性能
  1. 查询优化（Query Optimization）
  • 避免 SELECT * ：只选择需要的列，减少数据传输和处理量。
  • 使用 EXPLAIN 分析查询：用 EXPLAIN 关键字来查看MySQL如何执行你的 SELECT 查询，识别全表扫描、低效连接等性能瓶颈。
  • 优化 WHERE 和 JOIN 条件：确保用于过滤、排序和连接的列上有合适的索引。
  • 限制结果集：对于只需要部分数据的查询，使用 LIMIT 限制返回的行数。
  • 避免在 WHERE 子句中使用函数：在索引列上使用函数（如 YEAR(date_col) = 2023）会导致索引失效，应重写为范围查询（如 date_col >= '2023-01-01' AND date_col < '2024-01-01'）。
  • 优化 LIKE 语句：避免使用前导通配符（如 LIKE '%keyword'），这也会导致索引失效。如果必须使用，可以考虑使用全文索引（Full-Text Index）。
  • 使用批量操作：使用批量 INSERT 或在事务中组合多个 UPDATE/DELETE 操作，可以减少数据库连接和日志写入开销。
  1. 索引策略（Indexing Strategies）
  • 创建合适的索引：在经常用于 WHERE 子句、JOIN 操作、ORDER BY 或 GROUP BY 的列上创建索引。
  • 复合索引（Composite Indexes） ：考虑创建包含多个列的索引。复合索引的列顺序很重要，应将最常用于过滤的列放在前面（遵循“最左前缀原则”）。
  • 避免过度索引：过多的索引会增加存储空间，并降低 INSERT、UPDATE 和 DELETE 操作的速度，因为每次数据修改都需要更新所有相关索引。
  • 使用覆盖索引（Covering Indexes） ：如果一个索引包含了查询中所需的所有列，MySQL可以直接从索引中获取数据，而无需回表查询，这在 EXPLAIN 结果中通常显示为 Using index。
  1. 表结构和设计（Table Structure and Design）
  • 表分区（Partitioning） ：对于非常大的表，可以考虑使用分区。分区将一个大表拆分成多个逻辑子表，但从应用层面看它仍然是一个表。这对于基于时间或ID范围的查询尤其有用，可以只扫描相关的分区。
  • 选择合适的数据类型：使用尽可能小且最合适的数据类型。例如，用 INT 代替 BIGINT，用 VARCHAR 代替 TEXT（如果数据长度可控），这可以减少存储空间和内存消耗。
  • 数据归档（Archiving） ：将不再活跃但需要保留的历史数据归档到另一张表、另一个数据库甚至其他存储介质（如 NoSQL 数据库），以减小主表的规模。
  1. 数据库和系统配置

  • 调整 innodb_buffer_pool_size：对于使用 InnoDB 存储引擎的表（MySQL默认），这是最重要的配置参数。将其设置为系统内存的 50% 到 70% 是一个好的起点，确保热数据和索引能尽可能地缓存在内存中。

  • 慢查询日志（Slow Query Log） ：启用慢查询日志来识别执行时间超过阈值的查询。这是查找性能瓶颈的首要工具。

  • 定期维护：

    • OPTIMIZE TABLE：对于一些存储引擎（如 MyISAM，虽然 InnoDB 不常用，但在某些情况下也可以使用），可以对表进行碎片整理，回收空间并提升I/O效率。
    • ANALYZE TABLE：运行此命令来更新表统计信息，帮助查询优化器选择最佳执行计划。
  1. 硬件升级
  • 内存（RAM） ：增加内存以容纳更大的 InnoDB 缓冲池。
  • 存储（Disk I/O） ：使用 SSD 固态硬盘替代传统的机械硬盘（HDD），可以显著提高数据读写速度。
  • CPU：对于高并发或计算密集型查询，升级 CPU。
avatar
文章
阅读
粉丝