关系数据库（MySQL）原理学习小记（架构、索引、锁、日志、高可用/高性能等）

 

这里以MySQL为例。

要掌握的主要内容是 索引（优劣、类型、场景）、架构和存储引擎、日志（两阶段提交等）、事务、锁、分库分表等。

 

1 什么是索引

数据库索引，是数据库管理系统中一个排序的数据结构。

对数据记录建立索引后，每条索引记录包含：（1）值与相应 数据记录 被索引列的值一样的键（2）相应数据记录的地址。

“索引的实现通常使用B树及其变种B+树”，即采用B树等对 索引记录 按键进行排序。

 

MySQL中约束（constraint）与索引（index）的区别：

约束是逻辑概念，用以保证数据的完整性。比如 InnoDB 存储引擎支持的约束有：Primary Key, Unique Key, Non NULL, Foreign Key, Default 。

索引是数据结构，既有逻辑上的概念，还代表物理存储的方式。通过索引来实现一些约束。

 

 

2 使用索引的利弊

利：加快查询速度

弊：索引需要占用空间；创建和维护索引（插入、修改、删除数据时）需要时间消耗。

查询性能和修改（插入、删除、更新）性能是一对矛盾：索引可以提高查询性能，但由于需要维护索引所以会影响修改的性能，此外还会增加存储空间占用。

 

创建索引可以大大提高系统的性能。

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 

 

3 索引适用条件

适用：常查询的列、where条件中的列（范围、单条件等）、经常用于表连接的列、经常需要排序的列等。

不适用：查询时少用到的列、取值情况少的列、定义为text, image和bit数据类型的列、频繁更新的列等。

 

取值情况少的列即使用索引，由于取值情况少，使得根据索引记录得到的中间结果会有很多，从而索引没多大效用；数据库通常会忽略这种索引直接扫描。对于取值情况少且取值变换不频繁的数据的列，可以采用位图索引，详情参阅 http://www.cnblogs.com/LBSer/p/3322630.html 

注：

尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

索引字段类型不要过长否则占用太多空间，一个节点中的关键字就越少了；索引字段不建议用无序的值（如UUID）因为数值随机容易导致B+树节点分裂或合并，费时又费空间（产生磁盘碎片）。

 

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询索引得到的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

4 索引类型 

树索引

物理存储上看：聚簇索引、非聚簇索引（二级索引或辅助索引）。

数据结构上看（即实现上看）：B+ 树索引、hash索引、全文索引等，见后文索引实现一节。

关键字约束上看：

唯一索引、非NULL索引（主键索引同时属于这两者）、普通索引（允许重、允许NULL）、联合索引、外键（若无显式加索引则会自动添加）

查询匹配上看：

前缀索引：即联合索引，经验是将选择性最高的列放到前缀索引最前列，可以在查询的时候过滤出更少的结果集。

覆盖索引：顾名思义，只需要在一棵索引树上就能获取SQL查询所需的列数据，这种查询就是索引覆盖查询。无需回表，速度更快。 explain的输出结果Extra字段为Using index时，能够触发索引覆盖。根据查询语句适当地给列建立索引或联合索引可以使得达到索引覆盖，从而无需回表，提高查询效率，示例见下文。

InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这种就是覆盖索引。

关于MySQL的绝大多数树索引相关 question 都可以从其数据结构和物理存储上去理解和回答。

MySQL索引创建相关语句：

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED] INDEX <索引名> ON <表名>(<列名>[ASC|DESC] [, <列名>[ASC|DESC]...])

ALTER INDEX <旧引索名字> RENAME TO<新引索名>

DROP INDEX<索引名>

 

 

这里着重讲聚簇索引和非聚簇索引（更多可参阅 https://www.cnblogs.com/jiawen010/p/11805241.html）。

区别：在MySQL中都是B+树索引，从数据的存储差异上区分（根据行数据是否和索引一起存放在一棵树上）：

聚簇索引：根据主键构建起B+树且叶节点存放了该关键字所在的行数据。它是其他二级索引的基础。

可见其索引文件和数据文件未分开是一体的、且叶节点索引项顺序和行数据物理存储的顺序一致。这也是“clustered”的含义，决定了每张表只能有一个聚簇索引、也决定了只能有一个主键。

聚集索引的存储是逻辑的连续而非物理上的。即页内是连续的，但是如果在多个页中，则并不需要在物理上连续。

非聚簇索引：根据关键字（可以但不一定是主键）构建B+树且叶节点存放的是不是行数据而是数据的地址（MyISAM）或主键值（InnoDB）。

可见需要通过该值再查一次得到数据记录，故也称为二级索引或辅助索引，例如非主键的唯一索引、非空索引、前缀索引、联合索引等是非聚簇索引；二级索引的存在不影响聚簇索引中数据的组织，故一张表可有多个二级索引。

优劣：后者查数据时需要多一次查询故相比于前者查询效率低；但写非聚簇索引时更快因为叶节点只是一个简单关键字而非整行数据。

使用场景：MySQL中InnoDB、MyISAM存储引擎的主键列分别用的是聚簇索引、非聚簇索引，而非主键列用都用的是非聚簇索引。可见，聚簇索引基于主键构建但基于主键构建的索引不一定是聚簇索引。

InnoDB中的聚簇索引列：若表定义了主键则PK就用聚集索引；若表没有定义主键则第一个非空唯一索引（not NULL unique）列用聚集索引； 否则，InnoDB会创建一个隐藏的row-id作为聚集索引列。最好选择值按写入递增的列做主键而非字符串等随机值的列否则节点频繁分裂从而写入性能会低。

InnoDB的非聚簇索引查询中，查到关键字后内部需要根据关键字去聚簇索引查行数据，此即一种回表查询：即先定位主键值，再定位行记录。可见，聚簇索引不会回表、非聚簇索引则会。

两种存储中非聚簇索引的区别：都要再查一次，但前者叶节点存的是主键的值故需要通过该值再去聚簇索引中查一次得到行数据、而后者叶节点存的是行数据的地址故需要通过该地址再查一次得到行数据，示意图：

InnoDB的非聚簇索引与MyISAM的相比存的是主键值而非行数据的地址，优点：移动数据或者数据页面分裂时不需要更新地址故减小了维护二级索引的开销。

实际上，MyISAM不支持聚簇索引，在MyISAM中，Primary key和其它索引没有什么区别，Primary key仅仅只是一个叫做PRIMARY的唯一，非空的索引而已。

是否需要回表——回表查询、索引覆盖查询（回表等价于索引不覆盖、不回表等价于索引覆盖）：查询时是否需要根据第一次在索引树中的查询结果再去其他地方查才得到最终数据，若不需要则是覆盖索引、否则就叫回表。聚簇索引中查不需要回表、是覆盖索引，只有非聚簇索引可能需要回表且是否要回表、是否索引覆盖则与查询条件有关。

比如对于上述(id, name, company)的表，

例1：select name from t where id='5' 和 select id where name='Jobs' InnoDB只需要查一个索引树即得结果而MyISAM除此外还需回表得到数据，故前者索引覆盖、不需回表而后者相反。

例2：select company from t where name='Jobs' 则在两种存储引擎下都无索引覆盖。

若添加了联合索引index(name, company) 则两者就索引覆盖了；在有该联合索引的前提下若select的是id则前者索引覆盖而后者没有。

例3：select * 查询时通常应把 * 改为需要的具体列，这样更可能用到覆盖索引。

减少回表次数——索引条件下推（ICP，Index Condition Pushdown）：详情可参阅 https://www.cnblogs.com/three-fighter/p/15246577.html。

含义：顾名思义，就是把MySQL架构中Server层进行的部分索引列的过滤工作下移到了存储层做，这也是“下推”的体现。常规的查询中，存储引擎层先通过where中索引条件找到（可能不是索引条件都用上比如索引失效）行数据列表（可能比最终需要的数据更多），然后Server层再根据where条件对该列表进一步过滤得到最终结果。MySQL 5.6引入了ICP，改进在于在索引遍历过程中会判断索引项是否可以进行where条件过滤，也即提前执行where的部分过滤操作，使得返回给Server层的数据减少，这就相当于减少了回表次数。

