03-数据库设计规范：三范式、主键、字段与索引设计原则

数据库设计规范三范式、主键、字段与索引设计原则作者黒漂技术佬适用读者能写SQL但表设计全靠感觉的同学关联场景无人售货柜、智慧农业温控系统一、为什么需要数据库设计规范见过太多项目表结构设计全凭感觉商品表里塞了订单信息、一张用户表200个字段、金额用FLOAT存……等数据量上来后查询慢得要死改一个字段影响半个系统。数据库设计规范的核心目标减少数据冗余、保证数据一致性、提升查询效率。好的表结构是高性能的地基。SQL写得好只能优化百分之几十表结构烂了优化器再强也救不回来。二、数据库三范式详解三范式是关系型数据库设计的基础规范层层递进。别被名字吓到用生活例子解释就很简单。2.1 第一范式1NF每个字段不可再分核心要求表的每一列都是原子的不能拆分。反例——无人售货柜商品表设计不规范product_idproduct_nameprice_info1001可口可乐“零售价3.5,进价2.0”price_info字段把零售价和进价塞在一起想查进价低于2元的商品就很难写SQL。符合1NF的设计product_idproduct_nameretail_pricecost_price1001可口可乐3.502.001NF一句话一个格子只放一个值。2.2 第二范式2NF非主键字段完全依赖主键核心要求在1NF基础上非主键字段必须依赖整个主键不能只依赖主键的一部分。针对联合主键的情况反例——订单明细表用order_id product_id做联合主键order_idproduct_idproduct_namequantitytotal_amount50011001可口可乐27.0050021001可口可乐13.50问题product_name只依赖product_id不依赖order_id。结果可口可乐这个商品名在每笔订单里都重复存了一份。如果商品改名为可口可乐330ml要更新几百条订单记录。符合2NF的设计——拆成两张表订单表 orders: order_id(主键) → total_amount, created_at 订单明细表 order_item: order_id product_id(联合主键) → quantity, unit_price 商品表 product: product_id(主键) → product_name, price2NF一句话别把不该放一起的东西硬塞在一张表。联合主键的表尤其要注意。2.3 第三范式3NF消除传递依赖核心要求在2NF基础上非主键字段之间不能有传递依赖。即非主键字段只能依赖主键不能依赖其他非主键字段。反例——售货柜订单表order_idcabinet_idcabinet_locationtotal_amount5001C001“北京海淀XX大厦”7.005002C001“北京海淀XX大厦”3.50cabinet_location依赖的是cabinet_id而cabinet_id又依赖order_id形成传递依赖order_id → cabinet_id → cabinet_location。后果是同一个柜子的位置信息在每笔订单里重复存储。符合3NF的设计订单表 orders: order_id → cabinet_id, total_amount 柜子表 cabinet: cabinet_id → location3NF一句话信息只存一份别到处复制。三范式总结范式要求口诀1NF字段不可再分一个格子一个值2NF完全依赖主键别塞不该放的3NF消除传递依赖信息只存一份三、反范式设计什么时候故意违反范式三范式是指导原则不是铁律。有时候为了查询性能故意违反范式。典型场景报表查询频繁、表关联代价高。比如无人售货柜每天要生成销售报表查询每个柜子的日销售额。严格按三范式设计需要JOIN订单表和柜子表。如果数据量大JOIN很慢。反范式做法在订单表里冗余一个cabinet_location字段CREATETABLEorders(order_idBIGINTPRIMARYKEY,cabinet_idINTNOTNULL,cabinet_locationVARCHAR(100),-- 冗余字段反范式total_amountDECIMAL(10,2),created_atDATETIME);查询时直接查订单表即可不用JOINSELECTcabinet_location,SUM(total_amount)FROMordersWHEREcreated_at2024-01-01GROUPBYcabinet_location;反范式的代价更新柜子位置时要同步更新所有历史订单的冗余字段否则数据不一致。实际工程中通常用异步消息定时校验来保证最终一致。冗余字段适合读多写少的场景。四、主键设计自增ID vs UUID vs 雪花ID主键是表的灵魂选错了后面改很痛苦。三种主流方案对比4.1 自增IDAUTO_INCREMENTCREATETABLEorders(order_idBIGINTPRIMARYKEYAUTO_INCREMENT,...);优点存储小8字节、插入快顺序写入、简单易用缺点分库分表会冲突、可被预测爬虫可遍历订单、暴露业务量单机MySQL场景下自增ID是最佳选择。但分布式场景就不行了。4.2 UUIDINSERTINTOorders(order_id,...)VALUES(REPLACE(UUID(),-,),...);优点全局唯一、不暴露业务量缺点36字节太长、无序写入导致B树频繁分裂页插入性能差、索引效率低UUID的致命问题是无序。InnoDB的聚簇索引按主键排序存储UUID随机插入会导致大量页分裂写入性能暴跌。不推荐用作InnoDB主键。4.3 雪花IDSnowflake| 1位符号位 | 41位时间戳 | 10位机器ID | 12位序列号 |优点趋势递增插入性能好、全局唯一、可分布式生成、64位长整型存储适中缺点需要机器时钟同步、实现稍复杂推荐方案单机用自增ID分布式用雪花ID。无人售货柜系统有多台服务同时写入用雪花ID最合适。五、外键的利弊分析外键Foreign Key是数据库层面保证表间关系一致性的约束。CREATETABLEorders(order_idBIGINTPRIMARYKEY,cabinet_idINT,...FOREIGNKEY(cabinet_id)REFERENCEScabinet(cabinet_id));外键的好处删除柜子时如果有关联订单数据库会阻止删除防止孤儿数据。但企业级项目中通常不用外键而是用应用层逻辑保证一致性。原因性能开销每次INSERT/UPDATE/DELETE都要检查外键约束死锁风险外键约束增加了锁的复杂度高并发下容易死锁分库分表障碍跨库的外键无法实现数据迁移困难导入数据时外键检查让人头疼实际做法表结构里不留外键约束但关联字段一定要建索引一致性别应用层保证。六、字段设计规范6.1 类型选择原则数据类型适用场景注意事项TINYINT状态值0/1/2TINYINT(1)常被ORM当布尔值INT / BIGINTID、计数订单ID用BIGINT防溢出DECIMAL(10,2)金额绝不用FLOAT/DOUBLEVARCHAR(N)变长字符串合理设N别动辄VARCHAR(255)TEXT / BLOB大文本/二进制不建索引、单独存表DATETIME时间8字节范围广TIMESTAMP时间4字节范围到2038年6.2 字段设计规范CREATETABLEcabinet(cabinet_idINTNOTNULLAUTO_INCREMENT,cabinet_codeVARCHAR(32)NOTNULLCOMMENT设备编号,locationVARCHAR(100)NOTNULLCOMMENT投放位置,temperatureDECIMAL(4,1)DEFAULTNULLCOMMENT柜内温度(℃),statusTINYINTNOTNULLDEFAULT1COMMENT状态: 0离线 1在线 2故障,last_heartbeatDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT最后心跳时间,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,updated_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(cabinet_id),UNIQUEKEYuk_code(cabinet_code))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT售货柜信息表;规范要点能NOT NULL就NOT NULLNULL值参与运算、比较、索引都有特殊行为容易出bug给字段设默认值DEFAULT 1、DEFAULT CURRENT_TIMESTAMP插入时不用显式指定ON UPDATE CURRENT_TIMESTAMPupdated_at字段自动更新最后修改时间加COMMENT每个字段都要写注释三个月后你自己都不记得status的2代表什么金额用DECIMAL(10,2)10位总长2位小数最大支持99999999.99状态枚举用TINYINT比VARCHAR省空间、查询快无人机售货柜的温度监控字段temperature允许为NULL——因为柜子离线时没有温度数据NULL比用0或-1更语义准确。七、索引设计原则索引是数据库性能的核心。设计不好的索引比没有索引还可怕因为索引也要占空间和维护成本。7.1 什么字段该建索引该建主键自动创建聚簇索引外键关联字段如cabinet_id、product_id查询条件字段WHERE后面常用的排序字段ORDER BY后面的分组字段GROUP BY后面的不该建数据量极少的表全表扫描比走索引还快频繁更新的字段索引也要跟着更新区分度低的字段如性别只有男/女索引基本没用区分度判断SELECT COUNT(DISTINCT field)/COUNT(*) FROM table;结果越接近1区分度越高越适合建索引。比如order_id区分度1.0适合建gender区分度0.5不适合。7.2 联合索引多个字段组合成一个索引-- 售货柜ID 下单时间 联合索引CREATEINDEXidx_cabinet_createdONorders(cabinet_id,created_at);这条索引能加速两种查询-- 能用到索引 ✅WHEREcabinet_id1-- 能用到索引 ✅WHEREcabinet_id1ANDcreated_at2024-01-01-- 用不到索引 ❌缺少cabinet_idWHEREcreated_at2024-01-017.3 最左前缀原则联合索引(A, B, C)相当于创建了(A)、(A,B)、(A,B,C)三个索引。查询必须从最左边的字段开始使用。查询条件能否用到索引(A,B,C)WHERE A1✅ 用到AWHERE A1 AND B2✅ 用到A,BWHERE A1 AND B2 AND C3✅ 用到A,B,CWHERE B2❌ 缺少AWHERE A1 AND C3⚠️ 只用到AC用不到设计技巧联合索引中区分度高的字段放前面查询频率高的字段放前面。idx(cabinet_id, created_at)中cabinet_id区分度高每台柜子不同放前面。7.4 索引不是越多越好每加一个索引写入变慢INSERT/UPDATE/DELETE要同步更新索引存储变大索引也是数据占磁盘空间优化器选择变难太多索引优化器可能选错实际建议一张表的索引数量控制在5个以内联合索引优先于单列索引。总结设计维度核心原则三范式减少冗余、消除传递依赖反范式读多写少时可冗余用空间换时间主键单机自增ID分布式雪花ID外键不建议用应用层保证一致性字段NOT NULL优先、金额用DECIMAL、状态用TINYINT索引区分度高优先、联合索引最左前缀、控制在5个以内表结构设计是架构师的基本功。数据库设计得好后面开发、优化、维护都会顺畅得多。