fundamentals.md

·interview-questions

MySQL 基础

执行一条 select 语句，期间发生了什么？一条 SQL 查询语句是如何执行的？

MySQL 的架构分为 Server 层和存储引擎层两部分，Server 层负责建立连接、分析和执行 SQL，存储引擎层负责数据的存储和提取

MySQL 是基于 TCP 进行传输的，要先连接 MySQL 服务才能执行 SQL 语句，可以用 show processlist; 命令来查看当前 MySQL 服务被多少个客户端连接了，以及连接的状态，空闲连接是有最大空闲时长的（wait_timeout），最大连接数也有限制（max_connections），可以用 kill connection + id; 来断开连接，用 show variables like 'wait_timeout'; 和 show variables like 'max_connections'; 来查看当前的配置

MySQL 的连接跟 HTTP 一样也有短连接和长连接两种模式，短连接是每次请求都建立和断开连接，长连接是建立连接后可以复用，减少了频繁建立连接的开销，但长连接会占用内存，我们可以定期断开长连接，或者客户端主动重置连接，来释放内存

建立连接之后就可以向 MySQL 服务发送 SQL 语句了，下面才是真正的执行过程：

  1. MySQL 8.0 之前：先解析 SQL 语句的第一个字段看一下是什么类型的语句，如果是查询语句就会先去查询缓存（以键值对类型保存在内存中）里查找缓存数据，如果命中就会直接返回值，如果没有才会继续往下执行
    • 由于对于更新比较频繁的表，查询缓存的命中率太低（只要有一个表有更新，这个表的所有查询缓存就会被清空），所以 MySQL 8.0 就移除了查询缓存
  2. 解析 SQL：
    1. MySQL 会先用解析器对 SQL 语句进行词法分析和语法分析
    2. 词法分析会识别关键字，如 select 和 from
    3. 语法分析会根据词法分析的结果和语法规则判断输入的 SQL 语句是否满足 SQL 语法，如果有问题就会报错，如果没问题就会构建 SQL 语法树，方便后续模块获取 SQL 类型、表名、字段名和 where 条件等信息
  3. 执行 SQL：（select —— 查询语句）
    1. 先进入预处理阶段，预处理器会检查 SQL 查询语句中的表或字段是否存在，并将 select * 中的 * 扩展为表上的所有列
    2. 然后是优化阶段，优化器会负责将 SQL 查询语句的执行方案确定下来，比如要使用哪个索引，可以在 SQL 前面加一个 explain 来查看执行计划
    3. 最后是执行阶段，执行器会跟存储引擎交互，真正执行语句

sql-process

主键、索引和外键的区别是什么？

主键是一列，它的值可以唯一标识表中的每一行数据，每个表只能有一个主键，并且主键的值不能重复也不能为空，通常用于保证数据的唯一性和在表中查找特定的行

索引没有重复值，但可以有一个空值，主要是用于快速查询到数据

外键是一个表中的一个字段，它的值是另一个表的主键，用于建立两个表之间的关系

优化器如何决定使用某个索引？

本质这是一个基于成本的决策，它会根据统计信息估算一下，在当前查询语句下，使用某个索引的选择性如何、需要扫描多少索引页和数据页、是否需要回表、能不能顺带解决排序/分组/limit 等，然后把这些成本和全表扫描、其他索引方案做对比，选择成本最低的方案执行

选择性高、组合顺序匹配 where/order by/group by/limit 的覆盖索引在大表上更有可能被优化器优先考虑，而选择性差、被函数包裹、表本身比较小的情况下，即使有索引也可能被忽略

如何分析一条慢 SQL 的执行瓶颈？

分析一条慢 SQL，我会先通过慢日志确认它到底慢在什么维度，看常用的 Query_time、Lock_time、Rows_examined、Rows_sent 等指标，来大致判断是扫描行数很大（索引/执行计划问题）、锁等待严重，还是排序/分组耗时

然后用 EXPLAIN/EXPLAIN ANALYZE 看执行计划和真实耗时，是全表扫描、错误索引、回表太多，还是临时表、额外排序或 join 过多

最后再结合表的统计信息和索引设计，从减少扫描行、减少随机 I/O、减少排序/临时表、减少锁等待这四个方向去针对性优化 SQL 或索引结构，而不是只盯着一个字段加不加索引

数据库的慢查询要怎么优化？

先通过慢查询日志定位到慢查询语句，查看 Query_time / Lock_time / Rows_examined / Rows_sent 等参数，然后用 EXPLAIN 分析执行计划，从而针对不同问题采取不同的优化措施

