关于使用innblock分析对innodb表进行update操作时delflag=N的疑惑

使用叶老师上课时提到的innblock工具分析update操作
(innblock操作参考此文:http://blog.itpub.net/7728585/viewspace-2145616/
 
具体流程如下:
1. 创建测试表并插入初始数据
root@localhost:[dbtest](17:37:15)>show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
 
root@localhost:[dbtest](17:37:31)>insert into a values(1,1),(2,1),(3,1);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
2. 使用innblock分析page
==== Block list info ====
-----Total used rows:5 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:125 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:151 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:177 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(5) SUPREMUM record offset:112 heapno:1 n_owned 4,delflag:N minflag:0 rectype:3
-----Total used rows:5 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 4,delflag:N minflag:0 rectype:3
(3) normal record offset:125 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:151 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:177 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
-----Total del rows:0 del rows list(logic):
-----Total slot:2 slot list:
(1) SUPREMUM slot offset:112 n_owned:4
(2) INFIMUM slot offset:99 n_owned:1
 
3. 加字段:
root@localhost:[dbtest](17:39:41)>alter table a add a varchar(10);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
此时分析page,跟上面一致,没有变化。
 
4. 新加列进行更新操作
root@localhost:[dbtest](17:40:11)>update a set a = 'zzz';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
此时分析page
==== Block list info ====
-----Total used rows:5 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(5) SUPREMUM record offset:112 heapno:1 n_owned 4,delflag:N minflag:0 rectype:3
-----Total used rows:5 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 4,delflag:N minflag:0 rectype:3
(3) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
-----Total del rows:3 del rows list(logic):
(1) normal record offset:180 heapno:4 n_owned 0,delflag:N minflag:0  rectype:0
(2) normal record offset:153 heapno:3 n_owned 0,delflag:N minflag:0  rectype:0
(3) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0  rectype:0
-----Total slot:2 slot list:
(1) SUPREMUM slot offset:112 n_owned:4
(2) INFIMUM slot offset:99 n_owned:1
 
发现更新操作进行类似于delete + insert操作
a. 删除的数据,产生了del row list, del rows list中的delflag=N(这里为何delflag=N,不太理解)
b. 新插入的数据,由于字段a由null变成'zzz',del row list中不能重用,产出新的heapno

5. 再新插入记录:
root@localhost:[dbtest](17:43:15)>insert into a values(4,1,'zzz');
Query OK, 1 row affected (0.01 sec)

==== Block list info ====
-----Total used rows:6 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:301 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
(6) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
-----Total used rows:6 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
(3) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:301 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
-----Total del rows:3 del rows list(logic):
(1) normal record offset:180 heapno:4 n_owned 0,delflag:N minflag:0  rectype:0
(2) normal record offset:153 heapno:3 n_owned 0,delflag:N minflag:0  rectype:0
(3) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0  rectype:0
-----Total slot:2 slot list:
(1) SUPREMUM slot offset:112 n_owned:5
(2) INFIMUM slot offset:99 n_owned:1
产生了新的heapno, 不会重用del rows list中的heapno
 
疑惑:
1. update操作时,产生的del rows list中的delflag为何要置为N,这导致后面insert操作无法重用,这样岂不是造成空间浪费? 感觉应该是delflag=Y才合理些
2. 这部分无法重用的空间似乎只能通过optimize table a 的方式进行碎片回收
 
 
已邀请:

yejr

赞同来自:

我的理解是这样的:
1. update操作时,产生的del rows list中的delflag为何要置为N,这导致后面insert操作无法重用,这样岂不是造成空间浪费? 感觉应该是delflag=Y才合理些
===
这些rows并不是被显式执行delete删除的,而是无法in-place update才被放到del rows list里(其实应该算是garbage off列表里),所以DELFLAG不是Y
为什么后面的insert不能被重用?因为新增的row长度比原来的要大,所以总是没法重用,你可以尝试 insert .. value (1,1, '') 这样试试看

2. 这部分无法重用的空间似乎只能通过optimize table a 的方式进行碎片回收
===
是的,碎片都得这种方式回收,但这些碎片有机会被重用
 

arron

赞同来自:

叶老师,我按你说的测试了一下,结果如下:
root@localhost:[dbtest](10:37:25)>select * from a;
+----+---+------+
| id | c | a    |
+----+---+------+
|  1 | 1 | zzz  |
|  2 | 1 | zzz  |
|  3 | 1 | zzz  |
+----+---+------+
3 rows in set (0.00 sec)
 
root@localhost:[dbtest](10:36:49)>insert into a values(4,1,'');
Query OK, 1 row affected (0.04 sec)
 
查看page信息
==== Block list info ====
-----Total used rows:6 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:301 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
(6) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
-----Total used rows:6 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
(3) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:301 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
-----Total del rows:3 del rows list(logic):
(1) normal record offset:180 heapno:4 n_owned 0,delflag:N minflag:0  rectype:0
(2) normal record offset:153 heapno:3 n_owned 0,delflag:N minflag:0  rectype:0
(3) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0  rectype:0
-----Total slot:2 slot list:
(1) SUPREMUM slot offset:112 n_owned:5
(2) INFIMUM slot offset:99 n_owned:1
 
发现仍然没有被重用,估计是新插入的列比原来的多。
 
但是如果insert语句变成这样
root@localhost:[dbtest](11:00:41)>insert into a(id, c) values (5,1);
Query OK, 1 row affected (0.03 sec)
 
insert的字段数和长度与update之前的一致时,发现可以被重用
==== Block list info ====
-----Total used rows:7 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:301 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:180 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(7) SUPREMUM record offset:112 heapno:1 n_owned 6,delflag:N minflag:0 rectype:3
-----Total used rows:7 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 6,delflag:N minflag:0 rectype:3
(3) normal record offset:180 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:208 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:239 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:270 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(7) normal record offset:301 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
-----Total del rows:2 del rows list(logic):
(1) normal record offset:153 heapno:3 n_owned 0,delflag:N minflag:0  rectype:0
(2) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0  rectype:0
-----Total slot:2 slot list:
(1) SUPREMUM slot offset:112 n_owned:6
(2) INFIMUM slot offset:99 n_owned:1
 

要回复问题请先登录注册