Skip to main content

MySQL

ventixyAbout 19 min

MySQL数据库基础

基础及常见问题

  • MySQL 中 EXISTS 和 IN 的区别是什么？

    • EXISTS：通常用于检查子查询是否返回至少一行数据，返回布尔值（存在或不存在）。性能较好，特别是在子查询结果集较大的情况下。
    • IN：用于检查某个值是否存在于子查询的结果集中，返回布尔值。适用于结果集较小的情况。

  • MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

    • count(*)：统计所有行数，包括 NULL 值。
    • count(1)：等同于 count(*)，常用于提高可读性。
    • count(字段名)：统计指定字段不为 NULL 的行数。

  • MySQL 中 INNER JOIN、LEFT JOIN 和 RIGHT JOIN 的区别是什么？

    • INNER JOIN：返回两个表中匹配的行。
    • LEFT JOIN：返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，结果为 NULL。
    • RIGHT JOIN：返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，结果为 NULL。

  • MySQL 中 DELETE、DROP 和 TRUNCATE 的区别是什么？

    • DELETE：删除表中的特定行，可以使用 WHERE 子句指定条件，操作日志记录。
    • TRUNCATE：删除表中的所有行，不记录日志，速度快，但不能回滚。
    • DROP：删除整个表，包括表结构，操作不可逆。

  • 什么是数据库的逻辑删除？数据库的物理删除和逻辑删除有什么区别？

    • 逻辑删除：通过设置标志位（如 is_deleted）来标记记录已删除，实际数据仍保留在数据库中。
    • 物理删除：彻底删除记录，释放存储空间。
    • 区别：逻辑删除可以恢复数据，物理删除不可恢复，且释放存储空间。

  • 在 MySQL 中，你使用过哪些函数？

    • 字符串函数：LOWER()、UPPER()、CONCAT()、SUBSTRING()
    • 数值函数：ABS()、CEIL()、FLOOR()、ROUND()
    • 日期函数：NOW()、CURDATE()、CURTIME()、DATE_ADD()、DATE_SUB()
    • 聚合函数：COUNT()、SUM()、AVG()、MAX()、MIN()

  • MySQL 中的数据排序是怎么实现的？

    • 命中索引：如果排序字段命中索引，MySQL 直接使用索引排序。
    • 文件排序（Filesort）：如果未命中索引，MySQL 使用文件排序算法进行排序。

  • MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么？

    • 错误：MySQL 会抛出错误 Error 1062: Duplicate entry '...' for key 'PRIMARY'。
    • 解决方案：手动重置 AUTO_INCREMENT 值，或删除表后重建。

  • MySQL 中 LIMIT 100000000, 10 和 LIMIT 10 的执行速度是否相同？

    • 不同：LIMIT 100000000, 10 需要先跳过前 100000000 行，性能较差；LIMIT 10 只需要获取前 10 行，性能较好。

  • MySQL 中如何解决深度分页的问题？

    • 使用覆盖索引：减少回表操作。
    • 使用键值对存储：将分页数据预先存储在键值对中。
    • 使用临时表：将分页数据存储在临时表中，减少查询次数。

其他问题

  • 什么是数据库的视图？

    • 视图是一个虚拟表，基于 SQL 语句的结果集。视图不存储实际数据，而是存储查询逻辑。

  • 什么是数据库的游标？

    • 游标用于从数据库中逐行检索数据，通常用于处理大量数据或需要逐行处理的场景。

  • 相比于 Oracle，MySQL 的优势有哪些？

    • 开源免费：MySQL 是开源软件，成本较低。
    • 轻量级：MySQL 资源占用较少，启动速度快。
    • 社区活跃：MySQL 社区活跃，支持和资源丰富。
    • 易用性：MySQL 的安装和配置较为简单，易于上手。

  • 为什么阿里巴巴的 Java 手册不推荐使用存储过程？

    • 可移植性差：存储过程依赖于特定的数据库，难以跨数据库移植。
    • 调试困难：存储过程的调试和维护较为复杂。
    • 影响可读性：业务逻辑分散在数据库和应用层，影响代码的可读性和可维护性。

