Mysql之总结篇-CSDN博客

索引的数据结构

层层递进理解最终的B+树：二叉树->红黑树->B树->B+树

二叉树

单边增长的倾斜问题，变相为链表，查找效率低，与全表扫描差别不大

RBTree-红黑树

大数据量情况下，高度不可控

B Tree B树-平衡多路查找树

所有节点存储：索引+数据
特点：
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列

B+Tree 索引

MySQL索引没有用B Tree，而是用B+Tree——B树的变种，有比B树更高的查询效率

其对B树进行改造，数据全部存叶子节点：

非叶子节点不存储data，从叶子节点取中间某些索引进行冗余存储，可以放更多的索引
叶子节点包含所有索引字段，且数据索引从左到右递增排列
叶子节点用双向指针连接，提高区间访问的性能，可变相理解是双向链表

索引的升级–树平衡过程

当节点插满数据之后，中间节点（即最小的一个元素）作为冗余索引上升到上一节点，在此基础上做树的平衡

千万级数据表如何用B+树快速查找

查找时比较费时的步骤是load节点到内存，而在内存查找数据是比较快的
对于MySQL来说，每个（页）节点（root）分配的空间是16kb，高度为3的B+树整体可存储两千多万数据：
非叶子页节点可存储的数据量：
- 16kb/（索引数据（一般占据8字节）+指针（6个字节）），约等于1170
叶子节点可存储的数据量：（有data，所以比非叶子节点少）
- 16kb/（索引+指针+数据）1kb（一般一行数据不会超过1kb）=16
- 整体可存储数据个数（高度为3）：1170 * 1170 * 16 两千多万条记录
因此，高度为3的B+树中，查找某一个元素，只经过3次磁盘IO即可找到

B树与B+树区别

B+树叶子节点之间多了双向指针，方便区间范围查找
B+树非叶子节点只存储索引，这样一个节点能存放更多索引，存放相同数量的索引，B+树的高度会更小

为什么MySQL选择B+树做索引？

树的高度，影响索引查找效率
B+树将data放到叶子节点，而非叶子节点只存储索引，存放同样数量的索引，其高度更小，查找效率更高

hash索引

对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+ 树索引更高效
仅能满足 “=”，“IN”，不支持范围查询
hash冲突问题

聚集索引&聚簇索引&稀疏索引

聚集索引也叫聚簇索引：叶节点包含完整的数据记录
非聚簇索引：索引文件和数据文件是分离的
稀疏索引：非聚簇索引的一种

为什么InnoDB表推荐使用整型的自增主键

为什么InnoDB表必须建立主键？

如果不建立主键
- 会从第一列开始，选择所有元素都不相等的一列作为索引，来组织B+树
- 如果选不到所有元素都不相同的列，则会创建隐藏列作为索引，组织B+树
- 浪费MySQL资源和性能

为什么推荐使用整型自增主键做索引？

整型：
- 在索引定位的过程中，中间经历过多次数据比较大小，整型查找过程中“值比较”效率高，如果是字符串会按ASCII码，逐个字符比较，又经历多次，效率低
- 整型数据占用的磁盘空间小，节约空间
自增：新增主键时一般不会导致叶子节点频繁分裂以及平衡，效率高

联合索引

一般不建议单表建立过多单值索引，而是建立2-3个联合索引来满足查询需要
分类
- 联合主键索引
- 联合非主键索引（不常用）
查询一行数据，当单列和索引和联合索引发生冲突时，MySQL优先选择单列索引

优势/好处:

当两个或多个列的组合是唯一值时，联合索引是个不错的选择
可以对后续多个键值进行排序
减少开销：每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销
效率高：索引列越多，通过索引筛选出的数据越少

explain关键列详解

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询达到range级别，最好达到ref
const：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）
- 用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快
system：是const的特例，表里只有一条数据匹配时为system
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录，这可能是在const 之外最好的联接类型，简单的 select 查询不会出现这种 type
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行：
range：走索引的范围查询/扫描，范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
index：扫描全索引就能拿到结果，一般是扫描某个二级索引。
- 这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的
- 这种查询一般未使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点，通常情况下这需要增加索引来进行优化

possible_keys列

这一列显示查询可能使用哪些索引来查找
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问
如果没有使用索引，则该列是 NULL
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
key_len计算规则如下：
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
- char(n)：如果存汉字长度就是 3n 字节
- varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
时间类型
- date：3字节
- timestamp：4字节
- datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

Extra列

Using index：使用覆盖索引
- 覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index
覆盖索引查找效率也很高：覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要回表查询
Using where：不能使用索引或者使用索引后仍需扫描全表或者索引树判断条件的情况
Using index condition：查询的列不完全被索引覆盖，查询条件不完全使用索引，比如第一个条件是符合最左前缀，第二个是模糊匹配或范围查询
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
- actor.name没有索引，此时创建了张临时表来distinct
```
explain select distinct name from actor;
```
- film.name建立了idx_name索引，此时查询时extra是using index，没有用临时表
```
explain select distinct name from film;
```
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化
- actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
```
explain select * from actor order by name;
```
- film.name建立了idx_name索引，此时查询时extra是using index
```
explain select * from film order by name;
```
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时

1、Using index举例：

首先，在"订单表"上，这里是一个多列复合索引 create index idx_userid_order_id_createdate on test_order (user_id,order_id,create_date);

查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index

2、Using index Using where举例：

查询的列被索引覆盖，数据都是先通过索引查询出来的索引覆盖数据，然后经过条件过滤得到最终数据；储存引擎内过滤，也算提高效率

