RD 面试小技巧之DB篇（数据库、MySQL 和 SQL）

纽约码农

已于 2023-10-30 15:01:10 修改

阅读量77

点赞数

文章标签：数据库面试 mysql

于 2023-10-18 15:39:28 首次发布

本文链接：https://blog.csdn.net/weixin_55413092/article/details/133907608

版权

MySQL 数据库和 SQL

关键点

性能优化

低延迟 vs 高吞吐
量化：制定业务指标
考虑点：时机，业务场景

DB 与 SQL 优化

是业务系统优化的的核心
- 业务处理本身无状态，系统的状态在数据库中存储。
- 业务系统规模扩大，数据量扩大，且各系统内部实现完全不同。
数据库设计范式（实体表的合理拆分）
- 1NF：当且仅当关系 R 中每个属性的值域只包含原子项（不可拆分项）。
- 2NF：满足1NF，消除非主属性对码（即主键，可由多列联合组成）的部分函数依赖。
- 3NF：满足2NF，消除非主属性对码的传递函数依赖。
- BCNF：满足3NF，消除非主属性对码的部分函数依赖和传递函数依赖。
- 4NF：消除非平凡的多值依赖。
- 5NF：消除不合适的连接依赖。
- 实际场景一：故意加一些冗余字段（常用字段），提高查询效率。
- 实际场景二：不需要依赖主键去其他表查数据：从表也在当前表中。

MySQL

关系型数据库
- 以关系模型描述、表达数据。
- 以关系代数理论操作、运算。
两个主流分支版本：MySQL, MariaDB
执行引擎 / 存储引擎

存储引擎 MyISAM InnoDB Memory Archive
存储限制 256TB 64TB 看内存大小压缩在磁盘上
事务支持 x √ x x
索引支持 √ √ √ x
锁的粒度表锁行锁表锁行锁
数据压缩 √ x x √
外键 x √ x x
索引原理
- 数据按页分块。
- InnoDB 引擎使用 B+ 树或 Hash 实现索引。
- B-Tree / B-Tree / B+Tree 类型：默认数据按主键索引的结构存储。
配置优化
- 关于“连接请求”的变量
- 关于“缓冲区”的变量
- 配置 InnoDB 的变量
事务
- 事务隔离：数据库的基础特征。
- InnoDB：支持事务的存储引擎。
- 可靠性模型：ACID (Atomicity, Consistency, Isolation, Durability)
- 隔离级别（涉及 Isolation）
  - 读未提交 READ UNCOMMITTED （不保证一致性、对性能要求高的场景）
    - 问题：脏读，幻读，不可重复读
  - 读已提交 READ COMMITTED (RC)
    - 问题：幻读，不可重复读
  - 可重复读 REPEATABLE READ (RR)
    - InnoDB 的默认隔离级别；仅支持基于行的 binlog。
    - 使用了MVCC技术（多版本并发控制）：快照机制，保证事务在执行时看到的数据快照的一致性。
  - 可串行化 SERIALIZABLE （串行处理，隔离最严格，性能最低）
- 隔离范围：全局，会话。
- 日志
  - undo log 撤销日志：保证原子性（Atomicity）；用于回滚。
  - redo log 重做日志：保证持久性（Durability）；记录事务对数据页做的修改。
锁
- 表级锁，行级锁
- 死锁
- 乐观锁：先尝试操作，有冲突再重新读，重新尝试，本地自旋。
- 悲观锁：在释放锁之前，其他事务会被该锁阻塞；影响性能。
  - 改进：无锁 / 乐观锁。
- 锁的竞争不是很激烈时，乐观锁的效率远高于悲观锁。

存储引擎	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	看内存大小	压缩在磁盘上
事务支持	x	√	x	x
索引支持	√	√	√	x
锁的粒度	表锁	行锁	表锁	行锁
数据压缩	√	x	x	√
外键	x	√	x	x

主从复制

目标：解决单机数据库的读写压力问题。
核心：主库写 binlog；从库订阅主库，本地化从主库拉取的数据成为 relay log，按其中的顺序和指令执行 SQL 线程。
原理：
- 异步复制：可能造成主从数据不一致。
- 半同步复制：可靠性较纯异步较好；超时确认，主库可能会退化到传统的异步复制。
- 组复制：所有节点对等，不存在主从；基于分布式 Paxos 协议实现，保证分布式数据一致性。存在多节点写冲突。
局限性
- 主从延迟问题
- 应用侧需要配合读写分离框架
- 未解决高可用问题

读写分离

目标：提升数据库集群读的能力。
实现：配置多个数据源。
- 支持配置多个从库
- 支持多个从库的负载均衡
切换数据源的“侵入性”问题
- 其他考虑：降低侵入性会导致“写完读”数据不一致问题！
- solution: ShardingSphere-jdbc 的 Master-Slave 功能。
  - SQL 解析和事务管理，自动实现读写分离。
  - 可以解决“写完读”不一致问题。