MySQL数据类型

  • MySQL 中 int(11) 的 11 表示什么？

    • 显示宽度：11 表示显示宽度，不影响存储范围。主要用于 ZEROFILL 属性。

  • MySQL 中 varchar 和 char 有什么区别？

    • 存储方式：CHAR 固定长度，VARCHAR 可变长度。
    • 存储空间：CHAR 预留固定空间，VARCHAR 只占用实际字符数加1字节（长度）。
    • 性能：CHAR 插入和更新速度较快，VARCHAR 存储空间利用率更高。

  • MySQL 中 TEXT 类型最大可以存储多长的文本？

    • TEXT：65,535 字节（约 64 KB）
    • MEDIUMTEXT：16,777,215 字节（约 16 MB）
    • LONGTEXT：4,294,967,295 字节（约 4 GB）

  • 在 MySQL 中存储金额数据，应该使用什么数据类型？

    • DECIMAL：用于存储精确的数值，避免浮点数精度丢失问题。

  • 不推荐在 MySQL 中直接存储图片、音频、视频等大容量内容的原因是什么？

    • 性能问题：大文件存储和读取会影响数据库性能。
    • 备份和恢复：大文件备份和恢复耗时较长，影响维护效率。
    • 存储成本：数据库存储大文件会占用大量磁盘空间。

  • MySQL 中 VARCHAR(100) 和 VARCHAR(10) 的区别是什么？

    • 存储空间：VARCHAR(100) 最多存储 100 个字符，VARCHAR(10) 最多存储 10 个字符。
    • 显示宽度：不影响实际存储，仅用于显示宽度。

  • MySQL 中 DATETIME 和 TIMESTAMP 类型的区别是什么？

    • 存储范围：DATETIME 范围从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'，TIMESTAMP 范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。
    • 存储空间：DATETIME 占用 8 字节，TIMESTAMP 占用 4 字节。
    • 时区：TIMESTAMP 自动转换为当前时区，DATETIME 不自动转换。

三大范式与表设计

  • MySQL 在设计表（建表）时需要注意什么？

    • 范式化：遵循范式化原则，减少数据冗余。
    • 索引设计：合理设计索引，提高查询性能。
    • 数据类型：选择合适的数据类型，节省存储空间。
    • 字段命名：使用有意义的字段名，提高代码可读性。
    • 约束：使用主键、外键等约束，确保数据完整性。

  • 数据库的三大范式是什么？

    • 第一范式（1NF）：确保每个列都是原子的，不可再分。
    • 第二范式（2NF）：在满足 1NF 的基础上，消除非主属性对主键的部分依赖。
    • 第三范式（3NF）：在满足 2NF 的基础上，消除非主属性对主键的传递依赖。

  • 什么是数据库的逻辑外键？数据库的物理外键和逻辑外键各有什么优缺点？

    • 物理外键：在数据库中定义的外键约束，确保数据完整性。
      • 优点：强制数据完整性，防止孤儿记录。
      • 缺点：影响插入和更新性能。
    • 逻辑外键：在应用程序中实现的外键关系，不依赖于数据库约束。
      • 优点：灵活性高，不影响数据库性能。
      • 缺点：需要手动维护，容易出错。

MySQL并发控制

并发问题

  • 数据库的脏读、不可重复读和幻读分别是什么？
    • 脏读：读取了未提交的事务数据。
    • 不可重复读：在同一事务中多次读取同一数据，结果不一致。
    • 幻读：在同一事务中多次执行同一查询，结果集发生变化。

事务与隔离级别

  • MySQL 中的事务隔离级别有哪些？

    • READ UNCOMMITTED：最低隔离级别，允许脏读。
    • READ COMMITTED：允许不可重复读，但不允许脏读。
    • REPEATABLE READ：默认隔离级别，允许幻读，但不允许脏读和不可重复读。
    • SERIALIZABLE：最高隔离级别，完全隔离事务，防止所有并发问题。

  • MySQL 默认的事务隔离级别是什么？为什么选择这个级别？

    • 默认级别：REPEATABLE READ
    • 原因：提供了较高的并发性能，同时防止了脏读和不可重复读。

  • 你们生产环境的 MySQL 中使用了什么事务隔离级别？为什么？

    • 生产环境：通常使用 REPEATABLE READ
    • 原因：平衡了并发性能和数据一致性，满足大多数应用场景的需求。

  • MySQL 是如何实现事务的？

    • 日志机制：通过重做日志（Redo Log）和回滚日志（Undo Log）确保事务的持久性和回滚功能。
    • 两阶段提交：确保事务的原子性和一致性。

  • MySQL 中长事务可能会导致哪些问题？

    • 锁定资源：长时间持有锁，影响其他事务的执行。
    • 日志堆积：重做日志和回滚日志堆积，影响性能。
    • 死锁：增加死锁的风险。

  • MySQL 事务的二阶段提交是什么？

    • 准备阶段：事务协调者通知所有参与者准备提交事务。
    • 提交阶段：事务协调者通知所有参与者提交事务，或回滚事务。

