选择更优的数据类型
- 尽量选择存储空间更小的数据类型
空间更小占用的磁盘空间会更小，更加节省空间
- 数据类型尽量简单
可以选择简单的数据类型就不要选择复杂的，例如可以用INT存储数据就不要用DOUBLE或者FLOAT存储，这没有任何意义
- 尽量不要使用NULL值列
因为NULL会给索引带来额外的存储空间、在SQL查询中更难优化
整数类型
| 类型 | 占用空间（BIT） |
|---|---|
| TINYINT | 8 |
| SMALLINT | 16 |
| MEDIUMINT | 24 |
| INT | 32 |
| BIGINT | 64 |
一般会选择BIGINT作为主键列的列类型，注意：即使在创建列时指定数据长度，也无法改变占用空间。例如INT(11)和INT(20)都是占用32位存储空间，指定数据长度只能够在显示结果的时候起作用。
在运算操作中，一般会使用BIGINT数据类型，即使在32位计算机中也如此。
实数类型
实数类型包括了小数部分，在MySQL中主要有三种数据类型：FLOAT、DOUBLE、DECIMAL
| 数据类型 | 占用空间（Byte） |
|---|---|
| FLOAT | 4 |
| DOUBLE | 8 |
| DECIMAL | 动态指定 |
FLOAT和DOUBLE都是采用近似浮点运算，有可能出现精度损失问题；DECIMAL是精确存储数字的类型，在运算中可以不损失精度，一般用于精确数值运算场景-例如财务数据；DECIMAL类型每9个数字占用4个字节。
DECIMAL可以动态指定数据长度，例如：DECIMAL(18, 9)代表保留小数点前9位、后9位，存储空间为9个字节，小数点占1个字节。
注意：CPU本身不支持浮点数的精确运算，DECIMAL的精确运算在MySQL服务器中实现，而运算速度比CPU的浮点运算慢，即FLOAT和DOUBLE的运算比DECIMAL快
字符串类型
| 数据类型 | 特点 |
|---|---|
| CHAR | 定长、MySQL5.0开始去除结尾空格 |
| VARCHAR | 变长、MySQL5.0开始保留结尾空格、额外占用1~2个字节标识数据长度 |
选用VARCHAR的标准：
- 数据的长度差异较大
- 不确定数据的长度
- 每个字符占用的字节数不一致，例如UTF-8编码下的字符可能占用1~2个字节
选用CHAR的标准：
- 数据的长度较一致，例如长度相同的MD5散列码值
- 数据长度很短的列，例如存储'Y'或'N'，因为VARCHAR需要额外1个字节存储数据长度
VARCHAR类型在更新时可能因为数据长度变得比原来更长，磁盘没有足够的存储空间存储该值，导致页分裂；而CHAR类型相对稳定，因为长度一致。
VARCHAR类型有可能导致内存碎片，它在存储值时如果数据长度未满，则会浪费一些存储空间，所以定义VARCHAR的数据长度需要谨慎考虑，以免产生大量内存碎片。例如VARCHAR(20)和VARCHAR(255)存储最大长度为15个字节的字符串，那么VARCHAR(255)就非常奢侈了。
日期和时间类型
| 数据类型 | 特性 |
|---|---|
| TIMESTAMP | 与时区有关，日期范围为1970~2038年，占用4个字节，默认为NOT NULL |
| DATETIME | 与时区无关，保存日期时间，格式YYYYMMHHMMSS，日期范围1000-9999年，占用8个字节 |
建议使用TIMESTAMP而不是DATETIME，因为在存储空间上占有优势，TIMESTAMP可以利用FROM_UNIXTIME()转换为日期，UNIX_TIMESTAMP()转换为时间戳，也可以在应用程序级别上进行两者的转换输出。
由于TIMESTAMP默认是NOT NULL，如果在插入时没有指定时间戳，会采用当前的时间戳存储。
位数据类型
| 数据类型 | 特性 |
|---|---|
| BIT | 最大占用64位，动态指定长度，保存二进制串 |
| SET | 一系列打包的位数据类型的集合，无法使用索引 |
在开发中应尽量少或者非常谨慎的使用BIT，原因如下：
- 在字符串上下文中，BIT查询到的结果是ASCII码对应的字符，而在数字上下文中，显示的是ASCII码本身的值
例如：
CREATE TABLE bittest(a bit(8));
INSERT INTO bittest (a) VALUES (b'00111001');
SELECT a, a + 0 FROM bittest;
复制代码| a | a + 0 |
|---|---|
| 9 | 57 |
SET可以用于存储多个BIT的值，例如('dog', 'fish', 'chicken')，但是带来的代价是修改表开销非常大，所以有另外一种更好的方式替代SET-利用整数的每一位代表不同的含义。
例如：使用TINYINT，占用8位，'dog'采用最低位，'fish'采用次低位，'chicken'采用次次低位
| 含义 | 整数值 |
|---|---|
| dog | 1 |
| fish | 2 |
| chicken | 4 |
| dog、chicken | 5 |
这样做的好处：
- 如果要表示新的含义，不用频繁地修改表
- 可以在应用程序级别上表示各个值的含义
类似的Linux操作文件的权限：
- READ：4
- WRITE：2
- EXECUTE：1
那么也可以使用TINYINT标识某个用户的执行权限
| 含义 | 整数值 |
|---|---|
| READ、WRITE、EXECUTE | 7 |
| READ | 4 |
| READ、WRITE | 6 |
| EXECUTE | 1 |
| ······ | ······ |
选择标识列
通常有两种选择：整数类型、字符串类型
整数类型：
- 最好采用
AUTO_INCREMENT，这有利于提高索引查找的效率，因为相邻的数据在磁盘上相隔不远，大概率在同一个磁盘页内；
字符串类型：
- 由于字符串类型是无序的，在索引分布上不均匀，根据主键查找数据时效率非常低，如果使用字符串做主键，最好采用
UUID()生成主键，因为在某种程度上UUID生成的字符串也是有序的，只不过程度很低 - 插入时，查找插入的位置也要比整数类型效率低下，原因也是因为索引分布不均匀
范式和反范式的使用
先看看什么是三范式：
- 第一范式满足每一列都是不可再分的、最小的原子列
- 第二范式满足非主属性完全依赖于主属性
- 第三范式满足所有非主属性都直接依赖于主属性，没有传递依赖
经典的“雇员-部门-领导”表：
| EMPLOYEE_ID | EMPLOYEE | DEPARTMENT | HEAD |
|---|---|---|---|
| 1 | Jones | Accounting | Jones |
| 2 | Smith | Engineering | Smith |
| 3 | Brown | Accounting | Jones |
| 4 | Green | Engineering | Smith |
该表存在如下问题：
- 如果员工不存在，部门和领导的信息也没法显示
- 如果员工
Jones和Brown的部门领导显示不一致，就不知道哪个是正确的了
所以上面的表结构符合第一范式，第一范式有可能出现的问题就是：删除异常、修改异常
范式化
将上面的表“升级”，得到的结果如下
“员工-部门”表：
| EMPLOYEE_ID | EMPLOYEE | DEPARTMENT_ID |
|---|---|---|
| 1 | Jones | 1 |
| 2 | Smith | 2 |
| 3 | Brown | 1 |
| 4 | Green | 2 |
“部门-领导”表：
| DEPARTMENT_ID | DEPARTMENT | HEAD |
|---|---|---|
| 1 | Accounting | Jones |
| 2 | Engineering | Smith |
解决了上面的两个问题了：
- 这里的
DEPARTMENT_ID作为外键关联列，只要DEPARTMENT或者HEAD作修改，都不会产生一致性问题 - 即使删除所有的
EMPLOYEE也不会丢失部门信息
上面的表结构符合第二范式（实际上也满足第三范式），在很多情况下符合第二范式已经很不错了，范式化的缺点有：
- 范式越高，查询的代价越大，造成过多的表关联，第三范式即是如此
- 关联查询在一些情况下使索引失效，全表扫描
范式化的优点有：
- 每张表的内存较小，数据操作更快
- 更新操作更容易、代价更小
- 很少出现冗余数据，例如第一范式存在
DEPARTMENT和HEAD冗余
反范式化
"USER"用户表：
| USER_ID | USER_NAME | ACCOUNT_TYPE |
|---|---|---|
| 1 | Jhon | premiumv |
| 2 | Jack | guest |
| 3 | Sam | guest |
| 4 | Mike | premiumv |
"MESSAGE"消息表：索引列（PUBLISHED）
| MESSAGE_ID | MESSAGE_TEXT | PUBLISHED | USER_ID |
|---|---|---|---|
| 1 | HELLO, I AM JHON! | 2020-01-01 12:00:00 | 1 |
| 2 | HELLO, IT'S SAD TODAY! | 2020-01-02 23:11:12 | 1 |
| 3 | I AM SORRY TO HEAR THAT | 2020-01-03 09:00:00 | 2 |
| 4 | BYE BYE! | 2020-01-03 10:00:00 | 3 |
| 5 | I AM PREMIUMV! | 2020-01-04 9:33:33 | 4 |
要查看付费用户的最近的10条消息，可以用关联查询：
SELECT MESSAGE_TEXT, USER_NAME
FROM USER
INNER JOIN MESSAGE
ON USER.USER_ID = MESSAGE.USER_ID
WHERE ACCOUNT_TYPE = 'preminmv'
ORDER BY PUBLISHED DESC
LIMIT 10;
复制代码但是这会扫描USER表中的所有用户，判断是不是付费用户preminmv，效率极低，即使调换查询顺序也是一样的道理，此时可以在MESSAGE表中增加两个冗余字段ACCOUNT_TYPE和USER_NAME，建立索引(PUBLISHED, ACCOUNT_TYPE)，只需要查询MESSAGE表，且可以利用组合索引扫描，效率提高很多。
SELECT MESSAGE_TEXT, USER_NAME
FROM MESSAGE
WHERE ACCOUNT_TYPE='preminmv'
ORDER BY PUBLISHED DESC
LIMIT 10;
复制代码反范式化大部分都是通过添加冗余字段达到目的，优点是：
- 避免关联查询，可以使用组合索引提高查询效率
缺点是：
- 字段冗余（这看起来很搞笑）
混用范式化和反范式化
完全的范式化导致查询的昂贵代价、完全的反范式化导致插入异常和删除异常，折中的办法就是两者混用：
- 如果在某个查询中代价过大，加入冗余字段可以减轻查询的代价（参考反范式化）
- 如果存在插入异常和删除异常，果断需要范式化（参考范式化）