扫描行数太多（Rows_examined 高）：

  • 索引没有覆盖到查询条件的 where/join
    • 可能只覆盖了某一个字段，导致没有被覆盖到的字段只能在回表后再过滤
    • 可以建立复合索引，where 的顺序不重要，索引列的顺序才重要，高选择性的列要放在前面
    • 也可以使用覆盖索引，即索引包含查询所需的所有列，避免回表，查询时也避免使用 select *，只查询需要的列
  • 在使用 where 时使用了函数、计算或前导 %，导致索引失效
    • 例如：
    • WHERE DATE(created_at) = '2025-01-01'
    • WHERE age + 1 = 18
    • WHERE name LIKE '%abc'
    • 优化后：
    • WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'
    • WHERE age = 17
    • WHERE name LIKE 'abc%'

排序/聚合/临时表耗时高：

  • ORDER BY / GROUP BY 没有复用索引顺序
    • 排序分组时列的顺序要和索引列的顺序一致（或前缀一致），让 ORDER BY / GROUP BY 尽量走索引顺序
    • 避免同时 ORDER BY 不同方向或多个不连续列
    • 当看到 EXPLAIN 结果中 Extra 列有 Using temporary 或 Using filesort 字样时，就尝试调整为
      • 建立合适的联合索引
      • 减少一次性排序的数据量

锁等待：

  • 行级锁范围过大
    • InnoDB 的行级锁是基于索引范围加的，如果 where 的字段没有合适的索引，就会退化成表锁，
    • 要在 where 的条件列上建立合适的索引，让锁范围变成小段索引，避免全表扫描
  • 控制事务大小
    • 应该先准备好数据，再开启事务，避免长事务占用锁资源
    • 可以拆成多个小事务，减少锁持有时间
  • 热点记录
    • 用缓存/队列累计后再批量更新，避免频繁更新同一行数据
    • 用分片计数，多行多库累加，最后汇总，避免单行热点
    • 把写入很频繁的字段拆出去，和大表主体分离

如果是表本身很大，可以分表，比如只保留最近几个月的数据在主表，旧数据定期迁移到历史表，也可以分区，比如按时间范围分区，让大部分查询只扫描少量分区，另外还可以把读多写少的查询放到 redis 缓存中

文件排序 filesort 是什么？

filesort 是 MySQL为了满足 ORDER BY 或 GROUP BY 语句的排序需求，在不能直接利用索引顺序时，启动的一套额外排序算法的统称，只要在 EXPLAIN 结果中看到 Extra 列有 filesort 字样，就表示这次排序没能完全靠索引完成

它不一定代表写磁盘，如果 ORDER BY 的列不在索引里/顺序不匹配，或者使用了表达式/函数，就会触发 filesort

MySQL 会先把参与排序的列和定位整行需要的信息读取出来，放到一个排序缓冲区（sort buffer）中，再在 sort buffer 中按照 ORDER BY 规则排序，如果 buffer 足够大，就会在内存里完成，如果不够大，就会分块排序并写临时文件，再归并，排完序之后再去按排序后的顺序取出整行数据返回

如果是数据量很小的排序，即使有 filesort，开销也是非常可控的，只有数据量非常大时才有可能是性能瓶颈

我们可以直接让 ORDER BY 能复用某个 B+ 树索引的顺序，针对常用的 WHERE + ORDER BY 组合设计联合索引，并让索引列顺序与条件和排序匹配，或者减少参与排序的数据量，比如加时间/条件过滤，从而避免 filesort，或者让排序尽量在内存中完成，而不是频繁落盘

SQL 调优你了解吗，比如说你调试的一些 SQL 语句怎么验证它是否命中了一些索引，以及它的性能是否是最优，该通过什么样的方式去验证？

先用慢日志/监控定位是哪条 SQL 有问题，然后用 EXPLAIN/EXPLAIN ANALYZE 来查看执行计划，确认是否命中了预期的索引，扫描行数是否合理，有没有临时表和 filesort 等额外开销，再在接近真实数据量的环境下，对比不同写法/索引设计下的执行时间和扫描行数来判断哪个方案更优

我们可以用 EXPLAIN 来查看 SQL 语句的执行计划，关注下面字段：

  • type
    • 访问类型，判断有没有走索引、走得好不好
    • 大致优劣顺序：ALL（全表扫描） < index（全索引扫描） < range < ref < eq_ref < const
  • key / ken_len
    • key 显示实际使用的是哪一个索引
    • key_len 可以看出用了联合索引的前几列
  • rows
    • 估算需要扫描的行数，越少越好
  • Extra
    • 额外信息，关注有没有下列几个：
    • Using index（覆盖索引）
    • Using where（有过滤条件）
    • Using temporary（使用临时表）
    • Using filesort（使用文件排序）

