MySQL

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 引擎中的聚簇索引和非聚簇索引有什么区别？

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

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

索引及其使用

回表及索引覆盖

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

回表（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 操作。

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

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

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

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

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

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

MySQL Buffer

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

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

MySQL高可用和扩展

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

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

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

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

主从复制

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

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

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

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

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

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

分库分表

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

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

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

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

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

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