关于数据库:从零到千万用户我是如何一步步优化MySQL数据库的

写在后面

很多小伙伴留言说让我写一些工作过程中的实在案例，写些啥呢？想来想去，写一篇我在以前公司从零开始到用户超千万的数据库架构降级演变的过程吧。

本文记录了我之前初到一家守业公司，从零开始到用户超千万，零碎压力暴增的状况下是如何一步步优化 MySQL 数据库的，以及数据库架构降级的演变过程。降级的过程极具技术挑战性，也从中播种不少。心愿可能为小伙伴们带来实质性的帮忙。

业务背景

我之前呆过一家守业工作，是做商城业务的，商城这种业务，外表上看起来波及的业务简略，包含：用户、商品、库存、订单、购物车、领取、物流等业务。然而，细分下来，还是比较复杂的。这其中往往会牵扯到很多晋升用户体验的潜在需要。例如：为用户举荐商品，这就波及到用户的行为剖析和大数据的精准举荐。如果说具体的技术的话，那必定就蕴含了：用户行为日志埋点、采集、上报，大数据实时统计分析，用户画像，商品举荐等大数据技术。

公司的业务增长迅速，仅仅 2 年半不到的工夫用户就从零积攒到千万级别，每天的访问量几亿次，顶峰 QPS 高达上万次每秒。数据的写压力来源于用户下单，领取等操作，尤其是赶上双十一大促期间，零碎的写压力会成倍增长。然而，读业务的压力会远远大于写压力，据不齐全统计，读业务的申请量是写业务的申请量的 50 倍左右。

接下来，咱们就一起来看看数据库是如何降级的。

最后的技术选型

作为守业公司，最重要的一点是麻利，疾速实现产品，对外提供服务，于是咱们抉择了私有云服务，保障疾速施行和可扩展性，节俭了自建机房等工夫。整体后盾采纳的是 Java 语言进行开发，数据库应用的 MySQL。整体如下图所示。

读写拆散

随着业务的倒退，访问量的极速增长，上述的计划很快不能满足性能需求。每次申请的响应工夫越来越长，比方用户在 H5 页面上一直刷新商品，响应工夫从最后的 500 毫秒减少到了 2 秒以上。业务高峰期，零碎甚至呈现过宕机。在这生死存亡的关键时刻，通过监控，咱们发现高期峰 MySQL CPU 使用率已靠近 80%，磁盘 IO 使用率靠近 90%，slow query（慢查问）从每天 1 百条回升到 1 万条，而且一天比一天重大。数据库俨然已成为瓶颈，咱们必须得疾速做架构降级。

当 Web 应用服务呈现性能瓶颈的时候，因为服务自身无状态，咱们能够通过加机器的程度扩大形式来解决。而数据库显然无奈通过简略的增加机器来实现扩大，因而咱们采取了 MySQL 主从同步和利用服务端读写拆散的计划。

MySQL 反对主从同步，实时将主库的数据增量复制到从库，而且一个主库能够连贯多个从库同步。利用此个性，咱们在利用服务端对每次申请做读写判断，若是写申请，则把这次申请内的所有 DB 操作发向主库；若是读申请，则把这次申请内的所有 DB 操作发向从库，如下图所示。

实现读写拆散后，数据库的压力缩小了许多，CPU 使用率和 IO 使用率都降到了 5% 以内，Slow Query（慢查问）也趋近于 0。主从同步、读写拆散给咱们次要带来如下两个益处：

• 加重了主库（写）压力：商城业务次要来源于读操作，做读写拆散后，读压力转移到了从库，主库的压力减小了数十倍。
• 从库（读）可程度扩大（加从库机器）：因零碎压力次要是读申请，而从库又可程度扩大，当从库压力太时，可间接增加从库机器，缓解读申请压力。

当然，没有一个计划是万能的。读写拆散，临时解决了 MySQL 压力问题，同时也带来了新的挑战。业务高峰期，用户提交完订单，在我的订单列表中却看不到本人提交的订单信息（典型的 read after write 问题）；零碎外部偶然也会呈现一些查问不到数据的异样。通过监控，咱们发现，业务高峰期 MySQL 可能会呈现主从复制提早，极其状况，主从提早高达数秒。这极大的影响了用户体验。

