MySQL初级

目标

  1. Inonodb
  2. 索引原理
  3. 锁原理
  4. 事务&隔离级别
  5. 日志
  6. 回表
  7. 索引失效&错选索引
  8. orderby
  9. bufferPool
  10. 死锁
  11. 慢SQL排查
  12. join

体系架构

整体架构

体系结构详解：

  • 第一层：网络连接层
    • 一些客户端和链接服务，包含本地 Socket 通信和大多数基于客户端/服务端工具实现的 TCP/IP 通信，主要完成一些类似于连接处理、授权认证、及相关的安全方案
    • 在该层上引入了连接池 Connection Pool 的概念，管理缓冲用户连接，线程处理等需要缓存的需求
    • 在该层上实现基于 SSL 的安全链接，服务器也会为安全接入的每个客户端验证它所具有的操作权限
  • 第二层：核心服务层
    • 查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，所有的内置函数（日期、数学、加密函数等）
      • Management Serveices & Utilities：系统管理和控制工具，备份、安全、复制、集群等
      • SQL Interface：接受用户的 SQL 命令，并且返回用户需要查询的结果
      • Parser：SQL 语句分析器
      • Optimizer：查询优化器
      • Caches & Buffers：查询缓存，服务器会查询内部的缓存，如果缓存空间足够大，可以在大量读操作的环境中提升系统性能
    • 所有跨存储引擎的功能在这一层实现，如存储过程、触发器、视图等
    • 在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询顺序，是否利用索引等， 最后生成相应的执行操作
    • MySQL 中服务器层不管理事务，事务是由存储引擎实现的
  • 第三层：存储引擎层
    • Pluggable Storage Engines：存储引擎接口，MySQL 区别于其他数据库的重要特点就是其存储引擎的架构模式是插件式的（存储引擎是基于表的，而不是数据库）
    • 存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信
    • 不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取合适的存储引擎
  • 第四层：系统文件层
    • 数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互
    • File System：文件系统，保存配置文件、数据文件、日志文件、错误文件、二进制文件等

工作流程

当执行完全相同的 SQL 语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存

查询过程：

  1. 客户端发送一条查询给服务器
  2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果（一般是 K-V 键值对），否则进入下一阶段
  3. 分析器进行 SQL 分析，再由优化器生成对应的执行计划
  4. MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
  5. 将结果返回给客户端

大多数情况下不建议使用查询缓存，因为查询缓存往往弊大于利

  • 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能费力地把结果存起来，还没使用就被一个更新全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低
  • 除非业务就是有一张静态表，很长时间才会更新一次，比如一个系统配置表，那这张表上的查询才适合使用查询缓存

优化器

成本分析

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

  • 根据搜索条件找出所有可能的使用的索引
  • 成本分析，执行成本由 I/O 成本和 CPU 成本组成，计算全表扫描和使用不同索引执行 SQL 的代价
  • 找到一个最优的执行方案，用最小的代价去执行语句

在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少意味着访问磁盘的次数越少，消耗的 CPU 资源越少，优化器还会结合是否使用临时表、是否排序等因素进行综合判断

统计数据

MySQL 中保存着两种统计数据：

  • innodb_table_stats 存储了表的统计数据，每一条记录对应着一个表的统计数据
  • innodb_index_stats 存储了索引的统计数据，每一条记录对应着一个索引的一个统计项的数据

MySQL 在真正执行语句之前，并不能精确地知道满足条件的记录有多少条，只能根据统计信息来估算记录，统计信息就是索引的区分度,一个索引上不同的值的个数（比如性别只能是男女，就是 2 ），称之为基数（cardinality），基数越大说明区分度越好

通过采样统计来获取基数，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数

在 MySQL 中，有两种存储统计数据的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

  • ON：表示统计信息会持久化存储（默认），采样页数 N 默认为 20，可以通过 innodb_stats_persistent_sample_pages 指定，页数越多统计的数据越准确，但消耗的资源更大
  • OFF：表示统计信息只存储在内存，采样页数 N 默认为 8，也可以通过系统变量设置（不推荐，每次重新计算浪费资源）

数据表是会持续更新的，两种统计信息的更新方式：

  • 设置 innodb_stats_auto_recalc 为 1，当发生变动的记录数量超过表大小的 10% 时，自动触发重新计算，不过是异步进行
  • 调用 ANALYZE TABLE t 手动更新统计信息，只对信息做重新统计（不是重建表），没有修改数据，这个过程中加了 MDL 读锁并且是同步进行，所以会暂时阻塞系统

EXPLAIN 执行计划在优化器阶段生成，如果 explain 的结果预估的 rows 值跟实际情况差距比较大，可以执行 analyze 命令重新修正信息

错选索引

采样统计本身是估算数据，或者 SQL 语句中的字段选择有问题时，可能导致 MySQL 没有选择正确的执行索引

解决方法：

  • 采用 force index 强行选择一个索引

    1
    SELECT * FROM user FORCE INDEX(name) WHERE NAME='seazean';

  • 可以考虑修改 SQL 语句，引导 MySQL 使用期望的索引

  • 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

终止流程

终止语句

终止线程中正在执行的语句：

1
KILL QUERY thread_id

KILL 不是马上终止的意思，而是告诉执行线程这条语句已经不需要继续执行，可以开始执行停止的逻辑（类似于打断）。因为对表做增删改查操作，会在表上加 MDL 读锁，如果线程被 KILL 时就直接终止，那这个 MDL 读锁就没机会被释放了

命令 KILL QUERYthread_id_A 的执行流程：

  • 把 session A 的运行状态改成 THD::KILL_QUERY（将变量 killed 赋值为 THD::KILL_QUERY）
  • 给 session A 的执行线程发一个信号，让 session A 来处理这个 THD::KILL_QUERY 状态

会话处于等待状态（锁阻塞），必须满足是一个可以被唤醒的等待，必须有机会去判断线程的状态，如果不满足就会造成 KILL 失败

典型场景：innodb_thread_concurrency 为 2，代表并发线程上限数设置为 2

  • session A 执行事务，session B 执行事务，达到线程上限；此时 session C 执行事务会阻塞等待，session D 执行 kill query C 无效
  • C 的逻辑是每 10 毫秒判断是否可以进入 InnoDB 执行，如果不行就调用 nanosleep 函数进入 sleep 状态，没有去判断线程状态

补充：执行 Ctrl+C 的时候，是 MySQL 客户端另外启动一个连接，然后发送一个 KILL QUERY 命令

终止连接

断开线程的连接：

1
KILL CONNECTION id

断开连接后执行 SHOW PROCESSLIST 命令，如果这条语句的 Command 列显示 Killed，代表线程的状态是 KILL_CONNECTION，说明这个线程有语句正在执行，当前状态是停止语句执行中，终止逻辑耗时较长

  • 超大事务执行期间被 KILL，这时回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长
  • 大查询回滚，如果查询过程中生成了比较大的临时文件，删除临时文件可能需要等待 IO 资源，导致耗时较长
  • DDL 命令执行到最后阶段被 KILL，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久

总结：KILL CONNECTION 本质上只是把客户端的 SQL 连接断开，后面的终止流程还是要走 KILL QUERY

一个事务被 KILL 之后，持续处于回滚状态，不应该强行重启整个 MySQL 进程，应该等待事务自己执行完成，因为重启后依然继续做回滚操作的逻辑

存储引擎

基本介绍

对比其他数据库，MySQL 的架构可以在不同场景应用并发挥良好作用，主要体现在存储引擎，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离，可以针对不同的存储需求可以选择最优的存储引擎

存储引擎的介绍：

  • MySQL 数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平等不同的功能和能力，在 MySQL 中，将这些不同的技术及配套的功能称为存储引擎
  • Oracle、SqlServer 等数据库只有一种存储引擎，MySQL 提供了插件式的存储引擎架构，所以 MySQL 存在多种存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能
  • 在关系型数据库中数据的存储是以表的形式存进行，所以存储引擎也称为表类型（存储和操作此表的类型）
  • 通过选择不同的引擎，能够获取最佳的方案, 也能够获得额外的速度或者功能，提高程序的整体效果。

MySQL 支持的存储引擎：

  • MySQL 支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE 等
  • MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后就改为了 InnoDB

引擎对比

MyISAM 存储引擎：

  • 特点：不支持事务和外键，读取速度快，节约资源
  • 应用场景：查询和插入操作为主，只有很少更新和删除操作，并对事务的完整性、并发性要求不高
  • 存储方式：
    • 每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，拓展名不同
    • 表的定义保存在 .frm 文件，表数据保存在 .MYD (MYData) 文件中，索引保存在 .MYI (MYIndex) 文件中

InnoDB 存储引擎：(MySQL5.5 版本后默认的存储引擎)

  • 特点：支持事务和外键操作，支持并发控制。对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引
  • 应用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作
  • 存储方式：
    • 使用共享表空间存储， 这种方式创建的表的表结构保存在 .frm 文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件
    • 使用多表空间存储，创建的表的表结构存在 .frm 文件中，每个表的数据和索引单独保存在 .ibd 中

MERGE 存储引擎：

  • 特点：

    • 是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，通过将不同的表分布在多个磁盘上
    • MERGE 表本身并没有存储数据，对 MERGE 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行的

  • 应用场景：将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用他们，适合做数据仓库

  • 操作方式：

    • 插入操作是通过 INSERT_METHOD 子句定义插入的表，使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上；不定义这个子句或者定义为 NO，表示不能对 MERGE 表执行插入操作
    • 对 MERGE 表进行 DROP 操作，但是这个操作只是删除 MERGE 表的定义，对内部的表是没有任何影响的
特性 MyISAM InnoDB MEMORY
存储限制 有（平台对文件系统大小的限制） 64TB 有（平台的内存限制）
事务安全 不支持 支持 不支持
锁机制 表锁 表锁/行锁 表锁
B+Tree 索引 支持 支持 支持
哈希索引 不支持 不支持 支持
全文索引 支持 支持 不支持
集群索引 不支持 支持 不支持
数据索引 不支持 支持 支持
数据缓存 不支持 支持 N/A
索引缓存 支持 支持 N/A
数据可压缩 支持 不支持 不支持
空间使用 低 高 N/A
内存使用 低 高 中等
批量插入速度 高 低 高
外键 不支持 支持 不支持

MyISAM 和 InnoDB 的区别？

  • 事务：InnoDB 支持事务，MyISAM 不支持事务
  • 外键：InnoDB 支持外键，MyISAM 不支持外键
  • 索引：InnoDB 是聚集（聚簇）索引，MyISAM 是非聚集（非聚簇）索引
  • 锁粒度：InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁
  • 存储结构：参考本节上半部分

引擎操作

  • 查询数据库支持的存储引擎

    1
    2
    SHOW ENGINES;
    SHOW VARIABLES LIKE '%storage_engine%'; -- 查看Mysql数据库默认的存储引擎

  • 查询某个数据库中所有数据表的存储引擎

    1
    SHOW TABLE STATUS FROM 数据库名称;

  • 查询某个数据库中某个数据表的存储引擎

    1
    SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';

  • 创建数据表，指定存储引擎

    1
    2
    3
    4
    CREATE TABLE 表名(
    	列名,数据类型,
        ...
    )ENGINE = 引擎名称;

  • 修改数据表的存储引擎

    1
    ALTER TABLE 表名 ENGINE = 引擎名称;

索引机制

索引介绍

基本介绍

MySQL 官方对索引的定义为：索引（index）是帮助 MySQL 高效获取数据的一种数据结构，本质是排好序的快速查找数据结构。在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样

索引使用：一张数据表，用于保存数据；一个索引配置文件，用于保存索引；每个索引都指向了某一个数据

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据的物理地址的指针，这样就可以运用二叉查找快速获取到相应数据

索引的优点：

  • 类似于书籍的目录索引，提高数据检索的效率，降低数据库的 IO 成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗

索引的缺点：

  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
  • 虽然索引大大提高了查询效率，同时却也降低更新表的速度。对表进行 INSERT、UPDATE、DELETE 操作，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，还会调整因为更新所带来的键值变化后的索引信息，但是更新数据也需要先从数据库中获取，索引加快了获取速度，所以可以相互抵消一下。
  • 索引会影响到 WHERE 的查询条件和排序 ORDER BY 两大功能

索引分类

