《SQL必知必会》学习随笔-进阶篇

前言

这是《SQL必知必会》学习随笔系列的进阶篇，承接基础篇的内容，主要聚焦于数据库性能优化相关的知识点。

进阶篇的核心内容包括：

• 数据库调优：从多个维度分析如何提升数据库性能
• 范式与反范式：理解数据库设计原则及其权衡
• 索引原理与应用：深入理解索引的底层实现和使用场景
• 锁机制：掌握不同粒度和类型的锁
• 性能优化实战：使用慢查询日志、EXPLAIN等工具定位和解决性能问题

如果你还没有阅读基础篇，建议先从基础篇开始，那里介绍了SQL的基本概念和MySQL的执行流程。

数据库调优

调优方向

1. 用户的反馈
2. 日志分析
3. 服务器资源使用监控
4. 数据库内部状况监控

调优维度

选择适合的DBMS

列式存储数据库可以大幅度降低系统的I/O，适合于分布式文件系统和OLAP，但不适用于数据需要频繁增删改的场景。

优化表设计

1. 表结构要尽量遵循第三范式的原则（关于第三范式，我在后面章节会讲）。这样可以让数据结构更加清晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。
2. 如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率。
3. 表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。一般来说，如果字段可以采用数值类型就不要采用字符类型；字符长度要尽可能设计得短一些。针对字符类型来说，当确定字符长度固定时，就可以采用CHAR类型；当长度不固定时，通常采用VARCHAR类型。

优化逻辑查询

SQL查询优化，可以分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变SQL语句的内容让SQL执行效率更高效，采用的方式是对SQL语句进行等价变换，对查询进行重写。重写查询的数学基础就是关系代数。

SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

EXISTS子查询和IN子查询时，会根据小表驱动大表的原则选择适合的子查询。在WHERE子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。

假设我想对商品评论表中的评论内容进行检索，查询评论内容开头为abc的内容都有哪些，如果在WHERE子句中使用了函数，语句就会写成下面这样：

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

我们可以采用查询重写的方式进行等价替换：

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_text LIKE 'abc%'

在数据量大的情况下，第二条SQL语句的执行时间为前者的1/10。

范式

范式-3NF

这个算是数据库系统原理必学内容了，我简单说一下我的理解。

首先，从1NF -> 到 3NF 是逐级递进的， 2NF 包含了1NF的特性，而 3NF 又包含了 2NF 的特性.作为一种约束来说，1NF 对数据库的约束性最弱， 3NF 对数据库的约束最强。

在理解范式之前，需要先了解几个基本概念：

• 候选键（Candidate Key）：能够唯一标识一条记录的最小属性集
• 主属性：包含在任一候选键中的属性
• 非主属性：不包含在任何候选键中的属性
• 函数依赖：如果通过属性A可以唯一确定属性B，则称B函数依赖于A，记作A→B
• 完全函数依赖：如果A→B，且A的任何真子集都不能决定B，则称B完全函数依赖于A
• 传递依赖：如果A→B，B→C，且B不依赖于A，则称C传递依赖于A

基于这些概念，三个范式可以这样理解：

1NF（第一范式）：每个字段都是原子性的，不可再分。

2NF（第二范式）：在1NF的基础上，非主属性完全函数依赖于候选键（消除了非主属性对候选键的部分依赖）。

3NF（第三范式）：在2NF的基础上，非主属性不传递依赖于候选键（消除了非主属性对候选键的传递依赖）。

但是，3NF仍是有问题的。

3NF存在的问题：

即使满足了3NF，仍然可能存在主属性对候选键的部分依赖或传递依赖。例如，在某些情况下，一个主属性可能依赖于候选键的一部分，或者通过其他主属性传递依赖于候选键，这会导致数据冗余和更新异常。

为了解决这个问题，人们提出了一种新的范式：巴斯-科德范式，即BCNF。

BCNF（Boyce-Codd范式）：在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。简单来说，就是要求每个决定因素都必须是候选键。

反范式设计

范式为数据库提供了约束规则，这避免了许多问题，但这也对性能造成了一定影响。

反范式就是相对范式化而言的，允许少量的冗余，通过空间来换时间。

反范式的适用情况

当冗余信息有价值或者能大幅度提高查询效率的时候，如：

订单中的收货人信息，包括姓名、电话和地址等。每次发生的订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。

索引

简而言之，索引就是帮助数据库管理系统高效获取数据的数据结构。好比一本书的目录，它可以帮我们快速进行特定值的定位与查找，从而加快数据查询的效率。

不适用索引的场景

作为一种数据结构，索引本身也会占据存储空间.因此，在一些情况下，索引对搜素速率的优化比不上自己对性能的影响。这个时候我们就不能建立索引。

1. 数据表中的数据行数比较少的情况下，比如不到1000行。

数据库本身查询速率就很高，千条数据不会对数据库产生压力，也就不需要用索引优化。

2. 数据重复度大，比如高于10%的时候。

性别只分为男女。假设男女数量相同，如果想要在100万行数据中查找性别为男的数据，一旦创建了索引，你需要先访问50万次索引，然后再访问50万次数据表，

索引的分类

