MySQL进阶

索引的数据结构

https://www.bilibili.com/video/BV1iq4y1u7vj?p=115

索引设计原则

哪些情况下适合创建索引

  1. 字段的数值有唯一性限制 —— 唯一索引或主键索引

  2. 频繁作为WHERE查询条件的字段

  3. 经常GROUP BY和ORDER BY的列

  4. UPDATE、DELETE的WHERE条件列

  5. DISTINCT字段需要创建索引

  6. 多表JOIN连接操作时，创建索引注意事项

    • 连接表数量不要超过3张
    • 对WHERE条件创建索引
    • 对连接的字段创建索引

  7. 使用列的类型小的创建索引

    这里的类型大小指该类型表示的数据范围大小

    以整型为例，如果我们想要对某个整数列建立索引，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用INT就不使用BIGINT，能使用MEDIUMINT就不使用INT，这是因为：

    • 数据类型越小，在查询时的比较操作越快
    • 数据类型越小，索引占用的存储空间就越小，一个数据页就可以存放更多记录，从而减少磁盘IO

    这个建议对表的主键来说更适用

  8. 使用字符串前缀创建索引

    1
    ALTER TABLE shop ADD INDEX(address(12));

    在VARCHAR字段上建立索引，没必要对全字段建立索引，根据实际文本区分度决定索引长度

    1
    COUNT(DISTINCT LEFT(列名,索引长度))/COUNT(*) # 计算区分度

    引申问题：使用索引列前缀的方式无法支持使用索引排序

  9. 区分度高（散列性高）的列适合作为索引

    列的基数指的是某一列当中不重复数据的个数

    在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。最好为列的基数大的列创立索引，为基数太小的列创建索引效果可能不好。

    1
    COUNT(DISTINCT 列名)/COUNT(*)

  10. 使用最频繁的列放在联合索引的左侧

  11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

限制索引的数目

建议单表索引数量不超过6个

  • 每个索引都需要占用磁盘空间
  • 索引会影响INSERT、UPDATE、DELETE等语句的性能
  • 优化器在优化查询时会对每个可以用到的索引进行评估，过多的索引会增加优化器生成执行计划的时间，降低查询性能

哪些情况下不适合创建索引

  1. 在WHERE使用不到的字段，不要设置索引
  2. 数据量小的表最好不要使用索引
  3. 有大量重复数据的列上不要创建索引
  4. 避免对经常更新的表创建过多的索引
  5. 不建议用无序的值作为索引
  6. 删除不再使用或很少使用的索引
  7. 不要定义冗余或重复的索引

性能分析工具

查看系统性能参数

1
SHOW STATUS LIKE '参数';

一些常用性能参数：

  • Connections: 连接MySQL服务器的次数
  • Uptime: MySQL服务器的上线时间
  • Slow_queries: 慢查询的次数
  • Innodb_rows_read: Select查询返回的行数
  • Innodb_rows_inserted: 执行INSERT操作插入的行数
  • Innodb_rows_updated: 执行UPDATE操作更新的行数
  • Innodb_rows_deleted: 执行DELETE操作删除的行数
  • Com_select: 查询操作的次数
  • Com_insert: 插入操作的次数（批量插入只累加一次）
  • Com_update: 更新操作的次数
  • Com_delete: 删除操作的次数

统计SQL的查询成本: last_query_coat

1
SHOW STATUS LIKE 'last_query_coat';

这个查询成本对应的就是SQL语句需要读取的页的数量

SQL查询是个动态的过程

  1. 位置决定效率：缓冲池>内存>磁盘
  2. 批量决定效率：顺序IO>随机IO

慢查询日志

默认情况下，MySQL数据库没有开启慢查询日志。如果不是调优需要，一般不建议开启该参数

开启慢查询日志参数

1
2
3
4
5
6
SHOW VARIABLES LIKE '%slow_query_log%'; # 查看慢查询日志是否开启
SHOW VARIABLES LIKE '%long_query_time%'; # 查看时间阈值

SET GLOBAL slow_query_log='ON';
SET GLOBAL long_query_time=1; # 仅修改GLOBAL对当前session失效
SET long_query_time=1; # 所以当前session也需要修改

通过配置文件修改

1
2
3
[mysqld]
slow_query_log=ON
long_query_time=1

