MySQL中的表设计优化
在MySQL数据库中，表设计的优劣同样对性能有非常重要的影响。本节将介绍表设计的优化方法，包括巧用多表关系、表结构设计优化和表拆分等。
巧用多表关系
在进行数据库表的设计时，应尽量满足三范式，而字段冗余存储是经常遇到的一个问题。比如，在如图1所示的销售明细表中，只要一件商品有多笔销售记录，商品名称、计量单位和商品单价都会被重复记录，因此会存在大量的冗余数据。
图1 销售明细表
如果想解决这些数据的冗余存储问题，可以考虑把这三个字段单独存放在商品表（商品编号作为主键）中，然后通过在销售明细表中添加商品编号作为外键，建立商品表和销售明细表之间的联系，关系图如图2所示。这样就避免了商品的相关属性列的多次重复存储。当需要得到如图1所示的明细表时，使用join进行表连接即可实现。
图2 关系图
需要注意的是，没有冗余的数据库未必是最好的数据库，冗余越小，所产生的数据表就越多，必然会导致查询数据时表之间的join连接操作越来越频繁。而表连接操作是性能较低的，尤其是时刻都在频繁增长的包含海量数据的表，join操作会成为数据库性能的瓶颈。因此，在实际应用中，有时为了提高运行效率，需要运用逆规范化进行反范式设计，降低范式标准，适当保留冗余数据，用空间来换时间。
反范式设计的好处是减少了表的数目，从而减少了join操作及外码和索引的数目，但是可能带来数据的完整性问题。另外，反范式设计虽然能加快查询速度，但是对数据的修改却需要更多的成本。因此，在进行反范式设计之前，一定要权衡利弊，充分考虑应用的数据存取需求及表的大小等因素。在实际应用场景中，经常根据实际需求，采用范式化和反范式化混用的方式来提高数据库的性能。
&提示：表优化设计是一个平衡性技巧：
- 当存储空间足够多时，可以侧重于对性能的追求，毕竟在商业环境下，响应速度越快，用户的体验感越好。
- 只有囊中羞涩，买不起更多的存储设备时，才毫厘必争，尽量拆分表，以减少数据冗余。
表结构设计优化
在进行表结构设计时，选择合适的数据类型，慎用NULL值，适度冗余，适当进行表拆分等方法对提高性能是至关重要的。表结构设计优化采取的措施通常包括以下几个方面。
- 尽量使用可以正确存储数据的最小的数据类型。在数据类型选择上尽量选择够用的数据类型，避免选择大存储空间的数据类型浪费磁盘、内存和CPU缓存空间，并且处理时也需要更长的CPU周期，处理速度慢。例如，记录人名字段，给了200字节的Char字段定义，显然非常浪费空间。
- 尽量使用简单的数据类型。简单的数据类型的操作通常需要更少的CPU周期。例如，整型比字符型操作代价更低。
- 尽可能使用NOT NULL定义字段。NULL值不利于索引，MySQL难以优化可为NULL的列查询。当可为NULL的列被索引时，每个索引记录需要一个额外的字节用于标识其是否可空。如果某列计划要创建索引，要尽量避免将其设计成可为NULL。
- 设计逻辑删除字段，尤其是业务数据。逻辑删除便于恢复数据，不建议进行物理删除，一旦误删，数据将不可恢复。
- 尽量少用text类型，非用不可时最好将其单独拆成小表。当表中存在类似于text或者很大的varchar类型的大字段时，如果在多数情况下访问该表时并不需要这个字段，那么可以将其拆分到另一个的独立的表中。
- 把常用属性分离成小表。可以考虑把常用字段和不常用的字段分离存储，把查询频度低的字段单独拆出来存储。
上述仅是理想状态下表结构设计优化措施，在实际商业环境下，需要根据实际情况进行灵活设计，合理平衡。
表单分拆
通常情况下，随着时间的推移及业务量的增大，数据库中的数据会越来越多。而单张表的存储数量有限，当数据达到几百万甚至上千万条的时候，即使使用索引查询，效率也会非常低。此时可以考虑拆表技术，以缓解单表的访问压力，提高数据库的访问性能。
拆表分为水平拆分和垂直拆分。表的水平拆分是指，如果某个表的记录太多，如记录超过1000万条时，就要将该表中的全部记录分别存储到多个表中，并且要保证每个表的结构都是完全一致的。表的垂直拆分是指，如果一个表中的字段太多，则需要将这些字段拆开并分别存储到多个表中，并且在这些表中要通过一个字段进行连接，其他字段都各不相同。
1．水平拆分
表的水平拆分是为了解决单表数据量过大的问题。水平拆分一般是根据表中的某一字段取值进行划分，将数据存储在多个独立的表中。根据系统处理的业务不同，常见的水平拆分方式如下：
- 按照表中某一字段值的范围进行划分，如按照时间、地域、类型、等级或者某列的取值范围等，把数据拆分后放到不同的表中。这种方式的缺陷是不同表中的数据量可能不均衡。
- 对id进行Hash取模运算，如要拆分成3个表，则用mod(id,3)获取0、1、2这3个值，每一行针对获取的不同值，将其放到不同的表中。
如果user表中的记录数超过了一定的量级，则需要把该表中的记录拆分到多个表中分别进行存储。这里采用对id进行取模3运算，每一条记录根据mod(id,3)的值是0、1还是2，分别存储到对应的表中。水平拆分效果如图3所示。
图3 水平拆分
不管用什么样的方式进行水平拆分，访问数据时要按照同样的规则去访问不同的数据表。水平拆分解决了单表数据量过大的问题，提高了系统的负载能力，可以有效降低查询数据时要读取的数据和索引的页数，改善查询性能。但是，由于数据是分散存储，也加大了数据的维护难度。
2．垂直拆分
表的垂直拆分是为了解决单表字段过多的问题。垂直拆分时可以考虑如下原则：
- 经常一起使用的字段放在一个表中。
- 不常用的字段单独放在一个表中。
- 大字段单独放在一个表中。
垂直拆分时要注意，主键列要在每一个表中都冗余出现，以作为这些表的连接条件。拆分后数据行的内容会变少，提高了查询数据的执行效率，业务逻辑也更加清晰，但缺点是要管理冗余列，当需要查询所有数据时需要进行join连接。
如果user表中的字段过多，则需要把该表中的常用字段和不常用字段垂直拆成两个表来分别存储数据。这里把用户名、密码、手机、email这几个常用字段单独放到一个表中，其他字段如是否超级用户、是否激活、注册时间、最后修改时间、最后登录时间等字段放到另一个表中。另外，为了关联两个表中的记录，把主键id分别冗余存储在这两个表中。垂直拆分效果如图4所示。
图4 垂直拆分效果
说明：本文节选自北京理工大学出版社新出版的《MySQL从入门到部署实战（视频教学版）》。