MySQL 数据类型选择和优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。

一、选择数据类型的原则

• 尽量小的存储空间

  一般情况下，尽量应该选择可以正确存储数据的最小数据类型，因为更小的数据类型可以占用更小的磁盘、内存和 CPU 缓存；处理数据时需要的 CUP 周期更少；但如果低估了数据占用的空间，后期需要更多空间时，对列的数据类型进行修改，将会是非常耗时的过程（假如表中数据量非常多）；

• 尽量选择简单类型

  CUP 通常在处理简单数据类型的时候执行计算需要的时间会更短，比如，整数比字符操作的代价更低（因为字符集处理和校对规则如排序比整数要复杂）；举例：应用应该使用 MySQL 的内建日期类型来存储日期含义的数据，而非使用字符串来存储；另一个例子是 IP 地址也可以使用整数来存储，比字符串存储效率高很多；

• 尽量避免 Null

  在应用程序不需要保存 Null 值时，尽量不要将列设置成可为空；因为 Null 值在 MySQL 中很难优化，这样的列使得索引、索引统计和值比较都更复杂；当可为空的列，被设置成索引时，每个索引记录都会需要一个额外的字节；

二、数值类型

数值类型包括整数类型和实数类型。

整数类型有可选的 UNSIGNED 属性，表述不允许负值，每种类型的表示数值个数一样，但是正数范围扩大了一倍；有符号数和无符号数的存储空间相同；

需要注意：MySQL 可以为正数设置指定宽度，如 INT(11)，对大多数应用是没有实际意义的，因为这样设置并不会影响该类型实际在磁盘上存储的空间，也不会影响类型可以表示的整数范围，而是只限制了连接 MySQL 服务的客户端对于该类型显示的字符个数；所以 INT(1)和 INT(20)在存储和计算处理上，是等价的；

对于 DECIMAL 而言，MySQL5.0 及以后，将数字打包成一个二进制字符串（每 4 个字节存 9 位数字），比如：DECIMAL(18,9)，小数点两边各存 9 个数字，小数点前占用 4 个字节，后占用 4 个字节，小数点本身占用一个字节，共 9 个字节；允许最多 65 个数字（低版本是 254 个数字限制）；
因为需要额外的空间和计算开销，应该尽量在需要对小数进行精确计算时，才选用这种类型，比如财务数据；但是对于这种情况，可以使用 BIGINT 来代替 DECIMAL，只需要根据货币单位和需要精确的小数位，将小数乘以相应的倍数，就可以变成整数来计算和存储；

字符串类型

VARCHAR 需要额外的 1 个或者 2 个字节来保存字符串的长度信息，如果字符串长度小于等于 255，则使用 1 个额外字节，大于 255 使用 2 个，因此该类型最长是 65535；
使用场景：字符串的最大长度，可能比平均长度长很多的列；列很少更新；使用 UTF-8 这样较复杂的字符集每个字符原本使用的字节数就可能不同；
CHAR 使用与存储较短的字符串，且长度想当；
对于单值字符或者极短的字符串，CHAR 比 VARCHAR 有效，例如使用 CHAR(1)存储'Y'或'N'，只需要一个字符，如果使用单字节字符集，只占用一个字节，而 VARCHAR(1)却需要额外的一个表示长度的字节，共两个字节才能表示；
另外，CHAR 在遇到字符串尾部有空格时，会忽略掉（不保留），而 VARCHAR 则会保留尾部空格；

日期类型

在足够表示的情况下，推荐使用 TIMESTAMP 表示时间，因为它为 DATETIME 空间效率更高；
另外，很多人会将时间戳存储为整数值存储，这样不方便处理，所以不推荐；
对于想要保存比秒更小的时间精度时，可以使用 BIGINT 在存储微妙级别的时间戳；

数据类型属性

上面总结了 MySQL 中的数据类型，下面再来总结一些常用的属性。

• 1.auto_increment

auto_increment 能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的 ID+1。

MySQL 要求将 auto_increment 属性用于作为主键的列。此外，每个表只允许有一个 auto_increment 列。例如：

代码如下:

id smallint not null auto_increment primary key

• 2.binary

binary 属性只用于 char 和 varchar 值。当为列指定了该属性时，将以区分大小写的方式排序。与之相反，忽略 binary 属性时，将使用不区分大小写的方式排序。例如：

代码如下:

hostname char(25) binary not null

• 3.default

default 属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为 MySQL 不允许插入函数或表达式值。此外，此属性无法用于 BLOB 或 TEXT 列。如果已经为此列指定了 NULL 属性，没有指定默认值时默认值将为 NULL，否则默认值将依赖于字段的数据类型。例如：

代码如下:

subscribed enum('0', '1') not null default '0'

• 4.index

如果所有其他因素都相同，要加速数据库查询，使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升。

代码如下:

create table employees
(
id varchar(9) not null,
firstname varchar(15) not null,
lastname varchar(25) not null,
email varchar(45) not null,
phone varchar(10) not null,
index lastname(lastname),
primary key(id)
);

我们也可以利用 MySQL 的 create index 命令在创建表之后增加索引：

代码如下:

create index lastname on employees (lastname(7));

这一次只索引了名字的前 7 个字符，因为可能不需要其它字母来区分不同的名字。因为使用较小的索引时性能更好，所以应当在实践中尽量使用小的索引。

• 5.not null

如果将一个列定义为 not null，将不允许向该列插入 null 值。建议在重要情况下始终使用 not null 属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。

• 6.null

为列指定 null 属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，null 精确的说法是“无”，而不是空字符串或 0。

• 7.primary key

primary key 属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键的列赋予 auto_increment 属性是很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一标识符。主键又分为以下两种：

(1)单字段主键

如果输入到数据库中的每行都已经有不可修改的唯一标识符，一般会使用单字段主键。注意，此主键一旦设置就不能再修改。

(2)多字段主键

如果记录中任何一个字段都不可能保证唯一性，就可以使用多字段主键。这时，多个字段联合起来确保唯一性。如果出现这种情况，指定一个 auto_increment 整数作为主键是更好的办法。

• 8.unique

被赋予 unique 属性的列将确保所有值都有不同的值，只是 null 值可以重复。一般会指定一个列为 unique，以确保该列的所有值都不同。例如：

代码如下:

email varchar(45) unique

• 9.zerofill

zerofill 属性可用于任何数值类型，用 0 填充所有剩余字段空间。例如，无符号 int 的默认宽度是 10；因此，当“零填充”的 int 值为 4 时，将表示它为 0000000004。例如：

代码如下:

orderid int unsigned zerofill not null