表结构设计详解

张

张建站

2026/4/23 22:54:37

10分钟阅读

表结构设计详解本章导读表结构设计是数据库性能优化的基础好的设计可以避免后续大量性能问题。本章将系统讲解范式与反范式设计、分库分表策略、字段类型选择等核心内容。学习目标目标1理解数据库三大范式掌握范式与反范式的权衡目标2掌握分库分表的设计原则和常见策略目标3能够根据业务需求选择合适的字段类型前置知识熟悉SQL基本语法了解数据库基本概念阅读时长约 18 分钟一、知识概述表结构设计是数据库性能优化的基础。合理的表结构设计可以提高查询效率、减少存储空间、简化应用逻辑。1.1 表设计原则┌─────────────────────────────────────────────────────────────┐ │ 表设计原则 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 1. 范式设计 │ │ - 减少数据冗余 │ │ - 保证数据一致性 │ │ - 适合OLTP系统 │ │ │ │ 2. 反范式设计 │ │ - 适当冗余减少JOIN │ │ - 提高查询性能 │ │ - 适合OLAP系统 │ │ │ │ 3. 权衡取舍 │ │ - 写入频率 vs 读取频率 │ │ - 存储空间 vs 查询效率 │ │ - 一致性要求 vs 性能需求 │ │ │ └─────────────────────────────────────────────────────────────┘1.2 三大范式第一范式1NF字段不可分割 - 每个字段都是原子性的第二范式2NF消除部分依赖 - 非主键字段完全依赖主键第三范式3NF消除传递依赖 - 非主键字段不依赖其他非主键字段二、范式与反范式2.1 范式设计-- -- 订单系统范式设计-- -- 用户表CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100),phoneVARCHAR(20),created_atDATETIMEDEFAULTCURRENT_TIMESTAMP,INDEXidx_username(username),INDEXidx_phone(phone))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 商品表CREATETABLEproducts(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(200)NOTNULL,category_idBIGINT,priceDECIMAL(10,2)NOTNULL,stockINTDEFAULT0,created_atDATETIMEDEFAULTCURRENT_TIMESTAMP,INDEXidx_category(category_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 订单表CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(50)NOTNULLUNIQUE,user_idBIGINTNOTNULL,statusTINYINTDEFAULT0,total_amountDECIMAL(10,2),created_atDATETIMEDEFAULTCURRENT_TIMESTAMP,INDEXidx_user(user_id),INDEXidx_order_no(order_no),FOREIGNKEY(user_id)REFERENCESusers(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 订单明细表CREATETABLEorder_items(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_idBIGINTNOTNULL,product_idBIGINTNOTNULL,quantityINTNOTNULL,unit_priceDECIMAL(10,2)NOTNULL,INDEXidx_order(order_id),INDEXidx_product(product_id),FOREIGNKEY(order_id)REFERENCESorders(id),FOREIGNKEY(product_id)REFERENCESproducts(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;2.2 反范式设计-- -- 订单系统反范式设计查询优化-- -- 订单表增加冗余字段CREATETABLEorders_denormalized(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(50)NOTNULLUNIQUE,user_idBIGINTNOTNULL,usernameVARCHAR(50),-- 冗余用户名user_phoneVARCHAR(20),-- 冗余手机号statusTINYINTDEFAULT0,total_amountDECIMAL(10,2),item_countINTDEFAULT0,-- 冗余商品数量created_atDATETIMEDEFAULTCURRENT_TIMESTAMP,INDEXidx_user(user_id),INDEXidx_status_created(status,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 优点查询订单时不需要JOIN用户表-- 缺点用户信息变更需要同步更新三、分库分表策略3.1 垂直拆分-- -- 垂直拆分按业务拆分库-- -- 用户库CREATEDATABASEuser_db;-- 用户基本信息表CREATETABLEuser_db.users(...);-- 用户扩展信息表CREATETABLEuser_db.user_profiles(...);-- 订单库CREATEDATABASEorder_db;-- 订单表CREATETABLEorder_db.orders(...);-- 订单明细表CREATETABLEorder_db.order_items(...);-- 商品库CREATEDATABASEproduct_db;-- 商品表CREATETABLEproduct_db.products(...);-- 库存表CREATETABLEproduct_db.inventory(...);3.2 水平拆分-- -- 水平拆分按规则拆分表-- -- 按用户ID取模分表-- 订单表拆分为 orders_0 到 orders_15CREATETABLEorders_0(idBIGINTPRIMARYKEY,order_noVARCHAR(50)NOTNULL,user_idBIGINTNOTNULL,...)ENGINEInnoDB;-- 路由规则table_index user_id % 16-- 按时间范围分表CREATETABLEorders_202401(...)ENGINEInnoDB;CREATETABLEorders_202402(...)ENGINEInnoDB;3.3 分片键选择┌─────────────────────────────────────────────────────────────┐ │ 分片键选择原则 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 1. 数据均匀分布 │ │ - 选择高基数列 │ │ - 避免数据倾斜 │ │ │ │ 2. 查询效率 │ │ - 常用查询条件作为分片键 │ │ - 减少跨分片查询 │ │ │ │ 3. 常用分片键 │ │ - 用户ID │ │ - 订单ID │ │ - 时间 │ │ │ │ 4. 分片数量 │ │ - 单表不超过1000万行 │ │ - 考虑未来增长 │ │ │ └─────────────────────────────────────────────────────────────┘四、字段类型选择4.1 数值类型-- -- 数值类型选择-- -- 整数类型-- TINYINT: 1字节, -128~127 / 0~255-- SMALLINT: 2字节-- MEDIUMINT: 3字节-- INT: 4字节-- BIGINT: 8字节-- 推荐使用合适的类型节省空间statusTINYINT,-- 状态值0-255足够ageTINYINTUNSIGNED,-- 年龄无符号user_idBIGINT,-- 用户ID可能很大-- 小数类型priceDECIMAL(10,2),-- 精确小数金额rateFLOAT,-- 近似小数比率4.2 字符串类型-- -- 字符串类型选择-- -- CHAR vs VARCHAR-- CHAR: 定长最大255适合MD5、手机号等-- VARCHAR: 变长最大65535适合变长字符串phoneCHAR(11),-- 手机号定长usernameVARCHAR(50),-- 用户名变长descriptionVARCHAR(500),-- 描述变长-- TEXT类型超过VARCHAR限制-- TINYTEXT: 255字节-- TEXT: 65535字节-- MEDIUMTEXT: 16MB-- LONGTEXT: 4GBcontentTEXT,-- 文章内容-- 使用VARCHAR代替TEXT如果长度允许-- TEXT会存储在溢出页影响性能4.3 时间类型-- -- 时间类型选择-- -- DATETIME vs TIMESTAMP-- DATETIME: 8字节, 1000-01-01 ~ 9999-12-31-- TIMESTAMP: 4字节, 1970-01-01 ~ 2038-01-19 (自动时区转换)created_atDATETIMEDEFAULTCURRENT_TIMESTAMP,updated_atDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,-- 推荐使用BIGINT存储时间戳避免2038问题created_timeBIGINT,-- 毫秒时间戳五、最佳实践总结5.1 表设计检查清单□ 选择合适的存储引擎 □ 字段类型选择合理 □ 主键设计合理 □ 索引设计合理 □ 范式与反范式权衡 □ 分库分表规划 □ 字符集和排序规则 □ 命名规范统一5.2 常见问题解决问题解决方案大字段影响性能拆分到单独表查询慢添加索引、反范式数据量大分库分表写入慢优化索引、批量写入六、总结表结构设计是数据库优化的基础。通过合理选择范式与反范式、设计分库分表策略、选择合适的字段类型可以显著提升数据库性能。核心要点范式设计减少冗余保证一致性反范式设计适当冗余提升查询性能分库分表应对数据量增长字段类型选择合适的类型节省空间六、思考与练习思考题基础题什么是数据库三大范式过度的范式化会带来什么问题进阶题在什么场景下应该使用反范式设计反范式化有什么代价实战题一个电商系统的订单表预计数据量达到10亿级别如何设计分库分表方案编程练习练习设计一个博客系统的数据库表结构要求包含用户表、文章表、评论表、标签表至少达到第三范式为高频查询场景添加适当的反范式设计为每个表选择合适的字段类型和索引请画出ER图并给出建表SQL语句。章节关联前置章节SQL优化详解后续章节MySQL调优详解扩展阅读《数据库系统概念》下一章预告下一章将讲解MySQL调优包括参数配置优化、主从复制、读写分离等内容全面提升数据库性能。本章完