数据库设计规范手册-含 ER 图 - 索引 - 分库分表方案

数据库设计规范手册

含ER图·索引·分库分表方案

从建模到分库分表的全链路设计标准

10大章节·80+设计规范·100+实战示例

数据库实战系列

目录

第一章数据库设计概述与规范体系

第二章命名规范与数据类型选择

第三章表结构设计规范

第四章字段设计规范

第五章ER图设计规范与实战

第六章索引设计规范与优化

第七章分库分表方案设计

第八章数据库性能优化实战

第九章数据安全与合规设计

第十章实战案例与速查表

数据库设计规范手册·含ER图/索引/分库分表方案

第一章数据库设计概述与规范体系

1.1数据库设计的核心价值

数据库设计是整个系统的基础。一个设计良好的数据库能支撑业务长期发展，一个设计糟糕的数据库则会在业

务增长时成为最大的瓶颈。数据库设计的质量直接影响系统的性能、可维护性、可扩展性和数据一致性。

数据库设计不当导致的典型问题包括：表结构频繁变更、查询性能随数据量增长急剧下降、数据冗余和不一

致、难以支撑业务扩展、维护成本高昂。这些问题在业务初期往往不明显，但一旦数据量增长到百万、千万级别，

就会集中爆发。

1.2数据库设计的三大范式

范式要求解决的问题

第一范式（1NF）字段不可再分消除重复列

第二范式（2NF）非主键字段完全依赖主键消除部分依赖

第三范式（3NF）非主键字段不依赖其他非主键字段消除传递依赖

范式理论是数据库设计的理论基础，但实际工程中，为了性能考虑，经常需要反范式设计。反范式就是故意引

入冗余，用空间换时间，减少关联查询。例如订单表中冗余商品名称，避免查询订单时需要关联商品表。

1.3数据库设计的核心原则

原则说明实践要点

满足业务需求设计服务于业务，不是理论模型理解业务场景和查询模式

适度冗余为性能考虑可以反范式冗余字段要可控、可维护

预留扩展为未来变化预留空间扩展字段、预留字段

统一规范全库命名、类型、索引风格统一建立团队设计规范

性能优先关键路径考虑查询性能索引设计、分库分表

安全合规敏感数据加密、审计数据脱敏、访问控制

可维护性设计清晰、文档完整ER图、数据字典

1.4数据库设计流程

数据库设计标准流程：

第1步：需求分析

-梳理业务实体和关系

-明确数据规模和增长预期

-收集查询模式和访问频次

第2步：概念设计

-绘制ER图（实体-关系图）

-确定实体、属性、关系

-不涉及具体数据库实现

第3步：逻辑设计

-转换为关系模型

-确定表、字段、主键、外键

-应用范式理论进行优化

第4步：物理设计

-选择数据类型

-设计索引

-考虑存储引擎、字符集

第5步：性能设计

-评估数据量增长

-设计分库分表方案

-设计缓存策略

第6步：安全设计

-敏感数据识别

-加密方案

-权限控制

第7步：评审与实施

-设计评审

-建表脚本

-数据初始化

第8步：持续优化

-监控慢查询

-索引优化

-结构演进

数据库设计的时机：数据库设计不是一次性的工作。业务初期可以简单设计，快速验证；业务稳定后需要根

据实际查询模式优化；业务增长到一定规模需要重新评估分库分表。好的设计是演进而来的，不是一次设计完

美的。

第二章命名规范与数据类型选择

2.1命名规范总则

统一的命名规范是团队协作的基础。好的命名让代码自解释，减少沟通成本，避免歧义。

对象命名规则示例

数据库名小写字母+下划线，体现业务order_db、user_center

表名小写字母+下划线，单数形式order、user_profile

字段名小写字母+下划线，见名知意user_id、created_at

主键统一命名ididBIGINT

外键引用表名+_iduser_id、order_id

索引名idx_字段名或idx_字段1_字段2idx_user_id

