浅析 Mysql 数据库优化设计规范的“度”

五千年中华文化源远流长，博大精深，寥寥数语便能包含玄机，令人思之无穷。更甚者一个字便彰显了前人无尽的智慧。例如国人做事讲究恰到好处，这里就是讲究对“度”的把握。

    实际生活和工作中，如何把握好“度”很重要。这里把“度”的审视放到 MYSQL 数据库设计规范的理解上，同样恰如其分。对于这些既定的数据库设计规范不能很好的审时度势把握其“度”，只知一味的照本宣科，非但无法达到预期目的，很有可能适得其反。下面通过我们的供应链采购中台的产品线近 5 年的演进和优化过程中数据库设计遇到的一些问题和经验进行了总结，助大家更透彻认知规范，最终达到契合自身系统环境，业务场景，真正把握其“度”,适合自己的才是最好的。

分析之前，先了解下 innodb 数据库表和索引的数据结构和特征，便于我们更好的理解。

ü  innodb 的表本质上是一个主键为索引，叶子节点存放数据的 B+树。

ü  innodb 的二级索引也就是我们常说的普通索引，是叶子节点存放主键键值的 B+树。

ü  B+树的叶子节点都是有序的双向列表。

ü  数据库操作的最小单位是页（16k），所有的节点都存放于页上的。

图 1：

图 2：

图 3：

1、          创建数据库表时，设置自增 ID 为主键

分析：通过图 1 或者图 2 可以看出索引的数据结构有几个特点：B+树，叶子节点组成有序双向链表，单个叶子节点数据组成有序单向链表。这些决定了使用自增 id 为主键有以下几个好处：

Ø  插入速度更快。

向一个链表中插入数据，毫无疑问是链表头和链表尾速度最快，自增 id 的有序性一方面保证插入的数据只会加在链表尾。另一方面可以避免像 uuid，md5 这类不规则数据，插入时有概率集中落入 B+树中间的某些特定叶子节点，因数据页的大小限制，可能导致不停的页分裂，新分裂的节点又可能破坏了 B+树的平衡性，数据库为保持自平衡而进行的页旋转，似乎陷入一个恶性循环。这些都最终会影响插入效率。

Ø  相同的空间，存放更多的数据

自增 ID 通常设置为 int 类型，其占用空间小（4 字节），有效减少索引占的磁盘空间，一个 16k 的数据页能存放更多的数据，范围查询显著降低磁盘 io 的次数。另外还附带经济价值，因为二级索引的叶子节点存放的都是主键的值，设想下一个 1000 万数据的表上面建了一个二级索引，主键设置 int 类型比 varchar（20）能节省最高 500M+的磁盘空间了。另一方面上限高无符号 int 类型可以存放 42 亿+的数据，避免无主键可用尴尬。当然若预计表业务量会非常大，还可以使用 bigint。

²  小知识点：数值类型后面的长度不是代表最大上限的，int（1）和 int（11）都可以存亿级的数字。

辩证分析：那是不是所有表都适合用自增 id 做主键呢？实则不然，万物有其利也必有其弊。其一，因自增 ID 是不具备业务属性的，业务上也不存在通过 id 查数据的情况，更多的是通过二级索引的业务字段检索数据，这就存在一个回表的操作（参考图 3），比直接取数据效率低一些。在一些类似字典表，配置表或者大数据平台灌数据的表会特别明显。其二，通过 binlog 和数据备份恢复数据库时，处理不好就会出现主键冲突。数据库挂掉后，业务紧急（宕机期间，每秒损失 xx 万）最快处理方式就是用最新的全量备份做数据恢复，恢复完后立马启动数据库回复业务，再用备份时间点后到启动前的 binlog 文件进行数据追加，这会儿自增的 id 就容易出现主键冲突问题。

2、         表中通过冗余字段，加快检索速度

分析：随着系统功能的不断优化与迭代，慢慢你就会发现为满足层出不穷的业务需求，要从多个表关联取值的情况会越来越多，无论使用哪种关联算法（NLJ，BNL，BKA）性能肯定都无法和单表比的，这好比仓库选取商品，你要从多个货架取商品，再怎么计算最优路径也不如别人从一个货架就能取完商品更快。适当字段冗余，通过空间换时间提升查询性能，在用户体验至上的主旋律下不失为一种好方法。

辩证分析：冗余字段牺牲一些磁盘空间提升了用户体验，氪金可以解决的问题似乎都不是问题。中国有句古话“过犹不及”，肆意冗余字段可能给我们带来哪些潜在的影响呢？

Ø  浪费公司资源，通俗说法“费钱”

这个不难理解，多加的字段总的要有地方存，占磁盘资源是不可少的。特别是当下新冠疫情肆虐全球，开源节流是很有必要的。不要小看这一条数据增加的几十或几百字节的长度，加持亿级数据的“光环”，立马脱胎换骨变成你不得不正视的存在。

Ø  增加运维难度

