白菜Java自习室涵盖核心知识

MySql 分库分表解决方案 MyCat 系列（一）
MySql 分库分表解决方案 MyCat 系列（二）
MySql 分库分表解决方案 MyCat 系列（三）
MySql 分库分表解决方案 MyCat 系列（四）
MySql 分库分表解决方案 MyCat 系列（五）

1. Mycat 实践注意

1.1. 分表分库原则

分表分库虽然能解决大表对数据库系统的压力，但它并不是万能的，也有一些不利之处，因此首要问题是，分不分库，分哪些库，什么规则分，分多少分片。

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

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

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

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

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

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

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

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

采用特殊的 JDBC 驱动程序，拦截所有业务 SQL，并写程序进行分析；
采用 Mycat 的 SQL 拦截器机制，写一个插件，拦截所欲 SQL，并进行统计分析；
打开 MySQL 日志，分析统计所有 SQL；
现在各种云（阿里云、华为云等）提供的 RDS 自带 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 一个维度分区。

1.2. 数据拆分原则

达到一定数量级才拆分（800 万）；
不到 800 万但跟大表（超 800 万的表）有关联查询的表也要拆分，在此称为大表关联表；
大表关联表如何拆：小于 100 万的使用全局表；大于 100 万小于 800 万跟大表使用同样的拆分策略；无法跟大表使用相同规则的，可以考虑从 java 代码上分步骤查询，不用关联查询，或者破例使用全局表。
破例的全局表：如 item_sku 表 250 万，跟大表关联了，又无法跟大表使用相同拆分策略，也做成了全局表。破例的全局表必须满足的条件：没有太激烈的并发 update，如多线程同时 update 同一条 id=1 的记录。虽有多线程 update，但不是操作同一行记录的不在此列。多线程 update 全局表的同一行记录会死锁。批量 insert 没问题。
拆分字段是不可修改的；
拆分字段只能是一个字段，如果想按照两个字段拆分，必须新建一个冗余字段，冗余字段的值使用两个字段的值拼接而成（如大区+年月拼成 zone_yyyymm 字段）；
拆分算法的选择和合理性评判：按照选定的算法拆分后每个库中单表不得超过 800 万；
能不拆的就尽量不拆。如果某个表不跟其他表关联查询，数据量又少，直接不拆分，使用单库即可。

1.3. 后端存储的选择

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

对于交易型的数据表，可以考虑 Mysql 官方稳定版，若交易型的数据表要求可靠性非常高，比如是替代 Oracle，也可以选择 Galera Cluster 这种高可用的方案，他以一定的写入性能损失带来了数据的高可用和高并发访问。
根据数据的可靠性要求，可以采用各种数据同步方案，比如 1 主多从，读写分离提升数据表的读的并发能力。
部分表可以用 NoSQL 方式存储，而前端访问方式不变，Mycat 支持后端 MongoDB 和很多 NoSQL 系统，以提升查询能力。
部分表可以采用 MySQL 内存表，来提升查询和写入速度，替代部分复杂缓存方案。

1.4. 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="oc_call" primaryKey="ID" dataNode="distribute(dn1$0-372,dn2$0-372)" rule="latest-month-calldate" /> 
复制代码

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

2. Mycat 的路由与分发流程

2.1. 路由的作用

从原理上来看，可以把 mycat 看成一个 sql 转发器。
mycat 接收到前端发来的 sql，然后转发到后台的 mysql 服务器上去执行。但是后面有很多台 mysql 节点（如 dn1，dn2，dn3），该转发到哪些节点呢？这就是路由解析该做的事情了。

路由能保证 sql 转发到正确的节点。转发的范围是刚刚好，不多发也不少发。多发会出现两种问题：

浪费性能：比如一个 select * from orders where pro=‘wuhan’这个语句，只有 dn1 节点，能查到数据，如果将语句同时转发到 dn1、dn2、dn3 三个节点，这样的范围就多发了，性能上是一种浪费。
找不到表：如果新增了一个节点 dn4，但是 orders 的 datanode 范围只是 dn1,dn2,dn3，如果同时转发到 dn1、dn2、dn3、dn4 四个节点，则发到 dn4 执行时会返回 table orders not exists。少发则会出现结果集不全的问题，如 select * from orders 如果只转发到 dn1，只会返回 dn1 上的结果集，dn2、dn3 上的结果集得不到。

2.2. 路由解析器

解析器指的是 sql 解析器，mycat1.3 之前使用的解析器为 fdb parser(FoundationDB SQL Parser)，从 1.3 开始引入 druid 解析器，从 1.4 开始去掉了 fdbparser，只保留 druidparser 方式。

fdbparser 解析器存在的问题：

