MySQL 对于千万级的大表要怎么优化？

川长思鸟来 2023-10-14 19:24 40阅读 0赞

实用预警：本文将以实际项目经验为例，为你解读优化的奥义！

首先采用Mysql存储千亿级的数据，确实是一项非常大的挑战。Mysql单表确实可以存储10亿级的数据，只是这个时候性能非常差，项目中大量的实验证明，Mysql单表容量在500万左右，性能处于最佳状态。

针对大表的优化，主要是通过数据库分库分表来解决，目前比较普遍的方案有三个：**分区，分库分表，NoSql/NewSql**。实际项目中，这三种方案是结合的，目前绝大部分系统的核心数据都是以RDBMS存储为主，NoSql/NewSql存储为辅。

### 分区 ###

首先来了解一下分区方案。分区表是由多个相关的底层表实现的。这些底层表也是由句柄对象表示，所以我们也可以[直接访问][Link 1]各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引。这个方案对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等。如何进行分区，在实际应用中是一个非常关键的要素之一。

下面开始举例：以客户信息为例，客户数据量5000万加，项目背景要求保存客户的银行卡绑定关系，客户的证件绑定关系，以及客户绑定的业务信息。此业务背景下，该如何设计数据库呢。项目一期的时候，我们建立了一张客户业务绑定关系表，里面冗余了每一位客户绑定的业务信息。基本结构大致如下：

![format_png][]

查询时，对银行卡做索引，业务编号做索引，证件号做索引。随着需求大增多，这张表的索引会达到10个以上。而且客户解约再签约，里面会保存两条数据，只是绑定的状态不同。假设我们有5千万的客户，5个业务类型，每位客户平均2张卡，那么这张表的数据量将会达到惊人的5亿，事实上我们系统用户量还没有过百万时就已经不行了。这样的设计绝对是不行的，无论是插入，还是查询，都会让系统崩溃。

[mysql数据库][mysql]中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的[datadir][]来查看）， 一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。这三个文件都非常的庞大，尤其是.myd文件，快5个G了。下面进行第一次分区优化，Mysql支持的分区方式有四种：

![format_png 1][]

在我们的项目中，[range分区][range]和list分区没有使用场景，如果基于绑定编号做range或者list分区，绑定编号没有实际的业务含义，无法通过它进行查询，因此，我们就剩下 HASH 分区和 KEY 分区了，HASH分区仅支持int类型列的分区，且是其中的一列。 KEY 分区倒是可以支持多列，但也要求其中的一列必须是int类型；看我们的库表结构，发现没有哪一列是int类型的，如何做分区呢？增加一列，绑定时间列，将此列设置为int类型，然后按照绑定时间进行分区，将每一天绑定的用户分到同一个区里面去。

这次优化之后，我们的插入快了许多，但是查询依然很慢，为什么？

因为在做查询的时候，我们也只是根据银行卡或者证件号进行查询，并没有根据时间查询，相当于每次查询，mysql都会将所有的分区表查询一遍。进行第二次方案优化，既然 HASH 分区和 KEY分区要求其中的一列必须是int类型的，那么创造出一个int类型的列出来分区是否可以？分析发现，银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串，我们取其中的某一位拿出来作为表分区是否可行呢，通过分析发现，在这串数字中，其中确实有一位是0到9随机生成的，我们基于银行卡号+随机位进行KEY分区，每次查询的时候，通过计算截取出这位随机位数字，再加上卡号，联合查询，达到了分区查询的目的，需要说明的是，分区后，建立的索引，也必须是分区列，否则Mysql还是会在所有的分区表中查询数据。

通过银行卡号查询绑定关系的问题解决了，那么证件号呢，如何通过证件号来查询绑定关系。前面已经讲过，做索引一定是要在分区健上进行，否则会引起[全表扫描][Link 2]。我们再创建了一张新表，保存客户的证件号绑定关系，每位客户的证件号都是唯一的，新的证件号绑定关系表里，证件号作为了主键，那么如何来计算这个分区健呢，客户的证件信息比较庞杂，有身份证号，港澳台通行证，[机动车驾驶证][Link 3]等等，如何在无序的证件号里找到分区健。为了解决这个问题，我们将证件号绑定关系表一分为二，其中的一张表专用于保存身份证类型的证件号，另一张表则保存其他证件类型的证件号，在身份证类型的证件绑定关系表中，我们将身份证号中的月数拆分出来作为了分区健，将同一个月出生的客户证件号保存在同一个区，这样分成了12个区，其他证件类型的证件号，数据量不超过10万，就没有必要进行分区了。这样每次查询时，首先通过证件类型确定要去查询哪张表，再计算分区健进行查询。作了分区设计之后，保存2000万用户数据时银行卡表的数据保存文件就分成了10个小文件，证件表的数据保存文件分成了12个小文件，解决了这两个查询的问题，还剩下一个问题：业务编号怎么办？**一个客户有多个签约业务**，如何进行保存？这时候，采用分区的方案就不太合适了，它需要用到分表的方案。

### 分表 ###

我们前面有提到过对于mysql，其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大时，操作系统对大文件的操作就会比较麻烦耗时，且有的操作系统就不支持大文件，这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，查询一个节点可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。所以综上我们就必须进行分表与分库的操作。

如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：阿里的TDDL，DRDS和cobar，[京东金融][Link 4]的sharding-jdbc；民间组织的MyCAT；360的Atlas；美团的zebra；其他比如网易，58，京东等公司都有自研的中间件。

这么多的分库分表中间件方案归总起来，就两类：client模式和proxy模式。

![format_png 2][]

client模式

![ac70789687e7088e5c023f76754f891f.png][]

proxy模式

无论是client模式，还是proxy模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。个人比较倾向于采用client模式，它架构简单，性能损耗也比较小，运维成本低。

如何对业务类型进行分库分表。[分库分表][Link 5]最重要的一步，即sharding column的选取，sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。在我们的项目场景中，sharding column无疑最好的选择是业务编号。通过业务编号，将客户不同的绑定签约业务保存到不同的表里面去，根据业务编号路由到相应的表中进行查询，达到进一步优化sql的目的。

[Link 1]: https://www.zhihu.com/search?q=%E7%9B%B4%E6%8E%A5%E8%AE%BF%E9%97%AE&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[format_png]: https://img-blog.csdnimg.cn/img_convert/9c8d37247727e59e9b6eae61b1ef5520.webp?x-oss-process=image/format,png
[mysql]: https://www.zhihu.com/search?q=mysql%E6%95%B0%E6%8D%AE%E5%BA%93&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[datadir]: https://www.zhihu.com/search?q=datadir&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/b3b514d77d712a0d9d97a43445e7649f.webp?x-oss-process=image/format,png
[range]: https://www.zhihu.com/search?q=range%E5%88%86%E5%8C%BA&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[Link 2]: https://www.zhihu.com/search?q=%E5%85%A8%E8%A1%A8%E6%89%AB%E6%8F%8F&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[Link 3]: https://www.zhihu.com/search?q=%E6%9C%BA%E5%8A%A8%E8%BD%A6%E9%A9%BE%E9%A9%B6%E8%AF%81&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[Link 4]: https://www.zhihu.com/search?q=%E4%BA%AC%E4%B8%9C%E9%87%91%E8%9E%8D&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/4b3b6eaeab87fb4944fd31f68539085e.webp?x-oss-process=image/format,png
[ac70789687e7088e5c023f76754f891f.png]: https://img-blog.csdnimg.cn/img_convert/ac70789687e7088e5c023f76754f891f.png
[Link 5]: https://www.zhihu.com/search?q=%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A574410444%7D