在 MySQL 8.0 后，还可以用 EXPLAIN ANALYZE 来查看 SQL 的实际执行时间和扫描行数，定位是哪个步骤耗时最多，比如是全表扫描、某个二级索引的回表过多，还是临时表 + filesort 的耗时多，然后再做针对性优化

还可以用原始的 SQL / 索引和优化后的 SQL / 索引在接近真实数据量的环境下多次对比执行时间和扫描行数（Rows_examined），来验证优化效果

MySQL 跟性能有关的参数你知道哪些？比如说你自己在安装 MySQL 的时候会去做哪些配置，比如说它的默认配置你会去关注哪些参数，你会去改什么？

我一般不会乱改 MySQL 的所有参数，我一般是会先确认当前机器是专用数据库机还是和应用混一起，然后再根据业务场景、内存、磁盘等信息去调整一些关键参数，比如 InnoDB 的 Buffer Pool 大小、redo / binlog 的刷盘策略、连接和线程上限、临时表和排序缓冲区以及慢查询日志等

  • 内存 / InnoDB 相关
    • innodb_buffer_pool_size
      • 是 InnoDB 的页缓存，缓存索引和数据页，命中率越高，磁盘 I/O 就越少
      • 一般设置为物理内存的 60%~80%
      • 会优先调整 innodb_buffer_pool_size，确保热点数据能尽量放进 buffer pool
    • innodb_buffer_pool_instances
      • buffer pool 的实例数，buffer pool 被划分成多个实例，减少并发访问时的锁竞争
      • 一般设置为 1~8 个实例，具体看 buffer pool 大小和并发量
      • 要避免实例数过多导致每个实例太小，影响缓存命中率
    • tmp_table_size / max_heap_table_size
      • 内存临时表的最大大小，超过这个大小就会写到磁盘上，影响性能
      • 当 Created_tmp_disk_tables 很高时，说明很多临时表在磁盘，可以考虑适当调大 tmp_table_size 和 max_heap_table_size，让更多的临时表留在内存
      • 但这两个参数是每个连接的上限，要结合连接数控制
  • 日志 / IO / 持久化策略相关
    • innodb_log_file_size / innodb_log_files_in_group
      • 是 InnoDB redo log 的文件大小和数量，影响 checkpoint 频率和崩溃恢复时间
      • 一般设置为 256 MB ~ 1 GB，数量一般是 2 个
      • 要避免日志文件过小导致频繁刷盘（checkpoint 太频繁），影响写入性能
    • innodb_flush_log_at_trx_commit
      • 控制事务提交时 redo log 的刷盘策略
      • 设置为 1 时（默认）：每次提交都会写入和刷盘（fsync），最安全但性能最低
      • 设置为 2 时：每次提交只写入操作系统缓存，每秒刷盘一次，性能较好但有可能丢失 1 秒内的事务
      • 设置为 0 时：每秒钟写入和刷盘一次，性能最高但数据丢失风险最大
      • 线上主库一般会保持默认值 1 确保事务持久性
      • 如果是对数据丢失容忍度较高的场景，可以考虑设置为 2，如日志型库
    • sync_binlog
      • 控制二进制日志的刷盘频率
      • 设置为 1 时：每次写入二进制日志都会刷盘，一般配合主库强一致
      • 设置为 0 时：完全依赖操作系统的缓存和刷盘策略，不强制刷盘，但有丢日志风险
      • 为了主库可靠性，一般 innodb_flush_log_at_trx_commit=1 配合 sync_binlog=1
      • 如果对性能特别敏感但能接受极小概率丢失，可以降低 sync_binlog 的刷盘频率
    • innodb_flush_method
      • 控制 InnoDB 的 I/O 刷盘方式
      • 常见配置：O_DIRECT，避免 OS 缓存和 InnoDB 缓存双重缓存，减少内存浪费
    • innodb_io_capacity / innodb_io_capacity_max
      • 告诉 InnoDB 底层磁盘大概的处理能力，影响后台刷脏页速度
  • 连接 / 线程相关
    • max_connections
      • 允许的最大连接数，避免连接数过多导致内存耗尽
      • 根据应用层的连接池配置和峰值并发来估算一个合理范围，比如 500~2000，然后配合线程池/连接池控制
    • thread_cache_size
      • 缓存一定数量的线程，减少频繁创建销毁线程的开销
      • 一般设置为 8~64，根据并发量调整
      • 可以看 Threads_created 是否不断快速增加，如果频繁创建，就适当增大 thread_cache_size
  • 排序 / JOIN / 读缓冲相关（这类参数每个连接都会分配，要注意内存总量）
    • sort_buffer_size
      • ORDER BY 的排序缓冲区大小
      • 应该根据实际排序需求微调，但优先还是考虑索引优化
    • join_buffer_size
      • 无法利用索引时的 JOIN 操作的缓冲区大小
      • 如果 Select_full_join 很高并且有大量无索引 JOIN，这个缓冲会被大量用到
    • read_buffer_size / read_rnd_buffer_size
      • 顺序读和随机读的缓冲区大小
  • 查询 / 慢日志相关
    • 安装后会先开启 slow_query_log
    • 再把 long_query_time 从默认的 10 秒设置为 1 秒
    • 调试时会临时开 log_queries_not_using_indexes 看全表扫描的语句
    • 从而方便定位慢 SQL
    • performance_schema 是默认开启的，对定位锁等待、热点表等问题很有帮助