定位慢查询的SQL

通过mysqldumpslow（在服务器内执行，非MySQL）

1
mysqldumpslow -a -s t -t 5 /var/lib/mysql/慢查询日志

删除慢查询日志

手动删除慢查询日志即可

1
rm -rf 慢查询日志

使用mysqladmin flush-logs重新生成查询日志文件，slow指定重新生成慢查询日志文件

1
mysqladmin -uroot -p flush-logs slow

慢查询日志都是使用该命令来删除重建的。但是要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

查看SQL执行成本

1
2
SHOW VARIABLES LIKE 'profiling';
SET profiling='ON'; # 开启Show Profile
1
2
3
SHOW PROFILES;
SHOW PROFILE; # 针对最近一次查询
SHOW PROFILE ALL FOR QUERY 2 # 指定Query ID

show profile常用查询参数：

  • ALL: 显示所有的开销信息
  • BLOCK IO: 显示块IO开销
  • CONTEXT SWITCHES: 上下文切换开销
  • CPU: 显示CPU开销信息
  • IPC: 显示发送和接收开销信息
  • MEMORY: 显示内存开销信息
  • PAGE FAULTS: 显示页面错误开销信息
  • SOURCE: 显示和Source_function，Source_file，Source_line相关的开销信息
  • SWAPS: 显示交换次数开销信息

需要注意的结论：

  • converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了
  • Creating tmp table：创建临时表。先拷贝数据到临时表，用完再删除临时表
  • Copying to tmp table on disk：把内存中临时表复制到磁盘上
  • locked

如果在show profile诊断结果中出现了以上情况，则SQL语句需要优化

注意：show profile命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看

EXPLAIN

定位到查询慢的SQL后，我们就可以使用EXPLAIN做针对性的分析查询语句。

注意：使用EXPLAIN并不真正执行语句，只是查看执行计划

EXPLAIN要语句输出的各列作用如下：

列名 描述
id 每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名，每一行记录都对应着一个单表
partitions 匹配的分区信息
type 针对表单的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外信息

type

MySQL对某个表执行查询时的访问方法，又称”访问类型“

  • const

    对主键或者唯一索引进行等值查询，对单表的访问方法就是const

  • system

    在const基础上，当表中只有一条记录，那么对该表的访问方法就是system，性能高于const，属于const的一种特殊情况

    前提条件：存储引擎对数据统计是精确的，由于Innodb不确定表正好有一行，所以在innodb中不会出现system

  • eq_ref

    联表查询中，关联条件是 主键或者唯一非空索引，则对该被驱动表的访问方式就是eq_ref

  • ref

    当通过普通的二级索引列与常量进行等值匹配时，对该表的访问方式就可能是ref

  • ref_or_null

    当通过普通的二级索引列与常量进行等值匹配时，如果该列的值也可以为NULL时，对该表的访问方式就可能是ref_or_null

  • index_merge

    单表访问时在某些场景下可以使用索引合并的方式来执行查询

  • unique_subquery

  • index_subquery

    MySQL服务层会对SQL进行优化，上面两种type不会在存储引擎层出现

  • range

    任意索引的范围查询，包括BETWEENT、LIKE、IN、>、<等，都是range访问方法

  • index

  • ALL

    上面两种type都是全表扫描，区别在于index只需要查询索引树就可以拿到结果，而ALL需要遍历所有行

Extra

一些额外信息

常见信息：

  • using index

    索引覆盖，想查找的数据都在索引树中，不需要回表再去查询聚簇索引

  • using index condition

    索引下推，如果搜索时需要大量回表，在回表之前，会先处理“下推”到存储引擎的过滤逻辑，过滤整理后才发到Server层进行处理

  • using where

    与using index condition相对，所有过滤处理都在Server层处理

  • using MRR

    对二级索引范围查询时，会先对二级索引的查询结果缓存并进行排序，然后统一去聚簇索引中回表查询，这样就可以把随机IO优化成顺序IO，提升效率

  • using join buffer（BNL、BKA）

    如果AB两表关联查询，A关联列上有索引而B没有，就会发生BNL优化，从A表中取10行数据放到Join Buffer内存空间中，再全表扫描B表和Join Buffer中这10行进行关联，然后循环这一步，直到A表所有数据都关联完

    BKA在BNL基础上，如果B表有索引，则配合MRR，将Join Buffer中的行进行排序后，去B表对应索引中查询，无需全表扫描

  • using union（indexs）

    如果多个查询条件都是索引，就会对索引进行合并，会出现using union，一般伴随type index_merge

  • using temporary

    表示语句执行过程中出现了临时表

  • using filesort

    通常出现在order by中，在查询之后需要额外单独进行一次排序，就会出现using filesort，可以通过将条件和排序字段组成联合索引进行优化