MVCC实现原理

  • MySQL 中的 MVCC 是什么？

    • 多版本并发控制：通过保存数据的多个版本，允许多个事务并发读取，同时保持数据一致性。

  • 如果 MySQL 中没有 MVCC，会有什么影响？

    • 并发性能下降：事务需要等待其他事务完成，影响并发性能。
    • 数据一致性问题：容易出现脏读、不可重复读和幻读等问题。

MySQL日志

  • 什么是 Write-Ahead Logging (WAL) 技术？它的优点是什么？MySQL 中是否用到了 WAL？
    • WAL：在数据写入磁盘前，先将日志写入磁盘，确保数据的一致性和持久性。
    • 优点：提高了数据的安全性和恢复能力。
    • MySQL：InnoDB 存储引擎使用了 WAL 技术。

MySQL中的锁

  • MySQL 中有哪些锁类型？

    • 共享锁（S 锁）：允许多个事务同时读取数据。
    • 排他锁（X 锁）：独占锁，不允许其他事务读取或写入数据。
    • 意向锁：用于表示事务对数据的意图，如意向共享锁（IS 锁）、意向排他锁（IX 锁）。

  • MySQL 的乐观锁和悲观锁是什么？

    • 乐观锁：假设冲突不经常发生，不加锁，通过版本号或时间戳检查冲突。
    • 悲观锁：假设冲突经常发生，加锁保护数据。

  • MySQL 中如果发生死锁应该如何解决？

    • 超时设置：设置事务超时时间，自动回滚超时事务。
    • 死锁检测：启用死锁检测，自动回滚其中一个事务。
    • 优化查询：减少事务的持有锁时间，避免复杂的嵌套事务。

索引及性能优化

存储引擎

  • MySQL 的存储引擎有哪些？它们之间有什么区别？

    • InnoDB：支持事务，行级锁，适合高并发写操作。
    • MyISAM：不支持事务，表级锁，适合读多写少的场景。
    • Memory：数据存储在内存中，速度快但不持久。
    • Archive：压缩存储，适合归档数据。
    • CSV：数据存储在 CSV 文件中，适合数据交换。
    • Blackhole：数据写入后丢弃，用于日志记录。
    • Federated：访问远程 MySQL 服务器上的表。
    • NDB Cluster：分布式存储，高可用性和负载均衡。

  • MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

    聚簇索引：数据行按索引顺序存储，叶子节点存储完整数据行，适用于主键查询和范围查询。

    非聚簇索引：数据行按聚簇索引顺序存储，叶子节点存储索引列值和聚簇索引键，适用于非主键列查询。

索引及其使用

MySQL索引及其使用

MySQL 的索引类型有哪些？

  • 从数据结构角度来看，MySQL 索引可以分为以下几类：
    B+树索引，哈希索引，倒排索引(Full-Text)，R-树索引(多维空间树)
  • 从常见的基于 InnoDB B+树索引角度来看，可以分为：
    聚簇索引(Clustered Index)，非聚簇索引(Non-clustered Index)
  • 从索引性质的角度来看，可以分为：
    普通索引(二级索引、辅助索引)，主键索引，唯一索引，联合索引，全文索引，空间索引

在 MySQL 中建索引时需要注意哪些事项？

  1. 避免过度索引：索引会占用磁盘空间，并增加插入、更新和删除操作的开销。
  2. 选择性高的列优先建索引： 重复值较多的列（如性别）通常不适合建索引。
  3. 避免在长字段上建索引：长字段（如 TEXT、LONGTEXT）会占用大量内存，可能降低整体性能。
  4. 考虑表的修改频率： 如果表的修改频率远大于查询频率，应慎重考虑是否建索引，因为索引会减慢修改操作。
  5. 频繁作为查询条件的列建索引：经常出现在 WHERE 子句中的列应建索引，以提高查询效率。 多个条件经常一起查询时，可以考虑联合索引，减少索引数量。
  6. 对 ORDER BY、GROUP BY、DISTINCT 的列建索引： 这些操作通常需要对结果进行排序、分组或去重，索引可以显著提高这些操作的性能。

