vlambda博客
学习文章列表

摘要

本文由以数据之名分享，正所谓“国以民为本，民以食为天，食以安为先，安以质为本，质以诚为根”。而数据库分库分表中间件概念，也可谓为“取之于民，用之于民”之道。取之于民，是因为业务请求通过中间件汇聚，从底层数据库获取数据返回；用之于民，业务数据操作经过中间件操作，又落入底层数据库。一般有分库分表中间件有两种实现方式：服务端分库分表中间件（如DBLE、MyCAT等，其中MyCAT是我们今天的主角）和客户端分库分表中间件（如ShardingJdbc，现在叫ShardingSphere），底层都是基于传统RDBMS或NoSql数据库，搭建的分布式数据库集群。

01

—

技术架构

1.1 技术架构

1.2  技术特征

• 遵守MySQL原生协议，跨语言，跨数据库的通用中间件代理。

• 基于心跳的自动故障切换，支持读写分离，支持MySQL一双主多从，以及一主多从

• 有效管理数据源连接，基于数据分库，而不是分表的模式。

• 基于NIO实现，有效管理线程，高并发问题。

• 支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数。

• 支持两表join，甚至基于caltlet的多表join。

• 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。

• 支持多租户方案。

• 支持分布式事务（弱XA）

• 支持全局序列号，解决分布式下的主键生成问题。

• 分片规则丰富，插件化开发，易于扩展。

• 强大的WEB，命令行监控。

• 支持前端作为MySQL通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 MongoDB。

• 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）。

02

—

部署策略

2.1  单点部署

单节点MyCAT的部署指的是只部署一台MyCAT服务器，它与MyCAT集群部署是相对的，如果这台MyCAT服务器宕机了，MyCAT就不可用了。

2.2  集群部署

• 什么是高可用？

高可用通常也叫HA（High Available）。指的是，一台服务器宕机了，照样能对外提供服务。常用的高可用软件方案有：LVS、Keepalived、Heartbeat等。

• 如何做到高可用？

MyCAT本身是无状态的，可以用HAProxy或四层交换机等设备组成MyCAT的高可用集群，后端MySQL则配置为主从同步，此时整个系统就是高可用的，下图是一个典型的HAProxy + Keepalived + MyCAT高可用与负载均衡集群方案：

• 集群架构解读

1、MyCAT集群中两台Master和两台Slaver都必须安装Keepalived和HAProxy，Keepalived负责为该服务器抢占VIP（虚拟IP），抢占到VIP后，对该主机的访问可以通过原来的IP（Master1或Slave1）访问，也可以直接通过VIP（Master或Slave的虚拟IP）访问。

2、Master2上的Keepalived也会去抢占VIP，抢占VIP时有优先级，配置keepalived.conf中的（priority 150 #数值愈大，优先级越高,Master2上改为120，Master和Slave上该值配置不同）决定。但是一般哪台主机上的Keepalived服务先启动就会抢占到VIP，即使是Slave，只要先启动也能抢到。

3、HAProxy负责将对VIP的请求分发到MyCAT上。起到负载均衡的作用，同时HAProxy也能检测到MyCAT是否存活，HAProxy只会将请求转发到存活的MyCAT上。

4、如果一台服务器（Keepalived+HAProxy服务器）宕机，另外一台上的Keepalived会立刻抢占VIP并接管服务。

如果一台MyCAT服务器宕机，HAProxy转发时不会转发到宕机的MyCAT上，所以MyCAT依然可用。

03

—

混合实践

通过MyCAT接入不同的同构或异构业务数据库后端，便捷高效的解决业务数据聚合查询的完整需求。示例如下图：

04

—

实施指南

4.1  项目实施步骤

首先，全面了解Mycat的能力、目前的限制、以及可能的解决办法，然后，在此基础上，考虑是否用Mycat的分表分片功能，根据目前业务的数据模型和数据访问模式，确定几个可能的分表方案，然后对方案进行针对性的性能测试，在性能数据的基础上，最终决定采用怎样的分片策略。

