专栏首页DBA随笔MySQL建表过程中的一些注意事项

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

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

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类型的数据存储在溢出段中,这里面牵扯很多知识点,后面将专门写一篇文章进行分析。

02

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号的文章内容,大家自己计算下。

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447),作者:AsiaYe

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-12-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • python写的小程序

    今天早上来公司比较早,就用python写了写数据结构的代码,工作之后虽然参与了一部分开发的工作,但都是在写业务逻辑,时间长了,发现自己成了if-else...

    AsiaYe
  • MySQL中的join语句

    在MySQL中,join语句想必大家都不陌生,今天我们围绕join语句展开,说一些可能平时不关注的知识点。

    AsiaYe
  • MongoDB运维与开发(8)---MongoDB备份与恢复

    任何数据库都离不开日常的备份与恢复,这二者是数据库安全的前提,MongoDB的备份与恢复方法一般而言都采用自带的工具来进行。

    AsiaYe
  • Ubuntu基础教程之apt-get命令

    apt-get 命令是 Ubuntu 系统中的包管理工具,可以用来安装、卸载包,也可以用来升级包,还可以用来把系统升级到新的版本。本文介绍 apt-get 命令...

    砸漏
  • Python入门之安装numpy和pandas

    最近要对一系列数据做同比比较,需要用到numpy和pandas来计算,不过使用python安装numpy和pandas因为linux环境没有外网遇到了很多问题就...

    Jetpropelledsnake21
  • linux 定时任务crontab防止脚本运行冲突

    定时任务crontab是linux下常用的功能,可以设置一定的间隔时间执行任务,但有可能出现任务脚本运行冲突问题 例如某脚本要运行30分钟,在crontab里...

    dys
  • 产品思维助力工程师职场进阶

    今天本来想发技术干货 Netty 介绍的,不过想了下昨天在组内的一次分享颇受大家好评,可能大家看了会有所启发。所以今天先给大家简单分享一个主题:如何成为一个具有...

    kunge
  • centos 7 安装python3.6

    centos7 默认安装了python2.7.5,当需要使用python3的时候,可以手动下载python源码后编译安装.

    py3study
  • Centos7安装python3并与python2共存

    程序员同行者
  • Webpack之before快速创建接口

    上次分享了用webpack的express模块快速启动一个服务接口,其实webpack还有HTTP模块也可以快速创建接口,有兴趣的可以去查查。今天要分享的也是基...

    wade

扫码关注云+社区

领取腾讯云代金券