简介面向Oracle数据库建设提供的一份可落地设计规范适合系统架构师、开发人员及数据库管理员在项目立项、开发或重构阶段参照使用。文档系统梳理了数据库对象长度、数据完整性、规范化设计与性能权衡、字段类型定义与使用等策略并覆盖数据库、表空间、表、字段、视图、序列、存储过程、函数、索引、约束等对象的完整命名规则同时给出金额、税率、姓名、地址等常用字段的类型与长度建议以及OLTP与OLAP分开设计等关键思路便于团队统一建模口径、减少数据冗余并提升系统可维护性与运行效率。资源为1个doc格式文档压缩包大小296KB内容结构清晰可直接查阅、复用或作为评审检查单目前已有267人学习下载适合需要快速建立或完善数据库设计规范的中大型项目团队参考。1. 为什么说数据库设计规范是一份“技术债台账”很多团队把数据库设计规范当成入职培训的PPT看完就忘真正建表时照样给你搞出一个user_info_2021_backup和OrderDetail混用的库。做后端和DBA这行久了会明白数据库设计规范不是约束开发者的条条框框而是技术债的台账。你每违反一条约定就等于往台账上记一笔需要未来用双倍工时偿还的利息比如字段类型选错导致索引失效或者字符集不统一造成关联查询乱码。这篇文章我按常见的生产环境做法把数据库设计规范从理论拆到可执行的DDL模板和评审清单让新手能照着建表熟手能拿来做Review。2. 建库建表之前先定命名、类型与字符集的底层约定2.1 库、表、字段的命名规范从“看得懂”到“查得快”命名规范是数据库设计规范里最容易被忽略却最影响协作的部分。我一般推荐使用小写字母、数字加下划线的方式禁止驼峰和大小写混用。原因很简单MySQL在Linux下对表名区分大小写但Windows下不区分跨环境迁移时驼峰命名极易引发“表找不到”的诡异错误。库名建议用业务域缩写比如order、crm表名用业务实体名加业务域前缀例如crm_customer避免不同业务模块出现同名的user表。字段命名上主键统一叫id业务自然键叫xxx_no比如order_no关联外键叫business_id这样带业务含义的名字。布尔类型建议用is_xxx或has_xxx状态字段用status时间字段统一create_time、update_time。这样做的好处是哪怕一个新人接手也能从字段名推断出它的语义不会出现flag、type这种十个表十个含义的烂命名。2.2 字段类型选择用最小的可表示空间换性能类型选择的核心原则是“够用就行留一点点余地”。常见做法是整型用INT如果存储的主键或雪花ID超过2^31就用BIGINT金额使用DECIMAL(10,2)或更大精度禁止使用FLOAT和DOUBLE因为浮点数在比较运算上会出错。字符串方面短字符串用CHAR变长用VARCHAR但需要注意VARCHAR长度不是字符数而是字符数乘以字符集的最大字节数utf8mb4下每个字符最多4字节所以VARCHAR(100)实际上最多占用400字节。枚举和布尔类型的选择也值得单独说。MySQL的ENUM类型看起来很方便但后续要扩展枚举值时就只能改表结构而且引擎对ENUM的存储做了压缩一旦排序或比较规则变化容易踩坑。我建议状态字段用TINYINT并在代码层定义常量枚举的语义放代码里数据库里只存数字。时间类型用DATETIME而不是TIMESTAMP虽然TIMESTAMP只有4字节但它支持的年份范围到2038年就有溢出风险而且会受时区影响DATETIME虽然占用8字节但存储的是字面时间不随会话时区改变排错时更直观。2.3 字符集与排序规则选错utf8mb4的坑字符集是数据库设计规范里最常被低估的一项。MySQL 8.0默认字符集是utf8mb4排序规则是utf8mb4_0900_ai_ci但很多老库还在用utf8注意utf8在MySQL里其实是utf8mb3最多只能存3字节的字符像 Emoji 表情和部分生僻字根本存不进去。所以新库一律使用utf8mb4排序规则建议选utf8mb4_0900_ai_ciMySQL 8或utf8mb4_general_ciMySQL 5.7不要用utf8mb4_bin除非你明确需要区分大小写。字符集不一致导致的问题非常隐蔽。比如表A是utf8mb4表B是utf8两张表做关联查询时MySQL会尝试隐式转换字符集导致索引失效。排查方法是用SHOW CREATE TABLE查看每张表的字符集或者查询information_schema里TABLES表的TABLE_COLLATION字段。我一般会在初始化数据库时强制所有库、表、字段统一字符集并把这写进规范文档。提示修改已有表的字符集要预估锁表时间。ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4会重写全表数据量大时建议用pt-online-schema-change或计划停机窗口执行。3. 用可执行的DDL模板落地数据库设计规范3.1 一份兼容MySQL 8的建表DDL模板直接把规范变成模板是最有效的落地方式。下面这份DDL模板是我平时建表时使用的基准它覆盖了主键、业务字段、审计字段、索引和表注释的所有约定。CREATE TABLE crm_customer ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, customer_no varchar(32) NOT NULL COMMENT 客户编号,业务自然键, name varchar(64) NOT NULL COMMENT 客户名称, status tinyint NOT NULL DEFAULT 1 COMMENT 状态:1-正常,2-冻结,3-注销, phone_mobile varchar(20) DEFAULT COMMENT 手机号, email varchar(128) DEFAULT COMMENT 邮箱, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, deleted tinyint NOT NULL DEFAULT 0 COMMENT 逻辑删除:0-否,1-是, PRIMARY KEY (id), UNIQUE KEY uk_customer_no (customer_no), KEY idx_name (name), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT客户表;这段DDL里有几个关键设计主键使用bigint unsigned而不是int避免未来数据量超出有符号整型上限业务自然键customer_no加了唯一索引保证业务上同一客户不会被重复插入逻辑删除字段deleted使用tinyint而不是直接物理删除方便审计和恢复。注意唯一索引uk_customer_no和逻辑删除字段之间是有冲突的如果同一个客户被删除两次第二次插入会因为唯一索引冲突而失败。常见解法是把deleted字段改成0表示未删除删除时改成该行的主键ID即deleted id这样唯一索引(customer_no, deleted)就能容纳多次删除的记录。3.2 索引设计规范不是越多越好是“够用且可解释”索引设计是数据库设计规范里最能体现功力的部分。我见过最极端的表有20多个索引每个查询都想照顾到结果写入时因为维护索引开销极大查询优化器反而无法选出最佳执行计划。规范要求是每个表索引数量建议控制在5个以内每个索引的字段数控制在3个以内覆盖查询的高频条件。索引设计的基本原则是“等值在前排序在后”。对于WHERE a? AND b? ORDER BY c这种查询联合索引应该设计为(a,b,c)而不是将c放在前面。如果有范围查询比如b ?那么b之后的字段无法用于排序或索引覆盖这点需要结合EXPLAIN反复验证。下面是一个反例和正例-- 反例:索引顺序与查询条件不一致 SELECT * FROM crm_customer WHERE status 1 AND create_time 2024-01-01 ORDER BY name; -- 如果只建了 (status, name) 索引,create_time 的范围条件会让 name 无法走索引排序 -- 正例:等值字段在前,范围字段居中,排序字段最后 CREATE INDEX idx_status_time_name ON crm_customer(status, create_time, name);我一般要求开发同学在提交建表SQL时必须附上EXPLAIN输出并且重点看type字段是否出现range或ref而不是ALL看Extra字段是否出现Using filesort。如果出现Using filesort说明索引没有覆盖排序需要调整索引顺序或增加字段。联合索引还有一个容易被忽略的“左前缀”原则如果查询条件只有create_time而没有status上面的联合索引就无法生效因此还要评估单列索引是否存在不可替代性。3.3 主键、外键与唯一约束什么时候不该用外键外键这个设计规范在大多数互联网业务里是被禁止使用的。不是外键本身有问题而是它会导致两个问题一是高并发写入时外键检查和行锁会放大锁竞争二是分库分表后外键约束根本没法跨库执行。所以常见做法是在应用层保证引用完整性数据库里只保留普通索引来加速关联查询。逻辑上有关联的表比如订单表和客户表库表设计只加customer_id字段并建KEY不建FOREIGN KEY。唯一约束需要特别小心NULL值。MySQL中唯一索引允许多个NULL值比如email字段如果没有NOT NULL DEFAULT 两个用户都可以插入NULL的邮箱而不会触发唯一冲突。因此业务要求“邮箱唯一”时字段必须定义为NOT NULL DEFAULT 空字符串只会被一个用户占用后续插入会撞唯一键。这个细节排查起来相当费劲很多人查了半天代码也没想通为什么数据重复了。4. 数据库设计规范在评审与变更中的落地4.1 设计评审检查表把规范变成Review清单有了规范文档和DDL模板还不够必须把它变成代码评审里卡得住的检查项。我常用的评审表分为四类命名合规、类型合规、索引合理、容量预估。下面这个表格可以在团队里直接复用检查项规范要求违规示例表名命名小写下划线带业务域前缀UserInfo、user_info_2024主键类型bigint unsigned或bigintint主键金额类型DECIMAL或INT(分)FLOAT、DOUBLE逻辑删除deletedtinyint默认0物理删除或status-1兼作删除时间字段DATETIME不允许字符串varchar(20)存时间每表索引数≤5个不允许冗余联合索引单表10个索引字符集utf8mb4排序规则统一utf8、latin1大字段TEXT/BLOB不得直接放在查询频繁的表把日志内容放业务表评审时不仅要看建表语句还要看把数据量放大100倍之后是否还能跑。比如一个VARCHAR(2000)的字段在InnoDB里可能会被压缩到溢出页如果频繁查询该字段性能就会下降。这时候应该把大字段拆到附属表或者用JSON类型存储但避免用JSON字段做条件过滤。4.2 用SQL查询元数据校验规范执行情况与其靠人工Review两眼一摸黑不如直接查元数据把违规项捞出来。MySQL的information_schema能拿到所有库表的元数据。下面这段SQL可以找出所有不是utf8mb4的表SELECT table_schema AS 库名, table_name AS 表名, table_collation AS 排序规则 FROM information_schema.tables WHERE table_schema your_db_name AND table_collation NOT LIKE utf8mb4%;再比如排查表中是否存在没有主键的表可以直接这样查SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON t.table_schema c.table_schema AND t.table_name c.table_name AND c.constraint_type PRIMARY KEY WHERE t.table_schema your_db_name AND c.constraint_name IS NULL;这两条SQL脚本建议放在每周巡检任务里跑出来的结果直接粘贴到技术周报推动责任方修正。还需要检查的一个隐藏指标是自增主键的饱和度如果表的主键是int unsigned最大值到42.9亿用下面的SQL可以查看当前自增值距离上限还差多少SELECT table_name, auto_increment, (auto_increment / 4294967295) * 100 AS 容量使用百分比 FROM information_schema.tables WHERE table_schema your_db_name AND auto_increment IS NOT NULL ORDER BY auto_increment DESC;4.3 拆分与归档当表数据量突破边界数据库设计规范不仅要管“建表时”还要管“表变大了以后”。比如业务表超过2000万行或者单表容量超过20GB时就该考虑归档和拆分。归档的常规做法是定时把status3的历史数据迁移到历史表或冷存储比如crm_customer_archive。拆分则分垂直和水平两种垂直拆分是把大字段和不常查的字段拆到另一张表水平拆分一般按customer_no%16分16个库或表。这里有个规范要求任何拆分都必须保留原来的查询路由口径。如果你按用户ID拆分那查询就必须带上用户ID否则全分片扫描。所以拆分前要明确定位“必带条件的查询”和“全局查询”全局查询走ES或宽表层而不是直接打全分片。我遇到过因为拆分后漏传分片键导致跨库查询慢到30秒的案例最终不得不对应用层加一道参数校验强制缺少分片键的请求直接报错。5. 分库分表后数据库设计规范要怎么变分库分表之后数据库设计规范里有一部分会失效比如自增主键会重复UNIQUE KEY无法跨库生效。这时需要把主键生成方式切换成雪花ID或号段模式同时将唯一约束下放到应用层。表名也会多出_0000这种后缀原来的表名规范列就必须补充通配规则和路由键定义。我一般在分片方案里额外加两条硬性要求一是每张分片表的字段结构必须完全一致用同一套DDL脚本发布二是分片键必须是查询的必选条件应用层的ORM或DAO里通过注解或包装类强制注入。数据迁移操作用pt-archiver做分批删除是最稳的方案。比如要清理半年之前的日志数据按主键ID分批删除避免一次性锁大量行pt-archiver \ --source h127.0.0.1,P3306,Dlog_db,taccess_log \ --where create_time 2024-01-01 \ --limit 1000 \ --bulk-delete \ --commit-each这个命令的参数含义是--limit 1000指定每批处理1000行--bulk-delete用批量删除代替逐行删除--commit-each表示每批都提交事务。注意执行环境需要安装Percona Toolkit执行前建议先加--dry-run参数预览会删除的行数避免误删。跑完后再对目标表执行OPTIMIZE TABLE回收空间但此操作会锁表生产环境建议使用pt-online-schema-change或放在维护窗口执行。本文还有配套的精品资源点击获取
数据库设计规范：从命名、类型到索引的落地实践
简介面向Oracle数据库建设提供的一份可落地设计规范适合系统架构师、开发人员及数据库管理员在项目立项、开发或重构阶段参照使用。文档系统梳理了数据库对象长度、数据完整性、规范化设计与性能权衡、字段类型定义与使用等策略并覆盖数据库、表空间、表、字段、视图、序列、存储过程、函数、索引、约束等对象的完整命名规则同时给出金额、税率、姓名、地址等常用字段的类型与长度建议以及OLTP与OLAP分开设计等关键思路便于团队统一建模口径、减少数据冗余并提升系统可维护性与运行效率。资源为1个doc格式文档压缩包大小296KB内容结构清晰可直接查阅、复用或作为评审检查单目前已有267人学习下载适合需要快速建立或完善数据库设计规范的中大型项目团队参考。1. 为什么说数据库设计规范是一份“技术债台账”很多团队把数据库设计规范当成入职培训的PPT看完就忘真正建表时照样给你搞出一个user_info_2021_backup和OrderDetail混用的库。做后端和DBA这行久了会明白数据库设计规范不是约束开发者的条条框框而是技术债的台账。你每违反一条约定就等于往台账上记一笔需要未来用双倍工时偿还的利息比如字段类型选错导致索引失效或者字符集不统一造成关联查询乱码。这篇文章我按常见的生产环境做法把数据库设计规范从理论拆到可执行的DDL模板和评审清单让新手能照着建表熟手能拿来做Review。2. 建库建表之前先定命名、类型与字符集的底层约定2.1 库、表、字段的命名规范从“看得懂”到“查得快”命名规范是数据库设计规范里最容易被忽略却最影响协作的部分。我一般推荐使用小写字母、数字加下划线的方式禁止驼峰和大小写混用。原因很简单MySQL在Linux下对表名区分大小写但Windows下不区分跨环境迁移时驼峰命名极易引发“表找不到”的诡异错误。库名建议用业务域缩写比如order、crm表名用业务实体名加业务域前缀例如crm_customer避免不同业务模块出现同名的user表。字段命名上主键统一叫id业务自然键叫xxx_no比如order_no关联外键叫business_id这样带业务含义的名字。布尔类型建议用is_xxx或has_xxx状态字段用status时间字段统一create_time、update_time。这样做的好处是哪怕一个新人接手也能从字段名推断出它的语义不会出现flag、type这种十个表十个含义的烂命名。2.2 字段类型选择用最小的可表示空间换性能类型选择的核心原则是“够用就行留一点点余地”。常见做法是整型用INT如果存储的主键或雪花ID超过2^31就用BIGINT金额使用DECIMAL(10,2)或更大精度禁止使用FLOAT和DOUBLE因为浮点数在比较运算上会出错。字符串方面短字符串用CHAR变长用VARCHAR但需要注意VARCHAR长度不是字符数而是字符数乘以字符集的最大字节数utf8mb4下每个字符最多4字节所以VARCHAR(100)实际上最多占用400字节。枚举和布尔类型的选择也值得单独说。MySQL的ENUM类型看起来很方便但后续要扩展枚举值时就只能改表结构而且引擎对ENUM的存储做了压缩一旦排序或比较规则变化容易踩坑。我建议状态字段用TINYINT并在代码层定义常量枚举的语义放代码里数据库里只存数字。时间类型用DATETIME而不是TIMESTAMP虽然TIMESTAMP只有4字节但它支持的年份范围到2038年就有溢出风险而且会受时区影响DATETIME虽然占用8字节但存储的是字面时间不随会话时区改变排错时更直观。2.3 字符集与排序规则选错utf8mb4的坑字符集是数据库设计规范里最常被低估的一项。MySQL 8.0默认字符集是utf8mb4排序规则是utf8mb4_0900_ai_ci但很多老库还在用utf8注意utf8在MySQL里其实是utf8mb3最多只能存3字节的字符像 Emoji 表情和部分生僻字根本存不进去。所以新库一律使用utf8mb4排序规则建议选utf8mb4_0900_ai_ciMySQL 8或utf8mb4_general_ciMySQL 5.7不要用utf8mb4_bin除非你明确需要区分大小写。字符集不一致导致的问题非常隐蔽。比如表A是utf8mb4表B是utf8两张表做关联查询时MySQL会尝试隐式转换字符集导致索引失效。排查方法是用SHOW CREATE TABLE查看每张表的字符集或者查询information_schema里TABLES表的TABLE_COLLATION字段。我一般会在初始化数据库时强制所有库、表、字段统一字符集并把这写进规范文档。提示修改已有表的字符集要预估锁表时间。ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4会重写全表数据量大时建议用pt-online-schema-change或计划停机窗口执行。3. 用可执行的DDL模板落地数据库设计规范3.1 一份兼容MySQL 8的建表DDL模板直接把规范变成模板是最有效的落地方式。下面这份DDL模板是我平时建表时使用的基准它覆盖了主键、业务字段、审计字段、索引和表注释的所有约定。CREATE TABLE crm_customer ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, customer_no varchar(32) NOT NULL COMMENT 客户编号,业务自然键, name varchar(64) NOT NULL COMMENT 客户名称, status tinyint NOT NULL DEFAULT 1 COMMENT 状态:1-正常,2-冻结,3-注销, phone_mobile varchar(20) DEFAULT COMMENT 手机号, email varchar(128) DEFAULT COMMENT 邮箱, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, deleted tinyint NOT NULL DEFAULT 0 COMMENT 逻辑删除:0-否,1-是, PRIMARY KEY (id), UNIQUE KEY uk_customer_no (customer_no), KEY idx_name (name), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT客户表;这段DDL里有几个关键设计主键使用bigint unsigned而不是int避免未来数据量超出有符号整型上限业务自然键customer_no加了唯一索引保证业务上同一客户不会被重复插入逻辑删除字段deleted使用tinyint而不是直接物理删除方便审计和恢复。注意唯一索引uk_customer_no和逻辑删除字段之间是有冲突的如果同一个客户被删除两次第二次插入会因为唯一索引冲突而失败。常见解法是把deleted字段改成0表示未删除删除时改成该行的主键ID即deleted id这样唯一索引(customer_no, deleted)就能容纳多次删除的记录。3.2 索引设计规范不是越多越好是“够用且可解释”索引设计是数据库设计规范里最能体现功力的部分。我见过最极端的表有20多个索引每个查询都想照顾到结果写入时因为维护索引开销极大查询优化器反而无法选出最佳执行计划。规范要求是每个表索引数量建议控制在5个以内每个索引的字段数控制在3个以内覆盖查询的高频条件。索引设计的基本原则是“等值在前排序在后”。对于WHERE a? AND b? ORDER BY c这种查询联合索引应该设计为(a,b,c)而不是将c放在前面。如果有范围查询比如b ?那么b之后的字段无法用于排序或索引覆盖这点需要结合EXPLAIN反复验证。下面是一个反例和正例-- 反例:索引顺序与查询条件不一致 SELECT * FROM crm_customer WHERE status 1 AND create_time 2024-01-01 ORDER BY name; -- 如果只建了 (status, name) 索引,create_time 的范围条件会让 name 无法走索引排序 -- 正例:等值字段在前,范围字段居中,排序字段最后 CREATE INDEX idx_status_time_name ON crm_customer(status, create_time, name);我一般要求开发同学在提交建表SQL时必须附上EXPLAIN输出并且重点看type字段是否出现range或ref而不是ALL看Extra字段是否出现Using filesort。如果出现Using filesort说明索引没有覆盖排序需要调整索引顺序或增加字段。联合索引还有一个容易被忽略的“左前缀”原则如果查询条件只有create_time而没有status上面的联合索引就无法生效因此还要评估单列索引是否存在不可替代性。3.3 主键、外键与唯一约束什么时候不该用外键外键这个设计规范在大多数互联网业务里是被禁止使用的。不是外键本身有问题而是它会导致两个问题一是高并发写入时外键检查和行锁会放大锁竞争二是分库分表后外键约束根本没法跨库执行。所以常见做法是在应用层保证引用完整性数据库里只保留普通索引来加速关联查询。逻辑上有关联的表比如订单表和客户表库表设计只加customer_id字段并建KEY不建FOREIGN KEY。唯一约束需要特别小心NULL值。MySQL中唯一索引允许多个NULL值比如email字段如果没有NOT NULL DEFAULT 两个用户都可以插入NULL的邮箱而不会触发唯一冲突。因此业务要求“邮箱唯一”时字段必须定义为NOT NULL DEFAULT 空字符串只会被一个用户占用后续插入会撞唯一键。这个细节排查起来相当费劲很多人查了半天代码也没想通为什么数据重复了。4. 数据库设计规范在评审与变更中的落地4.1 设计评审检查表把规范变成Review清单有了规范文档和DDL模板还不够必须把它变成代码评审里卡得住的检查项。我常用的评审表分为四类命名合规、类型合规、索引合理、容量预估。下面这个表格可以在团队里直接复用检查项规范要求违规示例表名命名小写下划线带业务域前缀UserInfo、user_info_2024主键类型bigint unsigned或bigintint主键金额类型DECIMAL或INT(分)FLOAT、DOUBLE逻辑删除deletedtinyint默认0物理删除或status-1兼作删除时间字段DATETIME不允许字符串varchar(20)存时间每表索引数≤5个不允许冗余联合索引单表10个索引字符集utf8mb4排序规则统一utf8、latin1大字段TEXT/BLOB不得直接放在查询频繁的表把日志内容放业务表评审时不仅要看建表语句还要看把数据量放大100倍之后是否还能跑。比如一个VARCHAR(2000)的字段在InnoDB里可能会被压缩到溢出页如果频繁查询该字段性能就会下降。这时候应该把大字段拆到附属表或者用JSON类型存储但避免用JSON字段做条件过滤。4.2 用SQL查询元数据校验规范执行情况与其靠人工Review两眼一摸黑不如直接查元数据把违规项捞出来。MySQL的information_schema能拿到所有库表的元数据。下面这段SQL可以找出所有不是utf8mb4的表SELECT table_schema AS 库名, table_name AS 表名, table_collation AS 排序规则 FROM information_schema.tables WHERE table_schema your_db_name AND table_collation NOT LIKE utf8mb4%;再比如排查表中是否存在没有主键的表可以直接这样查SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON t.table_schema c.table_schema AND t.table_name c.table_name AND c.constraint_type PRIMARY KEY WHERE t.table_schema your_db_name AND c.constraint_name IS NULL;这两条SQL脚本建议放在每周巡检任务里跑出来的结果直接粘贴到技术周报推动责任方修正。还需要检查的一个隐藏指标是自增主键的饱和度如果表的主键是int unsigned最大值到42.9亿用下面的SQL可以查看当前自增值距离上限还差多少SELECT table_name, auto_increment, (auto_increment / 4294967295) * 100 AS 容量使用百分比 FROM information_schema.tables WHERE table_schema your_db_name AND auto_increment IS NOT NULL ORDER BY auto_increment DESC;4.3 拆分与归档当表数据量突破边界数据库设计规范不仅要管“建表时”还要管“表变大了以后”。比如业务表超过2000万行或者单表容量超过20GB时就该考虑归档和拆分。归档的常规做法是定时把status3的历史数据迁移到历史表或冷存储比如crm_customer_archive。拆分则分垂直和水平两种垂直拆分是把大字段和不常查的字段拆到另一张表水平拆分一般按customer_no%16分16个库或表。这里有个规范要求任何拆分都必须保留原来的查询路由口径。如果你按用户ID拆分那查询就必须带上用户ID否则全分片扫描。所以拆分前要明确定位“必带条件的查询”和“全局查询”全局查询走ES或宽表层而不是直接打全分片。我遇到过因为拆分后漏传分片键导致跨库查询慢到30秒的案例最终不得不对应用层加一道参数校验强制缺少分片键的请求直接报错。5. 分库分表后数据库设计规范要怎么变分库分表之后数据库设计规范里有一部分会失效比如自增主键会重复UNIQUE KEY无法跨库生效。这时需要把主键生成方式切换成雪花ID或号段模式同时将唯一约束下放到应用层。表名也会多出_0000这种后缀原来的表名规范列就必须补充通配规则和路由键定义。我一般在分片方案里额外加两条硬性要求一是每张分片表的字段结构必须完全一致用同一套DDL脚本发布二是分片键必须是查询的必选条件应用层的ORM或DAO里通过注解或包装类强制注入。数据迁移操作用pt-archiver做分批删除是最稳的方案。比如要清理半年之前的日志数据按主键ID分批删除避免一次性锁大量行pt-archiver \ --source h127.0.0.1,P3306,Dlog_db,taccess_log \ --where create_time 2024-01-01 \ --limit 1000 \ --bulk-delete \ --commit-each这个命令的参数含义是--limit 1000指定每批处理1000行--bulk-delete用批量删除代替逐行删除--commit-each表示每批都提交事务。注意执行环境需要安装Percona Toolkit执行前建议先加--dry-run参数预览会删除的行数避免误删。跑完后再对目标表执行OPTIMIZE TABLE回收空间但此操作会锁表生产环境建议使用pt-online-schema-change或放在维护窗口执行。本文还有配套的精品资源点击获取