唯一索引uk_字段名uk_order_no

主键索引pk_表名（可选）pk_order

2.2命名规范细则

禁止使用保留字。如order、group、user、desc、key等。如果必须使用，用反引号包裹，但最好

换一个名字，如order_info、user_info。

禁止使用拼音。拼音命名不易理解，也容易产生歧义。统一用英文单词，如果英文不好可以查词典。

布尔字段用is_前缀。如is_deleted、is_active、is_vip。这样从名字上就能看出是布尔类型。

时间字段用_at或_time后缀。如created_at、updated_at、paid_at。统一风格便于识别。

计数字段用_count后缀。如view_count、order_count。

金额字段用_amount或_price后缀。如total_amount、unit_price。

2.3数据类型选择

场景推荐类型不推荐说明

主键IDBIGINTUNSIGNEDINT预留增长空间

状态字段TINYINTVARCHAR节省空间，查询快

金额DECIMAL(18,2)FLOAT/DOUBLE避免浮点精度问题

时间DATETIME或TIMESTAMPVARCHAR支持时间函数

手机号VARCHAR(20)BIGINT避免前导零丢失

布尔值TINYINT(1)CHAR(1)节省空间

枚举TINYINT/SMALLINTENUM便于扩展

文本VARCHAR/TEXTCHAR变长更省空间

IP地址INTUNSIGNEDVARCHAR节省空间，便于范围查询

JSON数据JSON（MySQL5.7+）TEXT支持JSON函数查询

2.4数据类型选择详解

--主键设计

--推荐：BIGINTUNSIGNED，无符号，预留增长空间

idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'主键ID',

--不推荐：INT，21亿就溢出了

--idINTNOTNULLAUTO_INCREMENT,

--状态字段

--推荐：TINYINT，节省空间

statusTINYINTNOTNULLDEFAULT0COMMENT'状态:0=待支付1=已支付2=已发货3=已完成4=已取

消',

--金额字段

--推荐：DECIMAL(18,2)，精确小数

amountDECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'订单金额',

--时间字段

--DATETIME：范围1000-9999年

--TIMESTAMP：范围1970-2038年，会受时区影响

created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

updated_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

ONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',

--字符串字段

--VARCHAR变长，只占实际长度

--CHAR定长，固定占空间，适合长度固定的场景

usernameVARCHAR(64)NOTNULLCOMMENT'用户名',

phoneVARCHAR(20)NOTNULLCOMMENT'手机号',

id_cardCHAR(18)NOTNULLCOMMENT'身份证号',

--布尔字段

--TINYINT(1)是MySQL的布尔实现

is_deletedTINYINT(1)NOTNULLDEFAULT0COMMENT'是否删除:0=否1=是',

--IP地址

--INET_ATON('')→3232235777

--INET_NTOA(3232235777)→''

ip_addrINTUNSIGNEDNOTNULLDEFAULT0COMMENT'IP地址',

--JSON字段

extraJSONCOMMENT'扩展信息',

数据类型选择的常见陷阱：第一，用INT做主键，21亿数据就溢出；第二，用FLOAT/DOUBLE存金额，会

出现精度问题（0.1+0.2≠0.3）；第三，用VARCHAR存时间，无法使用时间函数；第四，用ENUM存状态，

新增状态需要改表结构；第五，用BIGINT存手机号，会丢失前导零。

2.5字符集与排序规则

--字符集选择

--推荐：utf8mb4，支持完整的Unicode（包括emoji）

--不推荐：utf8（MySQL中的utf8只支持3字节，不支持emoji）

--库级别设置

CREATEDATABASEorder_db

DEFAULTCHARACTERSETutf8mb4

DEFAULTCOLLATEutf8mb4_general_ci;

--表级别设置

CREATETABLE`order`(

...

)ENGINE=InnoDB

DEFAULTCHARSET=utf8mb4

COLLATE=utf8mb4_general_ci;