索引一般的分类如下：

  • 功能分类

    • 主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引
    • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引（普通索引）
    • 联合索引：顾名思义，就是将单列索引进行组合
    • 唯一索引：索引列的值必须唯一，允许有空值，如果是联合索引，则列值组合必须唯一
      • NULL 值可以出现多次，因为两个 NULL 比较的结果既不相等，也不不等，结果仍然是未知
      • 可以声明不允许存储 NULL 值的非空唯一索引
    • 外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作

  • 结构分类

    • BTree 索引：MySQL 使用最频繁的一个索引数据结构，是 InnoDB 和 MyISAM 存储引擎默认的索引类型，底层基于 B+Tree
    • Hash 索引：MySQL中 Memory 存储引擎默认支持的索引类型
    • R-tree 索引（空间索引）：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型

    • Full-text 索引（全文索引）：快速匹配全部文档的方式。MyISAM 支持， InnoDB 不支持 FULLTEXT 类型的索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，MEMORY 引擎不支持

      | 索引 | InnoDB | MyISAM | Memory |
      | ————- | ———————— | ——— | ——— |
      | BTREE | 支持 | 支持 | 支持 |
      | HASH | 不支持 | 不支持 | 支持 |
      | R-tree | 不支持 | 支持 | 不支持 |
      | Full-text | 5.6 版本之后支持 | 支持 | 不支持 |

联合索引图示：根据身高年龄建立的组合索引（height,age）

索引操作

索引在创建表的时候可以同时创建， 也可以随时增加新的索引

  • 创建索引：如果一个表中有一列是主键，那么会默认为其创建主键索引（主键列不需要单独创建索引）

    1
    2
    CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] ON 表名(列名...);
    -- 索引类型默认是 B+TREE

  • 查看索引

    1
    SHOW INDEX FROM 表名;

  • 添加索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 单列索引
    ALTER TABLE 表名 ADD INDEX 索引名称(列名);

    -- 组合索引
    ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

    -- 主键索引
    ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

    -- 外键索引(添加外键约束，就是外键索引)
    ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

    -- 唯一索引
    ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

    -- 全文索引(mysql只支持文本类型)
    ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

  • 删除索引

    1
    DROP INDEX 索引名称 ON 表名;

聚簇索引

对比

聚簇索引是一种数据存储方式，并不是一种单独的索引类型

  • 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引

  • 非聚簇索引的叶子节点存放的是主键值或指向数据行的指针（由存储引擎决定）

在 Innodb 下主键索引是聚簇索引，在 MyISAM 下主键索引是非聚簇索引

Innodb

聚簇索引

在 Innodb 存储引擎，B+ 树索引可以分为聚簇索引（也称聚集索引、clustered index）和辅助索引（也称非聚簇索引或二级索引、secondary index、non-clustered index）

InnoDB 中，聚簇索引是按照每张表的主键构造一颗 B+ 树，叶子节点中存放的就是整张表的数据，将聚簇索引的叶子节点称为数据页

  • 这个特性决定了数据也是索引的一部分，所以一张表只能有一个聚簇索引
  • 辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

聚簇索引的优点：

  • 数据访问更快，聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
  • 聚簇索引对于主键的排序查找和范围查找速度非常快

聚簇索引的缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序（递增）插入是最快的方式，否则将会出现页分裂，严重影响性能，所以对于 InnoDB 表，一般都会定义一个自增的 ID 列为主键

  • 更新主键的代价很高，将会导致被更新的行移动，所以对于 InnoDB 表，一般定义主键为不可更新

  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

辅助索引

在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引等

辅助索引叶子节点存储的是主键值，而不是数据的物理地址，所以访问数据需要二次查找，推荐使用覆盖索引，可以减少回表查询

检索过程：辅助索引找到主键值，再通过聚簇索引（二分）找到数据页，最后通过数据页中的 Page Directory（二分）找到对应的数据分组，遍历组内所所有的数据找到数据行

补充：无索引走全表查询，查到数据页后和上述步骤一致

索引实现

InnoDB 使用 B+Tree 作为索引结构，并且 InnoDB 一定有索引

主键索引：

  • 在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这个索引的 key 是数据表的主键，叶子节点 data 域保存了完整的数据记录

  • InnoDB 的表数据文件通过主键聚集数据，如果没有定义主键，会选择非空唯一索引代替，如果也没有这样的列，MySQL 会自动为 InnoDB 表生成一个隐含字段 row_id 作为主键，这个字段长度为 6 个字节，类型为长整形

辅助索引：

  • InnoDB 的所有辅助索引（二级索引）都引用主键作为 data 域

  • InnoDB 表是基于聚簇索引建立的，因此 InnoDB 的索引能提供一种非常快速的主键查找性能。不过辅助索引也会包含主键列，所以不建议使用过长的字段作为主键，过长的主索引会令辅助索引变得过大

MyISAM

非聚簇

MyISAM 的主键索引使用的是非聚簇索引，索引文件和数据文件是分离的，索引文件仅保存数据的地址

  • 主键索引 B+ 树的节点存储了主键，辅助键索引 B+ 树存储了辅助键，表数据存储在独立的地方，这两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别
  • 由于索引树是独立的，通过辅助索引检索无需回表查询访问主键的索引树

索引实现

MyISAM 的索引方式也叫做非聚集的，之所以这么称呼是为了与 InnoDB 的聚集索引区分

主键索引：MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址

辅助索引：MyISAM 中主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复

参考文章：https://blog.csdn.net/lm1060891265/article/details/81482136

索引结构

数据页

文件系统的最小单元是块（block），一个块的大小是 4K，系统从磁盘读取数据到内存时是以磁盘块为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么

InnoDB 存储引擎中有页（Page）的概念，页是 MySQL 磁盘管理的最小单位

  • InnoDB 存储引擎中默认每个页的大小为 16KB，索引中一个节点就是一个数据页，所以会一次性读取 16KB 的数据到内存
  • InnoDB 引擎将若干个地址连接磁盘块，以此来达到页的大小 16KB
  • 在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率

数据页物理结构，从上到下：

  • File Header：上一页和下一页的指针、该页的类型（索引页、数据页、日志页等）、校验和、LSN（最近一次修改当前页面时的系统 lsn 值，事务持久性部分详解）等信息
  • Page Header：记录状态信息
  • Infimum + Supremum：当前页的最小记录和最大记录（头尾指针），Infimum 所在分组只有一条记录，Supremum 所在分组可以有 1 ~ 8 条记录，剩余的分组可以有 4 ~ 8 条记录
  • User Records：存储数据的记录
  • Free Space：尚未使用的存储空间
  • Page Directory：分组的目录，可以通过目录快速定位（二分法）数据的分组
  • File Trailer：检验和字段，在刷脏过程中，页首和页尾的校验和一致才能说明页面刷新成功，二者不同说明刷新期间发生了错误；LSN 字段，也是用来校验页面的完整性

数据页中包含数据行，数据的存储是基于数据行的，数据行有 next_record 属性指向下一个行数据，所以是可以遍历的，但是一组数据至多 8 个行，通过 Page Directory 先定位到组，然后遍历获取所需的数据行即可

数据行中有三个隐藏字段：trx_id、roll_pointer、row_id（在事务章节会详细介绍它们的作用）

BTree

BTree 的索引类型是基于 B+Tree 树型数据结构的，B+Tree 又是 BTree 数据结构的变种，用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序

BTree 又叫多路平衡搜索树，一颗 m 叉的 BTree 特性如下：

  • 树中每个节点最多包含 m 个孩子
  • 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)] 个孩子
  • 若根节点不是叶子节点，则至少有两个孩子
  • 所有的叶子节点都在同一层
  • 每个非叶子节点由 n 个 key 与 n+1 个指针组成，其中 [ceil(m/2)-1] <= n <= m-1

5 叉，key 的数量 [ceil(m/2)-1] <= n <= m-1 为 2 <= n <=4 ，当 n>4 时中间节点分裂到父节点，两边节点分裂

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据的工作流程：

  • 插入前 4 个字母 C N G A

  • 插入 H，n>4，中间元素 G 字母向上分裂到新的节点

  • 插入 E、K、Q 不需要分裂

  • 插入 M，中间元素 M 字母向上分裂到父节点 G

  • 插入 F，W，L，T 不需要分裂

  • 插入 Z，中间元素 T 向上分裂到父节点中

  • 插入 D，中间元素 D 向上分裂到父节点中，然后插入 P，R，X，Y 不需要分裂

  • 最后插入 S，NPQR 节点 n>5，中间节点 Q 向上分裂，但分裂后父节点 DGMT 的 n>5，中间节点 M 向上分裂

BTree 树就已经构建完成了，BTree 树和二叉树相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTree 的层级结构比二叉树少，所以搜索速度快

BTree 结构的数据可以让系统高效的找到数据所在的磁盘块，定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key 值互不相同，BTree 中的每个节点根据实际情况可以包含大量的关键字信息和分支

缺点：当进行范围查找时会出现回旋查找

B+Tree

数据结构

BTree 数据结构中每个节点中不仅包含数据的 key 值，还有 data 值。磁盘中每一页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率，所以引入 B+Tree

B+Tree 为 BTree 的变种，B+Tree 与 BTree 的区别为：

  • n 叉 B+Tree 最多含有 n 个 key（哈希值），而 BTree 最多含有 n-1 个 key
  • 所有非叶子节点只存储键值 key 信息，只进行数据索引，使每个非叶子节点所能保存的关键字大大增加
  • 所有数据都存储在叶子节点，所以每次数据查询的次数都一样
  • 叶子节点按照 key 大小顺序排列，左边结尾数据都会保存右边节点开始数据的指针，形成一个链表
  • 所有节点中的 key 在叶子节点中也存在（比如 5)，key 允许重复，B 树不同节点不存在重复的 key

B* 树：是 B+ 树的变体，在 B+ 树的非根和非叶子结点再增加指向兄弟的指针

优化结构

MySQL 索引数据结构对经典的 B+Tree 进行了优化，在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能，防止回旋查找

区间访问的意思是访问索引为 5 - 15 的数据，可以直接根据相邻节点的指针遍历

B+ 树的叶子节点是数据页（page），一个页里面可以存多个数据行

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。可以对 B+Tree 进行两种查找运算：

  • 有范围：对于主键的范围查找和分页查找
  • 有顺序：从根节点开始，进行随机查找，顺序查找

InnoDB 中每个数据页的大小默认是 16KB，

  • 索引行：一般表的主键类型为 INT（4 字节）或 BIGINT（8 字节），指针大小在 InnoDB 中设置为 6 字节节，也就是说一个页大概存储 16KB/(8B+6B)=1K 个键值（估值）。则一个深度为 3 的 B+Tree 索引可以维护 10^3 * 10^3 * 10^3 = 10亿 条记录
  • 数据行：一行数据的大小可能是 1k，一个数据页可以存储 16 行

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2-4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作

B+Tree 优点：提高查询速度，减少磁盘的 IO 次数，树形结构较小

索引维护

B+ 树为了保持索引的有序性，在插入新值的时候需要做相应的维护

每个索引中每个块存储在磁盘页中，可能会出现以下两种情况：

  • 如果所在的数据页已经满了，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂，原本放在一个页的数据现在分到两个页中，降低了空间利用率
  • 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做页合并，合并的过程可以认为是分裂过程的逆过程
  • 这两个情况都是由 B+ 树的结构决定的

一般选用数据小的字段做索引，字段长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

自增主键的插入数据模式，可以让主键索引尽量地保持递增顺序插入，不涉及到挪动其他记录，避免了页分裂

设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率

创建索引时的原则：

  • 对查询频次较高，且数据量比较大的表建立索引
  • 使用唯一索引，区分度越高，使用索引的效率越高
  • 索引字段的选择，最佳候选列应当从 where 子句的条件中提取，使用覆盖索引
  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率
  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价越高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗；另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但提高了选择的代价

  • MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

    N 个列组合而成的组合索引，相当于创建了 N 个索引，如果查询时 where 句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 对name、address、phone列建一个联合索引
    ALTER TABLE user ADD INDEX index_three(name,address,phone);
    -- 查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引进行数据匹配。
    (name,address,phone)
    (name,address)
    (name,phone)	-- 只有name字段走了索引
    (name)

    -- 索引的字段可以是任意顺序的，优化器会帮助我们调整顺序，下面的SQL语句可以命中索引
    SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
    1
    2
    -- 如果联合索引中最左边的列不包含在条件查询中，SQL语句就不会命中索引，比如：
    SELECT * FROM user WHERE address = '北京' AND phone = '12345';

哪些情况不要建立索引：

  • 记录太少的表
  • 经常增删改的表
  • 频繁更新的字段不适合创建索引
  • where 条件里用不到的字段不创建索引

索引相关概念

覆盖索引

覆盖索引：包含所有满足查询需要的数据的索引（SELECT 后面的字段刚好是索引字段），可以利用该索引返回 SELECT 列表的字段，而不必根据索引去聚簇索引上读取数据文件

回表查询：要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据

使用覆盖索引，防止回表查询：

  • 表 user 主键为 id，普通索引为 age，查询语句：

    1
    SELECT * FROM user WHERE age = 30;

    查询过程：先通过普通索引 age=30 定位到主键值 id=1，再通过聚集索引 id=1 定位到行记录数据，需要两次扫描 B+ 树

  • 使用覆盖索引：

    1
    2
    3
    DROP INDEX idx_age ON user;
    CREATE INDEX idx_age_name ON user(age,name);
    SELECT id,age FROM user WHERE age = 30;

    在一棵索引树上就能获取查询所需的数据，无需回表速度更快