索引下推的作用：

磁盘到内存——减少了存储引擎查询基础表的次数也即回表次数，从而减少了IO次数。

内存到内存——减少了MySQL Server从存储引擎接收数据量。

聚簇索引的数据和索引是在一棵树上的不存在回表这一说，只有非聚簇索引才有可能回表，故索引条件下推只可能发生在非聚簇索引上。

示例：

、

 

 

 

其他：

InnoDB 支持行锁和表锁，默认使用行锁，而 MyISAM 使用的是表锁，所以使用索引可以让查询锁定更少的行，这样也会提升查询的性能，如果查询中锁定了1000行，但实际只是用了100行，那么在 5.1 之前都需要提交事务之后才能释放这些锁，5.1 之后可以在服务器端过滤掉行之后就释放锁，不过依然会导致一些锁冲突。

事实上，当查出的行数据包含表中大部分的数据时，即使添加了B+树索引，数据库如oracle、mysql也可能不适用该索引，很有可能还是一行行全部扫描。

数据排序（Order By）的原理：MySQL有两种方式生成有序结果——即Server层对结果集进行排序 或 存储引擎层按照索引顺序扫描得出的结果自然是有序的，后者只有当索引列的顺序和ORDER BY子句的列顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。

 

5 索引失效的场景

（更多详情可参阅 MySQL索引失效的9种场景、15个索引失效场景）

• 确定：
  • 设置联合索引但查询条件不符合最左匹配原则（或成最左前缀、最左前缀匹配）：联合索引，查询时的条件列不是联合索引中的列的顺序，索引失效。考虑联合索引的树结构来体会为何此时会失效，见本节末图。因此，在设置联合索引时，将使用最频繁或取值情况最多的列放左边，以提高索引效果。
    • PS：这里只select列包含非联合索引列的情形会不走索引；若select列都是索引列则即使where不符合最左前缀也是会走索引的（即索引覆盖），只不过此时索引用于免回表、但起不到游标加快定位的作用。
  • 在索引列上使用mysql的内置函数，索引失效。如select * from class where left(className,4) = '好好生活';
  • 对索引列运算（如，+、-、*、/），索引失效。如select * from class where id + 1 = 666;
  • 列字段类型是字符串，where时一定用引号括起来，否则会因隐式类型转换而索引失效（InnoDB存储引擎对于字符串类型列与整数类型值的比较，默认会将字符串转为整数，此时相当对列用了内置函数故索引会失效），如select * from class where className = 666; 。当然，除了索引失效，正确性也可能受影响，因为字符串转整数可能会截断。
    • 相反，整数类型列与字符串类型值比较则不会索引失效，因为会将值隐式转换为int类型而不是对列转换。可见，整型与字符串的比较在隐式转换时是转为整型。当然若是显式对列转换则仍会失效，如： explain select * from class where convert(int_id,char) = '666'; 。
    • 总结：列类型和where值类型不匹配可能会导致索引失效。有列字符串值整数、列整数值字符串两种情形，按“InnoDB会将字符串转为整型然后对比”来例假哪种会索引失效。（PS，把整数转出字符串不是更好？为何不这样？）
  • like查询以通配符开头，索引失效。如select * from class where className like "%好好生活";
  • 查询条件中进行两个索引列（可以是同一个）的比较，如 select * from class where id>grade_id; 
  • MySQL优化器估计使用全表扫描要比使用索引快，则不使用索引。
• 可能（在SQL语句不符合上述确认索引失效情形的前提下）：（大多是因查询结果集大触发MySQL优化器行为）
• 索引字段上使用（ != 或者 < >，not in、not exists）时，可能会导致索引失效（与查询结果记录数占总记录数的比例有关，比如值唯一的列肯定会走），用=、in、exists、between and则可以走索引。如select * from class where className != "好好生活";
• 索引字段上使用is null， is not null，可能导致索引失效。如select * from class where className is null; # 或 is not null
• 查询条件包含or时若or左右至少有一个非索引列则索引失效，因为有非索引列就必定要全表扫描那么索引列就没必要走索引了。如 select * from class where id =1 or address = '北京'; # 假设id有索引而address没有。
  • 因此，or左右的列最好都是索引列才可能走索引。当然，即使在左右都是索引列情况下，若左右同时有 > 和 < 则也会索引失效，如 selece * fom class where id>1 or id<6; ，不过高版本中会进行索引合并以解决失效问题。
• order by 索引列，取决于select列：若select列可索引覆盖则走索引（减少回表但不加快定位）
• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

示例：

不符合最左匹配原则时为何索引会失效？

下图中，name有序、name相等时age才有序，故若where条件中的列不包含name则无法确定要往哪个子节点走，从而只能放弃索引转而全表扫描。

 

 

 

 

6 索引列的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

• 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。数据类型越小，B+树一个节点中的关键字就越多，进而树高越小，从而IO次数更少。
• 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
• 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

 

7 索引的实现

B、B+ 树的原理和区别：见 特殊搜索树之B+ tree——MarchOn。MySQL中的B+树节点高通常不超过3。

Hash索引与B+ 树索引的区别主要有：

后者支持范围查询、排序、索引前缀匹配、模糊查询等，前者不支持；

前者等值查询效率比后者高，O(1)与O(lgn) 的区别；

 

8 MySQL进阶

可从使用上串起各知识点：一条更新SQL的执行流程、加锁过程，一条查询SQL的执行过程。

8.1 架构

逻辑架构图：

• 客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
• Server层：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
• 存储引擎层：第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

8.2 存储引擎

不同存储引擎支持的功能：

 

MySQL5.5起将默认的存储引擎从MyISAM改为InnoDB，从5.6起InnoDB支持全文索引。

InnoDB和MyISAM区别主要有：

底层索引：前者支持聚簇和非聚簇索引索引、主键（显式或隐式）必须，而后者是非聚簇索引、允许表无主键；

上层索引：前者支持树索引、全文索引、哈希索引等，后者不支持哈希索引；

锁：前者支持行锁、表锁而后者支持表锁；

功能：前者支持事务、外键而后者不支持；

使用：后者有变量记录数据行数故对于不加条件的count * 比前者的快（O(1)时间），但有where条件则无该优势。

存储引擎的选择，大致上可如下：

大多数情况下，使用默认的InnoDB就够了。如果需要事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB。

如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎存储临时表等查询的中间结果。 

使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。

 

8.3 日志（原子性一致性持久性的实现）

（详见文末推荐的参考资料）

主要包括日志类型、日志的用途——写问题（buffer写、两阶段提交等）和读问题（慢查询）解决。

 

8.3.1 日志类型

MySQL Server的（general log、error log、slow query log、bin log） + InnoDB存储引擎的（redo log、undo log）。

MySQL日志文件有很多，包括 ：

• 一般查询日志（general log）：一般查询日志记录了所有对MySQL数据库请求的信息，无论请求是否正确执行。
• 错误日志（error log）：错误日志文件对MySQL的启动、运行、关闭过程进行了记录，能帮助定位MySQL问题。
• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
• 二进制日志（bin log）：它记录数据库所有执行的DDL和DML语句（除了数据查询语句select、show等）即表结构变化和数据变化，以事件形式记录并保存在二进制文件中。是mysql服务层产生的日志，常用来进行数据恢复、数据库复制，例如主从架构的数据复制、到其他数据源如ES的复制。

还有两个InnoDB存储引擎特有的日志文件（当事务对数据库进行修改，InnoDB引擎不仅会记录redo log还会生成对应的undo log日志）：

• 重做日志（redo log）：记录事务物理层面修改的日志，比如页号xxx、偏移量yyy写入了'zzz'数据。用于在数据库故障时数据的恢复，保证了数据持久性。
• 回滚日志（undo log）：记录事务逻辑操作的相应反操作日志，比如对某一行数据进行了INSERT语句操作那么 undo log就记录一条与之相反的DELETE操作。用于数据回滚，如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子。

 