--排序规则：

--utf8mb4_general_ci：不区分大小写，性能稍好

--utf8mb4_unicode_ci：不区分大小写，支持更多语言

--utf8mb4_bin：区分大小写，按二进制比较

--utf8mb4_0900_ai_ci：MySQL8.0默认，基于Unicode9.0

第三章表结构设计规范

3.1表设计核心原则

表是数据库的基本单元，表结构设计直接影响查询性能和可维护性。好的表设计应该满足：结构清晰、职责单

一、字段精简、便于扩展。

原则说明反例

单一职责一张表只存一类数据用户表和订单表混在一起

字段精简只保留必要字段一张表几十个字段

主键必备每张表都要有主键无主键表难以维护

字段有默认值避免NULL，设置默认值NULL会带来查询陷阱

字段注释完整每个字段都要有注释后续维护看不懂字段含义

审计字段完整创建时间、更新时间、创建人无法追溯数据来源

适度反范式关键字段冗余提升性能过多冗余导致一致性难保证

3.2表设计标准模板

--标准的业务表模板

CREATETABLE`order_info`(

--==========主键==========

`id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'主键ID',

--==========业务字段==========

`order_no`VARCHAR(32)NOTNULLCOMMENT'订单编号',

`user_id`BIGINTUNSIGNEDNOTNULLCOMMENT'用户ID',

`product_id`BIGINTUNSIGNEDNOTNULLCOMMENT'商品ID',

`product_name`VARCHAR(128)NOTNULLCOMMENT'商品名称（冗余）',

`quantity`INTUNSIGNEDNOTNULLDEFAULT1COMMENT'购买数量',

`unit_price`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'单价',

`total_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'总金额',

`discount_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'优惠金额',

`pay_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'实付金额',

`status`TINYINTNOTNULLDEFAULT0COMMENT'状态:0=待支付1=已支付2=已发货3=已完成4=

已取消',

`remark`VARCHAR(255)NOTNULLDEFAULT''COMMENT'备注',

--==========扩展字段==========

`extra`JSONDEFAULTNULLCOMMENT'扩展信息',

--==========审计字段==========

`is_deleted`TINYINT(1)NOTNULLDEFAULT0COMMENT'是否删除:0=否1=是',

`created_by`BIGINTUNSIGNEDNOTNULLDEFAULT0COMMENT'创建人ID',

`updated_by`BIGINTUNSIGNEDNOTNULLDEFAULT0COMMENT'更新人ID',

`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

`updated_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

ONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',

--==========主键==========

PRIMARYKEY(`id`),

--==========唯一索引==========

UNIQUEKEY`uk_order_no`(`order_no`),

--==========普通索引==========

KEY`idx_user_id`(`user_id`),

KEY`idx_status_created`(`status`,`created_at`),

KEY`idx_created_at`(`created_at`)

)ENGINE=InnoDB

DEFAULTCHARSET=utf8mb4

COLLATE=utf8mb4_general_ci

COMMENT='订单信息表';

3.3审计字段规范

审计字段是记录数据生命周期的关键字段，任何业务表都应该包含。它们帮助追踪数据的来源、变更历史和使

用情况。

字段类型说明必需

created_atDATETIME创建时间是

updated_atDATETIME更新时间是

created_byBIGINT创建人ID推荐

updated_byBIGINT更新人ID推荐

is_deletedTINYINT逻辑删除标识推荐

versionINT乐观锁版本号可选

3.4逻辑删除vs物理删除

方式优点缺点适用场景

物理删除彻底、节省空间无法恢复、审计困难日志、临时数据

逻辑删除可恢复、可审计占用空间、查询需过滤业务数据

--逻辑删除的实现

--1.添加is_deleted字段

ALTERTABLE`order_info`

ADDCOLUMN`is_deleted`TINYINT(1)NOTNULLDEFAULT0

COMMENT'是否删除:0=否1=是';