那如何监控主从同步状态？在从库机器上，执行 show slave status，查看 Seconds_Behind_Master 值，代表主从同步从库落后主库的工夫，单位为秒，若主从同步无提早，这个值为 0。MySQL 主从提早一个重要的起因之一是主从复制是单线程串行执行（高版本 MySQL 反对并行复制）。

那如何防止或解决主从提早？咱们做了如下一些优化：

• 优化 MySQL 参数，比方增大 innodb_buffer_pool_size，让更多操作在 MySQL 内存中实现，缩小磁盘操作。
• 应用高性能 CPU 主机。
• 数据库应用物理主机，防止应用虚构云主机，晋升 IO 性能。
• 应用 SSD 磁盘，晋升 IO 性能。SSD 的随机 IO 性能约是 SATA 硬盘的 10 倍甚至更高。
• 业务代码优化，将实时性要求高的某些操作，强制应用主库做读操作。
• 降级高版本 MySQL，反对并行主从复制。

垂直分库

读写拆散很好的解决了读压力问题，每次读压力减少，能够通过加从库的形式程度扩大。然而写操作的压力随着业务爆发式的增长没有失去无效的缓解，比方用户提交订单越来越慢。通过监控 MySQL 数据库，咱们发现，数据库写操作越来越慢，一次一般的 insert 操作，甚至可能会执行 1 秒以上。

另一方面，业务越来越简单，多个利用零碎应用同一个数据库，其中一个很小的非核心性能呈现提早，经常影响主库上的其它外围业务性能。这时，主库成为了性能瓶颈，咱们意识到，必须得再一次做架构降级，将主库做拆分，一方面以晋升性能，另一方面缩小零碎间的相互影响，以晋升零碎稳定性。这一次，咱们将零碎按业务进行了垂直拆分。如下图所示，将最后宏大的数据库按业务拆分成不同的业务数据库，每个零碎仅拜访对应业务的数据库，尽量避免或缩小跨库拜访。

垂直分库过程，咱们也遇到不少挑战，最大的挑战是：不能跨库 join，同时须要对现有代码重构。单库时，能够简略的应用 join 关联表查问；拆库后，拆分后的数据库在不同的实例上，就不能跨库应用 join 了。

例如，通过商家名查问某个商家的所有订单，在垂直分库前，能够 join 商家和订单表做查问，也能够间接应用子查问，如下如示：

select * from tb_order where supplier_id in (select id from supplier where name=’商家名称’)；

分库后，则要重构代码，先通过商家名查问商家 id，再通过商家 id 查问订单表，如下所示：

select id from supplier where name=’商家名称’select * from tb_order where supplier_id in (supplier_ids)

垂直分库过程中的经验教训，使咱们制订了 SQL 最佳实际，其中一条便是程序中禁用或少用 join，而应该在程序中组装数据，让 SQL 更简略。一方面为当前进一步垂直拆分业务做筹备，另一方面也防止了 MySQL 中 join 的性能低下的问题。

通过近十天加班加点的底层架构调整，以及业务代码重构，终于实现了数据库的垂直拆分。拆分之后，每个应用程序只拜访对应的数据库，一方面将单点数据库拆分成了多个，摊派了主库写压力；另一方面，拆分后的数据库各自独立，实现了业务隔离，不再相互影响。

程度分库

读写拆散，通过从库程度扩大，解决了读压力；垂直分库通过按业务拆分主库，缓存了写压力，但零碎仍然存在以下隐患：

• 单表数据量越来越大。如订单表，单表记录数很快就过亿，超出 MySQL 的极限，影响读写性能。
• 外围业务库的写压力越来越大，已不能再进一次垂直拆分，此时的零碎架构中，MySQL 主库不具备程度扩大的能力。

此时，咱们须要对 MySQL 进一步进行程度拆分。