bin log 和 redo log的区别：

范围：前者是MySQL Server的，记录了包括所有不同存储引擎的更新操作的日志；后者只记录InnoDB存储引擎的更新日志。

内容：前者记录的是关于事务逻辑操作的日志；后者记录的是关于物理层面每个page更改情况的日志（因为更新行时是读行所在的一页来更新，显然记录时记该页的变化）。

写入：前者在事务提交前写入、只写一次、以追加方式写入，只增不减；后者在事务过程中不断多次写入、可能会删除再写入，取决于事务中的SQL操作。

示例：假设一个事务对表做了10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入bin log文件中。

用途：前者通常用于数据库实例间或到其他数据库的数据复制，后者通常用于数据库故障时的数据恢复以保证数据持久性。

 

主要用途：bin log用于主从架构下数据复制、redo log用于服务故障时丢失数据的恢复、undo log用于数据回滚。

由这些类型的日志引申的问题：

bin log、redo log：写的问题——更新操作的流程、事务持久性的保证、WAL、两阶段提交等。事务的持久性用redo log解决。

undo log：读的问题——数据的回滚、事务的一致性、原子性用undo log解决。悲观锁（行锁、表锁）和乐观锁（MVCC）都用到undo log。

slow query log：读的问题——慢查询的分析、优化等。

 

8.3.2 写问题——更新操作的执行流程

总纲：

为解决数据IO慢而使用了内存缓冲区（数据写内存而不是立即写磁盘），内存缓冲区带来的数据丢失问题用WAL技术解决（即数据内存写再日志写）。

日志中的redo log频繁产生（bin log则不会），故同样存在IO慢的问题，所以其也引入了内存缓冲区，但不考虑其内存缓冲区中数据丢失问题，而是通过尽可能及时刷盘策略来减少丢失风险。

基于上述思路，本节主要知识点：内存写再日志写、日志写的作用、日志写时事务的两阶段提交的目的和内容、redo log落盘时机。

 

流程

一条更新语句的执行流程：连接、权限检查、SQL解析、取数据（内存查询缓存取不存在则从磁盘取行所在的一页数据）、内存数据更新、日志写（两阶段提交）操作，核心在于先写数据到内存再写日志。

包括：Server层进行SQL的解析和执行，还写binlog；InnoDB存储引擎进行数据的读和写，还写redo log，且事务是两阶段提交的。

为何先写内存后不直接写磁盘？为免频繁IO，先在内存攒一定数据再写磁盘。

注：里面涉及到加锁流程见后文锁相关的节。

为何内存写后要再日志写（日志写的作用）

作用：用于防止内存数据丢失，保证数据的持久性——为了提高性能，数据达到一page大小再写磁盘，故为因服务crash等原因导致丢失的未持久化的内存数据能恢复，写内存后写日志。这就是所谓的WAL（write-ahead）技术，保证写内存但没写磁盘的数据的逻辑持久性。

innodb是以页（16KB，而OS页为4KB）为单位来管理存储空间的，任何的增删改差操作最终都会操作完整的一个页，会将整个页加载到buffer pool中，然后对需要修改的记录进行修改，修改完毕不会立即刷新到磁盘，因为此时的刷新是一个随机io，而且仅仅修改了一条记录，刷新一个完整的数据页的话代价太大了。但是如果不立即刷新的话，数据此时还在内存中，如果此时发生系统崩溃最终数据会丢失的，因此权衡利弊，引入了redo log，也就是说，修改完后不立即刷新，而是记录一条日志，日志内容就是记录哪个页面，多少偏移量，什么数据发生了什么变更。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据redo log和binlog内容决定回滚数据还是提交数据。

事务的两阶段提交：

流程：从上图可看出，日志写过程是【写redo log、redo log记录事务prepare -①-> 写binlog -②-> 提交事务、edo log记录事务commit】。

目的：确保Server的bin log和InnoDB存储引擎的redo log记录的数据状态一致。如果只有一阶段直接提交，则不论redo log、bin log谁先写，两写之间服务器崩溃后两日志最后记录的a的值就不一样了（不一样会怎样？后者用于数据复制到其他节点，这样其他节点和本节点数据可能就不一致）；因此用两阶段提交，不论服务器在上面①、②哪位置崩溃，启动后通过redo log检测到事务是prepare状态（无commit记录），则会比较bin log是否写了该数据的记录，若有则prepare状态的redo log记录不删除否则删除。 

 

redo log：

缓存：redo log记录的是page的更改，其不会每次写都落盘，而是与更新操作的buffer pool类似有个内存redo log buffer用来暂存该事务内的redo日志（可见众纷繁技术本质不离其宗），达到一定量的redo log后批量落盘。那么这就存在类似于上面的内存buffer中redo log数据丢失的问题了，怎么解决？尽可能及时刷盘。bin log为何不这样？因为不会像redo log一样在一个事务内多次写。

redo log buffer 的大小：innodb_log_buffer_size (default 8M)

redo log的落盘时机：

1. 1. log buffer已用空间达到阈值：
   2. 后台线程定时刷盘：有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
   3. 事务提交时：在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，此时除了本事务的，可能还会刷入其它事务的日志。
   4. 正常关闭服务器时。
   5. 触发checkpoint规则。详情可参阅文末推荐阅读。

 

题外话，MySQL ACID的内部实现

原子性：undo log。在事务执行失败或手动rollback导致回滚时，通过undo log中的反操作将数据回滚到修改前的样子。另一方面，在服务器发生故障又启动后，判断redo log中prepare状态的事务的数据在bin log中是否有，若无则要回滚redo log以取消之前的写操作。

一致性：undo log，回滚功能。

隔离性：锁机制，包括表锁、行锁等悲观锁 和 MVCC乐观锁。

持久性：redo log，故障恢复使得丢失的内存数据可通过日志重做找回并持久化。

「一致性」可以理解为我们使用事务的「目的」，而「隔离性」「原子性」「持久性」均是为了保障「一致性」的手段。保证一致性需要由应用程序代码来保证，比如若事务执行过程中出现了异常则你就得回滚事务而不是强行提交事务来导致数据不一致。

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。

 

8.3.3 读问题——慢查询

慢查询定位

慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

慢查询优化

查看执行计划

explain select xxxx;  结果的数据格式：

字段含义：

=

==

extra 表示Server层需要对存储引擎层返回的数据做的操作，三者分别表示使用覆盖索引、使用where条件过滤、使用临时表。

 

8.4 InnoDB的锁（隔离性的实现）

提高并发的演进思路：普通锁（悲观锁，串行执行）->读写锁（悲观锁，读读并行，读写、写读、写写不并行）->MVCC（乐观锁，读读、读写、写读并行，写写不并行）。

大多数数据库同时支持悲观锁和乐观锁，且悲观锁用的最多，本节主要介绍悲观锁。本节内容主要包括 锁的类型，悲观锁中行锁的实现、表意向锁的作用、乐观锁MVCC、死锁等。

InnoDB存储引擎支持行锁、表锁，MyISAM则只支持表锁。可通过SQL  select * from information_schema.innodb_locks; 来查看MySQL的加锁情况。

 

8.4.1 锁的类型

包括 【共享锁、排它锁】、【行锁、表锁】、【意向共享锁、意向排它锁】、【读锁、写锁】、【记录锁、间隙锁、后键锁】、【悲观锁、乐观锁】等，它们是从不同角度分类的结果，就好比人类包括男人女人、好人坏人等。归纳如下：

悲观锁：

表锁：

类型（4）：表共享锁、表排它锁、表意向共享锁、表意向排它锁

特点：锁表，粒度大；开销小，加锁快；一张表只有一个加锁目标，故锁冲突概率大，并发性能低；不会死锁

行锁：

类型（2）：行共享锁、行排它锁

特点：锁行，粒度小；开销大，加锁慢；一张表有多个加锁目标，故锁冲突概率小，并发性能高；会死锁。