explain select user_id,order_id,create_date from test_order where user_id =1 and name='test';

3、Using where Using index举例：

a、查询的列被索引覆盖，并且where筛选条件是索引列之一但不是索引的前导列（最左匹配），Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据

b、查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据

4、Using index condition 举例：

a、查询的列不完全被索引覆盖，where筛选条件完全可以使用到索引（进行索引查找）

b、查询的列不完全被索引覆盖，where筛选条件中是一个前导列的范围

另外：

1、查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where

2、查询的列被索引覆盖时，会使用索引。（若未覆盖就不会走索引，原因还是直接在索引树直接可以查到数据）

SQL优化大全

查询优化

1.联合索引第一个字段用范围不一定会走索引，mysql会根据内部预估情况，第一个字段用范围，结果集应该很大，回表效率不高，还不如就全表扫描

EXPLAIN SELECT * FROM employees WHERE name > 'aa' AND age = 22 AND position ='manager';

2.联合索引强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'aa' AND age = 22 AND position ='manager';

虽然使用强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但最终查找效率不一定比全表扫描高，因回表效率不高

3.覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

4.in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

5.like KK% 一般情况都会走索引

索引下推

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，因为innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以索引下推并不会起到减少查询全行数据的效果
为什么范围查找Mysql没有用索引下推优化？
- 应该是Mysql认为范围查找过滤的结果集过大
- like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这不绝对，有时like KK% 也不一定就会走索引下推

trace工具用法

set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 开启trace 
select * from employees where name > 'a' order by position;
select * from information_schema.OPTIMIZER_TRACE; 
set session optimizer_trace="enabled=off";    --关闭trace

排序优化

1.MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2.order by满足两种情况会使用Using index。

order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。

3.尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4.如果order by的条件不在索引列上，就会产生Using filesort。

5.能用覆盖索引尽量用覆盖索引

6.group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理

单路排序

一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者

< sort_key, packed_additional_fields >

双路排序（又叫回表排序模式）

首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

排序模式选择

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模·式。

案例分析

select * from employees where name = 'codemonkey' order by position;

单路排序的详细过程

从索引name找到第一个满足 name = ‘codemonkey’ 条件的主键 id
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
从索引name找到下一个满足 name = ‘codemonkey’ 条件的主键 id
重复步骤 2、3 直到不满足 name = ‘codemonkey’
对 sort_buffer 中的数据按照字段 position 进行排序
返回结果给客户端

双路排序的详细过程：

从索引 name 找到第一个满足 name = ‘codemonkey’ 的主键id
根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
从索引 name 取下一个满足 name = ‘codemonkey’ 记录的主键 id
重复 3、4 直到不满足 name = ‘codemonkey’
对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

对比两个排序模式

对比两个排序模式：
- 单路排序会把所有需要查询的字段都放到 sort buffer 中
- 而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段
根据过程和对比，可了解MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率
- 如果 MySQL 排序内存 sort_buffer配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据
- 如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果

注意

如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整
排序模式和sort_buffer默认值调整验证，只是为了加深理解，除非熟读MySQL源码的专业DBA，不建议调整

索引设计实战

以社交场景APP来举例
- 一般会搜索一些好友，这里面就涉及到对用户信息的筛选，也就是对用户user表搜索，这个表一般来说数据量会比较大（先不考虑分库分表的情况）
- 比如，一般会筛选地区(省市)，性别，年龄，身高，爱好之类信息，有的APP可能用户还有评分，比如用户的受欢迎程度评分，可能还会根据评分来排序等等
对于后台程序来说除了过滤用户的各种条件，还需要分页之类的处理，可能会生成类似sql语句执行：
```
select xx from user where xx=xx and xx=xx order by xx limit xx,xx
```
对于这种情况如何合理设计索引？
- 比如用户可能经常会根据省市优先筛选同城的用户，还有根据性别去筛选
- 那我们是否应该设计一个联合索引(province,city,sex) ？
  - 这些字段好像基数都不大，其实是应该的，因为这些字段查询太频繁
- 假设又有用户根据年龄范围去筛选
  - 比如 where province=xx and city=xx and age>=xx and age<=xx
  - 尝试着把age字段加入联合索引 (province,city,sex,age)
  - 注意，一般这种范围查找的条件都要放在最后，之前讲过联合索引范围之后条件的是不能用索引的，但是对于当前这种情况依然用不到age这个索引字段，因为用户没有筛选sex字段
- 那怎么优化？其实可以这么来优化下sql的写法：
```
where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx
```
  对于爱好之类的字段也可以类似sex字段处理，所以可以把爱好字段也加入索引 (province,city,sex,hobby,age)
  
  假设可能还有一个筛选条件，比如要筛选最近一周登录过的用户，一般大家肯定希望跟活跃用户交友，这样能尽快收到反馈，对应后台sql可能是这样：
```
where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx and latest_login_time>= xx
```
  那我们是否能把 latest_login_time 字段也加入索引？比如 (province,city,sex,hobby,age,latest_login_time)，显然是不行的，那怎么来优化这种情况？
  
  其实可以试着再设计一个字段is_login_in_latest_7_days，用户如果一周内有登录值就为1，否则为0
  
  那么就可以把索引设计成 (province,city,sex,hobby,is_login_in_latest_7_days,age) 来满足上面那种场景！
  
  一般来说，通过这么一个多字段的索引是能够过滤掉绝大部分数据的，就保留小部分数据下来基于磁盘文件进行order by语句的排序，最后基于limit进行分页，那么一般性能还是比较高的，不过有时可能用户会这么来查询，就查下受欢迎度较高的女性，比如sql：
  
  where sex = ‘female’ order by score limit xx,xx
  
  那么上面那个索引是很难用上的，不能把太多的字段以及太多的值都用 in 语句拼接到sql里，那怎么办？其实可以再设计一个辅助的联合索引，比如(sex,score)，这样就能满足查询要求

