Welcome to Hexo! This is your very first post. Check documentation for more info. If you get any problems when using Hexo, you can find the answer in troubleshooting or you can ask me on GitHub.

Quick Start

Create a new post

1

$ hexo new "My New Post"

More info: Writing

Run server

1

$ hexo server

More info: Server

Generate static files

1

$ hexo generate

More info: Generating

Deploy to remote sites

1

$ hexo deploy

More info: Deployment

Innodb系统表-结构解析

MySQL中在information_schema下， 有几张’INNODB_SYS%’命名的系统表，其中记录了当前实例下Inoodb存储的表和索引等信息，也称之为数据字典，这些内容存储在ibdata1系统表空间文件中。在某些情况下，没有了.frm文件，也可以读取ibdata1文件获取对应的表结构。本文即介绍一下系统表空间结构及如何读取ibdata文件。

[TOC]

基本文件结构介绍

数据页组成及类型

数据文件和系统文件都是由多个数据页组成，每个数据页16K(默认)，每个数据页都有不同的作用，有以下几种类型(storage/innobase/include/fil0fil.h):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

/** File page types (values of FIL_PAGE_TYPE) @{ */

#define FIL_PAGE_INDEX 17855 /*!< B-tree node */

#define FIL_PAGE_RTREE 17854 /*!< B-tree node */

#define FIL_PAGE_UNDO_LOG 2 /*!< Undo log page */

#define FIL_PAGE_INODE 3 /*!< Index node */

#define FIL_PAGE_IBUF_FREE_LIST 4 /*!< Insert buffer free list */

/* File page types introduced in MySQL/InnoDB 5.1.7 */

#define FIL_PAGE_TYPE_ALLOCATED 0 /*!< Freshly allocated page */

#define FIL_PAGE_IBUF_BITMAP 5 /*!< Insert buffer bitmap */

#define FIL_PAGE_TYPE_SYS 6 /*!< System page */

#define FIL_PAGE_TYPE_TRX_SYS 7 /*!< Transaction system data */

#define FIL_PAGE_TYPE_FSP_HDR 8 /*!< File space header */

#define FIL_PAGE_TYPE_XDES 9 /*!< Extent descriptor page */

...省略一些不太关注的类型

每个数据页头部有38自己的 FIL Header ， 结构内容:

解析FIL Header可以得到一些有用内容:

• offset //相对文件的偏移量

• previous/next page //当前页面前后节点的偏移量， 构成链表

• page type //page的类型，对应上面所述

• space id //表空间ID

ibdata1文件结构

ibdata1文件是系统表空间，space id为0 ， 结构如下:

从上图中看到，ibdata1文件中的第7个页面，为FIL_PAGE_TYPE_SYS类型数据页，存放 Data Dictionary Header ，这个页面中存储内容对应的偏移量如下(storage/innobase/include/dict0boot.h):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

/* The offset of the dictionary header on the page */

#define DICT_HDR FSEG_PAGE_DATA //38字节文件头部信息

/-------------------------------------------------------------/

/* Dictionary header offsets */

#define DICT_HDR_ROW_ID 0 /* The latest assigned row id */

#define DICT_HDR_TABLE_ID 8 /* The latest assigned table id */

#define DICT_HDR_INDEX_ID 16 /* The latest assigned index id */

#define DICT_HDR_MAX_SPACE_ID 24 /* The latest assigned space id,or 0*/

#define DICT_HDR_MIX_ID_LOW 28 /* Obsolete,always DICT_HDR_FIRST_ID*/

#define DICT_HDR_TABLES 32 /* Root of SYS_TABLES clust index */

#define DICT_HDR_TABLE_IDS 36 /* Root of SYS_TABLE_IDS sec index */

#define DICT_HDR_COLUMNS 40 /* Root of SYS_COLUMNS clust index */

#define DICT_HDR_INDEXES 44 /* Root of SYS_INDEXES clust index */

#define DICT_HDR_FIELDS 48 /* Root of SYS_FIELDS clust index */

#define DICT_HDR_FSEG_HEADER 56 /* Segment header for the tablespace

segment into which the dictionary

header is created */

其中DICT_HDR_TABLES、DICT_HDR_TABLE_IDS、DICT_HDR_COLUMNS、DICT_HDR_INDEXES、DICT_HDR_FIELDS就分别对应INNODB_SYS_%这几个系统表聚集索引(DICT_HDR_TABLE_IDS是SYS_TABLES的二级索引page)。解析这几个page就可以得到对应系统表中的数据。

hexdump -C ibdata1 解析