按属性的类型分类

聚集索引：属性为主键。

非聚集索引：属性不为主键。

聚集索引比使用非聚集索引的查询效率略高，通常使用聚集索引。

按属性的数量分类

单一索引：索引列为一列时为单一索引。

联合索引：多个列组合在一起创建的索引叫做联合索引。

联合索引的最左原则

在使用联合索引（复合索引）时，查询条件必须从索引的最左列开始，并且连续匹配，索引才能被有效利用。

当遇到范围查询(>、<、between、like)就会停止匹配。

根据这个特点，在设计索引时，应该注意：

1. 区分度高的列放左边：优先将选择性（cardinality）高的列放在前面
2. 等值查询在前，范围查询在后：

-- 推荐顺序: (status, create_time)
-- 而非: (create_time, status)
WHERE status = 1 AND create_time > '2024-01-01'

3. 尽量使用覆盖索引：减少回表操作
4. 避免索引列参与运算：保持索引列”纯净”
5. 用 EXPLAIN 验证：

EXPLAIN SELECT * FROM user WHERE a = 1 AND b = 2;

索引的底层

不想看优化过程可以直接看B=树。

二叉树

二叉树是一种常见的树形存储结构，时间复杂度为O(log2n)，它的特点是：

• 如果key大于根节点，则在右子树中进行查找；
• 如果key小于根节点，则在左子树中进行查找；
• 如果key等于根节点，也就是找到了这个节点，返回根节点即可。

但它有一个漏洞：

如果构造出来的树是链表型的，那么时间复杂度会退化为O(n).如果用二叉树作为索引的实现结构，会让树变得很高，增加硬盘的I/O次数,所以我们不使用这种结构。

B树(平衡的多路搜索树)

它的每一个节点最多可以包括M个子节点，M称为B树的阶。

• 根节点的儿子数的范围是[2,M]。
• 每个中间节点包含k-1个关键字和k个孩子，孩子的数量=关键字的数量+1，k的取值范围为[ceil(M/2), M]。
• 叶子节点包括k-1个关键字（叶子节点没有孩子），k的取值范围为[ceil(M/2), M]。
• 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即Key[i]<Key[i+1]。此时k-1个关键字相当于划分了k个范围，也就是对应着k个指针，即为：P[1], P[2], …, P[k]，其中P[1]指向关键字小于Key[1]的子树，P[i]指向关键字属于(Key[i-1], Key[i])的子树，P[k]指向关键字大于Key[k-1]的子树。
• 所有叶子节点位于同一层。

B+树

B+树基于B树做出了改进，主流的DBMS都支持B+树的索引方式，比如MySQL。B+树和B树的差异在于以下几点：

• 有 k 个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1。
• 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
• 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点-既保存索引，也保存数据记录。
• 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

B+树和B树有个根本的差异在于，B+树的中间节点并不直接存储数据。这样的好处是：

1. B+树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。

2. B+树的查询效率更高，这是因为通常B+树比B树更矮胖（阶数更大，深度更低），查询所需要的磁盘I/O也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。

3. 在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，并通过有序链表进行了链接。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

哈希索引

用Hash进行检索效率非常高，逻辑为：

值key通过Hash映射找到桶bucket。桶（bucket）指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到Hash冲突时，会在桶中进行键值的查找。

Hash冲突

如果桶的空间小于输入的空间，不同的输入可能会映射到同一个桶中，这时就会产生Hash冲突，如果Hash冲突的量很大，就会影响读取的性能。

通常Hash值的字节数比较少，简单的4个字节就够了。在Hash值相同的情况下，就会进一步比较桶（Bucket）中的键值，从而找到最终的数据行。

Hash值的字节数多的话可以是16位、32位等，比如采用MD5函数就可以得到一个16位或者32位的数值，32位的MD5已经足够安全，重复率非常低。

哈希索引与B+树索引的区别

1. Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2. Hash索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而B+树可以。对于联合索引来说，Hash索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3. Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面前模糊查询（比如%开头）的话就可以起到优化作用。

索引的使用

什么时候创建索引

1. 字段的数值有唯一性的限制，比如用户名
2. 频繁作为WHERE查询条件的字段，尤其在数据表大的情况下
3. 需要经常GROUP BY和ORDER BY的列
4. UPDATE、DELETE的WHERE条件列，一般也需要创建索引
5. DISTINCT字段需要创建索引
6. 多表JOIN的连接字段需要创建索引
   • 连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
   • 其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
   • 最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如user_id在product_comment表和user表中都为int(11)类型，而不能一个为int另一个为varchar类型。

索引失效

1. 索引进行了表达式计算

需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式；

2. 对索引使用函数
3. 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引

OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描；

4. 使用LIKE进行模糊查询的时候，前面不能是%
5. 索引列尽量设置为NOT NULL约束

判断索引列是否为NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为NOT NULL约束。

锁

锁的分类

按照锁粒度进行划分

我们从锁定对象的粒度大小来对锁进行划分，分别为行锁、页锁和表锁。

1. 行锁

按照行的粒度对数据进行锁定。锁定力度小，发生锁冲突概率低，可以实现的并发度高，但是对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

