随便写写
0%

Posted on Edited on

MySQL中的统计信息相关参数介绍

统计信息的作用

上周同事在客户现场遇到了由于统计信息的原因导致应用数据迁移时间过慢，差点就导致了迁移失败。最后同事彭许生发现测试环境与生产环境SQL语句执行计划不一致，立刻收集统计信息才保证迁移能正常完成。
统计信息对于SQL的执行时间有重要的影响，统计信息的不准确会导致SQL的执行计划不准确导致SQL执行时间变慢，Oracle DBA非常了解统计信息的收集规则，在MySQL中也有相关的参数去控制统计信息。

相关参数

innodb_stats_auto_recalc

控制innodb是否自动收集统计信息，默认是打开的。当表中数据变化超过%10时候，就会重新计算统计信息。参数的生效依赖于建表时指定innodb_stats_persistent是打开的或CREATE TABLE , ALTER TABLE 时指定STATS_PERSISTENT=1采样page的个数通过参数innodb_stats_persistent_sample_pages来控制。

测试验证

创建一张测试表，并在表上创建一个索引:

1
2
create table dhytest (id int) STATS_PERSISTENT=1;
create index idx_id on dhytest(id);

通过mysql.innodb_index_stats可以查看索引最后收集统计信息的时间,这里的聚集索引我们删除先不用去看，只看自己创建的二级索引

1
2
3
4
5
6
7
8
9
10
[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:43]>select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_diff_pfx01 |          0 |           1 | id                                |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_diff_pfx02 |          0 |           1 | id,DB_ROW_ID                      |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)

我们手工往表中插入数据，让数据的变化超过%10

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

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:56]>insert into dhytest values (10);
Query OK, 1 row affected (0.00 sec)

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:17]>insert into dhytest select * from dhytest;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:28]>insert into dhytest select * from dhytest;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:31]>insert into dhytest select * from dhytest;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:34]>insert into dhytest select * from dhytest;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:35]>insert into dhytest select * from dhytest;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

这时我们在查看下mysql.innodb_index_stats表,last_update时间发生了变化

1
2
3
4
5
6
7
8
9
10
[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:36]>select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_diff_pfx01 |          1 |           1 | id                                |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_diff_pfx02 |          2 |           1 | id,DB_ROW_ID                      |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)

innodb_stats_persistent

控制是否将统计信息持久到磁盘当中，设置此参数之后我们就不需要实时去收集统计信息了，因为实时收集统计信息在高并发下可能会造成一定的性能上影响，并且会导致执行计划有所不同。建议是将此参数打开，将innodb_stats_auto_recalc参数进行关闭。

innodb_stats_persistent_sample_pages

控制收集统计信息时采样的page数量，默认是20。收集的page数量越多，每次收集统计信息的实际则越长，但是统计信息也相对比较准确。

