一、基础规范
-
命名规范
-
表名/字段名:小写+下划线,避免保留字(如
order→orders)。order→orders)。 -
见名知义:
user、order_detail。 -
避免冗余命名:
user表字段用name而非user_name。
-
-
存储引擎
-
InnoDB(默认):支持事务、行锁、外键,适用高并发写入。
-
MyISAM:仅适合读多写少场景(如日志表),不支持事务。
-
-
字符集
统一使用utf8mb4(支持Emoji及生僻字),排序规则选utf8mb4_general_ci。
-
表关系设计
-
一对一:通过唯一外键关联(如用户表与用户详情表)。
-
一对多:外键在“多”侧表(如部门表与员工表)。
-
多对多:需中间表(如学生表与课程表通过选课表关联)。
-
二、范式与反范式
-
三范式原则
-
第一范式(1NF):字段原子性(如拆分
address为省+市+详细地址)。 -
第二范式(2NF):消除部分依赖(复合主键时,非主键字段依赖全部主键)。
-
第三范式(3NF):消除传递依赖(如
订单表冗余用户姓名需拆分)。
-
-
反范式设计
-
适当冗余提升查询性能:如统计字段
订单总金额(更新时需维护一致性)。 -
场景:高频查询且对一致性要求不苛刻(如电商订单列表)。
-
三、字段与约束设计
-
数据类型优化
-
整数:
TINYINT(1字节)、INT(4字节),避免BIGINT过度使用。整数:TINYINT1字节)、INT(4字节),避免BIGINT过度使用。 -
字符:
VARCHAR按需设置长度(如手机号CHAR(11),邮箱VARCHAR(100))。字符:VARCHAR按需设置长度(如手机号CHAR(11),邮箱VARCHAR(100)). -
时间:
DATETIME(范围大,无时区)、TIMESTAMP(4字节,自动时区转换)。 -
禁用ENUM:改用
TINYINT+注释(扩展性差)。禁用ENUM:改用TINYINT+注释(扩展性差)。
-
-
约束规则
-
主键(PRIMARY KEY):唯一标识记录,不可为空。主键(PRIMARY KEY):唯一标识记录,不可为空。
-
外键(FOREIGN KEY):建立表间关联(建议应用层实现,避免性能损耗)。
-
唯一约束(UNIQUE):确保列值唯一,允许多个
NULL。唯一约束(UNIQUE):确保列值唯一,允许多个NULL -
非空约束(NOT NULL):非必要字段设置默认值替代
NULL(如status默认0)。非空约束(NOT NULL):非必要字段设置默认值替代NULL(如status默认0)。
-
四、主键与索引设计
-
主键策略
-
自增主键:简单高效,适用单机场景(
BIGINT UNSIGNED)。 -
业务主键:天然唯一键(如订单号
order_no),注意长度控制。 -
分布式主键:雪花算法(时间戳+机器ID+序列号)。
-
-
索引优化
-
索引类型:主键索引、唯一索引、普通索引、联合索引(最左匹配原则)。
-
覆盖索引:查询字段均在索引中,避免回表。
-
前缀索引:长文本字段取前N字符(如
INDEX(email(10)))。前缀索引:长文本字段取前N字符(如INDEX(email(10)))。 -
禁忌:频繁更新字段、低区分度字段(如性别)避免索引。
-
五、查询设计
-
聚合查询
聚合函数:-
MAX()/MIN()获取极值。MAX()/MIN()获取极值。 -
SUM()/AVG()对数值列求和、求平均。SUM()/AVG()对数值列求和、求平均。 -
COUNT(*)统计行数,COUNT(列)忽略NULL。COUNT(*)统计行数,COUNT(列)忽略NULL
-
分组与筛选:
-
GROUP BY按列分组(如GROUP BY 部门)。 -
HAVING对分组结果筛选(如HAVING AVG(工资) > 10000)。HAVING对分组结果筛选(如HAVING AVG(工资) > 10000)。
-
联合查询
JOIN操作:-
右连接(RIGHT JOIN):保留右表全部记录,左表无匹配则填充
NULL。 -
左连接(LEFT JOIN):保留左表全部记录,右表无匹配则填充
NULL。 -
内连接(INNER JOIN):仅返回匹配记录。内连接(INNER JOIN):仅返回匹配记录。
-
多表关联:通过
ON指定连接条件(如ON 表1.列=表2.列)。
-
-
-
六、数据操作与表结构
数据操作:
- 插入数据:
INSERT INTO 表名 [(列名)] SELECT ...(结合子查询) - 插入数据:
INSERT INTO 表名 [(列名)] SELECT ...(结合子查询)。 - 敏感数据:密码使用哈希算法存储(如
SHA256)。 - 表结构设计:
字段扩展性:预留ext_info字段(JSON类型存储动态属性)。
大字段处理:TEXT/BLOB单独存储,主表保留核心字段。
冷热分离:历史数据归档(如order_history表)。
七、性能优化策略
-
分库分表
垂直分表:拆分大字段(如商品详情与基础信息分离)。
水平分表:按规则分片(如user_id % 16分16张表)。
- 安全设计
权限控制:数据库账号遵循最小权限原则。
注释与文档:字段注释明确含义(如status枚举值说明)。
示例:用户表与订单表联合查询
-- 统计每个用户的订单总数和总金额
SELECT
u.id AS user_id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount
FROM
user u
LEFT JOIN
order o ON u.id = o.user_id
GROUP BY
u.id
HAVING
total_amount > 1000;
总结:
MySQL表设计需兼顾规范性与性能,核心包括:
-
合理设计表关系(一对一、一对多、多对多)。
-
灵活运用范式与反范式平衡数据冗余与查询效率。
-
优化字段类型、索引及主键策略。
-
掌握聚合查询与多表连接技巧,满足复杂业务需求。
-
通过分库分表、冷热分离等手段应对大数据场景。
308



被折叠的 条评论
为什么被折叠?



