别把规范当枷锁、当做工具就好了

大家好，我的皇子。

前段时间有小伙伴参加面试遇到一个问题：“如何设计支持千万级的大表”，这问题，听起来有点讽刺和刺激。

讽刺的是，目前互联网大厂存在多类应用场景的需求下，经过多年技术、架构的演进，很多的技术方案在内部已经沉淀好可以直接复用，并且挂在他们云上面向企业收费。所以这类的企业，不管是新项目、新业务的开展，无需过多担心如何去支持千万级的大表，很少会说等到千万级大表出现了，再想办法去优化，去做数据迁移、数据路由等的。

那么刺激的是，这种问题会出现快速发展起来的中小企业里面和面试官的问题清单里。

国庆节制定一些游玩计划，时间精力有限，同时祝大家国庆节快乐！所以今天主要分享的是设计支持千万级的大表，需要的数据库设计规范。

听到规范，有些技术人是不喜欢太多的条条框框的，觉得是个枷锁，所以有必要解释下做规范的重要性。

技术日新月异，团队人员技术水平参差不齐，习惯也不尽相同，在有基本共识后，防止出现“歧视”。
规范一旦定下来，大家就不用每次设计数据库/表结构的时候都纠结半天了，直接根据团队的规范直接套用，省时省力。
规范能帮助我们提前规避一些常见问题，比如索引没建好，数据冗余严重，SQL语句稀奇古怪等情况。

规范的本质不只是能减少设计不合理出现性能瓶颈，更主要的上减少出现潜在的一些问题。

以下规范内容取自“阿里巴巴 MySQL 规范”（黄山版），发布时间为2022.2.3，开源。

所有规范会按照【⾼危】、【强制】、【建议】三个级别进⾏标注，遵守优先级从⾼到低。

对于不满足【高危】和【强制】两个级别的设计，DBA会强制打回要求修改。

一）建库规范

1、【强制】库的名称必须控制在32个字符以内，相关模块的表名与表名之间尽量体现join的关系。

举例：用户表和用户登录表

正确：user、user_login

错误：user、login

2、【强制】库的名称格式：业务系统名称_⼦系统名，同⼀模块使⽤的表名尽量使⽤统⼀前缀。

举例：业务系统是用户中心user_center；子系统名有：auth（认证模块）

正确：认证模块的数据库：user_center_auth

错误：auth、user_auth

3、【强制】⼀般分库名称命名格式是：库通配名_编号。

举例：用户登陆数据库的编号以年月进行分库

正确：user_login_log_2410、user_login_log_2411

错误：user_login_log_01、user_login_log_user_login_log_24

二）建表规范

1、【强制】表和列的名称必须控制在32个字符以内，表名只能使⽤字母、数字和下划线，⼀律⼩写。

举例：表：用户中心认证模块的认证表、字段：用户认证来源

正确：用户中心认证模块的认证表auth_user、用户认证来源source

错误：AuthUser、authUser、Auth_user、auth-user、Source

2、【强制】表名要求模块名强相关。

举例：设计表前缀。

1）用户中心的认证模块包含的用户表、登陆记录表、验证码表，统一以auth作为表前缀。

用户表：auth_users
登录记录表：auth_login_records
验证码表：auth_verifications

2）用户中心的用户资料模块包含的用户资料表、用户地址表、用户头像表，统一以profile作为表前缀。

用户资料表：profile_user_info
用户地址表：profile_user_addresses
用户头像表：profile_user_avatars

3、【强制】创建表时必须显式指定表存储引擎类型，如⽆特殊需求，⼀律为InnoDB。

当需要使⽤除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核才能在⽣产环境中使⽤。

因为Innodb表⽀持事务、⾏锁、宕机恢复、MVCC等关系型数据库重要特性，为业界使⽤最多的MySQL存储引擎。⽽这是其他⼤多数存储引擎不具备的，因此⾸推InnoDB。

4、【强制】建表必须有注释（comment）。

表的注释可以帮助我们开发人员更好快速了解表的作用、理解业务逻辑和团队协作。

特别是对于复杂的业务系统，表和字段的注释可以提供宝贵的上下文信息，帮助开发人员在进行代码编写时能写出更严谨的逻辑判断。

5、【强制】表达是与否概念的字段，必须使⽤ is_xxx的⽅式命名。数据类型是 unsigned tinyint

任何字段如果为⾮负数，必须是 unsigned（ 1表⽰是，0表⽰否）。

举例：是否删除

正确：字段名 is_deleted，1 表⽰删除，0 表⽰未删除。

错误：have_deleted、not_deleted

6、【强制】表名不使⽤复数名词。

表名应该仅仅表⽰表⾥⾯的实体内容，不应该表⽰实体数量，对应于 DO类名也是单数。

在PHP最知名的框架之一：Laravel 框架，它在生成模型和迁移文件时，默认会使用复数形式的表名。

这是因为 Laravel 遵循了一些约定，以简化开发过程。如：模型类 User 对应的表名是 users

7、【强制】禁⽤保留字，如 desc、range、match、delayed、type等。