查看重写后的SQL

在使用EXPLAIN语句查看了某个查询的执行计划手，可以使用SHOW WARNINGS语句查看与这个查询的执行计划有关的扩展信息

1
SHOW WARNINGS;

索引优化与查询优化

MySQL中提高性能的一个最有效方式就是对数据库设计合理的索引

索引失效

索引失效的几种情况：

  1. 索引列参与运算、使用了函数、进行了类型转换（参数类型和字段类型不一致），会导致全表扫描，索引失效

  2. 模糊查询时（LIKE语句），%位于条件首部，会导致索引失效

  3. 不等于比较索引失效

  4. 两列数据做比较，即便两列都创建了索引，索引也会失效

  5. 查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效；

    or两边为“>”和“<”范围查询时，索引失效

  6. 查询条件使用is null时正常走索引，使用is not null时，不走索引

  7. 查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效；

    查询条件使用not exists时，索引失效

  8. 联合索引不满足最左匹配原则

  9. 范围查询导致联合索引右侧列索引失效

关联查询优化

  • 外连接

    给关联条件字段加上索引

  • 内连接

    对于内连接来说，查询优化器可以决定 驱动表 与 被驱动表

    • 如果关联条件字段只在一个表中有索引，那么该表将作为 被驱动表

    • 如果关联条件字段在两个表中都有索引，会选择数据量小的表作为 驱动表（小表驱动大表）

JOIN语句原理

JOIN本质上是各个表之间数据的循环匹配

简单嵌套循环连接（Simple Nested-Loop Join）

假设表A数据100条，表B数据1000条，则循环100*1000次，开销如下：

开销统计 SNLJ
外表扫描次数 1
内表扫描次数 A
读取记录数 A+B*A
JOIN比较次数 A*B
回表读取记录次数 0

索引嵌套循环连接（Index Nested-Loop Join）

INLJ的优化思路主要是为了减少内层表数据的匹配次数，要求被驱动表上有索引

开销统计 INLJ
外表扫描次数 1
内表扫描次数 0
读取记录数 A+B(match)
JOIN比较次数 A*Index(Height)
回表读取记录次数 B(match)（if possible）

INLJ每次JOIN的比较，其实就是一次索引查找，这里的Index(Height)个人理解为树的高度*二分查找的成本

块嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。但内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用Simple Nested-Loop Join算法的两表联接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。这也就是Block Nested-Loop Join算法的思想。

开销统计 BNLJ
外表扫描次数 1
内表扫描次数 A*used_column_size/join_buffer_size+1
读取记录数 A+B*(A*used_column_size/join_buffer_size)
JOIN比较次数 A*B
回表读取记录次数 0

A*used_column_size/join_buffer_size是join buffer分批加载表A记录的批次数

也可以写成 A/(join_buffer_size/used_column_size)，方便理解

总结

  • 整体效率：INLJ > BNLJ > SNLJ
  • 永远用小结果集驱动大结果集（本质是减少外层循环的数据数量）
  • 为被驱动表匹配的条件添加索引（减少内层表的循环匹配次数）
  • 适当增加join buffer的大小（一次缓存的数据越多，内表的扫描次数就越少）
  • 减少驱动表不必要的字段查询（字段越少，join buffer缓存的数据就越多）

注意：从MySQL的8.0.20版本版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始加入了hash join，默认都会使用hash join

子查询优化

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。但是子查询的执行效率不高：

  1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立临时表，然后外层查询语句从临时表中查询记录。查询完成后再撤销这些临时表。这样就会消耗过多的CPU和IO资源，产生大量的慢查询
  2. 子查询结果集存储的临时表，不会存在索引，所以查询性能会受到一定影响
  3. 对于返回结果集比较大的子查询，其对查询性能得意影响也就越大