以前对某个字段做 DDL 操作只需要对一个表，该字段冗余到多个表后，就需要对多个表进行操作，有遗漏就会埋下隐患。举个很现实的例子，商品名称字段冗余到 3 张很重要的业务表了，现在根据业务需要扩展商品名称字段的长度，因为疏忽只改了其中 2 张表，当有超长的商品名称数据存储时结果可想而知了。

Ø  影响性能

乍一看这似乎和加冗余字段的初衷相悖了？是的！随着字段列的不断增多，行长度越来越长，单个数据页存放的数据条数也就越来越少，某些情况下就会影响查询性能。打个比方，数据页比作一个篮子，其中的数据比作苹果，篮子大小固定的，苹果有大有小，一个篮子可以放 10 个小点的苹果，也可以放 5 个大苹果。同样取 100 个苹果，小的苹果只需要取 10 次，大的就需要 20 次。取苹果的次数就是 IO 的次数，这是影响性能的一个很重要指标。

3、         单表索引个数不宜超过 5 个

分析：加索引的目的是什么？当然是提升查询性能！那添加索引又会影响什么？相信不少人没有考虑过这个问题。

Ø  占用磁盘空间

从索引的数据结构不难看出索引是需要占用磁盘空间存储的，索引个数越多占的磁盘空间越大，积少成多的道理大家应该都懂的。

Ø  影响数据操作的性能

拿二级索引来说，本质就是维护了索引字段和主键的对应关系，对表数据进行增删改操作，如果涉及到了索引字段改动，必然涉及到对索引的维护操作。这些额外的工作最终影响执行性能，如果涉及大批量数据的变更操作，影响会十分可观。

²  小知识点：索引特别是重复的索引过多也可能影响查询的性能，因为 sql 解析阶段优化器会从众多索引中找出那个最优的索引。

辩证分析：诚然索引过多会造成资源浪费和降低批量操作数据的性能。Sql 规范中提及的 5 个索引的上限，正常情况下满足大多数业务场景需求。但是总有例外的情况，这里举个很常见的例子--报表分析数据，因业务特性这类数据通常都是读多写少，又要对数据进行各种维度的查询，对性能要求也很高，适当多加一些索引，通过空间换时间还是很划算的。

4、  表设计时字符型类型推荐选择 varchar

分析：不可否认数据库的表设计时字符型绝对是使用最多的类型，而 varchar 类型又是使用最多的字符类型，Varchar 自然有自身的一些优势。

Ø  按需分配空间

Varchar 作为变长字符类型都是按实际存储的字符长度分配存储空间的，即使你很“豪气”的定义了 varchar（200），存入“一二三”也是中规中矩的占用所需要的几个字符。

Ø  存储更长的字符串

Varchar 类型最大存储长度为 65535 个字节，而 char 的上限只有 255。

²  小知识点：Mysql 5.0 以后的版本字符型后面的数字都是代表字符数上限，而非字节数。所以能存放的字符串长度的上限会因字符集不同而存在差异的。另外 Varchar 还会额外占用 1-2 个字节用于存储长度信息。

辩证分析：实际情况也是 Char 类型貌似被选择性忽视了，究其原因无非是作为定长的字符类型会造成空间浪费，大家潜意识里面认为 varchar 是设置字符类型的绝配“CP”。Char 类型难道真的没有存在价值了？黑格尔有句名言“存在即合理”，char 类型也是不可或缺的。

Ø  Char 类型占据更小的空间

这似乎和上面的一些观点有矛盾，实则不然。我们实际业务总会碰到一些定长的字段，例如一些地点编码‘D025’，定义成 char（4）比 varchar（4）就占用更少的空间，varchar 会自身占用一个字节存放字符长度的。

Ø  Char 类型性能更高

还用上面那个地点编码说明一下。char 是定长的，存储引擎基本不需要关心存入字符串的长度了。而 varchar 是变长的，即使你存入的都是相同位数的字符串，统计长度还是必不可少的一步。Char（4）和 varchar（4）哪个更快是不是一目了然了。

总结一下，对一些短字符类型例如上面提到的地点编码，或者长度基本不变的字符类型例如一些订单号很适合用 char 类型的。其余情况还是推荐用 varchar 的，毕竟字段超长超过 255 字节长度已经是 char 类型力所不能及的，另外存的字段值长度差异很大，使用定长存储对空间的浪费也是不可取的。

Ø  小知识点：varchar 类型的长度不是随便填，你想填就能填。不少人主观认为 Varchar 既然是变长字符，定义个 varchar（20）和 varchar（200），只要不存储超过 20 个字符长度的字符串这俩是没差别的。的确占用的磁盘空间是一样的，但是在内存中占的空间可是实打实的 10 倍之差，可怕的是只要我们操作数据必然经过内存，特别是采用临时表进行排序操作。

以上都是个人日常工作中一点总结与思考,可能有些片面，跟多希望抛砖引玉,引起大家的一些思考。业内有句话“不存在解决一切问题的银弹”，所处的环境不同，业务不同，数据量级不同等等，再权威的规范也不能适应一切的问题。更多是要我们理解规范的精髓，审时度势，灵活运用。