从0开始的第7个page偏移量大小是1c000，从这里开始经过(FSEG_PAGE_DATA+ DICT_HDR_TABLES） 70字节，之后开始读取的内容即为这几个系统表对应的page号:

DICT_HDR_TABLES // 8

DICT_HDR_TABLE_IDS // 9

DICT_HDR_COLUMNS // 10

DICT_HDR_INDEXES // 11

DICT_HDR_FIELDS // 12

数据页解析

DICT_HDR_%对应的数据页类型为INDEX类型，INDEX结构如下:

其中INDEX Header 结构如下:

上面这两张图中Nmuber of Directory Slots和Page Directory比较重要，Slots的作用是加快在页面内数据的查找速度，实现二分查找，通过解析Nmuber of Directory Slots可以得到page中总共有多少Slot，每个Slot为2个字节，存放相对于page的偏移量。

page从后向前读取Directory Slot 中的偏移量，实现二分查找，加快在页面中查找数据的速度 ，组成结构如下:

例如上图中总共有7个Slot， 存放的偏移量数据为[99, 221, 349, 477, 605, 733, 112]， 如果要查询K=10这条记录，只需要扫描[477，349]这两个偏移量对应的Slot即可找到对应的数据。每个Slot包含的记录数(4-8条记录)。

记录解析

现在知道了如何通过Page Directory定位数据，就需要知道每一条记录的存储结构了， MySQL 记录格式有新旧两种(Redundant Or Compact)，Index Header 中Number of Heap Records 的最高位如果是1就是Compact格式，否则是Redundant。

(storage/innobase/include/page0page.h)

1
2
3
4

#define PAGE_N_HEAP 4 /* number of records in the heap,

bit 15=flag: new-style compact page format */

不同的类型，存储结构也不相同，记录由header和data两部分组成

header部分存放了记录长度信息和一些额外的信息，Redundant格式为6字节，Compact格式为5字节

(storage/innobase/include/rem0rec.ic)

这里我们解析的是ibdata1文件中的系统表，其格式都是Redundant，6个字节存储的内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

/* Offsets of the bit-fields in an old-style record. NOTE! In the table the

most significant bytes and bits are written below less significant.

(1) byte offset (2) bit usage within byte

downward from

origin -> 1 8 bits pointer to next record

2 8 bits pointer to next record

3 1 bit short flag

7 bits number of fields

4 3 bits number of fields

5 bits heap number

5 8 bits heap number

6 4 bits n_owned

4 bits info bi

*/

通过这些额外信息可以得到:

• 当前记录是否为delete记录

• 当前Slot中有几条记录

• 当前记录的类型， 如果heap number 为0是infimum，1是supremum，从2开始是用户记录

• 记录中有多个字段

• 变长字段存储格式(1 or 2 字节)

• 下一条记录的偏移量(相对于page)

例如我们解析第8个page，也就是innodb_sys_tables中的内容:

第8个page对应的偏移量大小是0x20000

第9个page对应的偏移量大小是0x24000

第8个page减去8个字节之后的2个字节就是第一个slot对应的值 00 65(16进制)= 101(10进制)，如下图:

从101向前读取6字节，就是record header信息，按规则解析，解析的代码示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

h.deleteFlag = (data[0] & 0x20) != 0

h.minRecFlag = (data[0] & 0x10) != 0

h.Owned = data[0] & 0x0f

h.heapNo |= uint16(data[1]) << 5

h.heapNo |= uint16(data[2]&0xf8) >> 3

h.nField |= uint16(data[2]&0x07) << 7

h.nField |= uint16(data[3]&0xfe) >> 1

h.sFlag = (data[3] & 0x1) != 0

h.nextRecorder = binary.BigEndian.Uint16(data[4:])

得到值为：

这可以知道记录类型是infimum， 下一条记录的Offset是366。

偏移量366对应的前6个字节内容如下:

解析后的值:

得到这条记录有10个字段，向前读取10个字节: 3a b6 36 32 2a 26 22 1a 13 0d， 就是字段的偏移量，通过偏移量就可以从fieldOffset位置处开始解析每一个字段的值。下面这个图更为直观一些

解析这10个字节就可以得到每个字段的长度，[13,6,7,8,4,4,8,4,0,4]， 解析大致方式是判断最高位是否为1，如果不为1就取后7位，并用当前字段的解析值减去前一个字段的解析值即得到这个字段的长度，例如:

3a b6 36 32 2a 26 22 1a 13 0d

0d = 0000 1101

13 = 0001 0011

这两个字段都不为空，所以第一个字段的长度是13 ， 第二个字段长度是19 - 13 = 6

通过每个字段的长度，再从fieldOffset位置处开始解析出每个字段的值:

sys_tables聚集索引的定义如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

enum dict_fld_sys_tables_enum {

DICT_FLDSYS_TABLESNAME = 0,

DICT_FLDSYS_TABLESDB_TRX_ID = 1,

DICT_FLDSYS_TABLESDB_ROLL_PTR = 2,

DICT_FLDSYS_TABLESID = 3,

DICT_FLDSYS_TABLESN_COLS = 4,

DICT_FLDSYS_TABLESTYPE = 5,

DICT_FLDSYS_TABLESMIX_ID = 6,

DICT_FLDSYS_TABLESMIX_LEN = 7,

DICT_FLDSYS_TABLESCLUSTER_ID = 8,

DICT_FLDSYS_TABLESSPACE = 9,

DICT_NUM_FIELDS__SYS_TABLES = 10

};

解析方法可参考函数(storage/innobase/dict/dict0load.cc)dict_sys_tables_rec_read，解析后的值:

与查询INNODB_SYS_TABLES表中的记录做个对比:

表中查询出的n_cols是5，但我们解析出来的是2，原因是表中查询会把三个隐藏字段也计算在内(DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID)。

file_format与row_format如何的出来的呢？

n_cols的第32位代表row_format格式，如果为1就是COMPACT。

file_format会比较特殊一些， 需要根据解析出来的type和n_cols共同计算完成，代码中是这样写的(storage/innobase/include/dict0dict.ic):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

Convert a 32 bit integer from SYS_TABLES.TYPE to dict_table_t::flags

The following chart shows the translation of the low order bit.

Other bits are the same.

========================= Low order bit ==========================

| REDUNDANT | COMPACT | COMPRESSED and DYNAMIC

SYS_TABLES.TYPE | 1 | 1 | 1

dict_table_t::flags | 0 | 1 | 1

==================================================================

@return ulint containing SYS_TABLES.TYPE */

UNIV_INLINE

ulint

dict_sys_tables_type_to_tf(

/=======================/

ulint type, /*!< in: SYS_TABLES.TYPE field */

ulint n_cols) /*!< in: SYS_TABLES.N_COLS field */

{

ulint flags;

ulint redundant = !(n_cols & DICT_N_COLS_COMPACT); // 判断是redundant还是COMPACT

/* Adjust bit zero. */

flags = redundant ? 0 : 1;

/* ZIP_SSIZE, ATOMIC_BLOBS & DATA_DIR are the same. */

flags |= type & (DICT_TF_MASK_ZIP_SSIZE

| DICT_TF_MASK_ATOMIC_BLOBS

| DICT_TF_MASK_DATA_DIR

| DICT_TF_MASK_SHARED_SPACE); //除了最低位，用相应的标志位与type做 '&'运算，判断是否存在这些属性，最后存放在flags中

ut_ad(!DICT_TF_GET_ZIP_SSIZE(flags) || DICT_TF_HAS_ATOMIC_BLOBS(flags));

return(flags);

}

n_cols为2第32位是0 ， 所以redundant=1，flags的低位是0

type为1，经过与相应的标志位做’&’运算后，所有位都为0，最后的flags即是0，用一张图解释:

查询的时候做相应的转换，判断file_format类型:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

i_s_dict_fill_sys_tables(

/=====================/

THD* thd, /*!< in: thread */

dict_table_t* table, /*!< in: table */

TABLE* table_to_fill) /*!< in/out: fill this table */

{

Field** fields;

ulint compact = DICT_TF_GET_COMPACT(table->flags); //获取compact标志位

ulint atomic_blobs = DICT_TF_HAS_ATOMIC_BLOBS(

table->flags); //获取atomic_blobs标志位，这个标志就代表了对于 blob or text field 做页外存储，也就代表file_format是Barracuda

const page_size_t& page_size = dict_tf_get_page_size(table->flags);

const char* file_format;

const char* row_format;

const char* space_type;

file_format = trx_sys_file_format_id_to_name(atomic_blobs); //可以参考file_format_name_map，是一个指针数组，下标0代表Antelope，下标1代表Barracuda

if (!compact) {

row_format = "Redundant";

} else if (!atomic_blobs) {

row_format = "Compact";

} else if (DICT_TF_GET_ZIP_SSIZE(table->flags)) {

row_format = "Compressed";

} else {

row_format = "Dynamic";

}

if (is_system_tablespace(table->space)) {

space_type = "System";

} else if (DICT_TF_HAS_SHARED_SPACE(table->flags)) {

space_type = "General";

} else {

space_type = "Single";

}

//省略无用代码
}

compact为0，!compact即为1，所以row_format为Redundant

同时atomic_blobs为0 ， file_format即为Antelope

• 再用以上规则解析一条非系统表记录

n_cols为2第32位是1 ， 所以redundant=0，flags的低位是1

type为33，对应的二进制是:0010 0001，经过与相应的标志位做’&’运算后，对应的二进制为:0010 0000，在与flags做’|’操作后，二进制为:0010001，则atomic_blobs和compact为1，对应的file_format则是Barracuda， row_format是Dynamic

同时n_cols 第32位的标志位需要取消，n_cols即为2了。

与查询表中得到的信息是一致的:

<!–14–>

结语

本文介绍了Innodb系统表空间基本的结构，及如何解析ibdata表空间中的记录。通过翻阅资料和MySQL代码的查看，学习到很多技巧，例如:位移操作、逻辑运算、如何节省空间等。后面会再写一篇关于系统表加载的文章。

MySQL中如何控制SQL具有相同执行路径

最近有客户提出一个比较有意思的问题，生产环境与测试环境数据量相差比较大，导致两个环境中执行路径大不相同，如何能保证这两个环境执行计划相同呢？

这还是一个比较实际的需求，MySQL中没有绑定执行计划功能，并且有时候测试环境与生产环境执行路径不同，可能会引发线上的故障。

能想到的一种方式就是将MySQL中持久化统计信息表进行同步，这样达到两个环境执行路径相同

想法验证

验证方法的可行性，通过一个实验做验证，大致步骤如下:

1. 表中插入10万条数据，执行一条查询，记录下执行计划

2. 查看mysql.innodb_index_stats中的统计信息，并导出

3. 删除表中数据

4. 手工收集一次统计信息，之后将innodb_stats_auto_recalc设置为off，为了防止之后自动收集统计信息

5. 插入5万条数据，执行一条查询，记录下执行计划

6. 导入步骤2中记录下来的统计信息后，再次查看执行计划，是否与步骤1中的相同

实验步骤

1. 创建表及插入函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE t ( id int(11) NOT NULL,a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, PRIMARY KEY (id), KEY a (a), KEY b (b)) ENGINE=InnoDB;

delimiter ;;

create procedure idata() begin

declare i int; set i=1; while(i<=100000)do

insert into t values(i, i, i);

set i=i+1; end while;

end;;

delimiter ;

call idata();

2. 插入数据

1
2
3
4

mysql> call idata();

Query OK, 1 row affected (10 min 9.89 sec)

3. 查看统计信息及执行计划

将统计信息用mysqldump导出

1

./mysqldump --host=127.0.0.1--port=3322 --user=root --password=123456 --databases mysql --tables innodb_index_stats --where "table_name = 't'"

4. 删除表数据，集统计信息，关闭统计信息自动收集

1
2
3
4
5
6
7
8

mysql> delete from t;

Query OK, 100000 rows affected (0.68 sec)

mysql> analyze table dhy.t;

mysql> set global innodb_stats_auto_recalc = off; //关闭统计信息持久化收集

这时对应的统计信息如下:

5. 再次插入数据

1
2
3
4

mysql> call idata();

Query OK, 1 row affected (5 min 10.59 sec)

这时再执行相同的查询，就会看到走不到索引了:

6. 导入统计信息

将原有的统计信息删除，并导入备份出来的统计信息

1
2
3
4
5
6
7
8
9
10
11
12

mysql> delete from innodb_index_stats where table_name = 't'; Query OK, 11 rows affected (0.01 sec)

mysql> INSERT INTO innodb_index_stats VALUES ('dhy','t','PRIMARY','2020-06-05 09:44:32','n_diff_pfx01',100256,20,'id'),('dhy','t','PRIMARY','2020-06-05 09:44:32','n_leaf_pages',208,NULL,'Number of leaf pages in the index'),('dhy','t','PRIMARY','2020-06-05 09:44:32','size',225,NULL,'Number of pages in the index'),('dhy','t','a','2020-06-05 09:44:32','n_diff_pfx01',100557,20,'a'),('dhy','t','a','2020-06-05 09:44:32','n_diff_pfx02',100710,20,'a,id'),('dhy','t','a','2020-06-05 09:44:32','n_leaf_pages',90,NULL,'Number of leaf pages in the index'),('dhy','t','a','2020-06-05 09:44:32','size',97,NULL,'Number of pages in the index'),('dhy','t','b','2020-06-05 09:44:32','n_diff_pfx01',100710,20,'b'),('dhy','t','b','2020-06-05 09:44:32','n_diff_pfx02',100557,20,'b,id'),('dhy','t','b','2020-06-05 09:44:32','n_leaf_pages',90,NULL,'Number of leaf pages in the index'),('dhy','t','b','2020-06-05 09:44:32','size',97,NULL,'Number of pages in the index');

Query OK, 11 rows affected (0.00 sec)

Records: 11 Duplicates: 0 Warnings: 0

同时需要把innodb_table_stats表中的n_rows修改为10万

mysql> update innodb_table_stats set n_rows = 100000 where table_name = 't';

导入之后直接执行语句，还是走不到索引，通过show index from t;可以看到索引的统计信息还没有更新，需要执行一次flush table(线上谨慎执行或者不建议操作)，将统计信息从持久化磁盘上刷新到内存中。

flush table 前:

flsuh table 后:

看到这里已经走到了索引，可以达到我们想要的结果了。

总结

通过更新Innodb两张持久化统计信息表，能够达到测试环境与生产环境具有相同的执行路径，需要注意的是，当再次analyze后还是会重新收集统计信息。

RC隔离级别下，死锁案例分析

文章的产生是因为生产上遇到一个死锁案例，根据此案例分析引申出比较多的内容，故总结一下

死锁分析

• 数据库版本: MySQL 5.6.39社区版
• 事务隔离级别: RC
• 死锁日志

这个死锁日志中，可以得到上锁信息如下:

1. 事务28608410 ， 申请X类型记录锁时，发生了锁等待，对应的记录就是user_id=195578这条(16进制的2fbfa就是195578)
2. 事务28608409持有user_id=195578这条记录上的X类型记录锁时
3. 事务28608409， 申请S类型的Next-Key Lock时发生了所等待，对应user_id=195578这条记录

RC隔离级别下理论上不应该存在Nexy-Lock的，为什么这里会出现S类型的Next-Lock呢？我们先不考虑这问题，先单纯的分析这个死锁。

上面的死锁对应操作如下:

针对上面这个死锁，有些奇怪:

• 为什么事务28608409申请S类型的Next-Key Lock时会发生死锁呢？是和谁冲突的呢？感觉不像是和事务28608410发生的冲突，因为28608409还没有结束，那么X类型的记录锁也申请不到。感觉这种情况下不应该发生死锁的，并且发现在8.0.18版本中进行同样的测试，就不会发现死锁问题。
  查询了最近8.0版本的Release Notes，在8.0.18版本中有一个bug修复:

  • InnoDB: A deadlock was possible when a transaction tries to upgrade a record lock to a next key lock. (Bug #23755664, Bug #82127)

    对应bug地址:

  • https://bugs.mysql.com/bug.php?id=82127

大概意思就是在某些情况下，锁升级问题导致了死锁。现象来看是bug导致，但通过show engine innodb status\G，查看加锁情况，就会发现还是有S类型的Next-Key Lock加锁成功的:

这里会发现在supremum添加S类型的Next-Key Lock加锁成功的，还要继续分析为何会添加这个S类型的Next-Key Lock。

• 注意:
  下面的分析就与此bug无关，因为RC隔离级别下某些情况确实会加S类型的Next-Key Lock。主要是看下为何会加S Next-Key Lock和一些延伸。

为何要添加S类型的间隙锁

MySQL中间隙锁是在RR隔离级别下才会有，为何RC情况下也会出现？官方文档中也有说过，如果insert时候，有唯一性索引检测到冲突，会添加S类型Nexy-Key Lock 锁。看一个例子:

• 测试环境MySQL-8.0.19

  1 2 3

  create table t1 (id int ); create unique index idx_uni_id on t1 (id); insert into t1 values(1);

  session1|session2|session3

——–|———|———–
begin;|||
DELETE FROM t1 WHERE id = 1;||
|begin;
|insert into t1 values(1);//等待|
||begin;
||insert into t1 values(1); //等待
commit;||

这里还需要两个概念:

唯一约束检测原则:

• 当发生唯一索引约束冲突时，会对当前记录和当前记录的下一条添加S类型Next-Key Lock

插入意向锁:

• 文档中关于插入意向锁的描述，简单说就是:插入意向锁直接是不冲突的，插入意向锁也是一种间隙锁，提高并发插入。
• 但还有一点就是申请插入意向锁时，会检查插入记录位置的下一条记录上是否持有锁，如果有,则判断是否与插入意向锁冲突

如果这里添加的不是S类型Next-Key Lock锁，会出现主键失效的情况， 假设添加的是S NOT GAP锁，情况如下:

• session1 添加X类型记录锁

• session2 添加S NOT GAP锁 //等待

• session3 添加S NOT GAP锁 //等待

  由于session1还没有提交，所以会做唯一性约束检查，申请S NOT GAP锁(假设)。当session1执行commit后，session2和session3获得S NOT GAP 锁(假设), 并且session2和session3同时会对下一条记录添加S NOT GAP 锁(假设)，这时session2检查插入记录的下一条时发现有S NOT GAP锁，不与插入意向锁冲突，则插入成功，同理session3也会做相应的检查，但也不会发生冲突，所以两条记录都会插入成功。

  所以这里需要添加S类型的Next-Key Lock，这样插入意向锁就会发生冲突，在一些场景下就会触发死锁，例如这个例子，死锁日志如下:

  

这里事务120495申请插入supremum上的插入意向锁时发生了锁等待，因为事务120496在supremum上添加了S类型的Next-Key Lock，并且事务120496申请supremum上的插入意向锁时发生了冲突，这样就造成了死锁。

这里为何说是一些场景情况下， 因为我发现测试中也有不会发生死锁现象的时候，分别看下出现死锁和没有出现死锁时performance_schema.data_locks上的加锁情况:

• 当发生锁等待时:

  

  session2,session3都会申请S类型的Next-Key Lock，与X类型的记录锁发生锁冲突，等待。

• 出现死锁时的情况

  

  由于出现了死锁，这里看到的就是留下来的事务加锁的信息，这里能看到个(S,GAP)，这个后面讲锁继承和锁迁移时会说到。

• 没有出现死锁情况

  

  没有出现死锁情况从上锁信息来看，像是session2和session3中，有一个执行的很快，首先在id=1这条记录上添加了X类型的记录锁，导致另外一个会话申请S类型的Next-Key Lock时发生了锁锁等待，从而没有导致死锁发生。但当中还有一点没有搞明白的是，为何S类型的GAP锁会发生锁等待，并且thread_id看起来也很奇怪。

代码上的补充说明

在做插入时首先会做唯一性约束检查，在函数row_ins_scan_sec_index_for_duplicate中，大致内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

do {
  省略若干代码
   const ulint lock_type =
        index->table->skip_gap_locks() ? LOCK_REC_NOT_GAP : LOCK_ORDINARY; //如果锁操作的表，是不允许跳过间隙锁的，则lock_type就是LOCK_ORDINARY --Next-Key Lock
  省略若干代码
  else {
      if (index->table->skip_gap_locks()) {
        /* Only GAP lock is possible on supremum. */
        if (page_rec_is_supremum(rec)) {
          continue;
        }

        if (cmp_dtuple_rec(entry, rec, index, offsets) < 0) {
          goto end_scan;
        }
      }

      err = row_ins_set_rec_lock(LOCK_S, lock_type, block, rec, index, offsets,
                                 thr); //添加LOCK_S锁，lock_type=0 ,也就是S类型的Next-Key Lock
    }

    switch (err) {
      case DB_SUCCESS_LOCKED_REC:
        err = DB_SUCCESS;
      case DB_SUCCESS:
        break;
      default:
        goto end_scan;
    }

    if (page_rec_is_supremum(rec)) {
      continue;
    }

    cmp = cmp_dtuple_rec(entry, rec, index, offsets);

    if (cmp == 0 && !index->allow_duplicates) {
      if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) { // 如果冲突检测到两条记录一样，则判断下记录是否是标记为delete的，有可能记录delete了但是还没有被purge线程purge
        err = DB_DUPLICATE_KEY;

        thr_get_trx(thr)->error_index = index;

        /* If the duplicate is on hidden FTS_DOC_ID,
        state so in the error log */
        if (index == index->table->fts_doc_id_index &&
            DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_FTS_HAS_DOC_ID)) {
          ib::error(ER_IB_MSG_958) << "Duplicate FTS_DOC_ID"
                                      " value on table "
                                   << index->table->name;
        }

        goto end_scan;
      }
    } else {
      ut_a(cmp < 0 || index->allow_duplicates);
      goto end_scan;
    }
  } while (btr_pcur_move_to_next(&pcur, mtr)); // 继续取下一条记录

