《极客时间教程 - SQL 必知必会》笔记

钝悟2022/7/16大约 12 分钟

《极客时间教程 - SQL 必知必会》笔记

01 丨了解 SQL：一门半衰期很长的语言

SQL 语言按照功能划分成以下的 4 个部分：

DDL（数据定义语言）：定义数据库对象（库、表、列），CREATE/ALTER/DROP
DML（数据操作语言）：操作记录，INSERT/UPDATE/DELETE
DCL（数据控制语言）：定义访问权限和安全级别
DQL（数据查询语言）：SELECT 查询，SQL 的重中之重

02 丨 DBMS 的前世今生

DB、DBS 和 DBMS 的区别：

DBMS（DataBase Management System）：管理多个数据库的系统
DB（DataBase）：存储数据的集合（多个数据表）
DBS（DataBase System）：数据库 + DBMS + DBA

NoSql 不同时期的释义

1970：NoSQL = We have no SQL
1980：NoSQL = Know SQL
2000：NoSQL = No SQL!
2005：NoSQL = Not only SQL
2013：NoSQL = No, SQL!

03 丨学会用数据库的方式思考 SQL 是如何执行的

Oracle 中的 SQL 是如何执行的

语法检查 → 语义检查 → 权限检查
共享池检查：库缓存中查找执行计划
- 命中 → 软解析，直接执行
- 未命中 → 硬解析，创建解析树、生成执行计划
优化器：硬解析，生成执行计划
执行器：按执行计划执行 SQL

共享池：缓存 SQL 语句和执行计划（库缓存）+ 对象定义（数据字典缓冲区）

MySQL 中的 SQL 是如何执行的

MySQL 采用 C/S 架构（mysqld 服务端），分三层：

连接层：建立客户端与服务端连接
SQL 层：处理 SQL 语句
存储引擎层：负责数据存储和读取（插件式架构）

SQL 层：查询缓存（8.0 已废弃）→ 解析器 → 优化器 → 执行器

常见存储引擎：

InnoDB（5.5+ 默认）：支持事务、行锁、外键
MyISAM（5.5 前默认）：速度快，不支持事务/外键
Memory：数据存内存，重启丢失
NDB：MySQL Cluster 分布式集群
Archive：压缩归档

04 丨使用 DDL 创建数据库&数据表时需要注意什么？

核心指令：CREATE、ALTER、DROP。DDL 无需 COMMIT 即可生效。

设计原则：

表数量越少越好（E-R 图简洁）
字段数量越少越好（减少冗余，平衡冗余与检索效率）
联合主键字段越少越好（减少索引空间和运行时间）

05 丨检索数据

SELECT：查询数据
DISTINCT：返回唯一值（作用于所有列）
LIMIT：限制返回行数（起始行从 0 开始）
ASC：升序（默认） / DESC：降序

SELECT 查询的基础语法

查询单列

SELECT name FROM world.country;

查询多列

SELECT name, continent, region FROM world.country;

查询所有列

SELECT * FROM world.country;

查询过滤重复值

SELECT distinct(continent) FROM world.country;

限制查询数量

-- 返回前 5 行
SELECT * FROM world.country LIMIT 5;
SELECT * FROM world.country LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM world.country LIMIT 2, 3;

SELECT 的执行顺序

关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num -- 顺序 5
FROM player JOIN team ON player.team_id = team.team_id -- 顺序 1
WHERE height > 1.80 -- 顺序 2
GROUP BY player.team_id -- 顺序 3
HAVING num > 2 -- 顺序 4
ORDER BY num DESC -- 顺序 6
LIMIT 2 -- 顺序 7

06 丨数据过滤：SQL 数据过滤都有哪些方法？

比较操作符

运算符	描述
`=`	等于
`<>`	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
`>`	大于
`<`	小于
`>=`	大于等于
`<=`	小于等于

范围操作符

运算符	描述
`BETWEEN`	在某个范围内
`IN`	指定针对某个列的多个可能值

逻辑操作符

运算符	描述
`AND`	并且（与）
`OR`	或者（或）
`NOT`	否定（非）

通配符