程度分库面临的第一个问题是，按什么逻辑进行拆分。一种计划是按城市拆分，一个城市的所有数据在一个数据库中；另一种计划是按订单 ID 均匀拆分数据。按城市拆分的长处是数据聚合度比拟高，做聚合查问比较简单，实现也绝对简略，毛病是数据分布不平均，某些城市的数据量极大，产生热点，而这些热点当前可能还要被迫再次拆分。按订单 ID 拆分则正相反，长处是数据分布平均，不会呈现一个数据库数据极大或极小的状况，毛病是数据太扩散，不利于做聚合查问。比方，按订单 ID 拆分后，一个商家的订单可能散布在不同的数据库中，查问一个商家的所有订单，可能须要查问多个数据库。针对这种状况，一种解决方案是将须要聚合查问的数据做冗余表，冗余的表不做拆分，同时在业务开发过程中，缩小聚合查问。

通过重复思考，咱们最初决定按订单 ID 做程度分库。从架构上，将零碎分为三层：

• 应用层：即各类业务利用零碎
• 数据拜访层：对立的数据拜访接口，对下层应用层屏蔽读写分库、分表、缓存等技术细节。
• 数据层：对 DB 数据进行分片，并可动静的增加 shard 分片。

程度分库的技术关键点在于数据拜访层的设计，数据拜访层次要蕴含三局部：

• 分布式缓存
• 数据库中间件
• 数据异构中间件

而数据库中间件须要蕴含如下重要的性能：

• ID 生成器：生成每张表的主键
• 数据源路由：将每次 DB 操作路由到不同的分片数据源上

ID 生成器

ID 生成器是整个程度分库的外围，它决定了如何拆分数据，以及查问存储 - 检索数据。ID 须要跨库全局惟一，否则会引发业务层的抵触。此外，ID 必须是数字且升序，这次要是思考到升序的 ID 能保障 MySQL 的性能（若是 UUID 等随机字符串，在高并发和大数据量状况下，性能极差）。同时，ID 生成器必须十分稳固，因为任何故障都会影响所有的数据库操作。

咱们零碎中 ID 生成器的设计如下所示。

• 整个 ID 的二进制长度为 64 位
• 前 36 位应用工夫戳，以保障 ID 是升序减少
• 两头 13 位是分库标识，用来标识以后这个 ID 对应的记录在哪个数据库中
• 后 15 位为自增序列，以保障在同一秒内并发时，ID 不会反复。每个分片库都有一个自增序列表，生成自增序列时，从自增序列表中获取以后自增序列值，并加 1，做为以后 ID 的后 15 位
• 下一秒时，后 15 位的自增序列再次从 1 开始。

程度分库是一个极具挑战的我的项目，咱们整个团队也在一直的迎接挑战中疾速成长。

为了适应公司业务的一直倒退，除了在 MySQL 数据库上进行相应的架构降级外，咱们还搭建了一套残缺的大数据实时剖析统计平台，在零碎中对用户的行为进行实时剖析。

对于如何搭建大数据实时剖析统计平台，对用户的行为进行实时剖析，咱们前面再具体介绍。

好了，明天就到这儿吧，我是冰河，咱们下期见！！

重磅福利

微信搜一搜【冰河技术】微信公众号，关注这个有深度的程序员，每天浏览超硬核技术干货，公众号内回复【PDF】有我筹备的一线大厂面试材料和我原创的超硬核 PDF 技术文档，以及我为大家精心筹备的多套简历模板（不断更新中），心愿大家都能找到心仪的工作，学习是一条时而郁郁寡欢，时而开怀大笑的路，加油。如果你通过致力胜利进入到了心仪的公司，肯定不要懈怠放松，职场成长和新技术学习一样，逆水行舟。如果有幸咱们江湖再见！

另外，我开源的各个 PDF，后续我都会继续更新和保护，感激大家长期以来对冰河的反对！！

写在最初

如果你感觉冰河写的还不错，请微信搜寻并关注「冰河技术 」微信公众号，跟冰河学习高并发、分布式、微服务、大数据、互联网和云原生技术，「 冰河技术 」微信公众号更新了大量技术专题，每一篇技术文章干货满满！不少读者曾经通过浏览「 冰河技术 」微信公众号文章，吊打面试官，胜利跳槽到大厂；也有不少读者实现了技术上的飞跃，成为公司的技术骨干！如果你也想像他们一样晋升本人的能力，实现技术能力的飞跃，进大厂，升职加薪，那就关注「 冰河技术」微信公众号吧，每天更新超硬核技术干货，让你对如何晋升技术能力不再迷茫！