Skip to content

Commit f9753b5

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#30 SUPPORT BIG COLUMN COMPRESS
New syntax: ----------- column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|COMPRESSED|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition] Description: ------------ If the column was defined as a compressed column, then the column data will be compressed using zlib. Currently blob/text/varchar/varbinary are supported. DDL: Currently Not allowed to define a index that include compressed column, an error message throw(ER_BLOB_KEY_WITH_COMPRESS) And use COPY algorithm if column_format was changed. So we can't use online ddl to change the column format. Control option: innodb_rds_column_compression_level innodb_rds_column_zip_threshold innodb_rds_column_zlib_strategy These variables above are used to control the param for deflate used by column data compression. innodb_rds_column_zip_threshold control the compress threshold, if the data length exceeds this value, then compress the data.
1 parent 959e2d3 commit f9753b5

17 files changed

Lines changed: 1193 additions & 35 deletions

File tree

‎mysql-test/r/compress_column.result‎

Lines changed: 358 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,358 @@
1+
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
2+
create table t1 (a int column_format compressed) CHARACTER SET gbk engine=innodb;
3+
Warnings:
4+
Warning 1887 Can not define column 'a' in compressed format, silently change column_format to default
5+
show create table t1;
6+
Table Create Table
7+
t1 CREATE TABLE `t1` (
8+
`a` int(11) DEFAULT NULL
9+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
10+
create table t2 (a varchar(100) column_format compressed) CHARACTER SET gbk engine=innodb;
11+
show create table t2;
12+
Table Create Table
13+
t2 CREATE TABLE `t2` (
14+
`a` varchar(100) /*!50616 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL
15+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
16+
create table t3 (a blob column_format compressed) CHARACTER SET gbk engine=innodb;
17+
show create table t3;
18+
Table Create Table
19+
t3 CREATE TABLE `t3` (
20+
`a` blob /*!50616 COLUMN_FORMAT COMPRESSED */
21+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
22+
create table t4 (a text column_format compressed) CHARACTER SET gbk engine=innodb;
23+
show create table t4;
24+
Table Create Table
25+
t4 CREATE TABLE `t4` (
26+
`a` text /*!50616 COLUMN_FORMAT COMPRESSED */
27+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
28+
create table t5 (a text binary column_format compressed) CHARACTER SET gbk engine=innodb;
29+
show create table t5;
30+
Table Create Table
31+
t5 CREATE TABLE `t5` (
32+
`a` text CHARACTER SET gbk COLLATE gbk_bin /*!50616 COLUMN_FORMAT COMPRESSED */
33+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
34+
create table t6(a text CHARACTER SET gbk column_format compressed) CHARACTER SET gbk engine=innodb;
35+
show create table t6;
36+
Table Create Table
37+
t6 CREATE TABLE `t6` (
38+
`a` text /*!50616 COLUMN_FORMAT COMPRESSED */
39+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
40+
create table t7(a text BINARY CHARACTER SET gbk column_format compressed) CHARACTER SET gbk engine=innodb;
41+
show create table t7;
42+
Table Create Table
43+
t7 CREATE TABLE `t7` (
44+
`a` text CHARACTER SET gbk COLLATE gbk_bin /*!50616 COLUMN_FORMAT COMPRESSED */
45+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
46+
create table t8 (a varbinary(1000) column_format compressed) CHARACTER SET gbk engine=innodb;
47+
show create table t8;
48+
Table Create Table
49+
t8 CREATE TABLE `t8` (
50+
`a` varbinary(1000) /*!50616 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL
51+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
52+
create table t9 ( a varchar(1000) column_format compressed) CHARACTER SET gbk engine=innodb;
53+
show create table t9;
54+
Table Create Table
55+
t9 CREATE TABLE `t9` (
56+
`a` varchar(1000) /*!50616 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL
57+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
58+
drop table t1;
59+
create table t1(a blob column_format compressed, key(a(10)));
60+
ERROR HY000: Compressed BLOB/TEXT/VARCHAR/VARBINARY column 'a' used in key list is not allowed
61+
create table t1(a blob column_format compressed);
62+
alter table t3 add key(a(10));
63+
ERROR HY000: Compressed BLOB/TEXT/VARCHAR/VARBINARY column 'a' used in key list is not allowed
64+
drop table t1;
65+
create table t1 (a blob) engine=innodb;
66+
alter table t1 add key(a(10));
67+
drop table t1;
68+
create table t1 (a blob) CHARACTER SET gbk engine=innodb;
69+
insert into t1 values (repeat('abc',5));
70+
insert into t1 values (repeat('abc',100));
71+
select * from t1;
72+
a
73+
abcabcabcabcabc
74+
abcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabc
75+
alter table t1 modify column a blob column_format compressed;
76+
select * from t1;
77+
a
78+
abcabcabcabcabc
79+
abcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabcabc
80+
show create table t1;
81+
Table Create Table
82+
t1 CREATE TABLE `t1` (
83+
`a` blob /*!50616 COLUMN_FORMAT COMPRESSED */
84+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
85+
checksum table t1;
86+
Table Checksum
87+
test.t1 1570560110
88+
alter table t1 modify column a varchar(10000) column_format compressed;
89+
show create table t1;
90+
Table Create Table
91+
t1 CREATE TABLE `t1` (
92+
`a` varchar(10000) /*!50616 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL
93+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
94+
alter table t1 modify column a varchar(10000);
95+
alter table t1 modify column a blob column_format compressed;
96+
checksum table t1;
97+
Table Checksum
98+
test.t1 1570560110
99+
drop table t1;
100+
create table t1 (a int auto_increment primary key) CHARACTER SET gbk engine=innodb;
101+
insert into t1 values (NULL);
102+
alter table t1 add column b text column_format compressed;
103+
insert into t1 (a) values (NULL);
104+
alter table t1 add column c varchar(1000) column_format compressed;
105+
insert into t1 (a,b,c) values (NULL, "hello!", "world!");
106+
alter table t1 add column d varchar(10000) column_format compressed default "hello, world! see you!";
107+
insert into t1 (a,b,c) values (NULL, "hello", "world!");
108+
select * from t1;
109+
a b c d
110+
1 NULL NULL hello, world! see you!
111+
2 NULL NULL hello, world! see you!
112+
3 hello! world! hello, world! see you!
113+
4 hello world! hello, world! see you!
114+
set @old_innodb_rds_column_compression_level = @@global.innodb_rds_column_compression_level;
115+
drop table t1;
116+
create table t1 (a blob column_format compressed) CHARACTER SET gbk engine=innodb;
117+
set @@global.innodb_rds_column_compression_level = -1;
118+
Warnings:
119+
Warning 1292 Truncated incorrect innodb_rds_column_compression_le value: '-1'
120+
select @@global.innodb_rds_column_compression_level;
121+
@@global.innodb_rds_column_compression_level
122+
0
123+
insert into t1 values(repeat('abcd', 200));
124+
set @@global.innodb_rds_column_compression_level = 5;
125+
select @@global.innodb_rds_column_compression_level;
126+
@@global.innodb_rds_column_compression_level
127+
5
128+
insert into t1 values(repeat('abcd', 200));
129+
set @@global.innodb_rds_column_compression_level = 9;
130+
select @@global.innodb_rds_column_compression_level;
131+
@@global.innodb_rds_column_compression_level
132+
9
133+
insert into t1 values(repeat('abcd', 200));
134+
set @@global.innodb_rds_column_compression_level = 10;
135+
Warnings:
136+
Warning 1292 Truncated incorrect innodb_rds_column_compression_le value: '10'
137+
select @@global.innodb_rds_column_compression_level;
138+
@@global.innodb_rds_column_compression_level
139+
9
140+
select max(length(a)) from t1;
141+
max(length(a))
142+
800
143+
set @old_innodb_rds_column_zlib_strategy = @@global.innodb_rds_column_zlib_strategy;
144+
truncate table t1;
145+
insert into t1 values(repeat('abcd', 10));
146+
set @@global.innodb_rds_column_zlib_strategy = -1;
147+
Warnings:
148+
Warning 1292 Truncated incorrect innodb_rds_column_zlib_strategy value: '-1'
149+
select @@global.innodb_rds_column_zlib_strategy;
150+
@@global.innodb_rds_column_zlib_strategy
151+
0
152+
insert into t1 values(repeat('abcd', 10));
153+
set @@global.innodb_rds_column_zlib_strategy = 0;
154+
select @@global.innodb_rds_column_zlib_strategy;
155+
@@global.innodb_rds_column_zlib_strategy
156+
0
157+
insert into t1 values(repeat('abcd', 200));
158+
set @@global.innodb_rds_column_zlib_strategy = 1;
159+
select @@global.innodb_rds_column_zlib_strategy;
160+
@@global.innodb_rds_column_zlib_strategy
161+
1
162+
insert into t1 values(repeat('abcd', 200));
163+
set @@global.innodb_rds_column_zlib_strategy = 2;
164+
select @@global.innodb_rds_column_zlib_strategy;
165+
@@global.innodb_rds_column_zlib_strategy
166+
2
167+
insert into t1 values(repeat('abcd', 200));
168+
set @@global.innodb_rds_column_zlib_strategy = 3;
169+
select @@global.innodb_rds_column_zlib_strategy;
170+
@@global.innodb_rds_column_zlib_strategy
171+
3
172+
insert into t1 values(repeat('abcd', 200));
173+
set @@global.innodb_rds_column_zlib_strategy = 4;
174+
select @@global.innodb_rds_column_zlib_strategy;
175+
@@global.innodb_rds_column_zlib_strategy
176+
4
177+
insert into t1 values(repeat('abcd', 200));
178+
set @@global.innodb_rds_column_zlib_strategy = 5;
179+
Warnings:
180+
Warning 1292 Truncated incorrect innodb_rds_column_zlib_strategy value: '5'
181+
select @@global.innodb_rds_column_zlib_strategy;
182+
@@global.innodb_rds_column_zlib_strategy
183+
4
184+
select max(length(a)) from t1;
185+
max(length(a))
186+
800
187+
truncate table t1;
188+
set @old_innodb_rds_column_zlib_wrap = @@global.innodb_rds_column_zlib_wrap;
189+
set @@global.innodb_rds_column_zlib_wrap = 0;
190+
select @@global.innodb_rds_column_zlib_wrap;
191+
@@global.innodb_rds_column_zlib_wrap
192+
0
193+
insert into t1 values (repeat('abcd', 10));
194+
insert into t1 values (repeat('abcd',1000));
195+
set @@global.innodb_rds_column_zlib_wrap = 1;
196+
select @@global.innodb_rds_column_zlib_wrap;
197+
@@global.innodb_rds_column_zlib_wrap
198+
1
199+
insert into t1 values (repeat('abcd', 10));
200+
insert into t1 values (repeat('abcd',1000));
201+
select max(length(a)) from t1;
202+
max(length(a))
203+
4000
204+
truncate table t1;
205+
set @old_innodb_rds_column_zip_threshold = @@global.innodb_rds_column_zip_threshold;
206+
show variables like 'innodb_rds_column_zip_threshold';
207+
Variable_name Value
208+
innodb_rds_column_zip_threshold 96
209+
set global innodb_rds_column_zip_threshold = 0;
210+
Warnings:
211+
Warning 1292 Truncated incorrect innodb_rds_column_zip_threshold value: '0'
212+
select @@global.innodb_rds_column_zip_threshold;
213+
@@global.innodb_rds_column_zip_threshold
214+
1
215+
insert into t1 values (repeat('abc',10));
216+
set global innodb_rds_column_zip_threshold = 96;
217+
select @@global.innodb_rds_column_zip_threshold;
218+
@@global.innodb_rds_column_zip_threshold
219+
96
220+
insert into t1 values (repeat('abc',40));
221+
set global innodb_rds_column_zip_threshold = 1000;
222+
select @@global.innodb_rds_column_zip_threshold;
223+
@@global.innodb_rds_column_zip_threshold
224+
1000
225+
insert into t1 values (repeat('abc',1000));
226+
select max(length(a)) from t1;
227+
max(length(a))
228+
3000
229+
show global status like '%innodb%compress%';
230+
Variable_name Value
231+
Innodb_column_compressed 29
232+
Innodb_column_decompressed 35
233+
drop table t1;
234+
create table t1 (a int auto_increment primary key, b int, c int, d blob column_format compressed) CHARACTER SET gbk engine=innodb;
235+
show create table t1;
236+
Table Create Table
237+
t1 CREATE TABLE `t1` (
238+
`a` int(11) NOT NULL AUTO_INCREMENT,
239+
`b` int(11) DEFAULT NULL,
240+
`c` int(11) DEFAULT NULL,
241+
`d` blob /*!50616 COLUMN_FORMAT COMPRESSED */,
242+
PRIMARY KEY (`a`)
243+
) ENGINE=InnoDB DEFAULT CHARSET=gbk
244+
insert into t1 select NULL,100,100,repeat('abc',100);
245+
insert into t1 select NULL,100,100,repeat('abc',100) from t1;
246+
insert into t1 select NULL,100,100,repeat('abc',100) from t1 a, t1 b;
247+
insert into t1 select NULL,100,100,repeat('abc',100) from t1 a, t1 b;
248+
insert into t1 select NULL,100,100,repeat('abc',100) from t1 a, t1 b;
249+
show global status like '%innodb%compress%';
250+
Variable_name Value
251+
Innodb_column_compressed 1835
252+
Innodb_column_decompressed 35
253+
select count(*) from t1;
254+
count(*)
255+
1806
256+
show global status like '%innodb%compress%';
257+
Variable_name Value
258+
Innodb_column_compressed 1835
259+
Innodb_column_decompressed 35
260+
select a,b,c from t1 limit 10;
261+
a b c
262+
1 100 100
263+
2 100 100
264+
3 100 100
265+
4 100 100
266+
5 100 100
267+
6 100 100
268+
10 100 100
269+
11 100 100
270+
12 100 100
271+
13 100 100
272+
show global status like '%innodb%compress%';
273+
Variable_name Value
274+
Innodb_column_compressed 1835
275+
Innodb_column_decompressed 35
276+
select max(length(d)) from t1;
277+
max(length(d))
278+
300
279+
show global status like '%innodb%compress%';
280+
Variable_name Value
281+
Innodb_column_compressed 1835
282+
Innodb_column_decompressed 1841
283+
drop table t2;
284+
create table t2 like t1;
285+
insert into t2 select * from t1;
286+
alter table t2 modify column d blob;
287+
show create table t2;
288+
Table Create Table
289+
t2 CREATE TABLE `t2` (
290+
`a` int(11) NOT NULL AUTO_INCREMENT,
291+
`b` int(11) DEFAULT NULL,
292+
`c` int(11) DEFAULT NULL,
293+
`d` blob,
294+
PRIMARY KEY (`a`)
295+
) ENGINE=InnoDB AUTO_INCREMENT=1837 DEFAULT CHARSET=gbk
296+
checksum table t1,t2;
297+
Table Checksum
298+
test.t1 2260691284
299+
test.t2 2260691284
300+
set @old_innodb_rds_column_zip_mem_use_heap = @@global.innodb_rds_column_zip_mem_use_heap;
301+
set global innodb_rds_column_zip_mem_use_heap = 1;
302+
create table t10 (a int auto_increment primary key, b blob column_format compressed) engine = innodb;
303+
insert into t10 values (NULL, repeat('abc',5000));
304+
select length(b) from t10;
305+
length(b)
306+
15000
307+
set @@global.innodb_rds_column_zip_threshold = 10;
308+
insert into t10 values (NULL,'abcdeghijklm');
309+
drop table t10;
310+
create table t10 (a int auto_increment primary key, b blob) engine = innodb;
311+
insert into t10 values (NULL, repeat('abc',5000));
312+
select length(b) from t10;
313+
length(b)
314+
15000
315+
drop table t10;
316+
create table t10 (a tinytext column_format compressed) engine = innodb;
317+
insert into t10(a) values (repeat('abc',50));
318+
select length(a) from t10;
319+
length(a)
320+
150
321+
drop table t10;
322+
create table t10 (a text column_format compressed) engine = innodb;
323+
insert into t10(a) values (repeat('abc',50));
324+
select length(a) from t10;
325+
length(a)
326+
150
327+
drop table t10;
328+
create table t10 (a mediumtext column_format compressed) engine = innodb;
329+
insert into t10(a) values (repeat('abc',50));
330+
select length(a) from t10;
331+
length(a)
332+
150
333+
drop table t10;
334+
create table t10 (a longtext column_format compressed) engine = innodb;
335+
insert into t10(a) values (repeat('abc',50));
336+
select length(a) from t10;
337+
length(a)
338+
150
339+
drop table t10;
340+
create table t10 (a int primary key, b blob , key (b(20))) engine = innodb;
341+
insert into t10 values (1,repeat('abc',10));
342+
insert into t10 values (2,repeat('bca',10));
343+
insert into t10 values (3,repeat('cab',10));
344+
select a from t10 where b like 'abc%' order by a;
345+
a
346+
1
347+
update t10 set b = repeat('aaa',10);
348+
drop table t10;
349+
create table t10 (a blob, b text ,primary key(a(1))) engine = innodb;
350+
insert into t10 values (REPEAT('a',8000),REPEAT('b',8000));
351+
update t10 set a = REPEAT('a',7999);
352+
drop table t10;
353+
set @@global.innodb_rds_column_compression_level = @old_innodb_rds_column_compression_level;
354+
set @@global.innodb_rds_column_zlib_wrap = @old_innodb_rds_column_zlib_wrap;
355+
set @@global.innodb_rds_column_zlib_strategy = @old_innodb_rds_column_zlib_strategy;
356+
set @@global.innodb_rds_column_zip_threshold = @old_innodb_rds_column_zip_threshold;
357+
set @@global.innodb_rds_column_zip_mem_use_heap = @old_innodb_rds_column_zip_mem_use_heap;
358+
drop table t1,t2,t3,t4,t5,t6,t7,t8,t9;

‎mysql-test/suite/sys_vars/r/all_vars.result‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,5 +12,15 @@ There should be *no* long test name listed below:
1212
select variable_name as `There should be *no* variables listed below:` from t2
1313
left join t1 on variable_name=test_name where test_name is null ORDER BY variable_name;
1414
There should be *no* variables listed below:
15+
INNODB_RDS_COLUMN_COMPRESSION_LEVEL
16+
INNODB_RDS_COLUMN_COMPRESSION_LEVEL
17+
INNODB_RDS_COLUMN_ZIP_MEM_USE_HEAP
18+
INNODB_RDS_COLUMN_ZIP_MEM_USE_HEAP
19+
INNODB_RDS_COLUMN_ZIP_THRESHOLD
20+
INNODB_RDS_COLUMN_ZIP_THRESHOLD
21+
INNODB_RDS_COLUMN_ZLIB_STRATEGY
22+
INNODB_RDS_COLUMN_ZLIB_STRATEGY
23+
INNODB_RDS_COLUMN_ZLIB_WRAP
24+
INNODB_RDS_COLUMN_ZLIB_WRAP
1525
drop table t1;
1626
drop table t2;

0 commit comments

Comments
 (0)