分页查询优化

根据自增且连续的主键排序的分页查询

优化前：

select * from employees limit 90000,5;

优化后：

select * from employees where id > 90000 limit 5;

这种改写得满足以下条件：

主键自增且连续
结果是按照主键排序的

还可以这样优化，这样虽然也load了数据，但是由于索引覆盖，所以速度会很快

select * from employees where id > (select id from employees limit 90000,1) limit 5

根据非主键字段排序的分页查询

优化前：

select * from employees ORDER BY name limit 90000,5;  # name存在索引，但不会走二级索引，因为数据量太大，还需要回表，所以还不如直接全表扫描

优化后：

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id; # 会走二级索引

Join关联查询优化

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 插入一些示例数据
-- 往t1表插入1万行记录
drop procedure if exists insert_t1; 
delimiter ;;
create procedure insert_t1()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=10000)do                 
    insert into t1(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t1();

-- 往t2表插入100行记录
drop procedure if exists insert_t2; 
delimiter ;;
create procedure insert_t2()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100)do                 
    insert into t2(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t2();

常见两种算法

Nested-Loop Join 算法
Block Nested-Loop Join 算法

Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集，如下:
```
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
```
驱动表是 t2，被驱动表是 t1
- 先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；
- 优化器一般会优先选择小表做驱动表，用where条件过滤完驱动表，然后再跟被驱动表做关联查询
- 所以使用 inner join 时，排在前面的表并不一定就是驱动表
- 当使用left join时，左表是驱动表，右表是被驱动表
- 当使用right join时，右表是驱动表，左表是被驱动表
- 当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
- sql使用了NLJ算法
- 一般 join 语句中，如果执行计划 Extra 中未出现Using join buffer，则表示使用的 join 算法是 NLJ
上面sql的大致流程如下：
- 从表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；
- 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
- 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
- 重复上面 3 步。
- 整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值
根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)，因此整个过程扫描了 200 行
如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有解释)，mysql会选择Block Nested-Loop Join算法

Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比,如下:
```
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
```
从执行计划的Extra 中看到Using join buffer (Block Nested Loop),说明该关联查询使用的是 BNL 算法
上面sql的大致流程如下：
- 把 t2 的所有满足条件的数据放入到 join_buffer 中
- 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
- 返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100，并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次
例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？·
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k
如果放不下表 t2 的所有数据话，策略很简单，就是分段放
- 比如 t2 表有1000行记录，join_buffer 一次只能放800行数据
- 那么执行过程就是先往join_buffer 里放800行记录，
- 然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果,
- 然后清空join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比
- 所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法
如果有索引一般选择 NLJ 算法，因为有索引的情况下 NLJ 算法比 BNL算法性能更高

关联查询sql的优化

关联字段加索引:
- 让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引
小表驱动大表:
- 写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释：
straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序
- 比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
straight_join只适用于inner join，并不适用于left join，right join，因为left join，right join代表已指定表的执行顺序
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的
使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱

in和exsits优化

原则：

小表驱动大表，即小的数据集驱动大的数据集

in：

当A表的数据集大于B表的数据集时，in优于exists

select * from A where id in (select id from B)  
#等价于：
for(select id from B){
	select * from A where A.id = B.id
}

exists：

当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
    for(select * from A){
      select * from B where B.id = A.id
    }
    
#A表与B表的ID字段应建立索引

count(*)查询优化

字段有索引：
- count(*)≈count(1)>count(字段)>count(主键 id)
- 字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段) >count(主键 id)
字段无索引：
- count(*)≈count(1)>count(主键 id)>count(字段)
- 字段没有索引 count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点
count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(1)来替代 count(*)

数据库字段类型

在MySQL数据类型设置方面

尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源
并且，尽量把字段定义为NOT NULL，避免使用NULL。