了解MyCAT的能力，包括如下的方面：

• 研发背景和解决目标；

• 在数据库中间件方面的独特功能和定位；

• 实际案例；

• 优点和不足；

• 所提供的监控和测试工具；

• 社区动态。

其中，关于分片规则的支持和扩展、多数据库支持、SQL拦截和注解、跨库Join、读写分离、缓存功能、高可用性等方面需要比较深入的学习和理解，有助于正确的使用MyCAT来解决当前的业务问题。

接下来是分析当前业务，具体内容包括如下几个方面：

• 数据模型：重点关注数据的增长模式（实时大量增长还是缓慢增长）和规律、数据之间的关联关系；

• 访问模式：通过抓取系统中实际执行的SQL，分析其频率、响应时间、对系统性能和功能的影响程度；

• 可靠性要求：系统中不同数据表的可靠性要求，以及操作模式；

• 事务要求：系统中哪些业务操作是严格事务的，哪些是普通事务或可以无事务的；

• 备份和恢复：目前的备份模式，对系统的压力等。

数据的模型和访问模式在很大程度上决定了未来数据分片的模式，包括哪些表用全局表、哪些用ER分片、哪些用范围分片规则、哪些用一致性Hash或自定义方式。而数据可靠性的要求，则影响到MyCAT后端是采用普通的MySQL主从还是用 Gluster多写模式，事务性要求需要相关的表或者SQL尽量不会跨分片执行，对于以后制定本项目的编程约束有重要意义。

分表方案则需要确定如下一些问题：

• 哪些表要分片、什么分片规则、依赖关联关系如何解决；

• 数据迁移和扩容的手段。

建议根据业务分析的结果，确定两套比较合适分表方案，然后进行性能测试，选出最佳的分表方案，性能测试可以采用MyCAT自带的超级工具，此工具在前面提到过，可以模拟接近真实业务数据的数据，并随机制造大量的数据供测试，是目前开源的最佳数据库性能测试工具。

在最终进入开发之前，架构师还需要给出一个编程约束，需要明确列出不能执行的SQL语句，这些约束可能包括如下几种：

• 跨越太多节点的查询语句；

• 不能Join的表和相关的Join SQL；

• 很影响性能的复杂SQL；

• 对比较大的表的SQL操作提示。

最后在开发阶段，还应该做到如下几点

• 一开始就按照最初的分片设计和数据规模，制造大量的随机数据，进行开发和测试，尽早发现性能问题；

• 对所有的SQL进行统计分析，找出异常的SQL，包括跨越太多分片的SQL，以及执行缓慢的SQL，对这些SQL进行分析和优化；

• 时刻关注性能问题。

当项目上线后，通过MyCAT Web对系统进行监控，特别是服务的IO和网络指标，除此之外，对MyCAT运行过程中的日志也要进行排查，告警信息可能是SQL错误，可能是Mycat Bug，及时分析处理，并积极反馈给MyCAT社区，寻求帮助。

4.2  分表分库原则

分表分库，虽然能解决大表对数据库系统的压力，但它并不是万能的。

• 实施前提：我们弄清楚，为什么要分库？具体分哪些库？使用什么规则分？拆成多少分片？

• 实施原则一：能不分就不分，1000万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。

• 实施原则二：分片数量尽量少，分片尽量均匀分布在多个DataHost上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。

• 实施原则三：分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容。

• 实施原则四：尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题。

• 实施原则五：查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会便利所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

如何准确统计和分析当前系统中最频繁的SQL呢？有几个简单做法：

• 采用特殊的JDBC驱动程序，拦截所有业务SQL，并写程序进行分析

• 采用MyCAT的SQL拦截器机制，写一个插件，拦截所有SQL，并进行统计分析

• 打开MySQL日志，分析统计所有SQL

