Mysql表设计需要注意的问题

database

下面探讨的数据库为MySQL 存储引擎为innodb因为这是最常见的，使用最多的数据库和引擎

什么是页分裂？
这是因为聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂。

为什么一定要设置一个主键
因为就算你不主动设置一个主键，innodb也会帮你生成一个隐藏列，作为自增主键来使用。所以，不管怎么样都会有自增主键，还不如自己指定一个，主键索引可以提高查询效率。

主键使用自增还是UUID
主键肯定是用自增。innodb种的主键是聚族索引，如果主键是自增的，那么每次插入的新数据都会顺序添加到当前索引节点的后续位置，当一页写满时，就会自动开启第二页，如果不是自增主键，那么就可能在中间插入，就会引发页的分裂，产生很多碎片，总之用自增主键性能更好。

UUID产生的索引文件更大，当数据量超过一百万行时，主键查询性能和索引文件大小都比UUID要更有效率和更小。

主键为什么不推荐有业务含义

1）因为任何含有业务的列都有改变的可能性，主键一旦带上业务含义，那么主键就有可能发生改变。主键发生改变，该数据在磁盘上的存储位置就会发生更改，有可能引发页分裂，产生空间碎片。

2）带有业务含义的主键不一定是顺序自增的，就有可能导致后边插入的数据主键一定比前面的大，如果出现后边插入的数据主键比前面插入的小，就肯能引发页分裂，产生空间碎片。

表示枚举的字段为什么不用enum类型
首先，枚举在MySQL中一般用tinyint类型，为什么不使用enum呢

1）ENUM类型，order by操作效率低，需要额外的操作

2）如果枚举是数值，有陷阱

例如：表结构如下create table test(sex enum("0","1"))此时执行插入语句insert into test values(1) 查询的结果为0

只有这样插入insert into test values("1")此时结果才是1

货币字段用什么类型
货币：如果货币单位是分，可以使用int类型，如果坚持用元的话，可以使用Decimal类型，不能使用float和double类型，因为这两个类型是以二进制存储的，所有有一定的误差。

例如：create table "t"("price" float(10,2) default null) engine=innodb default charset=utf8然后插入一条数据price=120.23,然后你会发现数据变为120.25，精度失准。

时间字段用什么类型
1）如果使用varchar，优点是显示直观，缺点是做时间比较运算的时候，需要使用str_to_date等函数把它转化为事件类型，你会发现这是无法命中索引的，数据量一大，这就是一个大坑。

2）如果使用timestamp类型，该类型为4个字节，表示的时间范围为1970-01-01 到2038-01-19 也就是说2038年以后的时间不能使用timestamp存储。该类型有一个优势，就是自带时区，如果修改了时区，该字段值会跟着改变。

3）dtatime类型，该类型为8个字节，自己维护一个时间戳，表示范围比timestamp大，因为需要自己维护，所以不太方便。

为什么不直接存储图片、音频、视频等大容量内容
在实际生产中，我们都是在文件服务器上存真实文件，数据库中保存对应的文件路劲即可，MySQL有存放大文件的类型，text和blob类型，但是基本不使用

1）MySQL内存临时表不支持text、blob这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表，导致效率低下。

2）binlog内容太多，因为数据内容过大，会导致binlog内容较多，而MySQL主从同步靠binlog进行同步，binlog太大就会导致主从同步效率问题。

字段为什么要定义为NOT NULL
1）索引性能不好

2）查询会出现一些不可预料的结果