使用 MySQL 时有遇到过什么性能方面的问题吗？

大表慢查询：

有一条 SQL 执行时间特别长，慢查询日志里经常能看到，用 EXPLAIN 看执行计划，发现它是全表扫描，Rows_examined 很高，Extra 里还有 Using temporary 和 Using filesort，说明排序和临时表开销也很大

查看表结构之后发现这个表只有主键索引和单列索引，所以我增加了联合索引，直接用 B+ 树的顺序来满足这个 SQL 的过滤和排序条件，并且只查询真正需要的列，再让这个联合索引尽可能成为覆盖索引，避免回表

更新热点行导致的行级锁竞争：

在做计数或者状态更新时，我把一些全局计数放在了同一行中，这在高并发写入的场景下会导致严重的行级锁竞争，EXPLAIN 中能看到 Lock_time 很高，虽然这个列是有索引的，确实是行级锁，但所有请求都在抢同一行，高并发下就变成了排队更新

为了解决这个问题，我把这个全局计数拆成了多个分片，比如按照 hash(user_id) % N 来更新不同的行，读的时候再把这 N 个分配的值加起来，或者用缓存/队列来累积更新，减少对同一行的频繁写入，另外还保证这个更新是在一个小事务里完成，不在同一个事务里做额外的复杂逻辑，从而减少锁持有时间

临时表和文件排序落盘导致的性能抖动：

有一个后台报表 SQL，它会做复杂的 GROUP BY 和 ORDER BY 操作，在数据量上来了之后，每天固定时间跑这个报表时，数据库 I/O 和 CPU 就都会明显飙高，影响到其他 SQL 业务，EXPLAIN 里能看到 Using temporary 和 Using filesort，SHOW GLOBAL STATUS 里 Created_tmp_disk_tables 也很高，说明大量临时表和排序都落盘了

这时除了为 GROUP BY 和 ORDER BY 的列设计更合适的联合索引、让 SQL 按时间分区查询之外，我还会考虑把这个报表 SQL 放到从库去跑，避免影响主库的业务，或者用预计算的方式，把一些中间结果提前计算好存到一个专门的报表表里，报表查询时直接读这个表，避免复杂计算，或者在评估内存的前提下，适当调大 tmp_table_size 和 max_heap_table_size，让更多的临时表留在内存中完成

总结：

在遇到 MySQL 的性能问题时，一般我都是先通过慢查询日志和监控定位到具体 SQL，然后结合 EXPLAIN / EXPLAIN ANALYZE、Rows_examined、Lock_time 等指标，分析是索引设计不合理/执行计划问题、锁竞争严重，还是排序/临时表开销大，再优先从索引设计和 SQL 写法入手优化，必要时才通过表结构设计、事务粒度以及个别参数调整来调优，最后用压测、监控和对比验证优化效果

MySQL 一行记录是怎么存储的？

MySQL 目前默认使用的是 InnoDB 存储引擎，它的数据是保存在磁盘的 /var/lib/mysql/ 目录下，每个数据库对应一个文件夹，每个表对应一对 .frm 和 .ibd 文件，.frm 文件保存表的结构信息，.ibd 文件保存表的数据和索引信息

表空间是由段、区、页、行（segment、extent、page、row）组成的，数据库表中的的记录都是按行进行存放的，每行记录根据不同的行格式有不同的存储结构

记录是按照行来存储的，但 InnoDB 是按页为单位来读写的，每个页默认的大小是 16 KB，页是 InnoDB 存储引擎磁盘管理的最小单元

