MySQL 常见的数据表设计误区汇总

吾爱主题阅读：158 2024-04-02 08:03:05 评论：0

误区一：过多的数据列

MySQL 存储引擎的 API 是按照行缓冲区方式从服务端和存储引擎复制数据。服务端将缓冲区数据解码成数据列。然而，将行缓冲区的格式转换为数据行数据结构的列可能会代价很高。MyISAM 固定使用与服务端匹配的行格式，因此无需转换。然而，MyISAM 的可变行格式以及 InnoDB 的行格式总是需要进行转换。转换的代价依赖于列的数量。如果当数据表的列超过上百列的时候，会引起很高的 CPU 资源消耗——即便是使用到的列很少。曾经看过一篇文章，指的是一个多语言的解决方案，直接简单粗暴地将系统支持的语言用对应的列表示，例如：

1 2 3 4 5 6 7 8 9 10 CREATE TABLE t_multi_language_news ( id INT PRIMARY KEY , title_cn VARCHAR (32), title_en VARCHAR (32), title_it VARCHAR (32), ... content_cn VARVHAR(256), content_en VARCHAR (256), conntent_it VARCHAR (256), );

这种方式随着系统支持的语言越多，数据表的列越多，最终导致性能严重下降。如果你设计一个数据表的列数量超过100时，就需要考虑你的设计是否合理了。 **应对方式：**首先是考虑业务本身的设计是否合理，如果确实一个实体需要很多字段来描述，那么可以拆分数据表，通过扩展信息表来做。举个例子，对于资讯类的数据表，因为内容一般占据的空间会比较大，但是在列表不会直接查看，就可以拆成资讯主表和资讯详情表，主表存储标题、时间、摘要、缩略图附件 id 等列表要查看的信息即可。而资讯详情可以存储资讯的内容、来源、原文链接等信息。

误区二：过多的联合查询

MySQL 一次联合查询最多只能61张表。而有些设计主张不做冗余字段设计，这会导致复杂业务时需要连接多张表查询。即便是联合的表数量低于61个，也会引起性能的下降，而且整个 SQL 语句的维护将变得十分困难。作为一个设计的首要原则，就是如果想追求速度的话，一次查询不要跨太多的数据表做联合查询，尤其面临高并发场景的时候。 **应对方式：**首先，对于确定不会改变的字段，可以考虑冗余字段的方式减少联合查询。例如，一家企业的所属省份信息，就可以把省份代码、省份名称冗余了，而无需再通过省份代码去查询省份名称。其次，确实需要查其他表的情况下，可以考虑使用分步查询的方法，通过应用程序完成数据的组装，这种效率在数据表很多的时候会更高效，而且代码也更好维护。误区三：万能的枚举例如下面这种表设计：

1 2 3 4 5 CREATE TABLE t_countries ( ... country ENUM( '' , '1' , '2' , ..., '45' ), ... );

这种方式本来可以通过一个以整数为 key的字典的查找表实现。如果是业务上增加了一个枚举，意味着整个表都需要使用 ALTER TABLE更新。而如果是使用应用代码的查找表，只需要增加新的键值对就好了。 **应对方式：**如果枚举确定不会变动（例如性别），那么没问题。如果枚举可能会增加，那么尽可能地通过应用程序来实现。

误区三：滥用 SET替代 ENUM

枚举ENUM 类型是数据表列的值只能是值集合中的一个，而 SET 类型是该列可以有一个或多个值。如果确定一个列只会有一个值，那么就应该优先使用枚举，而不是集合。例如下面的例子就是典型的滥用：

1 2 3 4 5 CREATE TABLE t_payment_way ( ... is_default SET ( 'Y' , 'N' ) NOT NULL DEFAULT 'N' , ... );

很显然，is_default 要么是 Y，要么是 N，因此这里应该使用 ENUM。 **应对方式：**从业务层面考虑列的值是不是可能有多个，如果只有1个可选值那么就用枚举ENUM。

误区四：生硬地避免NULL

很多文章都讨论过尽可能地避免使用 NULL，对于大部分场景这是一个好的设计，我们可以通过0，空字符串，约定的值等来表示空值。然而，不要因为这个而生硬套用，如果是这个值本身就是一个无意义的值的时候，那么使用 NULL 可能更合适。例如，如果要是有-1代表一个无意义的整数，可能会导致代码很复杂，甚至可能引起 bug。例如下面的例子：

1 2 3 4 CREATE TABLE t_person ( birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' , ..., );

将一个 DATETIME 类型的默认值设置为全部是0会很奇怪，假设我们要统计人员的年龄平均值的时候，会引起莫名其妙的问题，而这种场景使用 NULL 就不会纳入到统计中来。可以通过设置 MySQL 的 SQL_MODE 参数禁止使用无意义的日期，避免出现这种情况。 **应对方式：**设计表的时候可以尽量使用 NOT NULL 避免空值，但是不要过于生硬，对于有些字段使用默认值无法表名意义或与实际不符时，也是可以选择使用 NULL 列的。只是，需要注意索引列不要使用NULL。而实际上，绝大部分索引列不太可能会是 NULL。

误区五：使用整数替换时间戳

之前有讲到过时间格式如何选择的问题，实际上有些开发者会使用整数来存储时间戳，他们的理由是这样效率更高。从某种意义上来说，可能会提高一点效率，但是帮助不大，因为在 MySQL 内部DATETIME 和 TIMESTAMP 本身就是用整数存储的。而如果使用整数存储时间的话，意味着应用程序中需要做时间转换，或者是 SQL 语句要对指定的字段进行时间转换，带来的收益可能得不偿失。 **应对方式：**尽可能地使用 DATETIME 存储时间，如果需要存储秒级精度一下的时间，那么可以考虑使用 BIGINT 来存储。

误区六：忘记字段的最大存储范围

在实际中设计表的时候会忘记数据类型的存储范围，比如使用 TINYINT(2)并不是只能存储两位整数，实际TINYINT(2) 可以存储的范围是-128-127。存储超过255的整数。这种错误在使用整数类型的时候很容易出现问题，在插入整数的时候，MySQL 不会检查实际的整数位数，而是按对应存储字节数的范围存入，这种情况假设不注意会存入无意义的值。例如下面的 INSERT 操作会成功，而我们可能误以为 TINYINT(2)只能存储2位整数：

1 2 3 4 5 6 CREATE TABLE t_int_test ( id INT PRIMARY KEY , number TINYINT(2) ); INSERT INTO t_int_test (id, number) VALUES (3,123);