优化方式：使用连接查询来代替子查询

排序优化

在MySQL中支持两种排序方式，分别是FileSort和Index排序

  • Index排序中，索引可以保证数据的有序性，不需要再排序，效率更高
  • FileSort排序一般在内存中尽显，占用CPU较多。如果待排结果较大，会产生临时文件IO到磁盘进行排序的情况，效率较低

优化建议：

  1. 可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句中避免使用FileSort排序
  2. 尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同列就使用单索引；如果不同就使用联合索引
  3. 无法使用Index时，需要对FileSort方式进行调优

索引情况

使用order by时，以下情况会导致索引失效：

  • 无过滤条件（没有WHERE或者LIMIT）
  • 对不同的索引列使用order by
  • 不满足最左匹配原则
  • 排序方式不同，多个字段要么同时升序，要么同时降序

FileSort算法：双路排序和单路排序

  • 双路排序（慢）

    MySQL4.1之前使用双路排序，字面意思是两次扫描磁盘，最终得到数据

    1. 读取行指针和order by列，对他们进行排序
    2. 然后根据排好序的列表，重新读取对应数据输出

  • 单路排序（快）

    从磁盘读取查询需要的所有列，按照order by列进行排序，然后扫描排序后的列表进行输出。它的效率更快一些，避免了二次读取数据，并且把随机IO变成了顺序IO，但是会使用更多的空间

引申问题：

总体而言，单路排序优于双路排序，但是单路可能存在以下问题：

  • 单路比多路占用更多空间，有可能取出数据总大小超过了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小数据…从而多次IO
  • 单路本来想省一次IO操作，反而导致了大量的IO操作，得不偿失

优化：

  1. 尝试提高sort_buffer_size
  2. 尝试提高max_length_for_sort_data
  3. order by时select具体字段，避免使用select *

GROUP BY优化

  • group by使用索引的原则几乎和order by一样，group by即使没有过滤条件用到索引，也可以直接使用索引
  • group by 先排序再分组，遵照索引的最佳左前缀原则
  • 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
  • where效率高于having，能写在where的条件就不要写在having中
  • 减少使用order by。order by、group by、distinct这些语句较为耗费CPU，数据的CPU资源是极其宝贵的
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢

分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。

当遇到如LIMIT 2000000,10这种情况，优化思路：在索引上完成排序分页，最后根据主键关联回原表查询所需要的其他列内容

1
SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id=a.id;

覆盖索引

定义：一个索引包含了满足查询结果的数据就叫覆盖索引

优点：

  • 避免回表

    避免了对主键的二次查询，减少了IO操作

  • 可以把随机IO变成顺序IO加快查询效率

    由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少得多，因此利用覆盖索引在访问时也可以把磁盘的随机读取IO转变为索引查找的顺序IO

缺点：

  • 维护成本

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

索引下推

Index Condition Pushdown（ICP）是MySQL5.6中新特性，是一种存储引擎层使用索引过滤数据的优化方式。

为了理解ICP是如何工作的，我们先了解下没有使用ICP的情况下，MySQL是如何查询的：

  • 存储引擎读取索引记录；
  • 根据索引中的主键值，定位并读取完整的行记录；
  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程如下：

  • 读取索引记录（不是完整的行记录）；
  • 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
  • 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
  • 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

本质上ICP是以降低回表和访问存储引擎的次数，提高查询效率。

使用条件：

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

  • 只能用于InnoDB和 MyISAM存储引擎及其分区表；

  • 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

    索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，完整的行记录已经加载到缓存区了，索引下推也就没什么意义了。

  • 引用了子查询的条件不能下推；

  • 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

其他优化

EXISTS和IN的区分

1
2
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE cc EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

选择标准可以理解为小表驱动大表。

当A小于B时，用EXISTS；当B小于A时，用IN。

COUNT(*)、COUNT(1)、COUNT(具体字段)效率

