MySQL数据类型及优化

一、数据类型

1、整数类型

存储类型：

TINTINT,SMALLINT,MEDIUMINT,INT,BIGINT,分别使用8,16,24,32,64 位存储空间。表示范围大小从-2^(N-1)到2^(N-1)-1,N为存储空间的位数。
UNSIGNED可使数据为正数，正数范围翻倍，即0到2^(N)-1。TINTINT(-128-127) TINTINT UNSIGNED(0-255)
INT(10),INT(1)设置的只是正数类型显示时的数据宽度，对于存储和计算来说，两个并无区别。

计算类型：

一般使用64位的BIGINT正数作为计算类型。一些聚合函数可能会使用DECIMAL或者DOUBLE进行计算。

2、实数类型

存储类型：

DECIMAL: 可以指定小数点前后所允许的最大位数。DECIMAL(18,9)指定小数点两边各存储9个数字，一共使用9个字节，小数点前后的9个数字各使用4个字节，小数点占一个字节。
FLOAT:4个字节
DOUBLE:8个字节

计算类型：

DOUBLE类型

3、字符串类型

VARCHAR

定义：可变长字符串，仅使用必要的空间。

特点：

1、VARCHAR需要使用1或2个额外的字节记录字符串的长度：如果列最大长度小于等于255个字节则使用1个字节表示，否则使用2个字节。如varchar(10)实际需要11个字节存储空间，varchar(1000)实际需要1002字节的存储空间。
2、MYSQL存储和检索时会保留末尾空格。

适用场景:

1、字符串的最大长度比平均长度大很多。
2、列的更新很少，不易产生碎片。
3、UTF-8这种复杂字符集，每个字符都使用不同的字节数进行存储。

CHAR

定义：定长字符串。

特点：

1、每个值占用的长度一定，即使实际并没有那么多。
2、MYSQL存储和检索时会剔除末尾空格。

适用场景：

1、存储很短的字符串，或者所有值接近同一个长度。如md5,uuid。
2、经常变更但长度相近的字符串，不容易产生碎片。
3、非常短的列，如性别M/F,char(1)优于varchar(1)，因为varchar(1)实际还需要一个字节来保存长度，空间占用上char更占优。

4、BLOB和TEXT

#定义：存储很大的二进制数据或者字符串。

#格式：

TINYBLOB-LONGBLOB
TINYTEXT-LONGTEXT

特点：

1、BLOB存储二进制数据，所以没有排序规则和字符集。TEXT有排序规则和字符集。
2、MYSQL对TEXT类型每个列的最前max_sort_length字节做排序而不是整个字符串做排序（太大），所以通过修改max_sort_length或者order by SUBSTRING(column,length)排序.
3、不可使用BOLB和TEXT列全部长度的字符串作为索引。若需要，则可以通过保存前几个长度字符作为新列来建索引。

5、日期和时间类型

DATETIME

特点

1、可保存大范围的值，从1001年到9999年，精度为秒。
2、日期和时间封装为YYYYMMDDHHMMSS格式的整数中，与时区无关，使用8个字节的存储空间。
3、以一种可排序的，无歧义的格式显示时间，如“2018-01-01 22:45:23”

TIMESTAMP

特点

1、保存了格林尼治标准时间（19700101）以来的秒数，和UNIX时间戳一致。
2、使用4个字节的存储空间，只能表示1970到2038年。
3、MYSQL显示TIMESTAMP时一般会根据时区转换成标准时间格式。
4、插入时如果未指定第一个TIMESTAMP列的值，MYSQL则会设置这个值为当前时间。
5、TIMESTAMP列默认为NOT NULL

6、ENUM和SET

定义

ENUM 枚举类型，SET集合类型

特点

1、MYSQL内部使用正数存储ENUM和SET类型，做比较时转换为字符串。
2、适用于做有序的状态，产品类型，性别等。

二、优化

1、如何选择合适的标识符列？

参考原则

1、整数通常是最好的标识符列，比较快，且可以使用自增长。
2、避免使用ENUM和SET。
3、如果可能也要避免字符串类型作为标识符。不仅消耗空间，而且比较也比整数慢。特别是在MYISAM中，因为其默认对字符串使用压缩存储，导致查询慢很多。
4、对于随机的字符串如MD5,SHA1,UUID也要特别注意，这些随机产生的值可能分布在很大的空间之内，导致select或update很慢。

随机字符串做标识符导致的问题

1、插入值会随机的写到索引不同位置，所以insert语句很慢。导致页分裂，随机磁盘访问，聚簇索引产生碎片。
2、select语句变慢，逻辑上相邻的行会分布在磁盘的不同地方。
3、违反局部性原则，一次用到的数据可能之后其周围的数据也会用到，随机存储，导致缓存各个地方的数据，整个数据集都一样的“热”。

2、范式选择优化

范式化的优缺点

优点

1、范式化的更新通常比反范式化要快，因为只需要更新一张表可能就可以了。
2、数据很好的范式化之后，就很少有冗余数据，只需修改少部分数据即可。
3、范式的表更小，容易放进内存操作，所以更快。
4、很少的多余数据意味着DISTINCT和GROUP BY等语句的减少。

缺点

很明显，查数据经常会用到关联，本来查一张表的数据，范式化拆成两个表之后就需要查两个。

反范式化的优缺点

优点：不需要关联，查一张表可能就够了。

缺点：

1、数据重复，更新操作需要更新多张表，维护复杂。
2、占用存储空间大。

3、缓存表和汇总表

缓存表

提高查询性能时，可能把多个表的数据提前查好缓存到同一张缓存表时，查询更快。例如某些查询操作需要联合多张表查询，查询比较慢，那么可以把其数据提前整合到一张表中，这时数据虽然有冗余但只需查询一张表就可以了。

汇总表

一般指使用GROUP BY 语句聚合之后的数据表。
如每1个小时汇总一次网站的访问数，存成汇总表，那么查询过去24小时的访问数，就只需查询这些汇总表即可。

4、奇技淫巧

IP4地址保存

使用32位INT UNSIGNED存储IP4地址，而不是使用字符串。因为IP4本身时无符号32位整数，而不是实际看见易记标识。

计数器表

问题

统计一个网站的访问量，文件的下载次数等需要计数的表，通长都会遇到并发问题，设计单个字段来表示计数时，通常会导致每次更新操作都申请写锁，导致锁竞争。

解决方案

加快ALTER TABLE操作速度

问题

一般情况下ALTER操作修改表结构的操作都是用新的结构创建一个新的空表，然后从旧表里查出所有数据插入新表，然后删除旧表。通常会花费很长时间。

优化方式

（1）、MODIFY COLUMN和ALTER COLUNM的区别

（2）、直接修改.frm文件