在表中数据量大时，为某个索引分配空间时就不会以页为单位来分配了，而是以区为单位来分配，每个区包含 64 个连续的页，大小是 1 MB，这样对于 16 KB 的页来说就能使相邻页的物理位置页相邻，能使用顺序 I/O 从而提高读写性能

多个区组成段，表空间就是由多个段组成的，不同类型的数据会存放在不同的段中，比如说数据段，存放 B+ 树叶子节点的区，索引段，存放 B+ 树非叶子节点的区，回滚段，存放回滚数据的区

tablespace

InnoDB 提供 4 种行格式，分别是 Compact、Redundant、Dynamic 和 Compressed，其中 Redundant 由于太古老已经没有什么人用了，它并不是一种紧凑的行格式，而剩下的三种都是紧凑的行格式，能让一个数据页中存放更多的行记录

compact-format

一条完整的行记录分为记录的额外信息和记录的真实数据两个部分

  • 记录的额外信息
    • 变长字段长度列表：
      • 存放变长字段的长度信息，会按照列的顺序逆序存放，使位置靠前的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，提高 CPU Cache 的命中率
      • 每个变长字段占用 1 或 2 个字节，具体取决于表中变长字段的总数是否超过 255 个，如果超过了就需要 2 个字节（字符集是 ASCII 的话，每个字符占 1 个字节）
      • NULL 是不会存放在行格式中记录的真实数据中的，所以变长字段长度列表中也不会存放 NULL 值的变长字段的长度信息，当数据表没有变长字段时，变长字段长度列表就不会存在了
    • NULL 值列表：
      • 存放哪些字段是 NULL 的信息，每个字段占用 1 位，1 表示 NULL，0 表示非 NULL，也是按照列的顺序逆序存放
      • 当数据表的字段都定义成 NOT NULL 时，NULL 值列表就不会存在了
    • 记录头信息
      • delete_mask：标识该记录是否被删除，执行 delete 删除记录时并不会真正删除记录，只是把这个记录的 delete_mask 标记为 1
      • next_record：下一条记录的位置，记录与记录之间是通过链表组织的，指向的是下一条记录的记录头信息和真实数据之间的位置，这样向左读就是记录头信息，向右读就是真实数据，比较方便
      • record_type：表示当前记录的类型，0 表示普通记录，1 表示 B+ 树非叶子节点记录，2 表示最小记录，3 表示最大记录
  • 记录的真实数据（三个隐藏字段）
    • row_id
      • 如果建表时没有指定主键或唯一约束，InnoDB 会自动生成一个隐藏的 row_id，它并不是必须的，会占用 6 个字节
    • trx_id
      • 事务 id，表示这个数据是由哪个事务生成的，它是必需的，会占用 6 个字节
    • roll_pointer
      • 记录上一个版本的指针，它是必需的，会占用 7 个字节

MySQL 的 NULL 值是怎么存放的？

MySQL 的 Compact 行格式中会用 NULL 值列表来记录值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分

NULL 值列表会占用 1 字节空间，如果表中的字段数超过 8 个的话，每增加 8 个字段就会多占用 1 字节空间，当表中所有字段都定义成 NOT NULL 时，NULL 值列表就不会存在了，从而节省 1 字节的空间

MySQL 怎么知道 varchar(n) 实际占用数据的大小？

MySQL 的 Compact 行格式中会用 变长字段长度列表 来记录变长字段的实际长度

varchar(n) 中 n 最大取值为多少？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的总字节长度不能超过 65535 个字节，即一行的总长度最大为 为 65535 字节

如果字符集是 ASCII 的话，由于变长字段长度列表和 NULL 值列表都会占用字节，所以我们需要保证的是所有字段的长度 + 变长字段长度列表 + NULL 值列表 ≤ 65535 字节

如果是单字段的情况，因为变长字段长度列表允许存储的最大字节数大于 255 字节，所以需要 2 个字节，NULL 值列表需要 1 个字节，所以最大就是 65535 - 2 - 1 = 65532 字节

行溢出后，MySQL 是怎么处理的？

MySQL 中磁盘和内层交互的基本单位是页，InnoDB 存储引擎的默认页大小是 16 KB，一行记录如果超过了 16 KB（即一个页存不了一条记录），就会发生行溢出，多的数据就会存到另外的溢出页中

如果是 Compact 行格式，发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，剩下的数据会存到另外的溢出页中，并且在记录的真实数据处会用 20 字节保存一个指向溢出页的指针，从而找到剩余数据所在页

而如果是 Compressed 或 Dynamic 行格式，它们和 Compact 很像，但它们采用的是完全的行溢出方式，一旦发生行溢出，记录的真实数据处是不会保存任何数据的，所有数据都会存到另外的溢出页中，它们只会在记录的真实数据处用 20 字节保存一个指向溢出页的指针

