【MySQL】MySQL数据类型选择 与 阿里巴巴MySQL规范解读概要
【MySQL】MySQL数据类型选择 与 阿里巴巴MySQL规范解读概要
（1）小数类型为 decimal，禁止使用 float 和 double
（2）如果存储的字符串长度几乎相等，使用 char 定长字符串类型
（3）单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表
（4）业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
（5）超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
（6）页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
（7）不要使用 count(列名) 或 count(常量)来替代 count(*)
（8）禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
（9）in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。
（10）存储字符集推荐使用 utf8，如需存储表情，那么选择 utf8mb4
MySQL数据类型选择建议
在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：
（1）确定合适的大类型：数字、字符串、时间、二进制
（2）确定具体的类型：有无符号、取值范围、变长定长等
在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL！
避免使用NULL的原因：
- 节省空间，需要一个额外字节作为判断是否为NULL的标志位（空值不占空间）。
- 影响索引，MySQL难以优化引用可空列查询，它会使索引、索引统计和值更加复杂，索引的效率会下降很多。且对表索引时不会存储NULL值的。
- NULL不能用 = 来判断，必须要is null等函数进行判断，应用不方便
1. 数值类型
| 类型 | 大小 | 用途 |
| TINYINT | 1 字节 | 小整数值 |
| SMALLINT | 2 字节 | 大整数值 |
| MEDIUMINT | 3 字节 | 大整数值 |
| INT或INTEGER | 4 字节 | 大整数值 |
| BIGINT | 8 字节 | 极大整数值 |
| FLOAT | 4 字节 | 单精度 浮点数值 |
| DOUBLE | 8 字节 | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 小数值 |
优化建议
- 如果整形数据没有负数，如ID号，建议指定无符号类型，容量可以扩大一倍。
- 建议使用 tinyint 代替ENUM、BITENUM、SET。
- 避免使用整数的显示宽度(看后面)，也就是说，不要用 INT(10) 类似的方法指定字段显示宽度，直接用INT。
- decimal 最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
- 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
- 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
INT显示宽度
我们经常会使用命令来创建数据表，而且同时会指定一个长度。但是，这里的长度并非是TINYINT类型存储的最大长度，而是显示的最大长度。
CREATE TABLE `user`(
`id` TINYINT(2) UNSIGNED
);这里表示user表的id字段的类型是TINYINT，可以存储的最大数值是255。所以，在存储数据时，如果存入值小于等于255，如200，虽然超过2位，但是没有超出TINYINT类型长度，所以可以正常保存；如果存入值大于255，如500，那么MySQL会自动保存为TINYINT类型的最大值255。
在查询数据时，不管查询结果为何值，都按实际输出。这里TINYINT(2)中2的作用就是，当需要在查询结果前填充0时，命令中加上ZEROFILL就可以实现，如：
`id` TINYINT(2) UNSIGNED ZEROFILL这样，查询结果如果是5，那输出就是05。如果指定TINYINT(5)，那输出就是00005，但是实际存储的值还是5，而且存储的数据不会超过255，只是MySQL输出数据时在前面填充了0。
换句话说，在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用 zerofill 时有用，让查询结果前填充 0 。
2. 日期和时间
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
| DATE | 3 | 1000-01-01 到 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59' 到 '838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 到 2155 | YYYY | 年份值 |
| DATETIME（推荐） | 8 | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00 到 2038-01-19 03:14:07 | YYYYMMDDhhmmss | 混合日期和时间值，时间戳 |
优化建议
- MySQL能存储的最小时间粒度为秒。
- 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
- 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，不要使用字符串。
- 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
- TIMESTAMP是UTC时间戳，与时区相关。
- DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
- 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
3. 字符串
| 类型 | 大小 | 用途 |
| CHAR | 0-255字节 | 定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。 |
| VARCHAR | 0-65535 字节 | 变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
优化建议
- 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
- 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
- BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
- BLOB和TEXT都不能有默认值。
阿里巴巴MySQL规范解读概要
这里我们挑几条重要的阿里巴巴关于 MySQL编写的要求和建议进行讲解！
（1）小数类型为 decimal，禁止使用 float 和 double
在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。
如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
该点在上面有描述！
（2）如果存储的字符串长度几乎相等，使用 char 定长字符串类型
varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
该点在上面有描述！
（3）单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表
如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
阿里巴巴里面计算过，这样子综合效率应该是最高的。查询的效率可能变化不会很明显（因为索引树都是3层）；但是插入、删除可能会对B+索引树做一些结构的变更，对于它们的执行效率来说可能就会比较明显！
（4）业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
对于这种情形，数据的准确性会比执行效率重要的多！
唯一索引更多是为了约束而生！
（5）超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
这一个点很重要，太多的表进行关联非常影响SQL性能，而且极其难优化！
如果业务上一定要这么做，可以把这一块实现放在Java代码中，毕竟在JVM内存中进行处理比在磁盘中进行IO要快得多！而且MySQL不易扩展，放在Java中可以很随意的进行水平扩展，或者可以使用Redis缓存、消息中间件等等。
但是对于一些ERP项目，或者是一些“恶心至极”的报表开发项目而言，其实是无所谓的，因为它们只要结果，对于过程来说没有要求（点名“广汽丰田”，一条SQL执行了5个小时，才导完数据）。
（6）页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
（7）不要使用 count(列名) 或 count(常量)来替代 count(*)
count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
（8）禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
工作中基本没有用，而且存储过程的语法要求是真的高......可以多打了一个空格MySQL就会不识别，报错。
（9）in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。
这玩意挺麻烦的，如果表数据量太小可能不会走索引，如果in中的结果集太多也可能不走索引，所以这玩意能避免则避免！