运算符	描述
`LIKE`	搜索某种模式
`%`	表示任意字符出现任意次数
`_`	表示任意字符出现一次
`[]`	必须匹配指定位置的一个字符

07 丨什么是 SQL 函数？为什么使用 SQL 函数可能会带来问题？

数学函数
字符串函数
日期函数
转换函数
聚合函数

08 丨什么是 SQL 的聚集函数，如何利用它们汇总表的数据？

聚合函数

函数	说明
`AVG()`	返回某列的平均值
`COUNT()`	返回某列的行数
`MAX()`	返回某列的最大值
`MIN()`	返回某列的最小值
`SUM()`	返回某列值之和

09 丨子查询

非关联子查询：子查询执行一次，结果作为主查询条件
关联子查询：子查询循环执行，每次接收外部查询传入的值
关键词：EXISTS、IN、ANY、ALL、SOME
性能：表 A > 表 B 时，IN 子查询比 EXISTS 更高效（可利用索引）

10 丨 SQL92 连接

内连接（INNER JOIN）、自连接、自然连接（NATURAL JOIN）、外连接（LEFT/RIGHT JOIN）

11 丨 SQL99 是如何使用连接的，与 SQL92 的区别是什么？

12 丨视图

视图是基于 SQL 语句结果集的虚拟表，不存储数据，不能建索引。

作用：简化复杂 SQL、只暴露部分数据、增强安全性、更改数据格式

13 丨存储过程

存储过程（Stored Procedure）是一组预编译的 SQL 批处理，通过调用名称即可执行。

优点：

一次编译多次使用，执行效率高
可封装代码，提高复用性
降低网络通信量
可设置权限，安全性强

缺点：

可移植性差：不同数据库语法不通用
调试困难：只有少数 DBMS 支持调试
版本管理困难：无版本控制，表结构变更可能导致失效
不适合高并发：分库分表场景下难以维护

14 丨事务处理

ACID 特性：

原子性（A）：操作不可分割，要么全成功要么全回滚
一致性（C）：事务前后数据完整性约束不被破坏
隔离性（I）：事务之间彼此独立，提交前对其他事务不可见
持久性（D）：提交后修改永久保存，可通过日志恢复

控制语句：BEGIN / COMMIT / ROLLBACK / SAVEPOINT / RELEASE SAVEPOINT / SET TRANSACTION

15 丨初识事务隔离：隔离的级别有哪些，它们都解决了哪些异常问题？

事务隔离级别从低到高分别是：读未提交（READ UNCOMMITTED ）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和可串行化（SERIALIZABLE）。

16 丨游标：当我们需要逐条处理数据时，该怎么做？

17 丨如何使用 Python 操作 MySQL？

略

18 丨 SQLAlchemy：如何使用 PythonORM 框架来操作 MySQL？

略

19 丨基础篇总结：如何理解查询优化、通配符以及存储过程？

20 丨数据库调优维度

选择合适数据库 → 配置优化 → 硬件优化
优化表设计 → 优化查询 → 使用缓存
读写分离 + 分库分表

21 丨范式设计

1NF：属性原子性，不可再分
2NF：非主属性完全依赖候选键
3NF：非主属性不传递依赖候选键
BCNF：消除主属性对候选键的部分/传递依赖

范式化目标：减少冗余列，节省空间

22 丨反范式设计

反范式化目标：适当增加冗余列，避免关联查询

范式化优点：更节省空间、更新更快、更少需要 DISTINCT/GROUP BY
范式化缺点：增加关联查询（代价高）

23 丨索引概览

优点：减少扫描数据量、避免排序和临时表、随机 I/O 变顺序 I/O

缺点：创建维护耗时、占用额外空间、降低写性能

适用场景：频繁读操作、表数据量大、列名常用于 WHERE/JOIN

不适用场景：频繁写操作、小表（<1000行）、特大型表（用分区/NoSQL）

24 丨 B+树索引原理

磁盘 I/O 是索引效率的关键。B+树是“矮胖”结构，在磁盘页大小相同时 I/O 次数更少，查询性能更稳定，更适合范围查询。MySQL 采用 B+树作为索引数据结构。

25 丨 Hash 索引