2. 页锁

是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

3. 表锁

对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

从数据库管理的角度对锁进行划分

我们还可以从数据库管理的角度对锁进行划分，分为共享锁和排它锁。

1. 共享锁

也叫读锁或S锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

2. 排它锁

也叫独占锁、写锁或X锁。排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。

3. 意向锁（Intent Lock）

简单来说就是给更大一级别的空间示意里面是否已经上过锁。举个例子，你可以给整个房子设置一个标识，告诉它里面有人，即使你只是获取了房子中某一个房间的锁。这样其他人如果想要获取整个房子的控制权，只需要看这个房子的标识即可，不需要再对房子中的每个房间进行查找。这样是不是很方便？

返回数据表的场景，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁。同理，事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁。这时，意向锁会告诉其他事务已经有人锁定了表中的某些记录，不能对整个表进行全表扫描。

从程序员的角度进行划分

如果从程序员的视角来看锁的话，可以将锁分成乐观锁和悲观锁。

1. 乐观锁（Optimistic Locking）

认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

2. 悲观锁（Pessimistic Locking）

也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

避免死锁的发生

1. 如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步来获取，这样可以减少死锁发生的概率；
2. 如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；
3. 不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率。

优化步骤

慢查询定位执行慢的SQL

mysql > show variables like '%slow_query_log';

开启慢查询日志

mysql > set global slow_query_log='ON';

EXPLAIN查看执行计划

EXPLAIN可以帮助我们了解数据表的读取顺序、SELECT子句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量以及额外的信息（比如是否使用了外部排序，是否使用了临时表等）等。

EXPLAIN会返回一个表格：其中type是关键信息。

all是最坏的情况，因为采用了全表扫描的方式。index和all差不多，只不过index对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。如果我们在Extral列中看到Using index，说明采用了索引覆盖，也就是索引可以覆盖所需的SELECT字段，就不需要进行回表，这样就减少了数据查找的开销。

SHOW PROFILE查看SQL的具体执行成本

看下当前会话都有哪些profiles：

mysql > show profiles;

想要查看上一个查询的开销，可以使用：

mysql > show profile;

缓冲池和查询缓存的区别

对比

对比维度	缓冲池（Buffer Pool）	查询缓存（Query Cache）
适用场景	几乎所有场景，是数据库高性能的绝对核心。	极少更新、大量完全相同读取的静态表（如配置表、字典表）。
缓存内容	数据页和索引页（通常每页 16KB），即底层的物理/逻辑数据块。	完整的 SQL 查询语句及其对应的完整结果集。
工作原理	InnoDB 在内存中开辟的一块连续空间。当你执行查询时，InnoDB 会先检查需要的数据页是否在 Buffer Pool 中。如果在（缓存命中），直接返回内存数据；如果不在（缓存未命中），则从磁盘读取该页到 Buffer Pool 中，然后再返回数据。	在 Server 层维护一个哈希表，Key 是 SQL 语句的哈希值，Value 是查询结果集。收到 SELECT 请求时，先计算哈希值去查表，命中则直接返回，跳过解析、优化、执行和存储引擎交互的所有步骤。
写入机制	当执行更新操作时，InnoDB 会先修改 Buffer Pool 中的数据页，并将其标记为”脏页（Dirty Page）”，随后由后台线程异步将这些脏页刷入磁盘（Flush）。这保证了写入的高性能。	-

查询缓存被淘汰

MySQL 官方在 8.0 版本中彻底移除了 Query Cache，主要原因包括：

1. 扩展性差：查询缓存的全局锁机制在多核 CPU 和高并发环境下成为严重的性能瓶颈。
2. 失效过于频繁：现代互联网应用读写频繁，查询缓存命中率极低，大部分时间都在做”缓存-失效”的无用功。
3. 更优的替代方案：应用层缓存（如 Redis 或 Memcached）提供了更细粒度、更灵活、更高性能的缓存控制能力，完全取代了数据库层做结果集缓存的需求。

总结

进阶篇主要围绕数据库性能优化展开，涵盖了从宏观的调优方向到微观的索引实现细节。核心要点包括：

1. 调优要多维度考虑：选择合适的DBMS、优化表设计、改进查询逻辑，而不是只盯着SQL语句本身
2. 范式不是越高越好：需要在规范性和性能之间找到平衡，该反范式时就反范式
3. 索引是把双刃剑：能加速查询但会增加写入成本和存储开销，要根据实际场景判断是否创建
4. 理解底层原理：B+树、哈希索引、锁机制等原理能帮助我们更好地理解性能问题的根源
5. 工具辅助诊断：慢查询日志、EXPLAIN、SHOW PROFILE等工具是定位性能瓶颈的利器

数据库优化是一个系统工程，需要结合业务特点、数据规模、硬件资源等多方面因素综合考虑。没有一招鲜吃遍天的银弹，只有不断实践、测试、验证，才能找到最适合自己项目的优化方案。

下一步可以继续深入学习事务、MVCC、分库分表等更高级的话题，也欢迎查看本系列的其他文章：

• 《SQL必知必会》学习随笔-基础篇
• 《SQL必知必会》学习随笔-加餐