mysql 避坑建议(整合网上资料)

字段类型的避坑建议

1、数字类型-整型

 在整型类型中分为无符号和有符号，默认的取值范围是有符号的；设计表字段属性时，如非必要，不建议使用无符号的数据类型，因为当字段类型设置为无符号时，如果需要在数据库中进行加减操作时，一旦出现相减的数值为负数的时，就会报错，因为Mysql 中无符号类型的数值进行加减操作后，数值依然是无符号类型，所以当操作后的值为负数时，Mysql 就会报计算结果超出范围的错误；如果需要避免这个错误，就需要设置数据库参数 sql_mode 为 NO_UNSIGNED_SUBTRACTION，允许相减的结果为有符号的，这样最终的结果才是正确的； 

2、数值类型-浮点型

float 和double 类型是不建议使用的，因为这两者都会存在一个精度问题，如果是设计到金额的系统，那么一般会选中使用 DECIMAL；但是使用 DECIMAL就一定合适吗？这里有一个问题，DECIMAL是需要指定长度的，那么这个长度为多少合适呢，有没有肯能出现特殊情况呢？这里建议使用bigint 来存储金额数据，这样做的好处有几个：1.bigint的数值能存到千兆级别(1兆 = 1万亿)，对于金额系统是完全够用的；2.bigint只占用8个字节，就可以满足日常需要，DECIMAL如果要保存大额数字，就会占用更多的字节；3.使用整型参与计算比DECIMAL效率高(DECIMAL 是通过二进制实现的一种编码方式，所以计算效率没有整形高)。

 3、日期类型

设计时间类型时，推荐使用DATETIME：1、5.6版本后DATETIME类型支持毫秒级；2、DATETIME不存在时区问题；3、DATETIME时间能到9999年，比TIMESTAMEP长；4、大规模并发访问时，性能比TIMESTAMEP强

表压缩

在 MySQL 中，一个页的大小默认为 16K，一个个页又组成了每张表的表空间。通常我们认为，如果一个页中存放的记录数越多，数据库的性能越高。这是因为数据库表空间中的页是存放在磁盘上，MySQL 数据库先要将磁盘中的页读取到内存缓冲池，然后以页为单位来读取和管理记录。一个页中存放的记录越多，内存中能存放的记录数也就越多，那么存取效率也就越高；

启用压缩技术，数据库可以根据记录、页、表空间进行压缩，不过在实际工程中，我们普遍使用页压缩技术，这是为什么呢？

　　压缩每条记录： 因为每次读写都要压缩和解压，过于依赖 CPU 的计算能力，性能会明显下降；另外，因为单条记录大小不会特别大，一般小于 1K，压缩效率也并不会特别好。

　　压缩表空间： 压缩效率非常不错，但要求表空间文件静态不增长，这对基于磁盘的关系型数据库来说，很难实现。

而基于页的压缩，既能提升压缩效率，又能在性能之间取得一种平衡。

COMPRESS 页压缩

　　COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能；在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例即可；COMPRESS 页压缩就是将一个页压缩到指定大小，但是当16K的页无法压缩到指定大小时，会产生多个压缩后的页；

这里需要注意虽然 COMPRESS 压缩可以有效减小存储空间，但 COMPRESS 页压缩的实现对性能的开销是巨大的，性能会有明显退化。主要原因是一个压缩页在内存缓冲池中，存在压缩和解压两个页；

TPC 压缩

TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩；在创建时设置 COMPERSSION=ZLIB即可，通过ALTER 进行设置时，需要执行OPTIMIZE TABLE命令才可生效；由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的；空洞压缩的另一个好处是，它对数据库性能的侵入几乎是无影响的（小于 20%），甚至可能还能有性能的提升；

这里需要注意：文件系统的最小单位 4K，且其页压缩是 4K 对齐的。比如一个 16K 的页，压缩后为 7K，则实际占用空间 8K；压缩后为 3K，则实际占用空间是 4K；若压缩后是 13K，则占用空间依然为 16K。

数据库设计上的避坑建议

1、自增主键

在设置自增主键时如果使用int类型，那么会存在一个问题，一个int的最大值是2147483647，对于互联网项目来说，如果每日产生的数据条数按千万计算，几百天就能将表数据装满，当然这里不考虑分表的问题；自增主键在8.0版本之前是不持久化的，这样会存在回溯现象(删除数据后，AUTO_INCREMENT字段会在数据库重启之后回到未使用的数字，这里有个问题就是如果其他表存在这个自增键的引用，如果发生回溯，那么就会发生数据错乱的问题)

2、字符集

在设计数据库是，最好将mysql的默认字符集设置为UTF8MB4；utf8 是 MySQL 中最早支持的 Unicode 字符集，它使用 1 到 3 个字节来编码每个字符，当存储的数据是 Emoji 表情、部分罕用汉字、新增的 Unicode 字符等这些字符需要 4 个字节的数据时，会出现报错或者乱码；

3、核心表新增last_modify_time字段

　核心表新增一个last_modify_time字段，并且设置修改自动更新机制(CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6))，用于保存用户的上一次修改时间，这样在电商的订单表中，可以方便对支付超时的订单做处理；在金融业务中，可以根据用户资金最后的修改时间做相应的资金核对等。

 4、业务自定义生产主键

使用自增字段做主键时，自增存在回溯问题(5.6以前版本)；自增主键在当前实例中能保证唯一，却不能保证全局唯一，在分布式架构设计时存在问题，分表功能实现麻烦；

使用UUID做主键，虽然能保证全局唯一标识，但是UUID是根据时间位逆序存储，前 4 个字节会随着时间的变化而不断“随机”变化，并非单调递增。而非随机值在插入时会产生离散 IO，从而产生性能瓶颈；8.0版本可以通过UUID_TO_BIN函数解决排序问题;

INSERT INTO User VALUES UUID_TO_BIN(UUID(),TRUE)

虽然8.0解决了UUID排序问题，但是在面对分布式数据库架构时，仅仅使用UUID做主键依然不够；

使用业务自定义生成主键，可以通过规则保证全局唯一，并且可以再主键中加入额外的信息，来保证后续耳机索引的查询效率，推荐设计如下：

PK = 时间字段 + 随机码（可选） + 业务信息1 + 业务信息2....