请参考 MySQL官⽅保留字。

8、【强制】⼩数类型为 decimal，禁⽌使⽤ float和 double。

float和 double在存储的时候，存在精度损失的问题，很可能在值的⽐较时，得到不正确的结果。

如果存储的数据范围超过decimal的范围，建议将数据拆成整数和⼩数分开存储。

9、【强制】如果存储的字符串长度⼏乎相等，使⽤ char定长字符串类型。

如果字符串长度几乎相等使⽤ char定长字符串类型；如果内容长度差异较大，使用varchar。

varchar是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度⼤于此值，定义字段类型为text，独⽴出来⼀张表，⽤主键来对应，避免影响其它字段索引效率。

手机号无论是否带区号，长度相对固定。例如，中国大陆的手机号通常是11位数字，加上区号（如+86）最多也不会超过15位。因此，使用 CHAR 类型更为合适。
订单备注的长度变化较大，有的可能只有几个字，有的可能长达几百字。因此，使用 VARCHAR 类型更为合适。

三）建索引规范

1、【强制】主键索引名为 pk_字段名；唯⼀索引名为 uk_字段名；普通索引名则为 idx_字段名。

说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index的简称。

2、【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁⽌被更新。

3、【强制】业务上具有唯⼀特性的字段，即使是多个字段的组合，也必须建成唯⼀索引。

不要以为唯⼀索引影响了insert速度，这个速度损耗可以忽略，但提⾼查找速度是明显的。
另外，即使在应⽤层做了⾮常完善的校验控制，只要没有唯⼀索引，根据墨菲定律，必然有脏数据产⽣。

4、【强制】超过三个表禁⽌ join。需要 join的字段，数据类型必须绝对⼀致；多表关联查询时，保证被关联的字段需要有索引。

即使双表 join也要注意表索引、SQL性能。

MySQL JOIN ⽀持 NLJ（Nested Loop Join）和 NHJ（Nested Hash Join）两种⽅式。

对于 OLTP 业务，放⼼⼤胆使⽤ JOIN，但⼀定要做好索引的设计和索引覆盖的考虑（不考虑分布式数据库场景）。
对于 OLAP 业务，MySQL 8.0 版本开始，⽀持 Hash Join，对于⼤数据量的关联，性能提升⾮常多，可以在不超过 10T 的数仓场景中考虑使⽤，超过 10T数据量，请⼀定使⽤⼤数据产品，如 Hive、Spark、麒麟等产品。

OLTP（Online Transaction Processing，联机事务处理）：OLTP系统主要用于处理日常的事务操作，如插入、更新和删除等。这些操作通常是短小且频繁的，强调实时性和数据的一致性。

OLAP（Online Analytical Processing，联机分析处理）：OLAP系统主要用于支持复杂的分析操作，侧重于多维数据分析和数据挖掘。这些操作通常是批量的、复杂的查询，强调数据的聚合和分析能力。

5、【强制】在 varchar字段上建⽴索引时，必须指定索引长度，没必要对全字段建⽴索引，根据实际⽂本区分度决定索引长度可。

索引的长度与区分度是⼀对⽭盾体，⼀般对字符串类型数据，长度为 20的索引，区分度会⾼达 90%以上，可以使⽤ count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。

6、【强制】页⾯搜索严禁左模糊或者全模糊，如果需要请⾛搜索引擎来解决。

说明：索引⽂件具有 B-Tree的最左前缀匹配特性，如果左边的值未确定，那么⽆法使⽤此索引，即索引失效。

⚠️注意：创建索引时避免有如下极端误解：

宁滥勿缺。认为⼀个查询就需要建⼀个索引。

宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。

MySQL 是索引组织表，表中的数据以 B+ 树索引结构，根据主键逻辑排序，由于 B+ 树索引的特点是树的⾼度为 3~4 层，所以从数⼗亿的记录中，通过主键查询⼀条记录只需要 3、4 次 I/O，当前的消费级 SSD 存储设备4KB每秒⾄少能完成 50000 次的 I/O 查询，不要担⼼通过索引查询⼀条或⼏条记录的性能，企业级SSD存储设备4KB每秒百万次查询并不难。

不要因为数据量⼤，使⽤分区表，MySQL 是索引组织表，数据量再⼤，定位记录也只需要3、4 次 I/O。可以考虑分区表唯⼀的应⽤场景是：需要定期清理历史流⽔类数据。但如果业务可以按⽉、按天做分表，那么当前 MySQL 8.0 版本，分区表也不推荐使用。

四）分库分表分区规范

1、【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的⾸列。

2、【强制】采⽤分库/分表/分区策略的，库/表/区的数量不能超过1024

3、【强制】上线前RD或者DBA必须指定分区表的创建、清理策略。

4、【强制】访问分区表的SQL必须包含分区键。

5、【强制】对于分区表执⾏alter table操作，必须在业务低峰期执⾏。

五）其他【建议】

1、建表时关于主键

1）强制要求主键为id，类型为int或bigint，且为auto_increment