前提：不存在空数据（因为count(具体字段)会忽视空数据，其余两种不会，因为场景不同，所以存在空数据的情况下，比较效率是没有意义的）

  • COUNT(*)和COUNT(1)没有本质上的区别

  • 如果是MyISAM存储引擎，时间复杂度为O(1)，因为MyISAM的每张表都有一个meta信息存储了row_count值

    如果是InnoDB存储引擎，时间复杂度为O(n)，因为InnoDB支持事务，采用行级锁和MVCC机制，无法像MyISAM一样维护一个row_count变量，因此需要全表扫描

  • 在InnoDB中，如果使用COUNT(具体字段)来统计数据行，尽量采用二级索引。因为主键采用的聚簇索引包含信息更多，占用资源也更多。对于COUNT(*)和COUNT(1)，它们不需要查找具体行，只是统计行数，系统会自动采用占用空间更小的二级索引进行统计（判断依据为key_len），当没有二级索引时才会采用主键索引进行统计

SELECT *

尽量避免使用select *，原因如下：

  • MySQL在解析的过程中，会通过查询数据字典将*转换为所有列名，增加了资源耗费
  • 无法使用覆盖索引
  • 对表关联等使用到了buffer的操作会产生影响

LIMIT 1

针对的是全表扫描的SQL语句，如果确定结果集只有一条，加上LIMIT 1，当找到一条结果就不会再继续扫描了，加快查询结果

但如果数据表已经对字段建立了唯一索引，就可以通过索引进行查询，不会进行全表扫描，那么就不需要加LIMIT 1

范式

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式、第二范式、第三范式、巴斯-科德范式、第四范式和第五范式

键

  • 超键：能唯一标识元组的属性集叫做超键
  • 候选键：如果超键不包括多余的属性，那么这个超键就是候选键
  • 主键：用户可以从候选键中选择一个作为超键
  • 外键：如果数据表R1中的某个属性集不是R1的主键，而是另一个表R2的主键，那么这个属性集就是表R1的外键
  • 主属性：包含在任一候选键中的属性称为主属性
  • 非主属性：与主属性相对，不包含在任何一个候选键中的属性

第一范式

第一范式主要确保数据表中每个字段的值必须具有原子性，也就是说数据表中的每个字段的值都是不可再次拆分的最小数据单元。

第二范式

在满足第一范式的基础上，还要满足数据表里的每一条记录，都是可唯一标识的，而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

举例：

比赛表包含 球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等信息，这里的主键为（球员编号，比赛编号），可以通过主键决定如下关系：

1
（球员编号，比赛编号）-> （姓名，年龄，比赛编号，比赛时间，比赛场地）

但它不满足第二范式，因为表中字段之间还存在如下关系：

1
2
（球员编号）-> （姓名，年龄）
（比赛编号）-> （比赛时间，比赛场地）

第三范式

在满足第二范式的基础上，确保数据表中的每一个非主键字段与主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。非主键属性之间不能有依赖关系，必须相互独立。

举例：

部门信息表包含 部门编号、部门名称、部门简介等信息

员工信息表包含 员工编号、姓名、部门编号。列出部门编号后就不能再将部门名称等信息再加入到员工表中。

小结

  • 第一范式，确保每列保持原子性
  • 第二范式，确保每列和主键完全依赖
  • 第三范式，确保每列和主键直接相关

范式优点：

  • 数据的标准化有助于消除数据库中的数据冗余，第三范式通常被认为再性能、扩展性和数据完整性方面达到了最好的平衡

范式缺点：

  • 可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据冗余度越低，进行查询的时候可能就需要关联多表，这不仅代价昂贵，也可能使一些索引策略无效

范式只是提出了设计的标准，实际上设计时未必一定要符合这些标准。在开发中，可能会通过增加少量的冗余来提高数据库的读性能，减少关联查询的次数，实现空间换时间的目的。反范式化也是一种优化思路。

巴斯-科德范式

巴斯-科德范式，又称巴斯范式（BCNF），是对第三范式的改进，所以称为修正的第三范式，或扩充的第三范式，而不能称为第四范式。

它在第三范式的基础上消除主属性对候选键的部分函数依赖和传递函数依赖。

BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的关系都必然满足第三范式。或者还可以换一种说法：若一个关系达到了第三范式，并且它只有一个候选码，或者它的每个候选码都是单属性，则该关系自然达到BC范式。一般来说，一个数据库设计符合3NF或BCNF就可以了。

举例：

学生导师表，字段包含：学生ID，专业，导师，专业GPA

