Sep 29, 2022

MySQL 表设计注意问题

为什么一定要设一个主键？

不管设置不设置主键，innodb 也会生成一个隐藏列，作为自增主键。所以啦，反正都要生成一个主键，那你还不如自己指定一个主键，在有些情况下，就能显式的用上主键索引，提高查询效率！

自增。数据在物理结构上是顺序存储，性能最好。

把自增主键改成 bigint 类型就好了。不过一般 int 类型用不到最大值，就分表分库了。

因为任何有业务含义的列都有改变的可能性，主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。
带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。

如果货币单位是分，可以用 Int 类型。如果坚持用元，用 Decimal [ˈdes (ə) məl]。千万不要答 float 和 double，因为 float 和 double 是以二进制存储的，所以有一定的误差。

varchar，如果用 varchar 类型来存时间，优点在于显示直观。但是坑的地方也是挺多的。比如，插入的数据没有校验，你可能某天就发现一条数据为 2013111 的数据，请问这是代表 2013 年 1 月 11 日，还是 2013 年 11 月 1 日？其次，做时间比较运算，你需要用 STR_TO_DATE 等函数将其转化为时间类型，你会发现这么写是无法命中索引的。数据量一大，是个坑！
timestamp，该类型是四个字节的整数，它能表示的时间范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07。2038 年以后的时间，是无法用 timestamp 类型存储的。但是它有一个优势，timestamp 类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区 SET TIME_ZONE = “america/new_york”; 你会发现，项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！
datetime，datetime 储存占用 8 个字节，它存储的时间范围为 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，他存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！
bigint，也是 8 个字节，自己维护一个时间戳，表示范围比 timestamp 大多了，就是要自己维护，不大方便。

我们在实际应用中，都是用 HDFS 来存储文件。然后 mysql 中，只存文件的存放路径。mysql 中有两个字段类型被用来设计存放大容量文件，也就是 text 和 blob 类型。但是，我们在生产中，基本不用这两个类型！

Mysql 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢
binlog 内容太多。因为你数据内容比较大，就会造成 binlog 内容比较多。大家也知道，主从同步是靠 binlog 进行同步，binlog 太大了，就会导致主从同步效率问题！

create table table_2 (
`id` INT (11) NOT NULL,
name varchar(20) NOT NULL
)

表 table_2 数据是这样的:

你执行语句

select count(name) from table_2;

会发现结果为 2，但是实际上是有四条数据的！