数据库SQL

SQL 基础

关系型与非关系型数据库

关系型数据库（MySQL）

• 存储结构：表（行+列），通过 SQL 语言查询。
• 特性：ACID（原子性、一致性、隔离性、持久性），数据存储在硬盘中（支持内存表技术）。

非关系型数据库（NoSQL）

• 存储结构：键值对（K-V），通过 Key 查询，数据存储在内存中。

数据类型

char 与 varchar

• char：定长字符串，长度 0-255，不足部分补 0。
• varchar：变长字符串，长度 0-65535，节省空间但可能产生内存碎片。

数据库范式

1. 第一范式 (1NF)：属性不可再分（原子性）。
2. 第二范式 (2NF)：有主键，其他字段完全依赖主键。
3. 第三范式 (3NF)：非主键字段之间互不依赖。
4. 反范式：通过冗余字段优化查询性能（空间换时间）。

DDL 与 DML

• DDL：定义数据库结构（如 CREATE TABLE）。
• DML：操作数据（如 INSERT、UPDATE、DELETE）。

---

存储引擎

MySQL 执行流程

1. 客户端发送 SQL 语句到 MySQL 服务器。
2. 服务器解析 SQL，生成解析树。
3. 查询优化器选择索引和访问路径。
4. 执行引擎调用存储引擎接口，读取数据页到缓冲池。
5. 在内存中修改数据，记录 UndoLog（事务回滚）和 RedoLog（崩溃恢复）。
6. 提交事务时刷盘 RedoLog，失败则用 UndoLog 回滚。
7. 返回结果给客户端。

InnoDB 与 MyISAM 对比

特性	InnoDB	MyISAM
事务支持	支持	不支持
外键支持	支持	不支持
聚簇索引	支持	不支持
锁级别	行级锁、表级锁	表级锁
行数保存	不支持	支持
默认版本	MySQL 5.5 后	MySQL 5.5 前
全文索引	5.6 后支持	支持

InnoDB 行格式

• COMPACT（MySQL 5.0 前默认）：
  • 保存字段值、空值列表、变长字段长度列表、记录头信息。

---

事务

定义

数据库操作序列，满足 ACID 特性。

ACID 特性

1. 原子性：事务要么全部提交，要么全部回滚（依赖 UndoLog）。
2. 一致性：事务执行后数据库从一个有效状态转移到另一个有效状态。
3. 隔离性：并发事务互不干扰（通过 MVCC 和锁机制实现）。
4. 持久性：事务提交后修改永久保存（依赖 RedoLog）。

隔离级别

隔离级别	脏读 (DR)	不可重复读 (NR)	幻读 (PR)
读未提交 (RU)	Y	Y	Y
读已提交 (RC)	N	Y	Y
可重复读 (RR)	N	N	Y
串行化 (SERIALIZABLE)	N	N	N

解决方案

• 脏读：MVCC（读已提交及以上级别）。
• 不可重复读：MVCC（可重复读级别）。
• 幻读：MVCC + 间隙锁（可重复读级别），或强制串行化。

MVCC（多版本并发控制）

1. 隐藏字段：
   • trx_id：事务 ID（自增）。
   • roll_pointer：指向上一版本记录的指针。
2. UndoLog：存储老版本数据，形成版本链。
3. ReadView：判断事务可见性，包含活跃事务列表和版本规则。

---

锁机制

锁分类

按级别划分

• 共享锁 (S 锁)：读锁，允许多事务并发读，不可写。
• 排他锁 (X 锁)：写锁，独占资源，其他事务不可读写。

按粒度划分

• 全局锁：锁定整个数据库（只读）。
• 表级锁：锁定整张表。
• 行级锁：
  • Record Lock：锁定索引记录。
  • Gap Lock：锁定索引间隙，防止插入。
  • Next-Key Lock：结合 Record Lock 和 Gap Lock（左开右闭）。

其他锁

