0x00 原则

1. 更小的通常更好，更小的数据类型占用更少的磁盘空间、内存和CPU缓存，就比如说要存储性别，可以使用unsigned tinyint类型，没必要使用int

2. 简单就好，整数操作往往比字符操作或浮点数计算代价更低，比如我们要存银行卡余额，就尽量用整数，以分做单位，就比如56.89元存成int型写作5689分钱，浮点数要考虑精度，就上面银行卡余额的例子，如果存成double类型，精度丢失就容易导致钱对不上

3. 不要用NULL，而且必须把字段定义为NOT NULL并且提供默认值，原因如下

   • 如果定义表时没有指定NOT NULL，那么默认都是允许NULL的

   • 处理NULL值只能采用IS NULL或IS NOT NULL，而不能采用=、in、<、<>、!=、not in这些操作符号，例如：where name!=’Jack’，如果存在name为NULL值的记录，查询结果就不会包含name为NULL值的记录

   • NULL值需要更多的存储空间且会拉低数据库的处理性能，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识，增加数据库处理记录的复杂性，同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多

   • NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化

   • 例外：在使用InnoDB存储稀疏数据时，使用NULL有很好的空间效率，因为InnoDB使用单独的位来存储NULL，注：稀疏数据就是大部分数据为NULL，只有一小部分不是NULL，此点不适用于MyISAM

4. 尽量不用长数据类型，就比如BLOB或TEXT以及很大的VARCHAR或CHAR，举个例子，有一个1000万行的表，里面有一个使用UTF-8编码的VARCHAR(1000)的字段，每个字符占用3个字节，最坏情况下一行这个VARCHAR(1000)字段需要3000字节，如果在ORDER BY中使用这个列，并查询扫描整个表，我们就需要30001000万=30GB的临时表，这是一笔极大的开销，会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，*导致内存命中率急剧降低，影响数据库性能

0x01 数据类型的选择

0x00 数字

整数：

整数有TINYINT SMALLINT MEDIUMINT INT BIGINT 5种类型，分别占用8 16 24 32 64位存储空间，可以使用unsigned关键字仅存储正数，使其存储的正数的上限增加1倍，但要注意有符号和无符号占用相同的存储空间，具有相同的性能

同时MySQL可以为整数指定宽度，例如INT(9)，但是，他不会限制值的合法范围，加了和不加占用相同的存储空间且具有相同的性能，后面的9仅限制了MySQL的交互工具（MySQL命令行）用来展示字符的个数

实数：

FLOAT DOUBLE使用标准的浮点数运算进行近似计算，是不精确类型

DECIAML用来存储精确的小数，可进行精确计算，为精确类型

FLOAT DOUBLE在存储相同范围的值时比DECIMAL占用更少的空间，且计算代价较低

最后还是那句话：能用整数就不用小数，小数尽量采用扩大倍数的方式转换成整数，就比如一开始存储银行卡余额的例子，以分为单位将钱转换为整数，然后再在前端转换为小数

不要将手机号存成整数！

建议存成VARCHAR(20)，原因如下

1. 手机号有时候会到国家的区号，会出现+-()这样的字符，就比如中国的区号是+86美国是+1这样的

2. 我们可以构造这样的查询like '1350634%'查询数据库中所有山东省莱芜市移动手机号，用整数就很难实现

0x01 字符串

VARCHAR：

VARCHAR是可变长字符串，仅占用必要的存储空间，当然也可以让他变成定长字符串，就是对表指定ROW_FORMAT=FIXED

VARCHAR会占用额外的1-2个字节来记录字符串的长度，如果列的长度小于255则使用1个字节，否则使用2个字节

VARCHAR会节约空间，但不一定节约性能，例如UPDATE一个VARCHAR字段，新的字符串长度要比原来的大，就会导致数据库需要做额外的工作

一般在下列场景下使用VARCHAR：

1. 字符串的最大长度要比平均长度大得多

2. 列的更新很少，所以碎片不是问题

3. 使用了像UTF-8这样的复杂字符集，每个字符都使用不同的字节数来存储

注意：InnoDB会把过长的VARCHAR存储成BLOB

CHAR：

CHAR是定长的，注意MySQL会自动删除CHAR类型字符串最后的空格，VARCHAR不会

一般在下列场景下使用CHAR：

1. 比较短的字符串

2. 长度都相同字符串，就比如说密码的SHA-1值，SHA-1算法算出的16进制字符串就是定长的40位，这个时候将其存储为CHAR会相对好一些

3. 经常变更的字符串，用CHAR存储效率要高于VARCHAR

慷慨不一定明智

用VARCHAR(5)和VARCHAR(200)存储hello的空间开销是一样的，但是在查询时VARCHAR(200)会占用更多的内存，尤其是在使用内存临时表进行排序或操作时

所以最好的策略是只分配真正需要的空间

0x02 长数据

二进制长数据使用BLOB类型，长字符串使用TEXT类型，BLOB和TEXT分别为两组不同的数据类型家族，BLOB为TINYBLOB SMALLBLOB MEDIUMBLOB LONGBLOB，TEXT也类似如上，BLOB和TEXT默认都对应了SMALL的那一个