其中学生ID和专业是联合主键，该表满足第三范式，但是这里存在另一个依赖关系：专业 依赖于 导师，所以该表的部分主键依赖于非主键属性，我们可以拆分成两个表：

学生专业表（学生ID，专业，专业GPA）

导师表（导师，专业）

第四范式

第四范式在满足巴斯范式的基础上，消除非平凡且非函数依赖的多值依赖（即把同一表内的多对多关系删除）

举例：

职工表（职工编号，职工孩子姓名，职工选修课程）

在这个表中，同一个职工可能会有多个职工孩子姓名，同样，同一个职工也可能会有多个职工选修课程，即这里存在着多值事实，不符合第四范式

第五范式

在第四范式的基础上，消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连接依赖均由R的候选键所蕴含，则此关系模式符合第五范式

设计表的设计原则

  1. 数据表的个数越少越好

  2. 数据表中的字段个数越少越好

    上述的个数少是相对的，需在数据冗余和检索效率中进行平衡

  3. 数据表中联合主键的字段越少越好

    联合主键字段越多，占用索引空间越大

  4. 使用主键和外键越多越好

    数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明实体之间的冗余度越低，利用度越高

“三少一多”原则的核心就是简单可复用

这些原则不是绝对的，有时候我们需要牺牲数据的冗余度来换取数据处理的效率

事务

一组逻辑操作单元

ACID特性

  • 原子性

    事务被视为一个不可分割的最小单位，它要么完全执行，要么完全不执行

  • 一致性

    一致性保证了事务的执行将数据库从一个一致的状态转变到另一个一致的状态

  • 隔离性

    隔离性是指当多个事务同时对数据库进行操作时，每个事务都是独立的，一个事务的操作不会影响到其他事务

  • 持久性

    持久性意味着一旦事务被提交，它对数据库的修改就是永久性的，即使系统发生故障也不会丢失

如何使用事务

显式事务

开启事务

1
2
3
BEGIN;
# 或者
START TRANSACTION;

START TRANSACTION相较于BEGIN，后面可以跟随几个修饰符：

  • READ ONLY：标识当前事务是一个只读事务

    只读事务只是不允许修改其他事务也可以访问的表中数据，对于临时表来说，由于它们只在当前会话中可见，所以只读事务也是可以对临时表进行修改的

  • READ WRITE：标识当前事务是一个读写事务

  • WITH CONSISTENT SNAPSHOT：开启一致性读

提交事务/中止事务（回滚）

1
2
3
4
5
6
# 提交事务
COMMIT;
# 回滚事务
ROLLBACK;
# 将事务回滚到某个保存点
ROLLBACK [TO SAVEPOINT];
1
2
3
4
# 创建保存点
SAVEPOINT 保存点名称;
# 删除某个保存点
RELEASE SAVEPOINT 保存点名称;

隐式事务

默认情况下，如果我们不显式使用BEGIN或START TRANSACTION去开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交

如何关闭自动提交？

1
SET autocommit = FALSE; # 针对于DML操作是有效的，对DDL操作是无效的

或者显式创建一个事务，DML操作就不会自动提交数据

隐式提交数据的情况

  • 数据定义语言（DDL）

  • 隐式使用或修改 mysql数据库中的表

  • 事务控制或关于锁定的语句

    • 上一个事务还没有提交或回滚，又开启了另一个事务，会隐式提交上一个事务
    • 将autocommit从关闭调为开启时，也会隐式提交前边语句所属的事务
    • 使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式提交前边语句所属的事务

  • 加载数据的语句

    使用LOAD DATA语句来导入数据时，也会隐式提交前边语句所属的事务

  • 关于MySQL复制的语句

    使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句也会隐式提交前边语句所属的事务

  • 其他的一些语句

completion_type

1
SET @@completion_type = 1;
  • completion_type=0，这是默认情况。当我们执行COMMIT时会提交事务，在执行下一个事务时，还需要使用BEGIN或者START TRANSACTION开启
  • completion_type=1，这种情况下，当我们提交事务后，相当于执行了COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务后会开启一个相同隔离级别的事务
  • completion_type=2，这种情况下，当我们提交事务后，相当于执行了COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接

事务分类

  • 扁平事务
  • 带有保存点的扁平事务
  • 链事务
  • 嵌套事务
  • 分布式事务

事务隔离级别