仅 Memory 引擎显式支持。

优点：索引结构紧凑，查询速度非常快

缺点：

只支持等值比较（=、IN()、<=>），不支持范围查询和模糊查询
无法用于排序（数据无序）
不支持联合索引最左匹配原则
不能利用索引值避免读取行
可能出现哈希冲突（需遍历链表）

26 丨索引的使用原则：如何通过索引让 SQL 查询效率最大化？

✔️️️️ 什么情况适用索引？

字段的数值有唯一性的限制，如用户名。
频繁作为 WHERE 条件或 JOIN 条件的字段，尤其在数据表大的情况下
频繁用于 GROUP BY 或 ORDER BY 的字段。将该字段作为索引，查询时就无需再排序了，因为 B+ 树
DISTINCT 字段需要创建索引。

❌ 什么情况不适用索引？

频繁写操作（ INSERT/UPDATE/DELETE ），也就意味着需要更新索引。
列名不经常出现在 WHERE 或连接（JOIN）条件中，也就意味着索引会经常无法命中，没有意义，还增加空间开销。
非常小的表，对于非常小的表，大部分情况下简单的全表扫描更高效。
特大型的表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

索引失效的场景：

对索引使用左模糊匹配
对索引使用表达式或函数
对索引隐式类型转换
联合索引不遵循最左匹配原则
索引列判空
WHERE 子句中的 OR 前后条件存在非索引列

27 丨 B+树数据页结构

数据库管理存储空间的基本单位是页（Page）。

存储层级：表空间 > 段 > 区 > 页 > 行

页：存储最小单位（默认 16KB）
区：64 个连续页 = 1MB
段：创建表/索引时创建，不要求区连续
表空间：逻辑容器，包含系统/用户/撤销/临时表空间

28 丨磁盘 I/O 与查询成本

磁盘 I/O 远慢于内存，数据库采用缓冲池提升页查找效率。

两个关键原则：

位置决定效率：页在缓冲池中最快，内存次之，磁盘最慢
批量决定效率：顺序批量读取单页效率可能高于内存随机读取

29 丨为什么没有理想的索引？

略

30 丨悲观锁与乐观锁

悲观锁：假定并发冲突，查询后加锁直到提交。实现：数据库锁机制（SELECT ... FOR UPDATE）
乐观锁：假定无冲突，更新时检查版本。实现：版本号机制或 CAS

31 丨 MVCC

核心：Undo Log + Read View

Undo Log：保存数据历史版本
Read View：决定数据是否可见
不同隔离级别对应不同的 Read View 生成策略

32 丨查询优化器

MySQL 查询执行六步：

连接器：建立连接、获取权限
查询缓存：命中则直接返回（8.0 已废弃）
分析器：语法分析、词法分析
优化器：生成执行计划
执行器：调用存储引擎 API 执行
返回结果

33 丨如何使用性能分析工具定位 SQL 执行慢的原因？

34 丨答疑篇：关于索引以及缓冲池的一些解惑

35 丨主从同步

两种复制方式：基于行、基于语句，均通过主库记录二进制日志 + 从库重放实现异步复制。

三个线程：

binlog 线程：主库写 binlog
I/O 线程：从库读取主库 binlog → 写入 relay log
SQL 线程：从库执行 relay log 中的 SQL

三种复制模式：

异步复制：主库不等待从库确认，写效率高，但主库宕机可能丢数据
半异步复制（semi-sync）：等至少一个从库收到 binlog 后才返回，两阶段提交思想
组复制：基于 Paxos 协议，读写事务需“大多数人”同意才能提交

36 丨数据库没有备份，没有使用 Binlog 的情况下，如何恢复数据？

37 丨 SQL 注入

在输入字符串中注入 SQL 指令，被数据库服务器误认为正常 SQL 执行。

攻击示例：

-- 正常查询
SELECT * FROM user WHERE username='xxx' AND password='yyy'

-- 攻击输入: myuser' or 'foo' = 'foo' --
SELECT * FROM user WHERE username='myuser' or 'foo' = 'foo' --'' AND password='xxx'
-- -- 是注释标记，后续语句被忽略，攻击者无需密码即可登录