• 意向锁：事务请求行锁或表锁时自动获取，表明意向（IS/IX 锁）。
• AUTO-INC 锁：保证自增主键连续性。
• MDL 锁：控制元数据访问（如 DDL 操作）。

锁优化

• 乐观锁：假设冲突少，提交时检查（如 CAS、版本号）。
• 悲观锁：先加锁再访问（如排他锁），适合高并发写入。

死锁

• 现象：多个事务互相等待资源。
• 解决：事务回滚（超时或死锁检测）。

---

索引

分类

按数据结构

• B+树索引：
  • 非叶子节点只存索引，叶子节点存数据并顺序连接。
  • 支持范围查询和排序，适合高扇出场景。
• Hash 索引：
  • 无序存储，适合等值查询，不支持范围查询。

聚簇索引与非聚簇索引

• 聚簇索引（InnoDB 主键索引）：
  • 叶子节点存储整行数据。
• 非聚簇索引（二级索引）：
  • 叶子节点存索引值 + 主键值，需回表查询完整数据。

索引优化

• 索引覆盖：查询字段全部在索引中，避免回表。
• 索引下推：将过滤条件下推到存储引擎层，减少回表数据量。
• 避免回表：联合索引满足最左前缀匹配。

索引类型

• 唯一索引：列值唯一，允许 NULL。
• 联合索引：多列组合索引，遵循最左前缀原则。
• 字段特性：
  • 单列索引 vs 联合索引。
  • 普通索引 vs 唯一索引。

---

SQL 执行与优化

执行顺序

1. FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。

排序实现

• 索引排序：利用索引直接返回有序数据。
• filesort 排序：
  • 内存排序（sort_buffer_size 足够时）。
  • 磁盘临时文件排序（数据量大时）。

分页优化

• 问题：LIMIT 10000, 20 需先读取 10020 行再丢弃前 10000 行。
• 解决方案：
  • 覆盖索引 + 子查询：先通过索引获取 ID，再关联查询完整数据。

Join 优化

• 算法：嵌套循环（低效）→ Hash Join（高效）。
  • Hash Join 步骤：
    1. 构建哈希表：驱动表数据加载到内存。
    2. 探测哈希表：遍历非驱动表，匹配数据。

---

数据库操作

删除命令

命令	类型	删除内容	记录日志	支持回滚	支持 WHERE	速度	主键重置
DELETE	DML	记录	Y	Y	Y	慢	N
TRUNCATE	DDL	记录	N	N	N	快	Y
DROP	DDL	记录 + 表结构	N	N	N	快	表删除

---

数据库优化

设计优化

• 表设计：合理选择数据类型（如用 INT 替代 VARCHAR 存数字）。
• 查询优化：
  1. 避免 SELECT *，明确字段名。
  2. 避免索引失效（如对字段做计算、使用函数）。
  3. 用 UNION ALL 替代 UNION（减少去重开销）。
  4. Join 优化：小表驱动大表，优先 INNER JOIN。

索引优化

• 高频查询字段：优先建索引。
• 区分度：选择区分度高的列（如性别区分度低）。
• 联合索引：遵循最左前缀原则。

架构优化

• 读写分离：查询与更新分离到不同服务器。
• 分库分表：大表水平拆分（按 ID 范围或哈希）。
• 缓存：Redis 缓存热点数据，减轻数据库压力。

---

其他

约束

• PK：主键约束（唯一且非空，通常为聚簇索引）。
• UK：唯一约束（唯一但可为空，可有多个）。
• FK：外键约束（关联其他表主键，性能开销大）。
• CK：检查约束（如 CHECK(age > 0)）。
• DF：默认值约束。

数据库加密

• 服务端加密：存入前加密，查询时解密。
• 模糊查询：先解密再查询，或明文分词加密。

内存碎片

• 原因：频繁 DML 操作（如页分裂、逻辑删除）。
• 危害：存储不连续、I/O 效率低、备份慢。
• 解决：
  • 连续自增 ID、避免可变长字段索引。
  • 定期执行 OPTIMIZE TABLE 或 ALTER TABLE 重建表。