找出每个表最频繁的SQL，分析其查询条件，以及相互的关系，并结合ER图，就能比较准确的选择每个表的分片策略。

对于大家经常提起的同库内分表的问题，这里做一些分析和说明，同库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络。此外，库内分表的时候，要修改用户程序发出的SQL，可以想象一下A、B两个表各自分5个分片表情况下的Join SQL会有多么的反人类。这种复杂的SQL对于DBA调优来说，也是个很大的问题。

因此，MyCAT和一些主流的数据库中间件，都不支持库内分表，但由于MySQL本身对此有解决方案，所以可以与MyCAT的分库结合，做到最佳效果，下面是MySQL的分表方案：

• MySQL分区；

• MERGE表（MERGE存储引擎）。

通俗地讲MySQL分区是将一大表，根据条件分割成若干个小表。MySQL5.1开始支持数据表分区了。如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区，MySQL分区支持的分区规则有以下几种：LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

在MySQL数据库中，Merge表有点类似于视图，MySQL的Merge引擎类型允许你把许多结构相同的表合并为一个表。之后，你可以执行查询，从多个表返回的结果就像从一个表返回的结果一样。每一个合并的表必须有完全相同表的定义和结构，且只支持MyISAM引擎。

• MySQL Merge表的优点：

• 分离静态的和动态的数据；

• 利用结构接近的的数据来优化查询；

• 查询时可以访问更少的数据；

• 更容易维护大数据集。

在数据量、查询量较大的情况下，不要试图使用Merge表来达到类似于Oracle的表分区的功能，会很影响性能。我的感觉是和Union几乎等价。

MyCAT建议的方案是MyCAT分库+MySQL分区，此方案具有以下优势：

• 充分结合分布式的并行能力和MySQL分区表的优化；

• 可以灵活的控制表的数据规模；

• 可以两个维度对表进行分片，MyCAT一个维度分库，MySQL一个维度分区。

4.3  后端存储的选择

MySQL尽量用比较新的稳定版，当前来说5.6和5.7都是比较靠谱的一个选择，因为MySQL这两个版本做了大量优化。另外MySQL的各种变种版本都可以考虑。以下是一些通用准则：

对于非严格苛刻交易型的数据表，建议用MariaDB，这个版本目前在开源界很盛行，评价很高，Percona版本也值得推荐，Percona有很多辅助的运维工具。

• 对于交易型的数据表，可以考虑MySQL官方稳定版，若交易型的数据表要求可靠性非常高，比如是替代Oracle，也可以选择Galera Cluster这种高可用的方案，他以一定的写入性能损失带来了数据的高可用和高并发访问。

• 根据数据的可靠性要求，可以采用各种数据同步方案，比如1主多从，读写分离提升数据表的读的并发能力。

• 部分表可以用NoSQL方式存储，而前端访问方式不变，MyCAT支持后端MongoDB和很多NoSQL系统，以提升查询能力

• 部分表可以采用MySQL内存表，来提升查询和写入速度，替代部分复杂缓存方案。

下面是一个可能的MyCAT部署方案，不同的表用不同的存储方式，让不同的表根据其访问模式，都达到最佳状态。

4.4  数据拆分原则

• 拆分原则一：达到一定数据量级才拆分（1000万）

• 拆分原则二：不到1000万，但需要与大表（超1000万的表）有关联查询的表也要拆分

• 拆分原则三：大表关联表如何拆：小于100万的使用全局表；在100万与1000万之间的表，跟大表使用同样的拆分策略；无法跟大表使用相同规则的，可以考虑从Java代码上分步骤查询，不用关联查询，或者破例使用全局表。

• 拆分原则四：破例全局表：如goods_sku表180万，跟大表关联了，又无法跟大表使用相同拆分策略，也做成了全局表。破例全局表必须满足的条件：没有太激烈的并发update，如多线程同时update同一条id=1的记录。多线程update全局表的同一行记录会死锁。