1、数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128, 127)	(0, 255)	小整数值
SMALLINT	2 字节	(-32 768, 32 767)	(0, 65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608, 8 388 607)	(0, 16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808, 9 223 372 036 854 775 807)	(0, 18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38, 1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0, (1.175 494 351 E-38, 3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

优化建议

如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
建议使用TINYINT代替ENUM、BITENUM、SET。
避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

2、日期和时间

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01 到 9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’ 到 ‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901 到 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00 到 2038-01-19 03:14:07	YYYYMMDDhhmmss	混合日期和时间值，时间戳

优化建议

MySQL能存储的最小时间粒度为秒。
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
TIMESTAMP是UTC时间戳，与时区相关。
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

3、字符串

类型	大小	用途
CHAR	0-255字节	定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。
VARCHAR	0-65535 字节	变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

优化建议

字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
BLOB和TEXT都不能有默认值。

4、INT显示宽度

常会使用命令来创建数据表，且同时会指定一个长度(命令如下)
但是，这里的长度并非是TINYINT类型存储的最大长度，而是显示的最大长度
```
CREATE TABLE `user`( `id` TINYINT(2) UNSIGNED );
```
上面命令里表示user表的id字段的类型是TINYINT，可以存储的最大数值是255
所以在存储数据时:
- 如果存入值小于等于255，如200，虽然超过2位，但是没有超出TINYINT类型长度，可以正常保存；
- 如果存入值大于255，如500，那么MySQL会自动保存为TINYINT类型的最大值255
在查询数据时，不管查询结果为何值，都按实际输出
- 这里TINYINT(2)中2的作用就是：当需要在查询结果前填充0时，命令中加上ZEROFILL就可以实现，如：
- ```
`id` TINYINT(2) UNSIGNED ZEROFILL
```
这样，查询结果如果是5，那输出就是05
- 如果指定TINYINT(5)，那输出就是00005，其实实际存储的值还是5，而且存储的数据不会超过255，只是MySQL输出数据时在前面填充了0
- 换句话说，在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有用，让查询结果前填充0

事务隔离级别

概述

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题
这些问题的本质都是数据库的多事务并发问题
为了解决多事务并发问题，数据库设计事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性：
- 原子性(Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行，通过undo log实现
- 一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，通过锁 + undo log 实现
- 隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然，通过MVCC机制实现
- 持久性(Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持，通过redo log + binlog 实现

并发事务处理带来的问题

更新丢失(Lost Update)或脏写
- 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题
- 一句话：最后的更新覆盖了由其他事务所做的更新
脏读（Dirty Reads）
- 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”
- 一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
不可重读（Non-Repeatable Reads）
- 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”
- 一句话：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读（Phantom Reads）
- 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”
- 一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别

“脏读”、“不可重复读”和“幻读”，都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决
- 读未提交：脏读”、“不可重复读”和“幻读”都可能
- 读已提交：脏读”不可能，“不可重复读”和“幻读”都可能
- 可重复读：脏读”和“不可重复读”不可能，“幻读”可能
- 可串行化：脏读”、“不可重复读”和“幻读”都不可能

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化（查询也加锁）	不可能	不可能	不可能

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的
不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对 “不可重复读" 和 “幻读” 并不敏感，可能更关心数据并发访问的能力
常用操作/命令：
- 常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;
- 设置事务隔离级别：set tx_isolation=‘REPEATABLE-READ’;
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

大事务的影响

连接池容易被撑爆
锁定太多的数据，导致大量的阻塞和超时
容易发生死锁
执行时间长，容易造成主从复制延迟
undolog日志膨胀、回滚时间所需要的时间也长

事务优化建议

事务中避免远程调用，若要调用请设置超时机制，防止事务等待时间太久
事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
更新等涉及加锁的操作尽可能放在事务靠后的位置
能异步处理的尽量异步处理
如果能通过业务保证数据一致性，可以非事务方式执行，例如trycatch，有问题自己回滚

原则：尽量避免大事务，减少事务内执行时间

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制
在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突是影响数据库并发访问性能的一个重要因素

锁分类

从性能上分为乐观锁(用版本对比来实现) 和悲观锁
从对数据库操作的类型分，分为读锁和写锁 (都属于悲观锁)
- 读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
- 写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁、间隙锁、临键锁

表锁

每次操作锁住整张表
- 开销小，加锁快；
- 不会出现死锁；
- 锁定粒度大，发生锁冲突的概率最高，并发度最低；
- 一般用在整表数据迁移的场景
读锁：
```
# 加读锁 
lock table [表名] read
```
- 当前session和其他session都可以读该表
- 当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
写锁：当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
```
# 加写锁
lock table [表名] write
```
对MyISAM表的读操作 (加读锁) , 不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作
对MylSAM表的写操作(加写锁)，会阻塞其他进程对同一表的读写操作，只有当写锁释放后，才会执行其它进程的读写操作
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁

行锁

每次操作锁住一行数据
- 开销大，加锁慢；
- 会出现死锁；
- 锁定粒度最小，发生锁冲突的概率最低，并发度最高
InnoDB与MYISAM的最大不同有两点：
- InnoDB支持事务（TRANSACTION）
- InnoDB支持行级锁
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙
Mysql默认级别是repeatable-read，间隙锁在某些情况下可以解决幻读问题
假设account表里数据如下:

那么间隙就有 id 为 (3,10]，(10,20]，(20,正无穷) 这三个区间，在Session_1下面执行 update account set name = ‘zhuge’ where id > 8 and id <18;
则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙 里插入或修改任何数据
首先8~18之间的id值肯定都是加了行锁，此外因为9在第一个间隙区间，17在第二个间隙区间，因此id在(3,20]区间都无法修改数据
间隙锁是在可重复读隔离级别下才会生效

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合
- 像上面例子里的(3,20]的整个区间可以叫做临键锁
  
  https://www.cnblogs.com/tracydzf/p/16875302.html
无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
- session1 执行：update account set balance = 800 where name = ‘lilei’;
- session2 对该表任一行操作都会阻塞住
- InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)

# 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
select * from test_innodb_lock where a = 2 for update;

在这里插入图片描述

死锁

set tx_isolation=‘repeatable-read’;
Session_1执行：select * from account where id=1 for update;
- Session_2执行：select * from account where id=2 for update;
- Session_1执行：select * from account where id=2 for update;
- Session_2执行：select * from account where id=1 for update;
查看近期死锁日志信息：show engine innodb status\G;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁、临键锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离

MVCC多版本并发控制机制

同样的SQL查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务SQL语句的查询结果。

MySQL是如何做到这一点的？这个隔离性主要是依靠MVCC机制来保证的，在读已提交和可重复读这两个隔离级别下，它们的隔离性都是通过MVCC机制来实现的

而串行化也可以保证隔离性（高），但是是通过加锁互斥来实现的！有时候连select也要加上读锁！性能是相当的低，所以99%是不会去使用它的！

我们最常用的还是可重复读这个隔离级别！

MVCC机制是通过 undo日志 和 read view机制来实现的，所以我们先来看看这两个家伙。

undo日志版本链

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，MySQL会保留修改前的数据undo回滚日志，并且用两个隐藏字段 trx_id 和 roll_pointer 把这些undo日志串联起来形成一个历史记录版本链。

如果row的trx_id落在绿色部分(trx < min_id)，表示这个版本是已提交的事务生成的，这个数据是可见的
如果row的trx_id落在红色部分(trx > max_id)，表示这个版本是由将来启动的(未开始)事务生成的，是不可见的 (若row的trx_id就是当前自己的事务是可见的)
如果 row 的 trx_id 落在黄色部分(min_id <= trx_id <= max_id)，那就包括两种情况
- 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的不可见 (若 row 的 trx_id 就是当前自己的事务是可见的)
- 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的可见

读一致性视图read-view

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view
该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)
该视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成
事务里任何sql的查询结果需要从对应Undo日志版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果

Innodb的BufferPool缓存机制

update语句执行：客户端执行->连接器（->查询缓存）->分析器->优化器->执行器->调用引擎接口->存储引擎取数据
加载磁盘文件中所查记录的整页数据到bufferpool
1. 将更新数据的旧值写入undo日志，便于回滚
2. 更新buffer pool内存数据
3. 写redo日志至redo log buffer
4. 准备提交事务，将redo日志写入磁盘（innodb引擎特有）
5. 准备提交事务，将binlog日志写入磁盘（server层）
6. 将commit标记写入redo日志文件，提交事务完成，该标记为保证事务提交后，redo与binlog数据一致
7. IO线程读取buffer pool中修改的数据，将其随机写入磁盘，以page为单位写入，完成数磁盘里数据的修改

buffer pool

数据库的增删改查都直接操作buffer pool
bufferpool一般设置为机器内存的60%
缓存淘汰机制主要使用LRU算法
1. 3/8的list信息是作为old list，这些信息是被驱逐的对象。
2. list的中点就是我们所谓的old list头部和new list尾部的连接点，相当于一个界限
3. 新数据的读入首先会插入到old list的头部，
4. 如果是old list的数据被访问到了，这个页信息就会变成new list，变成young page，就会将数据页信息移动到new sublist的头部。
5. 在数据库的buffer pool里面，不管是new sublist还是old sublist的数据如果不会被访问到，最后都会被移动到list的尾部作为牺牲者

undo日志文件

如果事务提交失败需要回滚数据，可以用undo日志中数据来恢复buffer pool里的缓存数据(顺序写，效率高)

redo日志文件

如果事务提交成功，buffer pool中数据未来得及写入磁盘，此时系统宕机，可以用redo日志中数据来恢复buffer pool里的缓存数据(顺序写，效率高)
通过参数可以配置刷盘机制
- 0-每次事务提交，仅保存在buffer
- 1-每次事务提交，直接调用 fsync 函数刷盘到磁盘文件（最安全）
- 2-每隔1s调用 os writer 函数刷新 buffer 数据到 PageCache，然后调用 fsync 函数刷盘到磁盘文件

binlog日志文件

主要用来恢复数据库磁盘中的数据以及主从同步(顺序写，效率高)
通过参数可以配置刷盘机制
- 0-每次事务提交，都调用 os writer 函数把数据写到 PageCache，由操作系统自行判断刷盘时机
- 1-每次事务提交，直接调用 fsync 函数刷盘到磁盘文件（最安全）
- N-每次事务提交，都调用 os writer 函数把数据写到 PageCache，积累N个事务后刷盘到磁盘文件

为什么设置这么一套复杂的机制？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差
因为磁盘随机读写的性能非常差，所以直接更新磁盘文件是不能让数据库抗住很高并发
Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性
更新内存的性能极高，然后顺序写磁盘上的日志文件的性能也非常高，要远高于随机读写磁盘文件
正是通过这套机制，才能让MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求

慢查询：https://blog.csdn.net/qq_40884473/article/details/89455740

mysql内部两阶段提交（内部XA）

这是为了让redo log 和 binlog 两份日志之间的逻辑一致，mysql会采用两阶段提交。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。仍然用前面的 update 语句update T set c=c+1 where ID=2来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

场景1：假设在 redo log 写完，binlog 还没有写完的时候

由于我们前面说过的，redolog 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

场景2：先写 binlog 后写 redo log

如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

思考题： binlog 写完，redo log 还没 commit前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？

我们先来看一下崩溃恢复时的判断规则。

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

a. 如果是，则提交事务；

b. 否则，回滚事务。

所以，binlog 写完，redo log 还没 commit前发生 crash，对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。

追问：redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log，如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

追问：redo log buffer 是什么？是先修改内存，还是先写 redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin; 1
insert into t1 ... 2
insert into t2 ... 3
commit

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。

所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit语句的时候做的。

binLog

binlog归档

删库是不需要跑路的，因为开启binlog后，SQL执行时，会将sql语句的执行逻辑记录在binlog中
binlog定义：是Server层实现的二进制日志，它会记录我们的cud操作
binlog特点：
- Binlog在MySQL的Server层实现（引擎共用）
- Binlog为逻辑日志，记录的是一条语句的原始逻辑
- Binlog不限大小，追加写入，不会覆盖以前的日志
如果误删了数据库，可以使用binlog进行归档
要使用binlog归档，需要先开启MySQL的binlog功能

binlog配置–my.cnf

#配置开启binlog
log-bin=/usr/local/mysql/data/binlog/mysql-bin
#注意5.7以及更高版本需要配置本项：server-id=123454（自定义,保证唯一性）;
#binlog格式，有3种statement,row,mixed
binlog-format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1

statement
- 记录语句——结果产生的过程，而非结果
- 记录数据小，同步快，但在执行涉及非确定性函数、触发器和存储过程等操作时，可能会导致不一致的结果。
- 不支持 RU、RC 隔离级别；
- Statement 模式适用于大多数情况下的数据库复制需求。
  
  例如：一次更新大量数据，如二十万数据。反之，在复制时，从库可能会追得太慢，然后导致延时；
row
- 记录结果——语句执行所影响的结果
- 记录数据大，但安全性好
- 很多工具都是解析row格式的binlog文件，然后做后续处理。比如：阿里开源的binlog解析工具canal
mixed：上面两者的结合

常见binlog命令

# 查看bin-log是否开启
show variables like '%log_bin%'; 
# 会多一个最新的bin-log日志
flush logs;
# 查看最后一个bin-log日志的相关信息
show master status;
# 清空所有的bin-log日志
reset master; 
# 查看binlog内容
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001

binlog里的内容不具备可读性，所以需要自行去判断恢复的逻辑点位
如何观察判断呢？看重点信息。比如begin、commit这种关键词信息
只要在binlog当中看到，就可以理解为begin-commit之间的信息是一个完整的事务逻辑
然后再根据位置position判断恢复即可

数据归档操作—从bin-log恢复数据命令

# 恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p testdb(数据库名)

# 恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731"  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p testdb(数据库)

# 恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00"  --start-date= "2019-03-02 11:55:00"|mysql -uroot -p testdb(数据库)

数据归档测试–删除和恢复

1、定义一个存储过程，写入数据

drop procedure if exists tproc;
delimiter $$
create procedure tproc(i int)
begin
    declare s int default 1;
    declare c char(50) default repeat('a',50);
    while s<=i do
        start transaction;
        insert into test values(null,c);
        commit;
        set s=s+1;
    end while;
end$$
delimiter ;

2、删除数据

truncate test;

3、利用binlog归档/恢复数据

 /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p dbtest(数据库名)

4、归档完毕，数据恢复

注意：如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

备份数据库一般可以用mysqldump 命令工具

mysqldump ‐u root 数据库名>备份文件名; #备份整个数据库
mysqldump ‐u root 数据库名 表名字>备份文件名; #备份整个表
mysql ‐u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

Mysql全局调优参数

max_connections=3000 最大连接数，一个连接最少占用内存是256K，最大是64M
max_user_connections=2980 允许用户连接的最大数量，剩余连接数用作DBA管理。
back_log=300 如果MySQL的连接数达到max_connections时，新的请求将会被存在堆栈中，等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将被拒绝
wait_timeout=300 指的是app应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时
interactive_timeout=300 指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时
sort_buffer_size=4M 排序内存，连接专用
join_buffer_size=4M 表关联内存，连接专用
innodb_thread_concurrency=64 此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能
innodb_buffer_pool_size=40G innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。

Mysql 8.0重要新特性

新增降序索引

MySQL在语法上很早就已经支持降序索引，但实际上创建的仍然是升序索引。8.0之后，创建时指定是降序便是降序索引，此外，只有Innodb存储引擎支持降序索引。

create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。

增加隐藏索引

使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见，但是数据库后台还是会维护隐藏索引的，在查询时优化器不使用该索引，即使用force index，优化器也不会使用该索引，同时优化器也不会报索引不存在的错误，因为索引仍然真实存在，必要时，也可以把隐藏索引快速恢复成可见。注意，主键不能设置为 invisible。软删除就可以使用隐藏索引，比如我们觉得某个索引没用了，删除后发现这个索引在某些时候还是有用的，于是又得把这个索引加回来，如果表数据量很大的话，这种操作耗费时间是很多的，成本很高，这时，我们可以将索引先设置为隐藏索引，等到真的确认索引没用了再删除。

create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);

新增函数索引

之前我们知道，如果在查询中加入了函数，索引不生效，所以MySQL 8引入了函数索引，MySQL 8.0.13开始支持在索引中使用函数(表达式)的值。

函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。

create index func_idx on 表名((UPPER(字段名)));

Innodb存储引擎select for update跳过锁等待

对于select … for share(8.0新增加查询共享锁的语法)或 select … for update，在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定。

在5.7及之前的版本，select…for update，如果获取不到锁，会一直等待，直到 innodb_lock_wait_timeout超时。

在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。应用场景比如查询余票记录，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能

新增innodb_dedicated_server自适应参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题，前提是服务器是专用来给MySQL数据库的，如果还有其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序

死锁检查控制

MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否执行 InnoDB 死锁检查，默认是打开的。死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。

undo文件不再使用系统表空间

默认创建2个UNDO表空间，不再使用系统表空间。

binlog日志过期时间精确到秒

之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

窗口函数(Window Functions)：也称分析函数

从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。数据量大时不建议使用

# 创建一张账户余额表 
CREATE TABLE `account_channel` ( 
 `id` int NOT NULL AUTO_INCREMENT, 
 `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',
 `channel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '账户渠道',
 `balance` int DEFAULT NULL COMMENT '余额', 
 PRIMARY KEY (`id`) 
) ENGINE=InnoDB; 

# 插入一些示例数据 10
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('1','zhuge', 'wx', '100');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('2','zhuge', 'alipay', '200');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('3','zhuge', 'yinhang', '300');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('4','lilei', 'wx', '200');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('5','lilei', 'alipay', '100');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('6','hanmeimei', 'wx', '500');


mysql> select * from account_channel; 
+----+-----------+---------+---------+ 
| id | name      | channel | balance | 
+----+-----------+---------+---------+ 
| 1  | zhuge     | wx      | 100     | 
| 2  | zhuge     | alipay  | 200     | 
| 3  | zhuge     | yinhang | 300     | 
| 4  | lilei     | wx      | 200     | 
| 5  | lilei     | alipay  | 100     | 
| 6  | hanmeimei | wx      | 500     | 
+----+-----------+---------+---------+ 
6 rows in set (0.00 sec) 

mysql> select name,sum(balance) from account_channel group by name; 
+-----------+--------------+ 
| name      | sum(balance) | 
+-----------+--------------+ 
| zhuge     | 600          | 
| lilei     | 300          | 
| hanmeimei | 500          | 
+-----------+--------------+ 
3 rows in set (0.00 sec) 

# 在聚合函数后面加上over()就变成分析函数了，后面可以不用再加group by制定分组，因为在over里已经用partition关键字指明了如何分组计算，这种可以保留原有表数据的结构，不会像分组聚合函数那样每组只返回一条数据
mysql> select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;
+-----------+---------+---------+-------------+ 
| name      | channel | balance | sum_balance | 
+-----------+---------+---------+-------------+ 
| hanmeimei | wx      | 500     | 500         | 
| lilei     | wx      | 200     | 300         | 
| lilei     | alipay  | 100     | 300         |
| zhuge     | wx      | 100     | 600         | 
| zhuge     | alipay  | 200     | 600         | 
| zhuge     | yinhang | 300     | 600         |
+-----------+---------+---------+-------------+ 
6 rows in set (0.00 sec)

# 会按照排序顺序累加balance
mysql> select name,channel,balance,sum(balance) over(partition by name order by balance) as sum_balance from account_channel;
+-----------+---------+---------+-------------+ 
| name      | channel | balance | sum_balance | 
+-----------+---------+---------+-------------+ 
| hanmeimei | wx      | 500     | 500         | 
| lilei     | alipay  | 100     | 100         | 
| lilei     | wx      | 200     | 300         | 
| zhuge     | wx      | 100     | 100         | 
| zhuge     | alipay  | 200     | 300         | 
| zhuge     | yinhang | 300     | 600         | 
+-----------+---------+---------+-------------+ 
6 rows in set (0.00 sec) 

# over()里如果不加条件，则默认使用整个表的数据做运算
mysql> select name,channel,balance,sum(balance) over() as sum_balance from account_channel;
+-----------+---------+---------+-------------+ 
| name      | channel | balance | sum_balance | 
+-----------+---------+---------+-------------+ 
| zhuge     | wx      | 100     | 1400        | 
| zhuge     | alipay  | 200     | 1400        |  
| zhuge     | yinhang | 300     | 1400        |  
| lilei     | wx      | 200     | 1400        |  
| lilei     | alipay  | 100     | 1400        |  
| hanmeimei | wx      | 500     | 1400        | 
+-----------+---------+---------+-------------+ 
6 rows in set (0.00 sec) 

mysql> select name,channel,balance,avg(balance) over(partition by name) as avg_balance from account_channel;
+-----------+---------+---------+-------------+ 
| name      | channel | balance | avg_balance | 
+-----------+---------+---------+-------------+ 
| hanmeimei | wx      | 500     | 500.0000    | 
| lilei     | wx      | 200     | 150.0000    | 
| lilei     | alipay  | 100     | 150.0000    | 
| zhuge     | wx      | 100     | 200.0000    | 
| zhuge     | alipay  | 200     | 200.0000    | 
| zhuge     | yinhang | 300     | 200.0000    | 
+-----------+---------+---------+-------------+ 
6 rows in set (0.00 sec)

专用窗口函数：

序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

分布函数：PERCENT_RANK()、CUME_DIST()

前后函数：LAG()、LEAD()

头尾函数：FIRST_VALUE()、LAST_VALUE()

其它函数：NTH_VALUE()、NTILE()

默认字符集由latin1变为utf8mb4

在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。

MyISAM系统表全部换成InnoDB表

将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表

自增变量持久化

在8.0之前的版本，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199)，一直到8.0才被解决，8.0版本将会对AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变。

