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

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《MySQL 常见的数据表设计误区汇总》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

误区一：过多的数据列

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

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

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

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

误区三：滥用 SET替代 ENUM

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

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。例如下面的例子：

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位整数：

CREATE TABLE t_int_test (
    id INT PRIMARY KEY,
    number TINYINT(2)
);

INSERT INTO t_int_test (id, number) VALUES (3,123);

应对方式：在应用程序中做数据校验。

结语：

在实际设计数据表的过程中，除了需要考虑每个字段的数据类型之外，还需要考虑存储空间大小。对于常用的一些字段，如时间、标题、备注等，最好是内部形成一定的规范，大家遵照规范执行，并且增加校验能够避免很多问题。

好了，本文到此结束，带大家了解了《MySQL 常见的数据表设计误区汇总》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！