• 拆分原则五：拆分字段是不可修改的

• 拆分原则六：拆分字段只能是一个字段，如果想按照两个字段拆分，必须新建一个冗余字段，冗余字段的值使用两个字段的值拼接而成（如区域+年月拼成zone_yyyymm字段）。

• 拆分原则七：拆分算法的选择和合理性评判：按照选定的算法拆分后每个库中单表不建议超过1000万

• 拆分原则八：能不拆的就尽量不拆。如果某个表不跟其他表关联查询，数据量又少，直接不拆分，使用单库即可。

4.5  DataNode分布问题

DataNode代表MySQL数据库上的一个Database，因此一个分片表的DataNode的分布可能有以下几种：

• 都在一个DataHost上

• 虽在几个DataHost上，但有连续性，比如dn1到dn5在Server1上，dn6到dn10在Server2上，依次类推

• 在几个DataHost上，但均匀分布，比如dn1,dn2,d3分别在Server1,Server2,Server3 上，dn4到dn5又重复如此

原则上不建议第一种分布策略。对于范围分片来说，在大多数情况下，最后一种情况最理想，因为当一个表的数据均匀分布在几个物理机上的时候，跨分片查询或者随机查询，都是到不同的机器上去执行，并行度最高，IO竞争也最小，因此性能最好。

当我们有几十个表都分片的情况下，怎样设计DataNode的分布问题，就成了一个难题，解决此难题的最好方式是试运行一段时间，统计观察每个DataNode上的SQL执行情况，看是否有严重不均匀的现象产生，然后根据统计结果，重新映射DataNode到DataHost的关系。

MyCAT 1.4增加了distribute函数，可以用于Table的dataNode属性上，表示将这些dataNode在该Table的分片规则里的引用顺序重新安排，使得他们能均匀分布到几个DataHost上：

<table name="user" primaryKey="user_id" dataNode="distribute(dn1$0-35,dn2$0-35)" rule="latest-month-createdate" />

其中dn1xxx与dn2xxx是分别定义在DataHost1上与DataHost2上的36个分片。

4.6  目前的局限性

部分SQL还不能很好的支持

• 除了分片规则相同、ER分片、全局表、以及SharedJoin，其他表之间的Join问题目前还没有很好的解决，需要自己编写Caltlet来处理。

• 不支持Insert into 中不包括字段名的SQL。

• insert into tableA select from tableB 的SQL，若tableA 与tableB不是相同的分片规则，则不被支持，此时会涉及到跨分片转移。

• 跨分片的事务，目前只是弱XA模式，还没完全实现XA模式。

• 分片的Table，目前不能执行Lock Table这样的语句，因为这种语句会随机发到某个节点，也不会全部分片锁定，经常导致死锁问题，此类问题常常出现在SqlDump导入导出SQL数据的过程中。

• 目前SQL解析器采用Druid,在某些SQL例如order by，group by，sum ，count条件下，如果这类操作会出现兼容问题，比如：

select t.name as name1 from user order by t.name

• 这条语句Select 列的别名与order by 不一致解析器会出现异常，所以在对列加别名时候要注意这类操作异常，特别是由jpa等类似的框架生成的语句会有兼容问题。

开发框架方面，虽然支持Hibernate，但不建议使用Hibernate，而是建议MyBatis以及直接JDBC操作，原因Hibernate无法控制SQL的生成，无法做到对查询SQL的优化，导致大数量下的性能问题。此外，事务方面，建议自己手动控制，查询语句尽量走自动提交事务模式，这样MyCAT的读写分离会被用到，提升性能很明显。

01|数据库专题

02|Kettle插件专题

03|方法论专题

虽小编一己之力微弱，但读者众星之光璀璨。小编敞开心扉之门，还望倾囊赐教原创之文，期待之心满于胸怀，感激之情溢于言表。一句话，欢迎联系小编投稿您的原创文章！