今天在和业务方沟通一个建表的工单的时候,发现工单中有一处使用了text字段,于是提出建议把text字段替换为char类型或者varchar类型。跟业务方沟通,发现是某一类配置文件的存储需要使用到text字段,最终在确认了存储的配置文件大小之后,还是换成了varchar类型。text这种字段在数据库中是不建议使用的,之前看书的时候说是因为text类型的字段存在一些性能问题,没有仔细的研究过,今天研究了一下这种类型的字段,大概总结如下:

text类型的字段通常用来保存比较大的一些文本对象,除了text,blob类型也经常被使用,这两种类型之间的差别主要是blob能够保存二进制数据,例如图片信息等,而text只能保存字符数据,但是这两种数据类型都会存在一些性能问题,也就常说的表空间碎片,或者称之为表空间空洞,从而影响插入表的性能。解决这种性能问题通常可以采用optimeize table来对这类碎片表进行优化。这里我们还是通过例子来看这个问题:

1mysql> desc test_tbl; 2+-------+---------+------+-----+---------+-------+ 3| Field | Type    | Null | Key | Default | Extra | 4+-------+---------+------+-----+---------+-------+ 5| id    | int(11) | YES  |     | NULL    |       | 6| memo  | text    | YES  |     | NULL    |       | 7+-------+---------+------+-----+---------+-------+ 82 rows in set (0.19 sec) 910mysql> insert into test_tbl values (1,repeat('yeyz',100));11Query OK, 1 row affected (0.03 sec)1213mysql> insert into test_tbl values (2,repeat('yeyz',100));14Query OK, 1 row affected (0.03 sec)1516mysql> insert into test_tbl values (3,repeat('yeyz',100));17Query OK, 1 row affected (0.01 sec)1819mysql> insert into test_tbl select * from test_tbl;20Query OK, 3 rows affected (0.00 sec)21Records: 3  Duplicates: 0  Warnings: 02223mysql> insert into test_tbl select * from test_tbl;24Query OK, 6 rows affected (0.00 sec)25Records: 6  Duplicates: 0  Warnings: 026.27.28.2930mysql> insert into test_tbl select * from test_tbl;31Query OK, 98304 rows affected (1.92 sec)32Records: 98304  Duplicates: 0  Warnings: 03334mysql> insert into test_tbl select * from test_tbl;35Query OK,  rows affected (10.99 sec)36Records:   Duplicates: 0  Warnings: 037    首先我们创建一张表,这个表包含两个字段,一个int类型的id字段,一个text类型的memo字段,然后给这张表里面插入三条数据,这3条数据只是id不同,数据memo字段都是把'yeyz'这个字符串重复100次,然后我们通过重复添加表里面的数据,最终把数据添加到条,这样我们可以看到表的数据量变为189M,如下面的代码所示:

1[root@localhost db]# du -sh ./* 212K    ./datatypeC.frm 396K    ./datatypeC.ibd 412K    ./db_adminpass_details.frm 5180K    ./db_adminpass_details.ibd 64.0K    ./db.opt 712K    ./test0.frm 896K    ./test0.ibd 912K    ./test1.frm10132K    ./test1.ibd1112K    ./test_tbl.frm12189M    ./test_tbl.ibd    现在我们删除掉其中的一些数据:

1mysql> delete from test_tbl where id=3; 2Query OK,  rows affected (3.60 sec) 3 4 5[root@localhost db]# du -sh ./* 612K    ./datatypeC.frm 796K    ./datatypeC.ibd 812K    ./db_adminpass_details.frm 9180K    ./db_adminpass_details.ibd104.0K    ./db.opt1112K    ./test0.frm1296K    ./test0.ibd1312K    ./test1.frm14132K    ./test1.ibd1512K    ./test_tbl.frm16189M    ./test_tbl.ibd    通过匹配id=3的数据,我们将表中大概1/3的数据删除掉了,但是我们可以看到,表的大小并没有发生明显的改变,接下来我们对这个表进行优化操作,然后重新查看表的空间大小:

