首页
纵观苏宁供应链5年演化历程,他提炼了4条Mysql数据库优化设计规范!
发布来源: 苏宁科技 发布时间:2020-08-01

点击蓝字

关注我们

0

数据库作为系统的根基,设计的好坏会直接影响系统性能和系统扩展。


本文通过对苏宁的供应链采购中台的产品线近5年的演进和优化过程中数据库设计遇到的一些问题和经验进行总结,提炼了4条数据库设计规范,帮助大家更透彻地认知规范,最终达到契合自身系统环境、业务场景,真正把握其“度”。


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

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

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

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

  • 数据库操作的最小单位是页(16k),所有的节点都存放于页上的。

01






创建数据库表时,设置自增ID为主键

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

图1


图2

  • 插入速度更快

向一个链表中插入数据,毫无疑问是链表头和链表尾速度最快,自增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就容易出现主键冲突问题。


图3

02






表中通过冗余字段,加快检索速度

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


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


  • 浪费公司资源,通俗说法“费钱”

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


  •  增加运维难度

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


  •  影响性能

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

03






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

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


  • 占用磁盘空间

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


  • 影响数据操作的性能

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


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


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

04






表设计时字符型类型推荐选择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倍之差,可怕的是只要我们操作数据必然经过内存,特别是采用临时表进行排序操作。



业内有句话叫“不存在解决一切问题的银弹”,所处的环境不同,业务不同,数据量级不同等等,再权威的规范也不能适应一切的问题。理解规范的精髓,审时度势,灵活运用,才是解决之道。




本文作者胥磊,就职于苏宁科技集团。本文首发于“苏宁科技”公众号,未经许可,禁止进行转载、摘编、复制等任何使用。如需转载,请申请授权。


注:本文系本站转载,转载目的在于传递更多信息,并不代表本站赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题,请与本站联系,我们将在第一时间删除内容!本文版权归原作者所有 内容为作者个人观点 本站只提供参考并不构成任何投资及应用建议。

相关推送

如何解决金融业对MySQL数据库最关心的问题

可以关注我们哦杭州沃趣科技股份有限公司创建于2012年,谈起沃趣科技,也许很多人还有些陌生。这是一家成立不久的数据库第三方服务提供商,公司的联合创始人都是从阿里巴巴走出来的技术人。凭借着多年的运维经验,为行业客户提供专业数据库、系统相关的服务和产品。在沃趣科技的官网微博上我们找到了公司名称的来历:肥沃、有趣,代表可以赚到钱,做的事情也有意思。当然灵感来自某网络热词,好记好养活。从成立之初,沃趣科技

供应链物流网络规划设计及优化

不少企业在调整供应链时,常常寄希望于建立新项目,使用新技术,少有考虑供应链物流网络优化。实质上,新项目的建设往往需要投入大量的资金,项目周期通常很长,最终并不一定能够达到预期效果。近些年,日趋激烈的产品竞争让企业认识到,仅提升技术能够获得利润越来越少,时间与速度逐渐成为产品市场竞争优势之源,而供应链物流网络规划设计及优化能够给企业带来这方面的优势。供应链物流网络规划是着眼于整体的全局优化网络布局设

卓越供应链设计与优化人才培养的路径

随着经济全球化和企业竞争模式的转变,以及互联网数字化时代的到来,企业的供应链均在进行重塑和优化,供应链战略和策略的制定也建立在数据分析的基础之上。越来越多的企业将卓越的供应链设计列为自己的核心业务功能,这些企业使用建模技术不断优化其端到端供应链,从而提高企业服务水平,寻找主要的成本节约机会,降低风险,并保持其在市场中相对于其他企业的领先地位。卓越的供应链设计三个关键因素卓越的供应链设计必须建立在坚

 

供应链人的大招技能供应链设计和优化

供应链(supplychain)概念是在20世纪80年代末提出的,近年来随着中国企业全球化合作的深入,供应链的理念在企业中得到普遍应用。企业大多数的供应链部门都是从原来的采购部、计划部、物流部等部门发展整合形成的,因此,很少有企业的供应链是经过先期设计的。在全球事件频发的背景下,企业管理者将目光更多的投向了供应链,期望通过供应链的优化来提升或形成企业的核心竞争能力。目前企业供应链的优化大多停留在运

供应链优化建模演示仓网规划、Samp;OP、多级库存优化

演讲人简介LLamasoft解决方案顾问丁力丁力先生,毕业于美国佐治亚理工学院供应链工程硕士专业。先后在LLamasoft北美与中国担任资深项目顾问,项目经理。期间参与并主导了20余项供应链优化项目,为多个世界500强企业提供供应链网络布局、库存优化、产销协同计划、生产计划等解决方案。行业经验涉及零售、快消、电器、医疗健康和服装。服务的客户覆盖中国,北美,东南亚和澳大利亚,包括强生,蒙牛,惠而浦,

2019供应链优化与设计大会

LLamaCon供应链优化与设计大会LLamaCon是由LLamasoft智模软件一年一度举办的全国性供应链大会,每年汇聚来自国内各行业知名企业的总经理、供应链总监、经理参与。每届会议都有丰富的企业真实案例分享,目前已有农夫山泉、蒙牛、麦德龙、宜家、顺丰、海尔日日顺、妮维雅等众多知名企业在LLamaCon上演讲。即刻报名参会!请拉至文章最下方报名所有的2019年Gartner供应链大师和23家供应