数据并发问题

  • 脏写

    一个事务将另一个事务的更新回滚回最初状态

  • 脏读

    一个事务读取到另一个事务还没有提交的数据

  • 不可重复读

    一个事务先后读取同一条数据，但两次读取的数据不同

  • 幻读

    一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在（这个数据可能是由另一事务插入的，这些记录被称为幻影记录）

严重性：脏写 > 脏读 > 不可重复读 > 幻读

脏写的说法有点太理论。实际上脏写是不允许发生的。

两个事务同时操作一条数据的情况下，先操作数据的事务会锁定操作的那一行数据，其他事务无法对这条数据进行更新操作的。

也就是说脏写实际上是不可能发生的。

SQL四种隔离级别

  • READ UNCOMMITTED：读未提交

    所有事务都可以看到其他未提交事务的执行结果

    不能避免脏读、不可重复读、幻读

  • READ COMMITTED：读已提交

    一个事务只能看到已经提交事务所做的改变

    可以避免脏读，但不能避免不可重复读、幻读

  • REPEATABLE READ：可重复读

    事务A读到一条数据后，事务B对该数据进行了修改并提交，那么事务A再次读取该数据，读到的仍然是原本的内容

    可以避免脏读、不可重复读，但不能避免幻读

    这是 MySQL InnoDB引擎 的默认隔离级别

  • SERIALIZABLE：可串行化

    强制事务排序，所有并发问题都可以避免，但性能十分低下

    可以避免脏读、不可重复读、幻读

1
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;

锁

  • 全局锁

    锁定数据库中的所有表

  • 表级锁

    每次操作锁住整张表

  • 行级锁

    每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

典型场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

1
2
flush tables with read lock; # 上锁
unlock tables; # 解锁

https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=123

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

分类：

  • 表锁
  • 元数据锁（meta data lock，MDL）
  • 意向锁

表锁

表锁，分为两类：

  • 表共享读锁（read lock）

    所有客户端可读不可写

  • 表独占写锁（write lock）

    当前客户端可读可写，其余客户端不可读不可写

1
2
lock tables 表名 read/write;
unlock tables;

元数据锁

MDL加锁过程是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。

为了避免DML与DDL冲突，保证读写的正确性。

当对一张表进行增删改查时，加MDL读锁（共享）；当对表结构进行变更操作时，加MDL写锁（排他）

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

  • 意向共享锁（IS）

    由语句 select … lock in share mode 添加

    与表锁共享锁（read）兼容，与表锁排他锁（write）互斥

  • 意向排他锁（IX）

    由语句 insert、update、delete、select … for update 添加

    与表锁共享锁（read）及排他锁（write）都互斥

意向锁之间不会互斥

行级锁

每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

  • 行锁（Record Lock）：锁定单行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持
  • 临建锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据及数据前面的间隙。在RR隔离级别下支持

行锁

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 排他锁（X）：允许获取排他锁的事务执行更新数据，阻止其他事务获取相同数据集的共享锁和排他锁
SQL 行锁类型 说明
INSERT 排他锁 自动加锁
UPDATE 排他锁 自动加锁
DELETE 排他锁 自动加锁
SELECT 不加任何锁
SELECT … LOCK IN SHARE MODE 共享锁 需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE 排他锁 需要手动在SELECT之后加FOR UPDATE

InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

间隙锁/临建锁

默认情况下，InnoDB在RR隔离级别运行，使用临建锁进行搜索和索引扫描，以防止幻读。

  1. 索引上的等值查询（唯一索引），给已存在的记录加锁时，优化为行锁。

  2. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。

  3. 索引上的等值查询（普通索引），临建锁锁住自身和前面数据（到不满足查询需求的数据）的同时，会额外创建间隙锁，锁住后面的数据（到不满足查询需求的数据）。

  4. 索引上的范围查询（唯一索引），会创建临建锁。

注意：间隙锁唯一的目的是防止其他事务插入间隙，产生幻读。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

事务日志

  • 事务的隔离性由锁机制实现
  • 而事务的原子性、一致性和持久性由事务的redo日志和undo日志保证
    • REDO LOG被称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性
    • UNDO LOG被称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性

Redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定频率被刷入磁盘（checkpoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快

为什么需要Redo日志