MySQL 的存储引擎有哪些？

MySQL 当前默认的存储引擎是 InnoDB，支持事务、行级锁和 MVCC，有聚簇索引和崩溃恢复功能，适合大多数场景

早期版本的默认存储引擎是 MyISAM，支持表级锁和全文索引，但不支持事务、外键约束和行级锁，适合读多写少的场景

另外还有 MEMORY 存储引擎，数据存放在内存中，速度非常快，但数据不持久化，适合临时表和缓存表等场景，还有一些特殊用途的引擎，例如 NDB 用于 MySQL Cluster 的分布式场景，ARCHIVE 用于日志归档，CSV 用于 CSV 文件存储，FEDERATED 用于访问远端 MySQL 表，BLACKHOLE 只写入不存储，用于复制链路或测试等

同样是一百万的数据量，读的话，InnoDB 和 MyISAM 哪个更快？

如果是比较理想化的场景，例如单线程、大量顺序读或全表扫描，因为 MyISAM 不支持事务、没有 MVCC，内部结构更简单，开销更小，所以会比 InnoDB 略快一些

但如果是实际的业务场景，比如读写混合，并发访问并且有大量按主键或二级索引随机查单行的请求，这种情况下 InnoDB 的聚簇索引、buffer pool 和行级锁的优势就会体现出来，再考虑到事务、崩溃恢复和一致性等因素，InnoDB 的读并不会比 MyISAM 更慢，甚至整体的吞吐会更好

给你一个用户信息表，你会选择使用哪个存储引擎来存储，为什么？

用户信息表属于核心业务表，是肯定需要持久化的，我会优先选择 InnoDB，用户信息通常对于一致性和可靠性的要求很高，InnoDB 支持完整的事务、崩溃恢复和行级锁，可以保证在注册、修改资料、绑定手机号和重置密码等用户操作中，数据是可靠且一致的

另外，用户表通常会有大量的并发访问，除了读，还会有频繁的写操作，比如登录更新时间、状态字段等，InnoDB 的行级锁和 MVCC 能够更好地支持高并发的读写操作，而不是像 MyISAM 那样使用表级锁堵塞读写

最后，用户表往往会被很多其他业务表通过 user_id 做外键引用，从架构上也更适合统一使用 InnoDB，利用它的外键约束能力，能避免出现孤儿数据，MyISAM 还是更偏向于读多写少的引擎

SQL 中的 JOIN 操作是什么？常见的 JOIN 有哪几种？

JOIN 是指在一条查询中，把多张表按照某个关联条件拼在一起的操作，通常是主键和外键的关联，按照要不要保留未匹配上的行来区分，JOIN 有以下几种：

  • INNER JOIN（内连接）：
    • SELECT u.id, u.name, o.id AS order_id, o.amount FROM user u INNER JOIN `order` o ON u.id = o.user_id;
    • 只保留左右两边都能匹配的行，即交集
  • LEFT JOIN（LEFT OUTER JOIN，左外连接）：
    • SELECT u.id, u.name, o.id AS order_id, o.amount FROM user u LEFT JOIN `order` o ON u.id = o.user_id;
    • 保留左表的所有行，右表匹配不上时用 NULL 填充
  • RIGHT JOIN（RIGHT OUTER JOIN，右外连接）：
    • SELECT u.id, u.name, o.id AS order_id, o.amount FROM user u RIGHT JOIN `order` o ON u.id = o.user_id;
    • 保留右表的所有行，左表匹配不上时用 NULL 填充
    • 实际开发时 RIGHT JOIN 很少用，一般都用 LEFT JOIN 把表顺序调一下
  • FULL JOIN（FULL OUTER JOIN，全外连接）：
    • SELECT u.id, u.name, o.id AS order_id, o.amount FROM user u FULL OUTER JOIN `order` o ON u.id = o.user_id;
    • 保留左右两边的所有行，匹配不上时用 NULL 填充，即并集
    • MySQL 不直接支持 FULL JOIN，可以通过 UNION LEFT JOIN 和 RIGHT JOIN 来模拟
      • SELECT ... FROM A LEFT JOIN B ON ... UNION SELECT ... FROM A RIGHT JOIN B ON ... WHERE A.主键 IS NULL;
  • CROSS JOIN（笛卡尔积连接）：
    • SELECT u.id, u.name, o.id AS order_id, o.amount FROM user u CROSS JOIN `order` o;
    • 等价于：SELECT u.id, u.name, o.id AS order_id, o.amount FROM user u, `order` o;
    • 返回左右两边表的所有组合，即笛卡尔积，通常不加 ON 条件，如果 a 表有 100 行，b 表有 200 行，结果就是 100 * 200 = 20000 行
    • 实际开发中很少用，通常用 INNER JOIN 来代替
  • SELF JOIN（自连接）：
    • SELECT child.id, child.name, parent.name AS parent_name FROM user child LEFT JOIN user parent ON child.parent_id = parent.id;
    • 把同一张表当成两张表来连接，通常用于层级结构的数据查询，比如树形结构（类别、部门、上下级关系）、评论和回复等
    • 其实不算新的 JOIN 类型，只是用法特殊