我们可以在创建表的时候对不同的表指定不同的page数量、是否将统计信息持久化到磁盘上、是否自动收集统计信息

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY  (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
  STATS_PERSISTENT=1,
  STATS_AUTO_RECALC=1,
  STATS_SAMPLE_PAGES=25;

innodb_stats_on_metadata

此参数在5.6.5版本之前是默认开启的，设置此参数后当我们执行show index 或者 show table status 或者访问INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS表时就会收集统计信息，但是这样可能会导致执行计划改变。
在以前当表中记录变化超过1/16就会收集统计信息，但是现在如果设置了innodb_stats_persistent就不会有这样的说法了。

innodb_stats_include_delete_marked

5.6.35版本中新增的参数，就是在未提交的事务中如果我们删除了记录，收集统计信息的时候是排查这些删除了的记录的。这样就可能导致统计信息并不是很准确，设置此参数之后就是收集统计信息的时候包含未提交事务中被标记为已删除的数据。

innodb_stats_method

控制统计信息针对索引中NULL值的算法
当设置为nulls_equal 所有的NULL值都视为一个value group
当设置为nulls_unequal每一个NULL值被视为一个value group
设置为nulls_ignored时 NULL值被忽略
这个参数同事彭许生做过一些测试发现nulls_equal和nulls_unequal没有发现show index中的cardinality有不同的地方，但是如果设置为nulls_ignored的时候会有所不同

测试

表结构数据

设置为nulls_ignored

设置为nulls_unequal

设置为nulls_equal

推荐配置

innodb_stats_method 统计信息的自动收集在高并发情况下可能会带来性能的抖动，建议将此参数关闭。
innodb_stats_persistent 建议打开此参数将统计信息持久化到磁盘上
innodb_stats_include_delete_marked建议设置开启，这样可以针对未提交事务中删除的数据也收集统计信息
innodb_stats_method经过测试和mos查看到的按默认配置就可以，当然如果设置nulls_ignored时候会让你的语句走到索引，但是效率并不一定是好的

Posted on Edited on

MySQL的一个表最多可以有多少个字段

问题由来

引用我们客户的原话：
创建如下表，提示我：

1
2
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

如果我将下面表中的varchar（200），修改成text（或blob）：报错变为另一个：

1
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

我们查阅了很多的资料，不确定The maximum row size到底是65535 还是8126？原理是什么？

三种报错的疑惑

我们整理了一下，其实类似的错误有三种：

  • 错误1 创建表报maximum row size > 65535
    1
    [Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  • 错误2 创建表报Row size too large (> 8126)
    1
    Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
  • 错误3 表创建成功但是插入报 Row size too large (> 8126)
    1
    ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

到底要闹哪样

这么多错误，还都不一样，MySQL到底要闹那样
别急，一个问题一个问题的看

错误1:

这个报错其实我们查询MySQL官方手册就可以查询到， 对于一行记录最大的限制是65535字节。为什么是65535，不要问我，手册也没说:)—-一行数据里面字段长度定义有64k，我也是醉了

错误2:

既生瑜何生亮？有了65535的限制以后还有一个8126的限制是为什么列？

MySQL是分两层的，MySQL Server层 + 存储引擎层。
第2个问题其实是MySQL除了在Server层做了一次限制还会在Innodb存储引擎层在做一次限制。

innodb为了保证B+TREE是一个平衡树结构，强制要求一条记录的大小不能超过一个页大小的一半。这也就是我们上面看到的第二个错误。

下面是innodb B+树的结构，我们可以想象一下二分查找时，一个页的只有一条数据会是什么样子？

每个页只有一条数据的查找就变成了链表查找了。这样就没有二分查找的意义了。
而MySQL中默认的页大小是16K，16K的一半是8196字节减去一些元数据信息就得出了8126这个数字。
这就是8126的由来

错误3:

突破错误2

8126是不是不能突破的列？

我们这里就有个案例：按照附1的建表语句建立一个150个字段，每个字段是100个字符（特地使用了ASCII字符集，这样一个字符就是一个字节）的表。

150 * 100=15000 > 8126。按照上面的说法，应该要报错的，
但是各位可以在自己的数据库上试一下，表能够建立成功，这是为什么列？

其实MySQL在计算字段长度的时候并不是按照字段的全部长度来记的。
列字段小于40个字节的都会按实际字节计算，如果大于20 * 2=40 字节就只会按40字节。

对应到MySQL代码中storage/innobase/dict/dict0dict.cc的dict_index_too_big_for_tree()中:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
field_max_size = dict_col_get_max_size(col);
field_ext_max_size = field_max_size < 256 ? 1 : 2;


if (field->prefix_len) {
    if (field->prefix_len < field_max_size) {
        field_max_size = field->prefix_len;
    }
} else if (field_max_size > BTR_EXTERN_FIELD_REF_SIZE * 2
     ¦ && dict_index_is_clust(new_index)) {


    /* In the worst case, we have a locally stored
    column of BTR_EXTERN_FIELD_REF_SIZE * 2 bytes.
    The length can be stored in one byte.  If the
    column were stored externally, the lengths in
    the clustered index page would be
    BTR_EXTERN_FIELD_REF_SIZE and 2. */
    field_max_size = BTR_EXTERN_FIELD_REF_SIZE * 2;
    field_ext_max_size = 1;
}

也就是说，如果字段长度超过BTR_EXTERN_FIELD_REF_SIZE * 2，字段就只算20 * 2=40(BTR_EXTERN_FIELD_REF_SIZE=20)
举例如下:

  • 创建一个300个字段长度类型为varchar(30)的表，在创建时不会创建成功。因为varchar(30)没有超过202，那么总长度就是30030=9000 > 8126就会创建失败。
  • 创建一个150个字段长度类型为varchar(100)的表可以创建成功。因为varchar(100) 大于了202那么就只会按40计算 总长度就是15020*2=6000 < 8126 就会创建成功。

这个20字节是不是看着有点眼熟，可以联系到InnoDB的一个参数：innodb_file_format。该参数用于设置Innodb表内部存储的文件格式，该参数可设置为Antelope,Barracuda两种格式。

  • Antelope是MySQL原始的记录格式，是较古老的记录格式。
    在这种格式记录下Innodb 对于大字段的处理如下:

对于大字段，innodb只会存放前DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节在数据页中，超过768字节都会放到溢出页中。这种方式也是B+TREE结构，但是也并不是完美的，因为我们将大字段存放到了数据页中会造成叶子节点的个数会很多，同样会造成非叶子节点的的个数增加。最终导致索引层级增高，访问IO次数增加。

  • Barracuda格式是InnoDB新的存储格式
    他的存储方式如下:

在Barracuda格式下，会用20字节的指针指向溢出页，这样做的好处就是不会造成索引层级的增高。

回到错误3

回归正题，第二个错误我们可以越过去，但是我们是不是能够真的插入150个100字符的字段列。
用附2的插入语句试一下就知道，错误3也会报错出来。
也就是说表可以创建成功但是插入却失败，原因如下：

  • Antelope格式下的COMPACT大字段按照DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节溢出页。varchar(100)没有存储为溢出页。
  • Barracuda的DYNAMIC和COMPRESSED格式下只有长字段才会用20字节溢出页的方式，varchar(100)也没有存储为溢出页。

引用reference的原文如下：

1
2
3
4
When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long variable-length column values (for VARBINARY,VARCHAR, BLOB, and TEXT columns) are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page. InnoDB will also store long CHAR column values off-page if the column value is greater than or equal to 768 bytes, which can occur when the maximum byte length of the character set is greater than 3, as it is with utf8mb4, for example.

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDBchooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

总结

总的来说，

  • MySQL Server最多只允许4096个字段
  • InnoDB 最多只能有1000个字段
  • 字段长度加起来如果超过65535，MySQL server层就会拒绝创建表
  • 字段长度加起来（根据溢出页指针来计算字段长度，大于40的，溢出，只算40个字节）如果超过8126，InnoDB拒绝创建表
  • 表结构中根据ROW_FORMAT的存储格式确定行内部保留的字节数（20 VS 768），最终确定一行数据是否小于8126，如果大于8126，报错。

建议

  1. 放弃使用Antelope这种古老的存储格式吧，原因上面也说到了把大字段的前768字节放在数据页中，这样会导致索引的层级很高，会直接影响到查询的性能。
  2. 对于大字段类型建议单独存放到一张表中，不要与经常访问的表放在一起，会造成物理IO的增加。

参考

MySQL reference: Limits on Table Column Count and Row Size
MySQL reference: innodb row format dynamic
mysqlserverteam:Externally Stored Fields in InnoDB

MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构

附1. 建表语句

附上测试的建表语句和insert语句，有兴趣的朋友可以自己按照上面的几种方式在Antelope和Barracuda的几种不同ROW_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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
create table xjblg1
(
  jydm                         VARCHAR(100),
  rq                           VARCHAR(100),
  cb                           VARCHAR(100),
  khdkjdkjjse                  VARCHAR(100),
  xyhjkjzje                    VARCHAR(100),
  khckhtycfkxjzje              VARCHAR(100),
  qzkhck                       VARCHAR(100),
  tyjqtjrjgcfk                 VARCHAR(100),
  shcftyjqtjrjgje              VARCHAR(100),
  crzjxjlr                     VARCHAR(100),
  shdcczjje                    VARCHAR(100),
  xsdmchgssddxj                VARCHAR(100),
  shdmrfsxje                   VARCHAR(100),
  czjyxjrzcjzje                VARCHAR(100),
  sqlxsxfjyjdxj                VARCHAR(100),
  sdlx                         VARCHAR(100),
  sddsxf                       VARCHAR(100),
  jrqywlsrsddxj                VARCHAR(100),
  zjywsrsddxj                  VARCHAR(100),
  zqtzsysddxj                  VARCHAR(100),
  hdjsysddxj                   VARCHAR(100),
  qtyyjyywjsrsddxj             VARCHAR(100),
  crbzjsddxj                   VARCHAR(100),
  sddwtzj                      VARCHAR(100),
  gjsxjlr                      VARCHAR(100),
  txsddxj                      VARCHAR(100),
  xssptglwsddxj                VARCHAR(100),
  sddsffh                      VARCHAR(100),
  qtfzzjlrdxj                  VARCHAR(100),
  shdyyyqndhxddkjyskx          VARCHAR(100),
  qtyfzskjslcdxj               VARCHAR(100),
  czdzzcsddxj                  VARCHAR(100),
  sdqtyjyhdygdxj               VARCHAR(100),
  jyhdxjlrxj                   VARCHAR(100),
  khdkjdkjzje                  VARCHAR(100),
  cfzyyhhtykxjzje              VARCHAR(100),
  cfzyyh                       VARCHAR(100),
  cftyjqtjgck                  VARCHAR(100),
  cczjjxjlc                    VARCHAR(100),
  chzyyhjkxzyyhjkjjse          VARCHAR(100),
  zfdck                        VARCHAR(100),
  jstyjqtjrjgcfje              VARCHAR(100),
  chtyjqtjrjgcrje              VARCHAR(100),
  chmchgkxje                   VARCHAR(100),
  zfmrfskxje                   VARCHAR(100),
  czkgcsjrzcjjse               VARCHAR(100),
  zflxsxfjyjdxj                VARCHAR(100),
  zfdlx                        VARCHAR(100),
  sxfzczfdxj                   VARCHAR(100),
  zfgzgyjwzgzfdxj              VARCHAR(100),
  zfdgxsf                      VARCHAR(100),
  yxjzfdyyfy                   VARCHAR(100),
  yjrjgwlzcdxjje               VARCHAR(100),
  txzfdxj                      VARCHAR(100),
  yhxdzdkjlxsh                 VARCHAR(100),
  gjsxjlc                      VARCHAR(100),
  gmspjslwzfdxj                VARCHAR(100),
  jrqtzjjjse                   VARCHAR(100),
  qtzcjszcdxj                  VARCHAR(100),
  qtyszfkjsshdxj               VARCHAR(100),
  zfqtyjyhdygdxj               VARCHAR(100),
  jyhdxjlcxj                   VARCHAR(100),
  jyhdcsdxjllje                VARCHAR(100),
  shtzsddxj                    VARCHAR(100),
  qdtzsysddxj                  VARCHAR(100),
  qzfdglhlrssddxj              VARCHAR(100),
  czgdzcwxzcjqtzcesddxj        VARCHAR(100),
  czgqtzssddxj                 VARCHAR(100),
  qdzgsjqtyydwssddxjje         VARCHAR(100),
  sdqtytzhdygdxj               VARCHAR(100),
  tzhdxjlrxj                   VARCHAR(100),
  tzzfdxj                      VARCHAR(100),
  qyxtzzzzfdxj                 VARCHAR(100),
  zqtzszfdxj                   VARCHAR(100),
  gmzgslyqyjhyqytzszfdxjje     VARCHAR(100),
  zjzjgcszfdxj                 VARCHAR(100),
  gjgdzcwxzchqtcqzczfdxj       VARCHAR(100),
  qdzgsjqtyydwzfdxjje          VARCHAR(100),
  zfdqtytzhdygdxj              VARCHAR(100),
  tzhdxjlcxj                   VARCHAR(100),
  tzhdcsdxjllje                VARCHAR(100),
  xstzssddxj                   VARCHAR(100),
  fxzqhzcsxsdxj                VARCHAR(100),
  qdjksddxj                    VARCHAR(100),
  fxzqsddxj                    VARCHAR(100),
  qzfxcjzqsddxj                VARCHAR(100),
  zjgbssddxj                   VARCHAR(100),
  sdqtyczhdygdxj               VARCHAR(100),
  czhdxjlrxj                   VARCHAR(100),
  chzwszfdxj                   VARCHAR(100),
  fpgllrhcflxzfdxj             VARCHAR(100),
  qzcflxszfdxj                 VARCHAR(100),
  zfxgfxfy                     VARCHAR(100),
  zfqtyczhdygdxj               VARCHAR(100),
  czhdxjlcxj                   VARCHAR(100),
  czhdcsdxjllje                VARCHAR(100),
  hlbddxjjxjdjwdyx             VARCHAR(100),
  xjjxjdjwjzje                 VARCHAR(100),
  qcxjjxjdjwye                 VARCHAR(100),
  qmxjjxjdjwye                 VARCHAR(100),
  jlr                          VARCHAR(100),
  ssgdqy                       VARCHAR(100),
  qzjtdhzzb                    VARCHAR(100),
  jtddksszb                    VARCHAR(100),
  jtdzcjzzb                    VARCHAR(100),
  chcftyjzzb                   VARCHAR(100),
  jtdzzcjzzb                   VARCHAR(100),
  jtqtzcjzzb                   VARCHAR(100),
  gdzczjyqzcchscxswzccj        VARCHAR(100),
  tzxfdczj                     VARCHAR(100),
  wxzcdyzcjqtzcdtx             VARCHAR(100),
  qzwxzctx                     VARCHAR(100),
  cqdtfytx                     VARCHAR(100),
  czgdzcwxzchqtcqzcdsssy       VARCHAR(100),
  cztzxfdcdsssy                VARCHAR(100),
  gdzcbfss                     VARCHAR(100),
  cwfy                         VARCHAR(100),
  dtfydjszj                    VARCHAR(100),
  ytfydzjjs                    VARCHAR(100),
  tzssjsy                      VARCHAR(100),
  gyjzbdsyss                   VARCHAR(100),
  hdsy                         VARCHAR(100),
  ysjrgjjyjsy                  VARCHAR(100),
  chdjs                        VARCHAR(100),
  dkdjs                        VARCHAR(100),
  ckdzj                        VARCHAR(100),
  cjkxdjz                      VARCHAR(100),
  yjfzdzj                      VARCHAR(100),
  sdyhxkx                      VARCHAR(100),
  jrfzdzj                      VARCHAR(100),
  dysdszcdjs                   VARCHAR(100),
  dysdsfzdzj                   VARCHAR(100),
  jyxysxmdjs                   VARCHAR(100),
  jyxyfxmdzj                   VARCHAR(100),
  jyxqtzcdjs                   VARCHAR(100),
  jyxqtfzdzj                   VARCHAR(100),
  qt                           VARCHAR(100),
  jyhdxjllje                   VARCHAR(100),
  ygdzcchzw                    VARCHAR(100),
  ytzchzw                      VARCHAR(100),
  ygdzcjxtz                    VARCHAR(100),
  zyzwzb                       VARCHAR(100),
  ynndqdkzhgszq                VARCHAR(100),
  rzzrgdzc                     VARCHAR(100),
  qtbsjxjszdtzhczhdje          VARCHAR(100),
  xjdqmye                      VARCHAR(100),
  jxjdqcye                     VARCHAR(100),
  xjdjwdqmye                   VARCHAR(100),
  jxjdjwdqcye                  VARCHAR(100),
  xjjxjdjwjzje1                VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;;

附2. insert 语句

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
insert into xjblg1 values(
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100));

Posted on Edited on

参数描述

MySQL中不同的版本优化器会有很多新特性，比如MRR、BKA等，optimizer_switch这个参数就是控制查询优化器怎样使用这些特性。很多情况下我们会根据自身的需求去设置optimizer_switch满足我们的需求。前段时间客户的环境中遇到一个奇怪的问题，select count(*)显示返回是有数据但是select * 返回是空结果集，最终的原因就是因为optimizer_switch设置引起了一个BUG。这里和大家分享下防止大家也遇到同样的坑。

案例分析

环境描述

数据库版本MySQL5.6.35

SQL语句

1
select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;

分析过程

当时凌晨4点左右客户打来电话告知数据库查询不到数据了非常着急，我们赶到了现场，当时的现象是这样的:

1
select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;

这条语句查询返回的结果集是空，但是开发人员和我们说数据库中是有数据的，我当时不相信就执行了一下:

1
2
3
4
5
6
7
8
9
10
select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
Empty set (0.41 sec)

select count(*) from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
+----------+
| count(*) |
+----------+
|      475 |
+----------+
1 row in set (0.41 sec)

当时也是比较慌张了，count(*)显示返回475条记录但是select *却返回空结果集。。。
想了一下SQL语句有一层嵌套，我看看里面这个SQL是否有问题，测试后发现内层语句可以正常返回，加上外层语句时就会出现这种情况。询问了应用人员系统刚迁移过来，在原系统没有这种情况，快速连到原系统上执行同样的语句对比一下两边的执行计划:
原系统

1
2
3
4
5
6
7
8
9
10
11
12
13
explain  select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+-----------------------------+
| id | select_type | table      | type   | possible_keys          | key               | key_len | ref               | rows  | Extra                       |
+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+-----------------------------+
|  1 | PRIMARY     | <derived2> | ref    | <auto_key0>            | <auto_key0>       | 153     | const             |    10 | Using where; Using filesort |
|  2 | DERIVED     | o          | range  | idx_orderdatetime      | idx_orderdatetime | 6       | NULL              | 46104 | Using index condition       |
|  2 | DERIVED     | mm         | eq_ref | PRIMARY,idx_memberid   | PRIMARY           | 8       | mall.o.buyerid    |     1 | NULL                        |
|  2 | DERIVED     | ms         | ref    | idx_userid             | idx_userid        | 9       | mall.o.salerid    |     1 | NULL                        |
|  2 | DERIVED     | mmt        | eq_ref | PRIMARY,idx_merchantid | PRIMARY           | 8       | mall.o.salerid    |     1 | NULL                        |
|  2 | DERIVED     | ma         | eq_ref | PRIMARY                | PRIMARY           | 8       | mall.o.activityid |     1 | NULL                        |
|  2 | DERIVED     | md         | ref    | idx_activityid         | idx_activityid    | 8       | mall.ma.actid     |     1 | NULL                        |
+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+-----------------------------+
7 rows in set (0.00 sec)

新系统

1
2
3
4
5
6
7
8
9
10
11
12
13
explain  select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+----------------------------------+
| id | select_type | table      | type   | possible_keys          | key               | key_len | ref               | rows  | Extra                            |
+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+----------------------------------+
|  1 | PRIMARY     | <derived2> | ref    | <auto_key0>            | <auto_key0>       | 153     | const             |    10 | Using where; Using filesort      |
|  2 | DERIVED     | o          | range  | idx_orderdatetime      | idx_orderdatetime | 6       | NULL              | 46104 | Using index condition; Using MRR |
|  2 | DERIVED     | mm         | eq_ref | PRIMARY,idx_memberid   | PRIMARY           | 8       | mall.o.buyerid    |     1 | NULL                             |
|  2 | DERIVED     | ms         | ref    | idx_userid             | idx_userid        | 9       | mall.o.salerid    |     1 | NULL                             |
|  2 | DERIVED     | mmt        | eq_ref | PRIMARY,idx_merchantid | PRIMARY           | 8       | mall.o.salerid    |     1 | NULL                             |
|  2 | DERIVED     | ma         | eq_ref | PRIMARY                | PRIMARY           | 8       | mall.o.activityid |     1 | NULL                             |
|  2 | DERIVED     | md         | ref    | idx_activityid         | idx_activityid    | 8       | mall.ma.actid     |     1 | NULL                             |
+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+----------------------------------+
7 rows in set (0.00 sec)

两边的执行计划不同的地方就是新系统使用了MRR，数据库的版本都是5.6.20之后的小版本号没有差很多应该不会出现这种情况。
想到了optimizer_switch这个参数可以设置mrr特性，是不是有人修改了他，对比了两边optimizer_switch这个参数发现mrr_cost_based这个值设置的不同。快速的将参数设置为一样再次查询:

1
2
3
4
5
6
7
8
set optimizer_switch='mrr_cost_based=on';
Query OK, 0 rows affected (0.00 sec)

select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
.
.
.

立刻就返回数据了。

小结

mrr_cost_based代表是否使用基于代价的方式去计算使用MRR特性，新的系统中将他设置为off代表不使用基于代价方式而是使用基于规则的，这样设置的原因是考虑到MySQL基于代价的方式比较保守不能使用到MRR这个特性。本身设置这个参数是没有任何问题，只不过是正好将遇mrr_cost_based设置为off时碰到了这么诡异BUG，希望可以帮助到遇到同样问题的朋友们。

Posted on Edited on

在我们的系统中可能很多朋友都会遇到主从复制数据不一致的情况，有时候跑着跑着就会这样。
我整理了几个参数，是为了保证主从数据一致性。希望对大家有帮助。

  • 首先介绍一下两个文件:

|文件名 | 作用 |
|————-|:———–:|:————–:|
|relay-log-info|记录SQL线程读取Master binlog的位置，用于Slave down机之后根据文件中记录的pos点恢复SQL线程|
|master-info|记录IO线程读取已经读取到的Master binlog位置，用于Slave down机之后IO线程根据文件中记录的pos点重新拉取binlog日志|

这两个文件在写的时候并不是实时更新，而是有两个参数进行控制。

参数名 含义
sync_relay_log_info 执行多少个事务后将relay-log-info，sync一下文件刷新到磁盘上
sync_master_info 执行多少个事务后将master-info sync一下文件刷新到磁盘上

为了保证文件中内容是实时更新的(如果不是实时更新必然会造成主备主备数据不一致，例如Slave down机时，我们执行了999个事务。这时master-info还并没有sync到磁盘上，Slave恢复后就会多拉取999个事务)，我们要将两个参数设置为1。
但是这里有一个问题，虽然是每一次都去sync到磁盘上，但是还是存在问题。

举个例子:

  1. 正常跑的Slave突然掉电了，但是最后一个事务已经commit成功了。但是可能还没有将sync_relay_log_info sync到磁盘上。因为sync文件这个动作并不是在事务中，不能得到保证。
  2. 等待Slave恢复之后，会去读取relay_log_info文件。就会将最后一个事务进程重做一遍，后面主备可能会正常运行但是数据会出现不一致。

MySQL中提供了两个参数:

参数名 含义
relay-log-info-repository 将relay-log-info记录到表中(slave_relay_log_info)
master-info-repository 将master-info记录到表中(slave_master_info)

当记录在表中并且sync_relay_log_info与sync_master_info设置为1的时候就不会出现上面那种情况，因为都是Innodb表有事务的保证。但是sync_master_info设置为1每一次都去刷新一次效率必然很低。
这里可能有人会有疑问sync_relay_log_info不是不需要设置为1嘛？MySQL官方文档中是这样写的:

从图中我们就明白了 relay-log-info-repository设置为记录在表中时，默认就是1.

MySQL5.6中有一个参数提供了一个参数:

  • relay_log_recovery
    这个参数的含义是，当Shalve 重启之后会根据slave_relay_log_info表重新创建一个文件，SQL线程会根据这个文件进行恢复复制，IO线程会读取SQL线程的pos点，根据这个pos点向主库申请拉取数据。

也就是我们只要设置了:
relay-log-info-repository = TABLE
relay_log_recovery = ON
就可以避免主从库数据不一致情况。

Posted on Edited on

MVCC介绍

我们首先介绍下什么是MVCC及解决了什么问题:

session 1 session 2
select a from test; return a = 10
start transaction;
update test set a = 20;
start transaction;
select a from test; return ?
commit;
select a from test; return ?
我们看下上面这个例子，当我们修改一条记录没有提交的同时，session 2 来进行查询这时候返回记录应该是多少呢？在session 1 提交之后 session 2 查询出来的又是多少呢？
这个问题是需要看事务隔离级别的，情况如下:
  • 隔离级别为 READ-UNCOMMITTED 情况下:
    session 1 commit前后 session 2 去查看都会看到的是修改后的结果 a = 20
  • 隔离级别为 READ-COMMITTED 情况下:
    session 1 commit 前查看到的还是 a =10 , commit之后看到的是 a = 20
  • 隔离级别为 REPEATABLE-READ， SERIALIZABLE 情况下:
    session 1 commit前后 session 2 去查看都会看到的是修改后的结果 a = 10

我们抛开数据库中的ACID，这里就涉及到一个问题 ， 修改后的数据数据库是怎么做到查询出来的结果还是之前的数据呢？
其实就是借助数据库中的UNDO和MVCC实现的

当插入一条数据时记录上对应的回滚段指针为NULL

当更新记录时将原记录放入到undo表空间中，我们查询返回未修改的数据就是从undo中返回的。MySQL中就是根据记录上的回滚段指针及事务ID判断记录是否可见。
具体的判断流程如下:
在每个事务开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)

