数据库优化

例 1：创建表

CREATE TABLE company (
	company_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	company_name char(50) NOT NULL,
	review_status tinyint NOT NULL COMMENT '状态，1-正常，2-停用',
	create_time timestamp NOT NULL,
	update_time timestamp NOT NULL,
	PRIMARY KEY (company_id)
) ENGINE = InnoDB CHARSET = utf8 COMMENT '公司基本信息';

优化过程

这个 CREATE TABLE 的 SQL 语句总体看起来是能够正常执行的，但它存在一些潜在的问题和需要优化的地方，另外可能会报错 ERROR 1067 (42000): Invalid default value for 'update_time'，尤其是从可读性、性能优化以及业务需求的可扩展性方面考虑。以下是具体的分析和改进建议：

1. char(50) 的使用问题

问题：

  • company_name 字段使用了 char(50)，这是一种固定长度的字符串类型。

  • 如果公司名称的长度不固定（比如有的公司名只有几个字符），使用 char 会浪费存储空间，因为它会为所有记录分配固定的 50 字节。

改进建议：

  • 将 char(50) 改为 varchar(50)，varchar 是变长字符串类型，能够根据实际存储的数据长度分配空间，更加节省存储资源。

改进后：

company_name varchar(50) not null

2. review_status 字段设计问题

问题：

  • review_status 使用 tinyint 来存储状态，这种设计虽然可行，但建议在业务中明确状态含义时使用**枚举（ENUM）**类型，或者创建一个映射表来存储状态信息。

  • 如果以后需要增加更多状态（比如 3-审核中，4-已删除），直接修改代码中硬编码的状态值会导致维护成本增加。

改进建议：

  1. 方案一：使用 ENUM

    review_status ENUM('正常', '停用') NOT NULL COMMENT '状态，正常、停用'

  2. 方案二：建立状态映射表 创建一个独立的 review_status 表，存储状态值和描述。这样可以更方便地扩展和维护。

    CREATE TABLE review_status (
    status_id TINYINT NOT NULL PRIMARY KEY,
    status_name VARCHAR(20) NOT NULL COMMENT '状态描述'
);

    主表 company 的 review_status 字段使用外键关联（外键不推荐使用）：

    review_status TINYINT NOT NULL COMMENT '状态，关联 review_status 表'

3. 时间字段的默认值问题

问题：

  • create_time 和 update_time 是 timestamp 类型，但没有设置默认值，可能导致插入数据时需要手动提供这些字段的值。

  • 一般情况下，create_time 应该默认当前时间，update_time 应该自动更新为当前时间（如果记录被修改）。

改进建议：

  • 为 create_time 和 update_time 字段添加默认值和自动更新功能。

改进后：

create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

4. 表注释和字段注释的规范化

问题：

  • 表的注释 公司基本信息 和字段的注释 状态，1-正常，2-停用 比较简单，建议更加详细地描述用途和含义，增强可读性。

改进建议：

  • 优化为更全面的描述，例如：

    • 表注释：存储公司基本信息，包括公司名称、审核状态、创建时间和更新时间。

    • 字段注释：状态，1-正常，2-停用（表示公司当前运营状态）。

5. 字符集问题

问题：

  • 表的字符集设置为 utf8，而 MySQL 的 utf8 实际上只支持最多 3 个字节的字符，无法存储某些 4 字节的 Unicode 字符（如 emoji 表情）。

  • 如果可能需要存储特殊字符（如公司名中包含表情符号），建议使用 utf8mb4 字符集。

改进建议：

  • 将表的字符集设置为 utf8mb4，以支持更多字符集。

改进后：

engine = InnoDB default charset = utf8mb4 comment = '存储公司基本信息，包括公司名称、审核状态、创建时间和更新时间。'

6. 缺少唯一约束或索引

问题：

  • 在实际业务中，company_name 很可能需要唯一性约束，以保证公司名称不重复。

  • 如果将来需要频繁查询 review_status，建议为该字段添加索引以提高查询性能。

改进建议：

  1. 为 company_name 添加唯一约束：

    company_name VARCHAR(50) NOT NULL UNIQUE COMMENT '公司名称，唯一'

  2. 为 review_status 添加索引：

    CREATE INDEX idx_review_status ON company(review_status);

7. 缺少外键关联

问题：

  • 如果系统中有其他表需要和 company 表关联（比如员工表 employee 需要关联公司信息），建议为主键 company_id 添加外键约束。

  • 当前定义的 company_id 是主键，但没有说明是否会作为其他表的外键使用。

改进建议：

  • 为 company_id 明确外键用途，并在相关表中显式定义外键约束。

最终优化后的 SQL

结合上面的所有建议，优化后的 SQL 如下：

CREATE TABLE company (
    company_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公司 ID，主键',
    company_name VARCHAR(50) NOT NULL UNIQUE COMMENT '公司名称，唯一',
    review_status ENUM('正常', '停用') NOT NULL COMMENT '状态：正常、停用',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (company_id),
    INDEX idx_review_status (review_status)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COMMENT = '存储公司基本信息，包括公司名称、审核状态、创建时间和更新时间。';

总结

优化后的 SQL 主要解决了以下问题：

  1. 替换 char(50) 为更灵活的 varchar(50)。

  2. 改进 review_status 的表示方式，使用 ENUM 更清晰，或者结合状态映射表增强扩展性。

  3. 为时间字段添加默认值和自动更新功能。

  4. 使用 utf8mb4 字符集以支持更多字符。

  5. 为常用字段添加索引和唯一约束，提升查询性能和数据完整性。

通过这些优化，表的设计更加规范、灵活，并且能够很好地适应未来的需求。如果还有其他问题，欢迎继续提问！ 😊

PreviousMysql 必知必会Nextgit

Last updated

Was this helpful?