旧系统改造问题
- 思路：多个读写分离、主从复制的数据库作为一个单独的虚拟数据库使用；仅修改数据库连接字符串（即连接指向）。
- solution：MyCat / ShardingSphere-Proxy 的 Master-Slave 功能。
  - 部署一个中间件：读写分离、主从复制的规则配置在中间件。
  - 中间件模拟一个 MySQL 服务器：对业务系统几乎零侵入。

高可用

目标：提供 Failover 能力，防止物理机宕机对集群的影响，保证系统持续可用的时间（更少的不可用服务时间）。
指标：SLA (Service Level Agreement) / SLO (Service Level Objects)
方案1~2：MySQL 外部；方案3~5：MySQL 内部。
- 方案1：主从手动切换
  - 侵入性问题：数据库和应用系统之间的中间层的大量的配置和脚本定义；代码的侵入性问题。
- 方案2：MHA (Master High Availability)
  - 目标：故障切换，主从提升。
  - 问题：需要配置SSH信息；需要至少3台服务器。
- 方案3：MGR (MySQL Group Replication)
  - 目标：数据可靠性复制（基于组复制），主从切换。
  - 特点：高一致性，高容错性，高扩展性，高灵活性。
  - 使用场景：弹性复制；高可用分片。
  - 问题：从外部（应用程序或中间层）获得主从状态变更，需要读数据库；外部需使用 LVS/VIP 配置。
- 方案4：MySQL Cluster
  - 完整的数据库层高可用方案。
  - 组成：MGR 核心组件 + MySQL Router（提供负载均衡，配置读写分离、高可用规则） + MySQL Shell（Cluster Admin 管理控制台）
- 方案5：Orchestractor 编排器
  - 一款 MySQL 高可用和复制拓扑管理工具。
  - 特点：自动发现 MySQL 的复制拓扑关系，可在 Web 界面重构复制关系，可自动/手动恢复主节点异常，支持命令行和 Web 界面管理复制。

经验认识

DB 与 SQL 设计优化

数据
- 类型：选取合适的、明确的类型，避免字节数浪费。
- 数据量：初期应考虑系统增量，合理使用类型；新增字段，增加从表；新增索引，在停机维护阶段进行。
- 尽量避免修改表结构：尽量避免修改 DDL 文件，尽量避免增加索引。
- 大批量写入优化
  - solution 1: PreparedStatement，减少SQL解析。
  - solution 2: 多值（INSERT语句中拼多条记录） / 批量（PreparedStatement中ADD BATCH）插入。
  - solution 3: Load Data 原生命令，文本文件直接导入数据。
  - solution 4: 先把约束和索引去除，导入数据；之后一次性重建所有约束和索引。
- 合理拆分宽表：提高执行效率。
索引
- Hash 类型：适合内存中的索引。
- B-Tree / B-Tree / B+Tree 类型：按块存储数据，适合磁盘中的索引；默认按主键顺序索引。
- 字段选择：计算字段的选择性，= DISTINCT(col)/count(*)；重复性越低，选择性越好，越适合作为索引；等于1时最好。
- 组合索引的构建：应避免索引冗余（长短索引共存时短索引冗余；数据库默认对唯一约束产生索引，则有唯一约束的索引与其他列组合时冗余）。
- 索引失效
  - 与“空”的比较操作：NULL, not, not in
  - 函数（用函数也走不了索引）
  - 减少使用 ‘or’：使用 ‘union’（已去重；‘union all’ 未去重）
  - 数据量大：放弃所有条件组合都走索引的幻想，直接全文检索。
  - 必要时：‘force index’, 告诉数据库强制走某个索引。
查询
- SQL：使用简单的 SQL，避免隐式转换。
- 设计：主键单调递增，避免出现“页分裂”问题。
- 设计：主键长度不宜过大，避免影响每个数据块能容纳的数据条数。
- 设计：尽量不使用外键、触发器。
- 速度：按主键 vs 按非主键
  - 按主键：更快。“聚集索引”。
  - 按非主键：“二级索引”，单独的索引文件，不直接对应数据。
- 模糊查询
  - 数据量小：使用 LIKE（默认前缀匹配）。
  - 数据量大：建立”全文检索“的倒排索引；使用 ElasticSearch / solr 等全文检索类工具。
- 连接查询
  - 驱动表的选择：驱动表越小，数据越明确。
存储引擎（恰当选择）
- InnoDB：主流默认选择；强事务。
- MyISAM：不需要事务，数据操作量较大。
- Memory：数据量小，不需要持久化。
- Archive：针对归档数据。
- ToKuDB：针对归档数据（高压缩比，有大量重复数据时，压缩效率超高）。
参考 DBA 指导手册 / 数据库设计规范