MySQL 的缓存池是什么？

就是 InnoDB 存储引擎的 Buffer Pool，它是一个内存区域，用来缓存数据页和索引页，相当于是 InnoDB 自己的页缓存，用来减少对磁盘的读写操作，让绝大多数读写操作都优先在 Buffer Pool 里完成，命中率越高，磁盘 I/O 越少，从而提高数据库的性能

缓存池缓存的是按页（一般 16 KB）读取的数据块，除了数据页和索引页以外，也会缓存部分辅助数据结构的页，比如 undo 页、插入缓冲页等

在读数据时，如果命中了缓存就直接返回，如果没命中就从磁盘读到缓存池里，再返回给用户

在写数据时，先把数据写到缓存池里，然后把这页标记为脏页，再由后台线程异步把脏页刷到磁盘上

当空间不够时，InnoDB 会根据 LRU 算法把长时间没访问的冷门页淘汰掉，在淘汰前如果是脏页还会先刷盘，确保数据不丢失

实际开发中，可以通过 innodb_buffer_pool_size 参数来调整缓存池的大小，一般设置为物理内存的 60%~80%，确保热点数据能尽量放进缓存池，从而提升性能，可以通过 innodb_buffer_pool_instances 参数来设置缓存池的实例数，减少并发访问时的锁竞争，另外也可以通过 Innodb_buffer_pool_read_requests（逻辑读）和 Innodb_buffer_pool_reads（物理读）来查看命中率，如果物理读的比例很高就说明缓存池不够大，或者访问模式太离散

虽然操作系统也会缓存文件页，但那是整个文件系统维度的，InnoDB Buffer Pool 则是存储引擎自己管理的缓存，可以做更细粒度的策略

有了解过 SQL 注入等安全问题吗？

SQL 注入就是把用户输入当成 SQL 语句的一部分去拼接执行，导致攻击者可以通过构造特殊的输入，把原本只是数据的内容变成指令，从而绕过认证、越权查询甚至修改、删除数据

例如：

-- 伪代码
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

username = admin
password = ' OR '1'='1

-- 最终拼接成的 SQL 语句
SELECT * FROM users
WHERE username = 'admin'
  AND password = '' OR '1'='1';
-- 由于 '1'='1' 永远为真，导致绕过密码验证

本质原因就是用字符串拼接 SQL 语句，导致用户输入的内容被当成了 SQL 语句的一部分，数据库分不清哪里是数据哪里是指令

可以从代码层、数据库层和运维/框架层来防止 SQL 注入

  • 代码层
    • 使用参数化查询 / Prepared Statement
      • 不使用字符串拼接，而是用占位符和参数绑定
      • 例如：SELECT * FROM users WHERE username = ? AND password = ?
      • Go: db.Query("SELECT ... WHERE username = ? AND password = ?", name, pwd)
      • 这样让数据库明确知道 SQL 模板是什么，让用户输入的只是值，不会被当作 SQL 语法来执行
      • 所有对数据库的访问都走预编译 / ORM 的参数绑定
    • 严格禁止动态拼接敏感部分
      • 不允许前端直接传 orderBy = "xxx; DROP TABLE users;" 这种字符串
      • 如果确实需要动态拼接，比如排序字段，只允许传字段名，然后在后端做白名单校验，确保只能是合法字段
  • 数据库层
    • 最小权限原则
      • 应用连接数据库时只用最小权限的账号，避免有 DROP、DELETE 等高危权限
    • 读写分离
      • 把读请求和写请求分开，读请求走只读账号，避免读请求被注入写操作
  • 运维/框架层
    • 慢查询 / 审计日志
      • 发现异常的 SQL 模式
      • 比如大量 OR 1 = 1，恶意 union select 等
    • 反向代理
      • 使用 WAF（Web 应用防火墙）等中间件
      • 过滤和阻断常见的 SQL 注入攻击模式

解释一下数据库的三大范式？