具体代码

Innodb表存储

Inoodb表中会存有三个隐藏字段，这三个字段是mysql默认帮我们添加的。我们可以通过代码中查看到:

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
dict_table_add_system_columns(
/*==========================*/
dict_table_t*    table,    /*!< in/out: table */
mem_heap_t*    heap)    /*!< in: temporary heap */
{
ut_ad(table);
ut_ad(table->n_def == (table->n_cols - table->get_n_sys_cols()));
ut_ad(table->magic_n == DICT_TABLE_MAGIC_N);
ut_ad(!table->cached);

/* NOTE: the system columns MUST be added in the following order
(so that they can be indexed by the numerical value of DATA_ROW_ID,
etc.) and as the last columns of the table memory object.
The clustered index will not always physically contain all system
columns.
Intrinsic table don't need DB_ROLL_PTR as UNDO logging is turned off
for these tables. */

dict_mem_table_add_col(table, heap, "DB_ROW_ID", DATA_SYS,
      DATA_ROW_ID | DATA_NOT_NULL,
      DATA_ROW_ID_LEN);

#if (DATA_ITT_N_SYS_COLS != 2)
#error "DATA_ITT_N_SYS_COLS != 2"
#endif

#if DATA_ROW_ID != 0
#error "DATA_ROW_ID != 0"
#endif
dict_mem_table_add_col(table, heap, "DB_TRX_ID", DATA_SYS,
      DATA_TRX_ID | DATA_NOT_NULL,
      DATA_TRX_ID_LEN);
#if DATA_TRX_ID != 1
#error "DATA_TRX_ID != 1"
#endif

if (!table->is_intrinsic()) {
dict_mem_table_add_col(table, heap, "DB_ROLL_PTR", DATA_SYS,
      DATA_ROLL_PTR | DATA_NOT_NULL,
      DATA_ROLL_PTR_LEN);
#if DATA_ROLL_PTR != 2
#error "DATA_ROLL_PTR != 2"
#endif

/* This check reminds that if a new system column is added to
the program, it should be dealt with here */
#if DATA_N_SYS_COLS != 3
#error "DATA_N_SYS_COLS != 3"
#endif
}
}

