MySQL建表过程中的一些注意事项

AsiaYe

发布于 2019-11-06 16:12:22

1.6K0

发布于 2019-11-06 16:12:22

文章被收录于专栏：DBA随笔

MySQL建表过程中的一些注意事项

MySQL之text类型字段

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

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

mysql> desc test_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int() | YES  |     | NULL    |       |
| memo  | text    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
 rows in set (0.19 sec)

mysql> insert into test_tbl values (,repeat('yeyz',));
Query OK,  row affected (0.03 sec)

mysql> insert into test_tbl values (,repeat('yeyz',));
Query OK,  row affected (0.03 sec)

mysql> insert into test_tbl values (,repeat('yeyz',));
Query OK,  row affected (0.01 sec)

mysql> insert into test_tbl select * from test_tbl;
Query OK,  rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test_tbl select * from test_tbl;
Query OK,  rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
.
.
.

mysql> insert into test_tbl select * from test_tbl;
Query OK,  rows affected (1.92 sec)
Records: 98304  Duplicates: 0  Warnings: 0

mysql> insert into test_tbl select * from test_tbl;
Query OK,  rows affected (10.99 sec)
Records: 196608  Duplicates: 0  Warnings: 0

首先我们创建一张表，这个表包含两个字段，一个int类型的id字段，一个text类型的memo字段，然后给这张表里面插入三条数据，这3条数据只是id不同，数据memo字段都是把'yeyz'这个字符串重复100次，然后我们通过重复添加表里面的数据，最终把数据添加到196608条，这样我们可以看到表的数据量变为189M，如下面的代码所示：

[root@localhost db]# du -sh ./*
K    ./datatypeC.frm
K    ./datatypeC.ibd
K    ./db_adminpass_details.frm
K    ./db_adminpass_details.ibd
4.0K    ./db.opt
K    ./test0.frm
K    ./test0.ibd
K    ./test1.frm
K    ./test1.ibd
K    ./test_tbl.frm
M    ./test_tbl.ibd

现在我们删除掉其中的一些数据：

mysql> delete from test_tbl where id=;
Query OK,  rows affected (3.60 sec)


[root@localhost db]# du -sh ./*
K    ./datatypeC.frm
K    ./datatypeC.ibd
K    ./db_adminpass_details.frm
K    ./db_adminpass_details.ibd
4.0K    ./db.opt
K    ./test0.frm
K    ./test0.ibd
K    ./test1.frm
K    ./test1.ibd
K    ./test_tbl.frm
M    ./test_tbl.ibd

通过匹配id=3的数据，我们将表中大概1/3的数据删除掉了，但是我们可以看到，表的大小并没有发生明显的改变，接下来我们对这个表进行优化操作，然后重新查看表的空间大小：

mysql> optimize table test_tbl;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| db.test_tbl | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| db.test_tbl | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (11.64 sec)



[root@localhost db]# du -sh ./*
12K    ./datatypeC.frm
96K    ./datatypeC.ibd
12K    ./db_adminpass_details.frm
180K    ./db_adminpass_details.ibd
4.0K    ./db.opt
12K    ./test0.frm
96K    ./test0.ibd
12K    ./test1.frm
132K    ./test1.ibd
12K    ./test_tbl.frm
145M    ./test_tbl.ibd

我们发现，将表优化之后，表中的数据变为145M，已经减少了40多M的数据，这说明表中存在一些冗余空间已经被回收了。值得注意的是，这个回收表空间的操作耗时11.64s，还是比较长的。

除此之外，当我们使用innodb存储引擎存储text类型数据的时候，还会把数据进行分开存放，会将一部分text类型的数据存储在溢出段中，这里面牵扯很多知识点，后面将专门写一篇文章进行分析。

MySQL之表大小

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

mysql> CREATE TABLE `top_org` (
    -> `top_org_id` varchar()  NOT NULL COMMENT 'XXXX' ,
    ->  `db_type` varchar()  NOT NULL COMMENT 'XXXX' , 
    ->  `db_properties` varchar()  COMMENT 'XXXX' , 
    ->  PRIMARY KEY (`top_org_id`, `db_type`) 
    ->  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
ERROR  (): Column length too big for column 'db_properties' (max = ); use BLOB or TEXT instead

从上面的错误提示条件中不难看出，错误是由于varchar类型的字段设置的值太长导致的，也就是db_properties字段的1048576导致的，而且上面给了提示，最大的max值是21845，这里首先解释下为什么最大值是21845，因为MySQL在创建表的时候有一条规定，MySQL要求一个行的定义长度不能超过65535。再来具体看当前这个SQL，这个表在创建的时候表的字符集是utf8类型的，具体的计算方法如下：

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

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

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

mysql>   CREATE TABLE `top_organization` (
    -> `top_org_id` varchar()  NOT NULL COMMENT 'XXXX' ,
    ->  `db_type` varchar()  NOT NULL COMMENT 'XXXX' , 
    ->  `db_properties` varchar()  COMMENT 'XXXX' , 
    ->  PRIMARY KEY (`top_org_id`, `db_type`) 
    ->  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
ERROR  (): 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

mysql> drop table top_organization;
Query OK,  rows affected (0.01 sec)

mysql>  CREATE TABLE `top_organization` (
    -> `top_org_id` varchar()  NOT NULL COMMENT 'XXXX' ,
    ->  `db_type` varchar()  NOT NULL COMMENT 'XXXX' , 
    ->  `db_properties` varchar()  COMMENT 'XXXX' , 
    ->  PRIMARY KEY (`top_org_id`, `db_type`) 
    ->  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
Query OK,  rows affected (0.05 sec)

我们可以发现，当db_properties字段的值是21716的时候，仍然不能创建表，但是在db_properties变为21715的时候，创建表，这个时候表创建成功了。关于这个数值为什么会是21715，这里卖个关子，其实之前的文章也有写过，可以参考下9月30号的文章内容，大家自己计算下。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2018-12-14，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server