MySQL 数据量太大怎么提升查询性能?

比如随着业务的发展,订单表的数据量越来越大,这个时候查询变慢了,我们可以采取什么措施来提升查询性能呢?

1、存档历史数据

当单表的订单数据太多,多到影响性能的时候,首选的方案是,归档历史订单。
所谓归档,其实也是一种拆分数据的策略。简单地说,就是把大量的历史订单移到另外一张历史订单表中。为什么这么做呢?因为像订单这类具有时间属性的数据,都存在热尾效应。大多数情况下访问的都是最近的数据,但订单表里面大量的数据都是不怎么常用的老数据。因为新数据只占数据总量中很少的一部分,所以把新老数据分开之后,新数据的数据量就会少很多,查询速度也就会快很多。老数据虽然和之前比起来没少多少,查询速度提升不明显,但是,因为老数据很少会被访问到,所以慢一点儿也问题不大。
这样拆分的另外一个好处是,拆分订单时,需要改动的代码非常少。大部分对订单表的操作都是在订单完成之前,这些业务逻辑都是完全不用修改的。即使像退货退款这类订单完成后的操作,也是有时限的,那这些业务逻辑也不需要修改,原来该怎么操作订单表还怎么操作。基本上只有查询统计类的功能,会查到历史订单,这些需要稍微做一些调整,按照时间,选择去订单表还是历史订单表查询就可以了。很多电商大厂在它逐步发展壮大的过程中,都用这种订单拆分的方案撑了好多年。

2、分库分表

在考虑到底是分库还是分表之前,我们需要先明确一个原则,那就是能不拆就不拆,能少拆不多拆。原因也很简单,你把数据拆分得越散,开发和维护起来就越麻烦,系统出问题的概率就越大。
分库分表的目的是解决两个问题:
1、数据量太大查询慢。解决查询慢,只要减少每次查询的数据总量就可以了,也就是说,分表就可以解决问题。
2、应对高并发。一个数据库实例撑不住,就把并发请求分散到多个实例中去,所以,解决高并发的问题是需要分库的。
简单地说,数据量大,就分表;并发高,就分库。一般情况下,我们的方案都需要同时做分库分表,这时候分多少个库,多少张表,分别用预估的并发量和数据量来计算就可以了。

如何选择 Sharding Key?

选择Sharding Key最重要的参考因素是,我们的业务是如何访问数据的。选择Sharding key的时候,一定要能兼容业务最常用的查询条件,让查询尽量落在一个分片中,分片之后无法兼容的查询,可以把数据同步到其他存储中去,来解决这个问题。

如何选择分片算法?

1. 范围分片
比如订单表中分12个分片,每个月一个分片,这样对查询的兼容要好多,毕竟查询条件中带上时间范围,让查询只落到某一个分片上,还是比较容易的,我在查询界面上强制用户必须指定时间范围就行了。这种做法有个很大的问题,比如现在是 3 月份,那基本上所有的查询都集中在 3 月份这个分片上,其他 11 个分片都闲着,这样不仅浪费资源,很可能你 3 月那个分片根本抗不住几乎全部的并发请求。这个问题就是“热点问题”。
基于范围来分片容易产生热点问题,不适合作为订单的分片方法,但是这种分片方法的优点也很突出,那就是对查询非常友好,基本上只要加上一个时间范围的查询条件,原来该怎么查,分片之后还可以怎么查。范围分片特别适合那种数据量非常大,但并发访问量不大的 ToB 系统。比如说,电信运营商的监控系统,它可能要采集所有人手机的信号质量,然后做一些分析,这个数据量非常大,但是这个系统的使用者是运营商的工作人员,并发量很少。这种情况下就很适合范围分片。
2. 哈希分片
哈希分片比较容易把数据和查询均匀地分布到所有分片中。一般来说,订单表都采用更均匀的哈希分片算法。比如说,我们要分 24 个分片,选定了 Sharding Key 是用户 ID,那我们决定某个用户的订单应该落到那个分片上的算法是,拿用户 ID 除以 24,得到的余数就是分片号。这是最简单的取模算法,一般就可以满足大部分要求了。当然也有一些更复杂的哈希算法,像一致性哈希之类的,特殊情况下也可以使用。需要注意的一点是,哈希分片算法能够分得足够均匀的前提条件是,用户 ID 后几位数字必须是均匀分布的。比如说,你在生成用户 ID 的时候,自定义了一个用户 ID 的规则,最后一位 0 是男性,1 是女性,这样的用户 ID 哈希出来可能就没那么均匀,可能会出现热点。

实际案例:对于订单表进行分库分表,一般按照用户 ID 作为 Sharding Key,采用哈希分片算法来均匀分布用户订单数据。为了能支持按订单号查询的需求,需要把用户 ID 的后几位放到订单号中去。

3. 查表法
查表法决定某个 Sharding Key 落在哪个分片上,全靠人为来分配,分配的结果记录在一张表里面。每次执行查询的时候,先去表里查一下要找的数据在哪个分片中。查表法的好处就是灵活,怎么分都可以,你用上面两种分片算法都没法分均匀的情况下,就可以用查表法,人为地来把数据分均匀了。查表法还有一个特好的地方是,它的分片是可以随时改变的。比如我发现某个分片已经是热点了,那我可以把这个分片再拆成几个分片,或者把这个分片的数据移到其他分片中去,然后修改一下分片映射表,就可以在线完成数据拆分了。查表法相对其他两种分片算法来说,缺点是需要二次查询,实现起来更复杂,性能上也稍微慢一些。但是,分片映射表可以通过缓存来加速查询,实际性能并不会慢很多。
image.png


思考:“归档历史订单”的数据拆分方法,和直接进行分库分表相比,比如说按照订单创建时间,自动拆分成每个月一张表,两种方法各有什么优点和缺点?

  • 按月自动拆分订单的好处是,不需要做数据搬运,相对实现比较简单,数据分得更碎,缺点是跨月查询比较麻烦,但好处是容量也更大(因为分片更多)。
  • 归档历史订单的方法,实现起来更复杂,容量要小一些,但是对查询更加友好。
阅读 168

推荐阅读
目录