修改解析器源码的门槛太高。使用了 javacc 解析器，如果要修改解析器的源码必须搞清楚 javacc 的原理（修改解析器源码是有时碰到不支持的语法，要修改解析器来支持）。
没有好的 api 接口获取 ast 语法树中的表名、拆分字段条件等，所以路由解析时的代码很难有好的结构，就是写的很让人看不懂。
支持的语句太少。如 insert into …. On duplicate key update….，带注释的 create table 语句不支持，还有很多就不列举了。
解析性能很差。我们公司的 sql 一般都很长（select 语句），一个长点的 sql 解析花了 3、4 秒解析出 ast 语法树，这个在业务上无法让人忍受。

几种解析器性能对比

对 fdbparser、JSqlParser、druidparser3 种解析器做性能对比，对同一个 sql 语句，使用 3 种解析器解析出 ast 语法树（这是编译原理上的说法，在 sql 解析式可能就是解析器自定义的 statement 类型），执行 10 万次、
100 万次的时间对比。

10 万次：druid 比 fdbparser 快 10 倍，比 JSQLParser 快 6 倍。 
100 万次：druid 比 fdbparser 快 15 倍，比 JSQLParser 快近 10 倍。
复制代码

2.3. Druid 路由解析的两种方式

Druid 解析有两种方式：vistor 方式和 statement 方式。

Vistor 方式的用法

String sql = “select * from tableName”; 
MySqlStatementParser parser = new MySqlStatementParser(sql); 
SQLStatement statement = parser.parseStatement(); 
MycatSchemaStatVisitor visitor = new MycatSchemaStatVisitor(); 
stmt.accept(visitor); 
复制代码

经过上面的步骤后，你可以很方便的从 visitor 中获取表名、条件、表别名 map、字段列表、值类表等信息。
用这些信息就可以做路由计算了。

Statement 方式的用法

String sql = “select * from tableName”; 
MySqlStatementParser parser = new MySqlStatementParser(sql); 
SQLStatement statement = parser.parseStatement(); 
SQLSelectStatement selectStmt = (SQLSelectStatement) statement; 
复制代码

然后就可以从 selectStmt 里面得到想要的信息去了。
如果 sql = “delete from tableName”; 就要转型为
MySqlDeleteStatement deleteStmt = (MySqlDeleteStatement) statement。

2.4. 路由的计算

2.4.1. 路由计算接口

路由计算的入口方法为 io.mycat.route.RouteService 类中的 route 方法。方法签名如下：

public RouteResultset route(SystemConfig sysconf, SchemaConfig schema,int sqlType, 
String stmt, String charset, ServerConnection sc) throws SQLNonTransientException 
复制代码

2.4.2. 路由计算简要数据流图

路由计算数据流.jpg

说明：输入一个 sql，经过路由计算，输出路由结果。

该图实际是对路由接口的一个简化。路由接口中还包含 SystemConfig 、SchemaConfig 、sqlType、 charset、ServerConnection 等其他输入参数，但对于路由计算来说，这些参数都不是最主要参数。如
SystemConfig 、SchemaConfig 两个参数，完全可以不用传入，我们可以直接用其他方式获取，如:

SystemConfig sysconf =MycatServer.getInstance().getConfig().getSystem(); 
SchemaConfig schema = MycatServer.getInstance().getConfig().getSchemas().get(sc.getSchema()); 
复制代码

这些参数可以理解为一些次要参数（对路由计算本身次要，但是对其他流程有用，至于具体用处此处不做为重点），另一个需要传这些参数的原因，路由计算的流程比较长，要经过很多个方法的调用，如果每个方法中都通过曲折的途径去计算获取这些参数也是一种性能损耗。

2.4.3. 路由计算分解数据流图

路由计算数据流分解.jpg

其中 conditions 中每个 condition 为<表名、字段名、字段值>的三元组。

2.4.4. 路由计算流程

路由解析总体流程：

路由解析总体流程.jpg

其中 RouteStrategy 路由为子流程，见 RouteStrategy 路由子流程对其展开讲解。HintHandler 路由也是子
流程，但非主流程故本文不做重点讲解。路由解析序列图：

路由解析序列图.png

路由解析入口都从 RouteService 类的 route 方法进入，然后根据是否有注解决定是走 HintHandler 还是
RouteStrategy 进行路由解析。RouteStrategy 路由子流程：

RouteStrategy 路由子流程.jpg

该流程是 fdbparser 和 druidparser 两种解析策略的公共流程。该流程封装在 AbstractRouteStrategy 类的 route 方法中，相当于两种策略的模板方法。子流程“Ast 语法树解析”对应 routeNormalSqlWithAST 方法，下一节将对 ast 语法树解析流程再展开讲解（以 DruidMysqlRouteStrategy 策略类为例）。

DruidMysqlRouteStrategy 的 AST 语法树解析流程：

