数据库的规范化

范式是设计数据库结构过程中所要遵循的规则和指导方法。着重了解前三个范式即可。
  • 第一范式（1NF）：指数据库中的每一列都是不可分割的基本数据项（即数据项不可再细分，如编号；可再分的有如通讯号码，可分为手机号与座机号）
  • 第二范式（2NF）：表中必须存在主键，且其他的属性必须完全依赖于主键（如：学生表[学号，姓名，班级]）
  • 第三范式（3NF）：满足第二范式的情况下，所有的属性都不传递依赖于主键(如：学生借书情况[借阅编号，学生学号，书籍编号，书籍名称，书记作者]，此时书籍号应该依赖于节约编号，因此存在依赖传递的情况，所以应当将书籍信息拆分为另一张表。即：学生借书情况[借阅编号，学生学号，书籍编号]，书籍[书籍编号，书籍名称，书记作者])
  • 巴斯范式（BCNF）：BCNF是比第三范式更严格的规范化形式，由E.F. Codd和C.W. H. Boyce提出。一个关系模式R处于BCNF，如果对于R中的每一个非平凡的函数依赖X → Y（即Y不完全函数依赖于候选键），X都是超键（即包含候选键的集合）。换句话说，如果不存在非主属性对任何候选键的传递依赖，那么关系模式就处于BCNF。（如学生表[学生ID，学生姓名]课程表[课程ID，课程名称，教授姓名]成绩表[学生ID，课程ID，成绩]）
  • 第四范式（4NF）：第四范式是在处理多值依赖（Multivalued Dependencies，MVD）的情况下提出的。一个关系模式R处于4NF，如果对于R中的每个非平凡的多值依赖X →→ Y（其中X和Y是R的属性组，且X∩Y=∅），X都是超键。换句话说，4NF要求在关系模式中不存在非平凡的多值依赖，除非依赖的左侧是一个超键。4NF的目标是消除那些不属于单一实体的数据的冗余，这些数据可能与多个实体相关联。（BCNF范式中的例子中已经满足4NF，我们没有多值依赖，因为每个学生ID和课程ID组合都唯一地确定了一个成绩。如果存在多值依赖，比如一个课程可能有多个教授，我们会需要进一步分解。）
  • 第五范式（5NF）：第五范式被称为完美范式。是在处理连接依赖（Join Dependencies）的情况下提出的。一个关系模式R处于5NF，如果对于R中的任何连接依赖ρ(R1, R2, ..., Rn)，R都可以分解为一组关系模式{R1, R2, ..., Rn}，并且对于每个连接依赖，R的分解结果保持依赖关系。换句话说，5NF要求关系模式在进行投影和连接操作时，能够保持原有的依赖关系不变。5NF关注的是确保数据的完整性和避免在进行关系运算时可能出现的数据异常。（BCNF的例子中，没有额外的连接依赖需要处理，因为我们已经将数据分解得足够细，以确保在进行投影和连接操作时不会丢失信息或产生异常。故满足第五范式）

范式优点及缺点

  • 优点

    • 避免数据冗余
    • 减少数据库的空间
    • 数据变更速度快

  • 缺点

    • 范式等级越高，表的数量越多
    • 获取数据时表关联过多，性能差

反范式设计

范式设计的表无法满足性能要求时，需要根据业务场景，在范式的基础上灵活设计

对比范式化模型

  • 范式化模型

    • 数据没有冗余，更新容易
    • 表的数量较多
    • 查询数据需要多表关联时查询性能低下

  • 反范式化模型

    • 冗余将带来很好的读取性能
    • 需要维护冗余数据
    • 对磁盘空间的消耗可接受（空间换时间）

MySQL使用原则与设计规范

MySQL 虽然具有很多特性并提供了很多功能，但是有些特性会严重影响它的性能，比如，在数据库里进行计算，写大事务、大 SQL、存储大字段等。

想要发挥 MySQL 的最佳性能，需要遵循 3 个基本使用原则。

  1. 首先是需要让 MySQL 回归存储的基本职能：MySQL 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离；
  2. 其次是查询数据时，尽量单表查询，减少跨库查询和多表关联；

  3. 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。

    • 大事务，运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。
    • 大 SQL，复杂的 SQL 意味着过多的表的关联，MySQL 数据库处理关联超过 3 张表以上的 SQL 时，占用资源多，性能低下。
    • 大批量，意味着多条 SQL 一次性执行完成，必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。
    • 大字段，blob、text 等大字段，尽量少用。必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新。

下面具体讲解数据库的基本设置规则：

  1. 必须指定默认存储引擎为 InnoDB，并且禁用 MyISAM 存储引擎，随着 MySQL 8.0 版本的发布，所有的数据字典表都已经转换成了 InnoDB，MyISAM 存储引擎已成为了历史。
  2. 默认字符集 UTF8mb4，以前版本的 UTF8 是 UTF8mb3，未包含个别特殊字符，新版本的 UTF8mb4 包含所有字符，官方强烈建议使用此字符集。
  3. 关闭区分大小写功能。设置 lower_case_tables_name=1，即可关闭区分大小写功能，即大写字母 T 和小写字母 t 一样。

MySQL 数据库提供的功能很全面，但并不是所有的功能性能都高效。

  1. 存储过程、触发器、视图、event。为了存储计算分离，这类功能尽量在程序中实现。这些功能非常不完整，调试、排错、监控都非常困难，相关数据字典也不完善，存在潜在的风险。一般在生产数据库中，禁止使用。
  2. lob、text、enum、set。这些字段类型，在 MySQL 数据库的检索性能不高，很难使用索引进行优化。如果必须使用这些功能，一般采取特殊的结构设计，或者与程序结合使用其他的字段类型替代。比如：set 可以使用整型（0，1，2，3）、注释功能和程序的检查功能集合替代。

常用操作复习与速查

只提供了基本的CRUD语句，如需查看其他详细语句，可以翻看我以前写的MySQL学习记录

