我是在凌晨两点被一条慢SQL的电话拉起来的。数据库告警面板上那条查询跑了12秒把整个订单中心的读流量堵了一大半。登录进去一看是一张单表1.2亿行的订单表查询条件只有user_id和status但索引却建得五花八门——有单列索引有联合索引还有几个从来就没走到过的扩展字段索引。真正让我失眠的是问题根本不在那条SQL而在两年前那张表一开始的数据库设计决策。那次故障之后我把过去三年写过的所有建表DDL翻出来逐个评审发现痛点源头几乎都不是SQL写得不好而是表从一开始就设计错了。字段类型拍脑袋、索引跟着临时需求走、该冗余的字段没冗余、不该冗余的字段塞了一堆这些毛病在数据量只有几十万时毫无存在感一旦涨到千万级别就开始集中爆雷。这篇笔记不打算覆盖教科书式的面面俱到只记录我在真实项目里反复踩过的坑和沉淀下来的设计套路适合正在做业务系统、又在为数据库性能焦虑的开发者和架构师参考。1. 一张从10个字段膨胀到57个字段的订单表问题出在哪1.1 两年里表是怎么一步步变胖的这张订单表最初的设计其实很清爽id、user_id、order_no、status、amount、create_time总共10个字段左右查询模式也简单按用户查订单、按单号查详情顶多加个时间范围。但业务跑起来之后几乎每个迭代都在追加字段优惠券ID、活动标记、渠道来源、催付标记、超时关单标记、备注、JSON扩展位……每次需求都说加个字段就行没人认真想过这个字段未来的查询方式、更新频率和数据大小。两年之后表结构膨胀到57个字段其中3个是TEXT8个字段只被写入过一次从来没有出现在任何查询条件里。这里要澄清一下字段多本身不是罪真正的问题是所有字段混在同一行存储里。行变大之后InnoDB缓冲池能缓存的记录数急剧下降同样是读最近1000条订单原来几个字段的表可能几十个数据页就够了现在一个行要跨更多页加上日常开发习惯是裸写SELECT *三个TEXT字段每次都被拖出来IO和内存开销全线拉升慢是必然的。1.2 EXPLAIN里最刺眼的那一行ALL我把线上抓下来的慢查询跑了轮EXPLAIN最扎眼的两个细节彻底改变了我对字段设计的认知。第一个是隐式类型转换coupon_id字段在表里定义成varchar(32)但接口层传入的是整数MySQL做比较时会把varchar转换成数值这一列上的索引直接失效。执行计划里type从ref变成了ALL扫描行数从不到一万变成上千万。这类问题不把字段定义和代码参数逐字对照根本发现不了。第二个是索引冗余和缺失并存。表上既有(user_id, status)联合索引也有status单列索引还有(channel_id, create_time)这种当初为某个运营活动临时建的索引而真正高频的按用户查最近订单却没有一个能同时覆盖排序和分页的索引。索引不是越多越好每一次写入都要维护全量索引每多一个索引写入和更新的成本就高一分。这张表写流量本身不小索引又冗余等于每次插入都在给自己上刑。1.3 那次故障之后我的设计观彻底变了以前我的习惯是先跑起来出问题了再加索引那晚之后我彻底改掉了这个习惯。数据库设计不是建表那一小时的事而是从需求分析、实体建模、字段类型、主键选择、索引规划到上线评审的全过程。表结构一旦定下来后面每一行数据、每一条查询都在为当天的取舍买单。这个认知贯穿了后面所有章节也是我建议每个做业务开发的人尽早建立的意识。2. 建表之前需求分析和实体建模要做到多细2.1 先分清业务过程和数据快照很多人拿到需求就开始画ER图但ER图画得再多不如先问一句这张表记录的是一次业务过程还是一个当前状态业务过程是订单、支付流水、积分流水、操作日志这类数据特征是只追加、几乎不修改、天生按时间增长数据快照则是用户资料、商品信息、库存数量、配置参数这类特征是可修改、有唯一约束、读多写少且需要保证最新。把这两类数据混在一个模型里就会出事故。最典型的是修改商品资料时把历史订单里的商品名称、价格也连带改了用户看到的是昨天下单时写的商品名和今天不一样。正确做法是过程类数据保持不可变必要时在写入时做快照快照类数据才允许原地更新。设计表之前先给每个实体贴上过程或快照标签很多模糊地带会立刻清晰。2.2 实体识别与关系判断的标准做法实体识别没有捷径就是把需求文档里的名词圈出来、动词划出来。名词大部分是实体动词大部分是关系。比如用户签到获得积分名词是用户和积分动词是签到和获得积分兑换商品生成订单名词是积分、商品、订单动词是兑换和生成。实体之间的数量关系决定要不要中间表订单和商品是多对多必须拆出order_item用户和收货地址是一对多直接加user_id外键用户和积分账户是一对一可以在账户表上建唯一键。关系判断的关键是别偷懒。很多业务看似一对一实际上是一对多。比如一个用户只有一个默认地址不代表用户表该放地址字段因为用户可能有多条历史地址。建模时要区分业务上当前唯一和物理上永远唯一前者用关联表加唯一约束后者才适合合并成一张表。我最常犯的错就是被现在只有一个误导结果半年后改成一对多时迁移成本极高。2.3 我建模时固定自问的五个问题第1章那张订单表的悲剧根源就是建模时没人问过下面这五个问题。现在每次建模我都会把答案写进设计文档再动手。数据从哪里来由哪个系统产生是用户操作产生还是后台配置产生谁会改这条数据允许原地修改还是只允许新增、不允许变更数据怎么读按用户维度查、按时间维度查还是做报表聚合统计数据生命周期多长实时数据保留多久什么时候该归档是否涉及手机号、身份证、地址等敏感字段是否需要加密和脱敏这五个问题的答案直接决定表的粒度、要不要分表、索引往哪建、字段要不要加密。如果中间有一个答不上来说明需求还没聊透这时候建的表就是在给未来埋雷。3. 字段设计第一天偷的懒后期要花十倍时间补3.1 类型选型的性价比排序字段类型是数据库设计里最容易被忽略、又最影响长期稳定性的决策。我见过太多金额用FLOAT、日期用VARCHAR、状态用自由字符串的案例无一例外都在后期付出过惨痛代价。下面这张表是我在项目里反复用到的选型对照几乎可以直接抄。场景推荐类型不推荐类型核心原因状态/枚举TINYINTVARCHAR(20)存储小、比较快、通过注释表管理语义金额DECIMAL(10,2)FLOAT/DOUBLE浮点有精度误差账户对不上时会出大事故日期时间DATETIMEVARCHAR可以比较大小、可以用日期函数、排序结果正确主键/外键BIGINTVARCHAR(64)聚簇索引性能好避免字符串主键导致页分裂长文本TEXT/MEDIUMTEXTVARCHAR(5000)行内存储策略不同大字段会拖垮高频查询金额用DECIMAL这件事我多说一句。FLOAT在计算0.10.2时会得到0.30000000000000004单笔看不出来累计几百笔后对不上账就很麻烦。DECIMAL是精确小数虽然计算比浮点慢一点但账目正确性远比那一点性能重要。日期字段同理字符串存日期排序是按字典序排的当月超过10号就会出现2024-01-09排在2024-01-10后面之外的混乱情况而DATETIME天然解决这些问题。3.2 NULL、默认值和字符集三个容易被低估的决定NULL的问题很容易被忽视。一个字段允许NULL意味着查询条件里要多写IS NULL或IS NOT NULL意味着COUNT、SUM、JOIN的结果都可能和你直觉不一致更重要的是索引列上大量NULL会让优化器的选择判断变差。我的原则是能NOT NULL的字段就NOT NULL用默认值表达无意义的状态。比如状态字段默认1备注字段默认空字符串数值字段默认0。默认值本身也有讲究。创建时间统一用DATETIME DEFAULT CURRENT_TIMESTAMP更新时间用ON UPDATE CURRENT_TIMESTAMP这两行能让开发少写一半的赋值代码。字符集这块MySQL里全库统一utf8mb4排序规则统一否则两个表JOIN时如果字符集不一致索引直接失效这是非常隐蔽的性能杀手。现在新项目我已经强制要求所有建表语句必须显式写DEFAULT CHARSETutf8mb4不允许依赖全局配置。3.3 预留字段是我见过最危险的优化有些同事建表时喜欢每个表都加一个varchar(100)和text字段美其名曰预留扩展位以后不用改表。这个习惯我劝你尽早戒掉。所谓预留字段最后一定会变成垃圾场用户ID放进去、活动标记放进去、临时备注放进去同一个字段在不同时间段承载完全不同的业务含义后续既没法建索引也没法写统计SQL数据质量烂到连重建都无处下手。如果业务真的需要灵活扩展正确做法是引入JSON字段并明确这个字段只存放低频、非查询类扩展属性或者直接新增正式字段。扩展字段的诉求很多时候是模型本身没想清楚。我在设计评审时只要看到预留两个字基本上都会打回去重新想。敢于拒绝先加一个字段再说的需求本身就是设计能力的一部分。4. 主键与索引关系型数据库的性能命门4.1 自增主键、雪花ID还是UUID先想清楚主键是给谁用的主键在InnoDB里就是聚簇索引行数据按主键顺序物理存放所以主键的选择直接影响插入性能。自增BIGINT的优势是插入顺序递增B树叶子节点基本只追加不分裂写入吞吐稳定缺点是自增ID容易被外部猜测、跨库合并时会冲突。雪花ID是BIGINT趋势递增带时间信息适合分库分表和多机房写入但实现复杂度高一点。UUID字符串则完全随机作为主键会导致频繁的页分裂和碎片存储体积也更大。方案表现适合场景自增BIGINT插入顺序递增页分裂最少单库单表、写入密集型OLTP雪花BIGINT全局趋势递增带时间信息分库分表、多机房写入UUID字符串完全随机插入乱序存储膨胀只建议做业务流水号不建议做主键我的选择逻辑很简单能用自增就用自增要分库分表就统一用雪花IDUUID只做order_no这类业务标识。别把业务编号和物理主键混为一谈order_no可以在业务上唯一但物理主键应该始终是一个无业务含义的整数让数据库把精力放在数据组织上。4.2 索引设计的核心原则和常见误区索引设计的第一原则是从查询出发而不是从字段出发。很多人喜欢把WHERE条件里的字段都建一遍单列索引结果查询时优化器只能用一个剩下几个索引纯属浪费。联合索引有最左前缀原则(user_id, status, create_time)可以覆盖user_id开头的查询也可以覆盖user_idstatus的查询但单独查status时走不到这个索引。所以联合索引的字段顺序要看哪个字段最常见、哪个字段区分度更高。第二个常见误区是在索引列上做函数运算。WHERE DATE(create_time) 2024-01-01看起来没毛病但create_time上的索引直接失效必须扫全表。正确写法是create_time 2024-01-01 AND create_time 2024-01-02。凡是索引列上套了函数或隐式转换的都等于亲手把索引关掉。第三个误区是覆盖所有查询的万能索引那只是理论上的理想实际要为高频查询设计精细的联合索引为低频查询宁可不建。4.3 一个订单查询场景的完整索引设计具体到订单表最常见的查询是某个用户最近的订单列表带状态筛选按时间倒序分页。我给这个场景设计的联合索引是(user_id, status, create_time)查询条件写成WHERE user_id ? AND status ? ORDER BY create_time DESC LIMIT 20。user_id等值定位到用户status进一步过滤create_time天然满足排序和分页整个过程走索引性能非常稳定。另一个高频查询是按订单号查详情order_no上必须建唯一索引这是最高效的等值查询路径。真正等你做的还有深分页问题LIMIT 1000000, 20这类写法即使是索引执行也要回表跳过100万行。我常用的优化是延迟关联先按索引条件查出主键ID再JOIN回表拿完整行数据或者用游标分页记下上一页最后一个ID性能提升极大。5. 规范化和反规范化什么时候该主动破坏范式5.1 三范式用大白话怎么理解范式是数据库设计的理论基础但很多人一听范式就头大。我用大白话解释一下。第一范式要求每个字段只存一个原子值不能在一个字段里用逗号分隔多个商品。第二范式要求一张表只描述一个实体订单明细表就不能同时塞订单总额因为总额是订单级信息依赖订单ID而不是明细ID。第三范式要求非主键字段之间不要有传递依赖订单明细里放商品分类名称就没有必要因为分类名称是通过商品ID间接确定的。90%以上的业务表设计到第三范式就够了。第三范式的核心好处是避免数据冗余导致的不一致商品价格改了所有引用它的地方只要JOIN一次就能拿到最新值不会出现某些表更新了、某些表没更新的尴尬局面。但范式不是银弹当查询复杂度飙升时就该考虑反规范化了。5.2 反规范化的适用场景与具体实现反规范化最常见的场景是读多写少、查询频繁带出其他实体字段。比如订单列表要展示用户昵称每次JOIN用户表成本不低一个热门用户一屏展示100条订单就要JOIN 100次。这时候在订单表冗余一个user_nickname字段写入订单时从用户服务同步过来查询就直接命中不需要JOIN。这个冗余字段的来源必须单一用户表是唯一权威来源所有同步逻辑都围绕它做。还有一种反规范化不是为性能而是为业务正确性就是快照。兑换订单里必须冗余商品名称和积分价格快照这样商品下架或改价后历史订单依然能还原当时用户看到的信息。这类冗余没有争议设计时直接在注释里标明快照字段来自product表不可被后台修改。我在设计评审时会要求每一个冗余字段必须写明来源表、同步时机和更新策略写不出来就是设计不合格。5.3 反规范化的代价和回退策略反规范化的代价只有一个词一致性。冗余字段一旦存在就必须考虑源数据更新后冗余数据要不要同步、什么时候同步、失败了怎么办。我在订单表冗余用户昵称时踩过一次坑用户改名后订单表里的昵称还是旧值客服那边天天接到投诉。后来用binlog订阅把用户昵称变更异步同步到订单表再加上一个每日对账任务兜底才算把问题压住。所以我的建议是不要为了稍微快点就随意反规范化先确认这个查询的QPS真的高到JOIN扛不住再确认冗余字段的一致性可以接受异步延迟。如果做不到就先保持3NF用覆盖索引和缓存优化查询。反规范化不是随便加字段它是一次需要文档化的架构决策必须有明确的同步机制和回退计划。6. 从需求文档到建表SQL一个积分商城系统的完整设计复盘6.1 需求、约束与目标状态纸上谈兵说得再多不如完整走一遍设计流程。用一个小而全的案例积分商城。需求是用户可以通过签到、消费获取积分积分可以兑换实物商品库存有限兑换成功后生成订单并扣减积分。约束条件包括积分流水不能丢、账户余额不能为负、库存不能超卖、商品改名后历史订单必须保留当时的商品信息。从约束反推设计目标积分余额用单独账户表存最新值积分流水表存每一笔变动明细两者缺一不可库存扣减必须在事务里用条件更新防止超卖兑换订单必须冗余商品名称和积分价格快照。这个案例不复杂但覆盖了业务过程表、数据快照表、并发控制、快照冗余四个核心场景很适合作为设计模板。6.2 实体关系设计与建表SQL实体有用户、积分账户、积分流水、商品、兑换订单。用户和积分账户是一对一账户表加user_id唯一键账户和流水是一对多流水表存account_id订单和商品是多对一订单表冗余商品快照。核心建表SQL如下。-- 用户表数据快照 CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, mobile VARCHAR(20) NOT NULL COMMENT 手机号, nickname VARCHAR(64) NOT NULL COMMENT 昵称, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 2禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; -- 积分账户表余额快照version字段用于乐观锁 CREATE TABLE point_account ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL COMMENT 用户ID, balance INT NOT NULL DEFAULT 0 COMMENT 当前积分余额, version INT NOT NULL DEFAULT 0 COMMENT 乐观锁版本号, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT积分账户表; -- 积分流水表业务过程表只追加不修改 CREATE TABLE point_transaction ( id BIGINT NOT NULL AUTO_INCREMENT, account_id BIGINT NOT NULL COMMENT 积分账户ID, op_type TINYINT NOT NULL COMMENT 1签到 2消费 3兑换, change_amount INT NOT NULL COMMENT 变动积分数正负表示增加或扣减, balance_after INT NOT NULL COMMENT 变动后余额, biz_no VARCHAR(64) NOT NULL COMMENT 关联业务号如订单号, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_biz_no (biz_no), KEY idx_account_create (account_id, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT积分流水表; -- 商品表数据快照库存字段需防止超卖 CREATE TABLE product ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(128) NOT NULL COMMENT 商品名称, point_price INT NOT NULL COMMENT 兑换所需积分, stock INT NOT NULL COMMENT 剩余库存, status TINYINT NOT NULL DEFAULT 1 COMMENT 1上架 2下架, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT积分商品表; -- 兑换订单表业务过程表冗余商品快照字段 CREATE TABLE exchange_order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 用户ID, product_id BIGINT NOT NULL COMMENT 商品ID, product_name VARCHAR(128) NOT NULL COMMENT 商品名称快照, point_price INT NOT NULL COMMENT 兑换积分快照, status TINYINT NOT NULL COMMENT 1创建 2完成 3取消, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_status (user_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT积分兑换订单表;这里有个设计要点必须说明积分流水表和兑换订单表里有biz_no和order_no它们都建了唯一键这个唯一键是整个账务体系防重放的关键。积分流水通过biz_no唯一约束保证不会重复加积分兑换订单通过order_no保证订单不会重复创建。业务上一定要设计好这个幂等号否则并发重试会把数据和账目全部搞乱。6.3 索引与典型查询的对应关系这个模型的索引设计可以直接映射到查询场景。我的积分余额查point_account的uk_user_id唯一索引我的积分明细查idx_account_create因为流水按账户和时间组织我的兑换订单查idx_user_status按订单号查详情查uk_order_no。每个核心查询都有对应索引不会出现索引建了一堆却接不住查询的情况。防超卖的库存扣减是关键我一般会在事务里执行条件更新UPDATE product SET stock stock - 1, update_time NOW() WHERE id ? AND stock 1;受影响行数为0就说明库存不足事务直接回滚兑换订单和积分流水都保留。这条语句用条件判断替代了先查后改避免了两步操作之间的并发窗口。账户余额的扣减同样用乐观锁UPDATE时带上version条件更新成功再把version加一。这套模式在核心资金类场景已经验证过很多次稳定可靠。7. 上线评审清单我每次数据库设计必检的十二个点7.1 结构层面的七个检查点第一是主键。每张表必须有主键优先单列、数值、无业务含义主键类型要和表规模匹配。第二是字段类型最小化。能用TINYINT不用INT能用INT不用BIGINT字符串长度按实际最大值收紧不要VARCHAR字段一上来就配255、5000。第三是金额、日期、状态三类字段必须用正规类型任何FLOAT金额、VARCHAR日期、自由字符串状态都直接打回。第四是NULL和默认值。所有查询条件字段尽量NOT NULL用业务语义明确的默认值表达空。第五是字符集和排序规则统一全库一致避免JOIN时隐式字符集转换。第六是唯一约束业务要求唯一的字段必须有唯一索引比如手机号、订单号、渠道流水号。第七是关联字段必须有索引物理外键可以不用但JOIN条件的字段没有索引就是定时炸弹。7.2 性能与生命周期层面的五个检查点第八是用EXPLAIN验证每个核心查询的执行计划确保能走到索引而不是ALL全表扫描。第九是清理冗余索引联合索引能覆盖的单列索引就删掉长期没有被优化器选中的索引也要清理。第十是大字段单独拆表或用EXPLAIN确认不会拖累高频查询TEXT字段别放在最核心的业务表里。第十一是删除策略要明确。用逻辑删除就要保证所有查询统一加条件用物理删除就要设计好归档机制没有归档通道的物理删除会让历史数据变成黑洞。第十二是敏感字段必须加密存储并在日志中脱敏手机号、身份证、地址这些字段不能明文进数据库更不能被慢查询日志打印出来。这十二个检查点每次评审照着过一遍能拦下大部分线上故障。7.3 清单之外的三条个人心得我每次做完整表设计评审最后还会额外提醒自己三句话。第一句别把数据库设计看成一次性评审每次加字段、改索引都是一次迷你评审DDL变更单同样要走这套检查逻辑。第二句表和字段的注释必须写清楚COMMENT里写明业务含义和冗余来源这就是给三个月后的自己留的地图每次偷懒不写注释之后排查问题都要加倍还回去。第三句一张表只干一件事如果有人和你说这张表什么都能查你要立刻警惕那通常是模型混杂了多个实体的信号。这套思路用下来我已经很少被慢查询在半夜喊醒了。数据库设计没有银弹但把每一步都想清楚、把每一个字段的类型和索引都问一遍为什么绝大部分坑完全可以在第一天就避开。