checkpoint并不是每次更新的时候就触发，而是主线程隔一段时间去处理的。所以最坏的情况是事务提交后刚写入缓冲池，数据库宕机了，那么这段数据就是丢失的。这也导致事务失去了持久性特征。

InnoDB采用了WAL技术（Write-Ahead Logging），这种技术的思想是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log

优点

  • redo日志降低了刷盘频率

  • redo日志占用的空间非常小

    存储表空间ID、页号、偏移量以及需要更新的值，所需的磁盘空间是很小的，刷盘快

特点

  • redo日志是顺序写入磁盘的

    执行事务的过程中可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO，效率高于随机IO

  • 事务执行过程中，redo日志不断记录

    redo日志和bin日志的区别，redo日志是存储引擎层产生的，而bin日志是数据库层产生的。假设一个事务，对表不断的操作，这个过程中redo日志不断在记录，而bin日志只有在事务提交后一次性写入

Redo日志组成

  • 重做日志的缓存（redo log buffer），保存在内存中

    在服务器启动时就向操作系统申请了一大片称为redo log buffer的连续内存空间（日志缓冲区）。这片内存空间被划分为若干个连续的redo log block，一个redo log block占用512字节大小

  • 重做日志文件（redo log file），保存在磁盘中

Redo日志刷盘策略

https://www.bilibili.com/video/BV1iq4y1u7vj?p=170

Undo日志

在事务中更新数据的前置操作其实是先写入一个undo log。

出现意外情况时，我们需要把数据改回原先的样子，这个过程称之为回滚，这样就可以符合原子性要求。

MySQL把这些为了回滚而记录的内容称为撤销日志或者回滚日志，即undo log。

注意，由于查询操作并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志。

此外，Undo Log也需要进行持久化操作， 所以Undo Log也会产生Redo Log。由于Undo Log的完整性和可靠性需要Redo Log来保证，因此数据库崩溃时需要先做Redo Log数据恢复， 然后做Undo Log回滚。

Undo日志作用

  1. 回滚数据

  2. MVCC

    当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非阻塞读取

Undo日志的类型

在InnoDB存储引擎中，undo log分为：

  • insert undo log

    记录insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见，故该undo log可以在事务提交后直接删除。

  • update undo log

    记录update和delete操作中产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待清除（purge）线程进行最后的删除

清理线程两个主要作用：清理undo页和清除page里面带有Delete_Bit标识的数据行。

在InnoDB中，事务中的Delete操作实际上并不是真正的删除掉行数据，而是一种Delete Mark操作，在记录上标识Delete_Bit，而不删除记录，真正的删除工作需要后台清理线程去完成。

redo日志和undo日志

MVCC

基本概念

  • 当前读

    读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：

    select … lock in share mode（共享锁），select … for update、update、insert、delete（排他锁）都是一种当前读。

  • 快照读

    简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

    快照读的前提是隔离级别不是Serializable，Serializable级别下的快照读会退化成当前读。

  • MVCC

    多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖数据库记录中的三个隐式字段、undo log日志、ReadView。

实现原理

隐藏字段

  • DB_TRX_ID

    最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID

  • DB_ROLL_PTR

    回滚指针，指向这条记录的上一个版本，用于配合undo log

  • DB_ROW_ID

    隐藏主键， 如果表结构没有指定主键，将会生成该隐藏字段

UNDO LOG版本链

undo日志版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的数据，链表尾部是最早的数据

ReadView

读视图是快照读SQL执行时MVCC提供数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

  • ReadView包含了四个核心字段：
字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id ReadView创建者的事务ID

  • 版本链数据访问规则

    db_trx_id ：代表当前版本事务ID

    1. db_trx_id == creator_trx_id

      可以访问该版本

      说明数据是当前这个事务更改的

    2. db_trx_id < min_trx_id

      可以访问该版本

      说明数据已经提交了

    3. db_trx_id > max_trx_id

      不可以访问该版本

      说明事务是在ReadView生成后才开启的

    4. min_trx_id <= db_trx_id <= max_trx_id

      如果db_trx_id 不在m_ids中，可以访问该版本

      说明数据已经提交了

  • 不同隔离级别，生成ReadView的时机不同：

    • READ COMMITTED：在事务中每一次执行快照读时生成ReadView
    • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=145

≡