数据库定义语言DDL

  • 创建数据库：

    CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

  • 删除数据库：

    DROP DATABASE 数据库名

  • 创建数据表：

    create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])
    • 表级约束有四种：主键、外键、唯一、检查
    • 列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 check （MySQL不支持）、默认default 、非空/空值 not null/ null

  • 修改表：

    ALTER TABLE 表名 [ADD 新列名 数据类型[列级约束条件]]
                 [DROP COLUMN 列名[restrict|cascade]]
                [ALTER COLUMN 列名 新数据类型]
    • ADD:添加一个新的列
    • DROP：删除一个列。restrict（默认使用）表示如果此列作为其他表的约束或视图引用到此列时，将无法删除；cascade会强制连带引用此列的约束、视图一起删除。

  • 删除表：

    DROP TABLE 表名[restrict|cascade]

数据库操纵语言DML

  • 插入数据：

    INSERT INTO 表名 VALUES(值1, 值2, 值3) -- 数据与列一一对应可以忽略列名
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)

  • 修改数据：

    UPDATE 表名 SET 列名=值,... WHERE 条件

  • 删除数据

    DELETE FROM 表名

数据库查询语言DQL

  • 单表查询：

    -- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名
-- 限定查询目标
SELECT * FROM 表名 WHERE 条件

  • 查用查询条件

    • 一般的比较运算符，包括=、>、<、>=、<=、!=等。
    • 是否在集合中：in、not in
    • 字符模糊匹配：like，not like
    • 多重条件连接查询：and、or、not

  • 排序查询

    SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC -- 先按照列名1进行排序，每组列名1相同的数据再按照列名2排序

    ASC: 升序排序

    DESC：降序排序

  • 聚合函数

    • count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
    • count([distinct]列名)统计某列的值总和
    • sum([distinct]列名)求一列的和（注意必须是数字类型的）
    • avg([distinct]列名)求一列的平均值（注意必须是数字类型）
    • max([distinct]列名)求一列的最大值
    • min([distinct]列名)求一列的最小值

    使用例：

    SELECT count(distinct 列名) FROM 表名 WHERE 条件

  • 分组和分页查询

    通过使用group by来对查询结果进行分组，需要结合聚合函数一起使用：

    SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名

    添加having来限制分组条件：

    SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

    通过limit来限制查询的数量，只取前n个结果：

    SELECT * FROM 表名 LIMIT 数量

    进行分页：

    SELECT * FROM 表名 LIMIT 起始位置,数量

  • 多表查询

    多表查询是同时查询的两个或两个以上的表，多表查询会提通过连接转换为单表查询。

    SELECT * FROM 表1, 表2

    直接这样查询会得到两张表的笛卡尔积，也就是每一项数据和另一张表的每一项数据都结合一次，会产生庞大的数据。

    SELECT * FROM 表1, 表2 WHERE 条件

  • 外连接查询

    外连接就是专门用于联合查询情景的，比如现在有一个存储所有用户的表，还有一张用户详细信息的表，我希望将这两张表结合到一起来查看完整的数据，我们就可以通过使用外连接来进行查询，外连接有三种方式：

    • 通过使用inner join进行内连接，只会返回两个表满足条件的交集部分：

      SELECT * FROM a_table inner join b_table on a_table.tid = b_table.tid;

    在这里插入图片描述

    • 通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理，只是反过来而已）：

      SELECT * FROM a_table left join b_table on a_table.tid = b_table.tid;

    在这里插入图片描述

  • 嵌套查询

    SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件) -- 将查询的结果作为另一个查询的条件

数据库控制语言DCL

  • 创建用户

    CREATE USER 用户名;
CREATE USER 用户名 identified by 密码;

  • 登录用户

    login -u 用户名 -p

  • 添加授权

    通过使用grant来为一个数据库用户进行授权

    grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]

    其中all代表授予所有权限，当数据库和表为*，代表为所有的数据库和表都授权。如果在最后添加了with grant option，那么被授权的用户还能将已获得的授权继续授权给其他用户。

    如果要收回授权，可以使用revoke

    revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户

视图

视图可以理解为是一张查询出来的结果表，在这个表上操作不会影响到原表。通过CREATE VIEW来创建一个视图

CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];

WITH CHECK OPTION是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入

如现有一张表user，要限制视图更新时满足性别列sex为男时，可以使用以下语句：

CREATE VIEW user_view as select * from user where sex='male' WITH CHECK OPTION;

若要删除一个视图，使用drop：

DROP VIEW t_view

视图有以下规则：

  • 若视图是由两个以上基本表导出的，则此视图不允许更新。
  • 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
  • 若视图的字段来自集函数，则此视图不允许更新。
  • 若视图定义中含有GROUP BY子句，则此视图不允许更新。
  • 若视图定义中含有DISTINCT短语，则此视图不允许更新。
  • 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC： CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 　　导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。
  • 一个不允许更新的视图上定义的视图也不允许更新

索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。（引用自维基百科）

索引顾名思义，可以理解成给表中的每条数据添加一个目录，从而快速地定位到对应的数据行，而非遍历整个表，从而大大提高查询的速度。最常见的索引类型是基于B+树的索引。B+树是一种自平衡的树结构，每个节点可以拥有多个子节点，这允许树的高度保持相对较低，即使对于非常大的数据集也是如此。这意味着从根节点到叶子节点的路径较短，查询时可以快速找到数据所在的磁盘块位置。

索引实际是物理数据页，数据库页大小决定了一个页可以存储多少个索引行以及需要多少页来存储指定大小的索引。

-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 查看表中的索引
show INDEX FROM student
-- 删除索引
drop index 索引名称 on 表名

索引在提高检索速度的同时，也带来了一些负面影响，如需要耗费额外的储存空间来存储索引、更新、插入和删除数据时，数据库不仅需要更新数据，还需要维护索引的结构，这会增加写操作的开销（写操作性能下降）和需要定期维护索引避免失效

索引的分类

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
  • 按「字段个数」分类：单列索引、联合索引。

MySQL 中索引是在存储引擎层而非服务器层实现的，所以不同存储引擎层支持的索引类型可以不同。在 MySQL 中不同存储引擎间支持的常见索引类型有：哈希索引（Memory/InnoDB adaptive Hash index/NDB）、 B+Tree 索引（MyISAM/InnoDB）、全文索引（MyISAM/InnoDB）、空间索引（MyISAM R-Tree）、分形树索引（TokuDB Fractal Tree Index），如下表所示。