数据库的三大范式是为了减少数据冗余和避免异常操作而设计的规范，有以下三种：

  • 第一范式：1NF
    • 要求表中的每个字段都是原子性的，即每个字段只能存储一个值，不能是数组、列表等复合类型
    • 例如，不能用一个字段存储多个电话号码，应该拆成多个字段
  • 第二范式：2NF
    • 在满足第一范式的基础上，要求表中的每个非主键字段都完全依赖于主键，不能只依赖于主键的一部分
    • 例如，如果一个表的主键是 (A, B)，那么非主键字段 C 不能只依赖于 A 或 B，必须依赖于 (A, B) 两个字段
    • 这时可以把表拆成两个表，一个存 A 和 C，另一个存 A、B 和其他字段
  • 第三范式：3NF
    • 在满足第二范式的基础上，要求表中的每个非主键字段都不依赖于其他非主键字段，即消除传递依赖
    • 例如，如果字段 C 依赖于字段 B，而字段 B 又依赖于主键 A，那么 C 就是传递依赖于 A
    • 这时可以把表拆成两个表，一个存 A 和 B，另一个存 B 和 C

什么是数据库的分片和分区？有什么区别？

分片（Sharding）是指把整张表的数据水平切分到多个独立的数据库实例或服务器上，每个分片存储数据的一个子集，适用于大规模的分布式系统，数据分布在不同的物理节点上，每个分片通常有独立的存储和计算资源，主要用于提升数据库的扩展性和性能

分区（Partitioning）是指将单个数据库表的数据根据某个规则划分成多个部分，每个部分称为一个分区，所有分区都在同一个数据库实例内，分区之间共享数据库的资源，适用于管理和查询大表，能提升查询性能和维护效率

什么是数据库的连接池？为什么要使用连接池？

是用来管理数据库连接的，当需要建立数据库连接时，会从连接池中获取可用的连接，而不是直接创建连接

连接池可以降低频繁创建和销毁数据库连接带来的开销，还可以设置最大连接数量，从而限制应用对数据库的并发访问，提高系统稳定性

什么是数据库的外键？有什么作用？

数据库的外键是一个表中的字段，它引用了另一个表的主键，它的主要作用是维护数据库中表之间的数据一致性，确保引用关系的完整性

但外键约束会增加数据库的管理和检查开销，尤其是在进行插入、更新或删除操作时，会增加额外的检查操作，因此在高性能要求的系统中，外键有时会被禁用

MySQL 和 Redis 的区别？

MySQL 是关系型数据库，基于磁盘存储，底层是表、行、列结构，事务性完备 ACID，持久化主要靠 redo log 实现，基于 InnoDB 存储引擎，适用于处理复杂查询、事务处理和大量数据的场景

Redis 是键值对数据库，基于内层，底层有 String、Hash、Lish、Set 和 ZSet 等多种数据结构，事务功能一般，持久化主要靠 RDB 和 AOF 实现，适用于缓存、高速读写和实时数据处理的场景

解释一下数据库的备份和恢复策略？

数据库的备份是指将数据库的数据复制并存储在安全的地方，防止数据丢失或损坏，而恢复策略则是在数据丢失或损坏后，使用备份来还原数据库到某个一致的状态

  • 备份策略
    • 全量备份：定期备份整个数据库，适用于数据量较小或变化不频繁的场景
    • 增量备份：只备份自上次全量备份或增量备份以来发生变化的数据
    • 差异备份：备份自上次全量备份以来发生变化的数据
  • 恢复策略
    • 完全恢复：使用最近的全量备份和所有后续的增量或差异备份来还原数据库
    • 部分恢复：只恢复受损的部分数据
    • 时间点恢复：将数据库恢复到某个特定的时间点

可以对数据库表做哪些优化？

  • 合理使用数据库分表
    • 对于一些特别大的表，可以考虑将其拆分成多个子表，从而更多地管理数据
  • 建立索引
    • 在经常被查询的列上建立索引，提高查询性能
    • 但也要注意如果有过多的索引，会影响插入、更新和删除的性能
  • 避免使用 SELECT *
    • 只查询需要的列，减少数据传输和处理的开销
  • 选择合适的数据类型
    • 尽量使用 TINYINT、SMALLINT、MEDIUM_INT 来代替 INT 类型
    • 如果是非负则加上 UNSIGNED
    • VARCHAR 的长度只分配真正需要的空间
    • 尽量使用整数或枚举类型来代替字符串类型
    • 时间类型尽量使用 TIMESTAMP 而不是 DATETIME
  • 单表不要放太多字段
  • 尽量少使用 NULL
    • NULL 很难查询优化，并且会占用额外索引空间