实现（4）：

单记录：记录锁（Record Lock）

范围：间隙锁（Gap Lock）、后键锁（Next-key Lock）、插入意向锁（Insert Intention Lock）

乐观锁：MVCC（读读、读写、写读并发，写写不并发）

注：

1 页锁：有些存储引擎还支持页锁（Page Lock），其粒度、开销、并发性能等介于表锁和行锁间，且也会发生死锁。

2 共享和排它锁/读写锁：共享锁（Share Lock，S）也称读锁（Read Lock），读时不允许其他写但允许其他读；排它锁（Exclusive Lock，X）也叫写锁（Write Lock），其和排它锁、共享锁互斥即写时不允许其他同时读写。

3 意向锁：表的意向共享锁（Intention Share Lock，IS）、表的意向排它锁（Intention Exclusive Lock，IX）、行的插入意向锁（Insert Intention Lock），作用不同，详见下文介绍。

4 悲观、乐观锁：都用于共享资源访问的并发控制，但悲观锁排他而乐观锁不排他，这里的排他是指事务写一个资源时是否允许其他事务读写、读时是否允许其他写。

※ 悲观锁，认为对一个资源的访问一定会发生竞争，故在事务A持有资源的锁时其他事务无法更新该资源，只能等A释放锁后才可更新。如上面的行锁、表锁等通常都是悲观锁。

※ 乐观锁（逻辑锁，实际上不加锁），倾向于认为资源访问不会发生竞争故各事务可同时去更新同一数据，然后当发生竞争时再去尝试补偿解决竞争问题，因此写写并发性能大大提高。通常是通过在表中增加一个版本号(version，通常是timestamp等随时间增加的值)来实现乐观锁，。知道即可，InnoDB中用得最多的是悲观锁。

流程：

写写并发：各事务从数据库取得的数据包含版本号，修改数据后尝试更新到表中，此时会比较该版本号是否与数据库中该数据的最新版本号一致，若是则允许更新，否则说明被其他事务更新了从而不允许更新；此时的解决方式通常是重试上述过程，此即所谓的自旋操作或自旋锁。比如很多编程语言的compareAndSet等原子操作即此，MySQL行锁中的插入意向锁也在一定程度上支持写写并行。

写读或读写并发：比如InnoDB中的MVCC（见后文）。

适用情形：读多写少用乐观锁、写多读少用悲观锁，因为乐观锁的性能优势来于资源访问不会冲突从而不用加锁的前提假设。

在读数据方面的区别：悲观锁（如行锁）是当前读，读的是最新数据因为实际上也只有一份数据；而乐观锁则是快照读，读的是某个历史版本数据。

5 加锁SQL示例：

# 悲观锁，加锁，当前读，读最新数据
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE; #共享读锁
SELECT * FROM t WHERE id=1 FOR UPDATE/INSERT/DELETE; #排它写锁
# 乐观锁，不加锁，快照读，读历史版本数据
SELECT * FROM t WHERE id=1; #MVCC

 

8.4.2 悲观锁之行锁的实现

含义：行锁，顾名思义就是一个事务锁住一个连续区间内的行，使得其他事务不能【修改这些行、插入新行、删除行】。锁住的是范围而非仅是范围内存在的数据，区间内可以只有一行此时就是锁住单行数据。

锁什么：行锁是作用在索引之上的，当SQL命中了索引则锁住的就是命中条件内的索引节点（这种就是行锁），如果没有命中索引则锁的是整个索引树（表锁）。更具体而言为锁索引叶节点的范围，故若不走索引则行锁不生效而是用表锁。对于InnoDB聚簇索引和非聚簇索引，前者叶节点有数据记录。

实现：核心在于如何确定锁住的范围，直觉上理解，找出包含where条件给定的范围的最小范围即可，后者范围的端点值取于索引叶节点的key值。举几个例子（可参阅这篇文章）：

数据准备：为不失一般性，假设字段 a 上建了索引且已插入了值分别为 0、5、10、15、20 的五条数据

实际情形：

 select * from t where a = 5 for update; ，若a不允许重复则锁住id为5的行数据、若值允许重则须锁住 (0, 10) 范围才能覆盖where a=5的结果。

 select * from t where a = 8 for update; ，锁住 (5, 10) 范围，其他事务就不能增删改id值在该范围内的数据。

 select * from t where a>5 and a<8 for update; 锁住 (5, 10)， select * from t where a>21 for update; 锁住 (20, +∞) 。

 select * from t where a ≥5 and a<8 for update; 锁住 [5, 10)、 select * from t where a>5 and a≤8 for update; 锁住 (5, 10)， select * from t where a>5 and a≤10 for update; 锁住 (5, 10]。

行锁的实现总结：从上述例子可见，加锁的对象要么是 record 要么是 gap，加锁范围内的数据不能被修改。实际上可从这些例子归纳出行锁的实现（对应取名而已）。

1 Record Lock（记录锁）：单行的锁，对一行数据加锁。等值查询用到

2 Gap Lock（间隙锁）：范围的锁，对一个左右开区间加锁。等值查询、范围查询用到。

3 Next-key Lock（后键锁）：范围的锁，对一个左开右闭区间加锁，范围查询用到。是上面两种的结合。三种的前者依次可看成是后者的特例。

4 Insert Intention Lock（插入意向锁）：范围的锁。特殊的Gap Lock，专为减少 insert 操作的锁冲突从而提高插入效率而设计，相当于允许了一定条件下的写写并行。

insert操作也会根据索引列值对一个左右开区间范围加锁，但若多个事务的insert对该同一范围的不同位置同时插入数据，则后来者会因取不到锁而阻塞，显然效率低。故引入插入意向锁替代间隙锁，其特点是虽加锁范围一样但插入位置不一样的多个事务不会互相阻塞。可见其作用是让后来事务知道该范围内的哪些位置将会被插入数据。

拓展：上述各种行锁实现最终是怎么做到锁行或范围的？列的索引上附个数据结构，事务通过它记下锁范围、是共享还是排他等信息即可。

行锁几种实现间的兼容互斥性（行头表示已持有的、列头表示要申请的）：

（太多了，记不住，看看就好，也没啥用）

注：

1 InnoDB 默认的事务隔离级别虽是REPEATABLE-READ，但它也解决了幻读问题，就是借助这里的 Gap Lock 和 Next-key Lock 来加范围锁实现的。相反，若MySQL的隔离级别设置的不是可重复度（例如读已提交）则不存在gap、nex-key lock！！

2 InnoDB是通过索引查找来确定加锁范围的，故若一个操作无法走索引则只能退化为全表扫描，此时就只能用表锁，从而锁住的范围更大了。故应设计合适的索引以减少锁冲突。

2 更多例子加深理解：

例1 事务中一个事务写时不允许别事务读或写，这里要求两事务访问的是同一个资源，若不是同一个资源则两事务肯定就互不影响了。该资源就是指这里的加锁对象，具体而言是record或gap，它们在索引结构上。可见，对于如下图，若一个事务为 select id from t where name='Gates' lock in share mode，另一事务为 update t set Company='ST' where id=5，它们互不影响，因为前者符合覆盖索引从而不用访问聚簇索引、后者访问聚簇索引，两者访问的是不同索引树故加锁的gap或record无交集。

例2 包含主键id、普通索引列name 的 table (id, name, balance) ，两事务执行如下操作 insert 会死锁（详见此文）。

解析：name可重，故A update 加锁范围(e, +∞)、B update 加锁范围(-∞, w)，A insert加锁范围 (e, w)与B update的冲突、B insert的加锁范围(w, +∞)与A update的冲突，故互相等待。

 

8.4.3 悲观锁之表的意向锁

（更多详情可参阅这篇文章）

1 意向锁包括表的意向共享锁（intention shared lock, IS）、表的意向排它锁（intention exclusive lock, IX）。

行的插入意向锁不属于这里的意向锁，其是行锁且锁住的是一个范围，而表的意向锁锁的是表相当于一个标记。