索引设计的数据结构

二分查找法

优点：等值查询、范围查询性能优秀

缺点：更新数据、新增数据、删除数据维护成本高

哈希

哈希表是数据库中哈希索引的基础，是根据键值 <key,value> 存储数据的结构。简单说，哈希表是使用哈希函数将索引列计算到桶或槽的数组，实际存储是根据哈希函数将 key 换算成确定的存储位置，并将 value 存放到该数组位置上。访问时，只需要输入待查找的 key，即可通过哈希函数计算得出确定的存储位置并读取数据。

哈希索引的实现

哈希索引是基于哈希表来实现的。对哈希索引列的数据通过哈希算法计算得出对应列的哈希码并构建成哈希表。由哈希码与指向真实数据行的指针组成了哈希索引。哈希索引的应用场景旨在对哈希索引列等值查询（即包括 =、IN()、<=>）（安全等于， select null <=> null 和 select null=null 是不一样的结果)才有效。哈希索引只储存哈希索引和行指针，不存储实际字段值，所以结构紧凑，查询速度快。在无哈希碰撞的情况下只需一次查询即可命中

哈希碰撞的处理

哈希碰撞是指不同索引列值计算出相同的哈希码，如上图所示， 表中 name 字段为 John Smith 和 Sandra Dee 两个不同key根据 Hash 算法计算出来的哈希码都是 152，这就是 Hash 碰撞。

对于 Hash 碰撞通用的处理方法是使用链表，将 Hash 冲突碰撞的元素形成一个链表，发生冲突时在链表上进行二次遍历找到数据。

  • Hash 碰撞跟选择的 Hash 算法有关系，为了减少 Hash 碰撞的概率，优先选择避免 Hash 冲突的 Hash 算法，例如，使用 Percona Server 的函数 FNV64() ，其哈希值为 64 位，出现 Hash 冲突的概率要比 CRC32 小很多。
  • 其次是考虑性能，优先选择数字类型的 Hash 算法，因为字符串类型的 Hash 算法不仅浪费空间而且不方便进行比较。

常见的 CRC32、SHA1 和 MD5 Hash 函数生成的返回值如下图所示。

image-20240731214526904

最后再看看，MySQL 中如何使用 Hash 索引？在 MySQL 中主要是分为 Memory 存储引擎原生支持的 Hash 索引 、InnoDB 自适应哈希索引及 NDB 集群的哈希索引3类。

InnoDB 自适应哈希索引

InnoDB 自适应哈希索引是为了提升查询效率，InnoDB 存储引擎会监控表上各个索引页的查询，当 InnoDB 注意到某些索引值访问非常频繁时，会在内存中基于 B+Tree 索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

创建要求：

  • 连续100次成功使用的SQL查询
  • 成功查询的次数超过block上记录数的十六分之一
  • 当前block上推荐的前缀索引列发生了变化

在数据库系统中，通常B+树的高度为3-4层，所以访问数据需要查询3-4次，再引入哈希索引可以在无哈希碰撞的情况下只查询1次，提高查询效率

B+树索引

在数据库中大部分索引都是通过 B+Tree 来实现的。 对于 B+Tree 具体的定义可以参考《数据结构》等相关书籍。 在 MySQL 数据库中讨论索引时，如果没有明确指定类型，则默认是指使用 B+Tree 数据结构进行存储，其说法等价于 B+Tree、B-Tree、BTREE（看到创建索引语句为 BTREE 也不要惊讶，等同于 B+Tree）。

对于 MySQL 存储引擎而言，其实际使用的 B+Tree 索引是为了满足数据读写性能，以及适配磁盘访问模式优化后的数据结构，每一个叶子节点都包含指向下一个叶子节点的指针。

在 MySQL 中，索引是在存储引擎层而非服务器层实现的，所以不同存储引擎层支持的索引类型可以不同。例如，虽然 MyISAM 和 InnoDB 的索引都是使用 B+Tree 实现的，但是其实际数据存储结构有不少差异。下图中 B+Tree 示例一共2层，图中每个页面都已经被随机编号（编号可以认定为页面号），其中页面号为 20 的页面是 B+Tree 的根页面（根页面通常是存放在内存中的），根页面存储了 <key+pageno>，pageno 是指向具体叶子节点的页面号。其他页面都是叶子节点，存放了具体的数据 <key+data>。

B+Tree 索引能够快速访问数据，就是因为存储引擎可以不再需要通过全表扫描来获取数据，而是从索引的根结点（通常在内存中）开始进行二分查找，根节点的槽中都存放了指向子节点的指针，存储引擎根据这些指针能够快速遍历数据。例如，通过页面号为 20 的根节点可以快速得知 Key小于10 的数据在 pageno 33 的页面，key在[10,16) 范围的数据在 pageno 56 的页面。

叶子节点存放的 <key+data> ，对于真正要存放哪些数据还得取决于该 B+Tree 是聚簇索引（Clustered Index）还是辅助索引（Secondary Index）。

聚簇索引和辅助索引

聚簇索引是一种数据存储方式，它表示表中的数据按照主键顺序存储，是索引组织表。InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree，B+Tree 的叶子节点就是行记录，数据行和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据。

而 InnoDB 辅助索引（也叫作二级索引）只是根据索引列构建 B+Tree，但在 B+Tree 的每一行都存了主键信息，加速回表操作。

聚簇索引占用的空间就是整个表数据量的大小，而二级索引会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。

InnoDB 只能创建一个聚簇索引（假想下如果能支持多个聚簇索引，那就意味着一张表按不同排序规则冗余存储多份全表数据了），但可以创建多个辅助索引。

相比索引组织表，还有一种堆表类型，堆表是根据数据写入的顺序直接存储在磁盘上的。对于堆表而言，其主键和辅助索引唯一的区别就是键值是否唯一，两者都是根据索引列排序构建 B+Tree 的，在每个叶子节点加上指向堆表的行指针（row data pointer） 。堆表在各类数据库中也被广泛使用，MyISAM 存储引擎的表就是堆表。

