从零到上亿用户，我是如何一步步优化MySQL数据库的？（建议收藏）

雨点打透心脏的1/2处 2021-09-09 11:26 157阅读 0赞

**大家好，我是冰河~~**

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

本文记录了我之前初到一家创业公司，从零开始到用户超千万，系统压力暴增的情况下是如何一步步优化MySQL数据库的，以及数据库架构升级的演变过程。升级的过程极具技术挑战性，也从中收获不少。希望能够为小伙伴们带来实质性的帮助。

**如果文章对大家有点帮助，小伙伴们点赞，收藏，评论，分享走起呀~~**

## 业务背景 ##

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

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

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

## 最初的技术选型 ##

作为创业公司，最重要的一点是敏捷，快速实现产品，对外提供服务，于是我们选择了公有云服务，保证快速实施和可扩展性，节省了自建机房等时间。整体后台采用的是Java语言进行开发，数据库使用的MySQL。整体如下图所示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center]

## 读写分离 ##

随着业务的发展，访问量的极速增长，上述的方案很快不能满足性能需求。每次请求的响应时间越来越长，比如用户在H5页面上不断刷新商品，响应时间从最初的500毫秒增加到了2秒以上。业务高峰期，系统甚至出现过宕机。在这生死存亡的关键时刻，通过监控，我们发现高期峰MySQL CPU使用率已接近80%，磁盘IO使用率接近90%，slow query（慢查询）从每天1百条上升到1万条，而且一天比一天严重。数据库俨然已成为瓶颈，我们必须得快速做架构升级。

当Web应用服务出现性能瓶颈的时候，由于服务本身无状态，我们可以通过加机器的水平扩展方式来解决。 而数据库显然无法通过简单的添加机器来实现扩展，因此我们采取了MySQL主从同步和应用服务端读写分离的方案。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 1]

实现读写分离后，数据库的压力减少了许多，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秒以上。

另一方面，业务越来越复杂，多个应用系统使用同一个数据库，其中一个很小的非核心功能出现延迟，常常影响主库上的其它核心业务功能。这时，主库成为了性能瓶颈，我们意识到，必需得再一次做架构升级，将主库做拆分，一方面以提升性能，另一方面减少系统间的相互影响，以提升系统稳定性。这一次，我们将系统按业务进行了垂直拆分。如下图所示，将最初庞大的数据库按业务拆分成不同的业务数据库，每个系统仅访问对应业务的数据库，尽量避免或减少跨库访问。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 2]

垂直分库过程，我们也遇到不少挑战，最大的挑战是：不能跨库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进一步进行水平拆分。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 3]

水平分库面临的第一个问题是，按什么逻辑进行拆分。一种方案是按城市拆分，一个城市的所有数据在一个数据库中；另一种方案是按订单ID平均拆分数据。按城市拆分的优点是数据聚合度比较高，做聚合查询比较简单，实现也相对简单，缺点是数据分布不均匀，某些城市的数据量极大，产生热点，而这些热点以后可能还要被迫再次拆分。按订单ID拆分则正相反，优点是数据分布均匀，不会出现一个数据库数据极大或极小的情况，缺点是数据太分散，不利于做聚合查询。比如，按订单ID拆分后，一个商家的订单可能分布在不同的数据库中，查询一个商家的所有订单，可能需要查询多个数据库。针对这种情况，一种解决方案是将需要聚合查询的数据做冗余表，冗余的表不做拆分，同时在业务开发过程中，减少聚合查询。

经过反复思考，我们最后决定按订单ID做水平分库。从架构上，将系统分为三层：

*  应用层：即各类业务应用系统
 *  数据访问层：统一的数据访问接口，对上层应用层屏蔽读写分库、分表、缓存等技术细节。
 *  数据层：对DB数据进行分片，并可动态的添加shard分片。

水平分库的技术关键点在于数据访问层的设计，数据访问层主要包含三部分：

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

而数据库中间件需要包含如下重要的功能：

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

## ID生成器 ##

ID生成器是整个水平分库的核心，它决定了如何拆分数据，以及查询存储-检索数据。ID需要跨库全局唯一，否则会引发业务层的冲突。此外，ID必须是数字且升序，这主要是考虑到升序的ID能保证MySQL的性能（若是UUID等随机字符串，在高并发和大数据量情况下，性能极差）。同时，ID生成器必须非常稳定，因为任何故障都会影响所有的数据库操作。

我们系统中ID生成器的设计如下所示。

![在这里插入图片描述][9c3077ed56e448969df56cc4619fdc53.jpg_pic_center]

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

水平分库是一个极具挑战的项目，我们整个团队也在不断的迎接挑战中快速成长。

为了适应公司业务的不断发展，除了在MySQL数据库上进行相应的架构升级外，我们还搭建了一套完整的大数据实时分析统计平台，在系统中对用户的行为进行实时分析。

关于如何搭建大数据实时分析统计平台，对用户的行为进行实时分析，我们后面再详细介绍。

## 写在最后 ##

**如果你想进大厂，想升职加薪，或者对自己现有的工作比较迷茫，都可以私信我交流，希望我的一些经历能够帮助到大家~~**

**推荐阅读：**

