数据库设计全过程# MySQL表设计经验汇总篇

表设计是每一个后端程序员都无法避开的一块砖，而且这块砖一不小心就很容易烫手，本篇笔记就是为了帮助大家在设计表时能够轻松拿捏。

1.命名规范

数据库表名、字段名、索引名等都需要命名规范。命名可读性要高，尽量使用英文，采用驼峰或者下划线分割的方式，让人见名知意。

反例：这些命名过于简单，缺乏描述性，很难让人理解它的含义。

1表名：a、b、c 2字段名：aaa、bbb、ccc 3索引名：index1、index2、index3

正例：这些命名就让人见名知意。

1表名：customers、orders、products 2字段名：customer_id、order_date、product_name 3索引名：idx_customer_id、idx_order_date

TIP

●表名、字段名必须使用小写字母，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。

●主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。

2.选择合适的字段类型

设计表时，需要选择合适字段类型：

MySQL 8.0 字段类型速查表（常用）

类型分类	类型名称	占用空间	说明
整数类型	TINYINT	1 字节	-128 ~ 127（有符号） / 0 ~ 255（无符号）
	SMALLINT	2 字节	-32,768 ~ 32,767
	MEDIUMINT	3 字节	-8,388,608 ~ 8,388,607
	INT / INTEGER	4 字节	-2,147,483,648 ~ 2,147,483,647
	BIGINT	8 字节	±9.2×10¹⁸（64位）
小数类型	DECIMAL(M,D)	依赖精度	精确小数，适合用于金钱等场景（不受浮点误差）
	FLOAT	4 字节	单精度浮点（约 7 位精度）
	DOUBLE	8 字节	双精度浮点（约 16 位精度）
字符串类型	CHAR(n)	n 字节	定长字符串，最多 255 个字符（不建议用于变长数据）
	VARCHAR(n)	n+1 ~ n+2 字节	变长字符串，最大 65535 字节（受字符集和行宽限制）
	TEXT	最大 64KB	长文本，不适合索引、排序
	TINYTEXT	最大 255B	非常短文本
	MEDIUMTEXT	最大 16MB	中等长度文本
	LONGTEXT	最大 4GB	超长文本
日期时间类型	DATE	3 字节	'1000-01-01' 到 '9999-12-31'
	DATETIME	8 字节	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'
	TIMESTAMP	4 字节	从 1970 到 2038（受限于 UNIX 时间戳）
	TIME	3 字节	'−838:59:59' 到 '838:59:59'
	YEAR	1 字节	表示年份，如 2024
JSON 类型	JSON	动态	存储结构化 JSON 数据，支持索引部分字段
布尔类型	BOOLEAN / BOOL	1 字节	实际为 TINYINT(1)，0 为假，非 0 为真
二进制类型	BINARY(n)	定长	固定长度的二进制数据
	VARBINARY(n)	变长	可变长度的二进制数据
	BLOB 系列	TINYBLOB ~ LONGBLOB	与 TEXT 系列对应，用于存储二进制大对象

📝 注意事项（MySQL 8.0 以后）：

✅ 整数类型中的 INT(11) 等「显示宽度」写法已被弃用
✅ 推荐使用 UNSIGNED（无符号）提高正整数主键范围
✅ JSON 类型可以用作结构化字段，但不要滥用（查询复杂度高）
✅ TEXT/BLOB 类型不支持默认值，且无法参与 FULLTEXT 索引（除特殊配置）

3. 主键设计要合理

主键的设计在数据库中非常重要，它用于唯一标识表中的每一行数据，并且在数据操作和查询中起到关键作用。通常主键的设计，不要与业务相关联，因为业务是会发生变化的，应当使用自增的 id，并且保持主键的连续性。比如说可以使用优化的雪花 id 等等。

4.选择合适的字段长度

首先问大家一个问题，数据库字段长度表示字符长度还是字节长度？

在mysql中，varchar和char类型表示字符长度，而其他类型表示的长度都表示字节长度。

char(10)表示字符长度是10。

注意!!

从 MySQL 8.0 开始，所有整型类型的显示宽度语法（如 INT(11), BIGINT(4)）都被废弃了

以后写表结构时，直接写：BIGINT 或 BIGINT UNSIGNED 即可

不要使用 (4)、(11) 等旧的显示宽度写法（容易误解 + 已废弃）

所以在设计表时需要充分考虑一个字段的长度，比如一个用户名字段（它的长度5~20个字符），你觉得应该设置多长呢？可以考虑设置为 varchar（32）。需要注意字段长度一般设置为2的n次方。

5.优先考虑逻辑删除，而不是物理删除

什么是物理删除？什么是逻辑删除？