非聚簇索引均为辅助索引

无主键聚簇索引

前文可知，聚簇索引表示的是表中的数据按照主键顺序存储。对于没有指定主键的表，InnoDB 会自己选择合适字段为主键，其选择顺序如下：

  1. 显式主键；
  2. 第一个唯一索引（要求唯一索引所有列都非 NULL）；
  3. 内置的 6 字节 ROWID。

联合索引和覆盖索引

根据索引列个数和功能描述不同索引也可以分为联合索引和覆盖索引。

  • 联合索引是指在多个字段联合组建索引的。
  • 当通过索引即可查询到所有记录，不需要回表到聚簇索引时，这类索引也叫作覆盖索引。
  • 主键查询是天然的覆盖索引，联合索引可以是覆盖索引。

索引使用技巧

接下来介绍索引使用技巧的基础知识，这些知识可以帮助你建立高效索引，主要有谓词、过滤因子、基数（Cardinality）、选择率和回表。

谓词

谓词本身就是条件表达式，通俗讲就是过滤字段。如下文这句SQL语句

select * from city where city = 'BeiJing' and last_udpate = '2019-08-01'

其中的谓词就可以拆解为下面所示：

  • 简单谓词：city和 last_updata。
  • 组合谓词：city and last_updata。

知道谓词后就可以计算谓词的过滤因子了，过滤因子直接描述了谓词的选择性，表示满足谓词条件的记录行数所占比例，过滤因子越小意味着能过滤越多数据，你需要在这类谓词字段上创建索引。

过滤因子

过滤因子的计算算法，就是满足谓词条件的记录行数除以表总行数。

  • 简单谓词的过滤因子 = 谓词结果集的数量 / 表总行数
  • 组合谓词的过滤因子 = 谓词 1 的过滤因子 × 谓词 2 的过滤因子

基数

接着是基数（Cardinality），基数是某个键值去重后的行数， 索引列不重复记录数量的预估值，MySQL 优化器会依赖于它。

选择率

选择率是 count(distinct city) / count(*)，选择率越接近 1 则越适合创建索引，例如主键和唯一键的选择率都是 1。

回表

回表是指无法通过索引扫描访问所有数据，需要回到主表进行数据扫描并返回。

创建高效索引

基础知识熟悉后，我们通过实际的 SQL 语句来采集信息，借助这些基础信息可以创建高效索引。用一个例子来看下，如何快速根据 SQL 语句计算谓词、过滤因子、基数和选择率。

  1. 根据 SQL 语句可以快速得到谓词信息：简单谓词 city 和 last\_update，组合谓词 city and last\_update。
  2. 计算每个谓词信息的过滤因子，过滤因子越小表示选择性越强，字段越适合创建索引。例如：

  • city 的过滤因子 = 谓词 city 结果集的数量 / 表总行数

    select count(*) from city where city = ‘BeiJing’ / select count(*) from city；-- = 20%

  • last\_update 的过滤因子 = 谓词 last\_update 结果集的数量 / 表总行数

    select count(*) from city where last_update = ‘2019-08-01’/ select count(*) from city; -- = 10%
  • 组合谓词 = city 过滤因子 * last\_update 过滤因子 = 20% × 10% = 2%，组合谓词的过滤因子为 2%，即只有表总行数的 2% 匹配过滤条件，可以考虑创建组合索引 (city，last\_update)。

除谓词信息、过滤因子外，字段基数和选择率信息可以帮助你了解字段数据的分布情况。MySQL InnoDB 的统计信息参考基数 Cardinality 的信息。

Cardinality

Cardinality 能快速告知字段的选择性，高选择性字段有利于创建索引。优化器在选择执行计划时会依赖该信息，通常这类信息也叫作统计信息，数据库中对于统计信息的采集是在存储引擎层进行的。

执行show index from table_name会看到Cardinality，同时也会触发MySQL数据库对Cardinaltiy值的统计。除此之外，还有三种更新策略。

  • 触发统计：Cardinality统计信息更新发生在INSERT和UPDATE时，InnoDB 存储引擎内部更新的Cardinality信息的策略为：

    • 表中超过1/16的数据发生变化；
    • stat\_modified\_counter > 2000 000 000 （20亿）。
  • 采样统计（sample）：为了减少统计信息更新造成的资源消耗，数据库对Cardinality 通过采样来完成统计信息更新，每次随机获取 innodb\_stats\_persistent\_sample\_pages 页的数量进行 Cardinality 统计。
  • 手动统计：alter table table_name engine=innodb 或 analyze table table_name，当发现优化器选择错误的执行计划或没有走理想的索引时，执行 SQL 语句来手动统计信息有时是一种有效的方法。

由于采样统计的信息是随机获取8个（8是由innodb\_stats\_transient\_sample\_pages参数指定）页面数进行分析，这就意味着下一次随机的 8 个页面可能是其他页面，其采集页面的 Cardinality 也不同。因此当表数据无变化时也会出现 Cardinality 发生变化的情况，如下图所示。

关于统计信息的采集涉及如下主要参数。

  • information\_schema\_stats\_expiry:86400，Cardinality 存放过期时间，设置为 0 表示实时获取统计信息，严重影响性能，建议设置默认值并通过手动刷新统计信息；
  • innodb\_stats\_auto\_recalc:ON，是否自动更新统计信息，默认即可；
  • innodb\_stats\_include\_delete\_marked :OFF，计算持久化统计信息时 InnoDB 是否包含 Delete-marked 记录，默认即可；
  • innodb\_stats\_method:nulls\_equal，用来判断如何对待索引中出现的 NULL 值记录，默认为 nulls\_equal，表示将 NULL 值记录视为相等的记录；
  • innodb\_stats\_on\_metadata， 默认值 OFF，执行 SQL 语句 ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX，以及访问 INFORMATION\_SCHEMA 架构下表 tables和statistics 时，是否重新计算索引的 Cardinality 值；
  • innodb\_stats\_persistent:ON，表示通过 ANALYZE TABLE 计算得到的 Cardinality值存放到磁盘上；
  • innodb\_stats\_persistent\_sample\_pages:20，表示 ANALYZE TABLE 更新Cardinality 值时采样页的数量；
  • innodb\_stats\_transient\_sample\_pages:8，表示每次统计 Cardinality 时采样页的数量，默认为 8。