DB_ROW_ID:如果表中没有显示定义主键或者没有唯一索引则MySQL会自动创建一个6字节的row id存在记录中
DB_TRX_ID:事务ID
DB_ROLL_PTR:回滚段指针

mysql中并不是根据事务的事务ID进行比较判断记录是否可见，而是根据每一行记录上的事务ID进行比较来判断记录是否可见。

我们可以通过实验验证 ， 创建一张表里面插入一条记录

1
2
3
4
5
6
7
dhy@10.16.70.190:3306  12:25:47 [dhy]>select * from dhytest;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (7.99 sec)

手工开启一个事务 更新一条记录 但是并不提交:

1
2
3
dhy@10.10.80.199:3306  15:28:24 [dhy]>update dhytest set id = 20;
Query OK, 3 rows affected (40.71 sec)
Rows matched: 3  Changed: 3  Warnings: 0

在另外一个会话执行查询

1
dhy@10.16.70.190:3306  12:38:33 [dhy]>select * from dhytest;

这时我们可以跟踪调试mysql 查看他是怎么判断记录的看见性，中间函数调用太多列举最重要部分
首先介绍一个重要的类 ReadView,Read View是事务开启时当前所有事务的一个集合,这个类中存储了当前Read View中最大事务ID及最小事务ID

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/** The read should not see any transaction with trx id >= this
value. In other words, this is the "high water mark". */
trx_id_t    m_low_limit_id;