使用覆盖索引，要注意 SELECT 列表中只取出需要的列，不可用 SELECT *，所有字段一起做索引会导致索引文件过大，查询性能下降

索引下推

索引条件下推优化（Index Condition Pushdown，ICP）是 MySQL5.6 添加，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引下推充分利用了索引中的数据，在查询出整行数据之前过滤掉无效的数据，再去主键索引树上查找

  • 不使用索引下推优化时存储引擎通过索引检索到数据，然后回表查询记录返回给 Server 层，服务器判断数据是否符合条件

  • 使用索引下推优化时，如果存在某些被索引的列的判断条件时，由存储引擎在索引遍历的过程中判断数据是否符合传递的条件，将符合条件的数据进行回表，检索出来返回给服务器，由此减少 IO 次数

适用条件：

  • 需要存储引擎将索引中的数据与条件进行判断（所以条件列必须都在同一个索引中），所以优化是基于存储引擎的，只有特定引擎可以使用，适用于 InnoDB 和 MyISAM
  • 存储引擎没有调用跨存储引擎的能力，跨存储引擎的功能有存储过程、触发器、视图，所以调用这些功能的不可以进行索引下推优化
  • 对于 InnoDB 引擎只适用于二级索引，InnoDB 的聚簇索引会将整行数据读到缓冲区，不再需要去回表查询了，索引下推的目的减少回表的 IO 次数也就失去了意义

工作过程：用户表 user，(name, age) 是联合索引

1
SELECT * FROM user WHERE name LIKE '张%' AND　age = 10;	-- 头部模糊匹配会造成索引失效

  • 优化前：在非主键索引树上找到满足第一个条件的行，然后通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据，再对比 AND 后的条件是否符合，符合返回数据，需要 4 次回表

  • 优化后：检查索引中存储的列信息是否符合索引条件，然后交由存储引擎用剩余的判断条件判断此行数据是否符合要求，不满足条件的不去读取表中的数据，满足下推条件的就根据主键值进行回表查询，2 次回表

当使用 EXPLAIN 进行分析时，如果使用了索引条件下推，Extra 会显示 Using index condition

参考文章：https://blog.csdn.net/sinat_29774479/article/details/103470244

参考文章：https://time.geekbang.org/column/article/69636

前缀索引

当要索引的列字符很多时，索引会变大变慢，可以只索引列开始的部分字符串，节约索引空间，提高索引效率

注意：使用前缀索引就系统就忽略覆盖索引对查询性能的优化了

优化原则：降低重复的索引值

比如地区表：

1
2
3
4
5
6
area			gdp		code
chinaShanghai	100		aaa
chinaDalian		200		bbb
usaNewYork		300		ccc
chinaFuxin		400		ddd
chinaBeijing	500		eee

发现 area 字段很多都是以 china 开头的，那么如果以前 1-5 位字符做前缀索引就会出现大量索引值重复的情况，索引值重复性越低，查询效率也就越高，所以需要建立前 6 位字符的索引：

1
CREATE INDEX idx_area ON table_name(area(7));

场景：存储身份证

  • 直接创建完整索引，这样可能比较占用空间
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
  • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题（前 6 位相同的很多）
  • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

索引合并

使用多个索引来完成一次查询的执行方法叫做索引合并 index merge

  • Intersection 索引合并：

    1
    SELECT * FROM table_test WHERE key1 = 'a' AND key3 = 'b'; # key1 和 key3 列都是单列索引、二级索引

    从不同索引中扫描到的记录的 id 值取交集（相同 id），然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序

  • Union 索引合并：

    1
    SELECT * FROM table_test WHERE key1 = 'a' OR key3 = 'b';

    从不同索引中扫描到的记录的 id 值取并集，然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序

  • Sort-Union 索引合并

    1
    SELECT * FROM table_test WHERE key1 < 'a' OR key3 > 'b';

    先将从不同索引中扫描到的记录的主键值进行排序，再按照 Union 索引合并的方式进行查询

索引合并算法的效率并不好，通过将其中的一个索引改成联合索引会优化效率

索引优化

创建索引

