Description:
hi team:
If there are large records in the table but they do not exceed 8192, in certain situations, the table space will be greatly wasted.
In repeat example record 6/7/8/9/10/11 in separate pages.
How to repeat:
CREATE TABLE `sbtest2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pad` varchar(12000),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into sbtest2 values (10000, repeat('a',5120));
insert into sbtest2 values (10001, repeat('a',5120));
insert into sbtest2 values (1, repeat('a',5120));
insert into sbtest2 values (2, repeat('a',5120));
insert into sbtest2 values (3, repeat('a',5120));
insert into sbtest2 values (4, repeat('a',5120));
insert into sbtest2 values (5, repeat('a',5120));
insert into sbtest2 values (6,repeat('a',5120));
insert into sbtest2 values (7,repeat('a',5120));
insert into sbtest2 values (8,repeat('a',5120));
insert into sbtest2 values (9,repeat('a',5120));
insert into sbtest2 values (10,repeat('a',5120));
insert into sbtest2 values (11,repeat('a',5120));
Suggested fix:
At a certain moment, the page‘s records as follows
block n block x
9 10000 --> 10001
|
|
insert rec 10
The current logic is:
block n block n+1 block x
9 ---> 10 10000 ---> 10001
block n space will be greatly wasted !
This example key logic to insert an old page or a new split page,if insert new record to old page can be solved.
Like this :
block n block n+1 block x
9 10 ---> 10000 ---> 10001
next new rec 11 insert to block n+1
block n block n block x
9 10 ----> 11 10000 --> 10001
This way, the space won't be wasted.
key logic:
/* 6. The split and the tree modification is now completed. Decide the
page where the tuple should be inserted */
if (insert_left) {
insert_block = left_block;
} else {
insert_block = right_block;
}
Description: hi team: If there are large records in the table but they do not exceed 8192, in certain situations, the table space will be greatly wasted. In repeat example record 6/7/8/9/10/11 in separate pages. How to repeat: CREATE TABLE `sbtest2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pad` varchar(12000), PRIMARY KEY (`id`) ) ENGINE=InnoDB; insert into sbtest2 values (10000, repeat('a',5120)); insert into sbtest2 values (10001, repeat('a',5120)); insert into sbtest2 values (1, repeat('a',5120)); insert into sbtest2 values (2, repeat('a',5120)); insert into sbtest2 values (3, repeat('a',5120)); insert into sbtest2 values (4, repeat('a',5120)); insert into sbtest2 values (5, repeat('a',5120)); insert into sbtest2 values (6,repeat('a',5120)); insert into sbtest2 values (7,repeat('a',5120)); insert into sbtest2 values (8,repeat('a',5120)); insert into sbtest2 values (9,repeat('a',5120)); insert into sbtest2 values (10,repeat('a',5120)); insert into sbtest2 values (11,repeat('a',5120)); Suggested fix: At a certain moment, the page‘s records as follows block n block x 9 10000 --> 10001 | | insert rec 10 The current logic is: block n block n+1 block x 9 ---> 10 10000 ---> 10001 block n space will be greatly wasted ! This example key logic to insert an old page or a new split page,if insert new record to old page can be solved. Like this : block n block n+1 block x 9 10 ---> 10000 ---> 10001 next new rec 11 insert to block n+1 block n block n block x 9 10 ----> 11 10000 --> 10001 This way, the space won't be wasted. key logic: /* 6. The split and the tree modification is now completed. Decide the page where the tuple should be inserted */ if (insert_left) { insert_block = left_block; } else { insert_block = right_block; }