MySQL数据库表设计最佳实践——《高性能MySQL》第三版第四章学习笔记-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

1.选择合适的数据类型

MySQL的表是由多个字段组成的，那字段的类型改如何选择才更优呢？

1.1通用原则

1.1.1尽可能小

怎么做？

在满足业务需求的前提下，适当考虑20%的富余量，选择尽可能小的数据类型，这里的小是指的占用存储空间更小的数据类型，数据类型占用的空间可以在MySQL的手册中查找到。

因为一般互联网项目的业务总是在不断变化，而IE成功的互联网目业务的增长速度是很快的，你不可能永远满足未来的需求，只需要满足当前的业务需求即可，而且一般都是可以重构来满足心的业务需求的，只是重构的成本高低问题。

为什么？

更小的数据类型，意味着它对应的数据文件和索引文件都会更小，它占用的磁盘空间更小，运行时候消耗的内存和CPU缓存也都会更小，在有限的存储资源里能够存更过的数据量，因此这是更优的选择。

1.1.2越简单越好

怎么做？

在满足业务需求的前提下，选择那些CPU运算更简单的数据类型，整型比字符类型操作代价更低，使用内建的日期时间型要比使用字符串类型表示日期时间更简单，使用整型存储IP地址比字符型更简单。

为什么？

因为越简单的类型在查询的时候消耗的CPU周期，因此在进行查询的运算操作的时候拥有更高的效率，这有利于数据库表在以后的查询时拥有更高的效率。

尽量避免null

怎么做？

尽可能避免将那些可能会作为索引字段（符合索引字段要求的字段，比如经常作为查询条件、惟一性好、长度较短）的字段设为null，可以通过设为not null,然后将改字段设置一个无业务意义的默认值，比如整型的默认值是0。而对于innodb的表，如果该字段的值非常稀疏（即大部分值都是null）则设为null则更优，能够节省存储空间。

为什么？

因为对于设为null的字段，对于MySQL来说更难进行优化，它会是的索引、索引统计和值比较都更加复杂，可为null的列会使用更多的存储空间，在MySqL里也需要特殊处理，当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM会导致固定大小的索引变成可变大小的索引，这样导致索引的存储和查询都会更加复杂。

1.2各类型最佳实践

数值类型

整数类型

类型名称	占用空间（位）
tinyint	8
smallint	16
mediuint	24
int	32
bigint	64

注意事项：

对于一般业务来说是不需要存储负数的，则应该设置为unsigned,则存储范围可以增加一倍。绝大多数字段可以使用该向优化。

实数类型

类型名称	占用空间（位）	说明
float(m, d)	32	m总个数，d小数位，不支持精确计算。
double(m,d)	64	m总个数，d小数位，不支持精确计算。
decimal(m, d)	字符串	支持精确计算，实际以字符串存储，计算更加复杂，在对计算精确度要求高的场景下使用，慎用该类型。

注意事项：

1.实数类型相对于整型更加复杂，在某些场景下使用整型来代替实数类型效率更高，比如某些小数位数少而固定的场景，则可以用整型代替。比如两位小数的场景，实际数据为1.00，存储为100.通过100/100来还原到原来的数据。

2.decimal支持精确的计算，对于精确性要求高的场景，比如金额可以使用该类型，但是该类型的存储结构复杂，运算也比较复杂。应该少用该类型。

字符串类型

char

是固定长度的字符串，会分配固定的空间来存储值，若值不足长度也会用空格补齐。适合于存储很短的字符串或者长度很接近的字符串。比如用char(1)来存储只有Y和N两个值的内容。也适合存储长度固定的密码的md5值，varchar需要额外的字节来存储长度，对于比较短的内容，char会更优，节省长度字节。

varchar

varchar是存储动态长度的字符串，需要额外的1-2个字节来存储字符串的长度。对于长度变化差异较大的字符串内容使用varchar更加节省空间。而且一般varchar的长度不宜过长，在innodb中如果长度过长则会自动转为blob类型进行存储。

blob和text。

这两种类型都是为存储很大的数据而设计的，分别存储二进制和字符串数据。MySQL对这两种类型都是特殊处理的，会把这些值当作一个独立的对象处理，当blob和text类型的值太大时，innodb会使用额外的存储空间来存储，此时每个值需要1-4个字节来存储指针值。而且MySQL不会对这两种类型的值进行索引，memory引擎根本不支持这两种类型。

因此在对性能要求较高的互联网项目里根本就不推荐使用这两种类型，我们推荐的做法是将这些内容存储到分布式文件系统中，通过文件路径来引用这些内容，通过特殊的读取方式来读取文件内容。