●物理删除：把数据从硬盘中删除，可释放存储空间

●逻辑删除：给数据添加一个字段，比如is_deleted，以标记该数据已经逻辑删除。

为什么推荐用逻辑删除，不推荐物理删除呢？

●数据恢复困难。

●物理删除会导致索引树重构

6.每个表都需要添加通用字段

●id：主键，一个表必须得有主键，必须

●create_time：创建时间

●creator ：创建人

●update_time: 修改时间，必须，更新记录时，需要更新它

●update_by :修改人，非必须

●remark ：数据记录备注，非必须

7.一张表的字段不宜过多

建表的时候一张表的字段不要太多了。尽量不超过 20 个。超出的话优先考虑拆分，也就是通常的查询表，详情表。

●查询效率：当表中保存的数据数量很大时，查询操作需要检索的数据也会随之增加。如果表的字段数过多，查询操作就需要读取更多的数据，这会导致查询效率变慢。

●存储空间：表的字段数越多，每一行数据占用的存储空间也就越大。对于大型数据库来说，这可能会导致磁盘空间的浪费。

●数据库设计复杂性：当表的字段数过多时，数据库的设计和维护变得更加复杂。这可能涉及到索引和关联表的设计，以确保数据的完整性和一致性。

8.定义字段尽可能not null

如果没有特殊的理由，一般都建议将字段定义为 NOT NULL 。为什么呢？

●首先，NOT NULL 可以防止出现空指针问题。

●其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。

●NULL值有可能会导致索引失效

如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑，那就可以将这个字段设置为NOT NULL。

9.合理添加索引

当设计表时，需要考虑哪些字段需要加索引，可以遵循以下几个原则：

●根据查询条件进行选择（高频使用）：如果在查询中使用了某个字段作为查询条件，那么这个字段就应该建立索引。例如，在用户表中，如果需要根据用户的姓名进行查询，那么就应该为姓名字段建立索引。

●区分度高的字段优先：如果一个字段的取值范围非常小，例如性别只有男女两种可能，那么这个字段就不适合建立索引。相反，如果一个字段的取值范围很大且区分度高，例如用户ID，那么这个字段就非常适合建立索引。

●不要建立过多的索引：每个表所建立的索引数量应该控制在一个合理的范围内，一般不要超过5个。因为过多的索引会导致写入速度变慢，并占用更多的存储空间。

●联合索引优化：在某些情况下，可以通过联合索引的方式来优化查询速度，减少所需的索引数量。例如，在用户表中，如果需要根据用户姓名和年龄进行查询，那么可以将这两个字段组合成联合索引。

假设你有一个订单表，包含订单ID、用户ID、订单金额、订单状态等字段。现在需要根据用户ID和订单状态进行查询，可以考虑为用户ID和订单状态这两个字段建立联合索引，例如：

sql