元数据存储变动

MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。

DDL原子化

InnoDB表的DDL支持事务完整性，要么成功要么回滚。

MySQL 8.0 开始支持原子 DDL 操作，其中与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子DDL 操作内容包括：更新数据字典，存储引擎层的操作，在 binlog 中记录 DDL 操作。

支持与表相关的 DDL：数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE。

支持的其它 DDL ：存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER 语句。

支持账户管理相关的 DDL：用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME等等

参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效。

set persist innodb_lock_wait_timeout=25; # 举例，系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，当my.cnf 和mysqld-auto.cnf 同时存在时，后者具有更高优先级

Mysql高可用

异步复制

基于binlog位点同步的主从复制原理

1、主库会生成多个 binlog 日志文件。

2、从库的 I/O 线程请求指定文件和指定位置的 binlog 日志文件（位点）。

3、主库 dump 线程获取指定位点的 binlog 日志。

4、主库按照从库发送给来的位点信息读取 binlog，然后推送 binlog 给从库。

5、从库将得到的 binlog 写到本地的 relay log (中继日志) 文件中。

6、从库的 SQL 线程读取和解析 relay log 文件。

7、从库的 SQL 线程重放 relay log 中的命令

半同步复制

MySQL 从 5.7 版本开始，增加一种半同步复制（Semisynchronous Replication）的方式。这种机制与异步复制相比主要有如下区别：