1mysql> optimize table test_tbl; 2+-------------+----------+----------+-------------------------------------------------------------------+ 3| Table       | Op       | Msg_type | Msg_text                                                          | 4+-------------+----------+----------+-------------------------------------------------------------------+ 5| db.test_tbl | optimize | note     | Table does not support optimize, doing recreate + analyze instead | 6| db.test_tbl | optimize | status   | OK                                                                | 7+-------------+----------+----------+-------------------------------------------------------------------+ 82 rows in set (11.64 sec) [root@localhost db]# du -sh ./*1312K    ./datatypeC.frm1496K    ./datatypeC.ibd1512K    ./db_adminpass_details.frm16180K    ./db_adminpass_details.ibd174.0K    ./db.opt1812K    ./test0.frm1996K    ./test0.ibd2012K    ./test1.frm21132K    ./test1.ibd2212K    ./test_tbl.frm23145M    ./test_tbl.ibd24   我们发现,将表优化之后,表中的数据变为145M,已经减少了40多M的数据,这说明表中存在一些冗余空间已经被回收了。值得注意的是,这个回收表空间的操作耗时11.64s,还是比较长的。

今天在创建一个业务方提供的表时,没有仔细看,当时手头还有别的事情,审核的时候出了点岔子,结果MySQL报了一个错误:

1mysql> CREATE TABLE `top_org` (2    -> `top_org_id` varchar(64)  NOT NULL COMMENT 'XXXX' ,3    ->  `db_type` varchar(64)  NOT NULL COMMENT 'XXXX' , 4    ->  `db_properties` varchar()  COMMENT 'XXXX' , 5    ->  PRIMARY KEY (`top_org_id`, `db_type`) 6    ->  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;7ERROR 1074 (42000): Column length too big for column 'db_properties' (max = 21845); use BLOB or TEXT instead    从上面的错误提示条件中不难看出,错误是由于varchar类型的字段设置的值太长导致的,也就是db_properties字段的导致的,而且上面给了提示,最大的max值是21845,这里首先解释下为什么最大值是21845,因为MySQL在创建表的时候有一条规定,MySQL要求一个行的定义长度不能超过65535。再来具体看当前这个SQL,这个表在创建的时候表的字符集是utf8类型的,具体的计算方法如下:

字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过65535/2=32766;

字符类型若为utf8,每个字符最多占3个字节,最大长度不能超过65535/3=21845。

当然这只是一个笼统的计算,还没有考虑到细节问题,这里我们进行尝试,到底这个数值为多少的时候可以存储成功:

1mysql>   CREATE TABLE `top_organization` ( 2    -> `top_org_id` varchar(64)  NOT NULL COMMENT 'XXXX' , 3    ->  `db_type` varchar(64)  NOT NULL COMMENT 'XXXX' ,  4    ->  `db_properties` varchar(21716)  COMMENT 'XXXX' ,  5    ->  PRIMARY KEY (`top_org_id`, `db_type`)  6    ->  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ; 7ERROR 1118 (42000): 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 8 9mysql> drop table top_organization;10Query OK, 0 rows affected (0.01 sec)1112mysql>  CREATE TABLE `top_organization` (13    -> `top_org_id` varchar(64)  NOT NULL COMMENT 'XXXX' ,14    ->  `db_type` varchar(64)  NOT NULL COMMENT 'XXXX' , 15    ->  `db_properties` varchar(21715)  COMMENT 'XXXX' , 16    ->  PRIMARY KEY (`top_org_id`, `db_type`) 17    ->  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;18Query OK, 0 rows affected (0.05 sec)    我们可以发现,当db_properties字段的值是21716的时候,仍然不能创建表,但是在db_properties变为21715的时候,创建表,这个时候表创建成功了。关于这个数值为什么会是21715,这里卖个关子,其实之前的文章也有写过,可以参考下9月30号的文章内容,大家自己计算下。