CREATE TABLE order_tab (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    amount decimal(10,2) NOT NULL,
    status varchar(20) NOT NULL,
    create_time datetime NOT NULL,
    PRIMARY KEY (id),
    KEY idx_user_status (user_id, status) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10.不需要严格遵守 3NF，通过业务字段冗余来减少表关联

简单来说就是反范式设计。常见形式是在第三范式(3NF)的基础上，进一步进行冗余，从而减少表关联。

回顾下什么是数据库三范式（3NF）？

●第一范式：对属性的原子性，要求属性具有原子性，不可再分解；

●第二范式：对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖；

●第三方式：对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

假设需要设计一个产品订单表，包含以下字段：订单ID、用户ID、订单日期、产品名称、产品价格、产品数量以及订单总价。正常情况下，可能会分别设计订单表和产品表，并使用外键进行关联，例如：

sql

CREATE TABLE order (
    `id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_date` date NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE product (
    `id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个设计方式符合范式要求，但在查询时需要进行表关联操作，可能会降低查询效率。为了提高查询效率，我们可以使用反范式的设计方式，将订单表中的产品名称、产品价格和订单总价冗余存储到订单表中，从而避免关联查询。例如：

sql

CREATE TABLE order (
    `id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_date` date NOT NULL,
`product_name` varchar(255) NOT NULL,
`product_price` decimal(10,2) NOT NULL,
`quantity` int(11) NOT NULL,
`total_price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过这种反范式的设计方式，我们可以避免表关联操作，提高查询效率。但同时也带来了一些缺点，例如数据冗余、数据更新困难等。因此，在实际应用中需要根据具体情况进行选择。

避免使用MySQL保留字

如果库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号来引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

如果你需要使用这些保留字作为表名、列名或其他标识符，你可以考虑以下方法来避免冲突：

1在标识符前或后添加下划线：例如，将表名命名为 "my_table"，列名命名为 "column_name"。

2使用不同的单词或短语：例如，将表名命名为 "orders_table"，列名命名为 "order_status"。

3使用反引号（）将标识符括起来：例如，将表名命名为 "table"，列名命名为 "column`"。请注意，在使用反引号时要小心，确保使用正确的语法和规范

12.不搞外键关联，一般都在代码维护

在数据库设计中，使用外键关联是一种良好的实践，可以确保数据的完整性和一致性。外键关联可以帮助维护表之间的关系，防止无效或不一致的数据插入、更新或删除操作。然而，在某些情况下，也存在一些缺点，这可能是导致现在不太推荐使用外键关联的原因之一。以下是一些这种情况：

1可能会导致性能问题，尤其是在对大型数据集进行操作时。这是因为每次插入、更新或删除操作都需要进行约束检查，这可能会导致额外的开销和延迟。

2可能会限制数据库的灵活性和可扩展性。例如，如果需要对数据库进行分区或垂直分割，外键关联可能会导致额外的复杂性和限制。

3可能会导致死锁和死循环，特别是在进行并发操作时。这可能会导致数据库出现不稳定的状态，从而影响系统的性能和可用性。

4可能会导致数据库的维护和管理成本的增加。这是因为外键关联需要额外的管理和维护工作，例如添加、修改或删除外键约束时需要额外的测试和验证。

因此，在决定是否使用外键关联时，需要考虑实际业务需求和场景，并进行权衡和决策。在某些情况下，可以采用其他方法来保证数据的完整性和一致性，例如使用应用程序逻辑或数据库触发器来实现约束检查和数据操作。同时，需要注意数据库设计的基本原则和最佳实践，例如避免数据冗余、遵循规范化原则和正常化理论等。

13.字段注释

设计表时每个字段的含义要注释清楚，包括枚举类型。比如说：

14.时间的类型选择

时间类型的选择一般都要好好考虑，因为不同的类型存储的格式不同。

对于MySQL来说，主要有date、datetime、time、timestamp 和 year。

●date ：表示的日期值, 格式yyyy-mm-dd,范围1000-01-01 到 9999-12-31，3字节

●time ：表示的时间值，格式 hh:mm:ss，范围-838:59:59 到 838:59:59，3字节

●datetime：表示的日期时间值，格式yyyy-mm-dd hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59```,8字节，跟时区无关

●timestamp：表示的时间戳值，格式为yyyymmddhhmmss，范围1970-01-01 00:00:01到2038-01-19 03:14:07，4字节，跟时区有关

●year：年份值，格式为yyyy。范围1901到2155，1字节

推荐优先使用datetime类型来保存日期和时间，因为存储范围更大，且跟时区无关。

15.SQL 编写的一些优化经验

1避免使用SELECT * FROM 语句，应该只选择需要的列，以减少网络传输和提高查询性能。

2使用索引来提高查询速度，特别是在对大型表进行查询时。

3避免使用外键约束，因为它们可能会导致性能问题，特别是在对大型表进行插入、更新和删除操作时。

4使用LIMIT 1来限制查询结果只有一条记录。

5避免在where子句中使用OR来连接条件，应使用UNION来连接查询。

6注意优化LIMIT深分页问题，可以使用OFFSET来替代LIMIT。

7使用where条件限制要查询的数据，避免返回多余的行。

8尽量避免在索引列上使用MySQL的内置函数，这可能导致索引失效。

9应尽量避免在where子句中对字段进行表达式操作，这可能导致索引失效。

10应尽量避免在where子句中使用!=或<>操作符，这可能导致索引失效。

11使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

12对查询进行优化，应考虑在where及order by涉及的列上建立索引。

13如果插入数据过多，考虑批量插入。

14在适当的时候，使用覆盖索引。

15使用EXPLAIN 分析你SQL的计划。

数据库设计全过程# MySQL表设计经验汇总篇 ​

1.命名规范 ​

2.选择合适的字段类型 ​

📝 注意事项（MySQL 8.0 以后）： ​

3. 主键设计要合理 ​

4.选择合适的字段长度 ​

5.优先考虑逻辑删除，而不是物理删除 ​

6.每个表都需要添加通用字段 ​

7.一张表的字段不宜过多 ​

8.定义字段尽可能not null ​

9.合理添加索引 ​

10.不需要严格遵守 3NF，通过业务字段冗余来减少表关联 ​

避免使用MySQL保留字 ​

12.不搞外键关联，一般都在代码维护 ​

13.字段注释 ​

14.时间的类型选择 ​

15.SQL 编写的一些优化经验 ​