原则
1. 更小的通常更好
尽可能使用正确存储数据的最小数据类型，较小的内存、磁盘和CPU缓存，并且CPU时间更少
2. 简单就好
简单的数据类型占用更少的cpu周期。如：
- 存储时间尽量用MySQL内置的datetime类型
- 存储IP可以用整数
3. 尽量避免使用NULL
通常情况下最好指定列为Not NULL， 除非真的需要。
- 查询中包含NULL的列，对MySQL来说，优化更难，因为可为NULL的列使索引、索引统计、值比较都更复杂
- 如果计划对列加索引，应该避免设计成可为NULL的列
- InnoDB存储引擎使用单独的位（bit）存储NULL值，对稀疏数据（很多值为NULL，只有少量非NULL值）具有很好的空间效率
SHOW CREATE TABLE shop_suborder
- 显示创建shop_suborder的语句，字段类型均为基本类型
整数类型
INT(11) 对大多数应用是无意义的，不会限制值的范围，只是规定了一些交互工具（如客户端）显示字符的个数，即int(11) == int(20)
-
TINIINT
8 bit ，存储-2^7 ~ 2^7-1 ，即-128~127
加上UNSIGNED 属性 0~255
-
SMALLINT
16 bit
-
MEDIUMINT
24bit
-
INT
32bit 存储 -2147483648 ~ 21 4748 3647
-
BIGINT
64bit
存储 -9223372036854775808L ~ 922 3372 0368 5477 5807L
实数类型
DECIMAL
- 尽量只在对小数点进行精确计算时才使用DECIMAL---如财务数据
- 允许最多存储65个数字
- 5.0以上MySQL版本DECIMAL 支持精确计算
- 可指定小数点前后允许的最大位数
- 数据量较大时，可用BIGINT代替，乘以相应倍数
FLOAT
- 4个字节存储
DOUBLE
- 8个字节存储，比DECIMAL更少的存储空间
- 比FLOAT更高的精度，更大范围
- MySQL内部 使用DOUBLE作为内部浮点计算的类型
字符串类型
- 每个 字符串列 可以定义自己的 字符串集合 和 排序规则（校验规则）
- VARCHAR、CHAR存储和具体的存储引擎有关
VARCHAR
- 存储可变长字符串
- 仅使用必要的空间，对性能有帮助（ROW_FORMAT=FIXED则每行都定长存储，浪费空间）
- 在更新时若变长则需要额外工作
- 需要1或2个额外字节记录字符串的长度，如VARCHAR(10) 需11字节存储空间
- 以下情节使用VARCHAR合适：
- 字符串的 最长 比 平均长度 大很多
- 列的更新很少，碎片不是问题
- 使用了utf-8这样复杂的字符集
- 5.0以后，存储和检索会保留末尾空格
- InnoDB把过长的VARCHAR存储为BLOB
- VARCHAR(5) 和 VARCHAR(100)存储‘hello'是一样的，但是更长的列会消耗更多的内存，尤其是实用内存表进行排序或操作时会特别糟糕——只分配需要的空间
CHAR
- 定长，MySQL分配足够的空间
- 删除末尾空格
- 非常适合存储密码的MD5值
- 对于经常变更的数据，不易产生碎片
- 如CHAR(1) 占1个字节，VARCHAR(1)占2个字节（记录长度的额外字节）
BINARY和VARBINARY
- 二进制字符串
- 存储的是字节码
- 二进制 比 字符比较简单很多
BLOB
- 二进制很大的字符串
- MySQL把每个BLOB和TEXT当做一个独立的对象处理
- 没有排序规则和字符集
TEXT
- 有排序规则和字符集
- 尽量避免使用TEXT 和 BLOB
TIPS:
EXPLAIN 执行计划的Extra列包含 Using temporary 说明查询使用了隐式临时表
枚举 ENUM
- 有时可用枚举列代替常用的字符串类型
- MySQL存储枚举时非常紧凑，内部将每个值在列表中的位置保存为整数
- 在.frm 文件中保存‘数字--字符串’的映射关系
- 排序时是按保存的整数排序，而非字符串
- 在添加或删除字符串时必须使用ALTER TABLE
- VARCHAR/CHAR ---关联--- ENUM 比 VARCHAR/CHAR ---关联-- VARCHAR/CHAR慢
- 如果不是必须关联，则使用ENUM比较好
日期和时间
- Mysql能存储的最小时间粒度--秒
- BIGINT可用来存储微秒级别的时间戳，或者DECIMAL的小数部分
DATATIME
- 1001年--9999年，精确到秒
- 8个字节
- 可排序、无歧义
- UNIX_TIMESTAMP() 函数将日期转化成时间戳
TIMESTAMP
- 4字节
- 表示范围比DATETIME小很多：1970年-2038年
- 时区相关
- FROM_UNIXTIME() 函数将时间戳转化成日期
- TIMESTAMP列默认Not NULL，与其他数据类型不同
- 空间效率更高
位数据类型
BIT
- 在一列中存储一个或多个true/false值
- 行为与存储引擎有关
- MySQL将BIT当作字符串类型，而非数字类型
- 谨慎使用BIT类型
- 存储一个true/false值可创建CHAR(0)列
SET
- 保存很多true/false值可将这些列合并到一个SET数据类型
- 打包的位的集合表示，有效利用存储空间
- FIND_IN_SET()、FIELD()这样的函数方便在查询中使用
- 改变列的代价高，需要ALTER TABLE
- 可以将8个位包装到一个TINIINT中，按位操作来使用----保存权限的访问控制列表
为标识列选择数据类型
如关联查询的比较列、外键等
- 应选择和关联表中对应列一样的数据类型，类型间需要精确匹配
- 预留未来增长空间的前提下，选择最小的数据类型
- 整数通常是标识列最好的选择--可用AUTO_INCREAMENT
- 应避免用字符串类型作标识列，因为很消耗空间，并且比数字类型慢
- MD5(), UUID(), SHA1()生成的字符串分布在很大空间，会导致INSERT以及一些SELECT很慢
- 插入随机写到索引的不同位置，所以INSERT慢，聚簇存储引擎会产生聚簇索引碎片
- SELECT语句慢，逻辑相邻的行会分布在磁盘和内存的不同地方
- 随机值导致霍村对所有类型的查询效果都差
- 若存储UUID值，应移除“-”，用UNHEX()函数转换为16字节，并存储在BINARY(16)列中，检索是可通过HEX()函数格式化为十六进制格式
- UUID虽然分布不均匀，但也还是有一定顺序的，但还不如递增的整数好用
特殊数据类型
IP存储
- 经常使用VARCHAR(15)存储IP，然而：
- IPV4实际上是32位无符号整数，不是字符串，应使用无符号整数存储IP
- MySQL提供INET_ATON() 和 INET_NTOA()转换IP地址和整数