2 表的表锁和行锁同时存在，表的意向锁用于解决这两种锁共存时的某种低效率问题。以表的意向排它锁为例：

问题：当事务要申请表的排他锁时，需要检查该表上无排它锁、表内各行无排它锁，后者显然很耗时。

解决：为解决该问题引入了意向排它锁，即当一个事务申请到行排他锁时存储引擎默认会给行所在表加上意向排它锁，这样其他事务申请表的排它锁时通过检查无排它锁、无意向排它锁即可，若有任一则阻塞等待。当然，其他事务可以正常申请到该表另一行的排它锁，此时也会把该表加上意向排它锁。

表的意向共享锁同理。

3 作用：由上可见，表的意向锁虽然是锁，但本质上可理解为仅是个标记，表的意向排他（共享）锁标记用于加快判断表内是否有行、页被加了排他（共享）锁。

4 表的意向共享排他锁、表的共享锁排它锁、表内行共享锁排它锁 三者内部间及彼此间的兼容互斥情况（互斥时是写时不允许其他读写、读时不允许其他写但允许读）：

表共享排它锁间互斥、行共享排它锁间互斥（2*2-1 互斥）。

表意向锁彼此间兼容而不互斥（2*2两两兼容），即表意向锁可由行锁引起多次重复加锁。这点与表的共享锁、排它锁不同。比如不同事务可给同一张表同时加意向共享锁或意向排它锁或两者兼具，可理解为仅是个标记。

表意向锁与表内行锁兼容而不互斥（2*2两两兼容），即表意向锁的存在不会影响其他事务对其他行锁的获取（2*2两两兼容）。但表意向锁是因加表内行锁而加上的。

表意向锁与表共享锁或排它锁互斥（2*2-1 互斥），即表意向锁的存在会排斥其他事务对表共享锁排它锁的获取。

 

8.4.4 悲观锁之整体加锁过程

要加锁是因为多个事务更新的数据可能有交集此时就涉及到更新同一个数据的问题，故须进行并发访问的控制，靠的就是加锁。

前面介绍完了 锁的类型、行锁的实现、意向锁的作用 后，这里我们就可以描述下事务更新数据时加锁的整体流程了（即表锁、表意向锁、行锁内部间及彼此间的协调过程）：

行锁1：事务1想更新某行数据时，首先申请该行相关的排它锁（可能是一行或一个范围，可看为资源），若申到则也在该行所在表加上表意向排它锁、否则阻塞等待；

行锁2：在事务1申到了的情形下事务2也申请更新某行数据，此时与上同样找到要加锁的资源，若加锁目标跟事务1的有交集则阻塞等待，否则获得锁并与事务1并行而无需等待；

若该“更新”是插入操作且事务1、2插入位置不一样，则即使有交集也不用阻塞而是可并行。

表锁：在事务1申到了的情形下事务3申请该表的排它锁时，会检查该表是否被加了排它锁、意向排它锁，任一有则阻塞否则申请成功。

 

8.4.5 乐观锁之MVCC

悲观锁和乐观锁都用于共享资源的并发访问控制，其区别见前面“锁的类型”一节，很多数据库会都实现这两类锁但悲观锁用得最多。

悲观锁通过给数据加锁进行共享资源的并发访问控制，实现了读读并行而读写、写读、写写不并行；而乐观锁是逻辑锁，内部实际上不加锁而是通过引入版本号概念来存数据的不同版本，从而进一步实现了读写、写读甚至是写写的并行（不一定全支持，不同数据库实现的能力可能不同）。可见，乐观锁的主要目的是提高共享资源的并发访问性能。

 

MVCC（Multiversion Concurrency Control，多版本并发控制）是InnoDB实现的一种乐观锁，支持了读读、读写、写读并行但不支持写写并行，但也达到了事务中的读已提交、可重复读两种隔离级别。实现上很巧妙又简单。

注意不要与前面说的一大堆的加锁内容混起来，这里只是逻辑上的锁，内部实际上不涉及加锁。

用途：MySQL的“快照读”用的是MVCC，并发性能非常高。“快照读”和“当前读”的区别见后文。

关键词：隐藏字段、版本号（事务id）、上一版本位置（undo日志）、版本链、快照读&当前读、Read View

核心思想：undo log + read view 实现。

名为“锁”但内部没有任何加锁，而是每行数据用额外两个隐藏字段保存版本号和上一版本的位置从而形成版本链，允许一数据被读写、写读事务并行访问但通过规定的版本号比较规则来确定该行哪些版本对当前事务可见（不支持写写并行故写写仍要加排它锁）。

内部实现：借助非常巧妙的多版本保存方案和版本比较规则来实现

1 多版本的保存：

每行数据有【db_trx_id、db_roll_ptr】两隐藏列用于记录该次更新的更新者、该次更新对应的undo log位置，用undo log记录一个事务内的更新（增删改）操作的反操作日志。这样就可通过该指针对应的undo log得到上一版本数据，相当于每行数据包含了版本号和上版本的位置，从而逻辑上形成了该行数据的各版本版本链。

版本号/更新者的设计很关键：版本号用该次更新所在的事务的id表示，事务id是InnoDB存储引擎在事务开始时为事务生成的单调递增的数值，可表示事务开始的先后顺序，故相当于行数据的版本号。显然版本号不连续，因为纯读的事务id不会作为版本号。

反操作：inset、update的SQL操作对应的undo log是delete、update；delete SQL操作对应的undo log是update，标记该行被删除。undo log包含事务id及该事务所产生的反操作，效果上相当于保存了各老版本的数据。

2 读、写并行规则：完全无锁——数据被一事务读时另一事务可写新版本，后者不论有否提交都可写新版本；数据被一事务写时另一事务可读该数据，只不过读到的是历史版本的（当然，数据被一事务写时不可被另一事务写故仍用行锁的排他写锁）

3 多版本的使用——版本对当前事务可见性的判断：通过版本链递归地根据数据的“版本号”比较判断可见性，直到找到第一个可见版本或完全没找到。

（可参阅这篇文章）

当前读和快照读（也分别称为 一致性锁定读  Consistent Locking Read、 一致性非锁定读  Consistent Non-locking Read）：悲观锁（如行锁）是当前读，读的是最新数据因为实际上也只有一份数据；而乐观锁如MVCC是快照读，读的是某个历史版本数据。不加锁的读是当前读，SQL示例：

# 悲观锁，加锁，当前读读最新数据
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE; #共享读锁
SELECT * FROM t WHERE id=1 FOR UPDATE; #排它写锁
# 乐观锁，不加锁，快照读读历史版本数据
SELECT * FROM t WHERE id=1; #MVCC

Read View（读视图，即snapshot快照）：事务执行快照读（即不加锁的select时，注意不是开启事务时）时产生的读视图，相当于某时刻的一个快照。对于快照读的SQL才会用快照，select时会创建一个Read View，里面包含此时未提交的事务（即begin了但未commit的事务）id列表等信息，MVCC规定了版本号（事务id）比较规则来确定哪些版本对当前事务可见。

Read View 结构：

creator_trx_id：当前事务的id

trx_ids：Read View创建时系统中的未提交事务id列表，不包含当前事务自身的。相当于未提交的版本列表。

up_limit_id：该id列表中的最小值

low_limit_id：Read View创建时目前出现过的最大的事务id+1，即下一个将被分配的事务id。"目前出现过的最大的事务id"不一定是当前事务id，比如一个事务内多次select时创建的多个Read View且两次select间有其他事务写的情形。

比较规则：访问一个版本时，根据其版本号x和Read View确定该版本对当前事务是否可见，规则定义如下：

若 x==creator_trx_id，可见。说明当前是写事务，且在事务内写后又去读，尚未提交。

若 x<up_limit_id，可见。说明该版本是在当前事务创建该Read View前产生并提交的。

若 x≥low_limit_id，不可见。说明该版本是在当前事务创建该Read View后产生的、不一定提交。比如当前事务select后另一事务去修改了值（提交与否均可）。

