一、创建

1. 磁盘

1.1 页、扇区、寻道、寻址、硬盘性能

1、页和扇区大小如图2.20

2、磁盘内部结构如图2.53

磁头、扇区、磁道

硬盘的容量：存储容量=磁头数×磁道（柱面）数×每道扇区数×每扇区字节数

3、影响硬盘性能的因素

磁盘完成一个I/O请求所花费的时间，它由寻道时间、旋转延迟和数据传输时间三部分构成。

• 寻道时间

Tseek 即将读写磁头移动至正确的磁道上所需要的时间。avg在3-15ms

• 旋转延迟（寻址）

Trotation 即盘片旋转将请求数据所在的扇区移动到读写磁盘下方所需要的时间。

旋转延迟取决于磁盘转速，通常用磁盘旋转一周所需时间的1/2表示。比如：7200rpm的磁盘平均旋转延迟大约为60*1000/7200/2 = 4.17ms

• 数据传输时间

Transfer即完成传输所请求的数据所需要的时间，它取决于数据传输率，其值等于数据大小除以数据传输率。

SATA II可达到300MB/s的接口数据传输率，数据传输时间通常远小于前两部分消耗时间，可忽略。

4、衡量性能的指标

• 机械硬盘的连续读写性能很好，但随机读写性能很差

这主要是因为磁头移动到正确的磁道上需要时间，随机读写时，磁头需要不停的移动，时间都浪费在了磁头寻址上

• 衡量磁盘的重要主要指标是IOPS和吞吐量。

1）IOPS

IOPS（Input/Output Per Second）即每秒的输入输出量（或读写次数），即指每秒内系统能处理的I/O请求数量。

随机读写频繁的应用，如小文件存储等，关注随机读写性能，IOPS是关键衡量指标。可以推算出磁盘的IOPS = 1000ms / (Tseek + Trotation + Transfer)，如果忽略数据传输时间，理论上可以计算出随机读写最大的IOPS。常见磁盘的随机读写最大IOPS为

7200rpm的磁盘 IOPS = 76 IOPS

10000rpm的磁盘IOPS = 111 IOPS

15000rpm的磁盘IOPS = 166 IOPS

2）吞吐量

吞吐量（Throughput），指单位时间内可以成功传输的数据数量。

顺序读写频繁的应用，如视频点播，关注连续读写性能、数据吞吐量是关键衡量指标。

它主要取决于磁盘阵列的架构、通道的大小、磁盘的个数。

• 不同的磁盘阵列存在不同的架构，但他们都有自己的内部带宽，一般情况下，内部带宽都设计足够充足，不会存在瓶颈。
• 磁盘阵列与服务器之间的数据通道对吞吐量影响很大，比如一个2Gbps的光纤通道，其所能支撑的最大流量仅为250MB/s
• 假如磁盘的吞吐量为40MB/s，一秒就可以读取2560个页。一个页的读取耗时为0.4ms左右

2. 行结构row_format

2.1 Compact紧凑

2.1.1 行溢出

• 一页16kb = 16384字节
• varchar(65533)
• 显然一行数据的列值 > 一页的大小，这时，campact行格式采取的存储措施是，一页只存储该字段值的一部分，剩余部分存储在其他页中，使用20字节记录指向其他页的地址和存了多少字节

2.1.2 作用

Compact是一种基于固定长度存储的方式，存储的额外信息较少，具有较高的存储效率

2.1.3 内容1-额外信息

行结构整体如图2.34

1、变长字段长度

字段为name varchar(32)时，实际存储数据’mjp’，没用到32字符。这时候就需要记录下变长字段的真实长度

• eg：name varchar(32) 、mark varchar(255)，实际存储一条数据时为(‘mjp’，‘test’)，则变长字段长度为03、04，倒过来即为 04 03，即在.ibd文件中使用16进制查看数据时，这一行数据的变长字段长度内容为04 03
• Null值不记录

2、NULL值列表

• 插入一条数据(1,‘mjp’,null,‘2024-01-21’,null)，如果列字段值为null，则使用二进制1表示，0表示非null
• 则此数据二进制形式0 0 1 0 1,倒过来即10100对应十六进制结果为20，在ibd文件中存储的就是10100。
• 如果某一列明确声明NOT NULL，则不需要使用0、1来表示会忽略
• 所以在.ibd文件中，(1,‘mjp’,null,‘2024-01-21’,null)并不会记录null，只会通过10100 + 记录1、mjp和2024-01-21

3、记录头信息

记录头整体结构如图2.35所示

1）delete_mark

• 大小：占用1个二进制位
• 作用：标记着当前记录是否被删除
• 内容
  0：代表记录并没有被删除
  1：代表记录被删除掉了
  • delete语句实际上并没有真实的将数据从页中从磁盘上物理删除
  • 这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后其他的记录在磁盘上需要重新排列，导致性能消耗
  • 只打一个删除标记，被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为可重用空间
  • 之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉
  • 被覆盖掉时，数据才算是真正的被物流删除了

2）record_type

• 作用：记录的类型
• 内容：
  0：表示普通记录
  1：表示B+树非叶节点记录，即表示目录项
  2：表示最小记录
  3：表示最大记录

3）heap_no

• 作用：表示当前记录在本页中的位置

• 内容：插入数据时，图2.35中四条数据，heap_no从2开始算起，依次为2、3、4、5

  • 0：最小记录的位置，对应record_type = 2

  • 1：最大记录的位置，对应record_type = 3，

    它俩的位置最靠前

4）next_record

• 多条数据的简单结构如图2.25

• 作用：从当前记录的数据到下一条记录的数据的地址偏移量，即单链表

  • eg：第一条记录的next_record值为32，意味着从第一条记录的数据的地址处向后找32个字节便是下一条（按照主键值由小到大的顺序）记录的真实数据
  • 最小记录（heap_no = 0 && record_type=2）的下一条记录就是本页中主键值最小的用户记录
  • 而本页中主键值最大的记录的下一条记录就是最大记录
  • 上图用箭头代替偏移量表示next_record

• 删除操作

  如图2.26所示

  

  • 第2条数据的delete_mark会从0 -> 1
  • 第一条数据的next_record会指向第三条数据
  • n_owned会从5 -> 4
    • 和页目录有关
    • 最小记录作为组一，数据个数1条、4条数据+最大记录作为组二，个数5条
    • 会将组个数记录在每组的最大记录中
    • 组一的个数为1，记录在最大成员中即最小记录（组一就它一个成员），n_owned=1
    • 组二的个数为5，记录在最大成员中即最大记录（组二中最大成员就是最大记录），n_owned=5
    • 现在组二失去了第二条数据，所以成员个数从5 -> 4，所以最大记录中的n_owned从5 -> 4
  • 如果删除了多条数据，多条垃圾数据之间会用链表串联起来

2.1.4 内容2-真实数据

4、表中列的值

正常列的数据

5、transaction_id

事务id，6字节

6、roll_point

回滚指针，7字节

7、row_id

行id，6字节非空且唯一：如果用户未声明主键id，InnoDB会使用row_id作为primary key。

如果表中有主键id了，则不会存在row_id

2.2 dynamic(默认)

mysql会根据行的大小自动选择不同的存储方式进行存储，以实现更好的查询性能和空间利用率

1、和Compact的区别

发生行溢出时，dynamic是将该字段的全不值都存到其它页中

2.3 Compressed压缩

行溢出时，处理方式和dynamic相同，在此基础上使用zlib算法进行压缩处理

3. 页结构

3.1 基本信息

3.1.1 大小

16kb

3.1.2 最小单位

数据库I/O操作的最小单位是页，即使修改了一条数据，从内存回刷磁盘，IO也是页维度

mysql中的存储空间如图2.32

最大的就是表空间，即对立表空间.ibd文件，存储了索引和数据

3.1.3 页结构

如图2.33

3.2 File Header

3.2.1 作用

描述各种页的通用信息（如页的编号、其上一页、下一页是谁等）

3.2.2 内容

1、offset

每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号可以唯一定位一个页。

2、type

代表当前页的类型，页类型常用如下

• Undo log日志页
• 事务系统数据
• 索引页=数据页

3、prev和next

• InnoDB都是以页为单位存放数据的
• 如果数据分散到多个不连续的页中存储的话需要把这些页关联起来，通过双向链表关联
• prev和next就是双向链表的2个指针
• 保证这些页之间不需要是物理上的连续，而是逻辑上的连续。

4、checksum校验和

1）代表当前页面的校验和

2）校验和定义

• 类似hash算法：给定2个长字符串进行比较字符串是否相同，如果hash结果值不一样，则两个字符串一定不相同
• 校验和同理，用于校验2个页是否相同

3）作用

• 如果发生了update操作，在修改后的某个时间需要把数据同步到磁盘中
• 但是在同步了一半的时候断电了，造成了该页传输的不完整（可能只同步了半页的数据，不满足页作为最小IO操作的条件）
• 为了检测一个页是否完整，这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对
• 如果两个值不相等则证明页的传输有问题，需要重新进行传输。
• 具体的
  • 当完全写完时（没断电正常情况下），校验和也会被写到页的尾部，则页的首部和尾部的校验和应该是一致的。
  • 如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错
• eg
  • 磁盘中页10的头校验和值 = 111，尾检验和=111
  • 此时内存中，对页10进行了update操作，页校验和更新为777
  • 回刷，将磁盘中页10的头检验和更新为了777，此时断电了，磁盘中页10的尾检验和还是111，显然头尾不一样，说明不是一个完整的回刷页
  • 要么就是重启后，继续将页10剩下的数据回刷完成，这样尾校验和也更新为777；要么就是回滚本次回刷操作

5、lsn

Log Sequence Number：页面被最后修改时对应的日志序列位置

作用结合校验和一起校验页的完整性的

3.3 File Trailer

检验和 + lsn，同File Header一起检验页的完整性

3.4 用户记录User Records

按照比如Compact行格式一条一条，相互之间形成单链表，参考行结构

3.5 最大和最小记录

Compact行格式中的记录头信息中的heap_no和record_type

• heap_no = 0最小记录,record_type=2
• heap_no = 1最大记录,record_type=3

3.6 页目录

3.6.1 作用

在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高

页目录通过二分查找法的方式快速定位到具体的某行数据，提升效率。

3.6.2 页目录+二分法实现快速查找

slot槽位

页目录结构如图2.36和2.37

1、定义

• 每一页中，将一部分数据划分成一组
• 第 1 组，也就是最小记录所在的分组只有 1 个记录
• 最后一组，就是最大记录所在的分组，会有 1-8 条记录
• 其余的组记录数量在 4-8 条之间
• n_owned：在每个组中最后一条记录的头信息中会存储该组一共有多少条记录
• 页目录[]，用来存储每组最后一条记录的地址偏移量即槽（slot），每个槽相当于指针指向了对应组的最后一个记录（如果是聚簇索引，则指向这一组id最大值对应的数据，如果是二级索引，则指向这一组索引字段最大值对应的数据）

2、作用

当确认数据在某一页中，则从目录页中按照二分法，查找所有槽位slot的值，快速定位到具体在哪个组中

3、查找实操

• 页10，对应的页目录[1,9,18,27]
• 找id = 20的，很明显在slot4（27）对应的分组中
• 但是slot4=27是这个组中最大值，链表是单向从小到大的，不能向前找。
• 所以需要找slot3=18的next_record依次找到id=20的数据

3.7 Header

1、slots：页槽的个数，方便二分查找

2、还未使用空间的最小地址：当插入新数据时，可以快速获取要插入此页中的哪儿

3、页数据个数

4、最后插入记录的位置

5、max_trx_id：二级索引中，修改当前页的最大事务的id

6、页的在B+数中的层级

7、当前页属于哪个索引

3.8 多页数据存储

简略如图2.27

• 页之间的地址不一定连续（不需要页10地址0x01 -必须指向>页11地址0x02）
• 自增id必须连续
  • 必须是1 -> 3 -> 4 -> 5
  • 如果页10中已有数据1 ->3 -> 5且页10已经插满了数据
  • 此时插入数据id = 4
  • 不能将id =4的数据插入页28
  • 必须将页10分裂，重新排为1->3->4，id = 5的数据存入页28
• 每一页的多条数据之间是单向链表、页之间是双向链表

4. 区段表空间

1、区定义

一个区是1M，可以存放64个连续的页

2、区作用

顺序IO，因为页之间可能不是物理连续的存储，而是使用的双向链表进行逻辑上连续。为了顺序IO查找更快，将64个物理上连续的页放在一个区中。这样where c between 10 and 300，很有可能就在一个区的连续页中，这样顺序IO一次就可以将这些页都捞取出来。

3、段定义

如果区1、区2都放叶子节点，区3、区4都放目录页即非叶子节点，那么区1、2放在一个段中（数据段），这样更方便查询。区3、4放在一个段中（索引段）

4、表空间定义

InnoDB的最高层的逻辑结构，数据和索引都存放于此。

• 独立表空间：每张表都有一个单独的表空间，便于空间管理

• 大小：新表创建时.ibd文件默认96kb(6页)，mysql8.0之后.frm和.ibd都和为.ibd了，默认112kb(7页)

5. 索引

5.1 聚簇索引

5.1.1 数据结构

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

• 叶子节点：装的是每一行的数据，对应的record_type = 0表示普通记录

• 非页面节点：record_type = 1表示目录项记录，页30中

  • 第一行 ：2（最小）,1（目录）,1,1,1,3(最大)
  • 第二行：1表示这一页的最小数据id=1
  • 第三行：10表示id=1的数据，在第10页（是个地址）

• 数据即索引，索引即数据

  Innodb的索引和数据存放在.ibd文件中，因为数据本身就是要一个索引(数据结构)存储的，索引的叶子节点又是数据。

• 聚簇表示：叶子节点存放数据(聚簇索引)，非聚簇表示叶子节点不存放数据(二级索引，叶子节点只存放主键id，不存放数据，所有才会有根据主键id再回表这一说)

5.1.2 Innodb的索引模型-N叉树

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。

考虑到树根【叶子节点】的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。

在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个10亿级别行的表，使用1200叉树来存储，单独访问一个行需要3个10 ms的时间

5.1.3 B+树

• 数据都是存储在B+树中的
• 每一个索引在InnoDB里面对应一棵B+树。

5.2 二级索引

5.2.1 单列索引

如图2.29

• 叶子节点：索引列的排序值，以及对应的主键id值
• 非叶子节点即目录页中，实际上也存储了主键id，如图2.31

这样在存入(id=9,c2=1)的数据时，就可以在页3中，先根据c2=1判断，再根据id = 9判断，可推断出存入页5中

5.2.2 联合索引

如图2.30，以c2、c3两字段组成联合索引

• 页55中，第1、2条数据的c2列值相同，则再按照c3列的值进行递增排序o、u
• 当我们where c2 = 4 and c3 = ‘u’
  • 页72找到c2 = 4的数据，在c2=2对应的页59内（因为页59对应的c2最小值为2，页70对应的c2最小值为9了，肯定不满足）
  • 页59，找c2=4的数据，可能在页50（对应c2的最小值为2，c2=4可能在此页中），也可能在页55中
  • 但是页50中，c2min=2，但是c3min为e；页55，c2min = 4且c3min = o，我们要找的c3 = u的
  • 所以，可以判断处，c2=4 and c3 = 'u’一定在页55中
  • 页55，找到c2=4 and c3 = 'u’对应的主键id = 1
• 目录记录的唯一性：事实上非叶子节点中，除了存储了c2、c3、页地址，还存储了主键id的值。通过c2+c3+id保证目录记录的唯一性，因为只靠c2 + c3是无法保证目录的唯一性的，因为c2 + c3都相同的数据也可能存在，这样在插入一条新的数据时，加入c2=x and c3 = y的数据也存在，这个时候就需要靠id来判断存入哪个页中

5.2.3 前缀索引

为字符串创建前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

alter table SUser add index index1(email); 