AST 语法树解析流程.jpg

DruidParser 解析子流程：

DruidParser 解析子流程.jpg

此处 DruidParser 解析的含义说明：DruidParser 解析指的是利用 ast 语法树（SQLStatement，这是 druid 解析器已经解析出来的）解析出表名、条件表达式、字段列表、值列表等信息，用于我们计算路由的过程。该流程封装在 DefaultDruidParser 类的 parser 方法中。

2.4.5. 路由计算的核心要素

sql 中包含的表名；
sql 中包含的条件（Conditons），每个 Condition 是一个<表名、字段名、字段值>的 3 元组。
表对应的 schema。
表是否分片，如果分片，分片字段是什么？分片算法是什么？第 4 点的信息都可以根据第 3 条计算获得。

有以上一些数据就能计算出路由，所以路由计算需要解决以下问题：

从 sql 语句中提取出表名、条件（字段、字段所属表、字段值）。有了表名、条件，再根据表的分片规则就可以计算出准确的路由了。

2.4.6. 单个表的路由计算

单个表的路由计算.jpg

无表语句的路由计算

如 select 1 语句，返回 schema 的任意一个 dataNode 即可。

// 没有 from 的的 select 语句或其他 
if(druidParser.getCtx().getTables().size() == 0) { 
return RouterUtil.routeToSingleNode(rrs, schema.getRandomDataNode(), druidParser.getCtx().getSql());
复制代码

2.4.7. 多个表的路由计算

多个表的路由计算.jpg

多表路由计算中有子流程“单表路由计算”，这个子流程引用了上面的单表路由计算流程。

2.4.8. 全局表的路由计算

全局表 insert、update 语句：路由到所有节点。全局表 select 语句：路由到任意一个节点。

// 全局表 
if(tc.isGlobalTable()) {
    if(isSelect) { 
        rrs.setCacheAble(false); 
        return routeToSingleNode(rrs, tc.getRandomDataNode(),ctx.getSql()); 
    } else { 
        return routeToMultiNode(false, rrs, tc.getDataNodes(), ctx.getSql()); 
    } 
} 
复制代码

2.4.9. or 语句的路由计算

or 语句的路由需要特殊设计和处理，如果使用一般的计算流程，会出现逻辑错误，导致查询结果错误。如下面的场景：

travelrecord 表为分片表，其按照 id 范围分片，id 在 1—-2000000 范围内在第一分片，id 在 2000001—- 5000000 在第二分片，对于 select * from student where id = 1 or 1=1;如果按照常规的计算方式，只能路由到
第一分片，这样查询到的结果就是错误的。

or 语句问题解决方案思想—等价替换

使用 union 语句拆分 or 语句的等价替换

这个等价替换应该是大家都知道的。

Select * from travelrecord where id = 1 or id = 5000001 ;

等价于以下语句： 

Select * from travelrecord where id = 1 unioin Select * from travelrecord where id = 5000001 ;
复制代码

Union 语句的结果集并集等价于路由的并集

这个等价没有明确的理论基础，但是我们可以反证法证明：如果路由集合不同，那么结果集必然不同，所以结果集相同，路由集合必然相同。

Select * from travelrecord where id = 1 or id = 5000001 ;

的路由集合等价于 

Select * from travelrecord where id = 1 ; 的路由集合

与 Select * from travelrecord where id = 5000001 ; 的路由集合的并集。
复制代码

最终演变成对 Select * from travelrecord where id = 1 和 Select * from travelrecord where id =
5000001 两个语句分别求路由，然后取并集。

or 语句路由解析数据结构分解

每碰到一个 where 条件，如果这个 where 条件中有 or，就把整个 where 条件作为一个单元 WhereUnit，如果这个 WhereUnit 永真（类似 or 1=1 , 2>1 之类的），抛弃（抛弃 where 条件后就是全路由，如 select * from tableName,不带任何条件，就是路由到所有节点）。每个 WhereUnit 根据 or 拆分成多个 splitedExpr，构成 splitedExprList。每个 splitedExpr 中都是一些 and 相连的条件（如 classId= 1 and age >20）。

WhereUnit 拆分时使用逐步分解的过程，因为一个 where 条件中可能有多个 or，每个 or 都有 left 表达式和 right 表达式，left 和 right 中必然有一个是不可再拆的，而另一个可能还可再拆，所以逐步拆分，直到不可再拆分（没有了 or）。

2.4.10. 系统语句的路由计算

主要有 select @@xxx、show 语句、desc 等语句。

比如：

show tables; 
show full tables from databaseName; 
show fields from tableName; 
show variables; 
复制代码

这些语句暂时没有使用 sql 解析器进行解析，而是通过字符串解析来特殊处理的，可以考虑使用。