MySQL 中使用索引一定有效吗？如何排查索引效果？

  • 不一定有效：索引可能因选择性低、数据分布不均等原因失效。
  • 排查方法：使用 EXPLAIN 语句查看查询计划，检查索引是否被使用。

MySQL 中的索引数量是否越多越好？为什么？

  • 不是越多越好：索引会占用磁盘空间，增加插入、更新和删除操作的开销。
  • 合理创建：根据实际查询需求，选择合适的列创建索引。

回表及索引覆盖

最左前缀原则: 指的是在组合索引中，查询条件必须从索引的最左边的列开始匹配，才能有效利用索引。

回表（Table Lookup） 是指在使用非聚集索引（Secondary Index）进行查询时，数据库引擎需要先通过非聚集索引找到数据行的指针，然后再访问实际的数据行的过程(即需要再返回到主键索引树查询一次)。如何避免回表：

  • 尽量避免写 select * ，仅查询需要的字段，如果这些字段均包含在索引字段内且符合最左前缀原则，就可以不用回表了

  • 合理设计索引，可以考虑使用联合索引

  • MySQL 的索引下推是什么？
    索引下推（Index Condition Pushdown，ICP）是指通过在索引扫描过程中提前过滤掉不符合条件的记录，减少了回表操作的次数，从而提高了查询性能。

B+树

  • MySQL 的 B+ 树中查询数据的全过程是什么？

    • 根节点：从根节点开始，根据查询条件找到相应的子节点。
    • 中间节点：继续向下查找，直到找到叶子节点。
    • 叶子节点：在叶子节点中找到具体的记录，返回结果。

  • 为什么 MySQL 选择使用 B+ 树作为索引结构？

    • 高效查询：B+ 树支持快速的范围查询和排序。
    • 内存友好：B+ 树的中间节点只存储键值，叶子节点存储完整数据，适合内存缓存。

  • MySQL 三层 B+ 树能存多少数据？

    • 估算：假设每个节点存储 1000 个键值，三层 B+ 树可以存储约 1000 * 1000 * 1000 = 10^9 条记录。

查询和执行过程

  • 详细描述一条 SQL 语句在 MySQL 中的执行过程。

    • 连接管理：验证客户端的连接信息，建立连接。
    • 解析：将 SQL 语句转换为内部的解析树（Parse Tree），检查语法错误。
    • 预处理：解析表名、列名等标识符，检查权限。
    • 优化：查询优化器根据解析树生成最优的查询执行计划。
    • 执行：执行器根据优化器生成的执行计划，实际执行 SQL 语句。

  • 为什么在 MySQL 中不推荐使用多表 JOIN？

    • 性能问题：多表 JOIN 会导致大量的表扫描和数据比较，影响查询性能。
    • 维护难度：复杂的 JOIN 语句难以维护和调试。

执行计划与SQL调优

查询执行顺序：from - where -group by -having - select - order by-limit

  • MySQL 数据库的性能优化方法有哪些？

    • 索引优化：合理创建和使用索引，减少 I/O 操作。
    • 查询优化：使用 EXPLAIN 语句分析查询计划，优化查询逻辑。
    • 缓存优化：合理使用查询缓存和缓存策略。
    • 硬件优化：增加内存、使用更快的磁盘等。
    • 分区优化：对大表进行分区，提高查询性能。

  • MySQL 中如何进行 SQL 调优？

    • 使用 EXPLAIN：分析查询计划，找出瓶颈。
    • 优化索引：创建合适的索引，减少回表操作。
    • 简化查询：拆分复杂的查询，减少表扫描。
    • 批量操作：使用批量插入和更新，减少 I/O 操作。

SQL调优

定位问题

线上日志，其他数据库工具如MYSQL中的慢查询日志

查看执行计划

SQL本身问题 / 加索引

  • 避免回表

  • 避免在 where 子句中使用or来连接条件

  • 应考虑在 where 及 order by 涉及的列上建立索引，联表查询的条件

  • 使用联合索引

复杂SQL优化

SQL本身联表过多，且使用了函数和非索引字段排序

这种通常需要在业务层分解业务，重新实现功能， 对于查询条件相对固定的，且实时性要求不高的，可以考虑做定时任务，缓存结果