索引是数据库优化最重要的手段之一，通过索引通常可以帮助用户解决大多数的 MySQL 的性能优化问题

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    PRIMARY KEY(`sellerid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(name, status, address); # 联合索引

避免失效

语句错误

  • 全值匹配：对索引中所有列都指定具体值，这种情况索引生效，执行效率高

    1
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';

  • 最左前缀法则：联合索引遵守最左前缀法则

    匹配最左前缀法则，走索引：

    1
    2
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技';
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1';

    违法最左前缀法则 ， 索引失效：

    1
    2
    EXPLAIN SELECT * FROM tb_seller WHERE status='1';
    EXPLAIN SELECT * FROM tb_seller WHERE status='1' AND address='西安市';

    如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

    1
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND address='西安市';

    虽然索引列失效，但是系统会使用了索引下推进行了优化

  • 范围查询右边的列，不能使用索引：

    1
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status>'1' AND address='西安市';

    根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件 address 没有用到索引，使用了索引下推

  • 在索引列上函数或者运算（+ - 数值）操作， 索引将失效：会破坏索引值的有序性

    1
    EXPLAIN SELECT * FROM tb_seller WHERE SUBSTRING(name,3,2) = '科技';

  • 字符串不加单引号，造成索引失效：隐式类型转换，当字符串和数字比较时会把字符串转化为数字

    在查询时，没有对字符串加单引号，查询优化器会调用 CAST 函数将 status 转换为 int 进行比较，造成索引失效

    1
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status = 1;

    如果 status 是 int 类型，SQL 为 SELECT * FROM tb_seller WHERE status = '1' 并不会造成索引失效，因为会将 '1' 转换为 1，并不会对索引列产生操作

  • 多表连接查询时，如果两张表的字符集不同，会造成索引失效，因为会进行类型转换

    解决方法：CONVERT 函数是加在输入参数上、修改表的字符集

  • 用 OR 分割条件，索引失效，导致全表查询：

    OR 前的条件中的列有索引而后面的列中没有索引或 OR 前后两个列是同一个复合索引，都造成索引失效

    1
    2
    EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' OR createtime = '2088-01-01 12:00:00';
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' OR status='1';

    AND 分割的条件不影响：

    1
    EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' AND createtime = '2088-01-01 12:00:00';

  • 以 % 开头的 LIKE 模糊查询，索引失效：

    如果是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效

    1
    EXPLAIN SELECT * FROM tb_seller WHERE name like '%科技%';

    解决方案：通过覆盖索引来解决 

    1
    EXPLAIN SELECT sellerid,name,status FROM tb_seller WHERE name like '%科技%';

    原因：在覆盖索引的这棵 B+ 数上只需要进行 like 的匹配，或者是基于覆盖索引查询再进行 WHERE 的判断就可以获得结果

系统优化

系统优化为全表扫描：

  • 如果 MySQL 评估使用索引比全表更慢，则不使用索引，索引失效：

    1
    2
    3
    CREATE INDEX idx_address ON tb_seller(address);
    EXPLAIN SELECT * FROM tb_seller WHERE address='西安市';
    EXPLAIN SELECT * FROM tb_seller WHERE address='北京市';

    北京市的键值占 9/10（区分度低），所以优化为全表扫描，type = ALL

  • IS NULL、IS NOT NULL 有时索引失效：

    1
    2
    EXPLAIN SELECT * FROM tb_seller WHERE name IS NULL;
    EXPLAIN SELECT * FROM tb_seller WHERE name IS NOT NULL;

    NOT NULL 失效的原因是 name 列全部不是 null，优化为全表扫描，当 NULL 过多时，IS NULL 失效

  • IN 肯定会走索引，但是当 IN 的取值范围较大时会导致索引失效，走全表扫描：

    1
    2
    EXPLAIN SELECT * FROM tb_seller WHERE sellerId IN ('alibaba','huawei');-- 都走索引
    EXPLAIN SELECT * FROM tb_seller WHERE sellerId NOT IN ('alibaba','huawei');

  • MySQL 实战 45 讲该章节最后提出了一种场景，获取到数据以后 Server 层还会做判断

底层原理

索引失效一般是针对联合索引，联合索引一般由几个字段组成，排序方式是先按照第一个字段进行排序，然后排序第二个，依此类推，图示（a, b）索引，a 相等的情况下 b 是有序的

  • 最左前缀法则：当不匹配前面的字段的时候，后面的字段都是无序的。这种无序不仅体现在叶子节点，也会导致查询时扫描的非叶子节点也是无序的，因为索引树相当于忽略的第一个字段，就无法使用二分查找

  • 范围查询右边的列，不能使用索引，比如语句： WHERE a > 1 AND b = 1，在 a 大于 1 的时候，b 是无序的，a > 1 是扫描时有序的，但是找到以后进行寻找 b 时，索引树就不是有序的了

  • 以 % 开头的 LIKE 模糊查询，索引失效，比如语句：WHERE a LIKE '%d'，前面的不确定，导致不符合最左匹配，直接去索引中搜索以 d 结尾的节点，所以没有顺序

      ![](../image/post/MySQL-%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%BA%95%E5%B1%82%E5%8E%9F%E7%90%863.png)

参考文章：https://mp.weixin.qq.com/s/B_M09dzLe9w7cT46rdGIeQ

查看索引

1
2
SHOW STATUS LIKE 'Handler_read%';	
SHOW GLOBAL STATUS LIKE 'Handler_read%';

  • Handler_read_first：索引中第一条被读的次数，如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）
  • Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，值越低表示索引不经常使用（这个值越高越好）
  • Handler_read_next：按照键顺序读下一行的请求数，如果范围约束或执行索引扫描来查询索引列，值增加
  • Handler_read_prev：按照键顺序读前一行的请求数，该读方法主要用于优化 ORDER BY … DESC
  • Handler_read_rnd：根据固定位置读一行的请求数，如果执行大量查询并对结果进行排序则该值较高，可能是使用了大量需要 MySQL 扫描整个表的查询或连接，这个值较高意味着运行效率低，应该建立索引来解决
  • Handler_read_rnd_next：在数据文件中读下一行的请求数，如果正进行大量的表扫描，该值较高，说明表索引不正确或写入的查询没有利用索引

事务机制

基本介绍

事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个 SQL 语句，这些语句要么都执行，要么都不执行，作为一个关系型数据库，MySQL 支持事务。

单元中的每条 SQL 语句都相互依赖，形成一个整体

  • 如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态

  • 如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行

事务的四大特征：ACID

  • 原子性 (atomicity)
  • 一致性 (consistency)
  • 隔离性 (isolaction)
  • 持久性 (durability)

事务的几种状态：

  • 活动的（active）：事务对应的数据库操作正在执行中
  • 部分提交的（partially committed）：事务的最后一个操作执行完，但是内存还没刷新至磁盘
  • 失败的（failed）：当事务处于活动状态或部分提交状态时，如果数据库遇到了错误或刷脏失败，或者用户主动停止当前的事务
  • 中止的（aborted）：失败状态的事务回滚完成后的状态
  • 提交的（committed）：当处于部分提交状态的事务刷脏成功，就处于提交状态

事务管理

基本操作

事务管理的三个步骤

  1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败

  2. 执行 SQL 语句：执行具体的一条或多条 SQL 语句

  3. 结束事务（提交|回滚）

    • 提交：没出现问题，数据进行更新
    • 回滚：出现问题，数据恢复到开启事务时的状态

事务操作：

  • 显式开启事务

    1
    2
    START TRANSACTION [READ ONLY|READ WRITE|WITH CONSISTENT SNAPSHOT]; #可以跟一个或多个状态，最后的是一致性读
    BEGIN [WORK];

    说明：不填状态默认是读写事务

  • 回滚事务，用来手动中止事务

    1
    ROLLBACK;

  • 提交事务，显示执行是手动提交，MySQL 默认为自动提交

    1
    COMMIT;

  • 保存点：在事务的执行过程中设置的还原点，调用 ROLLBACK 时可以指定回滚到哪个点

    1
    2
    3
    SAVEPOINT point_name;						#设置保存点
    RELEASE point_name							#删除保存点
    ROLLBACK [WORK] TO [SAVEPOINT] point_name	#回滚至某个保存点，不填默认回滚到事务执行之前的状态

  • 操作演示

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 开启事务
    START TRANSACTION;

    -- 张三给李四转账500元
    -- 1.张三账户-500
    UPDATE account SET money=money-500 WHERE NAME='张三';
    -- 2.李四账户+500
    UPDATE account SET money=money+500 WHERE NAME='李四';

    -- 回滚事务(出现问题)
    ROLLBACK;

    -- 提交事务(没出现问题)
    COMMIT;

提交方式

提交方式的相关语法：

  • 查看事务提交方式

    1
    2
    SELECT @@AUTOCOMMIT;  		-- 会话，1 代表自动提交    0 代表手动提交
    SELECT @@GLOBAL.AUTOCOMMIT;	-- 系统

  • 修改事务提交方式

    1
    2
    SET @@AUTOCOMMIT=数字;	-- 系统
    SET AUTOCOMMIT=数字;		-- 会话

  • 系统变量的操作：

    1
    2
    SET [GLOBAL|SESSION] 变量名 = 值;					-- 默认是会话
    SET @@[(GLOBAL|SESSION).]变量名 = 值;				-- 默认是系统
    1
    SHOW [GLOBAL|SESSION] VARIABLES [LIKE '变量%'];	  -- 默认查看会话内系统变量值

工作原理：

  • 自动提交：如果没有 START TRANSACTION 显式地开始一个事务，那么每条 SQL 语句都会被当做一个事务执行提交操作；显式开启事务后，会在本次事务结束（提交或回滚）前暂时关闭自动提交
  • 手动提交：不需要显式的开启事务，所有的 SQL 语句都在一个事务中，直到执行了提交或回滚，然后进入下一个事务
  • 隐式提交：存在一些特殊的命令，在事务中执行了这些命令会马上强制执行 COMMIT 提交事务
    • DDL 语句 (CREATE/DROP/ALTER)、LOCK TABLES 语句、LOAD DATA 导入数据语句、主从复制语句等
    • 当一个事务还没提交或回滚，显式的开启一个事务会隐式的提交上一个事务

事务 ID

事务在执行过程中对某个表执行了增删改操作或者创建表，就会为当前事务分配一个独一无二的事务 ID（对临时表并不会分配 ID），如果当前事务没有被分配 ID，默认是 0

说明：只读事务不能对普通的表进行增删改操作，但是可以对临时表增删改，读写事务可以对数据表执行增删改查操作

事务 ID 本质上就是一个数字，服务器在内存中维护一个全局变量：

  • 每当需要为某个事务分配 ID，就会把全局变量的值赋值给事务 ID，然后变量自增 1
  • 每当变量值为 256 的倍数时，就将该变量的值刷新到系统表空间的 Max Trx ID 属性中，该属性占 8 字节
  • 系统再次启动后，会读取表空间的 Max Trx ID 属性到内存，加上 256 后赋值给全局变量，因为关机时的事务 ID 可能并不是 256 的倍数，会比 Max Trx ID 大，所以需要加上 256 保持事务 ID 是一个递增的数字

聚簇索引的行记录除了完整的数据，还会自动添加 trx_id、roll_pointer 隐藏列，如果表中没有主键并且没有非空唯一索引，也会添加一个 row_id 的隐藏列作为聚簇索引

隔离级别

四种级别

事务的隔离级别：多个客户端操作时，各个客户端的事务之间应该是隔离的，不同的事务之间不该互相影响，而如果多个事务操作同一批数据时，则需要设置不同的隔离级别，否则就会产生问题。

隔离级别分类：

隔离级别 名称 会引发的问题 数据库默认隔离级别
Read Uncommitted 读未提交 脏读、不可重复读、幻读
Read Committed 读已提交 不可重复读、幻读 Oracle / SQL Server
Repeatable Read 可重复读 幻读 MySQL
Serializable 可串行化 无

一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差

  • 脏写 (Dirty Write)：当两个或多个事务选择同一行，最初的事务修改的值被后面事务修改的值覆盖，所有的隔离级别都可以避免脏写（又叫丢失更新），因为有行锁

  • 脏读 (Dirty Reads)：在一个事务处理过程中读取了另一个未提交的事务中修改过的数据

  • 不可重复读 (Non-Repeatable Reads)：在一个事务处理过程中读取了另一个事务中修改并已提交的数据

    可重复读的意思是不管读几次，结果都一样，可以重复的读，可以理解为快照读，要读的数据集不会发生变化

  • 幻读 (Phantom Reads)：在事务中按某个条件先后两次查询数据库，后一次查询查到了前一次查询没有查到的行，数据条目发生了变化。比如查询某数据不存在，准备插入此记录，但执行插入时发现此记录已存在，无法插入

隔离级别操作语法：

  • 查询数据库隔离级别

    1
    2
    SELECT @@TX_ISOLATION;			-- 会话
    SELECT @@GLOBAL.TX_ISOLATION;	-- 系统

  • 修改数据库隔离级别

    1
    SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

加锁分析

InnoDB 存储引擎支持事务，所以加锁分析是基于该存储引擎

  • Read Uncommitted 级别，任何操作都不会加锁

  • Read Committed 级别，增删改操作会加写锁（行锁），读操作不加锁

    在 Server 层过滤条件时发现不满足的记录会调用 unlock_row 方法释放该记录的行锁，保证最后只有满足条件的记录加锁，但是扫表过程中每条记录的加锁操作不能省略。所以对数据量很大的表做批量修改时，如果无法使用相应的索引（全表扫描），在Server 过滤数据时就会特别慢，出现虽然没有修改某些行的数据，但是还是被锁住了的现象（锁表），这种情况同样适用于 RR

  • Repeatable Read 级别，增删改操作会加写锁，读操作不加锁。因为读写锁不兼容，加了读锁后其他事务就无法修改数据，影响了并发性能，为了保证隔离性和并发性，MySQL 通过 MVCC 解决了读写冲突。RR 级别下的锁有很多种，锁机制章节详解

  • Serializable 级别，读加共享锁，写加排他锁，读写互斥，使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差

    • 串行化：让所有事务按顺序单独执行，写操作会加写锁，读操作会加读锁
    • 可串行化：让所有操作相同数据的事务顺序执行，通过加锁实现

参考文章：https://tech.meituan.com/2014/08/20/innodb-lock.html

原子特性

实现方式

原子性是指事务是一个不可分割的工作单位，事务的操作如果成功就必须要完全应用到数据库，失败则不能对数据库有任何影响。比如事务中一个 SQL 语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态

InnoDB 存储引擎提供了两种事务日志：redo log（重做日志）和 undo log（回滚日志）

  • redo log 用于保证事务持久性
  • undo log 用于保证事务原子性和隔离性

undo log 属于逻辑日志，根据每行操作进行记录，记录了 SQL 执行相关的信息，用来回滚行记录到某个版本

当事务对数据库进行修改时，InnoDB 会先记录对应的 undo log，如果事务执行失败或调用了 rollback 导致事务回滚，InnoDB 会根据 undo log 的内容做与之前相反的操作：

  • 对于每个 insert，回滚时会执行 delete

  • 对于每个 delete，回滚时会执行 insert

  • 对于每个 update，回滚时会执行一个相反的 update，把数据修改回去

参考文章：https://www.cnblogs.com/kismetv/p/10331633.html

DML 解析

INSERT

乐观插入：当前数据页的剩余空间充足，直接将数据进行插入

悲观插入：当前数据页的剩余空间不足，需要进行页分裂，申请一个新的页面来插入数据，会造成更多的 redo log，undo log 影响不大

当向某个表插入一条记录，实际上需要向聚簇索引和所有二级索引都插入一条记录，但是 undo log 只针对聚簇索引记录，在回滚时会根据聚簇索引去所有的二级索引进行回滚操作

roll_pointer 是一个指针，指向记录对应的 undo log 日志，一条记录就是一个数据行，行格式中的 roll_pointer 就指向 undo log

DELETE

插入到页面中的记录会根据 next_record 属性组成一个单向链表，这个链表称为正常链表，被删除的记录也会通过 next_record 组成一个垃圾链表，该链表中所占用的存储空间可以被重新利用，并不会直接清除数据

在页面 Page Header 中，PAGE_FREE 属性指向垃圾链表的头节点，删除的工作过程：

  • 将要删除的记录的 delete_flag 位置为 1，其他不做修改，这个过程叫 delete mark

  • 在事务提交前，delete_flag = 1 的记录一直都会处于中间状态

  • 事务提交后，有专门的线程将 delete_flag = 1 的记录从正常链表移除并加入垃圾链表，这个过程叫 purge

    purge 线程在执行删除操作时会创建一个 ReadView，根据事务的可见性移除数据（隔离特性部分详解）

当有新插入的记录时，首先判断 PAGE_FREE 指向的头节点是否足够容纳新纪录：

  • 如果可以容纳新纪录，就会直接重用已删除的记录的存储空间，然后让 PAGE_FREE 指向垃圾链表的下一个节点
  • 如果不能容纳新纪录，就直接向页面申请新的空间存储，并不会遍历垃圾链表

重用已删除的记录空间，可能会造成空间碎片，当数据页容纳不了一条记录时，会判断将碎片空间加起来是否可以容纳，判断为真就会重新组织页内的记录：

  • 开辟一个临时页面，将页内记录一次插入到临时页面，此时临时页面时没有碎片的
  • 把临时页面的内容复制到本页，这样就解放出了内存碎片，但是会耗费很大的性能资源
UPDATE

执行 UPDATE 语句，对于更新主键和不更新主键有两种不同的处理方式

不更新主键的情况：

  • 就地更新（in-place update），如果更新后的列和更新前的列占用的存储空间一样大，就可以直接在原记录上修改

  • 先删除旧纪录，再插入新纪录，这里的删除不是 delete mark，而是直接将记录加入垃圾链表，并且修改页面的相应的控制信息，执行删除的线程不是 purge，是执行更新的用户线程，插入新记录时可能造成页空间不足，从而导致页分裂

更新主键的情况：

  • 将旧纪录进行 delete mark，在更新语句提交后由 purge 线程移入垃圾链表
  • 根据更新的各列的值创建一条新纪录，插入到聚簇索引中

在对一条记录修改前会将记录的隐藏列 trx_id 和 roll_pointer 的旧值记录到 undo log 对应的属性中，这样当前记录的 roll_pointer 指向当前 undo log 记录，当前 undo log 记录的 roll_pointer 指向旧的 undo log 记录，形成一个版本链

UPDATE、DELETE 操作产生的 undo 日志可能会用于其他事务的 MVCC 操作，所以不能立即删除

回滚日志

undo log 是采用段的方式来记录，Rollback Segement 称为回滚段，本质上就是一个类型是 Rollback Segement Header 的页面

每个回滚段中有 1024 个 undo slot，每个 slot 存放 undo 链表页面的头节点页号，每个链表对应一个叫 undo log segment 的段

  • 在以前老版本，只支持 1 个 Rollback Segement，只能记录 1024 个 undo log segment
  • MySQL5.5 开始支持 128 个 Rollback Segement，支持 128*1024 个 undo 操作

工作流程：

  • 事务执行前需要到系统表空间第 5 号页面中分配一个回滚段（页），获取一个 Rollback Segement Header 页面的地址

  • 回滚段页面有 1024 个 undo slot，首先去回滚段的两个 cached 链表获取缓存的 slot，缓存中没有就在回滚段页面中找一个可用的 undo slot 分配给当前事务

  • 如果是缓存中获取的 slot，则该 slot 对应的 undo log segment 已经分配了，需要重新分配，然后从 undo log segment 中申请一个页面作为日志链表的头节点，并填入对应的 slot 中

  • 每个事务 undo 日志在记录的时候占用两个 undo 页面的组成链表，分别为 insert undo 链表和 update undo 链表，链表的头节点页面为 first undo page 会包含一些管理信息，其他页面为 normal undo page

    说明：事务执行过程的临时表也需要两个 undo 链表，不和普通表共用，这些链表并不是事务开始就分配，而是按需分配

隔离特性

实现方式

隔离性是指，事务内部的操作与其他事务是隔离的，多个并发事务之间要相互隔离，不能互相干扰

  • 严格的隔离性，对应了事务隔离级别中的 serializable，实际应用中对性能考虑很少使用可串行化

  • 与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响

隔离性让并发情形下的事务之间互不干扰：

  • 一个事务的写操作对另一个事务的写操作（写写）：锁机制保证隔离性
  • 一个事务的写操作对另一个事务的读操作（读写）：MVCC 保证隔离性

锁机制：事务在修改数据之前，需要先获得相应的锁，获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁（详解见锁机制）

并发控制

MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制，用来解决读写冲突的无锁并发控制，可以在发生读写请求冲突时不用加锁解决，这个读是指的快照读（也叫一致性读或一致性无锁读），而不是当前读：

  • 快照读：实现基于 MVCC，因为是多版本并发，所以快照读读到的数据不一定是当前最新的数据，有可能是历史版本的数据
  • 当前读：又叫加锁读，读取数据库记录是当前最新的版本（产生幻读、不可重复读），可以对读取的数据进行加锁，防止其他事务修改数据，是悲观锁的一种操作，读写操作加共享锁或者排他锁和串行化事务的隔离级别都是当前读

数据库并发场景：

  • 读-读：不存在任何问题，也不需要并发控制

  • 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读

  • 写-写：有线程安全问题，可能会存在脏写（丢失更新）问题

MVCC 的优点：

  • 在并发读写数据库时，做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了并发读写的性能
  • 可以解决脏读，不可重复读等事务隔离问题（加锁也能解决），但不能解决更新丢失问题（写锁会解决）

提高读写和写写的并发性能：

  • MVCC + 悲观锁：MVCC 解决读写冲突，悲观锁解决写写冲突
  • MVCC + 乐观锁：MVCC 解决读写冲突，乐观锁解决写写冲突

参考文章：https://www.jianshu.com/p/8845ddca3b23

实现原理

隐藏字段

实现原理主要是隐藏字段，undo日志，Read View 来实现的

InnoDB 存储引擎，数据库中的聚簇索引每行数据，除了自定义的字段，还有数据库隐式定义的字段：

  • DB_TRX_ID：最近修改事务 ID，记录创建该数据或最后一次修改该数据的事务 ID
  • DB_ROLL_PTR：回滚指针，指向记录对应的 undo log 日志，undo log 中又指向上一个旧版本的 undo log
  • DB_ROW_ID：隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以 DB_ROW_ID 作为聚簇索引

版本链

undo log 是逻辑日志，记录的是每个事务对数据执行的操作，而不是记录的全部数据，要根据 undo log 逆推出以往事务的数据

undo log 的作用：

  • 保证事务进行 rollback 时的原子性和一致性，当事务进行回滚的时候可以用 undo log 的数据进行恢复
  • 用于 MVCC 快照读，通过读取 undo log 的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本

undo log 主要分为两种：

  • insert undo log：事务在 insert 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃

  • update undo log：事务在进行 update 或 delete 时产生的 undo log，在事务回滚时需要，在快照读时也需要。不能随意删除，只有在当前读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

每次对数据库记录进行改动，都会产生的新版本的 undo log，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，把这个链表称之为版本链，版本链的头节点就是当前的最新的 undo log，链尾就是最早的旧 undo log

说明：因为 DELETE 删除记录，都是移动到垃圾链表中，不是真正的删除，所以才可以通过版本链访问原始数据

注意：undo 是逻辑日志，这里只是直观的展示出来

工作流程：

  • 有个事务插入 persion 表一条新记录，name 为 Jerry，age 为 24
  • 事务 1 修改该行数据时，数据库会先对该行加排他锁，然后先记录 undo log，然后修改该行 name 为 Tom，并且修改隐藏字段的事务 ID 为当前事务 1 的 ID（默认为 1 之后递增），回滚指针指向拷贝到 undo log 的副本记录，事务提交后，释放锁
  • 以此类推
读视图

Read View 是事务进行读数据操作时产生的读视图，该事务执行快照读的那一刻会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID，用来做可见性判断，根据视图判断当前事务能够看到哪个版本的数据

注意：这里的快照并不是把所有的数据拷贝一份副本，而是由 undo log 记录的逻辑日志，根据库中的数据进行计算出历史数据

工作流程：将版本链的头节点的事务 ID（最新数据事务 ID，大概率不是当前线程）DB_TRX_ID 取出来，与系统当前活跃事务的 ID 对比进行可见性分析，不可见就通过 DB_ROLL_PTR 回滚指针去取出 undo log 中的下一个 DB_TRX_ID 比较，直到找到最近的满足可见性的 DB_TRX_ID，该事务 ID 所在的旧记录就是当前事务能看见的最新的记录

Read View 几个属性：

  • m_ids：生成 Read View 时当前系统中活跃的事务 id 列表（未提交的事务集合，当前事务也在其中）
  • min_trx_id：生成 Read View 时当前系统中活跃的最小的事务 id，也就是 m_ids 中的最小值（已提交的事务集合）
  • max_trx_id：生成 Read View 时当前系统应该分配给下一个事务的 id 值，m_ids 中的最大值加 1（未开始事务）
  • creator_trx_id：生成该 Read View 的事务的事务 id，就是判断该 id 的事务能读到什么数据

creator 创建一个 Read View，进行可见性算法分析：（解决了读未提交）

  • db_trx_id == creator_trx_id：表示这个数据就是当前事务自己生成的，自己生成的数据自己肯定能看见，所以此数据对 creator 是可见的

  • db_trx_id < min_trx_id：该版本对应的事务 ID 小于 Read view 中的最小活跃事务 ID，则这个事务在当前事务之前就已经被提交了，对 creator 可见（因为比已提交的最大事务 ID 小的并不一定已经提交，所以应该先判断是否在活跃事务列表）

  • db_trx_id >= max_trx_id：该版本对应的事务 ID 大于 Read view 中当前系统的最大事务 ID，则说明该数据是在当前 Read view 创建之后才产生的，对 creator 不可见

  • min_trx_id<= db_trx_id < max_trx_id：判断 db_trx_id 是否在活跃事务列表 m_ids 中
    • 在列表中，说明该版本对应的事务正在运行，数据不能显示（不能读到未提交的数据）
    • 不在列表中，说明该版本对应的事务已经被提交，数据可以显示（可以读到已经提交的数据）
工作流程

表 user 数据

1
2
id		name		age
1		张三		   18

Transaction 20：

1
2
3
START TRANSACTION;	-- 开启事务
UPDATE user SET name = '李四' WHERE id = 1;
UPDATE user SET name = '王五' WHERE id = 1;

Transaction 60：

1
2
START TRANSACTION;	-- 开启事务
-- 操作表的其他数据

ID 为 0 的事务创建 Read View：

  • m_ids：20、60
  • min_trx_id：20
  • max_trx_id：61
  • creator_trx_id：0

只有红框部分才复合条件，所以只有张三对应的版本的数据可以被看到

参考视频：https://www.bilibili.com/video/BV1t5411u7Fg

二级索引

只有在聚簇索引中才有 trx_id 和 roll_pointer 的隐藏列，对于二级索引判断可见性的方式：

  • 二级索引页面的 Page Header 中有一个 PAGE_MAX_TRX_ID 属性，代表修改当前页面的最大的事务 ID，SELECT 语句访问某个二级索引时会判断 ReadView 的 min_trx_id 是否大于该属性，大于说明该页面的所有属性对 ReadView 可见
  • 如果属性判断不可见，就需要利用二级索引获取主键值进行回表操作，得到聚簇索引后按照聚簇索引的可见性判断的方法操作

RC RR

Read View 用于支持 RC（Read Committed，读已提交）和 RR（Repeatable Read，可重复读）隔离级别的实现，所以 SELECT 在 RC 和 RR 隔离级别使用 MVCC 读取记录

RR、RC 生成时机：

  • RC 隔离级别下，每次读取数据前都会生成最新的 Read View（当前读）
  • RR 隔离级别下，在第一次数据读取时才会创建 Read View（快照读）

RC、RR 级别下的 InnoDB 快照读区别

  • RC 级别下，事务中每次快照读都会新生成一个 Read View，这就是在 RC 级别下的事务中可以看到别的事务提交的更新的原因

  • RR 级别下，某个事务的对某条记录的第一次快照读会创建一个 Read View， 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，使用的是同一个 Read View，所以一个事务的查询结果每次都是相同的

    RR 级别下，通过 START TRANSACTION WITH CONSISTENT SNAPSHOT 开启事务，会在执行该语句后立刻生成一个 Read View，不是在执行第一条 SELECT 语句时生成（所以说 START TRANSACTION 并不是事务的起点，执行第一条语句才算起点）

解决幻读问题：

  • 快照读：通过 MVCC 来进行控制的，在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，但是并不能完全避免幻读

    场景：RR 级别，T1 事务开启，创建 Read View，此时 T2 去 INSERT 新的一行然后提交，然后 T1 去 UPDATE 该行会发现更新成功，并且把这条新记录的 trx_id 变为当前的事务 id，所以对当前事务就是可见的。因为 Read View 并不能阻止事务去更新数据，更新数据都是先读后写并且是当前读，读取到的是最新版本的数据

  • 当前读：通过 next-key 锁（行锁 + 间隙锁）来解决问题

持久特性

实现方式

持久性是指一个事务一旦被提交了，那么对数据库中数据的改变就是永久性的，接下来的其他操作或故障不应该对其有任何影响。

Buffer Pool 的使用提高了读写数据的效率，但是如果 MySQL 宕机，此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证，所以引入了 redo log 日志：

  • redo log 记录数据页的物理修改，而不是某一行或某几行的修改，用来恢复提交后的数据页，只能恢复到最后一次提交的位置
  • redo log 采用的是 WAL（Write-ahead logging，预写式日志），所有修改要先写入日志，再更新到磁盘，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求
  • 简单的 redo log 是纯粹的物理日志，负责的 redo log 会存在物理日志和逻辑日志

工作过程：MySQL 发生了宕机，InnoDB 会判断一个数据页在崩溃恢复时丢失了更新，就会将它读到内存，然后根据 redo log 内容更新内存，更新完成后，内存页变成脏页，然后进行刷脏

缓冲池的刷脏策略：

  • redo log 文件是固定大小的，如果写满了就要擦除以前的记录，在擦除之前需要把对应的更新持久化到磁盘中
  • Buffer Pool 内存不足，需要淘汰部分数据页（LRU 链表尾部），如果淘汰的是脏页，就要先将脏页写到磁盘（要避免大事务）
  • 系统空闲时，后台线程会自动进行刷脏（Flush 链表部分已经详解）
  • MySQL 正常关闭时，会把内存的脏页都刷新到磁盘上

重做日志

日志缓冲

服务器启动时会向操作系统申请一片连续内存空间作为 redo log buffer（重做日志缓冲区），可以通过 innodb_log_buffer_size 系统变量指定 redo log buffer 的大小，默认是 16MB

log buffer 被划分为若干 redo log block（块，类似数据页的概念），每个默认大小 512 字节，每个 block 由 12 字节的 log block head、496 字节的 log block body、4 字节的 log block trailer 组成

  • 当数据修改时，先修改 Change Buffer 中的数据，然后在 redo log buffer 记录这次操作，写入 log buffer 的过程是顺序写入的（先写入前面的 block，写满后继续写下一个）
  • log buffer 中有一个指针 buf_free，来标识该位置之前都是填满的 block，该位置之后都是空闲区域（碰撞指针）

MySQL 规定对底层页面的一次原子访问称为一个 Mini-Transaction（MTR），比如在 B+ 树上插入一条数据就算一个 MTR

  • 一个事务包含若干个 MTR，一个 MTR 对应一组若干条 redo log，一组 redo log 是不可分割的，在进行数据恢复时也把一组 redo log 当作一个不可分割的整体处理

  • 所以不是每生成一条 redo 日志就将其插入到 log buffer 中，而是一个 MTR 结束后将一组 redo 日志写入 log buffer

InnoDB 的 redo log 是固定大小的，redo 日志在磁盘中以文件组的形式存储，同一组中的每个文件大小一样格式一样，

  • innodb_log_group_home_dir 代表磁盘存储 redo log 的文件目录，默认是当前数据目录
  • innodb_log_file_size 代表文件大小，默认 48M，innodb_log_files_in_group 代表文件个数，默认 2 最大 100，所以日志的文件大小为 innodb_log_file_size * innodb_log_files_in_group

redo 日志文件也是由若干个 512 字节的 block 组成，日志文件的前 2048 个字节（前 4 个 block）用来存储一些管理信息，以后的用来存储 log buffer 中的 block 镜像

注意：block 并不代表一组 redo log，一组日志可能占用不到一个 block 或者几个 block，依赖于 MTR 的大小

日志刷盘

redo log 需要在事务提交时将日志写入磁盘，但是比将内存中的 Buffer Pool 修改的数据写入磁盘的速度快，原因：

  • 刷脏是随机 IO，因为每次修改的数据位置随机；redo log 和 binlog 都是顺序写，磁盘的顺序 IO 比随机 IO 速度要快
  • 刷脏是以数据页（Page）为单位的，一个页上的一个小修改都要整页写入；redo log 中只包含真正需要写入的部分，减少无效 IO
  • 组提交机制，可以大幅度降低磁盘的 IO 消耗

InnoDB 引擎会在适当的时候，把内存中 redo log buffer 持久化（fsync）到磁盘，具体的刷盘策略：

  • 在事务提交时需要进行刷盘，通过修改参数 innodb_flush_log_at_trx_commit 设置：
    • 0：表示当提交事务时，并不将缓冲区的 redo 日志写入磁盘，而是等待后台线程每秒刷新一次
    • 1：在事务提交时将缓冲区的 redo 日志同步写入到磁盘，保证一定会写入成功（默认值）
    • 2：在事务提交时将缓冲区的 redo 日志异步写入到磁盘，不能保证提交时肯定会写入，只是有这个动作。日志已经在操作系统的缓存，如果操作系统没有宕机而 MySQL 宕机，也是可以恢复数据的
  • 写入 redo log buffer 的日志超过了总容量的一半，就会将日志刷入到磁盘文件，这会影响执行效率，所以开发中应避免大事务
  • 服务器关闭时
  • checkpoint 时（下小节详解）
  • 并行的事务提交（组提交）时，会将将其他事务的 redo log 持久化到磁盘。假设事务 A 已经写入 redo log buffer 中，这时另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么事务 B 要把 redo log buffer 里的日志全部持久化到磁盘，因为多个事务共用一个 redo log buffer，所以一次 fsync 可以刷盘多个事务的 redo log，提升了并发量

服务器启动后 redo 磁盘空间不变，所以 redo 磁盘中的日志文件是被循环使用的，采用循环写数据的方式，写完尾部重新写头部，所以要确保头部 log 对应的修改已经持久化到磁盘

日志序号

lsn (log sequence number) 代表已经写入的 redo 日志量、flushed_to_disk_lsn 指刷新到磁盘中的 redo 日志量，两者都是全局变量，如果两者的值相同，说明 log buffer 中所有的 redo 日志都已经持久化到磁盘

工作过程：写入 log buffer 数据时，buf_free 会进行偏移，偏移量就会加到 lsn 上

MTR 的执行过程中修改过的页对应的控制块会加到 Buffer Pool 的 flush 链表中，链表中脏页是按照第一次修改的时间进行排序的（头插），控制块中有两个指针用来记录脏页被修改的时间：

  • oldest_modification：第一次修改 Buffer Pool 中某个缓冲页时，将修改该页的 MTR 开始时对应的 lsn 值写入这个属性，所以链表页是以该值进行排序的
  • newest_modification：每次修改页面，都将 MTR 结束时全局的 lsn 值写入这个属性，所以该值是该页面最后一次修改后的 lsn 值

全局变量 checkpoint_lsn 表示当前系统可以被覆盖的 redo 日志总量，当 redo 日志对应的脏页已经被刷新到磁盘后，该文件空间就可以被覆盖重用，此时执行一次 checkpoint 来更新 checkpoint_lsn 的值存入管理信息（刷脏和执行一次 checkpoint 并不是同一个线程），该值的增量就代表磁盘文件中当前位置向后可以被覆盖的文件的量，所以该值是一直增大的

checkpoint：从 flush 链表尾部中找出还未刷脏的页面，该页面是当前系统中最早被修改的脏页，该页面之前产生的脏页都已经刷脏，然后将该页 oldest_modification 值赋值给 checkpoint_lsn，因为 lsn 小于该值时产生的 redo 日志都可以被覆盖了

但是在系统忙碌时，后台线程的刷脏操作不能将脏页快速刷出，导致系统无法及时执行 checkpoint ，这时需要用户线程从 flush 链表中把最早修改的脏页刷新到磁盘中，然后执行 checkpoint

1
write pos ------- checkpoint_lsn // 两值之间的部分表示可以写入的日志量，当 pos 追赶上 lsn 时必须执行 checkpoint

使用命令可以查看当前 InnoDB 存储引擎各种 lsn 的值：

1
SHOW ENGINE INNODB STATUS\G
崩溃恢复

恢复的起点：在从 redo 日志文件组的管理信息中获取最近发生 checkpoint 的信息，从 checkpoint_lsn 对应的日志文件开始恢复

恢复的终点：扫描日志文件的 block，block 的头部记录着当前 block 使用了多少字节，填满的 block 总是 512 字节， 如果某个 block 不是 512 字节，说明该 block 就是需要恢复的最后一个 block

恢复的过程：按照 redo log 依次执行恢复数据，优化方式

  • 使用哈希表：根据 redo log 的 space ID 和 page number 属性计算出哈希值，将对同一页面的修改放入同一个槽里，可以一次性完成对某页的恢复，避免了随机 IO
  • 跳过已经刷新到磁盘中的页面：数据页的 File Header 中的 FILE_PAGE_LSN 属性（类似 newest_modification）表示最近一次修改页面时的 lsn 值，如果在 checkpoint 后，数据页被刷新到磁盘中，那么该页 lsn 属性肯定大于 checkpoint_lsn

总结：先写 redo buffer，在写 change buffer，先刷 redo log，再刷脏，在删除完成刷脏 redo log

参考书籍：https://book.douban.com/subject/35231266/

工作流程

日志对比

MySQL 中还存在 binlog（二进制日志）也可以记录写操作并用于数据的恢复，保证数据不丢失，二者的区别是：

  • 作用不同：redo log 是用于 crash recovery （故障恢复），保证 MySQL 宕机也不会影响持久性；binlog 是用于 point-in-time recovery 的，保证服务器可以基于时间点恢复数据，此外 binlog 还用于主从复制
  • 层次不同：redo log 是 InnoDB 存储引擎实现的，而 binlog 是MySQL的 Server 层实现的，同时支持 InnoDB 和其他存储引擎
  • 内容不同：redo log 是物理日志，内容基于磁盘的 Page；binlog 的内容是二进制的，根据 binlog_format 参数的不同，可能基于SQL 语句、基于数据本身或者二者的混合（日志部分详解）
  • 写入时机不同：binlog 在事务提交时一次写入；redo log 的写入时机相对多元

binlog 为什么不支持奔溃恢复？

  • binlog 记录的是语句，并不记录数据页级的数据（哪个页改了哪些地方），所以没有能力恢复数据页
  • binlog 是追加写，保存全量的日志，没有标志确定从哪个点开始的数据是已经刷盘了，而 redo log 只要在 checkpoint_lsn 后面的就是没有刷盘的
更新记录

更新一条记录的过程：写之前一定先读

  • 在 B+ 树中定位到该记录（这个过程也被称作加锁读），如果该记录所在的页面不在 Buffer Pool 里，先将其加载进内存

  • 首先更新该记录对应的聚簇索引，更新聚簇索引记录时：

    • 更新记录前向 undo 页面写 undo 日志，由于这是更改页面，所以需要记录一下相应的 redo 日志

      注意：修改 undo页面也是在修改页面，事务凡是修改页面就需要先记录相应的 redo 日志

    • 然后先记录对应的的 redo 日志（等待 MTR 提交后写入 redo log buffer），最后进行真正的更新记录

  • 更新其他的二级索引记录，不会再记录 undo log，只记录 redo log 到 buffer 中

  • 在一条更新语句执行完成后（也就是将所有待更新记录都更新完了），就会开始记录该语句对应的 binlog 日志，此时记录的 binlog 并没有刷新到硬盘上，还在内存中，在事务提交时才会统一将该事务运行过程中的所有 binlog 日志刷新到硬盘

假设表中有字段 id 和 a，存在一条 id = 1, a = 2 的记录，此时执行更新语句：

1
update table set a=2 where id=1;

InnoDB 会真正的去执行把值修改成 (1,2) 这个操作，先加行锁，在去更新，并不会提前判断相同就不修改了

参考文章：https://mp.weixin.qq.com/s/wcJ2KisSaMnfP4nH5NYaQA

两段提交

当客户端执行 COMMIT 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交：

1
update T set c=c+1 where ID=2;

流程说明：执行引擎将这行新数据读入到内存中（Buffer Pool）后，先将此次更新操作记录到 redo log buffer 里，然后更新记录。最后将 redo log 刷盘后事务处于 prepare 状态，执行器会生成这个操作的 binlog，并把 binlog 写入磁盘，完成提交

两阶段：

  • Prepare 阶段：存储引擎将该事务的 redo 日志刷盘，并且将本事务的状态设置为 PREPARE，代表执行完成随时可以提交事务
  • Commit 阶段：先将事务执行过程中产生的 binlog 刷新到硬盘，再执行存储引擎的提交工作，引擎把 redo log 改成提交状态

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致，也有利于主从复制，更好的保持主从数据的一致性

数据恢复

系统崩溃前没有提交的事务的 redo log 可能已经刷盘（定时线程或者 checkpoint），怎么处理崩溃恢复？

工作流程：通过 undo log 在服务器重启时将未提交的事务回滚掉。首先定位到 128 个回滚段遍历 slot，获取 undo 链表首节点页面的 undo segement header 中的 TRX_UNDO_STATE 属性，表示当前链表的事务属性，事务状态是活跃的就全部回滚，如果是 PREPARE 状态，就需要根据 binlog 的状态进行判断：

  • 如果在时刻 A 发生了崩溃（crash），由于此时 binlog 还没完成，所以需要进行回滚
  • 如果在时刻 B 发生了崩溃，redo log 和 binlog 有一个共同的数据字段叫 XID，崩溃恢复的时候，会按顺序扫描 redo log：
    • 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，说明 binlog 也已经记录完整，直接从 redo log 恢复数据
    • 如果 redo log 里面的事务只有 prepare，就根据 XID 去 binlog 中判断对应的事务是否存在并完整，如果完整可以恢复数据，提交事务

判断一个事务的 binlog 是否完整的方法：

  • statement 格式的 binlog，最后会有 COMMIT
  • row 格式的 binlog，最后会有一个 XID event
  • MySQL 5.6.2 版本以后，引入了 binlog-checksum 参数用来验证 binlog 内容的正确性（可能日志中间出错）

参考文章：https://time.geekbang.org/column/article/73161

刷脏优化

系统在进行刷脏时会占用一部分系统资源，会影响系统的性能，产生系统抖动

  • 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长
  • 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的

InnoDB 刷脏页的控制策略：

  • innodb_io_capacity 参数代表磁盘的读写能力，建议设置成磁盘的 IOPS（每秒的 IO 次数）
  • 刷脏速度参考两个因素：脏页比例和 redo log 写盘速度
    • 参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%，InnoDB 会根据当前的脏页比例，算出一个范围在 0 到 100 之间的数字
    • InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，InnoDB 根据差值算出一个范围在 0 到 100 之间的数字
    • 两者较大的值记为 R，执行引擎按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度
  • innodb_flush_neighbors 参数置为 1 代表控制刷脏时检查相邻的数据页，如果也是脏页就一起刷脏，并检查邻居的邻居，这个行为会一直蔓延直到不是脏页，在 MySQL 8.0 中该值的默认值是 0，不建议开启此功能

一致特性

一致性是指事务执行前后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。

数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）

实现一致性的措施：

  • 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
  • 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
  • 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

锁机制

基本介绍

锁机制：数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全有序所设计的一种规则

利用 MVCC 性质进行读取的操作叫一致性读，读取数据前加锁的操作叫锁定读

锁的分类：

  • 按操作分类：
    • 共享锁：也叫读锁。对同一份数据，多个事务读操作可以同时加锁而不互相影响 ，但不能修改数据
    • 排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入
  • 按粒度分类：
    • 表级锁：会锁定整个表，开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低，偏向 MyISAM
    • 行级锁：会锁定当前操作行，开销大，加锁慢；会出现死锁；锁定力度小，发生锁冲突概率低，并发度高，偏向 InnoDB
    • 页级锁：锁的力度、发生冲突的概率和加锁开销介于表锁和行锁之间，会出现死锁，并发性能一般
  • 按使用方式分类：
    • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁
    • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据

  • 不同存储引擎支持的锁

    | 存储引擎 | 表级锁 | 行级锁 | 页级锁 |
    | ———— | ———— | ———— | ——— |
    | MyISAM | 支持 | 不支持 | 不支持 |
    | InnoDB | 支持 | 支持 | 不支持 |
    | MEMORY | 支持 | 不支持 | 不支持 |
    | BDB | 支持 | 不支持 | 支持 |

从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理系统

内存结构

对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，结构包括

  • 事务信息：锁对应的事务信息，一个锁属于一个事务
  • 索引信息：对于行级锁，需要记录加锁的记录属于哪个索引
  • 表锁和行锁信息：表锁记录着锁定的表，行锁记录了 Space ID 所在表空间、Page Number 所在的页号、n_bits 使用了多少比特
  • type_mode：一个 32 比特的数，被分成 lock_mode、lock_type、rec_lock_type 三个部分
    • lock_mode：锁模式，记录是共享锁、排他锁、意向锁之类
    • lock_type：代表表级锁还是行级锁
    • rec_lock_type：代表行锁的具体类型和 is_waiting 属性，is_waiting = true 时表示当前事务尚未获取到锁，处于等待状态。事务获取锁后的锁结构是 is_waiting 为 false，释放锁时会检查是否与当前记录关联的锁结构，如果有就唤醒对应事务的线程

一个事务可能操作多条记录，为了节省内存，满足下面条件的锁使用同一个锁结构：

  • 在同一个事务中的加锁操作
  • 被加锁的记录在同一个页面中
  • 加锁的类型是一样的
  • 加锁的状态是一样的

Server

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

MDL 叫元数据锁，主要用来保护 MySQL 内部对象的元数据，保证数据读写的正确性，当对一个表做增删改查的时候，加 MDL 读锁；当要对表做结构变更操作 DDL 的时候，加 MDL 写锁，两种锁不相互兼容，所以可以保证 DDL、DML、DQL 操作的安全

说明：DDL 操作执行前会隐式提交当前会话的事务，因为 DDL 一般会在若干个特殊事务中完成，开启特殊事务前需要提交到其他事务

MDL 锁的特性：

  • MDL 锁不需要显式使用，在访问一个表的时候会被自动加上，在事务开始时申请，整个事务提交后释放（执行完单条语句不释放）

  • MDL 锁是在 Server 中实现，不是 InnoDB 存储引擎层能直接实现的锁

  • MDL 锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁

FLUSH TABLES WITH READ LOCK 简称（FTWRL），全局读锁，让整个库处于只读状态，DDL DML 都被阻塞，工作流程：

  1. 上全局读锁（lock_global_read_lock）
  2. 清理表缓存（close_cached_tables）
  3. 上全局 COMMIT 锁（make_global_read_lock_block_commit）

该命令主要用于备份工具做一致性备份，由于 FTWRL 需要持有两把全局的 MDL 锁，并且还要关闭所有表对象，因此杀伤性很大

MyISAM

表级锁

MyISAM 存储引擎只支持表锁，这也是 MySQL 开始几个版本中唯一支持的锁类型

MyISAM 引擎在执行查询语句之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁，这个过程并不需要用户干预，所以用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁

  • 加锁命令：（对 InnoDB 存储引擎也适用）

    读锁：所有连接只能读取数据，不能修改

    写锁：其他连接不能查询和修改数据

    1
    2
    3
    4
    5
    -- 读锁
    LOCK TABLE table_name READ;

    -- 写锁
    LOCK TABLE table_name WRITE;

  • 解锁命令：

    1
    2
    -- 将当前会话所有的表进行解锁
    UNLOCK TABLES;

锁的兼容性：

  • 对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
  • 对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作

锁调度：MyISAM 的读写锁调度是写优先，因为写锁后其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞，所以 MyISAM 不适合做写为主的表的存储引擎

锁操作

读锁

两个客户端操作 Client 1和 Client 2，简化为 C1、C2

  • 数据准备：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE `tb_book` (
      `id` INT(11) AUTO_INCREMENT,
      `name` VARCHAR(50) DEFAULT NULL,
      `publish_time` DATE DEFAULT NULL,
      `status` CHAR(1) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM DEFAULT CHARSET=utf8 ;

    INSERT INTO tb_book (id, NAME, publish_time, STATUS) VALUES(NULL,'java编程思想','2088-08-01','1');
    INSERT INTO tb_book (id, NAME, publish_time, STATUS) VALUES(NULL,'mysql编程思想','2088-08-08','0');

  • C1、C2 加读锁，同时查询可以正常查询出数据

    1
    2
    LOCK TABLE tb_book READ;	-- C1、C2
    SELECT * FROM tb_book;		-- C1、C2

  • C1 加读锁，C1、C2 查询未锁定的表，C1 报错，C2 正常查询

    1
    2
    LOCK TABLE tb_book READ;	-- C1
    SELECT * FROM tb_user;		-- C1、C2

    C1、C2 执行插入操作，C1 报错，C2 等待获取

    1
    INSERT INTO tb_book VALUES(NULL,'Spring高级','2088-01-01','1');	-- C1、C2

    当在 C1 中释放锁指令 UNLOCK TABLES，C2 中的 INSERT 语句立即执行

写锁

两个客户端操作 Client 1和 Client 2，简化为 C1、C2

  • C1 加写锁，C1、C2查询表，C1 正常查询，C2 需要等待

    1
    2
    LOCK TABLE tb_book WRITE;	-- C1
    SELECT * FROM tb_book;		-- C1、C2

    当在 C1 中释放锁指令 UNLOCK TABLES，C2 中的 SELECT 语句立即执行

  • C1、C2 同时加写锁

    1
    LOCK TABLE tb_book WRITE;

  • C1 加写锁，C1、C2查询未锁定的表，C1 报错，C2 正常查询

锁状态

  • 查看锁竞争：

    1
    SHOW OPEN TABLES;

    In_user：表当前被查询使用的次数，如果该数为零，则表是打开的，但是当前没有被使用

    Name_locked：表名称是否被锁定，名称锁定用于取消表或对表进行重命名等操作

    1
    LOCK TABLE tb_book READ;	-- 执行命令

  • 查看锁状态：

    1
    SHOW STATUS LIKE 'Table_locks%';

    Table_locks_immediate：指的是能立即获得表级锁的次数，每立即获取锁，值加 1

    Table_locks_waited：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加 1，此值高说明存在着较为严重的表级锁争用情况

InnoDB

行级锁

记录锁

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁，InnoDB 同时支持表锁和行锁

行级锁，也称为记录锁（Record Lock），InnoDB 实现了以下两种类型的行锁：

  • 共享锁 (S)：又称为读锁，简称 S 锁，多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
  • 排他锁 (X)：又称为写锁，简称 X 锁，不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，只有获取排他锁的事务是可以对数据读取和修改

RR 隔离界别下，对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（行锁），在 commit 时自动释放；对于普通 SELECT 语句，不会加任何锁（只是针对 InnoDB 层来说的，因为在 Server 层会加 MDL 读锁），通过 MVCC 防止并发冲突

在事务中加的锁，并不是不需要了就释放，而是在事务中止或提交时自动释放，这个就是两阶段锁协议。所以一般将更新共享资源（并发高）的 SQL 放到事务的最后执行，可以让其他线程尽量的减少等待时间

锁的兼容性：

  • 共享锁和共享锁 兼容
  • 共享锁和排他锁 冲突
  • 排他锁和排他锁 冲突
  • 排他锁和共享锁 冲突

显式给数据集加共享锁或排他锁：加锁读就是当前读，读取的是最新数据

1
2
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE	-- 共享锁
SELECT * FROM table_name WHERE ... FOR UPDATE			-- 排他锁

注意：锁默认会锁聚簇索引（锁就是加在索引上），但是当使用覆盖索引时，加共享锁只锁二级索引，不锁聚簇索引

锁操作

两个客户端操作 Client 1和 Client 2，简化为 C1、C2

  • 环境准备

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE test_innodb_lock(
    	id INT(11),
    	name VARCHAR(16),
    	sex VARCHAR(1)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;

    INSERT INTO test_innodb_lock VALUES(1,'100','1');
    -- ..........

    CREATE INDEX idx_test_innodb_lock_id ON test_innodb_lock(id);
    CREATE INDEX idx_test_innodb_lock_name ON test_innodb_lock(name);

  • 关闭自动提交功能：

    1
    SET AUTOCOMMIT=0;	-- C1、C2

    正常查询数据：

    1
    SELECT * FROM test_innodb_lock;	-- C1、C2

  • 查询 id 为 3 的数据，正常查询：

    1
    SELECT * FROM test_innodb_lock WHERE id=3;	-- C1、C2

  • C1 更新 id 为 3 的数据，但不提交：

    1
    UPDATE test_innodb_lock SET name='300' WHERE id=3;	-- C1

    C2 查询不到 C1 修改的数据，因为隔离界别为 REPEATABLE READ，C1 提交事务，C2 查询：

    1
    COMMIT;	-- C1

    提交后仍然查询不到 C1 修改的数据，因为隔离级别可以防止脏读、不可重复读，所以 C2 需要提交才可以查询到其他事务对数据的修改：

    1
    2
    COMMIT;	-- C2
    SELECT * FROM test_innodb_lock WHERE id=3;	-- C2

  • C1 更新 id 为 3 的数据，但不提交，C2 也更新 id 为 3 的数据：

    1
    2
    UPDATE test_innodb_lock SET name='3' WHERE id=3;	-- C1
    UPDATE test_innodb_lock SET name='30' WHERE id=3;	-- C2

    当 C1 提交，C2 直接解除阻塞，直接更新

  • 操作不同行的数据：

    1
    2
    UPDATE test_innodb_lock SET name='10' WHERE id=1;	-- C1
    UPDATE test_innodb_lock SET name='30' WHERE id=3;	-- C2

    由于 C1、C2 操作的不同行，获取不同的行锁，所以都可以正常获取行锁

锁分类

间隙锁

InnoDB 会对间隙（GAP）进行加锁，就是间隙锁 （RR 隔离级别下才有该锁）。间隙锁之间不存在冲突关系，多个事务可以同时对一个间隙加锁，但是间隙锁会阻止往这个间隙中插入一个记录的操作

InnoDB 加锁的基本单位是 next-key lock，该锁是行锁和 gap lock 的组合（X or S 锁），但是加锁过程是分为间隙锁和行锁两段执行

  • 可以保护当前记录和前面的间隙，遵循左开右闭原则，单纯的是间隙锁左开右开
  • 假设有 10、11、13，那么可能的间隙锁包括：(负无穷,10]、(10,11]、(11,13]、(13,正无穷)

几种索引的加锁情况：

  • 唯一索引加锁在值存在时是行锁，next-key lock 会退化为行锁，值不存在会变成间隙锁
  • 普通索引加锁会继续向右遍历到不满足条件的值为止，next-key lock 退化为间隙锁
  • 范围查询无论是否是唯一索引，都需要访问到不满足条件的第一个值为止
  • 对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么会加间隙锁

间隙锁优点：RR 级别下间隙锁可以解决事务的一部分的幻读问题，通过对间隙加锁，可以防止读取过程中数据条目发生变化。一部分的意思是不会对全部间隙加锁，只能加锁一部分的间隙。

间隙锁危害：

  • 当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害，影响并发度
  • 事务 A B 同时锁住一个间隙后，A 往当前间隙插入数据时会被 B 的间隙锁阻塞，B 也执行插入间隙数据的操作时就会产生死锁

现场演示：

  • 关闭自动提交功能：

    1
    SET AUTOCOMMIT=0;	-- C1、C2

  • 查询数据表：

    1
    SELECT * FROM test_innodb_lock;

  • C1 根据 id 范围更新数据，C2 插入数据：

    1
    2
    UPDATE test_innodb_lock SET name='8888' WHERE id < 4;	-- C1
    INSERT INTO test_innodb_lock VALUES(2,'200','2');		-- C2

    出现间隙锁，C2 被阻塞，等待 C1 提交事务后才能更新

意向锁

InnoDB 为了支持多粒度的加锁，允许行锁和表锁同时存在，支持在不同粒度上的加锁操作，InnoDB 增加了意向锁（Intention Lock）

意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁，意向锁分为两种：

  • 意向共享锁（IS）：事务有意向对表加共享锁
  • 意向排他锁（IX）：事务有意向对表加排他锁

IX，IS 是表级锁，不会和行级的 X，S 锁发生冲突，意向锁是在加表级锁之前添加，为了在加表级锁时可以快速判断表中是否有记录被上锁，比如向一个表添加表级 X 锁的时：

  • 没有意向锁，则需要遍历整个表判断是否有锁定的记录
  • 有了意向锁，首先判断是否存在意向锁，然后判断该意向锁与即将添加的表级锁是否兼容即可，因为意向锁的存在代表有表级锁的存在或者即将有表级锁的存在

兼容性如下所示：

插入意向锁 Insert Intention Lock 是在插入一行记录操作之前设置的一种间隙锁，是行级锁

插入意向锁释放了一种插入信号，即多个事务在相同的索引间隙插入时如果不是插入相同的间隙位置就不需要互相等待。假设某列有索引，只要两个事务插入位置不同，如事务 A 插入 3，事务 B 插入 4，那么就可以同时插入

自增锁

系统会自动给 AUTO_INCREMENT 修饰的列进行递增赋值，实现方式：

  • AUTO_INC 锁：表级锁，执行插入语句时会自动添加，在该语句执行完成后释放，并不是事务结束
  • 轻量级锁：为插入语句生成 AUTO_INCREMENT 修饰的列时获取该锁，生成以后释放掉，不需要等到插入语句执行完后释放

系统变量 innodb_autoinc_lock_mode 控制采取哪种方式：

  • 0：全部采用 AUTO_INC 锁
  • 1：全部采用轻量级锁
  • 2：混合使用，在插入记录的数量确定是采用轻量级锁，不确定时采用 AUTO_INC 锁
隐式锁

一般情况下 INSERT 语句是不需要在内存中生成锁结构的，会进行隐式的加锁，保护的是插入后的安全

注意：如果插入的间隙被其他事务加了间隙锁，此次插入会被阻塞，并在该间隙插入一个插入意向锁

  • 聚簇索引：索引记录有 trx_id 隐藏列，表示最后改动该记录的事务 id，插入数据后事务 id 就是当前事务。其他事务想获取该记录的锁时会判断当前记录的事务 id 是否是活跃的，如果不是就可以正常加锁；如果是就创建一个 X 的锁结构，该锁的 is_waiting 是 false，为自己的事务创建一个锁结构，is_waiting 是 true（类似 Java 中的锁升级）
  • 二级索引：获取数据页 Page Header 中的 PAGE_MAX_TRX_ID 属性，代表修改当前页面的最大的事务 ID，如果小于当前活跃的最小事务 id，就证明插入该数据的事务已经提交，否则就需要获取到主键值进行回表操作

隐式锁起到了延迟生成锁的效果，如果其他事务与隐式锁没有冲突，就可以避免锁结构的生成，节省了内存资源

INSERT 在两种情况下会生成锁结构：

  • 重复键：在插入主键或唯一二级索引时遇到重复的键值会报错，在报错前需要对对应的聚簇索引进行加锁

    • 隔离级别 <= Read Uncommitted，加 S 型 Record Lock
    • 隔离级别 >= Repeatable Read，加 S 型 next_key 锁

  • 外键检查：如果待插入的记录在父表中可以找到，会对父表的记录加 S 型 Record Lock。如果待插入的记录在父表中找不到

    • 隔离级别 <= Read Committed，不加锁
    • 隔离级别 >= Repeatable Read，加间隙锁

锁优化

优化锁

InnoDB 存储引擎实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高，但是在整体并发处理能力方面要远远优于 MyISAM 的表锁，当系统并发量较高的时候，InnoDB 的整体性能远远好于 MyISAM

但是使用不当可能会让 InnoDB 的整体性能表现不仅不能比 MyISAM 高，甚至可能会更差

优化建议：

  • 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件及索引范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离（需要业务层面满足需求）
锁升级

索引失效造成行锁升级为表锁，不通过索引检索数据，全局扫描的过程中 InnoDB 会将对表中的所有记录加锁，实际效果和表锁一样，实际开发过程应避免出现索引失效的状况

  • 查看当前表的索引：

    1
    SHOW INDEX FROM test_innodb_lock;

  • 关闭自动提交功能：

    1
    SET AUTOCOMMIT=0;	-- C1、C2

  • 执行更新语句：

    1
    2
    UPDATE test_innodb_lock SET sex='2' WHERE name=10;	-- C1
    UPDATE test_innodb_lock SET sex='2' WHERE id=3;		-- C2

    索引失效：执行更新时 name 字段为 varchar 类型，造成索引失效，最终行锁变为表锁

死锁

不同事务由于互相持有对方需要的锁而导致事务都无法继续执行的情况称为死锁

死锁情况：线程 A 修改了 id = 1 的数据，请求修改 id = 2 的数据，线程 B 修改了 id = 2 的数据，请求修改 id = 1 的数据，产生死锁

解决策略：

  • 直接进入等待直到超时，超时时间可以通过参数 innodb_lock_wait_timeout 来设置，默认 50 秒，但是时间的设置不好控制，超时可能不是因为死锁，而是因为事务处理比较慢，所以一般不采取该方式

  • 主动死锁检测，发现死锁后主动回滚死锁链条中较小的一个事务，让其他事务得以继续执行，将参数 innodb_deadlock_detect 设置为 on，表示开启该功能（事务较小的意思就是事务执行过程中插入、删除、更新的记录条数）

    死锁检测并不是每个语句都要检测，只有在加锁访问的行上已经有锁时，当前事务被阻塞了才会检测，也是从当前事务开始进行检测

通过执行 SHOW ENGINE INNODB STATUS 可以查看最近发生的一次死循环，全局系统变量 innodb_print_all_deadlocks 设置为 on，就可以将每个死锁信息都记录在 MySQL 错误日志中

死锁一般是行级锁，当表锁发生死锁时，会在事务中访问其他表时直接报错，破坏了持有并等待的死锁条件

锁状态

查看锁信息

1
SHOW STATUS LIKE 'innodb_row_lock%';

参数说明：

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量

  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度

  • Innodb_row_lock_time_avg：每次等待所花平均时长

  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间

  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数

当等待的次数很高，而且每次等待的时长也不短的时候，就需要分析系统中为什么会有如此多的等待，然后根据分析结果制定优化计划

查看锁状态：

1
2
SELECT * FROM information_schema.innodb_locks;	#锁的概况
SHOW ENGINE INNODB STATUS\G; #InnoDB整体状态，其中包括锁的情况

lock_id 是锁 id；lock_trx_id 为事务 id；lock_mode 为 X 代表排它锁（写锁）；lock_type 为 RECORD 代表锁为行锁（记录锁）

乐观锁

悲观锁：在整个数据处理过程中，将数据处于锁定状态，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据，修改删除数据时也加锁，其它事务同样无法读取这些数据

悲观锁和乐观锁使用前提：

  • 对于读的操作远多于写的操作的时候，一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量，最后需要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
  • 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险，这时候可以选择悲观锁

乐观锁的实现方式：就是 CAS，比较并交换

  • 版本号

    1. 给数据表中添加一个 version 列，每次更新后都将这个列的值加 1

    2. 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号

    3. 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化

    4. 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      -- 创建city表
      CREATE TABLE city(
      	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
      	NAME VARCHAR(20),                   -- 城市名称
      	VERSION INT                         -- 版本号
      );

      -- 添加数据
      INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);

      -- 修改北京为北京市
      -- 1.查询北京的version
      SELECT VERSION FROM city WHERE NAME='北京';
      -- 2.修改北京为北京市，版本号+1。并对比版本号
      UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

  • 时间戳

    • 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是 timestamp
    • 每次更新后都将最新时间插入到此列
    • 读取数据时，将时间读取出来，在执行更新的时候，比较时间
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化

乐观锁的异常情况：如果 version 被其他事务抢先更新，则在当前事务中更新失败，trx_id 没有变成当前事务的 ID，当前事务再次查询还是旧值，就会出现值没变但是更新不了的现象（anomaly）

解决方案：每次 CAS 更新不管成功失败，就结束当前事务；如果失败则重新起一个事务进行查询更新

日志

日志分类

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的过程，可以帮助数据库管理员追踪数据库曾经发生过的各种事件。

MySQL日志主要包括六种：

  1. 重做日志（redo log）
  2. 回滚日志（undo log）
  3. 归档日志（binlog）（二进制日志）
  4. 错误日志（errorlog）
  5. 慢查询日志（slow query log）
  6. 一般查询日志（general log）
  7. 中继日志（relay log）

错误日志

错误日志是 MySQL 中最重要的日志之一，记录了当 mysql 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志

该日志是默认开启的，默认位置是：/var/log/mysql/error.log

查看指令：

1
SHOW VARIABLES LIKE 'log_error%';

查看日志内容：

1
tail -f /var/log/mysql/error.log

归档日志

基本介绍

归档日志（BINLOG）也叫二进制日志，是因为采用二进制进行存储，记录了所有的 DDL（数据定义语言）语句和 DML（数据操作语言）语句，但不包括数据查询语句，在事务提交前的最后阶段写入

作用：灾难时的数据恢复和 MySQL 的主从复制

归档日志默认情况下是没有开启的，需要在 MySQL 配置文件中开启，并配置 MySQL 日志的格式：

1
2
3
4
5
6
7
cd /etc/mysql
vim my.cnf

# 配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如: mysqlbin.000001
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format=STATEMENT

日志存放位置：配置时给定了文件名但是没有指定路径，日志默认写入MySQL 的数据目录

日志格式：

  • STATEMENT：该日志格式在日志文件中记录的都是 SQL 语句，每一条对数据进行修改的 SQL 都会记录在日志文件中，通过 mysqlbinlog 工具，可以查看到每条语句的文本。主从复制时，从库会将日志解析为原语句，并在从库重新执行一遍

    缺点：可能会导致主备不一致，因为记录的 SQL 在不同的环境中可能选择的索引不同，导致结果不同

  • ROW：该日志格式在日志文件中记录的是每一行的数据变更，而不是记录 SQL 语句。比如执行 SQL 语句 update tb_book set status='1'，如果是 STATEMENT，在日志中会记录一行 SQL 语句； 如果是 ROW，由于是对全表进行更新，就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更

    缺点：记录的数据比较多，占用很多的存储空间

  • MIXED：这是 MySQL 默认的日志格式，混合了STATEMENT 和 ROW 两种格式，MIXED 格式能尽量利用两种模式的优点，而避开它们的缺点

日志刷盘

事务执行过程中，先将日志写（write）到 binlog cache，事务提交时再把 binlog cache 写（fsync）到 binlog 文件中，一个事务的 binlog 是不能被拆开的，所以不论这个事务多大也要确保一次性写入

事务提交时执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

write 和 fsync 的时机由参数 sync_binlog 控制的：

  • sync_binlog=0：表示每次提交事务都只 write，不 fsync
  • sync_binlog=1：表示每次提交事务都会执行 fsync
  • sync_binlog=N(N>1)：表示每次提交事务都 write，但累积 N 个事务后才 fsync，但是如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志

日志读取

日志文件存储位置：/var/lib/mysql

由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，语法如下：

1
mysqlbinlog log-file;

查看 STATEMENT 格式日志：

  • 执行插入语句：

    1
    INSERT INTO tb_book VALUES(NULL,'Lucene','2088-05-01','0');

  • cd /var/lib/mysql：

    1
    2
    -rw-r-----  1 mysql mysql      177 5月  23 21:08 mysqlbin.000001
    -rw-r-----  1 mysql mysql       18 5月  23 21:04 mysqlbin.index

    mysqlbin.index：该文件是日志索引文件 ， 记录日志的文件名；

    mysqlbing.000001：日志文件

  • 查看日志内容：

    1
    mysqlbinlog mysqlbing.000001;

    日志结尾有 COMMIT

查看 ROW 格式日志：

  • 修改配置：

    1
    2
    # 配置二进制日志的格式
    binlog_format=ROW

  • 插入数据：

    1
    INSERT INTO tb_book VALUES(NULL,'SpringCloud实战','2088-05-05','0');

  • 查看日志内容：日志格式 ROW，直接查看数据是乱码，可以在 mysqlbinlog 后面加上参数 -vv 

    1
    mysqlbinlog -vv mysqlbin.000002

日志删除

对于比较繁忙的系统，生成日志量大，这些日志如果长时间不清除，将会占用大量的磁盘空间，需要删除日志

  • Reset Master 指令删除全部 binlog 日志，删除之后，日志编号将从 xxxx.000001重新开始

    1
    Reset Master	-- MySQL指令

  • 执行指令 PURGE MASTER LOGS TO 'mysqlbin.***，该命令将删除 *** 编号之前的所有日志

  • 执行指令 PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss' ，该命令将删除日志为 yyyy-mm-dd hh:mm:ss 之前产生的日志

  • 设置参数 --expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样做有利于减少管理日志的工作量，配置 my.cnf 文件：

    1
    2
    3
    log_bin=mysqlbin
    binlog_format=ROW
    --expire_logs_days=3

数据恢复

误删库或者表时，需要根据 binlog 进行数据恢复，

一般情况下数据库有定时的全量备份，假如每天 0 点定时备份，12 点误删了库，恢复流程：

  • 取最近一次全量备份，用备份恢复出一个临时库
  • 从日志文件中取出凌晨 0 点之后的日志
  • 把除了误删除数据的语句外日志，全部应用到临时库

跳过误删除语句日志的方法：

  • 如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用 –stop-position 参数执行到误操作之前的日志，然后再用 –start-position 从误操作之后的日志继续执行
  • 如果实例使用了 GTID 模式，假设误操作命令的 GTID 是 gtid1，那么只需要提交一个空事务先将这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时就会自动跳过误操作的语句

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的 SQL 语句

默认情况下，查询日志是未开启的。如果需要开启查询日志，配置 my.cnf：

1
2
3
4
# 该选项用来开启查询日志，可选值0或者1，0代表关闭，1代表开启 
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为host_name.log，存放在/var/lib/mysql
general_log_file=mysql_query.log

配置完毕之后，在数据库执行以下操作：

1
2
3
4
SELECT * FROM tb_book;
SELECT * FROM tb_book WHERE id = 1;
UPDATE tb_book SET name = 'lucene入门指南' WHERE id = 5;
SELECT * FROM tb_book WHERE id < 8

执行完毕之后， 再次来查询日志文件：

慢日志

慢查询日志记录所有执行时间超过 long_query_time 并且扫描记录数不小于 min_examined_row_limit 的所有的 SQL 语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度到微秒

慢查询日志默认是关闭的，可以通过两个参数来控制慢查询日志，配置文件 /etc/mysql/my.cnf：

1
2
3
4
5
6
7
8
# 该参数用来控制慢查询日志是否开启，可选值0或者1，0代表关闭，1代表开启 
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名，存放在 /var/lib/mysql
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s
long_query_time=10

日志读取：

  • 直接通过 cat 指令查询该日志文件：

    1
    cat slow_query.log

  • 如果慢查询日志内容很多，直接查看文件比较繁琐，可以借助 mysql 自带的 mysqldumpslow 工具对慢查询日志进行分类汇总：

    1
    mysqldumpslow slow_query.log