这里还有个问题就是为何还要获取下一条记录呢？这个可以阅读下文章最后面的参考连接(4.5.6)，网易温正湖老师的三篇文章。

冲突检测后，会进入lock_rec_insert_check_and_lock函数，主要作用就是检测，插入记录的下一条记录是否存在锁，如果存在是为与插入意向锁冲突:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

ulint heap_no = page_rec_get_heap_no(next_rec); //读取当前记录的下一条，获取heap_no
 //省略若干代码
  lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no); //查看是否有锁存在

  if (lock == NULL) {
    //省略若干代码
  }


//省略若干代码

  /* If another transaction has an explicit lock request which locks
  the gap, waiting or granted, on the successor, the insert has to wait.

  An exception is the case where the lock by the another transaction
  is a gap type lock which it placed to wait for its turn to insert. We
  do not consider that kind of a lock conflicting with our insert. This
  eliminates an unnecessary deadlock which resulted when 2 transactions
  had to wait for their insert. Both had waiting gap type lock requests
  on the successor, which produced an unnecessary deadlock. */

  const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;

  const lock_t *wait_for =
      lock_rec_other_has_conflicting(type_mode, block, heap_no, trx); //添加插入意向锁，判断是否冲突

隐式锁、锁继承、锁分裂

先看一些后面文章中会用到的内容, 涉及到锁类型和锁模式，还有一些数字，其定义如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