若 x∈[up_limit_id, low_limit_id)，则trx_ids中若不存在x则可见，因为说明是当前事务创建该Read View前产生并提交的，否则不可见，因为说明该版本是创建该Read View前产生的且未提交 或 创建后产生的、不一定提交。

可见，在上述规则下，一数据被读写事务同时访问时虽无锁但只有已提交的数据版本才会被读到（例外是读写是同一事务，不过没影响）。根据读事务内多次select是使用第一次的Read View还是每次都创建，可以得到读已提交、可重复度的事务隔离级别。

故，MVCC的读已提交、可重复读都只允许读已提交的数据，但前者每次select都创建Read View而后者不会。只创建一次Read View 也使得MVCC解决了幻读问题，故MVCC的可重复读隔离级别也解决了幻读问题。 

悲观锁实现了事务隔离性的四种隔离级别，但MVCC只实现读已提交、可重复读隔离级别，即只能在这两种隔离级别下工作。

 

 

8.4.6 MySQL死锁预防/排查/解决

无法千篇一律，一种可能的方案大概如下：

排查：

1 查看锁日志， show engine innodb status; ，会列出各事务执行的SQL、持有的锁、等待获取的锁等情况，结果示例：

2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account  values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

2 找出死锁SQL并分析加锁情况

3 模拟死锁场景

解决：资源剥夺、撤销进程（撤销代价小的事务、杀死比较不重要的请求等）

一个真实案例分析可参阅此文章，一个模拟示例可参阅此文章。

 

死锁时MySQL会回滚其中的一个事务并以异常日志记录下来。如何尽可能避免死锁？

 

让查询尽可能走索引来，如果不走索引则会退化为表锁，锁住的范围更大，这样更可能锁冲突。

合理设计索引，尽量缩小锁的范围。

尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。

尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。

降低隔离级别。如果业务允许尽可能使用低级别的事务隔离机制，可以避免很多gap锁造成的死锁。

如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。

 

8.5 事务

见 事务ACID及事务隔离-MarchOn。

InnoDB存储引擎中的事务隔离是通过上述的锁机制来实现的。加的“锁”通常是悲观锁的共享锁、排它锁等，但也可以是乐观锁如MVCC，前者实现了四种隔离级别、后者实现了读已提交、可重复读两种隔离级别。

MySQL的幻读问题由范围锁+MVCC共同解决，分别解决当前读、快照读的幻读问题。

 

8.6 高性能/高可用

（很容易想到的八股内容，主要整理自文末推荐文章）

8.6.1 读写分离（主从复制）架构

整体架构

读写分离的基本实现是:

• 数据库服务器搭建主从集群，一主一从、一主多从都可。
• 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。
• 数据库主机负责读写操作，从机只负责读操作。
• 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。

主从读写分离的架构非常适合读多写少的场景。

 

业务层读写分离的实现

将读写操作区分开来，然后访问不同的数据库服务器，一般有两种方式：程序代码封装和中间件封装。