*  《[我用多线程进一步优化了亿级流量电商业务下的海量数据校对系统，性能再次提升了200%！！（全程干货，建议收藏）][200]》
 *  《[我用多线程优化了亿级流量电商业务下的海量数据校对系统，性能直接提升了200%！！（全程干货，建议收藏）][200 1]》
 *  《[我用10张图总结出了这份并发编程最佳学习路线！！（建议收藏）][10]》
 *  《[高并发场景下一种比读写锁更快的锁，看完我彻底折服了！！（建议收藏）][Link 1]》
 *  《[全网最全性能优化总结！！（冰河吐血整理，建议收藏）][Link 2]》
 *  《[三天撸完了MyBatis，各位随便问！！（冰河吐血整理，建议收藏）][MyBatis]》
 *  《[奉劝那些刚参加工作的学弟学妹们：要想进大厂，这些并发编程知识是你必须要掌握的！完整学习路线！！（建议收藏）][Link 3]》
 *  《[奉劝那些刚参加工作的学弟学妹们：要想进大厂，这些核心技能是你必须要掌握的！完整学习路线！！（建议收藏）][Link 4]》
 *  《[奉劝那些刚参加工作的学弟学妹们：这些计算机与操作系统基础知识越早知道越好！万字长文太顶了！！（建议收藏）][Link 5]》
 *  《[我用三天时间开发了一款老少皆宜的国民级游戏，支持播放音乐，现开放完整源代码和注释（建议收藏）！！][Link 6]》
 *  《[我是全网最硬核的高并发编程作者，CSDN最值得关注的博主，大家同意吗？（建议收藏）][CSDN]》
 *  《[毕业五年，从月薪3000到年薪百万，我掌握了哪些核心技能？（建议收藏）][3000]》
 *  《[我入侵了隔壁妹子的Wifi，发现。。。（全程实战干货，建议收藏）][Wifi]》
 *  《[千万不要轻易尝试“熊猫烧香”，这不，我后悔了！][Link 7]》
 *  《[清明节偷偷训练“熊猫烧香”，结果我的电脑为熊猫“献身了”！][Link 8]》
 *  《[7.3万字肝爆Java8新特性，我不信你能看完！（建议收藏）][7.3_Java8]》
 *  《[在业务高峰期拔掉服务器电源是一种怎样的体验？][Link 9]》
 *  《[全网最全Linux命令总结！！（史上最全，建议收藏）][Linux]》
 *  《[用Python写了个工具，完美破解了MySQL！！（建议收藏）][Python_MySQL]》
 *  《[SimpleDateFormat类到底为啥不是线程安全的？（附六种解决方案，建议收藏）][SimpleDateFormat]》
 *  《[MySQL 8中新增的这三大索引，直接让MySQL起飞了，你竟然还不知道！！（建议收藏）][MySQL 8_MySQL]》

**好了，今天就到这儿吧，小伙伴们点赞、收藏、评论，一键三连走起呀，我是冰河，我们下期见~~**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center]: /images/20210901/1c0cbeb57c6140998551bc69d713a572.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210901/267d678e8893433ba0ddbb645697ad82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210901/d9ceb9d56fc14a8bba5634279dff91d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210901/82832d38d29f407f9455696c39f6a867.png
[9c3077ed56e448969df56cc4619fdc53.jpg_pic_center]: /images/20210901/bcfd86748d94484fa4cb2def2b35d3bc.png
[200]: https://blog.csdn.net/l1028386804/article/details/119724650
[200 1]: https://blog.csdn.net/l1028386804/article/details/119632011
[10]: https://blog.csdn.net/l1028386804/article/details/119529877
[Link 1]: https://blog.csdn.net/l1028386804/article/details/118887662
[Link 2]: https://blog.csdn.net/l1028386804/article/details/118175154
[MyBatis]: https://blog.csdn.net/l1028386804/article/details/118079011
[Link 3]: https://blog.csdn.net/l1028386804/article/details/119341751
[Link 4]: https://blog.csdn.net/l1028386804/article/details/116081409
[Link 5]: https://blog.csdn.net/l1028386804/article/details/115923034
[Link 6]: https://blog.csdn.net/l1028386804/article/details/116191713
[CSDN]: https://blog.csdn.net/l1028386804/article/details/116280491
[3000]: https://blog.csdn.net/l1028386804/article/details/115677451
[Wifi]: https://blog.csdn.net/l1028386804/article/details/116477701
[Link 7]: https://blog.csdn.net/l1028386804/article/details/115450665
[Link 8]: https://blog.csdn.net/l1028386804/article/details/115607742
[7.3_Java8]: https://blog.csdn.net/l1028386804/article/details/115547910
[Link 9]: https://blog.csdn.net/l1028386804/article/details/115576102
[Linux]: https://blog.csdn.net/l1028386804/article/details/117917710
[Python_MySQL]: https://blog.csdn.net/l1028386804/article/details/118378477
[SimpleDateFormat]: https://blog.csdn.net/l1028386804/article/details/118783851
[MySQL 8_MySQL]: https://blog.csdn.net/l1028386804/article/details/119442194
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxMDI4Mzg2ODA0_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20210901/0f6f07585d394197a5a7ff051e2f6463.png