enum lock_mode { LOCK_IS = 0, /* intention shared */ LOCK_IX, /* intention exclusive */ LOCK_S, /* shared */ LOCK_X, /* exclusive */ LOCK_AUTO_INC, /* locks the auto-inc counter of a table in an exclusive mode */ LOCK_NONE, /* this is used elsewhere to note consistent read */ LOCK_NUM = LOCK_NONE, /* number of lock modes */ LOCK_NONE_UNSET = 255 }; #define LOCK_TABLE 16 /*!< table lock */ #define LOCK_REC 32 /*!< record lock */ #define LOCK_WAIT 256 /* 表示正在等待锁 */ #define LOCK_ORDINARY 0 /* 表示 next-key lock ，锁住记录本身和记录之前的 gap*/ #define LOCK_GAP 512 /* 表示锁住记录之前 gap（不锁记录本身） */ #define LOCK_REC_NOT_GAP 1024 /* 表示锁住记录本身，不锁记录前面的 gap */ #define LOCK_INSERT_INTENTION 2048 /* 插入意向锁 */ #define LOCK_CONV_BY_OTHER 4096 /* 表示锁是由其它事务创建的(比如隐式锁转换) */ 例如文中提到的，546= LOCK_GAP|LOCK_REC|LOCK_S(512+32+2) = S,GAP heap_no是记录在物理文件中的位置编号，是物理位置，例如有可能是这样存储: heap_no : 2 3 存储的值: 2 1 heap_no = 1代表supermum heap_no = 0 代表infimum 我们插入的数据都是从heap_no = 2开始计算

