表过大如何优化

通过优化数据类型、合理增加冗余字段、拆分表和使用非空约束等方法，来改进表的设计，从而提高查询性能。

数据类型优化

在改进表的设计时，首先可以考虑优化字段的数据类型。下面我就来讲解 2 种方法，一种是针对整数类型数据，尽量使用小的整数类型来定义；另外一种是，如果字段既可以用文本类型，也可以用整数类型，尽量使用整数类型。

先说第一种方法，对整数类型数据进行优化。

遇到整数类型的字段可以用 INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。

但是，随着你的经验越来越丰富，参与的项目越来越大，数据量也越来越多的时候，你就不能只从系统稳定性的角度来思考问题了，还要考虑到系统整体的效率。

这是因为，在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。这个时候，你就必须同时考虑稳定性和效率。

第 2 种优化方法，就是既可以使用文本类型也可以使用整数类型的字段，要使用整数类型，而不要用文本类型。

跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存。所以，遇到既可以使用文本类型，又可以使用整数类型来定义的字段，尽量使用整数类型，这样可以提高查询的效率。

在 demo.test1 中，我给商品编号设定的数据类型是 MEDIUMINT，给流水唯一编号设定的数据类型是 BIGINT。

这样设定的原因是，MEDIUMINT 类型的取值范围是“无符号数 0 – 16777215”。对于商品编号来说，其实够用了。我的 400 万条数据中没有超过这个范围的值。而流水唯一编号是一个长度为 18 位的数字，用字符串数据类型 TEXT 肯定是可以的，大整数类型 BIGINT 的取值范围是“无符号数 0 – 18446744083709551616”，有 20 位，所以，用大整数类型数据来定义流水唯一编号，也是可以的。

原来，INT 类型占用 4 个字节存储空间，而 MEDIUMINT 类型只占用 3 个字节的存储空间，比 INT 类型节省了 25% 的存储空间。demo.test1 的第一个字段的数据类型是 MEDIUMINT，demo.test 的第一个字段的数据类型是 INT。因此，我们来对比下两个表的第一个字段 ，demo.test1 占用的存储空间就比 demo.test 节省了 25%。

再来看看这两个表的第二个字段：流水唯一编号 transuniqueid。在 demo.test 中，这个字段的类型是 TEXT，而 TEXT 类型占用的字节数等于“实际字符串长度 + 2”，在咱们的这个场景中，流水唯一编号的长度是 18，所占用的存储空间就是 20 个字节。在 demo.test1 中，流水唯一编号的数据类型是 BIGINT，占用的存储空间就是 8 个字节。这样一来，demo.test1 在第二个字段上面占用的存储空间就比 demo.test 节省了（20-8）÷20=60%。很明显，对于流水唯一编号字段，demo.test1 比 demo.test 更加节省空间。

因此，我建议你，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充分发挥资源的效率，使系统达到最优。

合理增加冗余字段以提高效率

在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

不过，你要注意的一点是，这样一来，商品流水表中包含了一个冗余字段“商品名称”，不但存储空间变大了，而且，如果某个商品名称做了修改，一定要对应修改流水表里的商品名称。否则，就会出现两个表里的商品名称不一致的情况。

所以，在实际的工作场景中，你需要权衡增加冗余字段的利与弊。这里给你一个建议：增加冗余字段一定要符合 2 个条件，第一个是，这个冗余字段不需要经常进行修改；第二个是，这个冗余字段查询的时候不可或缺。只有满足这两个条件，才可以考虑增加冗余字段，否则就不值得增加这个冗余字段了。

拆分表

跟刚刚的在表中增加冗余字段的方法相反，拆分表的思路是，把 1 个包含很多字段的表拆分成 2 个或者多个相对较小的表。

这样做的原因是，这些表中某些字段的操作频率很高，经常要进行查询或者更新操作，而另外一些字段的使用频率却很低，如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。

这个时候，如果把这个大表拆分开，把使用频率高的字段放在一起形成一个表，把剩下的使用频率低的字段放在一起形成一个表，这样查询操作每次读取的记录比较小，查询效率自然也就提高了。

使用非空约束

在设计字段的时候，如果业务允许，我建议你尽量使用非空约束。这样做的好处是，可以省去判断是否为空的开销，提高存储效率。而且，非空字段也容易创建索引。使用非空约束，甚至可以节省存储空间（每个字段 1 个比特）。

这样一来，我们就省去了判断空值的开销，还能够节省一些存储空间。

总结

• 修改数据类型以节省存储空间；
• 在利大于弊的情况下增加冗余字段；
• 把大表中查询频率高的字段和查询频率低的字段拆分成不同的表；
• 尽量使用非空约束。

但是，我要提醒你的是，这些方法都是有利有弊的，比如，修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围；增加冗余字段的时候，不要忘了确保数据一致性；把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。因此，你一定要结合实际的业务需求进行权衡。