领导：习惯拍脑袋就随意建表，我怎么敢让你继续待下去？

在公司，建表并不是随心所欲的，而是需要经过审慎的研究和讨论。

首先确定要建什么表，有哪些字段

根据需求，我们可能会想新建一些表。表的数量要尽量少，可以通过在原有表加字段的方法解决的需求就不要去建新表。要思考需求有没有可能只用一张表就可以实现，这一点很难，需要对业务有充分的理解，所以建表之前要认真研究需求说明书，去明确自己要建什么表，需要哪些字段。

另外字段名应该见名知意，同时与其他表有关联的字段应该起能够让人产生联想的字段名。

确定字段的数据类型

这是个大学问，合适的数据类型能够有效减少MySQL、CPU、磁盘等的工作量。当然在一些小数据量的场景下，凭借MySQL自身的优化和高性能的硬件资源，数据类型的优化并没有显得很重要，但是这并不妨碍我们去研究和学习MySQL数据类型并在实际业务中去应用。

确定字段数据类型的大方向

首先确定字段是应该使用整数类型、浮点数类型、定点数类型、位类型、字符串类型、时间和日期类型等中的哪个类型。这看似简单，实则不然，例如：IP地址应该用整数类型来存储，而不是字符串。IP地址实际上是32位无符号整数，不是字符串，加入小数点将地址分成四部分仅仅只是为了方便人们阅读，所以应该使用无符号整型来存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法中转换。

了解每种数据类型的大小、表示范围和特点

为什么我们需要了解基本数据类型呢？表中显示了各个基本数据类型所占的字节数和表示范围，记住一个原则：更小的通常更好。在我们能够确定业务需要的数据范围下，选择占字节数最小的数据类型通常最好，因为它们占有更少的磁盘、内存和CPU缓存，所以通常更快。

下面我们讨论一下各个数据类型的特点，此处不研究数据类型的定义和使用方法，请自行去学习。

整数类型

整数类型都有可选属性UNSIGNED，表示非负，这样会让这个类型的表示范围在正数上提高一倍（因为它不再需要符号位，可以多一位用来表示数据）。有符号和无符号使用相同的存储空间，也有相同的性能，所以要不要加UNSIGNED是根据业务来决定的。

整数的计算一般使用BIGINT，即便在32为系统中也是如此，但是一些聚合函数例外，它们可能会使用DECIMAL或DOUBLE类型来进行计算。另外也可以用DECIMAL来保存一些比BIGINT还要大的数据，因为它是使用字符串来保存数据的。

实数类型

FLOAT和DOUBLE都可以使用标准的浮点计算进行近似计算（无论FLOAT还是DOUBLE在MySQL内部都是使用DOUBLE类型进行计算），但是会损失精度，所以在需要精确计算的场景，比如财务计算可以尝试使用DECIMAL类型，但是DECIMAL需要额外的存储空间和计算开销（因为CPU可以直接进行浮点数计算，而不支持DECIMAL计算，是MySQL自己实现的DECIMAL计算，所以浮点数计算更快），所以在数据量很大的情况下，可以考虑使用BIGINT来代替DECIMAL，只要将需要存储的数据按照小数位数乘以倍数即可，例如要精确到万分之一的数据520.13141，只要乘以10000变成52013141以BIGINT类型存储即可。

另外实数类型都可以指定精度，但这是非标准用法，在定义精度的情况下，MySQL为了更好的性能可能会偷偷选择不同的数据类型，或者在存储的时候对值进行取舍，而且在数据库迁移时也有麻烦，所以不建议指定精度。

字符串类型

字符串类型因字符集和排序规则对性能的影响极大，所以应该特别审慎地考虑。不同的存储引擎对字符串的存储方式不同。

VARCHAR

VARCHAR是可变长度字符串类型，比定长字符串类型更节省空间，因为它只使用必要的空间。虽然VARCHAR节省了存储空间，性能较好，但是在UPDATE时，新的数据可能比原来的更长，这样就需要做额外的处理，特别是因此导致行记录存储需要的空间变大，但是页中没有空余的空间存储，就需要通过各种方法来处理，例如MyISAM会将行记录拆分成片段进行存储，InnoDB则通过分裂页来使行能放进页内。InnoDB会把过长的VARCHAR存储为BLOG。

当存储的数据最长长度远大于平均长度，或者列很少更新，或者使用UTF-8这种不定长字符集时，可以考虑选择VARCHAR。

CHAR

CHAR存储定长字符串，MySQL会将数据的尾部空格切割掉，对于比指定长度短的数据会用空格进行填充。CHAR适合存储很短的字符串，或者字符串的长度接近某一个值，对于非常短的字符串，CAHR的效率要比VARCHAR高，例如可以使用CHAR(1)来存储Y/N或其他单字符可以表示的数据。

BLOB和TEXT

BLOB和TEXT的区别仅仅在于BLOB存储的是二进制数据，没有排序规则和字符集，TEXT存储的是字符串数据，有排序规则和字符集。

MySQL在对BLOB和TEXT排序时，是根据它们的前max_sort_length字节的内容来排序的，可以通过修改max_sort_length的值来调整排序，或者使用ORDER BY SUBSTRING(column,length)。

尽量不要使用BLOB和TEXT。

时间和日期类型

我们仅介绍DATETIME和TIMESTAMP

DATETIME

DATETIME存储年月日时分秒的时间，将时间和日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。显示时按照ANSI标准显示为YYYY-MM-DD HH:MM:SS。

TIMESTAMP

TIMESTAMP的存储空间比DATETIME小，所以表示的范围比DATETIME小得多，但是它显示的值可以根据时区进行变化。

除了特殊的需求外，一般都应该用TIMESTAMP，因为它的效率更高，但是它的随时区变化的特性在某些场景下也可能是累赘。

确定约束

主键

主键约束不是必要的，但是最好还是指定主键，因为它涉及到聚集索引。事实上如果你不指定主键，MySQL会把第一个NOT NULL的唯一索引作为聚簇索引，如果也没有，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。通常使用自增的整型来充当主键。

尽量不要使用外键约束

使用外键是有成本的，比如外键通过要求在修改数据时先在外键关联的表中进行一次查询操作，虽然MySQL强制外键使用索引，但是还是无法抵消这种约束检查带来的消耗。并且如果这个外键的选择性很低，就会导致MySQL去维护一个非常大且选择性很低的索引，而且这个索引除了做这个外键限制，就没有其他用途了。

在某些场景下，外键会提高一些性能。如果想要保证两个表始终有一致的数据，使用外键约束会比在应用程序中做一致性检查性能要高，此外在相关数据的删除和更新上，也要比在应用程序中维护效率更高。但是外键的删除和更新是逐行进行的，所以比批量删除和更新效率要低。

外键约束使得查询需要访问其他表，这就意味着需要额外的锁，可能会导致锁的等待，甚至发生死锁。

如果只是使用外键做约束，那么在应用程序中实现这种约束会更好，外键有时候是数据库性能瓶颈所在。

尽量指定非空约束

因为列默认为NULL，所以很多表可能会含有NULL的列，尽管应用程序并不需要保存NULL值也一样。通常情况下最好指定NOT NULL约束，除非业务真的需要储存NULL值。

查询中包含NULL值会使得索引、索引统计和值比较都更加复杂，MySQL很难去优化。可为NULL的列需要更多的存储空间，在MySQL中也要做特殊的处理。当可为NULL的列被索引时，每个索引记录需要额外的字节，这在MyISAM中可能导致定长索引变为变长索引。