上面的例子中，我们会看到有S类型的GAP锁出现，这里面涉及到了锁继承和锁分裂，这里我们解释一下:

正常的插入时，不会添加锁的，除非发生有唯一性冲突检测时会添加S类型的Next-Key Lock，通过一个例子来感受下:
session1 开启会话，执行insert语句，这时查看performance_schema.data_locks表，只能看到一个表上的意向锁(IX),但如果session 2 开启会话，执行同样的insert语句，就会看到如下结果:

这里看到表上有了X，REC_NOT_GAP也就是记录锁，但是仔细看会发现thread_id是81，81是session2的线程ID，这就是隐式锁，因为这里的X，REC_NOT_GAP记录锁是session2会话构建的。
还有两个圈红的地方是数值3，代表的heap_no，下面会用到。

涉及到GAP锁时，会有锁继承和锁分裂现象，看下面这个例子:

1
2
3

create table t1 (id int );
create unique index idx_uni_id on t1 (id);
insert into t1 values(2);

查看performance_schema.data_locks表:
当执行rollback之前的加锁情况，这时能看到申请S类型Next-Key Lock发生了锁冲突

执行rollback之后情况如下:

这里的锁模式都是S类型的间隙锁，这是如何来的呢？看thread_id是80，那就是session1创建的。执行rollback时候，gdb查看到信息如下:

• 这里主要看这里: lock_rec_inherit_to_gap(heir_block=0x000000012b837a80,block=0x000000012b837a80, heir_heap_no=2, heap_no=3)
  将heap_no=3继承给heap_no=2, type_mode=546(S,GAP) , heap_no=3就是插入id=1这条记录

  • 锁继承是当原有记录被删除时，需要将原记录上的GAP属性继承给下一条记录。例如:表中有两条记录(1,2),原有的GAP锁加在(-oo,1)上，当记录1被删除后，要保证GAP锁能继续起到锁住这段范围的作用，就会将GAP锁继承给记录2，也就是变成了(-oo,2)。
    所以这里session1执行了rollback后，会将原有记录上申请S类型的Next-Key Lock的GAP属性继承给下一条记录。

  1 2 3 4 5 6 7 8 9 10

  thread #42, stop reason = breakpoint 56.2 * frame #0: 0x000000010c2e94f0 mysqld`lock_rec_set_nth_bit(lock=0x00007fb758839778, i=2) at lock0priv.ic:83:3 frame #1: 0x000000010c2e91ac mysqld`RecLock::lock_alloc(trx=0x000000012b2bf060, index=0x00007fb7587c6c88, mode=546, rec_id=0x00007000071ab1a8, size=9) at lock0lock.cc:1033:3 frame #2: 0x000000010c2ea030 mysqld`RecLock::create(this=0x00007000071ab180, trx=0x000000012b2bf060, add_to_hash=true, prdt=0x0000000000000000) at lock0lock.cc:1308:18 frame #3: 0x000000010c2ecc72 mysqld`lock_rec_add_to_queue(type_mode=546, block=0x000000012b837a80, heap_no=2, index=0x00007fb7587c6c88, trx=0x000000012b2bf060, we_own_trx_mutex=false) at lock0lock.cc:1551:12 frame #4: 0x000000010c2edd3c mysqld`lock_rec_inherit_to_gap(heir_block=0x000000012b837a80, block=0x000000012b837a80, heir_heap_no=2, heap_no=3) at lock0lock.cc:2625:7 frame #5: 0x000000010c2eead3 mysqld`lock_update_delete(block=0x000000012b837a80, rec="\x80") at lock0lock.cc:3443:3 frame #6: 0x000000010be4b211 mysqld`btr_cur_optimistic_delete_func(cursor=0x00007000071ab8a8, flags=0, mtr=0x00007000071ab9e0) at btr0cur.cc:4616:5 frame #7: 0x000000010c5560c5 mysqld`row_undo_ins_remove_sec_low(mode=16386, index=0x00007fb7587c6c88, entry=0x00007fb75603f2b8, thr=0x00007fb75a3af210, node=0x00007fb75916c2b8) at row0uins.cc:245:11 省略 ......

  • 下面执行insert插入时候，会将原有的一个间隙锁，分裂成两个(锁分裂)，例如原有的GAP是加在了(1,5)上，现在插入一条记录3，则会变成(-oo,3),(3,5)这两个GAP锁:
    heap_no=3上的锁从heap_no=2上分裂过来 ， heap_no=3也就是session2中插入id=1这条记录 lock_rec_inherit_to_gap_if_gap_lock(block=0x000000012b837a80, heir_heap_no=3, heap_no=2)

  1 2 3 4 5 6 7 8 9 10

  thread #39, stop reason = breakpoint 56.2 * frame #0: 0x000000010c2e94f0 mysqld`lock_rec_set_nth_bit(lock=0x00007fb758839778, i=3) at lock0priv.ic:83:3 frame #1: 0x000000010c2ecbd1 mysqld`lock_rec_add_to_queue(type_mode=546, block=0x000000012b837a80, heap_no=3, index=0x00007fb7587c6c88, trx=0x000000012b2bf060, we_own_trx_mutex=false) at lock0lock.cc:1538:9 frame #2: 0x000000010c2ee956 mysqld`lock_rec_inherit_to_gap_if_gap_lock(block=0x000000012b837a80, heir_heap_no=3, heap_no=2) at lock0lock.cc:2656:7 frame #3: 0x000000010c2ee856 mysqld`lock_update_insert(block=0x000000012b837a80, rec="\x80") at lock0lock.cc:3417:3 frame #4: 0x000000010be41bb2 mysqld`btr_cur_optimistic_insert(flags=0, cursor=0x00007000070ce478, offsets=0x00007000070ce448, heap=0x00007000070ce558, entry=0x00007fb7587d22c8, rec=0x00007000070cdcc8, big_rec=0x00007000070cdcc0, n_ext=0, thr=0x00007fb75797d350, mtr=0x00007000070ce8a8) at btr0cur.cc:2928:5 frame #5: 0x000000010c46f606 mysqld`row_ins_sec_index_entry_low(flags=0, mode=2, index=0x00007fb7587c6c88, offsets_heap=0x00007fb75680fc18, heap=0x00007fb75681d218, entry=0x00007fb7587d22c8, trx_id=0, thr=0x00007fb75797d350, dup_chk_only=false) at row0ins.cc:3004:11 frame #6: 0x000000010c471fb9 mysqld`row_ins_sec_index_entry(index=0x00007fb7587c6c88, entry=0x00007fb7587d22c8, thr=0x00007fb75797d350, dup_chk_only=false) at row0ins.cc:3200:9 frame #7: 0x000000010c47ce56 mysqld`row_ins_index_entry(index=0x00007fb7587c6c88, entry=0x00007fb7587d22c8, multi_val_pos=0x00007fb75797d130, thr=0x00007fb75797d350) at row0ins.cc:3300:13 省略 ......

  这样就成了我们上面看到的结果了。