索引使用细节

至此，了解了这些基本信息后，我们开始学习索引使用过程中需要重点关注的细节。

首先是创建索引后如何确认 SQL 语句是否走索引了呢？创建索引后通过查看执行 SQL 语句的执行计划即可知道 SQL 语句是否走索引。执行计划重点关注跟索引相关的关键项，有 type、possible\_keys、key、key\_len、ref、Extra 等。

其中，possible\_keys 表示查询可能使用的索引，key表示真正实际使用的索引，key\_len 表示使用索引字段的长度。

另外执行计划中 Extra 选项也值得关注，例如 Extra 显示 using index 时就表示该索引是覆盖索引，通常性能排序的结果是 using index > using where > using filsort，如下图所示。

当索引选择组合索引时，通过计算 key\_len 来了解有效索引长度对索引优化也是非常重要的，接下来重点讲解 key\_len 计算规则。

key_len 表示得到结果集所使用的选择索引的长度[字节数]，不包括order by，也就是说如果order by也使用了索引则 key_len 不计算在内。

key_len 计算规则从两个方面考虑，一方面是索引字段的数据类型，另一方面是表、字段所使用的字符集。

  1. 索引字段的数据类型，根据索引字段的定义可以分为变长和定长两种数据类型：
  • 索引字段为定长数据类型，比如 char、int、datetime，需要有是否为空的标记，这个标记需要占用 1 个字节；
  • 对于变长数据类型，比如 varchar，除了是否为空的标记外，还需要有长度信息，需要占用 2 个字节；（备注：当字段定义为非空的时候，是否为空的标记将不占用字节）。
  1. 表所使用的字符集，不同的字符集计算的 key\_len 不一样，例如，GBK 编码的是一个占用 2 个字节大小的字符，UTF8 编码的是一个占用 3 个字节大小的字符。

举例说明：在四类字段上创建索引后的 key\_len 如何计算呢？

  • Varchr(10) 变长字段且允许 NULL:10*(Character Set：utf8=3，gbk=2，latin1=1)+1（标记是否为 NULL 需要 1 个字节）+ 2（变长字段存储长度信息需要 2 个字节）。
  • Varchr(10) 变长字段且不允许 NULL:10*(Character Set：utf8=3，gbk=2，latin1=1)+2（变长字段存储长度信息需要2个字节），非空不再需要占用字节来标记是否为空。
  • Char(10) 固定字段且允许 NULL:10*(Character Set：utf8=3，gbk=2，latin1=1)+1（标记是否为 NULL 需要 1 个字节）。
  • Char(10) 固定字段且不允许 NULL:10*(Character Set：utf8=3，gbk=2，latin1=1)，非空不再需要占用字节来标记是否为空。

最左前缀匹配原则

通过 key\_len 计算也帮助我们了解索引的最左前缀匹配原则。

最左前缀匹配原则是指在使用 B+Tree 联合索引进行数据检索时，MySQL 优化器会读取谓词（过滤条件）并按照联合索引字段创建顺序一直向右匹配直到遇到范围查询或非等值查询后停止匹配，此字段之后的索引列不会被使用，这时计算 key\_len 可以分析出联合索引实际使用了哪些索引列。

试设计性能索引

接下来我们根据实际SQL语句来判断索引的性能好坏，学习设计性能索引。创建一个test表。 在a、b、c上创建索引，执行表中的SQL语句，快速定位语句孰好孰坏。