alter table SUser add index index2(email(6));

select id,name,email from SUser where email='zhangssxyz@xxx.com';

查询步骤如图2.4（回表1次）

• 从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值
• 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集
• 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email='zhangssxyz@xxx.com’的条件了，循环结束。

前缀索引查询步骤如图2.5（回表4次）

• 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1
• 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃
• 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集
• 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

1）优点：

对于这个查询语句来说，如果你定义的index2不是email(6)而是email(7），也就是说取email字段的前7个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到ID2，只扫描一行就结束了。

2）缺点：

• 有可能额外增加回表的次数

• 使用前缀索引就用不上覆盖索引对查询性能的优化了，必须回到ID索引再去判断email字段的值

• 前缀索引无法做orderBy、groupBy（可以在代码中使用map的groupBy）

3）确定前缀的长度

• 算出这个列上有多少个不同的值：

 select count(distinct email) as L from user;//假设有1000个不同的值

• 依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

select count(distinct left(email,4) as L4, //只有200个不同的值，损失了太多区分度

select count(distinct left(email,5) as L5, //有500个不同值，仍损失一半区分度

select count(distinct left(email,6) as L6, //有800个不同值

select count(distinct left(email,7) as L7, //有950个不同值，损失比例5%，可以接受。
left(email,7)既节省空间，又不用额外增加太多的查询成本（损失区分度导致的多次回表）

4）如果前缀索引很难区分，则其他优化方式：

• 倒序存储

  如果你存储身份证号的时候把它倒过来存，每次查询的时候，可以这么写

select x,x,x from t where id_card = reverse('xxxxx');

由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。

5）使用limit时候，不能使用前缀索引

alter table user add index index2(email(6));

select id,name,email from user where name = 'mjp' order by email limit 3;

在order by的时候，可能前3个数据都一样，无法支持使用索引排序，只能用文件排序

5.3 索引创建SOP

5.3.1 强制

1、索引字段禁止为NULL，请为字段设置默认值

2、创建联合索引时区分度高的字段在前

• 区分度计算

select count(distinct(a)) / count(*) from t;

• 发展角度

  表中字段poi_id和sale_date，已知poi_id总共就只会有<100种不同的值，而sale_date每个月就是30种不同的值，而且随着日期不断推迟，sale_date字段的区分度只会越来越高。这种情况下，联合索引的顺序建议为:

sale_date,poi_id

3、对于区分度很低的字段，没必要放在联合索引中。

• eg：性别非男即女50%的区分度
• eg：是否有效字段有效|无效，没必须放在索引中
• 尤其是在创建唯一键的时候，这种区分度为50%的字段，更不要作为唯一键的一部分，想想这种字段是否可以不存在于表中最好

4、ctime字段要加上索引(便于后续数据归档)

5.3.2 建议

1、单张表中索引数量不超过6个, 单个索引中的字段数不超过5个

2、建议使用联合索引，而非单个索引

3、如果必须建立(a,b)和b两个索引，注意索引字段大小。eg：name字段是比age字段大的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

4、sale_date日期字段作为索引时，建议使用int(11)而非varchar

• sale_date int(11) NOT NULL COMMENT ‘销售日期’
• 好处1：统一格式均为20240101
• 好处2：sale_date作为索引字段，相较于varchar更好比较大小，便于查询

5、对频繁更新的字段，尽量不要创建索引

• eg：status字段、utime

6. 三范式

要想设计一个结构合理的表，必须满足一定的范式(标准)

6.1 键和属性

球员表(player) ：id、球员编号 、 姓名 、 身份证号 、 年龄 、 球队编号
球队表(team) ：id、球队编号 、主教练 球队所在地

• 主键 ：默认为自增主键id

• 外键 ：球员表中的球队编号。

• 主属性 ：能够唯一确定一条数据的字段。在球员表中，主属性是id、球员编号、身份证号

• 非主属性 ：其他的属性（姓名） （年龄）（球队编号）都是非主属性。

6.2 一范式

1、定义：每一列原子不可再分

2、举例：address列：上海市杨浦区大桥街道1000弄

可以拆分为：

省	市	区	详细地址
上海	上海	杨浦	大桥街道1000弄

3、原子性

是主观的，主要是根据业务是否会用于搜素，统计等诉求。加入要统计上海市-杨浦区的用户有多少人，则address字段就需要拆分为原子的更小粒度。如果没有这种诉求，则直接作为user_info一起存储也可。

6.3 二范式

1、定义

非主键必须完全依赖主键，不能有部分依赖

2、举例

• 完全依赖

商品表: skuId是主键，skuName、品类id、商品价格等都是非主键。都完全依赖skuId

skuId已知，则skuName就知

skuId已经，则对应的品类id就知

skuId已知，则对应的价格就知

• 非完成依赖

学生id	老师id	学生name	老师name
10001	2001	张三	李丽
10002	2002	李四	韩理
10003	2003	王五	齐其

主键：学生id + 老师id

部分依赖：学生name字段，只依赖主键中学生id，即部分依赖。同理老师name

问题：数据冗余

3、解决：多对多

• 学生表
• 老师表
• 学生-老师关联表，多对多关系

id	学生id	老师id
1	10001	2001
2	10002	2002
3	10003	2003
4	10004	2003
5	10004	2004

学生1：老师N

10004学生的数学老师是2003，语文老师是2004

老师1：学生N

2003数学老师，既教10003同学，又教10004同学

6.4 三范式

1、定义：非主键必须直接依赖主键，不能传递依赖

本质：两个非主键之间不能有依赖关系

• 球队名称 -->球队编号 -->球员编号
• 部门名称 --> 部门编号 -->员工编号
• 品类名称 --> 品类id --> sku_id

2、解决

产生依赖传递的两个非主键，单独抽出来一张表。

• 表1：品类id、skuId、商品价格等
• 表2：品类id、品类名称

3、优点和缺点

• 优点：冗余低

• 缺点：需要各种join，查询效率低

6.5 反范式

1、背景

范式的缺点就是需要各种join，查询效率低。我司甚至不允许复杂sql查询，比如join、子查询等。所以，这时候就需要反范式

2、举例

品类名称 --> 品类id --> sku_id，不满足3NF。但业务一般展示sku的品类id同时也会展示这个品类名称。如果为了满足3NF拆开，则需要用join查询，这样业务访问量大的时候，反而影响查询性能。所以，在性能和标准之间，我们也需要考量

3、问题

如果我们为了满足性能，采用了反范式，可能存在以下问题

品类名称 --> 品类id --> sku_id

• 原本品类id：100100的品类名称是蔬菜。所以sku表中的一条数据为id：1、skuId：777、category_id:100100、category_name: 蔬菜
• 有一天，业务语义调整了，品类id：100100不表示蔬菜了，表示牛奶；或者干脆品类id：100100不存在了。这样的话，id=1这条数据中，category_name就是脏数据，这样后续查询等操作就不准确了
• 为了数据准确，就需要刷新id=1的数据
• 所以，如果我们满足3NF，单独抽出来category_id和category_name关系表，这样category_id对应的category_name变了，就只需要更新下最新的name即可。category_id没了，则直接删除category_id即可。不会有脏数据。

4、思考和感悟

• 对玉sku_id和sku_name这种name一般不会改变或删除的，且又需要一起查询到并展示，可以使用反范式
• 对于category_id和category_name这种经常可能变化的，可以遵循3NF，单独抽出来

7. 表设计规范

• 不建议使用复数做列名或表名
• 建表

create table `my_table`(
	`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `order_no` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '单号',
    `supply_code` int(11) DEFAULT 0 COMMENT '供应商code',
    `sku_category_level` tinyint(4) NOT NULL DEFAULT 0 COMMENT '品类级别',
    `sku_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'sku名',
    `extra_info` json DEFAULT NULL COMMENT 'sku额外信息',
    `price` decimal(26,6) DEFAULT NULL COMMENT 'sku价格',
    `trigger_date` date NOT NULL  COMMENT '触发日期2024-01-01',
    `trigger_time` time NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '触发时间10:00:00',
    `apply_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
    `approve_time` datetime DEFAULT NULL COMMENT '审批时间',
    `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `valid` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否有效0无效,1有效',
    PRIMARY KEY(`id`),
    UNIQUE KEY uniq_order_no(`order_no`),
    KEY idx_ctime(`ctime`),
    KEY idx_code_level(`supply_code`,`sku_category_level`),
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT '自定义表'

补充：order_no使用了utf8mb4_bin大小写敏感（区分大小写）

8. 字段设计规范

8.1 SOP

• 建议字段not null，因为null的存储会占用更多的空间相比较默认值0（等效’0’）、""等，在索引统计和值的比较时都更加复杂，索引必须是not null的
• varchar 是可变长字符串，不预先分配存储空间，长度不要超过1024，如果存储长度过长 MySQL 将定义字段类型为 json。甚至考虑独立出来一张表，用主键来对应，避免影响其它字段索引效率。
• 请为列添加默认值
• tinyint(1)：tinyint值有符号范围: -128 - 127，tinyint(1)代表显示1位（boolean即1）
• tinyint(4)：表示byte，显示4位，数字+符号 =》4位
• int(11)：用来展示字符的个数11个，int(1)和int(20)是相同的，主要是mysql客户端命令行用来显示字符的个数
• varchar(1000)：一个字母是1字节，一汉字是3字节，所以最多占用1000*3+2(varchar > 255需要额外与的2字节记录字符串的长度)=3002字节
• 当需要唯一约束时才使用 NULL，仅当列不能有缺失值时才使用 NOT NULL

8.2 主键

8.2.1 默认主键

如果你创建的表没有主键，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。

8.2.2 自增id存在的问题

• id是在数据库服务端生成的，不是在业务层面生成
• 安全性不高，前端入参id = 1，很明显能够知道where id =1是查询条件。容易攻击

8.2.3 UUID作主键的劣势

对比	自增整型	UUID
大小	8字节	mysql是用字符串存储占36字节（使用二进制方式存储则占16字节），浪费存储空间
比较大小	字符串比大小慢	整型快
插入	随机插入，如果4k页已满，则需要移动分页	顺序插入，不需要移动已经数据，效率高
查询	慢	连续存储，查询是一段范围，更快

• 自增UUID：mysql8.0中对UUID作为优化，uuid可以单调递增（将秒-分-时，改为时-分-秒）；同时存储占用16字节

  (uuid_to_bin(UUID(), true));
  

综上，顺序io不需要再额外的寻道，只需要第一次寻址完成后，磁头顺序读取数据即可。

所以顺序I/O一般比随机I/O快几千倍

8.2.4业务字段作主键

• 商品id：不可以，这个id可能会注销，给别的商品用

• 手机号:：不可以，用户可能会注销，运营商再给别人用

• 身份证号：不可以，因为主键id不能为空，但是并不是所有用户都愿意将隐私信息输入。可能会影响业务推进

• 单据号：可以。TG|HT + 01|02|03|04 + 231231 + 10位随机数(一个业务类型，一天最多有10位数的单据生成，可以使用雪花算法生成、也可以使用leaf生成、也可以使用redis自增生成)

  推荐：生成单据号

8.2.5 自增主键不一定连续

1、背景

唯一键冲突导致id自增+1后，无法恢复到错误发生之前的值（事务的回滚也会产生类似的现象）

2、描述

表中name字段是唯一键，假如表中有了一条数据(1,mjp,18)，此时再insert(null,mjp,28)

3、执行流程如下

• Innodb发现用户没有传入自增id值，会去获取当前表的自增值应为2

• 将传入的内容改为(2,mjp,18)

• 再insert之前会先修改自增至为3，然后再插入

• 报唯一键冲突。语句返回

• 此时id=2的数据没有插入成功，但是id此时已经为3了不会被改回去。下次再插入数据之前会获取到id=3

• 所以没有id=2这行数据

• 补充：批次一200、批次二200、批次三200，批量插

  public void batchInsert(List<TestTableDO> DOList) {
      if (CollectionUtils.isEmpty(DOList)) {
          return;
      }
      Lists.partition(DOList, 200).forEach(partitionData -> {
          this.testTableAutoGeneratorMapper.batchInsert(partitionData);
      });
  }
  

  • 同一个批次，插入多条数据时，是原子性的，一条失败，这个批次200条都失败，不会写入表中

  • 不同批次之间，没有原子性，报错的批次 以及 其后面的批次，都不会写入。报错之前的批次数据可正常插入
    eg1：批次一失败，批次一中200条数据都不会写入。且后续批次二、三都不会执行
    eg2：批次一成功，会插入200条数据，批次二中有失败，则批次二全部写入失败，批次三不会执行

    eg3：原本id = 1，批次一中因为唯一键冲突导致插入失败，则批次2再插入时，下一条数据的id = 1 + batchSize即id从201开始

4、优化： 可以使用alter table A engine=InnoDB语句来优化空洞 。

8.3 Varchar

8.3.1 长度

• varchar(100) 就是指 100 个字符

• 最大65535，但是只能Varchar(65532)，行结构中变长字段长度2字符、NULL值列表1字符（如果声明了Not Null，则不需要），业务中不建议超过5000，超过了5000可选择将此字段拆出来text（65535）放在另外一张表或使用Json

8.3.2 char,varchar,json类型的选用

• 知道固定长度的用char（0-255）,比如MD5串固定是32位
• 经常变化的字段用varchar，最好不要超过255
• 超过255字符的只能用varchar或者text,不能用char

8.3.3 varchar(20)与varchar(255)

• 都是可变的字符串

• 使用二者保存较短的字符串’hello world’时,占用的空间都是一样的

• 当20<字符长度<255,varchar(20)会报错,varchar(255)会正常插入

• 字符串特别长
  字符长度>255,可以选择更大的值,VARCHAR(M)定义的列长度为可变长字符串，M取值可以为0~65535(64K)

8.3.4 varchar(255) 和varchar(256)

• 当定义varchar长度小于等于255时，长度标识位需要一个字节
• 当大于255时，长度标识位需要两个字节

故

当我们定义一个varchar(255)的字段时，其实它真实使用的空间是256（255+1）字节
当我们定义一个一个varchar(256)字段时，它真实使用的空间是258（256+2）字节

8.3.5 BloB

• 大小：BLOB：16k，mediumblob：16M、LongBlob：4G

8.4 字符集COLLATE

8.4.1 定义

字符集是一套符号和编码，用于比较字符的一套规则。

8.4.2 作用

• 在mysql中，字符类型的列比如：char、varchar，都需要有collate和mysql进行交互，告诉mysql这些列该如何进行排序
• COLLATE和order by、distinct、group by、having语句息息相关，同时也会影响where条件中大于小于等于的查询

8.4.3 默认值

• 通常的字符集都utf8mb4（MySQL8.0默认）
• utf8mb4编码的默认值为utf8mb4_general_ci（不区分大小写）

8.4.4 utf8mb4分类

• utf8mb4_bin：将字符串每个字符用二进制数据编译存储，其数据比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写
• utf8mb4_general_ci：没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。
• utf8_general_ci：校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci的校对规则相比，比较正确性较差
• utf8mb4_unicode_ci：是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

总结：general_ci 更快，unicode_ci 更准确、utf8mb4_bin对字符大小写敏感。（补充：现在的CPU来说，它远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。使用者更应该关心字符集与排序规则在db里需要统一）

8.4.5 大小写敏感

1、概念

ci即case insensitive，不区分大小写即大小写不敏感。"A"和"a"在排序和比较的时候是一视同仁。

selection * from test where cloumn="a"同样可以把cloumn为"A"的值选出来。对于mysql来说，'a'和‘A’没有区别

2、实战

使用单据号作为唯一键： 数据001A落表的时候会变成001a，因为mysql默认是不区分大小写的。

这样，当表里面有001a的时候 & 其是唯一键，再存001A就会导致唯一键冲突。

3、情景复现

CREATE TABLE `user` (
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名字',
  UNIQ_KEY `name` (`name`) COMMENT '唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=''
  
这里的建表语句，使用的是COLLATE=utf8mb4_unicode_ci，这种方式创建的表中的字符串字段，都是不区分大小写的。

• 表里面已经有name = "aaaa"的数据

• name字段是唯一键

• name为字符串，且为ci格式

• 再次插入数据name = 'AAAA’时，就会报错。唯一键冲突

  复现

4、解决

utf8mb4_unicode_ci或utf8mb4_general_ci ==> utf8mb4_bin

• 方式一：改变表的属性，区分表中字符串的列，使所有字符列都区分大小写（不建议）

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

• 方式二：也可以针对某一字符的字段属性，utf8mb4_bin（推荐）

  alter table table modify column `name` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '姓名';
  

5、sop

• 建表时使用

ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='xxxx'

• 需要大小写区分：

  如：用于存用户登陆的账号、密码。密码就必须大小写敏感

  当业务需要某个字段，需要区分大小写。即大小写不同有不同的含义时，则需要将字段设置为大小写敏感，即utf8mb4_bin

8.5 Json类型

8.5.1 使用场景

一般用于记录单据对应的明细（即1：N场景）

{
    "单据某个属性1":{
        "属性1的name、描述":"", 
        "属性1的类型type":"",
        "属性1的值value":""
        "属性1的额外extra信息":""
    }
}

8.5.2 大小

实战中，单行json字段列最大存储65535（64kb）

8.5.3 创建字段

`xxx` json default null comment ''

8.0.13版本之前，json类型字段不允许设置默认值。之后则可以default(‘’)

8.5.4 CRUD

• 插入

 INSERT INTO table (id, data) VALUES (1, '{"name": "John", "age": 30}'); 

• 更新

UPDATE table SET data = JSON_SET(data, '$.age', 31) WHERE id = 1; 

删除JSON列中的数据

UPDATE my_table SET data = JSON_REMOVE(data, '$.age') WHERE id = 1;

8.5.5 对象转为json字符串

1、GsonUti

@UtilityClass
@Slf4j
public class GsonUtils {
    private static final Gson GSON = new Gson();

    public static String toJsonStr(Object object) {
        return GSON.toJson(object);
    }

    public static String toJsonStr(Object obj, Type type) {
        return GSON.toJson(obj, type);
    }

    public static <T> T fromJson(String json, Class<T> classOfT) {
        return GSON.fromJson(json, classOfT);
    }

    public static <T> T fromJson(String json, Type typeOfT) {
        return GSON.fromJson(json, typeOfT);
    }

    public static String toJson(Object object) {
        try {
            return GSON.toJson(object);
        } catch (Exception e) {
            log.error("序列化失败", e);
        }
        return StringUtils.EMPTY;
    }
}

2、map->json

map.put("0915", "1");
map.put("0916", "2");
String jsonStr = GsonUtils.toJsonStr(map);

3、date -> json

如果类中有Date类型的属性字段，则需要在创建gson的时候设置一下日期格式
    Gson gson = new GsonBuilder()
        .setDateFormat("yyyy-MM-dd")
        .create();
    System.out.println(gson.toJson(new Date()));

8.5.6 json字符串转对象

1、{} -> 对象

String str = "{\"status\":{\"code\":0,\"__isset_bit_vector\":[1]},\"poiId\":1}";
 TResponse tResponseA = GsonUtils.fromJson(str, TResponse.class);

2、json->map

Map<String,String> json2Map = GsonUtils.fromJson(jsonStr,new TypeToken<HashMap<String,String>>(){}.getType());

3、json->list

List<SkuDTO> list = GsonUtils.fromJson(jsonStr,new TypeToken<List<SkuDTO>>(){}.getType());

List<Long> list2 = GsonUtils.fromJson(Lists.newArrayList(1L,2L), new TypeToken<List<Long>>() {}.getType());

8.5.7 json字符串比较不同

public void t() throws JSONException {
        String s1 = "{\"skuId\":1,\"skuName\":\"test\",\"temp\":2,\"address\":\"bj\"}";
        String s2 = "{\"skuId\":1,\"skuName\":\"test\",\"temp\":3,\"author\":\"mjp\"}";

        // 方式一：
        JSONCompareResult result  = JSONCompare.compareJSON(s1, s2, JSONCompareMode.STRICT);
          <dependency>
            <groupId>org.skyscreamer</groupId>
            <artifactId>jsonassert</artifactId>
            <version>1.5.1</version>
            <scope>test</scope>
        </dependency>

        // 1中有2中没有
        System.out.println(result.isMissingOnField());
        List<FieldComparisonFailure> fieldMissing = result.getFieldMissing();
        for (FieldComparisonFailure fieldComparisonFailure : fieldMissing) {
            System.out.println(fieldComparisonFailure.getExpected());//address
        }

        // 2中有1中没有
        System.out.println(result.isUnexpectedOnField());
        List<FieldComparisonFailure> fieldUnexpected = result.getFieldUnexpected();
        for (FieldComparisonFailure fieldComparisonFailure : fieldUnexpected) {
            System.out.println(fieldComparisonFailure.getActual());//author
        }

        // 1中2中都有，但是val值不一样
        System.out.println(result.isFailureOnField());
        List<FieldComparisonFailure> list = result.getFieldFailures();
        for (FieldComparisonFailure fieldComparisonFailure : list) {
            System.out.println(fieldComparisonFailure.getField());//temp
            System.out.println(fieldComparisonFailure.getActual());//3
            System.out.println(fieldComparisonFailure.getExpected());//2
        }


        // 方式二：
        HashMap<String, Object> diffMap = Maps.newHashMap();
        Gson gson = new Gson();
        Map<String,Object> json1Map = gson.fromJson(s1,new TypeToken<HashMap<String,Object>>(){}.getType());
        Map<String,Object> json2Map = gson.fromJson(s2,new TypeToken<HashMap<String,Object>>(){}.getType());

        for (Map.Entry<String, Object> entry : json1Map.entrySet()) {
            String k1 = entry.getKey();
            Object v1 = entry.getValue();
            Object v2 = json2Map.get(k1);
            // 1中有2中没有
            if (v2 == null) {
                diffMap.put(k1, v1);
                continue;
            }

            // 1中2中都有，但是不一样
            if (!Objects.equals(v1, v2)){
                diffMap.put(k1, "expect:" + v1 + ", actual:" + v2);
            }
        }
        json2Map.forEach((k2, v2) -> {
            Object v1 = json1Map.get(k2);
            // 2中有1中没有
            if (v1 == null) {
                diffMap.put(k2, v2);
            }
        });

        System.out.println(diffMap);//{temp=expect:2.0, actual:3.0, address=bj, author=mjp}
    }

8.6不使用明文存储密码。

使用RSA加密实现如下

public class TestRSA {

    /**
     * RSA最大加密明文大小
     */
    private static final int MAX_ENCRYPT_BLOCK = 117;

    /**
     * RSA最大解密密文大小
     */
    private static final int MAX_DECRYPT_BLOCK = 128;

    /**
     * 获取密钥对
     *
     * @return 密钥对
     */
    public static KeyPair getKeyPair() throws Exception {
        KeyPairGenerator generator = KeyPairGenerator.getInstance("RSA");
        generator.initialize(1024);
        return generator.generateKeyPair();
    }

    /**
     * 获取私钥
     *
     * @param privateKey 私钥字符串
     * @return
     */
    public static PrivateKey getPrivateKey(String privateKey) throws Exception {
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        byte[] decodedKey = Base64.decodeBase64(privateKey.getBytes());
        PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(decodedKey);
        return keyFactory.generatePrivate(keySpec);
    }

    /**
     * 获取公钥
     *
     * @param publicKey 公钥字符串
     * @return
     */
    public static PublicKey getPublicKey(String publicKey) throws Exception {
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        byte[] decodedKey = Base64.decodeBase64(publicKey.getBytes());
        X509EncodedKeySpec keySpec = new X509EncodedKeySpec(decodedKey);
        return keyFactory.generatePublic(keySpec);
    }

    /**
     * RSA加密
     *
     * @param data 待加密数据
     * @param publicKey 公钥
     * @return
     */
    public static String encrypt(String data, PublicKey publicKey) throws Exception {
        Cipher cipher = Cipher.getInstance("RSA");
        cipher.init(Cipher.ENCRYPT_MODE, publicKey);
        int inputLen = data.getBytes().length;
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        int offset = 0;
        byte[] cache;
        int i = 0;
        // 对数据分段加密
        while (inputLen - offset > 0) {
            if (inputLen - offset > MAX_ENCRYPT_BLOCK) {
                cache = cipher.doFinal(data.getBytes(), offset, MAX_ENCRYPT_BLOCK);
            } else {
                cache = cipher.doFinal(data.getBytes(), offset, inputLen - offset);
            }
            out.write(cache, 0, cache.length);
            i++;
            offset = i * MAX_ENCRYPT_BLOCK;
        }
        byte[] encryptedData = out.toByteArray();
        out.close();
        // 获取加密内容使用base64进行编码,并以UTF-8为标准转化成字符串
        // 加密后的字符串
        return new String(Base64.encodeBase64String(encryptedData));
    }

    /**
     * RSA解密
     *
     * @param data 待解密数据
     * @param privateKey 私钥
     * @return
     */
    public static String decrypt(String data, PrivateKey privateKey) throws Exception {
        Cipher cipher = Cipher.getInstance("RSA");
        cipher.init(Cipher.DECRYPT_MODE, privateKey);
        byte[] dataBytes = Base64.decodeBase64(data);
        int inputLen = dataBytes.length;
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        int offset = 0;
        byte[] cache;
        int i = 0;
        // 对数据分段解密
        while (inputLen - offset > 0) {
            if (inputLen - offset > MAX_DECRYPT_BLOCK) {
                cache = cipher.doFinal(dataBytes, offset, MAX_DECRYPT_BLOCK);
            } else {
                cache = cipher.doFinal(dataBytes, offset, inputLen - offset);
            }
            out.write(cache, 0, cache.length);
            i++;
            offset = i * MAX_DECRYPT_BLOCK;
        }
        byte[] decryptedData = out.toByteArray();
        out.close();
        // 解密后的内容
        return new String(decryptedData, "UTF-8");
    }

    /**
     * 签名
     *
     * @param data 待签名数据
     * @param privateKey 私钥
     * @return 签名
     */
    public static String sign(String data, PrivateKey privateKey) throws Exception {
        byte[] keyBytes = privateKey.getEncoded();
        PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(keyBytes);
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        PrivateKey key = keyFactory.generatePrivate(keySpec);
        Signature signature = Signature.getInstance("MD5withRSA");
        signature.initSign(key);
        signature.update(data.getBytes());
        return new String(Base64.encodeBase64(signature.sign()));
    }

    /**
     * 验签
     *
     * @param srcData 原始字符串
     * @param publicKey 公钥
     * @param sign 签名
     * @return 是否验签通过
     */
    public static boolean verify(String srcData, PublicKey publicKey, String sign) throws Exception {
        byte[] keyBytes = publicKey.getEncoded();
        X509EncodedKeySpec keySpec = new X509EncodedKeySpec(keyBytes);
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        PublicKey key = keyFactory.generatePublic(keySpec);
        Signature signature = Signature.getInstance("MD5withRSA");
        signature.initVerify(key);
        signature.update(srcData.getBytes());
        return signature.verify(Base64.decodeBase64(sign.getBytes()));
    }

    public static void main(String[] args) {
        try {
            // 生成密钥对
            KeyPair keyPair = getKeyPair();
            String privateKey = new String(Base64.encodeBase64(keyPair.getPrivate().getEncoded()));
            String publicKey = new String(Base64.encodeBase64(keyPair.getPublic().getEncoded()));
            System.out.println("私钥:" + privateKey);
            System.out.println("公钥:" + publicKey);
            // RSA加密
            String data = "待加密的文字内容";
            String encryptData = encrypt(data, getPublicKey(publicKey));
            System.out.println("加密后内容:" + encryptData);
            // RSA解密
            String decryptData = decrypt(encryptData, getPrivateKey(privateKey));
            System.out.println("解密后内容:" + decryptData);
            // RSA签名
            String sign = sign(data, getPrivateKey(privateKey));
            System.out.println("加签后："+sign);
            // RSA验签
            boolean result = verify(data, getPublicKey(publicKey), sign);
            System.out.print("验签结果:" + result);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.print("加解密异常");
        }
    }
}

二、查询

1. sql执行顺序

1. from
2. join
3. on
4. where
5. group by(开始使用select中的别名，后面的语句中都可以使用)
6. avg,sum…【selec后面的分组函数、having后面的分组函数。having max(salary) > 8000)】
7. having
8. select : 拿出所有数据
9. distinct
10. union
11. order by
12. limit

如图1.1

两块：一块是Server层，它主要做的是MySQL功能层面的事情

一块是引擎层，负责存储相关的具体事宜。

1、连接器-建立连接

• 长连接
• 防止长连接占用内存过快导致OOM：5.7后执行 mysql_reset_connection来重新初始化连接资源

2、分析器-解析sql

• 你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。
• 一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

3、优化器优化

• sql执行顺序、索引的选择
• mysql会选择总代价最小的sql进行执行

总代代价= IO代价 + CPU代价+ 内存代价 + 远程代价

1）其中IO代价

• 从磁盘中读取一页的数据的代价，默认代价值=1

2）CPU代价

• 键比较和行估算

3）内存代价

创建临时表，默认代价值为20。在内存中创建临时表还好点，磁盘中代价太大

4、执行器执行

• 第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

5、调用Innodb的API接口查询数据并返回

2. sop

2.1 强制

1、不要使用负向查询，如：NOT IN/NOT LIKE

2、严禁 like ‘%abc’、like ‘%abc%（如果非要，则走es等搜索引擎）’、not in 、!= 、not exists、<>等语法。推荐like ‘北京%’

3、禁止使用select for update

4、禁止使用select子查询，使用join替代

• 原因：子查询一般会建临时表

select * from t where t.id in(
	select f.id from table where table.id in(10,20,30)
);
// 使用join替换
select * from t inner join table on t.id = table.id where table.id in(10,20,30);

2.2 建议

1、不建议使用前项通配符查找，例如 “％foo”，查询参数有一个前项通配符的情况无法使用已有索引。

2、对于连续的数值，能用 BETWEEN 就不要用 IN 了

select id from t where num in(1,2,3,4);

3、conut（任意）统计某列非空个数，一般都是全表扫描，尽量减少使用（有NULL的行不算）。

count查询where条件要能走索引

4、新增的查询SQL一定要执行过执行计划，访问类型type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。禁止all和index

5、ORDER BY、GROUP BY、DISTINCT（UNION）的字段,充分利用索引先天顺序避免排序，否则会消耗大量磁盘IO

6、对于select, in 操作建议控制在200个之内。最好在100内

7、索引中断问题

• 使用了函数

• 对字段做了函数计算，就用不上索引

  对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

• 8.0之后的版本，a_b_c联合索引，当where条件是b = and c = 也会使用到联合索引

8、not in、not exists建议使用left join on where xxx is null替代

9、如果要使用where (col1 = a and col2 = b) or (col1 = c and col2 =d)

• mysql 5.7.3之后优化支持以下等效查询

where  (col1,col2)  in  (
   (a,b),
   (c,d)
) 

• 对应代码

@Select
("<script"+
	"SELECT * FROM fulfill_data where (sale_dt, rdc_id) in " +
		"<foreach collection='pairs' item = 'pair' open='(' separator= ',' close=')'>" +
			"(#{pair.saleDate}, #{pair.rdcId})" +
		"</foreach> " +
	" limit 0,200 " +
"</script>")
List<XxxDO> select(@Param("pairs") List<ListKey> pairs);

• 不可使用以下方式查询

SELECT * FROM t WHERE sale_dt in('20240115', '20240116') and rdc_id in (111,777)

原因：但是使用上述方式查询，可能会产生笛卡尔乘积结果

sale_dt	rdc_id
20240115	111
20240116	111
20240115	777
20240116	777

但实际我们查询的是(sale_da = 20240115 and rdc_id = 111) or (sale_da = 20240116 and rdc_id = 777) 应该只返回2条数据才对

3. 常见函数

3.1 distinct

1、对结果中某一列去重

select * from 表名 where id in(select max(id) from 表名 group by 要去重的字段 )

2、去重

select distinct(rdc_id) from table where sale_date = '2024-01-22';

使用distinct时，去重字段最好作为联合索引的一部分，因为索引本身就是排序好的，这样找where满足条件的数据后，本身就是排序好的去重即可。否则会using temp

3.2 count

按照效率排序的话，count(普通字段)<count(主键id)<count(索引字段)<count(1)≈count(*)

3.3 when then

(
CASE table1.sku_temperature_zone 
	WHEN 5 THEN '常温' 
	WHEN 2 THEN '冷藏' 
	WHEN 1 THEN '冷冻' 
END
) as `温层`

3.4 日期格式转换

1、yyyymmdd 转 yyyy-mm-dd

 where
  sale_date = concat(
    substr('20220517', 1, 4),
    '-',
    substr('20220517', 5, 2),
    '-',
    substr('20220517', 7, 2)
  );

2、yyyy-mm-dd 转yyyymmdd

concat(substr('2022-05-18',1,4),substr('2022-05-18',6,2),substr('2022-05-18',9,2));

方式2

sale_date = replace('2021-11-03','-','');

4. 索引

4.1 数据展示顺序

• 如果使用到了联合索引，则按照联合索引的顺序依次展示数据。
• 如果没有用到索引，则按照主键id自增顺序展示数据

4.2 回表 & 覆盖索引

1、索引覆盖

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下2.1

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。【除非全覆盖索引，查询selcet 字段和联合索引字段一致，则不需要回表】也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。同时：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
• 如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引，或者联合索引的字段+id字段包含了所有要查询的字段，即为覆盖索引，即不需要回表
• 如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

2、回表

• 需要执行几次树的搜索操作，会扫描多少行

select * from T where k between 3 and 5

1. 在k索引树上找到k=3的记录，取得 ID = 300；
2. 再到ID索引树查到ID=300对应的R3；
3. 在k索引树取下一个值k=5，取得ID=500；
4. 再回到ID索引树查到ID=500对应的R4；
5. 在k索引树取下一个值k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表（回表本质是随机I/O比较耗时）。可以看到，这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。

• 回表属于随机IO

  原因：二级索引叶子节点字段1 + id1 、字段2 + id2，其中字段1、2是顺序排序的在一个页或一个区中，但是id1和id2可能在不同的页不同的区，所以回表本质上是随机IO

4.3 索引下推

• 背景

联合索引（name, age），现在需要检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩

 select * from tuser where name like '张%' and age=10 and ismale=1;

• 问题

like后面，索引失效。索引联合索引只能走到第一个name字段

• 无索引下推图2.2

在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

此时不关注age字段，只关注name字段，（name，age）二级索引找到叶子节点id，然后回表聚簇索引，查询id对应的这行数据。然后再根据age值与这行数据中的age值对比，一致才留下。因此，需要回表4次。

• 索引下推图2.3

而MySQL 5.6 引入的优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。

• 优点：减少回表次数（所以无需回表的查询，都不会触发索引下推）

• 索引下推ICP触发的条件

  name_age是联合索引，其中age在联合索引字段中

• 8.0优化：

8.0之后的版本，where name like ‘张%’ and age=10 and ismale=1，联合索引为name_age，不需索引下推直接正常走索引

4.4 唯一索引和二级索引

1、查询

• 唯一索引

唯一索引查找到数据在某一页中的某一条数据时，则可以结束。因为数据是唯一的

• 二级索引

这里以单字段二级索引为例，当查到是某一页的某一条数据时，还需要向后继续判断，因为可能name ='mjp’的有多条数据

• 性能可以忽略

2、更新

• 唯一索引

由于需要提前将数据页读取到内存中，用于判断插入的数据是否满足唯一性，故无法使用change_buffer(用于缓存更新语句的内容，类似于懒加载，缓存多条更新语句，等某条语句需要查询最新的内容了，再去刷盘（merge操作）（也会有后台线程定期的merge）。这种懒加载避免了，每次更新一条语句都要直接读取页，刷页，刷盘，减少读盘操作)

• 二级索引，可以使用到change_buffer

3、结论

• 如果更新频繁，建议使用普通索引（查询性能几乎无差、更新又可以使用到buffer减少读盘）
• 普通索引改为唯一索引，有风险，可能阻塞整个表的更新，因为唯一索引会导致更新变得“复杂”
• 写多读少的业务（日志、账单类业务），可以打开change_buffer且值设置大一些

5. join

5.1 left join

1、功能：获取左表的所有记录，即使右表没有对应的匹配记录，用null填充

2、建议：使用小表作为驱动表

1）本质

双层for循环

2）小表作驱动表的原因

• 假设：驱动表全表扫描、被驱动表走索引

• 被驱动表行数M，每次查询一条数据，需要先搜索索引，索引是B+树结构，且需要回表，综上复杂度2（回表+二级索引搜索） * Log2M（索引查询M条数据）

• 驱动表行数N，没索引全表扫描N行，且每一行都要到被驱动表上去匹配一次

• 双层For循环复杂度 N * 2 * Log2M

• N扩大1000倍，整体复杂度就扩大1000倍，所以一定要让N小的做驱动表（即“小表”作驱动表的原理）

3）“小表”定义

不是表本身数据量的多少，而是

两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”（正常情况下数据量小的表一般是小表）

3、强制

• 被驱动表的查询条件，必须是索引字段。理论上驱动表的匹配字段也最好是索引字段。

• 匹配的字段类型一定要一样，要么都是整型，要么都是字符串，否则会使用函数转换，索引失效

select * from t1 left join t2 on t1.name = t2.user_name;

5.2 inner join

1、功能：获取两个表中字段匹配关系记录，最终留下的每行数据中，左表和右表对应的列一定有数据

2、建议：使用inner join 即简写join时，mysql会选择小表作为驱动表

3、优化器

• 如果t1的name字段没索引，t2的user_name有索引，会选择t2作为驱动表
• 如果t1.name和t2.user_name都有索引，会选择小表作为驱动表
• 如果不想让优化器选择，类似于强制走某个索引，可以使用

straight_join代替join

5.3 on 和 where区别

5.3.1 概述

on：用于生产临时表的条件

where：在临时表产生后，再对临时表进行过滤，返回最终的数据

5.3.2 举例

t1

id	sku_id
1	111
2	222
3	333

t2

sku_id	sale_date
111	2023-01-01
222	2023-06-30
222	2023-12-31

5.3.3 on + where

select * from t1 left join t2 on t1.sku_id = t2.sku_id where t2.sale_date= '2023-06-30';

1、步骤一：将t1表中每行数据，用on条件（t1.sku_id = t2.sku_id）去获取匹配到的数据

t1.id	t1.sku_id	t2.sku_id	t2.sale_date
1	111	111	2023-01-01
2	222	222	2023-06-30
2	222	222	2023-12-31

2、t1未匹配到的行数据，也保留下来（这一行仅仅除了t1有正常数据，t2的数据字段列均为null）

t1.id	t1.sku_id	t2.sku_id	t2.sale_date
3	333	null	null

3、经过on条件后，生成的临时表temp

t1.id	t1.sku_id	t2.sku_id	t2.sale_date
1	111	111	2023-01-01
2	222	222	2023-06-30
2	222	222	2023-12-31
3	333	null	null

5.3.4 on

select * from t1 left join t2 on t1.sku_id = t2.sku_id and.sale_date= '2023-06-30';

1、将t1表中每行数据，用on条件（t1.sku_id = t2.sku_id and.sale_date= ‘2023-06-30’）去获取匹配到的数据

t1.id	t1.sku_id	t2.sku_id	t2.sale_date
2	222	222	2023-06-30

2、t1未匹配到的行数据，也保留下来（这一行仅仅除了t1有正常数据，t2的数据字段列均为null）

t1.id	t1.sku_id	t2.sku_id	t2.sale_date
1	111	null	null
3	333	null	null

3、经过on条件后，生成的临时表temp

t1.id	t1.sku_id	t2.sku_id	t2.sale_date
2	222	222	2023-06-30
1	111	null	null
3	333	null	null

5.3.5 IS NOT NULL 和 IS NULL

如图2.38

• 场景4

select * from a left join b on a.key = b.key where b.key is null

结合上述on + where的执行过程，先生成临时表，然后在临时表的基础上进行b.key is null过滤。明显是过滤掉a中存在b中不存在的数据。所以场景4的图如上

• 结论：查询条件not in 、not exists都可以替换成left join on where x is null

6. group by

6.1 分组、聚合

• 自动忽略null值

• 需要先分组、再聚合（avg、sum），否则整张表就是一个分组

• 使用group by 的场景：当出现每种、每个的时候，后续的内容就是需要分组的字段

  • eg1：查询每种性别的最高分

  select gender, max(math_score) from user group by gender;
  

  • eg2: 查询每个部门的员工数

  select depart_id, count(*) from emp group by depart_id;
  

  • eg3: 接上having过滤

  查询部门员工数  > 100的部门id
  
  步骤一：每个部门的员工数
  步骤二: 人数> 100
  select depart_id, count(*) from emp group by depart_id
  having count(*) > 100;
  

  每种工种最高工资 > 1w的工种id和最高工资数
  
  步骤一：每种工种的最高工资
  步骤二：最高工资 > 1w
  select job_id, max(salary) from emp group by job_id
  having max(salary) > 10000;
  

  查询最低工资 > 10号部门最低工资的  部门id和其对应的最低工资
  
  步骤一：每个部门的最低工资
  步骤二：10号部门的最低工资
  步骤三：每个部门的最低工资 > 10号部门的最低工资
  select depart_id, min(salary) from emp group by depart_id
  having min(salary) > (
      select min(salary) from emp where depart_id = 10
  );
  建议改成join连接查询
  

6.2 临时表问题

联合索引a_b_c

1、简单的临时表

select a,b,c from t where a = 1 group by c;

group by未使用到索引，会产生临时表和排序using filesort、using temporary

2、复杂临时表

索引为a

select id % 10 as m , count(*) as c from t where a = 1 group by m;

• 产生带临时表，内含2字段m（主键）和 c

• 扫描表t的索引a，依次取出其叶子节点上的id值

• 计算id % 10 =》 x

• 此时查看临时表中是否有值为x的行(1 % 10 = 1)，第一次没有，在临时表对应的m、c两列中插入一条记录(x, 1)

  m	c
  1	1

  如果后续有了值为x的行(11 % 10 = 1)，则m列值为x的这行，对应的c列值+1即（x，2）

  m	c
  1	2

• 遍历完索引a后，对临时表按照主键m进行正排序，结果返回给客户端

3、不产生临时表、排序

select * from t where a = 1 and b = 2 group by c;
select * from t where a = 1 group by b;

​

7. union 和 UNION ALL

1、union：选取表1和表2不同的值

2、union all：选取表1和表2所有的值，允许重复

3、表1order by UNION ALL 表2 order by

(select * from t1 where xxx order by xxx limit 100) 
UNION ALL
(select * from t2 where xxx order by xxx limit 100) 

如果在UNION中使用order by，那么必须使用()来包含查询，参考综合查询中

4、如果不强制要求数据不可重复，建议使用union all

因为union会给临时表加上distinct，用上了临时表主键id的唯一约束。唯一性校验代价很高

如果非要用，可以在java程序内存中进行去重。

如图2.19，union执行去重过程

8. order by

8.1 背景

查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。

索引city

select city,name,age from t where city='杭州' order by name limit 1000 ;

• Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

• Extra字段显示Using temporary，表示的是需要使用临时表

• 二者都出现，需要临时表，并且需要在临时表上排序

8.2 全字段filesort过程

如图2.6

1）初始化sort_buffer，确定放入name、city、age这三个字段；

2）从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3）到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；

4）从索引city取下一个记录的主键id；

5）重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；

6）对sort_buffer中的数据按照字段name做快速排序；

7）按照排序结果取前1000行返回给客户端。

如果排序数据量太大超过sort_buffer_size的大小（默认1M）

内存放不下，则不得不利用磁盘临时文件辅助排序。外部排序一般使用归并排序算法，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这number_of_tmp_files = 12个有序文件再合并成一个有序的大文件。

8.3 rowId排序过程

• 原理

max_length_for_sort_data，是mysql中专门用于控制排序的行数据的长度参数（默认1024）

如果select的字段加起来长度超过这个值，则会从全字段排序换为rowId排序

• 实现步骤如图2.7

1）初始化sort_buffer，确定放入两个字段name和id

2）从索引city中找到满足city="杭州"的第一个主键id

3）然后使用id回表，取name和id字段值放入sort_buffer

4）从索引city字段取下一个满足的id，同时使用id再回表，然后再存值，直到不满足city=“杭州”

5）对sort_buffer中的数据按照name排序

6）对排序后的结果取limit 1000

7）然后再使用这1000个id去回表，依次拿出city、name、age三个字段返回给客户端

• 总结

  • 默认会选择全字段排序，内存sort_buffer_size(1M|256k)够用，直接将所有字段放入sort_buffer中排序
  • 内存不够用，会在堆外进行排序，用临时文件进行排序，然后再归并
  • 如果查询的单行数据内容超过1024，则降为rowId排序

8.4 sop

1、如果无法避免filesort

• 可以调整max_length_for_sort_data，尽量使用全字段排序而非rowId排序
• 可以调整sort_buffer_size，尽量在内存中排序，而非在磁盘上排序

2、避免filesort

where查询条件可以使用到索引即可

select city,name,age from t where city='杭州' order by name limit 1000 ;

• 联合索引city_name在数据存储时就是有序的，所以查询的还是直接匹配到city="杭州"的排序好的数据
• 然后limit取1000条即可
• 然后根据city、name、id使用id去回表拿到所需字段即可
• 结合limit

3、发生了filesort但性能很好

• 联合索引name_age_address

select * from t where name = 'm' and age > 10 order by address;
1）会使用filesort，但是性能比不使用filesort还好
2)只使用到了联合索引的前半部分name_age，进行过滤后，发现数据量不大1w级别这种，可以直接在内存中排序反而更快

4、limit起作用

• 联合索引name_age

全表扫描 + filesort
select * from t where age = 18 order by name;

走索引 + using where
select * from t where age = 18 order by name limit 10
原因是：优化器认为先按照name进行order by然后再where比对 age = 18的取10条，认为走索引更快

9. limit

不要直接limit 10000, 200的写法正例:select table_name from table where id > 10000 limit 200

实战

List<DO> result = Lists.newArrayList();
boolean loop = true;
long id = 0L;
do {
    XxxDOExample example = new XxxDOExample();
    example.limit(200);
    example.setOrderByClause("id asc");
    XxxDOExample.Criteria criteria = example.createCriteria();
    criteria1.andIdGreaterThan(id);
    List<DO> selectByExample = myMapper.selectByExample(example);
    if (CollectionUtils.isNotEmpty(selectByExample)) {
    	result.addAll(selectByExample);
    	int size = selectByExample.size();
    	if (size < 200) {
        	loop = false;
    	} else {
        	id = selectByExample.get(size - 1).getId();
    	}
    }
} while (loop);

10. json类型

1、背景json字段net_pois_json数据内容如下

{
    "test1":{
        "poiId":323,
        "skuId2Qty":{
            "1":1,
            "2":2
        },
        "skuId2Date":{
            "3":"2023-01-01",
            "4":"2024-01-01"
        }
    },
    "test2":{
        "poiId":10005977,
        "skuId2Qty":{
            "5":5,
            "6":6
        },
        "skuId2Date":{
            "7":"2027-01-01",
            "8":"2028-01-01"
        }
    },
    "test3":10,
    "test4":["mjp","wxx"]
}

2、Map<String, Object> map 转为jsonString

map的key是String，第test1、test2的v是个对象，test3的v是Integer、test4的v是List<Stirng>
Gson g = new Gson();
String netPpoisJson = g.toJson(map);

10.1 json相关函数

json_extract()：从json中返回想要的字段

json_contains()：json格式数据是否在字段中包含特定对象

json_object():将一个键值对列表转换成json对象

json_array():创建json数组

10.2 k-v作为查询条件

1、k-v是一级

select * from t where 联合索引字段查询 and json_contains(net_pois_json, json_object("test3", 10));

• 等价

select * from t where 联合索引字段查询 and net_pois_json -> '$.test3' = 10;

• 结果

这一行的数据

• explain

ref + 使用联合索引 + using where

2、k-v是二级

• 背景

"test1":{
        "poiId":323,
        "skuId2Qty":{
            "1":1,
            "2":2
        },
        "skuId2Date":{
            "3":"2023-01-01",
            "4":"2024-01-01"
        }
    },

• 查询一级k1：test1对应的v1中，二级k：poiId对应的v2

select * from t where id = 1 and json_contains(
    json_extract(net_pois_json, '$.test1'), json_object("poiId", 323)
);

即判断

{
	"poiId":323,
     "skuId2Qty":{
            "1":1,
            "2":2
     },
     "skuId2Date":{
            "3":"2023-01-01",
            "4":"2024-01-01"
     }
}

中是否有指定的对象"poiId", 323

3、k-v是三级

select * from t where id = 1 and json_contains(
    json_extract(net_pois_json, '$.test1."skuId2Date"'), json_object("3", "2023-01-01")
);

4、k-v作为范围查询条件

select * from t where id = 1 and json_contains(net_pois_json -> '$.test4', json_array("mjp"));

10.3 查询k对应的v

1、k是一级

select id,  json_extract(net_pois_json, '$.test1') as v from t where id = 1;

• 等价

select id,  net_pois_json -> '$.test1' as v from t where id = 1;

• 结果

id, test1对应的v

{
        "poiId":323,
        "skuId2Qty":{
            "1":1,
            "2":2
        },
        "skuId2Date":{
            "3":"2023-01-01",
            "4":"2024-01-01"
        }
}

2、k是二级

select id,  json_extract(net_pois_json, '$.test1."skuId2Qty"') as v from t where id = 1;

3、k是三级

select id,  json_extract(net_pois_json, '$.test1."skuId2Qty"."3"') as v from t where id = 1;

10.4 查询所有的一级k

select id, json_keys(net_pois_json) from t where id = 1 ;

结果：[“test1”,“test2”,“test3”,“test4”]

10.5 json_search函数

1、作用：查询包含v的数据

2、前提：v必须是字符串

net_pois_json列{“id”:1, “name”:“苹果”, “price”:0.5}

select * from t where json_serarch(net_pois_json, "all", "苹果") is not null;

10.6 在原有的k-v基础上再增加k-v

{“id”:1, “price”:0.5} -> {“id”:1, “name”:“苹果”, “price”:0.5}

update t set net_pois_json = json_insert('{"id":1, "price":0.5}', '$.name', '苹果');

10.7 更新k对应的v值

{“id”:1, “name”:“苹果”, “price”:0.5} -> {“id”:1, “name”:“香蕉”, “price”:0.5}

update t set net_pois_json = json_replace('{"id":1, "name":"香蕉", "price":0.5}', '$.name', '香蕉') where id = 1;

补充

• 如果id=1的数据不是 {“id”:1, “name”:“苹果”, “price”:0.5}，则会被替换成 {“id”:1, “price”:0.5}
• 替换三级k对应的v

update t set net_pois_json = json_replace(
	'"test1":{
        "poiId":323,
        "skuId2Qty":{
            "1":1,
            "2":2
        },
        "skuId2Date":{
            "3":"2023-01-01",
            "4":"2024-01-01"
        }
    }', 
    '$.test1."skuId2Date"."3"', 
    '1994-11-23'
)
 where id = 1 ;

参考文档

MySQL5.7中文文档

​

11. 综合查询

11.1 背景表

1、sale_log as result: 主表，大部分字段都是取自这个表

2、sale_num as sale：需要从这个表获取真实销量sale_num字段

3、schedule as snap: 需要从这个表获取最终售最大售卖量total_stock字段

11.2 运算介绍

(sale.sale_num - result.origin_max_sale) as `降低|带来多货|提高销量PCS`;
(sale.sale_num - result.origin_max_sale) * result.sku_price as `销售额提高`;

11.3 分析

如图2.18

1、场景1

result表 left join sale 表，并按照 where条件形成场景1-降多货

• on条件result和sale二者中一一对应的关系字段
• where条件即降多货的条件

2、场景2

result 表 left join sale 表

• on条件result和sale二者中一一对应的关系字段

• where条件形成场景2-提升售卖量即提升GMV

3、场景1 UNION ALL场景2，组成临时表t

4、result表left join snap表，组成临时表m

• on条件result和snap二者中一一对应的关系字段

• where条件是日期范围

5、临时表t INNER JOIN 临时表m，形成最终的表

• on条件t和m二者中一一对应的关系字段

• where条件是网店id

6、概述sql

select
	t.主要字段，t.真实销量, m.最终最大售卖量
from t
inner join
	m
on
	t和m一一对应关系
where
	xxx

11.4 具体Sql

select distinct
	t.`商品SKUID`,
	t.`网店ID`,
	t.`网店名称`,
	t.`销售日期`,
	t.`角色`,
	t.`修改前数值`,
	t.`修改后数值`,
	t.`真实销量`,
	m.total_stock as `实际修改量`,
	t.`OR值`,
	t.`降低|带来多货|提高销量`,
	t.`降低|带来多货|提高销量PCS`,
	t.`销售价`,
	t.`销售价` * t.`真实销量` as `GMV`
from(
	 (
        select
        	result.sku_id as `商品SKUID`,
            result.poi_id as `网店ID`,
        	sale.poi_name as `网店名称`,
        	result.sale_date as `销售日期`,
        	(CASE result.role WHEN 1 THEN '商家'  WHEN 2 THEN '普通用户' END) as `角色`,
        	result.origin_max_sale as `修改前数值`,
        	result.update_max_sale as `修改后数值`,
        	sale.sale_num as `真实销量`,
        	result.or_qty as `OR值`,
        	(CASE 1 WHEN 1 THEN '降低多货'  END) as `降低|带来多货|提高销量`,
        	(result.update_max_sale - result.origin_max_sale) as `降低|带来多货|提高销量PCS`,
        	result.sku_price as `销售价`
        from
        	sale_log as result
        Left join
        	sale_num as sale
        on
      		result.sku_id = sale.sku_id
        	and result.poi_id = sale.poi_id
        	and replace(result.sale_date,'_','') = sale.sale_date
        where
        	result.sale_date between '$$begindate' and '$$enddate'
        	and result.update_status = 1
        	and result.update_type = 1
        	and sale.sale_num < result.update_max_sale
        Order by
        	result.operate_time
        Desc
        Limit
        	5000000
    )
    
    Union All
    
    (
        select
        	result.sku_id as `商品SKUID`,
            result.poi_id as `网店ID`,
        	sale.poi_name as `网店名称`,
        	result.sale_date as `销售日期`,
        	(CASE result.role WHEN 1 THEN '商家'  WHEN 2 THEN '普通用户' END) as `角色`,
        	result.origin_max_sale as `修改前数值`,
        	result.update_max_sale as `修改后数值`,
        	sale.sale_num as `真实销量`,
        	result.or_qty as `OR值`,
        	(CASE 1 WHEN 1 THEN '提高销量'  END) as `降低|带来多货|提高销量`,
        	(result.update_max_sale - result.origin_max_sale) as `降低|带来多货|提高销量PCS`,
        	result.sku_price as `销售价`
        from
        	sale_log as result
        Left join
        	sale_num as sale
        on
      		result.sku_id = sale.sku_id
        	and result.poi_id = sale.poi_id
        	and replace(result.sale_date,'_','') = sale.sale_date
        where
        	result.sale_date between '$$begindate' and '$$enddate'
        	and result.update_status = 0
        	and result.update_type = 2
        	and sale.sale_num <= result.origin_max_sale
        Order by
        	result.operate_time
        Desc
        Limit
        	5000000
    )
) as t
Inner join(
    select
    	snap.total_stock as total_stock, snap.base_sku_id as base_sku_id, snap.poi_id as 	  poi_id,snap.schedule_date as schedule_date
    from
    	sale_log as result
    Left join
    	schedule as snap
    on
    	result.sku_id = snap.base_sku_id
    	and result.poi_id = sanp.poi_id
    	and result.sale_date = snap.schedule_date
    	and snap.dt = replace(snap.schedule_date,'-','')
    where
    	result.sale_date between '$$begindate' and '$$enddate' 
) as m

On
	t.`商品SKUID` = m.base_sku_id
	and t.`网店ID` = m.poi_id
	and t.`销售日期` = m.schedule_date
where
	t.`网店ID` in($poiId)

注意

• ''引号 和 ``的区别
• order by和union一起使用，必须使用()包括查询语句
• 小表驱动

12. 慢查询检测

12.1 常用命令

1、查看最近执行的sql语句，读取了几页数据

SHOW STATUS LIKE 'last_query_cost';

• 两条sql语句，sql1（between and）从100条页中读取的数据，sql2从1000页中读取的数据，数据页查了一个数量级，但是查询耗时基本一样。原因就是顺序I/O

2、是否开启慢查询日志

SHOW variables LIKE '%slow_query_log';

默认是off的，因为开启ON时，可能会影响性能，当需要慢查询分析时，可以开启慢查询日志

3、开启慢查询日志

set global slow_query_log = on;

4、慢查询时间为10s

show variables like '%long_query_time%';

5、慢查询时间定义为0.1s

 set global long_query_time = 0.1;#全局
 set long_query_time= 0.1;#session级别
 2条语句都要执行，否则只执行global只会对新增的表查询生效

6、慢查询日志

名称：主机名-slow.log

查看：日志地址

show variables like '%slow_query_log_file';//opt/tmp/mysql.slow

7、慢查询定义

当一条sql的执行时间超过了设定的long_query_time时间 && 扫描的记录数（数据条数） > min_examined_row_limit

show variables like '%min_examined_row_limit'; // 默认为0,表示扫描过的最小记录数

这里加入将min_examined_row_limit值设置为10w，即使sql执行时间 > 慢查询定义0.1s，但是sql扫描的记录数不足10w，那这条sql也不算慢查询

12.2 mysqldumpslow工具

分析步骤

1、找到slow.sql文件地址

2、常用查询

具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S

-s: 是表示按照何种方式排序：

• c: 访问次数
• l: 锁定时间
• r: 返回记录
• t: 查询时间
• al:平均锁定时间
• ar:平均返回记录数
• at:平均查询时间 （默认方式）
• ac:平均查询次数

-t: 即为返回前面多少条的数据；

-g: 后边搭配一个正则匹配模式，大小写不敏感的

#得到返回记录集最多的10个SQL
mysqldumpslow -a -s r -t 10 /var/lib/mysql/slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -a -s c -t 10 地址

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -a -s t -t 10 -g "left join" 地址

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -a -s r -t 10 地址 | more

13. Explain

13.1 table

select a.id, b.name from a left join b on a.id = b.id where a.date >= '2024-01-23';

• table：查询语句中涉及几张表，explain语句就会生产几行。
  • 对于join操作，会生成多行explain解析结果
  • 第一行table关键字对应的表名是驱动表a；第二行table对应的表名是被驱动表b
• id：同一条查询语句中，id值大的先执行

13.2 Type：连接类型

all < index < rang < index_merge < ref < eq_ref < const < system

• const：使用主键或唯一索引，进行等值匹配

select * from table where id = 1;

• eq_ref

join查询，on对应的驱动表table2通过主键、唯一索引、或者二级索引的等值匹配

select * from table1 inner join table2 on table2.id = table1.id;//或table2.age = 18二级索引等值匹配

• ref

使用二级索引列，进行常量等值匹配查询

联合索引name_age

select * from table where name = 'm' and age = 10;

• index_merge

  索引1 name

  索引2 remark

select * from table where name = 'mjp' or remard = 'sorry';

如果or前后字段有不是索引的字段，则type直接变为all全表扫描

• rang

使用到了范围查找 > < between and like in or等

• index

联合索引name_address_age

select id,name,address,age from table where age = 18;//key使用了name_address_age

key：使用到了name_address_age联合索引，且查询的列被联合索引覆盖。

但没有通过此索引进行过滤数据，需要扫描此联合索引的全部数据，本质属于“全表”扫描

• all

全表扫描

联合索引name_address_age

select id,name,age,sex from table where age = 18;
等价
select * from table where age = 18;

全表扫描且查询返回大量的数据（几十万条数据），并不会把MySQL的内存打爆，但是会打爆Java内存

因为MySQL查询数据后把数据返回给客户端，流程如下

1）获取一行数据，写到net_buffer（默认16k，由net_buffer_length决定）中

2）再次获取一行数据，写到net_buffer中，当写满时，调用网络接口发送出去

3）发送成功后，清空net_buffer，然后重复1、2步骤

13.3 rows

理论上，rows值越小越好。表示此次查询总共扫描了多少行，是一个预估值

查询用到了唯一索引，rows = 1

其他二级索引，只要回主键索引取一次数据，系统就会对row = row + 1。

13.4 extra

• using index

  覆盖索引查询（ 使用到了二级覆盖索引查询，且索引内容覆盖了要获取的字段 ）

  • 联合索引name_age

  • select id, name, age from table where name = 'mjp';
    

  • 加上order by id则降为using where ；using index

• using index condition

  索引下推查询（参考上文-索引下推，减少回表次数）

  • 联合索引name_age

  • select * from table where name like '张%' and age = 10;
    

  • 加上order by id则降为using index condition；using filesort

• using where ；using index

  使用索引访问数据，并达到索引覆盖，但是 where 子句中有属于索引一部分(可以是联合索引第一个字段或第二个字段)但无法使用索引的条件（比如 like ‘%abc’ 左侧字符不确定）时：

  • 联合索引 name_age_address

  • select id, name, age from table where name like 'm%' and age > 10;
    select id, name, age from table where age = 10;
    select id, name, age from table where age > 10;
    

  • 加上order by id则降为using where

• using where

  场景1：使用全表扫描type = all

  联合索引 name_address_age

  select * from table where address = '铁岭';
  select * from table where address like '铁岭%';
  

  加上order by id仍为using where

  场景2：type = ref

  联合索引name_age

  select * from table where name = 'm' and age = 10 and valid = 1;
  

  查询条件中有除了索引包含的列之外的其它列查询

  思考：我们设计表的时，是否需要valid字段：1表示有效、0表示无效，用于逻辑删除。这样的话查询语句都要加上where xxx and valid = 1

• using filesort

  order by、group by时，无法使用到联合索引。只能在内存中排序

• using temporary

  场景1：order by、group by时，无法使用到联合索引。而且内存大小不够排序，需要io创建临时文件用于排序

  场景2：

  select distinct(name) from t where id = 1;
  

  name字段非索引字段，则需要使用临时表在内存中去重

性能分析

using index = null > using index condition > using index; using where > using where

order by| group by排序时：using filesort > using temporary。其他性能同上

13.5 filtered

1、单表查询

• 查询条件过滤数据的百分比越接近100越好。

• 结合rows一起理解。rows值为999表示预估扫描行数999，filtered = 100表示rows扫描的999行全部都是我们需要的。说明索引的过滤性很好

如果rows = 5000，filtered = 50，说明扫描了5000行，最终符合的数据只有2500行，说明查询条件的过滤性不好，白白的扫描了页中的数据

• 单表查询的时候，filtered的指标意义不大

2、join

filtered指标显示被驱动表要执行的次数

select * from t1 join t2 on t2.key = t1.key where t1.name = 'mjp';

• explain执行结果

table	rows	filtered
t1	9000	10
t2	1	100

• 外层驱动表t1，大概需要扫描的行数9000行，大概10%满足t1.name = ‘mjp’，也就是900条数据
• 内存被驱动表t2，rows = 1 && filtered = 100，则t2大概需要执行900次的t2.key = t1.key

13.6 key

真正使用到的索引

强制走索引语句

select * from t force index(idx_name) where xxx;

13.7 key_length

索引的长度

这个字段可以确认联合索引是否所有字段都被用上查询了，还是只是用到了最左部分。比如联合索引name_age_address是全部用上了，还是只用到了name（联合索引中的字段被使用的越多即key_length越大越好，这样能更好的使用联合索引进行数据过滤）

eg:

select * from t where name = 'mjp' and address = 'cn';

不满足最左前缀匹配原则，即使查询用到了name_age_address索引，但是从ken_length结果可以看到只用到了索引的最前部分name字段

总结：

eg: 联合索引a_b_c
where a = 'm' and b ='j' and c='p' 
优于 
where a = 'm' and b = 'j' //等效where a = 'm' and b like 'j%' and c = 'p';
优于 
where a = 'm' //等效 where a = 'm' and c = 'p';

计算规则如下：

• 字符串：varchar(n)，对应utf8mb4编码，则长度为4n+2字节，如果字段允许为null，则需要再+1字节（索引字段最好not null）
• 数值类型
  • tinyint：1
  • int：4
  • bigint：8

13.8 ref

结合type理解：当索引列使用等值查询时，ref内容即与索引列进行等值匹配的对象的信息

1、如果type类型是ref

select * from table where id = 1;

则ref是const，表示与索引列(这里是id主键索引)进行等值匹配的对象信息（这里与id进行等值匹配的对象是1，是个常量const）

2、场景2

联合索引name_address

select * from table where name = 'm' and address = '铁岭';

则ref指标会显示: const,const（与联合索引进行等值匹配的对象是2个常量）

3、场景3

select * from table1 inner join table2 on table2.id = table1.id;

这里type是eq_ref，ref是table1.id，表示与索引列table2.id进行等值查询的对象的信息为：table1.id

4、场景4

select * from table where id = abs(18);

ref: func，表示与索引列id，进行等值匹配的对象信息（是个函数）

13.9 格式

• 正常情况下的explain是普通的展示，还是使用explain FORMAT= JSON语句
• json格式的展示会多出2个指标
  • 本次查询读取的数据量大小 data_read_per_join
  • 成本cost
    • read_cost ：IO成本 + rows*(1-filtered)条记录的cpu成本
    • eval_cost: 检测rows * filtered条记录的成本

13.10 优化器优化后的语句

背景：

• where id in(1)，优化器帮我们会优化成where id = 1
• 驱动表和被驱动表的顺序，优化器也会帮我们优化
• 联合索引a_b_c，where a = x and c = x and b = x 优化为where a = x and b = x and c = x；

那么我们怎么查看真正执行的sql语句是啥样子的呢，步骤如下

• 步骤一：explain select * from t where id in(1)

• 步骤二: SHOW WARNINGS

  message中会有上述sql真正的执行内容

13.11 监控分析视图-sys schema

类似dump文件，一定不要在业务高峰期执行，收集信息时会影响性能，影响业务

• 索引相关

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
// eg：有了联合索引name_age，又创建了name单值索引属于冗余

#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;

#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;

• 表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;

# 2. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

• 语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;

#2. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

• 锁

#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;

13.12 mybatis自定义Explain插件

mybatis自定义Explain拦截

三、事务

1. 事务定义

事务就是要保证事物内的一组 操作单元（CRUD），要么全部成功，要么全部失败

2. 事务特性ACID

1、Atomicity：原子性-全部提交成功/全部失败回滚

用户A给用户B转 50块，要么就是转成功A-50、B+50，要么就是转失败，二者账户都不增不减。

2、Consistency：一致性-数据从一个合法状态 变换到另外一个合法状态

• 用户A有200块，现在给用户B转300块，200-300 = -100，显然-100不是一个合法状态，不满足一致性
• A给B转钱，A-的必须和B+的一样，总额不能变

3、Isolation：隔离性-隔离级别决定

4、Durability：持久性-一旦事物提交则修改会永久保存到数据库

• 通过事务日志（重做日志redo log和回滚日志undo log）来保障

总结：

A是基础、C是约束条件、I是手段、D是目的

3. 事务状态

• 部分提交：内存更新了但未刷盘
• 提交：持久化

4. 隔离性和隔离级别

事务并发时，各个事务之间不能互相干扰（类似于多线程并发操作共享数据，多线程可以通过加锁解决），并发事务可以通过隔离级别解决

总述如图2.17

4.1 读未提交

含义：一个事务还没提交时，它做的变更就能被别的事务看到

问题：读未提交级别下可能会有脏读、不可重复读、幻读。这里简单介绍下脏读：

读取到一条不存在的数据（读取到的数据，别人回滚了）

解决：读已提交

4.2 读已提交

含义：一个事务开始时，只能读到已经提交的事务所做的修改。即：一个事务从开始到提交，所做的任何修改，对其他事务都是不可见的

问题：可能会有不可重复读、幻读，这里简单介绍下不可重复读

两次执行相同的sql查询，可能得到不一样的结果，如图2.9

不可重复读的重点是修改!!!update操作

解决：可重复读

4.3 可重复读RR【mysql的隔离级别】

含义： 在一个事务中，从开始到结束的任意一瞬间读取到的数据应该都是一致的。

存在问题：幻读如图2.10幻读

• 事务A第一次查询db，没有id = 5的数据。
• 事务B，插入了一条id = 5的数据
• 事务A第二次查询db id = 5，发现有5这条数据了。像是幻觉一样

幻读的重点在于新增或者删除操作

解决：

• 可串行化-不推荐

• 快照读（普通读）中的幻读使用的MVCC解决的

• 当前读select for update，中的幻读采用next-key lock解决

  select * from table where id = 5 for update;
  假如id在1，3,6有值
  

  在可重读读隔离级别下，select for update会对数据加锁

  如果id = 5值不存在，则从record lock 降级为next-lock间隙锁，锁住范围(1,6)，这样就确保id=5无法插入，即图中步骤3无法执行。这样两次读取到的数据一样，不会出现幻读

4.4 可串行化【最安全】

含义：在读取的每一行数据都加锁。这样，就不会出现第一次和第二次读中间，事务B插入一条数据了。因为id = 5这行被加锁了

问题：大量的超时和锁竞争【几乎不用】

场景：除非非常需要数据一致性 且 没有并发

5. 事务使用SOP

5.1 不要使用长事务的原因

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

• 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库
• 场景

delete大量数据，一次会占满整个事务日志，锁住很多数据，会阻塞重要的查询。

建议在业务低峰期，批量且有时间间隔的删除1w、1w这种方式

5.2 事务的生效范围

	@Transactional(rollbackFor = Exception.class)
    public void main() {
        //1. insert A
        
        //2. insert B 、C
        try {
            insertBC();
        } catch (Exception e) {
            
        }
        //3.其他业务逻辑 
    }

    @Transactional(rollbackFor = Exception.class, propagation = Propagation.NESTED)
    public void insertBC() {
        // insert b
        //insert c
    }

• B和C任意一个失败，B、C都会一起失败
• B、C失败不会影响A的成功，因为try-catch住了
• 3中其他业务逻辑失败，会抛出Exception，会导致整个main方法回滚，则A、B、C都会一起失败回滚事务

注意点：

• main方法必须加上@事务注解，否则会因为在方法中a中调用方法b，aop会失效。
• try-catch要加上，吃掉异常

5.3 采坑记录

5.3.1 本地写 + rpc写

1、结论

本地写事务和rpc写操作（最好）不要放在一个事物中

2、原因

因为若本地写事务成功了，rpc写操作ReadTimeout执行失败（实际上此rpc写已经在远程成功了），会导致，本地写操作回滚。

造成，本地未写，远程写成功了

3、特殊

• 非强一致性

如果本地写和rpc写不要求那么强的一致性，而且rpc写失败了又可以重拾+幂等。那么允许放在一起

• Job触发，有重试机制

在保证幂等的情况下，job执行失败会有重试。同样能达到重试的机制

4、解决

• 背景：就要本地写+rpc写，而且要保证事务一致性
• 方案：最大努力重试

@Transaction{
    // 1、本地写
    // 2、使用最大努力重试机制，保证rpc或者多个rpc一定成功
     @最大努力重试{ 
        rpc1
        rpc2
     }
}

5.3.2 @Transactional使用注意事项

1、踩坑-Methods should not call same-class methods with incompatible “@Transactional” values问题

方法和调用事务的方法，在同一个类中

• 问题描述

  同一个类中无事务方法a()内部调用有事务方法b()，b方法上的事务不会生效

  public class Demo{
      public void funcA(){
  		funcB();
  	}
  
  	@Transactional(rollbackFor = Exception.class)
  	public void funcB() {
  	}
  }
  
  

• 解决

  • 方式1：不推荐

  ((类名) AopContext.currentProxy()).funcB()
  
  @Transactional(rollbackFor = Exception.class)
  public void processBill() {
    
  }
  

  • 方式2：推荐

  public class Demo{
  	@Transactional(rollbackFor = Exception.class)
      public void funcA(){
  		funcB();
  	}
  
  	@Transactional(rollbackFor = Exception.class)
  	public void funcB() {
  	}
  }
  
  

3、其他@Transactional不生效的场景

• @Transactional 只能被应用到public方法上，否则事务不会生效（AOP原理）
• 在具体的类（或类的方法）上使用 @Transactional 注解，而不要使用在类所要实现的任何接口上

5.3.3 多数据源问题

• 问题描述

  NoUniqueBeanDefinitionException: No qualifying bean of type ‘org.springframework.transaction.PlatformTransactionManager’ available: expected single matching bean but found 2: default

• 原因

  多数据源的时，某一数据源未配置事务name，导致事务失效

  第一个数据源，事务默认名称为default，第二个数据源事务名称为sale_smart

  当第一个数据源，使用事务，但是，没有指定事务处理器，就会报这个错误。

  因为每个数据源都有自己的事务配置，单纯地用@Transactional 没法确定是哪个事务处理

• 解决

在多数据源配置文件中，指定name

数据源1 transactionName = sale_smart
数据源2 transactionName = other

在使用注解时，指定对应的数据源名称

@Transactional(value= "sale_smart", rollbackFor = Exception.class)
事务1方法
    
@Transactional(value= "other", rollbackFor = Exception.class)
事务2方法

6. 事务日志

6.1 一个事务中2条更新语句的执行过程

update table set name = 'mjp' where id = 2;
update table set name = 'wxx' where id = 3;

如图2.22

1）将id = 2数据从磁盘中读取到内存，放入data_buffer中

• 准备更新内存数据前，先将id=2的旧值，写入undo log中便于回滚

• 将data_buffer中的id = 2数据进行值修改

• 将id=2的更改动作实时记录到内存的redo log buffer中

2）将id = 3的更新语句执行步骤1

3）当这2条更新语句对应的事务提交commit时！，将redo log buffer中的更新记录会按照一定的策略写到磁盘中的redo log file中

• 再进行半异步方式-主从复制（bin log 和 中继日志，参考后文主从复制过程）

• 主从复制中继日志写成功后，发动ACK，主库确认ACk后返回客户端事务处理成功

4）再将内存data_buffer中的更新结果以一定的时间间隔频率刷盘data中

6.2 redo log

InnoDB引擎特有

内容	物理日志，记录的是“在某个数据页上做了什么DML操作”
作用	保障事务的持久性。即如何将更新的数据从内存中可靠的刷到磁盘中
区别	一个事务中10条更新语句，redo log是不间断的顺序记录的
特点	循环写，空间固定会用完，会覆盖。固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB从头开始写，写到末尾就又回到开头循环写。write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件（因为随着内存data中数据刷盘，完成刷盘部分的数据就没必要再存在于redo log中了，就可以被覆盖了）。

循环写过程，如图2.42

其中checkpoint -> write pos范围内的数据，是内存data_buffer还未完成刷盘的数据，redo log必须要存好

write pos -> checkpoint范围内的数据，是内存data_buffer已完成刷盘的数据，redo log就没必要存储了。这部分就可以被循环写

6.2.1 组成

• redo log buffer
  • 保存在内存中，默认大小16M，又可以分为一个一个512kb的redo log block
• redo log file
  • 保存在磁盘中的，是持久化的
  • 在文件：ib_logfile0 和 ib_logfile1中，单个默认大小48M

6.2.2 redo log刷盘操作

含义

• 事务提交时，将内存中的redo log buffer的更新记录以一定的频率写到磁盘中的redo log file中
• 这个动作执行成功才能真正的保证事务的持久性，即使是服务器宕机了，重启后也能从redo log file中读取到磁盘data中
• 如果事务没提交，系统挂了，内存中的redo log buffer的更新记录没了，也没关系。因为事务本身也没提交，仍是事务的最初状态。一致性也没改变

过程

1、先将内存中的redo log buffe刷到文件系统缓存page cacahe中

2、至于什么时候将page cache中内容写入redo log file中，完全交于操作系统自己决定

3、Innodb提供了参数innodb_flush_log_at_trx_commit，可以调整redo log刷盘的策略

• 设置为1（ 默认值 ） ：表示每次事务提交时，都会立即将redo log buffer内容记录到page cache中，然后再立即将page cache中数据写到入redo log file。实时性很好！可靠性高！效率性差！
• 设置为0 ：表示每次事务提交时不直接进行刷盘操作（值1是事务提交时会立即触发）
• Innodb后台线程，每间隔1s，不管你有没有事务提交commit动作，都会执行redo log buffer --> page cache --> 然后调用fysnc刷到 redo log file
• 设置为2 ：表示每次事务提交时会立即把 redo log buffer 内容写入 page cache。由os自己决定什么时候从page cache --> redo log file
• page cache是os系统级别的，所以事务提交后， page cache写成功后，MySQL数据库服务器宕机了，没关系。重启MySQL数据库后可以从系统os的 page cache中读取数据
• 但是如果os操作系统挂了，那就无法刷盘了
• 所以，如果os系统基本不可能宕机的情况下，想优化事务comit时间（3w个事务提交动作，默认1花费的时间是值2的3倍大概），可以采取值为2的方式

6.2.3 两阶段提交（最终版）

提示：两阶段提交过程，是写redo log、写bin log与事务commit提交动作，先后顺序关系的最终解释版

1、背景

• redo log是边更新边写入的。bin log是事务提交时，一把写入的
• 当事务提交时，假如先刷盘的redo log成功了，然后服务器宕机了bin log刷盘失败了，则重启服务器时：
  • master会根据redo log进行事务持久性恢复，恢复到更新后的值
  • slaver需要根据bin log进行主从同步，但是bin log没有本次更细的记录
  • 导致主从数据不一致了

2、解决：redo log两阶段提交

两个阶段如图2.50

3、两阶段如何解决上述主从数据不一致问题

如图2.51

重启服务器时：

• 因为写bin log失败了，所以还没走到redo log的commit阶段
• 判断是否存在bin log，显然不存在，则回滚事务
• 此时redo log也回滚了，bin log也没写入，主从的数据还是一致的

四、锁

1. 数据操作类型

1.1 共享锁

1、定义

对于同一份数据，多个事务之间可以并发读取，相互之间不阻塞

2、加锁

select * from t whrer id = 1 lock in share mode;

即事务1为上述sql、事务2也为上述sql，二者都加的共享锁，则读-读之间互不影响

3、生效范围

表、行

1.2 排他锁

1、定义

当前写操作没有完成之间，会阻塞其他的写、读操作。

确保只有一个事务可以写，其它事务不能写，并且其它用户不能读取正在写的同一资源

2、加锁

• 这里演示的是对读加排他锁（所以共享锁不能简单概括为读锁，因为读也可以加排他锁）

select * from t whrer id = 1 for update;

即事务1为上述sql，事务2也为上述sql或事务2为select * from t whrer id = 1 lock in share mode。事务1未提交之前，事务2都会被阻塞

• 对写，加排他锁
  • delete、update：直接加排他锁
  • insert：正常情况下插入操作，并不加排他锁，而是通过隐式锁保驾护航，确保插入的数据未提交之前，不能被其他事务访问（隐式锁类似于懒加载，只有别的事务企图来共享|排他访问新增但还未提交的这条数据时，才会加上隐式锁）

3、生效范围

表、行

2. 数据操作粒度

2.1 表锁

2.1.1 优缺点

优点

• 资源开销小，无需大量的获取、检查、释放锁
• 不会产生死锁

缺点

• 并发度差

行锁优缺点相反

2.1.2 自增锁（Auto Icr）

1、定义

当我们主键id auto increment时，而且我们batchInsert时或insert的内容是select的结果时，我们不知道要插入多少条数据，这个时候，一个事务持有表级锁-自增锁时，其他事务无法执行插入操作

2、特殊

我们普通的mybatis的单条数据的insert，知道要插入数据的条数。

只需使用metux轻量级锁，在分配id值期间保持即可，无需像上述批量插入，需要保持自增锁到插入语句结束为止

2.1.3 元数据锁（MDL）

• 场景

CRUD时，对表加MDL读锁；Alter table时，加MDL写锁（更改字段属性、表会锁全表）。读-写、写-读、写-写都是阻塞的

• 总结

Alter table操作导致的MDL-写锁，必须要在业务低峰期进行，否则可能阻塞CRUD-MDL读锁

原因如图2.8

• session A先启动，这时候会对表t加一个MDL读锁【语句结束后并不会马上释放】。由于session B需要的也是MDL读锁，因此可以正常执行
• session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞
• 之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。等于这个表现在完全不可读写了
• 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。
• 故Alter操作一定要在业务低峰期，防止大量的读被阻塞

2.2 行锁

2.2.1 快照读和当前读

• 快照读即普通读，不加锁，读取的快照数据

select * from table where id = 5;

• 当前读，读取最新的数据，需要加锁，读取的不是快照

select * from table where id = 5 for update;

如果查询条件字段不是索引字段，会降级为表锁

2.2.2 加行锁SOP

2.2.3 影响并发度的锁放在事务最后

1、背景

顾客A要在影院B购买电影票

• 从顾客A账户余额中扣除电影票价
• 给影院B的账户余额增加这张电影票价
• 记录一条交易日志。

2、解析

• 这个交易，需要update两条记录，并insert一条记录

• 为了保证交易的原子性，把这三个操作放在一个事务中

• 如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

3、建议

• 把语句2安排在最后，按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少
• 最大程度地减少了事务之间的锁等待，提升了并发度

4、总结

如果你的事务中需要锁多个行，在不影响业务的情况下，把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

2.2.4 行锁变表锁场景

一旦某个加锁操作没有使用到索引，则该锁就会退化为表锁

2.2.5 Record lock：记录锁

场景1：排他锁-读

SELECT * FROM table WHERE id = 5 FOR UPDATE;//锁住id = 5单行

• 如果这里的where查询的字段是唯一键，则加锁的效果等同主键
• updateByPrimaryKey，也会对id = 匹配这行，仅对这一行加锁
• =匹配在没数据时，也会降为间隙锁
  • id = 1、3、6,执行 WHERE id = 5 FOR UPDATE的查询，此时会降级为间隙锁，锁住(1,6)，所以我们需要偶尔执行一下alter table A engine=InnoDB，防止自增主键因为插入冲突、事回滚导致的有过多的空隙不连续问题
• 如果where条件不是=匹配，而是> < like等，会变成临键锁
• 上述语句未提交时，再执行共享锁-读时，也会被阻塞（当然再执行排他锁，更是阻塞）

select * from table where id = 5 share in mode;

场景2：排他锁-写

update table set name = 'mjp' WHERE id = 5 ;//锁住id = 5单行

• 此时记录锁锁住了id = 5的这一行
• update未提交时，再执行共享锁-读时，也会被阻塞（当然再执行排他锁，更是阻塞）

select * from table where id = 5 share in mode;

2.2.6 Gap lock：间隙锁

• 锁定一个范围()，是基于非唯一索引和非主键索引的，即二级索引
• age是普通索引时，where age = 5，则锁定(1,5)不包含行本身

SELECT * FROM table WHERE age = 5 FOR UPDATE;
SELECT * FROM table WHERE id >1 and id < 10 FOR UPDATE;

• 间隙锁锁分析
  • 在(1，10）区间内的记录行都会被锁住，所有id为 2、3、4、5、6、7、8、9 的数据行的插入(排他锁-写)会被阻塞（排他锁-读for update、共享锁-读均是），但是 1 和 10 两条记录行并不会被锁住。

补充

1、当前读情况下，可以通过加此锁，解决可重复的读时的幻读问题

2、间隙锁，不区分共享间隙锁和排他间隙锁，即以下两种查询会加间隙锁，锁住(1,10)

SELECT * FROM table WHERE age = 5 FOR UPDATE;
SELECT * FROM table WHERE age = 5 share in mode;

3、 如果是between 1 and 10，则会锁住[1,10]。同理如果是id >=1 and id <= 10，也是锁定[1,10]

2.2.7 next-key lock：临键锁

• 本质：Record lock + Gap lock

• 每个数据行上的，非唯一索引字段列上，都会存在一把临键锁

  如图2.11

• 假设age为索引字段，则改表中age列上潜在的临键锁锁定的范围有

(0，10]

(10,24]

(24,32]

(32,45]

(45,无穷]

则

SELECT * FROM table WHERE age = 24 FOR UPDATE;

锁定的范围是：左gap lock + record lock+ 右gap lock =》 (10,24) + 24 + (24,32) =>(10,32)

• 作用：解决事务隔离级别为可重复读时，当前读（for update）可能存在的幻读问题

  举例：事务A在写数据id=24时，会先查db有无id=24这条数据，有则不插入。无则插入

  • 事务A第一次查询select where id = 24 for update，间隙锁会锁住(10,32)
  • 事务B，打算插入了一条id = 24的数据，发现(0,32)范围被锁住了，阻塞
  • 事务A第二次查询db id = 24，因为可重复读，所以，第二次读取的结果和第一次一样（没有出现幻读）
  • 事务A按照自己的逻辑，认为可以插入id=24的一条数据

2.2.8加锁2原则2优化1bug

原则1：

加锁的基本单位是next-key lock ,锁定范围( ]

原则2：

查找过程中访问到的对象才会加锁

• 如果age不是索引字段，则查询where age = 5，仅访问age=5这一条记录是不能马上停下来的，需要向右遍历，查到age=6才放弃（因为age是二级索引不是唯一索引，所以需要再往下继续查找，直到不满足查询条件为止）
• 根据原则2，访问到的都要加锁，故where age = 5的查询也会对age=6这行数据加锁(1,6]
• 聚簇索引即主键where id=5，就可以立即停下来(前提是id=5这一行有数据，唯一索引字段查询同理)，不会访问id=6

优化1：

索引上的等值查询（where id = 5），给唯一索引加锁的时候（id字段），next-key lock退化为record lock，只锁id=5这一行的数据(前提是id=5这一行有数据)。

优化2：

索引上的等值=查询（where age = 5为二级索引），向右遍历时且最后一个值不满足等值条件的时候（age = 6），next-key lock退化为间隙锁Gap lock，即锁定范围不是(1,6]，还是间隙锁(1,6)

一个“bug”

唯一索引上的范围查询（id=7这一行无数据）会访问到不满足条件的第一个值为止。

CREATE TABLE table (
  `id` int(11) NOT NULL, 
  `c` int(11) DEFAULT NULL, 
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`), 
  KEY `c` (`c`) 
) ENGINE=InnoDB; 

insert into table values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、等值查询从record lock降级为间隙锁场景

SELECT * FROM table WHERE id = 7 FOR UPDATE;//没有id=7的数据

• 原则1，加锁范围(5,7]
• 优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)。
• 所以，session B要往这个间隙里面插入id=8的记录会被锁住，但是session C修改id=10这行是可以的。

2、据此再分析图2.11的sql

SELECT * FROM table WHERE age = 24 FOR UPDATE;

• 原则1临键锁（0，24]
• 优化2:这是一个等值查询(age=24)，会向右查找，直到找到第一个不满足的age = 32，此时临键锁退化为间隙锁（24，32] -> (24,32)
• 锁定范围是（0，32）

2.3 乐观悲观锁

2.3.1 悲观锁

认为在更细数据时，总有别的线程会更新我们这份数据，所以会加锁，如表锁、行锁、Java锁关键字

2.3.2 乐观锁

不加锁，通过代码层面如CAS，version版本号的方式，解决潜在的资源可能别修改问题。适用于读操作多的场景

其中版本号操作如下：

• 先查询出来数据的版本号，version = 10
• 此时对这条数据进行更新操作：update t set name = ‘mjp’, version = version +1 where version = 10
• 如果此期间没有别的事务对这条数据进行update，则这条语句能更新成功。如果别的事务更新了这条语句，n那么version值变为11了，则此条更新语句不会成功
• 注意：使用version版本号乐观锁的前提是：主从没延时，否则读到的version可能不是最新的。所以，读取的时候一定要走主

2.4 死锁

2.4.1 产生死锁的条件

进程A和进程B举例

• 互斥

在一段时间内，资源被A占用。若此时B来请求此资源，那么B只能等待

• 不可剥夺

A获到的资源在其未使用结束前，B不能强行夺走此资源。B只能等待A主动释放

• 请求与保持

B提出资源获取请求，但是此资源正在被A占有，此时B被阻塞

B被阻塞的过程中，B占有的资源也保持持有，不释放

即阻塞申请资源的同时，也不释放自己占有的资源

• 循环等待

A持有a资源，B持有b资源，A访问b资源，B访问a资源。形成一个循环等待

2.4.2 避免死锁的方式

即避免产生死锁的四个条件

• 避免循环：加锁的时候，可以先对对象排序，防止循环
• 避免阻塞：获取资源的时候，不要一直阻塞获取，尽量使用tryLock(timeout)，一段时间内没获取到，则不再获取，这样自己占有的资源也可以被释放。

2.4.3 发生死锁时的策略

当发生死锁时，会主动检测，将死锁链条中持有最少行排他锁的事务进行回滚。这样便打破了死锁产生的条件，让其他事务得以继续执行

2.4.4 死锁实战

间隙锁导致死锁

• 背景：Mybatis的upsert方法

  插入数据时，本质为：

insert into xxx on duplicate key update;

• 原因：假设code字段是唯一索引

1）初始化数据

 insert into table (code, other) values(1,1),(3,3),(5,5);

2）事务1

 insert into table (code, other) values(3,3) on duplicate key update

由于（3，3）这条数据已经存在，所以会在(1,3)范围内加间隙锁

3）事务2

insert into table (code, other) values(5,5) on duplicate key update

由于（5，5）这条数据已经存在，所以会在(3,5）范围内加间隙锁

4）产生死锁

• 如果此时事务1要插入一条code=4的数据，就需要等待事务2释放(3,5）的锁
• 如果事务2要插入一条code=2的数据，就需要等待事务1释放(1,3）的锁
• 锁住期间，其他事务不能向此范围内插入数据，此时会形成死锁

总结过程如图2.44

5）建议

在并发事务执行的insert语句中，最好不要使用，可能会造成死锁

如果要用，那么inset的batch大小要小一些，这样单个事务获取的next-key范围就会变小，减少死锁发生的概率

锁商品时遇到的死锁

• 背景

下单操作，需要锁定订单汇总多个商品的库存（手机、鞋子、衣服）

• 步骤

先拿到所有商品的锁

依次进行扣减库存

释放锁

• 注意

为了避免死锁，可以在对商品加锁的时候，可以通过对购物车中的商品先进行排序来实现顺序的加锁。这样就能有效避免产生死锁的循环等待这一条件

• 实现

public void createOrder(List orderList) {
        //定义存放锁的集合
        List lockList = Lists.newArrayList();
        
        // 1、对所有购物车中待下单的商品进行排序
        Collections.sort(orderList);
        
        // 2、对排序好的商品进行依次加锁
        try {
            for (Object order : orderList) {
                if (order.lock.tryLock(10, TimeUnit.SECONDS)) {
                    //加锁成功
                    lockList.add(order.lock);
                }
            }

            //3、依次扣减库存
            orderList.forEach(order -> order.remaining--);
        } catch (Exception e) {
            
        } finally {
            lockList.forEach(ReentrantLock::unlock);
        }
    }

3. 锁结构

3.1 产生一条锁结构

• 同一个事务中进行加锁 + 被加锁的记录在同一页 + 加的相同类型的锁 + 等待状态是一样的。则为这个事务生成一个锁结果
• 否则一个事务中1000个更新语句，生成1000个锁结构，性能太差了

3.2 锁结构

锁结构如图2.45

1、锁所在的事务信息

不论是表锁还是行锁，哪个事务生成了这个锁结构 ，这里就记录这个事务的id。

2、 索引信息

对于行锁来说，需要记录一下加锁的记录是属于哪个索引的

3、表锁信息

对哪个表加的锁

4、行锁信息

• Space ID ：记录所在表空间
• Page Number ：记录所在页号。

5、type_mode

32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三部分，如图2.46

1）锁的模式（ lock_mode ）

• 占用低4位
• 值如下
  • LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁
  • LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁
  • LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁
  • LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁
  • LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。

2）锁的类型（ lock_type ）

• 占用第5～8位
• LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁
• LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁

3）行锁的具体类型（ rec_lock_type ）

• LOCK_ORDINARY （十进制的 0 ）：表示 next-key临建锁
• LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap间隙锁
• LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示record lock记录锁
• LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向锁
• is_waiting
  • type_mode 这个32 位的数字中，当第9个比特位置为 1 时，表示 is_waiting = true ，即当前事务尚未获取到锁，处在等待状态
  • 当这个比特位为 0 时，表示 is_waiting = false ，即当前事务获取锁成功。

6、一堆比特位

• 如果是行锁结构 的话，在该结构末尾还放置了一堆比特位
• 作用：因为上述事务中的多个更新在一个页中，用于记录这个页中的哪些数据被加了行锁，哪些数据没有加锁

4. 锁监控

4.1 查看行锁的竞争信息

show status like 'innodb_row_lock%';

• Innodb_row_lock_waits ：系统启动后到现在总共等待的次数
• Innodb_row_lock_time_avg ：每次等待所花平均时间

如果这两个参数值比较大，即等待次数较多，且等待的时间avg较大，则有可能是事务语句可能有问题

4.2 查询锁信息

• 查询正在被锁阻塞的sql语句

SELECT * FROM information_schema.INNODB_LOCKS;

指标1：trx_query：等待锁的sql语句

指标2：trx_rows_locked：锁定的行数

• 查询目前处于等待锁的事务

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

指标1： REQUESTING_ENGINE_TRANSACTION_ID: 13845 #被阻塞的事务ID

指标2：BLOCKING_ENGINE_TRANSACTION_ID: 13844 #正在执行的事务ID，阻塞了13845

5. MVCC多版本并发控制

5.1 作用

• 在数据库并发场景中，只有读-读之间的操作才可以并发执行，读-写**，写-读，**写-写操作都要阻塞，这样就会导致 MySQL 的并发性能极差。
• 采用了 MVCC 机制后，只有写写之间相互阻塞，其他三种（读读、写-读、读-写）操作都可以并行（读使用Mvcc写加锁），这样就可以提高了 MySQL 的并发性能。
• 多事务并发时，MySQL通过 隐藏字段组成的undo log版本链 + undo log + ReadView解决各种问题

5.2 组成

5.2.1 隐藏字段

每行数据有两个隐藏的字段trx_id、roll_pointer

• trx_id

  就是最近一次更新这条数据的事务id，它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

• roll_pointer，指向了你更新这个事务之前生成的链。隐藏字段组成的undo log版本链如图2.12

5.2.2 uodo log回滚日志

1、概念

• 更新数据时，先将数据从磁盘读取到内存的data_buffer中
• 更新data_buffer中对应的数据之前，会先将旧值拷贝到undo log里（insert之前记录主键id便于delete、delete之前需要记录全部要被删除的内容，便于后续insert）
• 和redo log一样是Innodb的，并且undo log的产生过程，也需要redo log保护它持久性用于数据可恢复

2、特点

是逻辑上的回滚，不是物流上的回滚

• 加入insert了一条语句，开辟了新的页，然后插入了一条数据id=100
• 事务回滚了，并不能物理上将开辟的页再收回，因为可能别的事务也insert了语句，也放到了这一页中，只能执行delete语句，将id = 100的数据再删除
• eg：用户A转账了100给用户B，事务需要回滚，并不是物理上，让A和B都回到未转账之前的状态，事情发生了就无法倒流，是能通过逻辑上的弥补，即用户B再转账100给用户A，达到回到最初的状态。
• 物理操作：发生的事情，再让它不发生，即类似时光可以倒流
• 逻辑操作：发生的事情就只能让它发生了，唯一能做的就是想办法弥补，让状态回到事情未发生时候的样子（吵架了，伤害已经实打实的发生了，无法倒流，只能通过好好沟通弥补感情回到之前的状态0.o）

3、作用

• 事务回滚，保证事务原子性和一致性，可以用undo log的数据进行恢复
• Mvcc，当用户读取一行记录时，若该记录被其他事务占用，则该事务可以通过undo log读取之前的行版本信息，以此实现非锁定读取（即读-写的读是不加锁的读）。

4、存储结构

• 类似redo log的block块，undo log是段，即回滚段rollback segement。每个回滚段中包含1024个undo log segement（这里面有undo页，一个undo log页中可能有多个事务，用于记录待恢复的内容）
• 并发度，Innodb支持128个段（可通过参数innodb_undo_logs设置），每个段1024个undo log segement，所以支持事务的并发度为：128*1024

5、回滚段rollback segement中数据类型分类

• 未来提交的

随时可能作为回滚的恢复内容，所以不能被其他事务的数据覆盖

• 已提交

  • 未过期

  mvcc作用：可能有其他事务需要undo log内容来读取之前的行版本信息。所以不能被立即删除，由purge线程来决定什么时候删除

  • 已过期

  超过了undo retention参数指定的时间，属于过期数据，当回滚段满了之后，会优先覆盖这部分的数据

6、undo 类型

• insert

commit时，可直接删除

• update

  • 事务在进行 update 或 delete 时产生的 undo log， 不仅在事务回滚时需要（作用1），在Mvcc机制中（作用2）也需要。所以不能随便删除，等待 purge 线程统一清除。

  • 所以，delete语句不要有大事务，会长时间的占满整个事务日志

    长时间：不能及时的删除日志

    占满：undo log需要记录待删除的数据全部内容，内容很多

7、一条语句的更新过程（redo + undo log）

如何2.43（基本和图2.22一致）

5.2.3 ReadView一致性视图

定义

帮我们解决行的可见性问题。分析如图2.13

• m_ids：表示在生成ReadView时当前系统中 还没有提交的事务id列表
• min_trx_id：m_ids中的最小值
• max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。（当前系统里面已经创建过的事务ID最大值加1）
• creator_trx_id：表示生成该ReadView的事务的事务id（读操作事务id=0，更新操作才会分配事务id）

事务能读取到哪条数据

规则1

当前事务内的更新，能读到

即被访问的trx_id = 视图中的creator_trx_id，说明是当前事务（生成视图的事务）内的更新，能读到

规则2

其他事务已提交，且在视图创建前提交，能读到

• 即被访问的trx_id(其他事务) < 视图中的（当前事务）min_trx_id，表明被访问的事务，在生成该视图之前就提交了(因为不是活跃事务了)，所以被访问的事务可以被当前事务读到
• 对应图中的绿色部分

规则3

其他事务已提交，但在视图创建后提交，不能读到

• 即被访问的trx_id(其他事务) >= 视图中的（当前事务）max_trx_id，表明被访问的事务，在生成该视图之后才开启的，所以被访问的事务不能被当前事务读到

• 对应图中的红色部分

规则4

被访问的事务min_trx_id =< trx_id < max_trx_id

• trx_id ∈m_ids集合中，说明是活跃的事务，还未提交，不可被读取

• 不在m_ids集合中，说明不是活跃的事务，说明已提交，可被读取

5.3 作用于事务隔离级别

5.3.1 MVCC保证可重复读

有两个事务并发过来执行，事务A(id=45），事务B（id=59），事务A要去读取这行数据，事务B要去修改这行数据，事务A开启一个ReadView如图2.14

事务A第一次查询（快照读即普通读非当前读）这行数据时，如图2.15

• 判断被访问的这行数据的trx_id是否小于ReadView中的min_trx_id

  • 此时这行数据的trx_id = 32小于事务A的ReadView里min_trx_id=45
  • 根据规则2：能读到trx_id = 32的这行数据

• 事务B开始修改这行数据

  • 事务B把值修改为B，然后这行数据的trx_id设置为自己的id=59，同时roll_pointer指向了修改之前生成的版本链 log，如图2.16
    

• 事务A第二次查询

  • 被访问的事务trx_id=59，min =<trx_id < max

    且trx_id ∈m_ids[45,59]，根据规则4，读取不到

• 此时事务A会根据隐藏字段roll pointer顺着undo log版本链查询之前的版本，于是就会查到trx_id=32的数据，trx_id=32是小于ReadView里min_trx_id=45的，可以查到值仍为A

• 事务A第一次读 和 第二次读，值都为A，即可重复读。即通过MVCC实现MySQL的可重复读（快照读情况下）事务隔离级别

5.3.2 MVCC解决幻读

• MVCC可以解决快照读下的幻读
  • 只不过可重复读，是普通读的id = 1匹配查询；幻读是普通读的范围查询id >= 1
  • 原理分析同上，可重复读的隔离级别下，视图使用同一个，所以插入数据前后两次id >=1查询结果个数一样
• MVCC无法解决当前读下的幻读，当前读for update是通过加间隙锁，解决幻读。参考

5.3.3 可重复读、读已提交区别

它们生成ReadView的时机不同

• 在可重复读隔离级别下，一个事务在执行过程中，只有第一次执行select时会生成一个视图，之后的select都会复用这个视图；
• 在读提交隔离级别下，每一个select语句执行前都会重新算出一个新的视图。

五、主从复制、数据库备份与恢复

1. bin log

Server层所有引擎都可以使用

1.1 内容

• binary log二进制日志。记录了所有的DDL、DML事件
• 一个事件由begin和commit组成，使用使用pos记录事件的开始和结束位置
• 可以使用mysqlbinlog -v "/bin log完整路径"查看二进制内容，内容是逻辑日志即伪sql

伪sql内容如图2.52

1.2 作用

主从复制、数据恢复

Redo和bin在数据恢复上的不同点

1. Redo log：
   • 记录数据修改操作（DML）
   • 当数据库发生崩溃或意外关闭时，通过Redo log可以将数据库恢复到最近的一次提交点。
   • 保证事务的持久性和原子性，确保了在事务提交之后所做的修改操作可以被恢复。
2. Bin log：
   • 记录所有的数据库更新操作，包括对数据的增删改以及数据库结构的修改。（DDL+DML）
   • 用于数据恢复、主从同步。
   • 在数据恢复方面，Bin log可以用于恢复到指定的时间点或指定的事务。

1.3 特点

• 不同于redo log边更新，边写。bin log直到整个事务提交，才会一次性将10条更新语句写入
• 不同于redo log的覆盖写，bin log是追加写，写到一定大小后（默认1G可伸缩，防止大事务无法放在同一个文件中）会切换到下一个，并不会覆盖以前的日志。

1.4 写入机制

同redo log一样，也不是直接刷盘。而是采用默认策略（sync_binlog = 0）：

如图2.49

• 更新操作先写入内存bin log cache（类似redo log buffer）
• 每次事务提交，都只写入文件系统缓存page cache
• 最后os决定什么时候，刷盘到bin log文件中
• 不怕MySQL服务器宕机，因为写到文件系统page cache了，但是怕os操作系统宕机，因为事务提交了，page cache内容却丢了

2. 数据恢复

因为所有DDL、DML操作事件都有记录（备份）在bin log中。所有可以据此进行数据恢复

执行步骤：

1、flush logs：生成一个新的bin log2专门记录本次数据恢复事件动作，不要影响原有的正常bin log1

2、方式一：通过读取bin log1中的pos位置，来恢复[pos,pos]之间的事件

方式二：通过读取bin log1中的指定世间段内的事件

找打对应事件的pos或时间，准备恢复

3、恢复读取到的内容

• 方式一

/usr/bin/mysqlbinlog --start-position=1  --stop-position=100  --database = 你的db名 /bilog的全路径 | /usr/bin/mysql -uroot -p密码 -v 你的db名;

• 方式二

--start-datetime="2024-01-01 12:00:01"  --stop-datetime="2024-01-02 12:00:01"

3. 主从复制

作用：读写分离、数据备份、高可用性。

1、事务提交后，redo log刷盘完成后，主库把更新操作先记录到bin log中，状态为Prepare

2、从库将主库上的bin log复制到自己的中继日志relay log中，如图2.23

• 从库启一个I/O线程，去和主库建立客户端连接
• 主库启一个binary down 线程，该线程会读取主库上的bin log中的更新操作事件，将其发送给从库
• 如果binary down线程读取bin log中事件，追上了主库写入bin log中的事件，则其会进入sleep状态。直到主库bin log中又写入新的事件
• 从库I/O线程会将接收到的bin log事件，写入到中继日志relay log中

3、从库会读取其中继日志relay log内容，重放执行sql，存于从库数据中

如图2.24

• 从库中的sql线程（coordinator线程），会从中继日志relay log中读取事件和分发事务。worker线程去执行在从库中回放，实现从库数据的更新
  • 分发事务时，更新同一行的事务，会放在一个worker线程中
  • 同一个事务不会被拆开，会放在一个worker线程中
• sql线程支持并行的回放中继日志relay log中的事件

3.1 水平分表

1、散列hash取模

• hash算法取模

• 可解决数据热点问题，但是无法扩容和数据迁移

  一旦扩容 %值变了，导致% 值结果也变了，原本6%2 =0,去table0查询数据，现在6%4 = 2需要去table2查询数据，查不到数据

• shardingsphere实战

根据order_id取模，order_id%2
= 0，插入表1
= 1，插入表2

2、Range增量

• 按照年月日拆分
• 便于扩容，但是有数据热点问题（热点查询都集中在某一张表中）

3、group分组（hash+range）

1）hash解决热点数据

如图2.39，3个db、10张table

eg：

• 插入id = 12
• 先确定db：id % 10 = 2，落在了[0,1,2,3]区间内，即db0
• 再确定table: id∈[0,1000w]，所以在db0的table0中

2）range解决不易扩容

如图2.40

• Group2的解决热点原理同group1，扩容原理
• id -> group(0-4000w是1，4000-8000w是2)
• id % 10确认db
• id大小∈确认table

3）组-库-表结构

如图2.41

3.2 垂直分表

按照业务分（订单、库存等）

4. 主从延时

4.1 表象

• 主库写入bin log时刻为T1
• 从库读取完bin log时刻为T2
• 从库sql线程完全回放完成sql时刻为T3
• 假如T3-T1=100ms（即从库的seconds_behind_master值），用户insert后立即select假如间隔50ms。这个时候就有可能查不到最新的数据

4.2 产生场景

• 网络波动
• 大事务！
  • delete大量数据（必须业务低峰期）
  • 一次性insert太多数据（强制只能200批量插入）
  • 大表的DDL如新增字段

4.3 解决

• T2 - T1的时间几乎可以忽略
• 现在就是如何让T3 - T2的时间更短
  • mysql做的：sql线程支持并行的回放中继日志relay log中的事件
  • 我们做的：避免大事务
• 兜底：写完立马读的业务场景，走主
  • 写完数据，发送MQ，消费者会立即读新增的数据
  • 页面修改动作结束，立即触发查询（也可以让前端针对这种更细操作，更新后延时200ms再查询）

4.4 从根本上解决数据一致性

1）异步复制

主commit后，不 care从库的复制过程和复制结果，直接返回客户端成功。

• 主库写效率高
• 数据一致性最弱

2）半异步复制

过程如前文图2.22

• 必须等从库Ack之后，再返回给客户端成功（有一个从库Ack即可）

3）组复制

MySQL5.7.17版本基于Paxos协议推出的MGR复制方案

• 简单理解就是，一半以上的从Ack了，即可返回客户端成功

5. 主从切换

1、停止向master中写入

2、让slave追上master

3、找到可以成为mater的slaver

• 确认当前哪个slaver的数据最新
• 通过在slaver中选择master_log_file/reader_master_log_pos值最新的slaver成为master

4、其他slaver指向新的master，开启新的master写

其他slaver如何指向master，指向的reader_master_log_pos的值如何确定，如图2.48

• 老master的insert对应的pos = 1582，Server2的reader_master_log_pos值最新，被选为新master
• Server3继续为slaver，其pos = 1493，距离新master的1582差89
• 新master的insert值为8167，所以Server3指向新master时change_master_to_master_host = “Server2”，Pos = 8167 - 89 = 8078
• 这样就完成了新master的确认，以及slaver指向新master

5、通过pos，slaver确认指向master的pos

• Server3（slaver）在一个特定的偏移位置pos = 8078连接到新master（Server2）。一个给定的二进制未见汇总，master再从给定的连接点8078开始发送所有的事件
• Server3需要告诉Server2从哪个偏移量Pos再次开启增量同步。如果指定位置错误的话，会造成事件的遗漏，数据的丢失

6. 数据库集群|db|表备份

参考附件

六、参数和命令

• show index from sellout_warn_sku;

  查看索引的区分度，一个索引上不同的值的个数我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

  “采样统计”。

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

  而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

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

  • 设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
  • 设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

• force index(idx_a)

  如果使用索引a，每次从索引a上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。直接在主键索引上扫描的，没有额外的代价。

  优化器会估算这两个选择的代价，如果优化器认为直接扫描主键索引更快，就不会走索引a。当然，从执行时间看来，这个选择并不是最优的。所以，可以强制其走索引a

• alter table A engine=InnoDB

  重建表，主键索引更紧凑，数据页的利用率也更高。这个方案在重建表的过程中，允许对表A做增删改操作

• SET max_length_for_sort_data = 16;

  如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

• long_query_time = 0

  捕获所有的查询。几乎没有额外的IO开销

  分析工具：pt-query-digest

• show table status like ‘表名’ : 可以查看表的基本信息

• innodb_change_buffer_max_size ： 值表示占用buffer_size整体大小的百分比。更新频繁的表此值可以设置的大一点，减少读盘次数

• SHOW variables LIKE ‘log_err%’ 查询错误日志的地址，默认是开启的

• sql_safe_updates ：执行update和delete语句必须要有where条件

写在最后：

本文主要参考了书籍《高性能MySQL》、 林晓斌老师 的《MySQL实战45讲》、尚硅谷康师傅的《MySQL高级教程》以及网上资料并结合自己工作经验总结而出。理解有误的地方，欢迎批评指正，感谢！

​ 2024-01-31 22:00:00 mjp