主节点在收到客户端的请求后，必须在完成本节点日志写入的同时，还需要等待至少一个从节点完成数据同步的响应之后（或超时），才会响应请求。
从节点只有在写入 relay-log 并完成刷盘之后，才会向主节点响应。
当从节点响应超时时，主节点会将同步机制退化为异步复制。在至少一个从节点恢复，并完成数据追赶后，主节点会将同步机制恢复为半同步复制

相比于异步复制，半同步复制在一定程度上提高了数据的可用性，在未退化至异步复制时，如果主节点宕机，此时数据已复制至至少一台从节点。同时，由于向客户端响应时需要从节点完成响应，相比于异步复制，此时多出了主从节点上网络交互的耗时以及从节点写文件并刷盘的耗时，因此整体上集群对于客户端的响应性能表现必然有所降低。

半同步复制有两个重要的参数：

rpl_semi_sync_master_wait_slave_count（8.0.26之后改为rpl_semi_sync_source_wait_for_replica_count）：至少等待数据复制到几个从节点再返回。这个数量配置的越大，丢数据的风险越小，但是集群的性能和可用性就越差。
rpl_semi_sync_master_wait_point（8.0.26之后改为rpl_semi_sync_source_wait_point）：这个参数控制主库执行事务的线程，是在提交事务之前（AFTER_SYNC）等待复制，还是在提交事务之后（AFTER_COMMIT）等待复制。默认是 AFTER_SYNC，也就是先等待复制，再提交事务，这样就不会丢数据。