CREATE TABLE `test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `a` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
    `b` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
    `c` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
    PRIMARY KEY(`id`),
    KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

运行以下SQL进行测试

可以得到结果：

  • SQL1 可以使用覆盖索引，性能好；
  • SQL2 可以使用覆盖索引同时可以避免排序，性能好；
  • SQL3 可以使用覆盖索引，但是需要根据 where 字句进行过滤；
  • SQL4 可以使用部分索引 a，但无法避免排序，性能差；
  • SQL5 完全使用覆盖索引，同时可以避免排序，性能好；
  • SQL6 可以使用覆盖索引，但无法避免排序，这是因为 MySQL InnoDB 创建索引时默认asc升序，索引无法自动倒序排序；
  • SQL7 可以使用覆盖索引，但是需要根据 where 子句进行过滤（非定值查询）。

设计高性能索引分析过程

在实际设计高性能索引时，可以结合前面讲解的内容按照如下步骤进行分析。

1. 定位由于索引不合适或缺少索引而导致的慢查询。

通常在业务建库建表时就需要提交业务运行相关的 SQL 给 DBA 审核，也可以借助Arkcontrol Arkit 来自动化审核。比如，慢查询日志分析，抓出运行慢的 SQL 进行分析，也可以借助第三方工具例如 Arkcontrol 慢查询分析系统进行慢查询采集和分析。在分析慢查询时进行参数最差输入，同时，对 SQL 语句的谓词进行过滤因子、基数、选择率和 SQL 查询回表情况的分析。

2. 设计索引。

设计索引的目标是让查询语句运行得足够快，同时让表、索引维护也足够快，例如，使用业务不相关自增字段为主键，减缓页分裂、页合并等索引维护成本，加速性能。也可以使用第三方工具进行索引设计，例如 Arkcontrol SQL 优化助手，会给出设计索引的建议。

3. 创建索引策略。

优先为搜索列、排序列、分组列创建索引，必要时加入查询列创建覆盖索引；计算字段列基数和选择率，选择率越接近于 1 越适合创建索引；索引选用较小的数据类型（整型优于字符型），字符串可以考虑前缀索引；不要建立过多索引，优先基于现有索引调整顺序；参与比较的字段类型保持匹配并创建索引。

4. 调优索引。

分析执行计划；更新统计信息（Analyze Table）；Hint优化，方便调优（FORCE INDEX、USE INDEX、IGNORE INDEX、STRAIGHT\_JOIN）；检查连接字段数据类型、字符集；避免使用类型转换；关注 optimizer\_switch，重点关注索引优化特性 MRR（Multi-Range Read）和 ICP（Index Condition Pushdown）。

  • MRR 优化是为了减少磁盘随机访问，将随机 IO 转化为顺序 IO 的数据访问，其方式是将查询得到辅助索引的键值放到内存中进行排序，通常是按照主键或 RowID 进行排序，当需要回表时直接根据主键或 RowID 排序顺序访问实际的数据文件，加速 SQL 查询。
  • ICP 优化同样也是对索引查询的优化特性，MySQL 根据索引查询到数据后会优先应用 where 条件进行数据过滤，即无法使用索引过滤的 where 子句，其过滤由之前 Server 层的数据过滤下推到了存储引擎层，可以减少上层对记录的检索，提高数据库的整体性能。

索引命名规范

最后讲解索引创建规范。各个公司都应形成开发规范，MySQL 创建索引规范如下。

  • 命名规范，各个公司内部统一。
  • 考虑到索引维护的成本，单张表的索引数量不超过5个，单个索引中的字段数不超过5个。
  • 表必需有主键，推荐使⽤UNSIGNED自增列作为主键。表不设置主键时InnoDB会默认设置隐藏的主键列，不便于表定位数据同时也会增大 MySQL 运维成本（例如主从复制效率严重受损、pt工具无法使用或正确使用）。
  • 唯一键由3个以下字段组成，并且在字段都是整形时，可使用唯一键作为主键。其他情况下，建议使用自增列或发号器作主键。
  • 禁止冗余索引、禁止重复索引，索引维护需要成本，新增索引时优先考虑基于现有索引进行rebuild，例如 (a,b,c)和 (a,b)，后者为冗余索引可以考虑删除。重复索引也是如此，例如索引(a)和索引(a,主键ID) 两者重复，增加运维成本并占用磁盘空间，按需删除冗余索引。
  • 联表查询时，JOIN 列的数据类型必须相同，并且要建⽴索引。
  • 不在低基数列上建⽴索引，例如“性别”。在低基数列上创建的索引查询相比全表扫描不一定有性能优势，特别是当存在回表成本时。
  • 选择区分度（选择率）大的列建立索引。组合索引中，区分度（选择率）大的字段放在最前面。
  • 对过长的 Varchar 段建立索引。建议优先考虑前缀索引，或添加CRC32或MD5伪列并建⽴索引。
  • 合理创建联合索引，(a,b,c) 相当于(a)、(a,b)、(a,b,c)。
  • 合理使用覆盖索引减少IO，避免排序。

触发器

触发器就像其名字一样，在某种条件下会自动触发，在select/update/delete时，会自动执行我们预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活。

触发器所依附的表称为基本表，当触发器表上发生select/update/delete等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）

比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中。

-- 创建触发器
CREATE TRIGGER 触发器名称 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno
-- 查看触发器
SHOW TRIGGERS
-- 删除触发器
DROP TRIGGER 触发器名称

事务

当我们要进行的操作非常多时，比如要依次删除很多个表的数据，我们就需要执行大量的SQL语句来完成，这些数据库操作语句就可以构成一个事务！只有Innodb引擎支持事务，我们可以这样来查看支持的引擎：

SHOW ENGINES;

MySQL默认采用的是Innodb引擎，我们也可以去修改为其他的引擎。

事务特性（ACID）

实现事务必须要遵守 4 个特性，分别如下：

  • Atomicity（原子性）： 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • Consistency（一致性）： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • Isolation（隔离性）： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • Durability（持久性）： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

实现事务特性的技术

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

  • 持久性是通过 redo log （重做日志）来保证的；
  • 原子性是通过 undo log（回滚日志） 来保证的；
  • 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
  • 一致性则是通过持久性+原子性+隔离性来保证；

事务的隔离级别

  • Read Uncommitted（读未提交）：最宽松的隔离级别，这个级别可以看到其他事务未提交的数据更改。这种级别存在严重的并发问题，包括脏读（Dirty Read）、不可重复读（Non-Repeatable Read）和幻读（Phantom Read）。
  • READ COMMITTED（读提交）：一个事务只能看到已经提交的其他事务所做的更改。这样可以避免脏读，但是仍然可能发生不可重复读和幻读。
  • REPEATABLE READ（可重复读）：一旦事务开始，它就能看到一个一致的数据库视图，即使有其他事务在这段时间内提交了更改。然而，仍然可能发生幻读。这是MySQL默认的隔离级别。
  • SERIALIZABLE（序列化）：这是最严格的隔离级别，可以防止脏读、不可重复读和幻读。在这个级别下，事务是完全隔离的，它们之间的操作必须串行化执行，这可能会导致性能降低，但在确保数据一致性方面是最强的。
事务隔离级别脏读不可重复读幻读
READ UNCOMMITTED 读未提交是是是
READ COMMITTED 读提交否是是
REPEATABLE READ 可重复读否否是
SERIALIZABLE 序列化否否否

img

数据不一致现象

  • Dirty Read（脏读）：指一个事务读取到了另一个事务未提交的数据。如果后续该事务回滚，那么第一个事务读取的数据就是无效的。
  • Non-repeatable Read（不可重复读）：指在一个事务内多次读取同一数据时，其结果可能不同。这是因为其他事务在这期间提交了对数据的修改。例如，一个事务在开始时读取了一行数据，然后在事务结束前再次读取同一行数据时，发现数据已经被其他事务修改。
  • Phantom Read（幻读）：指在一个事务内，两次相同的查询返回的结果集不同。这种现象通常发生在第二次查询时，由于其他事务插入了新的行，导致结果集中出现了第一次查询时不存在的行。由于InnoDB使用的是多版本并发控制（MVCC），在Repeatable Read隔离级别下仍有可能发生幻读。只有在Serializable隔离级别下，通过加锁的方式才能完全避免幻读。

我们通过以下例子来探究以下事务：

begin;   #开始事务
...
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
...
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！

多版本并发控制（MVCC）

  • InnoDB存储引擎，实现的是基于多版本的并发控制协议MVCC
  • MVCC最大好处是读不加锁，读写不冲突
  • 现阶段几乎所有的RDBMS都支持MVCC

  • 在MVCC并发控制中读操作可分为Snapshot Read（快照读）和Current Read（当前读）

    • 快照读：简单的select操作，不加锁

      select * from table where ?

    • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需加锁

      select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (...);
update table set ? where ?;
delete from table where ?;
  • MVCC只在READ COMMITED和REPEATABLE READ两个隔离级别下工作

MVCC中多版本的实现

  1. 假设现有一张表，F1~F6为表中字段名，1~6则为对应字段的数据。该数据行后面会有三个隐藏字段分别对应该行的隐含ID、事务号和回滚指针

    • 隐含 ID（DB\_ROW\_ID），6 个字节，当由 InnoDB 自动产生聚集索引时，聚集索引包括这个 DB\_ROW\_ID 的值。
    • 事务号（DB\_TRX\_ID），6 个字节，标记了最新更新这条行记录的 Transaction ID，每处理一个事务，其值自动 +1。
    • 回滚指针（DB\_ROLL\_PT），7 个字节，指向当前记录项的 Rollback Segment 的 Undo log记录，通过这个指针才能查找之前版本的数据。

  2. 首先，假如这条数据是刚 INSERT 的，可以认为 ID 为 1，其他两个字段为空。然后，当事务 1 更改该行的数据值时，会进行如下操作，如下图所示

    • 用排他锁锁定该行；记录 Redo log；
    • 把该行修改前的值复制到 Undo log，即图中下面的行；
    • 修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。

  3. 接下来，与事务 1 相同，此时 Undo log 中有两行记录，并且通过回滚指针连在一起。因此，如果 Undo log 一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的是在 InnoDB 中存在 purge 线程，它会查询那些比现在最老的活动事务还早的 Undo log，并删除它们，从而保证 Undo log 文件不会无限增长，如下图所示。

锁

MySQL三种锁的类别

  • 表级锁：开销小，加锁快；不会出现死锁；粒度大，锁冲突概率最高，并发度最低
  • 行级锁：开销大，加锁慢；会出现死锁；粒度最小，锁冲突概率最低，并发度最高
  • 页级锁：开销和加锁时间界于两者间；会出现死锁；粒度界于两者之间，并发度一般

三种级别的锁分别对应存储引擎关系

InnoDB锁分类

  • 行锁

    • 共享锁（S）：也称为读锁，允许一个事务读取一个数据项，同时阻止其他事务对该数据进行写操作，但允许其他事务读取同样的数据。故多个事务可以同时对同一数据项持有共享锁，只要没有事务试图对其进行写操作。共享锁的主要目的是支持读取操作，同时确保数据的一致性。
    • 排他锁（X）：也称为写锁，允许一个事务读取和写入一个数据项，同时阻止其他任何事务读取或写入该数据项。这意味着如果一个事务持有了排他锁，那么其他所有事务都将被阻止，直到该锁被释放。排他锁主要用于支持写操作，确保数据在修改时不会被其他事务干扰。

  • 表锁

    • 意向共享锁（IS）：它表示事务想要在未来的某个时刻获取一个共享锁。当一个事务获取了IS锁，它表明该事务想要在某个数据块上读取数据，但并不立即读取。这种锁可以与其他的IS锁或S锁兼容，但与IX锁或X锁不兼容。使用场景如下：

      • 当事务开始时，如果事务打算在多个数据页上获取共享锁，它可能先获取IS锁，然后再升级为S锁。
      • 在InnoDB的Repeatable Read隔离级别下，事务在读取数据时会获取IS锁，以防止其他事务插入新的行，即防止幻读。

    • 意向排他锁（IX）：与IS锁类似，。当一个事务获取了IX锁，它表明该事务想要在未来的某个时刻获取一个排他锁。IX锁可以与其他的IX锁或IS锁兼容，但与S锁或X锁不兼容。使用场景如下：

      • 当事务开始时，如果事务打算在多个数据页上获取排他锁，它可能先获取IX锁，然后再升级为X锁。
      • IX锁用于防止其他事务在某个范围内的数据上获取任何类型的锁，直到该事务完成其操作。
    • 自增锁（AUTO-INC Locks）：详见下文

InnoDB锁关系矩阵

InnoDB 锁关系矩阵如下图所示，其中：+ 表示兼容，- 表示不兼容。

InnoDB自增锁

在设计表结构时，通常会添加一列id作为自增主键，这时就会涉及到自增锁（AUTO-INC Locks）

  • 自增锁属于表锁的一种，在insert结束后立即释放
  • 可以通过执行show engine innodb status来查看自增锁的状态信息

使用自增锁时，有一个核心参数innodb_autoinc_lock_mode,它有三个值，分别代表如下：

  • 0：Traditional locking mode (传统锁定模式)，是一种旧的表锁方式，不推荐。在这个模式下，InnoDB在每次事务开始时获取一个全局的AUTO_INCREMENT锁，直到事务结束。这意味着在同一个事务中进行的所有插入操作都会使用相同的自增值。这种模式下，如果有多个事务同时启动，它们都会等待全局的AUTO_INCREMENT锁，直到前一个事务完成。这种方式可以避免自增ID的冲突，但会导致较高的锁竞争，影响并发性能。
  • 1：Per-table locking mode (按表锁定模式)，这是默认的模式。在这个模式下，InnoDB在插入操作开始时获取一个表级别的AUTO_INCREMENT锁，直到该插入操作完成。这意味着在同一个事务中，连续的插入操作可能会获得不同的自增值。这种方式比模式0提供了更好的并发性能，因为锁的竞争仅限于正在进行插入操作的时间段。
  • 2：Optimized per-table locking mode (优化的按表锁定模式)，这是进一步优化的模式，旨在减少锁定的时间长度，从而提高并发性能。在这个模式下，InnoDB在插入操作开始时获取AUTO_INCREMENT锁，但是在确定了自增值之后立即释放锁，而不是等到插入操作完全完成。这使得其他事务可以在等待插入操作完成之前就获取自增值，进一步减少了锁的竞争。

InnoDB行锁实现

  • Next-Key Lock：是 InnoDB 中一种特殊的行锁类型，它是 Record Lock（记录锁）和 Gap Lock（间隙锁）的组合。当一个事务需要读取或修改一个特定的行时，InnoDB 会为该行加上 Record Lock。此外，InnoDB 还会在行的“next key”（即下一个唯一索引项）上加 Gap Lock，以防止其他事务在该行与下一个行之间插入新行，这样可以防止幻读（Phantom Reads）的发生。
  • Gap Lock： 是用于锁定索引记录之间的“间隙”的锁，它们用于阻止其他事务在已锁定记录之间插入新记录。Gap Locks 主要是在 Repeatable Read 隔离级别下使用，以防止幻读，即防止事务读取到在事务开始后插入的新行。假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

  • Record Lock: 单行记录的锁（锁数据，不锁GAP）。记录锁有 S 锁和 X 锁之分：

    • 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
    • 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。
  • Lock Manager： 负责管理所有的锁请求和锁冲突。当一个事务请求一个锁时，Lock Manager 会检查是否有冲突的锁存在。如果存在冲突，事务将会等待，直到锁释放。Lock Manager 使用多种算法和数据结构来跟踪锁的持有者和等待者，以确保锁的分配是公平且高效的。
间隙锁的工作原理

假设有一个索引B树，其中包含数值1, 5, 9。如果一个事务正在读取所有大于等于1且小于等于5的行，那么除了锁定数值为1和5的行外，InnoDB还会在数值1之前和数值5之后的间隙上放置间隙锁。这阻止了其他事务在这两个间隙内插入任何新行。具体来说，以下情况会被阻止：

  • 插入数值2、3或4（在1和5之间）
  • 插入数值0或小于1的任何值（在1之前）
  • 插入数值6或大于5的任何值（在5之后）
间隙锁与Next-Key Locks

在InnoDB中，为了更有效地处理间隙锁，引入了Next-Key Locks的概念。Next-Key Locks实际上是行锁和间隙锁的组合。对于每一行，InnoDB不仅锁定该行，还锁定该行前面的间隙。因此，Next-Key Locks可以被认为是一种更细粒度的锁定策略，它能更精确地控制对索引项的访问。

排查InnoDB锁问题

  1. 打开innodb_lock_monitor表（使用后记得关闭，会影响性能）

    -- 开启标准监控
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

-- 关闭标准监控
DROP TABLE innodb_monitor;

-- 开启锁监控
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

-- 关闭锁监控
DROP TABLE innodb_lock_monitor;

  2. 查看information_schema库下面的innodb_locks，innodb_lock_waits，innodb_tex三个视图

    • INNODB_TRX:打印阻塞innodb内核中的事务
    • INNODB_LOCKS：打印当前产生的innodb锁（仅在阻塞时打印）
    • INNODB_LOCK_WAITS：打印当前产生的innodb锁等待（仅在阻塞时打印）

InnoDB不同索引的加锁行为

  • 主键+RR

    假设条件是：

    • update t1 set name=‘XX’ where id=10。
    • id 为主键索引。

    加锁行为：仅在 id=10 的主键索引记录上加 X锁。

  • 唯一键+RR

    假设条件是：

    • update t1 set name=‘XX’ where id=10。
    • id 为唯一索引。

    加锁行为：

    • 先在唯一索引 id 上加 id=10 的 X 锁。
    • 再在 id=10 的主键索引记录上加 X 锁，若 id=10 记录不存在，那么加间隙锁。

  • 无索引+RR

    假设条件是：

    • update t1 set name=‘XX’ where id=10。
    • id 列无索引。

    加锁行为：

    • 表里所有行和间隙均加 X 锁。

  • 非唯一键+RR

    假设条件是：

    • update t1 set name=‘XX’ where id=10。
    • id 为非唯一索引。

    加锁行为：

    • 先通过 id=10 在 key(id) 上定位到第一个满足的记录，对该记录加 X 锁，而且要在 (6,c)~(10,b) 之间加上 Gap lock，为了防止幻读。然后在主键索引 name 上加对应记录的X 锁；
    • 再通过 id=10 在 key(id) 上定位到第二个满足的记录，对该记录加 X 锁，而且要在(10,b)~(10,d)之间加上 Gap lock，为了防止幻读。然后在主键索引 name 上加对应记录的X 锁；
    • 最后直到 id=11 发现没有满足的记录了，此时不需要加 X 锁，但要再加一个 Gap lock： (10,d)~(11,f)。

死锁产生的四个条件

  1. 互斥条件：一个资源每次只能被一个进程使用
  2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
  3. 不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺
  4. 循环等待条件：多个进程之间形成一种互相循环等待资源的关系

如何避免死锁

  • 加锁顺序一致
  • 基于primary或unique key更新数据
  • 单次操作数据量不宜过多，表尽量少
  • 减少表上索引，减少锁定资源
  • 设置事务等待锁的超时时间。InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
  • 开启主动死锁检测，将参数 innodb_deadlock_detect 设置为 on
  • 使用pt-deadlock-logger

查看死锁

可以通过执行show engine innodb status来查看检测到的死锁

InnoDB死锁优化

  • 更新sql的where条件尽量用索引
  • 加锁索引准确，减少锁定范围
  • 减少范围更新，尤其非主键/非唯一索引上的范围更新
  • 控制事务大小，减少锁定数据量和锁定时间长度（innodb_row_lock_time_avg）
  • 加锁顺序一致，尽可能一次性锁定所有所需数据行

select执行过程

  1. 客户端通过通信协议向Mysql服务端发起请求
  2. 查询缓存将语句文本 SELECT与发送给客户端的相应结果一起存储。如果稍后收到相同的查询语句，服务器将从查询缓存中检索结果，而不是再次解析和执行该语句；如果无缓存，则会经过解析器进行语法语义解析生成解析树
  3. 到语义解析器生成新的解析树
  4. 到查询优化器生成执行计划
  5. 到查询引擎执行真正SQL
  6. 查询引擎通过不同存储引擎的API向底层存储引擎交互获取结果，由Mysql过滤缓存后再返回给客户端
最后修改：2024 年 08 月 07 日
© 允许规范转载
如果觉得我的文章对你有用，能不能v我50参加疯狂星期四