--2.添加索引（用于过滤查询）

ALTERTABLE`order_info`

ADDINDEX`idx_is_deleted`(`is_deleted`);

--3.查询时过滤已删除数据

SELECT*FROM`order_info`

WHERE`is_deleted`=0;

--4.删除时改为更新

UPDATE`order_info`

SET`is_deleted`=1,`updated_at`=NOW()

WHERE`id`=123;

--5.联合唯一索引的处理

--如果order_no需要唯一，逻辑删除后可能重复

--方案：唯一索引包含is_deleted

UNIQUEKEY`uk_order_no_deleted`(`order_no`,`is_deleted`);

3.5大字段处理

大字段（TEXT、BLOB、JSON）会显著影响表性能和存储。合理处理大字段是数据库设计的重要环节。

方案说明适用场景

垂直拆分大字段单独放一张表大字段查询频率低

外部存储大字段存OSS，表里存URL图片、视频、文件

压缩存储应用层压缩后再存入文本、JSON

JSON拆分常用的字段独立成列JSON中部分字段常用

--大字段垂直拆分示例

--主表：只存常用字段

CREATETABLE`article`(

`id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,

`title`VARCHAR(255)NOTNULLCOMMENT'标题',

`author_id`BIGINTUNSIGNEDNOTNULLCOMMENT'作者ID',

`summary`VARCHAR(500)NOTNULLDEFAULT''COMMENT'摘要',

`status`TINYINTNOTNULLDEFAULT0COMMENT'状态',

`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,

`updated_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

ONUPDATECURRENT_TIMESTAMP,

PRIMARYKEY(`id`),

KEY`idx_author`(`author_id`),

KEY`idx_status_created`(`status`,`created_at`)

)ENGINE=InnoDBCOMMENT='文章主表';

--详情表：只存大字段

CREATETABLE`article_content`(

`id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,

`article_id`BIGINTUNSIGNEDNOTNULLCOMMENT'文章ID',

`content`LONGTEXTNOTNULLCOMMENT'正文内容',

`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,

PRIMARYKEY(`id`),

UNIQUEKEY`uk_article_id`(`article_id`)

)ENGINE=InnoDBCOMMENT='文章内容表';

3.6表设计检查清单

表名是否体现业务含义？是否符合命名规范？

是否有主键？主键是否使用BIGINT？

是否有审计字段（created_at、updated_at）？

是否设置了字符集为utf8mb4？

存储引擎是否为InnoDB？

字段是否都有注释？

字段是否都设置了NOTNULL和默认值？

是否有必要的大字段拆分？

是否使用了逻辑删除？

表大小是否在可控范围（<1000万行）？

是否需要分库分表？

第四章字段设计规范

4.1字段设计核心原则

原则说明示例

避免NULLNULL会带来查询陷阱使用NOTNULL+DEFAULT

类型精准选择最合适的类型金额用DECIMAL，不用FLOAT

长度合理不浪费也不过短手机号VARCHAR(20)足够

注释完整每个字段都有注释COMMENT'用户ID'

避免保留字不用数据库保留字用order_info替代order

统一风格同类字段命名统一时间用_at后缀

4.2NULL的陷阱与处理

NULL在SQL中是一个特殊的值，表示"未知"。它与任何值的比较都返回UNKNOWN，包括NULL=NULL。这

会导致很多陷阱。

--NULL的常见陷阱

--1.统计遗漏

SELECTCOUNT(*)FROMusersWHEREage>18;--不包含ageISNULL的行

SELECTCOUNT(*)FROMusersWHEREage<=18;--也不包含ageISNULL的行

--两数之和小于总行数

--2.索引失效

SELECT*FROMusersWHEREage=18;--NULL值不会被索引覆盖

SELECT*FROMusersWHEREage!=18;--NULL值也不会被包含

--3.聚合函数忽略NULL

SELECTAVG(age)FROMusers;--分母不含NULL行

SELECTSUM(age)FROMusers;--NULL行不计入

--4.字符串拼接失效

SELECTCONCAT('Name:',name)FROMusers;--name为NULL时结果为NULL

--5.算术运算失效

SELECTage+1FROMusers;--age为NULL时结果为NULL

--正确处理NULL

--1.尽量使用NOTNULL+DEFAULT

ageINTNOTNULLDEFAULT0COMMENT'年龄',

nameVARCHAR(64)NOTNULLDEFAULT''COMMENT'姓名',

--2.查询时显式处理NULL

SELECTCOUNT(*)FROMusersWHEREage>18ORageISNULL;

--3.使用COALESCE或IFNULL

SELECTCOALESCE(age,0)FROMusers;

SELECTIFNULL(age,0)FROMusers;

--4.索引设计考虑NULL

--如果age可能为NULL，使用ISNULL的查询效率低

--建议用NOTNULL+DEFAULT0替代

NULL的处理原则：除非业务上必须有"未设置"的语义，否则所有字段都应该是NOTNULL+DEFAULT。

DEFAULT值应该选择合适的"中性值"：数值用0，字符串用空串，时间用'1970-01-0100:00:00'或当前时间。如

果确实需要表达"未设置"，用一个特殊值如-1，并在注释中说明。

4.3枚举字段设计

--枚举字段设计的三种方案

--方案一：使用TINYINT（推荐）

`status`TINYINTNOTNULLDEFAULT0

COMMENT'状态:0=待支付1=已支付2=已发货3=已完成4=已取消',

--优点：节省空间、查询快、便于扩展

--缺点：可读性差、需查注释

--方案二：使用ENUM（不推荐）

`status`ENUM('pending','paid','shipped','completed','cancelled')

NOTNULLDEFAULT'pending',

--优点：可读性好

--缺点：新增值需要ALTERTABLE、不同数据库不兼容

--方案三：使用VARCHAR（不推荐）

`status`VARCHAR(20)NOTNULLDEFAULT'pending'

COMMENT'状态:pending/paid/shipped/completed/cancelled',

--优点：可读性好

--缺点：占用空间大、查询慢

--方案对比

|方案|空间|性能|可读性|可扩展性|

|------|------|------|--------|----------|

|TINYINT|1字节|高|差|好|

|ENUM|1-2字节|高|好|差|

|VARCHAR|变长|低|好|好|

--结论：生产环境推荐TINYINT，可读性通过注释和常量类保证

4.4金额字段设计

--金额字段的正确设计

--❌错误方案：使用FLOAT/DOUBLE

`amount`FLOATCOMMENT'订单金额',

--问题：浮点数精度问题

--0.1+0.2=0.30000000000000004

--❌错误方案：使用INT存储"分"

`amount`INTCOMMENT'订单金额（分）',

--问题：可读性差、易错、处理退款时容易出错

--✅推荐方案：使用DECIMAL

`amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'订单金额',

--说明：DECIMAL(18,2)表示总共18位，小数2位

--最大值：9999999999999999.99

--精度：精确到分

--金额字段的扩展设计

`total_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'商品总额',

`discount_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'优惠金额',

`pay_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'实付金额',

`currency`CHAR(3)NOTNULLDEFAULT'CNY'COMMENT'币种:CNY/USD/EUR',

--多币种场景

`amount_cny`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'人民币金额',

`exchange_rate`DECIMAL(10,6)NOTNULLDEFAULT1.000000COMMENT'汇率',

`original_amount`DECIMAL(18,2)NOTNULLDEFAULT0.00COMMENT'原币金额',

--金额计算示例

--元转分

SELECTROUND(amount*100)ASamount_centsFROMorders;

--分转元

SELECTamount_cents/100ASamountFROMorders;

--金额求和

SELECTSUM(pay_amount)AStotal_payFROMorders;

4.5时间字段设计

--时间字段的规范设计

--创建时间

`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

COMMENT'创建时间',

--更新时间

`updated_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

ONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',

--业务时间（如支付时间、发货时间）

`paid_at`DATETIMEDEFAULTNULLCOMMENT'支付时间',

`shipped_at`DATETIMEDEFAULTNULLCOMMENT'发货时间',

--DATETIMEvsTIMESTAMP

|特性|DATETIME|TIMESTAMP|

|------|----------|-----------|

|存储|8字节|4字节|

|范围|1000-9999年|1970-2038年|

|时区|不受影响|受时区影响|

|默认值|支持|支持|

|自动更新|5.6+支持|支持|

--推荐使用DATETIME

--理由：范围大、不受时区影响、行为一致

--时间存储注意事项

--1.统一使用UTC时间存储

--2.展示时根据用户时区转换

--3.避免用字符串存时间

--4.时间范围查询用BETWEEN或>=AND<

--错误示例

`created_at`VARCHAR(20)COMMENT'创建时间',

--问题：无法用时间函数、排序按字符串、占用空间大

--正确示例

`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

COMMENT'创建时间',

--时间索引

KEY`idx_created_at`(`created_at`),

--时间范围查询

SELECT*FROMorders

WHEREcreated_at>='2026-09-0100:00:00'

ANDcreated_at<'2026-10-0100:00:00';

4.6字符串字段长度设计

字段推荐长度说明

用户名VARCHAR(64)通常4-32字符

密码（哈希）CHAR(60)bcrypt输出固定60字符

手机号VARCHAR(20)考虑国际号码

邮箱VARCHAR(128)标准限制

身份证CHAR(18)固定18位

URLVARCHAR(512)长URL一般不超过500

标题VARCHAR(255)通用长度

描述VARCHAR(1024)较长文本

正文TEXT/LONGTEXT大文本，考虑拆分

UUIDCHAR(36)标准UUID格式

IP地址INTUNSIGNED用整数存储

VARCHAR长度设计原则：不要一上来就VARCHAR(255)，也不要精确到实际最大长度。VARCHAR长度影

响索引大小（索引会限制总长度），但不会影响存储（变长存储）。一般原则是：实际最大长度+30%余量。

如用户名通常4-32字符，设VARCHAR(64)足够。

第五章ER图设计规范与实战

5.1ER图的作用

ER图（Entity-RelationshipDiagram，实体-关系图）是数据库概念设计阶段的核心工具。它以图形化的方式表达

业务实体、属性和关系，帮助团队理解业务模型、发现设计问题、统一沟通语言。

一份好的ER图应该能够回答：业务中有哪些实体？每个实体有哪些属性？实体之间有什么关系？关系的基数

是什么？

5.2ER图的核心元素

元素符号说明示例

实体Entity矩形业务对象用户、订单、商品

属性Attribute椭圆实体的特征用户ID、用户名

关系Relationship菱形实体间的联系下单、支付

主键PrimaryKey下划线唯一标识属性用户ID

外键ForeignKey虚线引用其他实体的键订单中的用户ID

5.3关系类型

关系类型说明示例实现方式

一对一（1:1）A对应一个B，B对应一个A用户-用户详情外键或共享主键

一对多（1:N）一个A对应多个B用户-订单B表中加A的外键

多对多（M:N）多个A对应多个B学生-课程中间关联表

5.4ER图示例：电商订单系统

电商订单系统ER图（文字描述）：

┌─────────────┐

│USER│

│用户表│

├─────────────┤

│id│

│username│

│phone│

│email│

│created_at│

└──────┬──────┘

│

│1:N(一个用户可以有多个订单)

│

▼

┌─────────────┐┌─────────────┐

│ORDER││PRODUCT│

│订单表││商品表│

├─────────────┤├─────────────┤

│id││id│

│user_id││name│

│order_no││price│

│amount││stock│

│status││category│

│created_at││created_at│

└──────┬──────┘└──────┬──────┘

││

│1:N│1:N

││

▼▼

┌─────────────────────────────────┐

│ORDER_ITEM│

│订单明细表│

├─────────────────────────────────┤

│id│

│order_id│

│product_id│

│product_name(冗余)│

│quantity│

│unit_price│

│total_price│

└─────────────────────────────────┘

┌─────────────┐

│PAYMENT│

│支付表│

├─────────────┤

│id│

│order_id│

│pay_no│

│amount│

│method│

│status│

│paid_at│

└─────────────┘

关系说明：

-USER1:NORDER（一个用户有多个订单）

-ORDER1:NORDER_ITEM（一个订单有多个明细）

-PRODUCT1:NORDER_ITEM（一个商品出现在多个订单明细中）

-ORDER1:1PAYMENT（一个订单对应一笔支付）

5.5ER图工具推荐

工具类型特点适用场景

draw.io在线免费、易用、支持多种图快速绘图

PlantUML代码文本生成图、可版本控制团队协作

dbdiagram.io在线专为数据库设计、支持DDL导出数据库设计

MySQLWorkbench桌面官方工具、功能强大MySQL项目

Navicat商业可视化、反向工程企业项目

PowerDesigner商业企业级建模大型项目

5.6PlantUML绘制ER图

//PlantUMLER图示例

@startuml

'实体定义

entity"用户USER"asuser{

*id:BIGINT<>

--

username:VARCHAR(64)

phone:VARCHAR(20)

email:VARCHAR(128)

created_at:DATETIME

}

entity"订单ORDER"asorder{

*id:BIGINT<>

--

*user_id:BIGINT<>

order_no:VARCHAR(32)

total_amount:DECIMAL(18,2)

status:TINYINT

created_at:DATETIME

}

entity"订单明细ORDER_ITEM"asorder_item{

*id:BIGINT<>

--

*order_id:BIGINT<>

*product_id:BIGINT<>

product_name:VARCHAR(128)

quantity:INT

unit_price:DECIMAL(18,2)

}

entity"商品PRODUCT"asproduct{

*id:BIGINT<>

--

name:VARCHAR(128)

price:DECIMAL(18,2)

stock:INT

category_id:BIGINT

}

entity"支付PAYMENT"aspayment{

*id:BIGINT<>

--

*order_id:BIGINT<>

pay_no:VARCHAR(64)

amount:DECIMAL(18,2)

method:TINYINT

status:TINYINT

paid_at:DATETIME

}

'关系定义

user||--o{order:"1:N下单"

order||--o{order_item:"1:N包含"

product||--o{order_item:"1:N被订购"

order||--o|payment:"1:1支付"

@enduml

5.7ER图设计检查清单

所有实体是否都识别完整？

每个实体的属性是否完整？

每个实体是否都有主键？

实体之间的关系是否正确？

关系基数是否正确（1:1/1:N/M:N）？

是否识别了所有必要的中间表？

命名是否统一、清晰？

是否有冗余设计（反范式）的必要？

大字段是否需要拆分？

ER图是否有完整的说明文档？

第六章索引设计规范与优化

6.1索引的本质与代价

索引是数据库中用于加速查询的数据结构。它通过牺牲写入性能和存储空间，换取查询性能的大幅提升。理解

索引的本质和代价，是合理设计索引的前提。

维度说明

查询性能索引能将O(n)的全表扫描降为O(logn)的B+树查找

写入性能每次INSERT/UPDATE/DELETE都需要维护索引

存储空间索引本身占用磁盘空间，大表索引可能超过数据大小

维护成本索引碎片化、统计信息更新、重建索引

6.2索引设计核心原则

原则说明

最左前缀原则联合索引按定义顺序匹配，跳过前面的列无法使用

选择性原则高选择性列优先建索引，如用户ID比性别好

覆盖索引原则查询字段都在索引中，避免回表

前缀索引原则长字符串可用前缀索引，节省空间

避免冗余索引(a)和(a,b)中，(a)是冗余的

避免重复索引同名同列的索引重复创建

索引数量控制单表索引不超过5个，写入性能考虑

定期审查清理删除未使用的索引

6.3联合索引设计

--联合索引的顺序原则

--1.等值条件在前，范围条件在后

--查询：WHEREuser_id=1ANDstatus=2ANDcreated_at>'2026-01-01'

--正确索引：(user_id,status,created_at)

--错误索引：(created_at,user_id,status)--范围条件在第一位

--2.高选择性列在前

--user_id有100万个不同值，status只有5个

--正确索引：(user_id,status)

--错误索引：(status,user_id)

--3.排序字段放最后

--查询：WHEREuser_id=1ORDERBYcreated_atDESC

--正确索引：(user_id,created_at)

--因为user_id确定后，created_at天然有序

--4.常用条件优先

--如果有多个查询模式，优先满足最常用的

--联合索引实战示例

--场景一：用户订单列表查询

--WHEREuser_id=?ANDstatus=?ORDERBYcreated_atDESC

CREATEINDEXidx_user_status_created

ONorder_info(user_id,status,created_at);

--场景二：时间范围统计

--WHEREcreated_atBETWEEN?AND?ANDstatus=?

--注意：created_at是范围，应放在最后

CREATEINDEXidx_status_created

ONorder_info(status,created_at);

--场景三：订单号精确查询

--WHEREorder_no=?

CREATEUNIQUEINDEXuk_order_no

ONorder_info(order_no);

--场景四：组合查询+排序

--WHEREuser_id=?ANDstatusIN(1,2)ORDERBYcreated_atDESC

--status是IN也可视为等值

CREATEINDEXidx_user_status_created

ONorder_info(user_id,status,created_at);

--场景五：覆盖索引

--SELECTuser_id,status,created_atFROMorder_info

--WHEREuser_id=?

--索引包含所有查询字段，避免回表

CREATEINDEXidx_user_covering

ONorder_info(user_id,status,created_at);

6.4索引失效场景

--索引失效的常见场景

--1.函数操作

--❌索引失效

SELECT*FROMusersWHEREYEAR(created_at)=2026;

--✅索引生效

SELECT*FROMusers

WHEREcreated_at>='2026-01-01'ANDcreated_at<'2027-01-01';

--2.类型转换

--phone是VARCHAR，传数字

--❌索引失效（隐式转换）

SELECT*FROMusersWHEREphone

--✅索引生效

SELECT*FROMusersWHEREphone=;

--3.前导通配符

--❌索引失效

SELECT*FROMusersWHEREnameLIKE'%张%';

--✅索引生效

SELECT*FROMusersWHEREnameLIKE'张%';

--4.OR条件

--❌部分失效（name无索引）

SELECT*FROMusersWHEREid=1ORname='张三';

--✅改为UNION

SELECT*FROMusersWHEREid=1

UNION

SELECT*FROMusersWHEREname='张三';

--5.NOT和!=

--❌索引可能失效

SELECT*FROMusersWHEREstatus!=1;

--✅改为IN或范围

SELECT*FROMusersWHEREstatusIN(0,2,3);

--6.不满足最左前缀

--索引(a,b,c)

--❌无法使用索引

SELECT*FROMtWHEREb=1;

SELECT*FROMtWHEREc=1;

--✅可以使用索引

SELECT*FROMtWHEREa=1;

SELECT*FROMtWHEREa=1ANDb=1;

SELECT*FROMtWHEREa=1ANDb=1ANDc=1;

SELECT*FROMtWHEREa=1ANDc=1;--a能用，c用不上

--7.ISNULL/ISNOTNULL

--取决于索引实现，可能失效

SELECT*FROMusersWHEREemailISNULL;

--8.索引列参与计算

--❌索引失效

SELECT*FROMordersWHEREamount+100>