基于binlog 位点主从复制痛点分析

痛点1：首次开启主从复制的步骤复杂

第一次开启主从同步时，要求从库和主库是一致的。
找到主库的 binlog 位点。
设置从库的 binlog 位点。
开启从库的复制线程。

痛点 2：恢复主从复制的步骤复杂

找到从库复制线程停止时的位点。
解决复制异常的事务。无法解决时就需要手动跳过指定类型的错误，比如通过设置 slave_skip_errors=1032,1062。当然这个前提条件是跳过这类错误是无损的。（1062 错误是插入数据时唯一键冲突；1032 错误是删除数据时找不到行）

不论是首次开启同步时需要找位点和设置位点，还是恢复主从复制时，设置位点和忽略错误，这些步骤都显得过于复杂，而且容易出错。所以 MySQL 5.6 版本引入了 GTID，彻底解决了这个困难。

基于全局事务标识符（GTID）复制

GTID是一个基于原始mysql服务器生成的一个已经被成功执行的全局事务ID，它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器器上唯一，在所有存在主从关系的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠

一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
GTID用来代替传统复制方法，不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制
在传统的replica端，binlog是不用开启的，但是在GTID中replica端的binlog是必须开启的，目的是记录执行过的 GTID（强制）

GTID 的优势

更简单的实现 failover，不用以前那样在需要找位点（log_file 和 log_pos）。
更简单的搭建主从复制。
比传统的复制更加安全。
GTID 是连续的没有空洞的，保证数据的一致性，零丢失。