结语

• 这块内容也是看了很多大牛的文章和资料，根据自己对这块知识存在的疑问做了个总结。当然其中还是有很多地方也不是很明白，还需要多看多试验才可以。由于水平有限文章必然也会存在错误，还望大家能够指出问题。

参考文章

1. http://mysql.taobao.org/monthly/2017/12/02/ –MySQL · 引擎特性 · Innodb 锁子系统浅析
2. http://mysql.taobao.org/monthly/2016/01/01/ –MySQL · 引擎特性 · InnoDB 事务锁系统简介
3. http://mysql.taobao.org/monthly/2016/06/01/ – MySQL · 特性分析 · innodb 锁分裂继承与迁移
4. https://zhuanlan.zhihu.com/p/52098868 –MySQL RC级别下并发insert锁超时问题 - 现象分析和解释
5. https://zhuanlan.zhihu.com/p/52100378 –MySQL RC级别下并发insert锁超时问题 - 源码分析
6. https://zhuanlan.zhihu.com/p/52234835 –MySQL RC级别下并发insert锁超时问题 - 案例验证
7. http://mysql.taobao.org/monthly/2015/06/02/ – MySQL · 捉虫动态 · 唯一键约束失效
8. https://www.jianshu.com/p/1e1e13f8ec27 –MySQL：一个死锁分析 (未分析出来的死锁)

MySQL复制安全分析

在MySQL复制中我们经常会遇到，Slave异常down机，当Slave再次启动时，会涉及到一个问题，如何能保证复制的正确性(可能是复制报错或者是数据异常)。
在5.6包括之前版本没有GTID情况下，需要将IO线程和SQL线程的复制信息都记录到表中并且每一个事物都要将位点信息更新，这样效率比较低，可以设置relay_log_recovey为ON，这样IO线程的位点信息就不用每次都更新了，提高了一定的性能，具体内容可以参考: <https://donghy-coredumped.github.io/2018/01/23/MySQL%E4%B8%BB%E4%BB%8E%E6%95%B0%E6%8D%AE%E4%B8%80%E8%87%B4%E6%80%A7/> , 此篇文章我们主要分析在GTID情况下的复制安全性

GTID下的复制安全

在官方文档中可以看到一个关于复制安全的表格:

从这个表格中看到，当GTID设置为ON时，并且是MASTER_AUTO_POSITION时，relay_log_recover和relay_log_info_repository设置为任何值对于复制都是安全的。
这里就有一些疑问了，如果将relay_log_recover设置为OFF，relay_log_info_repository设置为FILE，Slave意外down机重启后，根据文件中的信息去拉取日志，这时文件中的位点信息有可能不是最新的(因为放在文件中，无法用事务保证位点的信息和应用的事务是原子性的)，这样不就会出现问题嘛？

GTID下Slave向Master拉取日志的方式

GTID下，Slave向Master拉取日志，和以前基于位点这种方式并不一样，根据官方文档中手册描述，向Master申请拉取的GTID值是根据UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)算出来的

• global.gtid_executed 这个值可以通过Slave上的binlog获取到
• Retrieved_gtid_set 和last_received_GTID 都是可以从relay log中获取

这样可以看出来GTID模式下，向主库拉取日志的时候需要的值都可以在日志中取到，并不需根据位点信息，所以relay_log_recover和relay_log_info_repository 设置为任何值都是可以的。

详细的内容，可以看下文章最后面的两个链接。

关于还要读取mysql.slave_relay_log_info表的理解

上面我们知道了GTID的拉取方式，但是在实际环境中，我们发现Slave在异常down机重启后，还是会读取mysql.slave_relay_log_info这个表，这里我个人觉得读取这个表中的内容，就是用于定位要从哪个relay log文件和哪个位点开始扫描，去找Retrieved_gtid_set，并不是利用表里的位点去拉取binlog，因为这个位点信息有可能会是不准的(上面图片中红框的地方显示relay_log_info_repository可以设置为Any)。

参考文档

1. https://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247484693&idx=1&sn=9f54fb01a92a11f478326834aea919fa&chksm=fb242c18cc53a50ef1810f84872c59dc89e7226fa78b7ff66c54f1bae9a3b6e265679241c817&mpshare=1&scene=1&srcid=0808sXwNnl5Bm5hfnUWvRz06&sharer_sharetime=1565273045106&sharer_shareid=696b16f7bf543a042f0c964a25755e34%23rd
2. https://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247484696&idx=1&sn=1341c99926c662135d900c1b42f2c800&chksm=fb242c15cc53a50314894a1ada2facbddf8397d8b07c76b9b9601f8eab4ccee73d25d4082e7f&mpshare=1&scene=1&srcid=0808urpY9zSH24QHn9avvZkS&sharer_sharetime=1565273038980&sharer_shareid=696b16f7bf543a042f0c964a25755e34%23rd