分库分表

  1. 冷热分离
  2. 正式的分库分表

  • 如何在 MySQL 中监控和优化慢 SQL？

    • 慢查询日志：启用慢查询日志，记录执行时间较长的查询。
    • 性能分析工具：使用 EXPLAIN 和 SHOW PROFILES 等工具分析查询性能。
    • 优化查询：根据分析结果优化查询逻辑和索引。

  • 如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

    • 语法：EXPLAIN SELECT ...
    • 输出：查看 EXPLAIN 输出，重点关注 type、possible_keys、key、rows 等列，分析查询计划。
    • 优化：根据分析结果优化查询逻辑和索引。

  • MySQL 的查询优化器如何选择执行计划？

    • 统计信息：查询优化器根据表的统计信息（如行数、列分布等）选择最优的执行计划。
    • 代价模型：评估不同执行计划的代价，选择代价最小的计划。

MySQL Buffer

  • 从 MySQL 获取数据，是从磁盘读取的吗？

从 MySQL 获取数据时，首先会检查缓冲池中是否有所需的数据页。如果有，直接从内存中读取；如果没有，则从磁盘读取数据页到缓冲池中，然后再从缓冲池中读取数据。(从 MySQL 8.0 开始，查询缓存已经被移除)

Buffer

MySQL 的 Change Buffer 是什么？它有什么作用？

Change Buffer 是一种优化机制，通过延迟和批量处理（二级）索引变更，减少了磁盘 I/O 次数

MySQL 的 Doublewrite Buffer 是什么？它有什么作用？

Doublewrite Buffer 是一种重要的数据保护机制，通过确保数据页的完整性和一致性，防止部分写失败导致的数据损坏

MySQL 的 Log Buffer 是什么？它有什么作用？

Log Buffer 是 InnoDB 存储引擎中用于暂存重做日志的一个内存缓冲区，通过减少磁盘 I/O 次数和提高写入性能，确保了数据的一致性和完整性，同时也提高了系统的整体性能和并发能力

MySQL高可用和扩展

  • 如何在 MySQL 中避免单点故障？

    • 主从复制：使用主从复制，确保数据冗余。
    • 负载均衡：使用负载均衡器分担读写压力。
    • 故障切换：配置自动故障切换机制，确保高可用性。

  • 如何实现数据库的不停服迁移？

    • 在线迁移：使用在线迁移工具（如 pt-online-schema-change）进行无停机迁移。
    • 双写方案：在新旧数据库中同时写入数据，逐步切换流量。

主从复制

  • 如何在 MySQL 中实现读写分离？

    • 代理工具：使用代理工具（如 ProxySQL、MaxScale）自动路由读写请求。
    • 应用层：在应用程序中手动分发读写请求。

  • 什么是 MySQL 的主从同步机制？它是如何实现的？

    • 主从同步：主库将事务日志（Binlog）发送给从库，从库应用日志进行数据同步。
    • 实现：主库开启 Binlog，从库配置主库地址，从库通过 IO 线程读取 Binlog，通过 SQL 线程应用日志。

  • 如何处理 MySQL 的主从同步延迟？

    • 优化网络：改善网络环境，减少传输延迟。
    • 优化 Binlog：减少 Binlog 的大小，提高传输效率。
    • 并行复制：启用并行复制，提高同步速度。
    • 监控和报警：定期监控同步延迟，设置报警机制。

分库分表

  • 什么是分库分表？分库分表有哪些类型（或策略）？

    • 分库分表：将数据分散到多个数据库和表中，提高性能和可扩展性。
    • 类型：水平分表（按行分表）、垂直分表（按列分表）、混合分表。

  • 如果组长要求你主导项目中的分库分表，大致的实施流程是？

    • 需求分析：确定分库分表的目标和需求。
    • 方案设计：选择合适的分库分表策略，设计分库分表方案。
    • 工具选型：选择合适的分库分表工具（如 ShardingSphere）。
    • 数据迁移：进行数据迁移，确保数据一致性和完整性。
    • 测试验证：进行全面的测试，验证分库分表的效果。
    • 上线监控：上线后持续监控性能和稳定性。

  • 对数据库进行分库分表可能会引发哪些问题？

    • 数据一致性：跨库事务处理复杂，容易出现数据不一致。
    • 查询复杂度：跨库查询和联表查询变得复杂。
    • 维护难度：数据库结构复杂，维护和管理难度增加。
    • 性能瓶颈：不当的分库分表策略可能导致新的性能瓶颈。
Default footer
Copyright © 2025 ventixy