GTID结构

GTID表示为一对坐标，由冒号(:)分隔，如下所示:

GTID = source_id:transaction_id
# 单个服务器，单个事务
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
# 单个服务器，多个事务
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
# 源自同一服务器的多个单一gtid或gtid范围也可以包含在单个表达式中，gtid或范围以冒号分隔，如下例所示
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49
# 多个服务器，多个事务
2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19

source_id 标识source服务器，即源服务器唯一的server_uuid，由于GTID会传递到replica，所以也可以理解为源ID。

transaction_id 是一个序列号，由事务在源上提交的顺序决定。序列号的上限是有符号64位整数（2^63-1）

GTID存储在mysql数据库中名为gtid_executed的表中。该表中的一行包含它所代表的每个GTID或 GTID集合的起始服务器的UUID，以及该集合的开始和结束事务id

GTID工作原理

主库计算主库 GTID 集合和从库 GTID 的集合的差集，主库推送差集 binlog 给从库

当从库设置完同步参数后，主库 A 的 GTID 集合记为集合 x，从库 B 的 GTID 集合记为 y。从库同步的逻辑如下：
从库 B 指定主库 A，基于主备协议建立连接。
从库 B 把集合 y 发给主库 A。
主库 A 计算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是1~100，集合 y 是 1~90，那么这个差集就是 91~100。这里会判断集合 x 是不是包含有集合 y 的所有 GTID，如果不是则说明主库 A 删除了从库 B 需要的 binlog，主库 A 直接返回错误。
主库 A 从自己的 binlog 文件里面，找到第一个不在集合 y 中的事务 GTID，也就是找到了 91。
主库 A 从 GTID = 91 的事务开始，往后读 binlog 文件，按顺序取 binlog，然后发给 B。
从库 B 的 I/O 线程读取 binlog 文件生成 relay log，SQL 线程解析 relay log，然后执行 SQL 语句