程序代码封装：指在代码中抽象一个数据访问层（所以有的文章也称这种方式为 "中间层封装" ） ，实现读写操作分离和数据库服务器连接的管理。例如基于 Hibernate 简单封装，开源的实现方案有淘宝的 TDDL (Taobao Distributed Data Layer）等。

中间件封装：指是独立一套系统出来实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议，业务服务器无须自己进行读写分离。对于业务服务器来说，访问中间件和访问数据库没有区别，事实上在业务服务器看来，中间件就是一个数据库服务器。例如基于 MySQL Proxy 开发的奇虎 360 的 Atlas 、阿 里 的Cobar、基于 Cobar 开发的 Mycat 等。

、

 

MySQL主从复制的过程

 

主库专门的线程负责将bin log发到从库，从库专门的线程接收bin log到replay log文件并用专门的线程读取replay log执行。

• master数据写入，更新binlog
• master创建一个dump线程向slave推送binlog
• slave连接到master的时候，会创建一个IO线程接收binlog，并记录到relay log中继日志中
• slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
• slave记录自己的binglog

主从复制的优点和存在的问题

优点：

数据冗余提高了数据可靠性；

故障时从节点顶上，提高了故障应对能力；

读写分离提高了并发处理能力。

问题：

主从延迟、网络故障等导致数据不一致；

主节点接收写任务外还要往多节点复制，网络等开销增大；

 

主从复制延迟

原因：主服务器需要连接N个从服务器同步数据，当某个从服务器的SQL执行过长或要锁表等会导致主服务器上要同步的bin log大量积压，未同步到从服务器，这就是主从延迟。

解决：

1 把读操作也发到主服务器上

2 从服务器先读读不到再读主服务器，即两次读取。

3 关键业务部读写分离、非关键业务读写分离。前者如用户注册、登录等业务，后者如用户的介绍、爰好、等级等业务。

4 同时往缓存写，然后可从缓存读最新数据。

 

8.6.2 分库分表分区

（详情可参阅这篇文章）

为什么要分库？单库容量、连接数等问题。为什么分表？表数据太多导致查询和写入响应慢。

分库分表要解决的是现存海量数据访问的性能瓶颈，是对持续激增数据量所做出的预见性的架构改变。其核心就是采用某些策略对数据进行分片（Sharding）使得数据相对均匀分布到不同库、表中。

含义

分库：

垂直分库：以表为单位，按业务归属的不同（例如用户、商品、订单等）将一个库中的不同的表拆分到不同的库中。微服务拆分通常伴随着这种分库设计，如用户中心、课程中心、管理端、教学中心有各自数据库。

水平分库：以行为单位，按照一定策略（例如range、hash等）将一个库中的一张表数据拆分到多个库中的同名、同结构表中。

分表：

垂直分表：以列为单位，按照字段的活跃性，将表中字段拆到不同的表中。例如将包含用户名密码昵称等信息的表拆成只包含用户名密码的主表 和 包含其他信息的扩展表。数据库以行为单位加载数据到内存，故这样拆分可减少高频主表数据的加载量、减少磁盘IO。

水平分表：以行为单位，按照一定策略（例如range、hash等）将一张表数据拆分到同一库中的多个异名、同结构表中。

水平分区：

是什么：MySQL5.1 起自身支持水平“分区”，这是MySQL支持的功能业务代码无需改动。本质上就是把上面业务层的水平分表操作在数据库层给实现了，其支持的分区策略（HASH分区/KEY分区、RANGE分区、LIST分区）跟水平分别的路由策略类似。各分区内的数据分别在各自的文件中存储。详情可参阅这篇文章。

示例：

CREATE TABLE `m_test_db`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partition_key` INT NOT NULL,
  `amt` DECIMAL(5) NULL,
  PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;


INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');


explain partitions select * from `m_test_db`.`Order`  where partition_key='201903';# 走分区，加快查询
explain partitions select * from `m_test_db`.`Order`  where amt='800'; #走不了分区，查询加快不了，甚至因须多分区文件分别查询故更慢

性能：与分库分别类似，分区并不是总能提高读写性能：只有where例有分区列时才可能提高定位数据的效率，否则需要遍历各分区下的文件此时性能反而不如只有一个文件的不分区情形。

先分库再分表再分区，同类内先垂直再水平。整体上垂直分库、垂直分表、水平分库、水平分表。

分库分表分区三者不冲突，可以搭配使用，比如先分库、库内的表分表、表内分区，本质上就是把数据管理的批粒度不断细化的过程。三者粒度从粗到细，比如分库虽提升了容量和系统连接数支持但没解决单表数据量过大的问题，故通常需分表配合。

先垂直再水平：实践上通常在设计阶段就先垂直分好库或表、当数据量达到一定程度后进行水平分表；还满足不了就水平分区。

分库分别架构模式

Client模式：在应用内部进行分库分别逻辑控制（通常是引入Jar包），应用负责将SQL应用到多库或多表然后进行结果汇总。

Proxy模式：由代理负责进行分库分别逻辑控制及SQL结果汇总，应用连接代理，代理对应用而言就是数据库，应用感知不到分库分表的存在。

、

 

选择：性能——Client性能相对好点而Proxy执行链路较长；维护——Client引jar即可而Proxy需要搭建单独的服务维护成本高；升级、治理和监控——Proxy更容易。

 

常用的分库分表工具

sharding-jdbc
Mycat

分库分表方案应该考虑的因素

方案的持续性（后期容易扩容等）、数据分布尽可能均匀、扩容时数据迁移尽可能少等。

水平分表的路由方式

即确定一条数据存到哪张表的路由方式。大概有以下几种：

1 范围路由：选取有序的数据列 （例如，整形、时间戳等） 作为路由的条件，不同分段分散到不同的数据库表中。

难点：范围大小的确定，太小会导致表太多维护复杂、太大会有单表性能问题。一般在千万级别。

优缺点：优点是扩容方便增表方便原有数据不用变、单表数据量可控；缺点是数据分布不均匀例如虽范围大小一样但有的表数据很多有的少。

2 哈希路由：选取某个列 （或者某几个列组合也可以） 的值进行 Hash 运算，然后根据 Hash 结果分散到不同的数据库表中。如取模、一致性哈希算法等

难点：初始表数量的确定，太多会导致表太多维护复杂、太少会有单表性能问题。一般在千万级别。

优缺点：优点是数据分布均匀；缺点是扩容麻烦原有数据需要移动、单表数据量不可控。可用一致性哈希算法减少移动的数据量。

3 两者结合——先范围再哈希：先根据范围让不同分段落到不同库、库内根据哈希路由落到不同表。为何不先哈希再范围？保持数据在一个库内的临近性。

4 配置路由：用一张独立的关联表来记录哪条数据对应哪个表，如(orderId, tableId)。

优缺点：优点是扩容方便，只需先迁移数据然后修改路由信息即可；缺点是多一次关联表查询会影响整体性能，关联表自身也存在单表性能问题。

 

什么时候分表

没有统一标准，跟业务访问量、机器硬件资源等有关，阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB才推荐进行分库分表，相当于业务实践的经验。

从内部实现上看，达到大数据量时数据文件很大写入耗时、索引也很大。

 

分库分表的问题

从分库角度看：

1 无法使用数据库自身的事务，只能用分布式事务，后者编码上更复杂且性能比较低。

2 无法使用数据库自身的JOIN，只能业务层自己查数据后再去查，因此编码更复杂性能也更低。解决：

冗余：当前表适当冗余字段以减少Join它表。

数据异构：通过binlog同步等方式，把需要跨库join的数据异构到ES等存储结构中，通过ES进行查询。

3 count、order by、group by 、聚合函数问题，只能在各库分别执行再由业务层汇总各库结果后进行。

从分表角度看（分库也有这些问题）：

1 容量规划，扩容问题，扩容时的数据迁移等。

2 多库多表集群和数据的高效治理，包括自动化建表、监控、告警、问题排查等。

3  ID问题，无法再使用自增主键。通过全局唯一ID生成器（称为发号器）解决，方案有：

使用UUID，优点是随机、简单，缺点是值不连续可能导致索引页分裂较多。

Twitter 开源的 snowflake 算法。

Redis incryBy。

MySQL自增主键，但自增带步长。例如有三张分表则步长设为3且三张表初始id分别为1、2、3，接下来是4、5、6... 

 

8.6.3 不停机扩容

扩容方案

通常有翻倍扩容、一致性哈希扩容等。

采用hash路由时翻倍扩容使得数据所在表或库位置要么不变要么后移原总数据数量的步长，与HashMap扩容的rehash类似

在线扩容过程

准备好表结构一样的新库，新库包含更多节点，然后：

新旧库在线双写，并使用数据迁移程序等将旧库老数据同步到新库。

同步完后，新旧库仍在线双写，但把读操作切换到新库。

一段时间后，旧库不再写入，确认无请求后下线旧库。

、、

8.6.4 其他

如何高效分页

问题： SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M  的问题：执行时会取出N+M行、丢弃前N行、返回 N ~ N+M 行的记录，如果N值非常大，效率极差（表记录1500w，N=10000000,M=30 需要9秒）。

解决：在where条件列可走索引的前提下，改为  SELECT * FROM ttl_product_info WHERE id > N LIMIT M ，（表记录1500w，N=10000000,M=30，需要0.9毫秒）；实际场景中where条件可能不都是索引列，故另一种更为通用可行的方案是使用嵌套SQL，内层SQL查出id列表、外层根据id列表查数据，示例：

SELECT a.* FROM 表 1 a， (select id from 表 1 where 条件 LIMIT 100000，20 ) b where a.id=b.id

前提：

id列有索引。（其实没有也可？）
N 的值是上一次查询的记录的最后一条id，（需要前端保存一下，不能直接用传统的方法获得）
不支持跨页查询，只能按照第1，2，3，4页这样查询逐页查询。

 

delete、truncate、drop 的区别

 

truncate 原理：先drop表再创建表。

百万级别以上行的表如何删除部分数据

直接一行行删删过程需要维护索引故可能很慢。方法：先删索引，再删无用数据，重建索引。如果是删除全部数据，那truncate该表。

百万千万级大表如何添加字段？

大数据量表中添加字段可能造出长时间锁表。通常的做法有：

1 借助中间表（中间件pt-online-schema-change内部就是这个原理）：创建一个临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，删除旧表，新表命名为旧表的名称，这种方式可能回丢掉一些数据。

2 先在从库添加字段，然后主从切换，再在所有从库添加字段。

MySQL 数据库 cpu 飙升要怎么处理

排查过程：

（1）使用 top 命令观察，确定是 mysqld 导致还是其他原因。

（2）如果是 mysqld 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的 sql 在运行。

（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些session (同时观察 cpu 使用率是否下降)，

（2）进行相应的调整 (比如说加索引、改 sql、改内存参数)

（3）重新跑这些 SQL。

 

SQL建议

表连接时小表驱动大表。因为表连接是耗时操作，连接次数是外表的行数，故小表驱动大表可减少连接次数。Left join 内部执行原理：

mysql采用嵌套循环的方式处理left join。
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)

其中P1是on过滤条件，缺失则认为是TRUE，P2是where过滤条件，缺失也认为是TRUE，该语句的执行逻辑可以描述为：
FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行，找到满足join条件的行
IF P2(lt, rt) {//满足 where 过滤条件
t:=lt||rt;//合并行，输出该行
}
b=TRUE;// lt在RT中有对应的行
}
IF (!b) { // 遍历完RT，发现lt在RT中没有有对应的行，则尝试用null补一行
IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
t:=lt||NULL; // 输出lt和null补上的行
}
}
}

 

in、exists、join 的选择？

用in还是exists？A、B两表通过id关联，查两表id都存在的数据：当B表数据较少时用in比exists好  SELECT * FROM A WHERE ID IN (SELECT ID FROM B)  ，B数据远大于A时用exists比in好 SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.ID = A.ID) 。当然，用left join好像更好。

用in还是join？同上，B作为条件时，若B数据较少则in比较合适、否则join比较合适。

实例：

背景：MFI税务本年度上报 本税年、上税年 未实现盈亏，指数期权转出时要扣除对应的上税年未实现盈亏后剩余部分才上报。实现时把每次转出后的结果都作为一份快照保存，故一笔盈亏可能存在多条记录，体现在它们的transaction_id相同但id、transferout_date（可理解为版本号）不同（quantity可能不同）。上报时，每个transaction_id选transferout_date最大的记录上报。

需求：也就是要查询 (transaction_id, max(transferout_date) ) 的记录，假设表名为unPL，查询该最大记录的SQL为 subQuery= select transaction_id, max(transferout_date) from unPL ;

实现：

in 方案：select * from unPl t1where (transaction_id, transferout_date) in (subQuery) t2  

join 方案： select * from unPl t1 inner join (subQuery) t2 on t1.transaction_id=t2.transaction_id and t1.transferout_date = t2.transferout_date

 

 8.7 数据如何存储

主键索引表（index organized table）

表是根据主键顺序存放的，称为索引组织表（index organized table）。每张表都有主键，要么是用户定义的、要么是用户无定义时内部自动生成的。

_rowid 隐藏列

（详情可参阅这篇文章）

都说MySQL InnoDB表中不存在主键或唯一非空索引时会创建隐藏列_rowid，该说法实际上不太准确。实际上：

1 若表结构存在 只包含一列、且类型为整型的【主键 或 唯一非空】索引，则_rowid会指向该索引列。优先找主键，主键不存在则找唯一非空的索引列，若存在多个则找按索引定义顺序的第一个索引列。

例如对于如下表结构，_rowid将指向no列、若主键索引未注释掉则是id列。

CREATE TABLE `table8_2` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `name` varchar(32),
  //PRIMARY(id),
  UNIQUE KEY(id, no),
  UNIQUE KEY(no),
  UNIQUE KEY(id)
) ENGINE=InnoDB;

2 否则会创建自增的_rowid作为隐含主键。

InnoDB中维护了一个无符号整型全局变量 dictsys.row_id 用于各表自增主键的取值，该变量6个字节，故最大值为2^48-1 = 281 474 976 710 656（≈280万亿），达到最大时又从0开始，故若数据很大可能覆盖（当然现实中几乎不会发生）。

该自动创建的自增主键是被一个schema的各表共用的；另外，在插入失败 或者 事务回滚时 或者 删除数据后用过的id并不会回收；此文，自动自增主键在插入主键冲突时会覆盖该行数据。

即该隐藏列的值分别依次来自 主键索引列（须为整型）、第一个定义的唯一非空索引列（须为整型）、6B大小的整型变量。

 

自增主键

有自动创建自增主键和自定义自增主键两种。区别：可表示的数据范围、范围用完及主键冲突时的处理方式不同

自动创建的自增主键 _rowid：大小为6B；达到最大值时下次从0起；插入主键冲突时覆盖该行而不报错。

满后从0起、冲突直接覆盖，这些很容易理解：因为使用者并没有设置主键，要做到对用户无感，你给人家报主键冲突明显不合适。

自定义自增主键：大小为int（4B）或bigint（8B）两种，通常用后者。虽大小是这两种，但可指定id值保存的位数，保存时会按位数截断，如： `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT 表示id值最多保存20位；满后下次仍取最大值；插入主键冲突时报错： Duplicate entry '4294' for key 'increment_id_test.PRIMARY' 

自定义自增主键的内部实现：最大的区别就在于重启时auto_increment值的确定。

MySQL5.7及之前，自增值auto_increment保存在内存且每次重启时初始化找  auto_increment=所在表已有记录的max(id)+1  。显然，若删掉最后一条记录后重启，auto_increment值会回退。

MySQL5.7起保存在redo log，重启时从redo log恢复auto_increment值。

自定义自增主键是 递增、不保证连续的。

不连续是指：新增记录时记录的id值与已有记录的不连续（删掉已有记录使得各记录的id看上去不连续，显然不是“不连续”）。

不连续的原因有：自增值更新后不允许回滚、自增值的申请上可能存在批量申请但未全用完。对应例子：

1 自增值不支持回滚（申请的自增值用上了但数据记录最后没了而自增值却没回滚）：自增值更新的内部机制——先设置记录的id=auto_increment、更新auto_increment值，然后记录插入db。当插入失败或插入成功并回滚时，auto_increment更新但不会回滚，故不连续了

 新增一条记录失败（例如其他列的唯一键冲突）、然后再新增一条记录成功。

新增一条记录成功后回滚、然后再新增一条记录成功。

　　2 自增值批量申请但没用完： insert into xxx select xxx from xxx  这类语句，因不确定插入多少条故内部会按申请 1、2、4、8、...这样指数递增的规律多次申请id个数，显然一次申请得到的id个数可能没被全用上，从而造成不连续。

为何不支持回滚auto_increment？性能考虑——若允许回滚则auto_increment会有数据一致性问题，为解决该问题需要更多的措施，从而性能会降低。

具体而言：若支持回滚则有可能回滚后的auto_increment值比已有记录的最大id小，则之后插入新记录时得到的id可能在表中已存在了，为了解决该问题要么判重要么加锁，显然性能会大大降低，这对于“自增”这种基础、广泛使用的特性来说是不可接受的。

 

 

InnoDB存储结构

逻辑存储结构

（可参阅这篇文章）

与很多存储系统一样，MySQL数据存储结构也是一层层的“分区”，包括： tablespace（表空间）、segment（段）、extent/block（区）、page（页）、row（行）。

前三是逻辑上的划分、page是最终的物理存储。

OS的页大小是4KB，，默认下：InnoDB的page是16KB即由4个OS页组成、extent由连续的64个page组成即一个extent大小为1MB、若干个extent组成segment、若干个segment组成tablespace。

tablespace分为共享表空间、独立表空间，区别在于一张表的数据、索引、回滚段、双写缓冲区等内容是分别单独存在一个表空间还是都存在同一个表空间。默认情况下用共享表空间。

tablespace的基本单位是page，由于用32位无符号整数计数，故InnoDB最大支持2^32个tablespace、共享表空间最大支持2^32个表、一个表空间最大支持2^32个page、一个表空间最大大小为2^32 * 16KB=64TB

数据按行存储，每 page 最多允许存放 16KB/2-200=7992 行。why？？

 

 行记录格式

（详情参阅 MySQL一行数据是怎样存储-公众号低并发编程、这篇文章）

通过命令  show variables like 'datadir'  查看MySQL数据存储位置：

数据库配置（db.opt）、表结构（.frm）、表数据和索引（.ibd）、触发器（.TRN  .TRG）等都是分别以文件形式存储在数据目录下的，示例如下：

-rw-r----- 1 mysql mysql  98304 Feb 26 06:56 course.ibd
-rw-r----- 1 mysql mysql  13878 Jan 25  2021 course.frm
-rw-r----- 1 mysql mysql   1634 Aug 10  2020 experiment.TRG
-rw-r----- 1 mysql mysql   1608 Aug 10  2020 experiment_step.TRG
-rw-r----- 1 mysql mysql     42 Aug 10  2020 updateExpNumInCourseTableAfterDeleteExp.TRN
-rw-r----- 1 mysql mysql     42 Aug 10  2020 updateExpNumInCourseTableAfterInsertExp.TRN
-rw-r----- 1 mysql mysql     42 Aug 10  2020 updateExpNumInCourseTableAfterUpdateExp.TRN
-rw-r----- 1 mysql mysql     47 Aug 10  2020 updateStepNumInExperimentTableAfterDeleteStep.TRN
-rw-r----- 1 mysql mysql     47 Aug 10  2020 updateStepNumInExperimentTableAfterInsertStep.TRN
-rw-r----- 1 mysql mysql     47 Aug 10  2020 updateStepNumInExperimentTableAfterUpdateStep.TRN
-rw-r----- 1 mysql mysql 114688 May 18  2020 experiment.ibd
-rw-r----- 1 mysql mysql 114688 May 18  2020 experiment_step.ibd
-rw-r----- 1 mysql mysql  30142 May 18  2020 experiment.frm
-rw-r----- 1 mysql mysql   8934 May 18  2020 experiment_step.frm
-rw-r----- 1 mysql mysql     61 Mar 12  2020 db.opt

InnoDB 行记录格式：REDUNDANT, COMPACT, DYNAMIC, COMPRESSED。

 

10 数据库领域的一些优化技术

向量化查询引擎优化

火山模型

 

11 参考资料

MySQL 66问——“面渣逆袭”：推荐

http://blog.csdn.net/kennyrose/article/details/7532032：数据库索引原理

https://tonydong.blog.csdn.net/article/details/103324323-MySQL锁机制：MySQL 锁机制

https://lotabout.me/2020/Book-Notes-InnoDB-Engine-2nd-edition：《MySQL技术内幕：InnoDB存储引擎（第2版）》笔记大纲，推荐。当然，更推荐读原书。