innodb_large_prefix对查询性能的影响

前段时间在生产环境遇到个生产问题，开发反应业务系统响应时间很慢，CPU使用率达到百分之百，严重影响到了业务

问题排查

CPU百分之百，首先想到了慢SQL，将慢SQL取出，发现当天慢SQL文件多达60几个(RDS云环境，自己做了分割)，查看文件发现出现大量相同的SQL语句

这个SQ是用在登录认证， 根据token查询用户信息，按理说这种查询理论上应该效率是非常高的，查看了执行计划如下:

通过执行计划我们看到了，rows扫描行数还是比较多，跟想象的还是有差距，我们认为应该是根据token唯一定位到某条记录，扫描的行数应该是很少的。
查看表结构，发现token这个字段类型是varchar(4000)，默认情况下innodb下创建的索引最大长度是767字节，大概原因我们就知道了，由于字段长度过长，导致创建索引的长度有限，这样实际索引的区分度就非常低了，每次查询都要扫描很多的行，加上这个查询是一个高频的查询，导致了系统运行缓慢。

问题解决

这个问题有几种解决方式:

• 第一种:
  修改数据库参数innodb_large_prefix设置为on，这样可以将创建索引的长度扩大到3072，这样可以提高索引数据的区分度，每次查询时扫描的行数就会降低。当天晚上修改了此参数后，查看执行计划rows扫描的行数就降至到了95，并且第二天也无此SQL的慢查询，慢查询日志文件也从前一天的60个文件较少到了1个

• 第二种:
  通过增加缓存的方式，将token放到缓存中，减少对数据库的访问次数

update 子查询一则优化

问题描述

同事找到我，说有个SQL UPDATE 语句，在线上执行了26分钟，都没有执行完成，让我优化下。
数据库版本是5.7， 服务器配置4核32G的虚拟机

分析过程

首先就是让同事explain看下执行计划:

具体的SQL如下:

1
2

update survey_state set financ_requirements = 1 where org_credit_code in (SELECT     org_credit_code   FROM    answer_info a     LEFT JOIN answer_detail b       ON a.id = b.answer_info_id   WHERE b.question_id = 'f32825fa-bd98-11e9-b26c-fa163e4c9a89'     AND b.selection_id = '0d4ed3a9-bd9b-11e9-b26c-fa163e4c9a89');

这个UPDATE带了一个where条件， 里面是一个子查询，执行计划中也可以看到b表是全表扫描，所以首先在b表上创建一个索引

1

create index idx_question_selection_id on answer_detail(question_id, selection_id);

同时也在survey_state表中的org_credit_code字段上创建了个索引:

1

create index idx_org_credit_code on survey_state(org_credit_code);

创建索引后让同事再次执行，本以为会很快完成，但是还是需要很长时间。

再次分析执行计划

再次看执行计划，发现有DEPENDENT SUBQUERY，出现这个代表并不是先执行完子查询(过滤一部分数据)后再与外表做关联，而是外表扫描一条与里面子查询做匹配，然后在做过滤，这样效率自然会低下一些，所以将子查询改成关联查询:

1

UPDATE   survey_state t1,  (SELECT     org_credit_code   FROM    answer_info a     LEFT JOIN answer_detail b       ON a.id = b.answer_info_id   WHERE b.question_id = 'f32825fa-bd98-11e9-b26c-fa163e4c9a89'     AND b.selection_id = '0d4ed3a9-bd9b-11e9-b26c-fa163e4c9a89') t2 SET  t1.financ_requirements = 1 WHERE t1.org_credit_code = t2.org_credit_code ;

再次执行，几秒钟完成:

MySQL Group By中的松散索引和紧凑索引

最近看官方文档中发现，有两个索引扫描方式:松散索引和紧凑索引，以前也是看到过，但是一直没有去详细的了解，这次看了下，发现官方文档中说的也不是很清楚，就自己总结下对这两种索引扫描方式的理解

松散索引(Loose Index Scan)

在官方文档中提到，当有Group By子句时，在特定的情况下可以使用松散索引，减少扫描的次数。官方文档中给出了以下的一些情况可以使用到松散索引:

1
2
3
4
5
6
7

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

根据官方文档的描述，也就是当语句能满足索引”最左原则”时就可以使用到松散索引。创建个表用于测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) NOT NULL,
  `c4` int(11) DEFAULT NULL,
  KEY `idx` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中数据如下:
root@localhost:mysql-8.0-3319.sock  10:30:32 [dhy]>select * from t1;
+------+------+----+------+
| c1   | c2   | c3 | c4   |
+------+------+----+------+
|    1 |    2 |  1 |    3 |
|    1 |    2 |  2 |    3 |
|    1 |    2 |  3 |    3 |
+------+------+----+------+
3 rows in set (0.01 sec)

以”SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;”这个语句为例，查看其执行计划:

1
2
3
4
5
6
7

root@localhost:mysql-8.0-3319.sock  10:32:37 [dhy]>explain SELECT c1, c2 FROM t1 WHERE c3 = 1 GROUP BY c1, c2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx           | idx  | 14      | NULL |    2 |   100.00 | Using where; Using index for group-by |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

执行计划中Extra这列中出现了 Using index for group-by，代表使用了松散索引，但为何这个语句能用到松散索引，松散索引是到底是什么呢？
个人对松散的理解，就像是跳跃索引，”SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;”这个语句group by c1,c2遵循索引的”最左原则”c1,c2这两列是有序排列的，则可以先对前两列distinct，再过滤c3这个字段，这样减少了前两列的扫描次数。

紧凑索引(Tight Index Scan)

紧凑索引就像是进行了索引全表扫描，以下这两个语句不能使用松散索引，但是可以使用紧凑索引扫描:

1
2

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

先看下执行计划:

1
2
3
4
5
6
7

root@localhost:mysql-8.0-3319.sock  11:00:42 [dhy]>explain SELECT c1, c2, c3 FROM t1 WHERE c2 = 2  GROUP BY c1, c3;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | idx           | idx  | 14      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

这里type是index，也就是索引全表扫描，并且Extra里没有Using index for group-by，为何这里不能利用松散索引呢？因为group by字段是c1 和 c3两个字段，中间出现了GAP，所以无法按”SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;”这个SQL一样先distinct c1 ，c2这两个字段，因为c1,c3两个字段组合起来不一定是排序的，只能逐条扫描然后再过滤c2字段的值。例如数据是这样的:

1
2
3
4
5
6
7
8
9
10
11
12

