MySQL学习之schema类型

schema设计原则

  1. 选择能够存储正确数据的最小数据类型
  2. 简单数据类型的操作通常需要更少的CPU周期。如:整型<字符串。推荐MySQL内建类型存储日期和时间，使用整型存储IP地址。
  3. 尽量避免使NULL的列，因为NULL的列会使索引、索引统计和值比较更为复杂。（性能影响较小）

整数类型

整数

类型有两种:整数和实数

类型名

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

字节大小

8

16

24

32

64

UNSIGNED属性使数字范围翻倍。

实数

存储小数，但不完全是小数。如可使用DECIMAL存储比BIGINT大的整数。

  • 精确类型：DECIMAL
  • 浮点近似计算类型：FLOAT和DOUBLE

两者均可指定精度。

  • 存储空间：DECIMAL>浮点类型

存储大小：

  • DECIMAL类型在MySQL５.０已经更高版本可存储６５个数字，在早期版本可存储２５４个数字
  • FLOAT：4
  • DOUBLE：8

由于需要更多的空间和计算开销，因此只在精确计算当中使用DECIMAL，如财务记账等。

字符串类型

可变长字符串：VARCHAR 固定长度：CHAR

VARCHAR

VARCHAR需要在有1~2个字节记录长度，小于255时为1，大于255时为2。
由于变长，因此UPDATE时会导致额外工作，具体由存储引擎而定（InnoDB或MyISAM）。

二进制字符串

BINARY和VARBINARY

BLOB和TEXT类型

对应TINY*，SMALL*，MEDIUM*，LONG*。避免使用。

使用枚举（ENUM）代替字符串类型

优点：存储紧凑，使表的大小变小。MySQL内部会将每个值在列表中的位置转换为整数
缺点：内容固定，添加和删除字符串必须通过ALTER TABLE。
注意：ENUM和VARCHAR/CHAR进行关联时会使QPS变小。

日期和时间类型

日期类型：DATETIME和TIMESTAMP

DATETIME
  • 范围更大，精度为妙
  • 8字节存储
  • 格式如：2008-01-16 22:37:08
TIMESTAMP
  • 时间戳
  • 4字节存储

如无特殊要求，应优先使用TIMESTAMP，其空间效率更高。
可使用BIGINT或DOUBIE替换比秒还小的值。

位数据类型

BIT实际为字符串类型，可使用BIT在一列中存储一个或多个true/false值
BIT由于其字符串存储的特性，因此在使用时会存在与数字发生歧义。因此应避免使用。
SET也可存储一个或多个true/false值，缺点为改变列的代价高，需使用ALTER TABLE。（替代方案为使用TINYINT类型等代替）

标识列的选择

  1. 整数作为标识列最好，并可以使用AUTO_INCREMENT
  2. ENUM和SET可作为产品类型等的标识列，从而生成术语表，供网站生成下拉菜单。一般情况下应避免使用ENUM和SET作为标识列
  3. 避免使用字符串作为标识列。
  4. 不能使用随机值如MD5作为标识列，会导致页分裂、磁盘随机访问等情况发生，从而使查询变慢。
  5. UUID存储时去掉’-’，并使用UNHEX()进行转换为16字节数字，并存在BINARY(16)中。检索时使用HEX()。UUID与MD5等不同，有一定顺序。