MySQL会将BLOB和TEXT作为一个独立的对象存储，InnoDB会使用专门的外部存储区域存储，然后在行内使用1-4字节的指针

MySQL在排序时对于BLOB或TEXT类型，仅排序前max_sort_length个字节

MySQL不会对BLOB或TEXT中的全部数据进行索引，也不能对其使用索引消除排序

尽量不用长数据类型，原因已在0x00原则第4点说明

如果你想存放照片或用户文件这样的数据，不如在数据库里存放文件的URI，不要让数据库去干他不擅长的事情

0x03 枚举类型

尽量不要用枚举类型，原因有如下几点

• 枚举类型可以通过新建一个表的形式代替，而且效果更好，枚举类型的更新需要进行DDL操作（ALTER TABLE），新建一个表出来的话，直接INSERT就可以了

• 枚举类型排序时使用其内部存储的那个整数进行排序，比如ENUM('WWW', 'AAA')，排序的结果就是先WWW再AAA，而不是依据字符串排序为AAA再WWW

0x04 日期时间

DATETIME：

这个能保存很大范围的时间，精度为秒，把时间封装到YYYYMMDDHHMMSS这样的一个整数中，占用8个字节，默认情况下他会显示成2018-02-11 21:50:40的形式

TIMESTAMP：

这个就是Unix时间戳，从1970年1月1日到现在的秒数，占用4个字节，表示的范围从1970-2038年

在数据库中把时间都存成GMT时间（格林尼治标准时间），这样我们就能来回倒换时区了

0x05 位数据类型

位数据类型用于存放紧凑的位数据，底层存放均为字符串类型

BIT：

BIT(1)存放1个位，BIT类型最多存放64个位，BIT的行为因存储引擎而异

• MyISAM会打包存储所有的BIT列，比如17个单独的BIT列需要存储17位的数据，MyISAM需要3个字节（24个位）来存放，计算方法为：ROUNDUP(17/8)*8

• InnoDB和Memory引擎会为每个BIT列使用一个足够存储的最小整数类型，所以不能节省空间

BIT底层存放为字符串类型，所以当1存放在BIT(1)中是得到的结果是字符1的ASCII码值而不是数字1，如下：

mysql> create table bittest(a bit(8));
Query OK, 0 rows affected (0.27 sec)
​
mysql> insert into bittest value(65); # 插入数字
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into bittest value(b'01000010'); # 插入二进制数据
Query OK, 1 row affected (0.00 sec)
​
mysql> select a, a+0 from bittest;
+------+------+
| a    | a+0  |
+------+------+
| A    |   65 |
| B    |   66 |
+------+------+
2 rows in set (0.00 sec)

我们可以看出，使用INSERT语句插入数字65，然后SELECT出来是65所对应的ASCII字符A，在SELECT一个数学表达式时a+0，所参与运算的值为数字65

最好不要使用BIT类型，如果你使用BIT类型的缘由是要存储布尔值，可以使用CHAR(1)或者TINYINT来表示

存放布尔值的野路子之使用CHAR(0)

CHAR(0)可以保存NULL值和长度为0的字符串（空字符串）

我们可以假定:当is null的时候为true，当is not null的时候为false

当值为NULL的时候他占用1个bit

但是这不被SQL-92标准所支持，MySQL可以支持这样的用法，以下摘抄自MySQL5.7官方手册：

MySQL permits you to create a column of type CHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

既然他是野路子，就说明不推荐使用这样的方法

SET：

SET跟枚举差不多，如果需要保存一组布尔值就可以考虑使用SET，它在MySQL内部以一系列打包的位的集合来表示

不推荐使用SET，SET和枚举一样改变需要进行DDL操作（ALTER TABLE)，DDL操作成本太高，灵活性较差

不过如果存放ACL访问控制列表这样的，只有几个固定的取值还是可以的，对于Linux下文件的rwx权限控制还是建议将其转换为整数存放

0x02 标识列（identifier）的选择

标识列主要就是用于主键这样的，用来区分每一行的数据，要注意的一点是一旦选择了一种类型，要确保每个关联表中使用精确的相同类型，包括像unsigned这样的属性，混用不同的数据类型容易导致性能问题，在比较时需要进行隐式类型转换，有可能导致很难发现的错误，整数是最好的选择，速度快且可以使用AUTO_INCREAMENT

避免使用字符串类型，首先因为他们很消耗空间，而且比较时速度较整数慢，对于MyISAM引擎默认会对字符串进行压缩存储，会导致很大的性能问题

更要避免使用MD5或SHA1产生的值做标识列，因为MD5或SHA1产生的值散列分布在很大的空间内，会导致INSERT和SELECT变得很慢，原因如下：

• INSERT时插入的值会随机散列在索引的不同位置，会导致页分裂，磁盘随机访问

• SELECT时逻辑上相邻的行为会分布在磁盘和内存的不同地方

• 随机值导致索引对所有类型的查询语句效果都很差，整个数据集都一样热，缓存任何一部分特定数据到内存都没有好处，如果工作集比内存大，缓存将会有很多刷新和不命中