/** The read should see all trx ids which are strictly
smaller (<) than this value.  In other words, this is the
low water mark". */
trx_id_t    m_up_limit_id;

/** trx id of creating transaction, set to TRX_ID_MAX for free
views. */
trx_id_t    m_creator_trx_id;


当我们执行上面的查询语句时,跟踪到主要函数如下:

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
函数row_search_mvcc->lock_clust_rec_cons_read_sees
bool
lock_clust_rec_cons_read_sees(
/*==========================*/
const rec_t*    rec,    /*!< in: user record which should be read or
passed over by a read cursor */
dict_index_t*    index,    /*!< in: clustered index */
const ulint*    offsets,/*!< in: rec_get_offsets(rec, index) */
ReadView*    view)    /*!< in: consistent read view */
{
ut_ad(index->is_clustered());
ut_ad(page_rec_is_user_rec(rec));
ut_ad(rec_offs_validate(rec, index, offsets));

/* Temp-tables are not shared across connections and multiple
transactions from different connections cannot simultaneously
operate on same temp-table and so read of temp-table is
always consistent read. */
//只读事务或者临时表是不需要一致性读的判断
if (srv_read_only_mode || index->table->is_temporary()) {
ut_ad(view == 0 || index->table->is_temporary());
return(true);
}

/* NOTE that we call this function while holding the search
system latch. */

trx_id_t    trx_id = row_get_rec_trx_id(rec, index, offsets); //获取记录上的TRX_ID这里需要解释下，我们一个查询可能满足的记录数有多个。那我们每读取一条记录的时候就要根据这条记录上的TRX_ID判断这条记录是否可见
return(view->changes_visible(trx_id, index->table->name)); //判断记录可见性
}

