首页 > 代码库 > 8.1 第八章 选择合适的数据类型

8.1 第八章 选择合适的数据类型

在使用MySQL 创建数据表时都会遇到一个问题,如何为字段选择合适的数据类型。例 
如,创建一张员工表用来记录员工的信息,这时对员工的各种属性如何来进行定义?也许大 
家会想,这个问题很简单,每个字段可以使用很多种数据类型来定义,比如int、float、double、 
decimal 等。其实正因为可选择的数据类型太多,才需要依据一些原则来“挑选”最适合的 
数据类型。本章将详细介绍字符、数值、日期数据类型的一些选择原则。

8.1 CHAR 与VARCHAR 
CHAR 和VARCHAR 类型类似,都用来存储字符串,但它们保存和检索的方式不同。CHAR 
属于固定长度的字符类型(长度可以自由设置,但一旦设置后便固定,不会自动扩展),而VARCHAR 属于可变长度的字符类型。 
表8-1 显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR 
和VARCHAR 之间的差别。 
技术分享

请注意表8-1 中最后一行的值只适用非“严格模式”时,如果MySQL 运行在严格模式, 
超过列长度的值将不会保存,并且会出现错误提示,关于“严格模式””将在第16 章的SQL 
MODE 及其相关问题的章节中详细介绍。 
从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR 列删除了尾部 
的空格。下面通过一个例子说明该差别: 
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); 
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO vc VALUES (‘ab ‘, ‘ab ‘); 
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT(v, ‘+’), CONCAT(c, ‘+’) FROM vc; 
+—————-+—————-+ 
| CONCAT(v, ‘+’) | CONCAT(c, ‘+’) | 
+—————-+—————-+ 
| ab + | ab+ | 
+—————-+—————-+ 
1 row in set (0.00 sec)

由于CHAR 是固定长度的,所以它的处理速度比VARCHAR 快得多,但是其缺点是浪费 
存储空间,程序需要对行尾空格进行处理,所以对于那些长度变化不大并且对查询速度有较 
高要求的数据可以考虑使用CHAR 类型来存储。 
另外,随着MySQL 版本的不断升级,VARCHAR 数据类型的性能也在不断改进并提高, 
所以在许多的应用中,VARCHAR 类型被更多地使用。 
在MySQL 中,不同的存储引擎对CHAR 和VARCHAR 的使用原则有所不同,这里简单概 
括如下。 
? MyISAM 存储引擎:建议使用固定长度的数据列代替可变长度的数据列。 
? MEMORY 存储引擎:目前都使用固定长度的数据行存储,因此无论使用CHAR 或 
VARCHAR 列都没有关系。两者都是作为CHAR 类型处理。 
? InnoDB 存储引擎:建议使用VARCHAR 类型。对于InnoDB 数据表,内部的行存储 
格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在 
本质上,使用固定长度的CHAR 列不一定比使用可变长度VARCHAR 列性能要好。因而,主 
要的性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多于VARCHAR,因此使 
用VARCHAR 来最小化需要处理的数据行的存储总量和磁盘I/O 是比较好的。

8.2 TEXT 与BLOB 
一般在保存少量字符串的时候,我们会选择CHAR 或者VARCHAR;而在保存较大文本时, 
通常会选择使用TEXT 或者BLOB,二者之间的主要差别是BLOB 能用来保存二进制数据,比 
如照片;而TEXT 只能保存字符数据,比如一篇文章或者日记。TEXT 和BLOB 中有分别包括 
TEXT、MEDIUMTEXT、LONGTEXT 和BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型,它们 
之间的主要区别是存储文本长度不同和存储字节不同,用户应该根据实际情况选择能够满足 
需求的最小存储类型。本节主要对BLOB 和TEXT 存在的一些常见问题进行介绍。 
? BLOB 和TEXT 值会引起一些性能问题,特别是在执行了大量的删除操作时。 
删除操作会在数据表中留下很大的“空洞”,以后填入这些“空洞”的记录在插入的性能上 
会有影响。为了提高性能,建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理,避 
免因为“空洞”导致性能问题。

当对表有大量的增删改操作时,需要用optimize对表进行优化。可以减少空间与提高I/O性能,命令optimize table tablename;假如有foo表且存储引擎为MyISAM。 
mysql>optimize table foo; 
+————+———-+———-+———-+ 
| Table | Op | Msg_type | Msg_text | 
+————+———-+———-+———-+ 
| test.foo| optimize | status | OK | 
+————+———-+———-+———-+ 
如果是InnoDB引擎,首先查看innodb_file_per_table(是否独享表空间)。 
mysql>show variables like ‘innodb_file_per_table’; 
+———————–+——-+ 
| Variable_name | Value | 
+———————–+——-+ 
| innodb_file_per_table | OFF | 
+———————–+——-+ 
OFF代表开启共享表空间没有打开,即采用的是默认的共享表空间。这个时候可以在mysql的datadir路径下看到一个非常大的文件ibdata1,这个文件存储了所有InnoDB表的数据与索引。 
如果foo是InnoDB,执行如下命令 
mysql>optimeze table foo; 
会返回如图信息,最后的一条Table does not support optimize, doing recreate + analyze instead,即代表optimize无法优化表。 
这个时候使用如下命令优化表 
mysql>alter table foo ENGINE = ‘InnoDB’; 
mysql>analyze table foo; 
返回如下信息

+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| test.foo | analyze | status   | OK       |
+------------------------+---------+----------+----------+

即可优化该表 optimize在mysql中的用法 如果开启了独享表空间,即每张表都有ibdfile。这个时候如果删除了大量的行,索引会重组并且会释放相应的空间因此不必优化 。

5. 由于共享表空间所有表的数据与索引都存放于ibddata1文件中,随着数据量的增长会导致该文件越来越大。超过10G的时候查询速度就非常慢,因此在编译的时候最好开启独享表空间。因为mysql默认是关闭了独享表空间,下面有两个解决方案 

6.方案一:先逻辑备份所有的数据库,将配置文件中innodb_file_per_table参数=1,再将备份导入 

7.方案二:只要修改innodb_file_per_table参数,然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后,原来库中的表中的数据会继续存放于ibdata1中,新建的表才会使用独立表空间

8.1 第八章 选择合适的数据类型