本文已收录在Github，关注我，紧跟本系列专栏文章，咱们下篇再续！
- 🚀 魔都架构师 | 全网30W技术追随者
- 🔧 大厂分布式系统/数据中台实战专家
- 🏆 主导交易系统百万级流量调优 & 车联网平台架构
- 🧠 AIGC应用开发先行者 | 区块链落地实践者
- 🌍 以技术驱动创新，我们的征途是改变世界！
- 👉 实战干货：编程严选网
1 前言
注册会员 → 展示商品 → 加入购物车 → 生成订单
涉及电商常用功能模块的数据库设计
涉及常见问题的数据库解决方案
只包含数据库开发部分，不涉及前后端程序开发
2 准备工作
MySQL实例，推荐使用MySQL5.7版本
MySQL图型客户端程序，推荐使用 SQLyog
Linux命令和Shell脚本的基础知识
3 项目说明
- 用户模块 → 完成用户注册和登录验证
- 商品模块 → 前后台商品管理和浏览
- 订单模块 → 订单及购物车的生成和管理
- 仓配模块 → 仓库库存和物流的管理
4 数据库设计规范
数据结构设计：逻辑设计 → 物理设计
实际工作中：逻辑设计 + 物理设计
物理设计：表名 字段名 字段类型
所有数据库对象名称必须使用小写字母并用下划线分割
不同的数据库名：DbName dbname
不同的表名：Table table tabLe
所有数据库对象名称禁止使用MySQL保留关键字
select id,username , 【from】 ,age 【from】 tb_user
MySQL Keywords and Reserved Words。
数据库对象的命名要能做到见名识义，最好不要超过32个字符。
如用户数据库 mc_userdb，用户账号表 user_account
临时库表必须以tmp为前缀并以日期为后缀
备份库，备份表必须以bak为前缀并以日期为后缀
所有存储相同数据的列名和列类型必须一致
CREATE TABLE customer_inf ( customer_inf_id int unsigned AUTO_INCREMENT not null comment '自增', # see!!! customer_id int unsigned not null comment 'customer login表的自增ID', customer_name varchar(20) not null comment '用户真实姓名', identity_card_type tinyint not null default 1 comment '证件类型：1', identity_card_no varchar(20) comment '证件号码',
CREATE TABLE order_master( order_id int unsigned not null AUTO_INCREMENT comment '订单ID', order_sn bigint unsigned not null comment '订单编号 yyyymmddnnnnnnnnn', # see!!! customer_id int unsigned not null comment '下单人ID', shipping_user varchar(10) not null comment '收货人姓名', province SMALLINT not null comment '收货人所在省',
5 数据库命名规范
- 所有数据库对象名称必须小写
- 命名要做到见名识意，禁止使用MySQL保留关键字
- 临时表以tmp_开头，备份表以bak_开头并以时间戳结尾
- 所有存储相同数据的列名和列类型必须一致
6 数据库基础设计规范
MySQL 5.5使用之前Myisam（默认存储引擎）情况。
所有表必须使用Innodb存储引擎，5.6 以后的默认引擎。
支持事务，行级锁，更好的恢复性，高并发下性能更好。
数据库和表的字符集统一使用UTF8
统一字符集可避免由于字符集转换产生的乱码。
MySQL中UTF8字符集汉字占3个字节，ASCII码占用1个字节。
所有表和字段都需要添加注释
使用comment从句添加表和列的备注。
从一开始就进行数据字典的维护。
尽量控制单表数据量的大小，建议控制在500万以内。500万并不是MySQL的限制。
MySQL最多可以存储多少万数据呢？取决于存储设置和文件系统。
可用历史数据归档、分库分表等手段来控制数据量大小。
谨慎使用MySQL分区表
分区表在物理上表现为多个文件，在逻辑上表现为一个表。
谨慎选择分区键，跨分区查询效率可能更低。
建议采用物理分表的方式管理大数据。
尽量做到冷热数据分离，减小表的宽度
减少磁盘IO，保证热数据的内存缓存命中率。
利用更有效的利用缓存，避免读入无用的冷数据。
经常一起使用的列放到一个表中。
禁止在表中建立预留字段
预留字段的命名很难做到见名识义。
预留字段无法确认存储的数据类型，所以无法选择合适的类型。
对预留字段类型的修改，会对表进行锁定。
禁止事项
- 禁止在数据库中存储图片、文件等二进制数据
- 禁止在线上做数据库压力测试
- 禁止从开发环境、测试环境直连生产环境数据库
小结
- 所有表必须使用Innodb存储引擎
- 所有表及字段都要有备注信息，并使用UTF8字符集
- 要做到尽量控制单表大小，并且把冷热数据分离
- 禁止使用预留字段及在表中存储大的二进制数据
7 数据库索引设计规范
索引对数据库的查询性能来说是非常重要的，不要滥用索引。
限制每张表上的索引数量，建议单张表索引不超过5个。
索引并不是越多越好！索引可以提高效率同样可以降低效率。
禁止给表中的每一列都建立单独的索引。
Innodb是按照哪个索引的顺序来组织表的呢？主键！
每个Innodb表必须有一个主键。
- 不使用更新频繁的字段作为主键，不使用多列主键
- 不使用UUID、MD5、HASH、字符串列作为主键
- 主键建议选择使用自增ID值
常见索引列建议
在哪些列上建立索引？
- SELECT、UPDATE、DELETE语句的WHERE从句中的列
- 包含在ORDER BY、GROUP BY、DISTINCT中的字段
- 多表JOIN的关联列
如何选择索引列的顺序
- 区分度最高的列放在联合索引的最左侧
- 尽量把字段长度小的列放在联合索引的最左侧
- 使用最频繁的列放到联合索引的左侧
避免建立冗余索引和重复索引
primary key(id)、index(id)、unique index(id)index(a,b,c)、index(a,b)、index(a)
覆盖索引
频繁的查询，优先考虑覆盖索引 覆盖索引：包含所有查询字段的索引
避免InnoDB表进行索引的二次查找，可将随机IO变为顺序IO，加快查询效率
外键
MySQL 建立外键时，会自动在外键建立索引
不建议外键约束，但一定在表与表之间的关联键上建立索引，而尽量避免直接使用外键。
外键可用于保证数据的参照完整性，但建议在业务应用端实现
外键会影响父表和子表的写操作从而降低性能
小结
- 每个Innodb表都要有一个主键
- 限制表上索引的数量，避免建立重复和冗余索引
- 注意合理选择复合索引键值的顺序
8 数据库字段设计规范
优先选择符合存储需要的最小的数据类型
将字符串转化为数字类型存储
INET_ATON('255.255.255.255') = 4294967295 INET_NTOA(4294967295) = '255.255.255.255'
INET_ATON
建表：
create table JavaEdge (ip int unsigned, name char(1)) completed in 37 ms
插入数据
insert into JavaEdge values(inet_aton('192.168.1.200'), 'A'), (inet_aton('200.100.30.241'), 'B') 2 rows affected in 13 ms
使用INET_ATON()函数将字符串形式的 IP 地址转换为无符号整数进行存储：
'192.168.1.200'→ 对应整数值（如：3232235776）'200.100.30.241'→ 对应整数值（如：3366451009）
这种方式高效且节省空间，符合数据库设计中“使用最小合适数据类型”的最佳实践。
insert into JavaEdge values(inet_aton('24.89.35.27'), 'C'), (inet_aton('100.200.30.22'), 'D') 2 rows affected in 5 ms
select * from JavaEdge 4 rows retrieved starting from 1 in 402 ms (execution: 6 ms, fetching: 396 ms) 查询结果 ip name ----------------- 3232235976 A 3362004721 B 408494875 C 1690836502 D
sql> select * from JavaEdge where ip = inet_aton('192.168.1.200') 1 row retrieved starting from 1 in 76 ms (execution: 7 ms, fetching: 69 ms) ip name 1 3232235976 A
sql> select inet_ntoa(ip) from JavaEdge 4 rows retrieved starting from 1 in 55 ms (execution: 5 ms, fetching: 50 ms) inet_ntoa(ip) 1 192.168.1.200 2 200.100.30.241 3 24.89.35.27 4 100.200.30.22
当前很多应用都适用字符串char(15)来存储IP地址（占用16个字节)，利用INET_ATON和INET_NTOA函数，来存储IP地址效率很高，使用unsigned int 就可以满足需求，不需要使用bigint/字符型，只需要4个字节，节省存储空间，效率高。
无符号相对于有符号可以多出一倍的存储空间。
对于非负数据采用无符号整型进行存储
SIGNED INT -2147483648 ~ 2147483647
UNSIGNED INT 0 ~ 4294967295
VARCHAR(N) 中的 N 代表的是字符数，而不是字节数
MySQL 中的VARCHAR(255)可存储255个中文字符!!!
使用 UTF8 存储汉字 Varchar(255) = 765 个字节
过大的长度会消耗更多的内存
避免使用TEXT、BLOB数据类型
TinyText、Text、MediumText、LongText
建议把BLOB或是TEXT列分离到单独的扩展表中
TEXT或BLOB类型只能使用前缀索引
避免使用ENUM数据类型
修改ENUM值需要使用ALTER语句
ENUM类型的ORDER BY操作效率低，需要额外操作
禁止使用数值作为ENUM的枚举值
尽可能把所有列定义为 NOT NULL
索引 NULL 列需要额外的空间来保存，所以要占用更多的空间
进行比较和计算时要对 NULL 值做特别的处理
字符串存储日期型的数据（不正确的做法）
缺点1：无法用日期函数进行计算和比较
缺点2：用字符串存储日期要占用更多的空间
使用TIMESTAMP或DATETIME类型存储时间
TIMESTAMP 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
TIMESTAMP占用4字节和INT相同，但比INT可读性高
超出TIMESTAMP取值范围的使用DATETIME类型
浮点数类型
- 非精准浮点：float, double
- 精准浮点：decimal
同财务相关的金额类数据，必须使用decimal类型
Decimal类型为精准浮点数，在计算时不会丢失精度
占用空间由定义的宽度决定
可用于存储比bigint更大的整数数据
小结
- 选择符合存储要求的最小的数据类型
- 避免使用Blob或是Text类型及ENUM类型
- 每个字段尽可能具有NOT NULL属性
- 使用datetime或timestamp类型存储时间
9 SQL开发规范
预编译语句
建议使用预编译语句进行数据库操作
mysql> PREPARE stmt1 -> FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ | 5 | +------------+ mysql> DEALLOCATE PREPARE stmt1;
只传参数，比传递SQL语句更高效
相同语句可以一次解析，多次使用，提高处理效率
隐式转换
避免数据类型的隐式转换
隐式转换会导致索引失效
select name,phone from customer [where id = '111']
充分利用表上已经存在的索引
合理利用存在索引，而不是盲目增加索引。
避免使用双%号的查询条件。如：
a like '%123%'
一个SQL只能利用到复合索引中的一列进行范围查询
使用left join 或 not exists 来优化not in 操作。
数据库设计时，应该要对以后扩展进行考虑。
程序连接不同的数据库使用不同的账号，禁止跨库查询
为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险
禁止使用SELECT * 必须使用 SELECT <字段列表> 查询
消耗更多的CPU和IO以及网络带宽资源
无法使用覆盖索引
可减少表结构变更带来的影响
禁止使用不含字段列表的INSERT语句
insert into t values( 'a' , b' , c' ); insert into t(c1,c2,c3) values( 'a' , b' , c' );
可减少表结构变更带来的影响
避免使用子查询，可以把子查询优化为 JOIN 操作
- 子查询的结果集无法使用索引
- 子查询会产生临时表操作，如果子查询数据量大则严重影响效率
- 消耗过多的 CPU 及 IO 资源