下面是真正判断记录的看见性。

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
bool changes_visible(
trx_id_t    id,
const table_name_t&    name) const
MY_ATTRIBUTE((warn_unused_result))
{
ut_ad(id > 0);

//如果ID小于Read View中最小的, 则这条记录是可以看到。说明这条记录是在select这个事务开始之前就结束的
if (id < m_up_limit_id || id == m_creator_trx_id) {

return(true);
}

check_trx_id_sanity(id, name);

//如果比Read View中最大的还要大，则说明这条记录是在事务开始之后进行修改的，所以此条记录不应查看到
if (id >= m_low_limit_id) {

return(false);

} else if (m_ids.empty()) {

return(true);
}

const ids_t::value_type*    p = m_ids.data();

return(!std::binary_search(p, p + m_ids.size(), id)); //判断是否在Read View中， 如果在说明在创建Read View时 此条记录还处于活跃状态则不应该查询到，否则说明创建Read View是此条记录已经是不活跃状态则可以查询到
}

对于不可见的记录都是通过row_vers_build_for_consistent_read函数查询UNDO构建老版本记录，直到记录可见。

这里需要说明一点 不同的事务隔离级别，可见性的实现也不一样:

  • READ-COMMITTED
    事务内的每个查询语句都会重新创建Read View，这样就会产生不可重复读现象发生
  • REPEATABLE-READ
    事务内开始时创建Read View ， 在事务结束这段时间内 每一次查询都不会重新重建Read View ， 从而实现了可重复读。

参考资料:
《唐成－2016PG大会-数据库多版本实现内幕.pdf》