+------+------+----+------+
| c1   | c2   | c3 | c4   |
+------+------+----+------+
|    1 |    1 |  3 |    3 |
|    1 |    2 |  1 |    3 |
|    1 |    2 |  2 |    3 |
|    1 |    3 |  1 |    3 |
|    2 |    1 |  2 |     3 |
|    2 |    2 |  1 |     3 |
|    2 |    3 |  3 |    3 |
+------+------+----+------+

如果按c1和c3两个字段先group by则数据会是这样的:

1
2
3
4
5
6
7
8

c1 c3 c2
1   3   1
1   1   2
1   2   2
1   1   3
2   2   1
2   1   2
2   3   3

显然c1 和c3 这两列的数据不是排序好的(1,1,2)，(1,2,2)，(1,1,3)，所以如果要做distinct操作则需要进行一次排序操作，或者根据c1这一列开始做索引的全表扫描，这样就保证了数据是有序的。

总结

松散索引其实就是遵循最左原则，这样数据都是有序的，可以不进行排序直接distinct，然后过滤where条件的字段。而紧凑索引数据是无序的，需要进行索引全扫描，会造成多扫描一些数据。

MySQL中组合索引使用小结

总结一些MySQL中组合索引，什么情况下会走索引，什么情况下无法走到索引

测试表结构及数据

1
2
3
4
5
6
7
8
9
10

CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into t values (1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3);
下面这两条语句是为了测试场景1中，能出现跳跃索引扫描
INSERT INTO t SELECT a, b + 5 FROM t;
INSERT INTO t SELECT a, b + 10 FROM t;

场景1where条件后只带有组合索引的第二个字段

1
2
3
4
5
6
7

root@localhost:mysql-8.0-3319.sock  16:26:33 [dhy]> explain select a,b from t where b = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_a_b       | idx_a_b | 10      | NULL |    3 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

从执行计划中可以看到，key这列显示使用到索引，同时key_ley的长度是10，也就是使用到了组合索引的所有字段，Extra中显示走了跳跃索引扫描(Using index for skip scan)，如果是8.0以下的版本则不会出现跳跃索引，而是索引全表扫描。
这里执行时的流程应该是如下发生:

• 获取组合索引中，第一列去重之后的第一个值(本例子中就是a=1)

• 根据此值与组合索引中的第二列构成一个查询条件(a=1 and b = 2)

• 执行查询

• 获取组合索引中，第一列去重之后的第二个值(本例子中就是a=2)

• 根据此值与组合索引中的第二列构成一个查询条件(a=2 and b = 2)

• 执行查询

反复按以上规则执行，直到第一列去重之后的值全部扫描完成

场景2where条件后第一列是范围查询

1
2
3
4
5
6
7

mysql> explain select * from t where a > 1 and b = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_a_b       | idx_a_b | 5       | NULL |    6 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

从这个执行计划中看到，key_len是5，也就是只使用到了组合索引的一部分，其实这里只使用到了组合索引的前半部分。
为何只能使用前半部分呢？我们再看下表中数据情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> select * from t ;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    2 |    1 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    3 |
+------+------+
9 rows in set (0.00 sec)

当a>1这个where条件过滤后，数据就变成了

1
2
3
4
5
6
7
8
9
10
11
12

mysql> select * from t where a > 1 ;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    3 |
+------+------+
6 rows in set (0.00 sec)

这里a字段是排序的，但是b字段则是无序的，所以第二列是无法用到索引的。

同理我们就知道了，只要第一列是范围查询，后面的第二列则无法用到索引，向下面这种order by和group by也是同样的

1
2
3
4
5
6
7

mysql> explain select * from t where a > 1 order by b ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_a_b       | idx_a_b | 5       | NULL |    6 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到这里使用到了排序，在MySQL8.0中explain 后面加上format=tree可以更加清楚看到执行顺序:

1
2
3
4
5
6
7
8
9
10

oot@localhost:mysql-8.0-3319.sock  22:07:57 [dhy]>explain format=tree select * from t where a > 1 order by b ;
+---------------------------------------------------------------------------------------+
| EXPLAIN                                                                               |
+---------------------------------------------------------------------------------------+
| -> Sort: t.b
    -> Filter: (t.a > 1)
        -> Index range scan on t using idx_a_b
 |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

先根据索引做范围查询，过滤掉不满足条件(a>1)的数据，然后在根据b字段排序

场景3where条件后第一列是等值查询

1
2
3
4
5
6
7

mysql> explain select * from t where a =1 and b = 2 ;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ref  | idx_a_b       | idx_a_b | 10      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这个执行计划就非常好了，用到了索引，并且key_len是10，同时select_type是ref代表是索引扫描(不是全扫描)。
之所以这里能使用组合索引两个字段做索引扫描的原因，是因为根据第一例的值查询后，第二列的值也是排序的，所以能利用到第二列的索引

Group Replication中同步的几个过程

MGR组复制大家都知道是一个数据强一致性的架构，但是这个强一致性是最终一致性，也就是在正常的复制过程中各个节点之间还是存在延迟，这篇文章目的是梳理下MGR的几个同步过程。

Group Replication数据同步的几个阶段

MGR中数据同步可以分为以下几个阶段:

• locally applying
• generating a binlog event
• sending the binlog event to the slave(s)
• adding the binlog event on the relay log
• applying the binlog event from the relay log

这几个阶段中只有sending the binlog event to the slave这个过程是一个同步过程(要等待包过自己在内的大部分节点返回ACK)，这个过程包含了数据冲突检测。

locally applying

这个阶段也就是本地事务执行，具体步骤如下:

首先开启事务

在事务内执行一些语句

generating a binlog event

这个阶段主要是生产binlog event ，用于MGR将他封装成write set 通过paxos原子广播到各个节点

当事务提交时会产生 binlog event

sending the binlog event to the slave(s)

发送binlog event到各个节点(其实也就是write set)，但这个步骤是一个同步的过程, 这里就用了paxos协议将数据有序的广播出去，需要有包括自己在内的大多数节点返回ack之后才会继续

每个节点收到发送到的binlog event 就要在自己的本地做冲突检测

adding the binlog event on the relay log/applying the binlog event from the relay log

当node1节点，完成冲突检测后，node1节点上的事务commit就可以继续执行了

从上面这个图可以看出，node1上面可以继续commit了，不用等待别的节点完成冲突检测，或者是应用完事务

别的节异步的应用队列中的数据，并且可以看到数据在不同的节点上提交的时间也是不一样的

总结

通过上面的步骤图我们了解了MGR同步的几个阶段，知道了MGR的数据强一致性并不是需要等到每个节点都应用完事务，而是只在发送binlog
event做冲突校验这个阶段是同步的过程，其余阶段都是异步的，也就是各个节点读取到的数据可能存在延迟，所以在MySQL8.0.14中增加了group_replication_consistency参数，用于一致性读

参考资料:

https://lefred.be/content/mysql-group-replication-synchronous-or-asynchronous-replication/