2）标识表⾥每⼀⾏主体的字段不要设为主键，建议设为其他字段。如user_id，order_id等，并建⽴unique key索引。

因为如果设为主键且主键值为随机插⼊, 则会导致innodb内部page分裂和⼤量随机I/O，性能下降。

2、核⼼表（如⽤户表，⾦钱相关的表）必须有⾏数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。

3、字段允许适当冗余，以提⾼查询性能，但必须考虑数据⼀致。

反范式设计：把经常需要join查询的字段，在其他表⾥冗余⼀份。如user_name属性在user_account，user_login_log等表⾥冗余⼀份，减少join查询。

冗余字段应遵循：

1）不是频繁修改的字段。

2）不是 varchar超长字段，更不能是 text字段。

正例：商品类⽬名称使⽤频率⾼，字段长度短，名称基本⼀成不变，可在相关联的表中冗余存储类⽬名称，避免关联查询。

4、表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。因为使⽤NULL值会存在每⼀⾏都会占⽤额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

5、建议对表⾥的blob、text等⼤字段，垂直拆分到其他表⾥，仅在需要读这些对象的时候才去select。

6、敏感字段需加密，如账户密码、信⽤卡号等存储。如密码类只用于校验的使⽤：动态盐 + ⾮固定加密算法（MD5/AES256等） + 多轮加密，不要简单使⽤ MD5算法加密，容易被暴⼒破解。

7、单表⾏数超过 500万⾏或者单表容量超过 2GB，才推荐进⾏分库分表。 如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

8、表中的⾃增列（auto_increment属性），推荐使⽤bigint类型。因为⽆符号int存储范围为-2147483648~2147483647（⼤约21亿左右），溢出后会导致报错。

9、业务中选择性很少的状态status、类型type等字段推荐使⽤tinytint或者smallint类型节省存储空间。

10、不推荐使⽤enum。因为它们浪费空间，且枚举值写死了，变更不⽅便。推荐使⽤tinyint或smallint。

11、【建议】存储⾦钱的字段，建议⽤int，程序端乘以100和除以100进⾏存取。因为int占⽤4字节，⽽double占⽤8字节，空间浪费。

12、单个表上的索引个数不能超过7个。

13、在建⽴索引时，多考虑建⽴联合索引，并把区分度最⾼的字段放在最前⾯。

14、如果有 order by的场景，请注意利⽤索引的有序性。order by 最后的字段是组合索引的⼀部分，并且放在索引组合顺序的最后，避免出现 file_sort的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范围查找，那么索引有序性⽆法利⽤，如：WHERE a>10 ORDER BY b; 索引a_b⽆法排序。

15、利⽤覆盖索引来进⾏查询操作，避免回表。

能够建⽴索引的种类：主键索引、唯⼀索引、普通索引，⽽覆盖索引是⼀种查询的⼀种效果，⽤ explain的结果，extra列会出现：using index。

MySQL 是索引组织表，⼆级索引只存储（键值、主键值），因此需要再通过⼀次主键索引查询得到记录，这种⽅式成为回表。在核⼼业务中，使⽤索引覆盖技术，提升索引查询性能，对于回表记录数⽐较⼤的场景，甚⾄可以有 10 倍的性能提升。

16、利⽤延迟关联或者⼦查询优化超多分页场景。

说明：MySQL并不是跳过 offset⾏，⽽是取 offset+N⾏，然后返回放弃前 offset⾏，返回N⾏，那当 offset特别⼤的时候，效率就⾮常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进⾏ SQL改写。

正例：先快速定位需要获取的id段，然后再关联：

SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

17、建组合索引的时候，区分度最⾼的在最左边。

正例：如果 where a=? and b=? ，a列的⼏乎接近于唯⼀值，那么只需要单建 idx_a索引即可。

说明：存在⾮等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>?and b=? 那么即使 a的区分度更⾼，也必须把 b放在索引的最前列。

18、防⽌因字段类型不同造成的隐式转换，导致索引失效。

19、时间类型建议使用datetime，除非记录时区信息，使用timestamp，但更高阶的用法是使用int类型。

datetime和timestamp相比int类型都具备很好的可读性，但是int类型的比较和计算性能更好，特别适用于高并发和大数据量的场景。

DATETIME 类型的范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59，足够覆盖大多数应用场景。
TIMESTAMP 类型的范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC，适用于大多数现代应用，但不适用于需要更长时间范围的场景。TIMESTAMP 类型的时区转换依赖于MySQL服务器的配置。

20、单个分表不超过500W⾏，ibd⽂件⼤⼩不超过2G，这样才能让数据分布式变得性能更佳。

以上，点亮【赞与在看】让我们心中充满力量、披荆斩棘、不惧未来！

【推荐阅读】

如何下保证MySQL数据库与Redis缓存数据一致性？

上交所P0故障，思考技术上可以有哪些优化？

面试：电商的核心数据库设计

关于作者：一位热爱技术，并在职场与自媒体间探索的实践者，希望通过分享个人经验和见解，帮助更多人实现自我成长和价值。

ITPub博客

支持千万级的大表，都有哪些规范？