MySQL 数据类型

MySQL 数据类型

字段类型

整型

• TINYINT:8

• SMALLINT :16

• MEDIUMINT:24

• INT:32

• BIGINT: 64

分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

• FLOAT 和 DOUBLE 为浮点类型

• DECIMAL 为高精度小数类型

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

字符串

• CHAR:定长的

• VARCHAR:变长的

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。
但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

适合场景：

VARCHAR场景：

• 字符串的最大长度比平均长度大很多
• 列的更新很少，所以碎片不是问题
• 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

CHAR场景：

• 存储很短的字符串
• 所有值都接近同一个长度，如密码的MD5值、uuid等
• 对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片）

当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。VARCHAR 会保留字符串末尾的空格。

使用VARCHAR(10)和VARCHAR(100)存有什么优势吗？

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。

时间和日期

• DATETIME
  能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间，与时区无关。

• TIMESTAMP
  和 UNIX 时间戳一样，保存从1970 年 1 月 1 日午夜(格林威治时间)以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年，与时区有关

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。
应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

BLOB 和 TEXT

BLOB和TEXT都是为存储很大的数据的数据类型，分别采用二进制和字符方式存储。

MySQL对每个BLOB和TEXT的储存与其他类型不同的：把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。

数据类型选择优化

1、更小存储空间的通常更好更快；因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；

2、使用内建类型；

• 不要使用字符串来存储日期和时间，而是时间日期来存储
• 整形比字符串操作代价更低
• 用整形存储IP地址

3、尽量避免NULL

• 查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂

• 把可为NULL的列改为NOT NULL带来的性能提升比较小

• 计划在列上创建索引，就应该尽量避免设计成可为NULL的列

4、整数类型通常是主键或者索引的最佳选择选择

• 整数类型很快并且可以使用AUTO_INCREMENT
• 应该避免使用字符串类型作为主键，因为它们很耗空间，并且比数字类型慢
• 对于完全随机的字符串（MD5、UUID），也需要避免。

全随机的字符串， 这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢

• 因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致叶分裂、磁盘随机访问。
• SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方
• 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效

数据库的三范式是什么
第一范式：列不可再分
第二范式：行可以唯一区分，主键约束
第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束
且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上