Mycat2/3---分库分表设计_mycat2单库分表

1.表分类

（1）分片表

分片表，是指那些有很大数据，需要切分到多个数据库的表，这样每个分片都有一部分数据，所有分片构成了完整的数据。

table name=”t_goods” primaryKey=”vid” autoIncrement=”true” dataNode=”dn1，dn2” rule=”rulel” />
1

（2）非分片表

一个数据库中并不是所有的表都很大，某些表是可以不用进行切分的，非分片是相对分片表来说的，就是那些不需要进行数据切分的表。

<table name=”t_node” primaryKey=”vid” autolncrement=”true” dataNode=”dn1”/>
1

示例:
商家表，数量500万内

CREATE TABLE t_shops(
    id BIGINT PRIMARY key AUTO_INCREMENT,
  name VARCHAR(100) not NULL
);
1
2
3
4

<table name ="t_shops" primaryKey="id" dataNode="dn1"/>
1

INSERT INTO t_shops(name) values('stephanie001');
1

（3）ER表

Mycat中的ER表是基于E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上，保证数据Join不会跨库操作。
ER分片是解决跨分片数据join的一种很好的思路，也是数据切分规划的一条重要规则。

<table name=”customer” primaryKey=”ID” dataNode=”dn1，dn2” rule=”sharding-by-intfile”>
    <childTable name=”orders” primaryKey=”ID” joinKey=”customer_id” parentKey=”id”>
        <childTable name=”order_items”joinKey=”order_id” parentKey=”id” />
    </childTable>
</table>
1
2
3
4
5

（4）全局表

一个真实的业务系统中，往往存在大量的类似字典表的表，这些表基本上很少变动。
问题：业务表往往需要和字典表Join查询，当业务表因为规模而进行分片以后，业务表与字典表之间的关联跨库了。
解决：Mycat中通过表冗余来解决这类表的join，即它的定义中指定的dataNode上都有一份该表的拷贝。(将字典表或者符合字典表特性的表定义为全局表。)
适合数据量小的情况（10万条以下的数据）。

<table name=”company” primaryKey=”ID” type=”global” dataNode=”dn1，dn2，dn3” />
1

示例:
省份表t_province,在各个数据节点所在的库上分别创建全局表

CREATE TABLE t_province(
    id INT PRIMARY KEY,
    name VARCHAR(100) not NULL
);
1
2
3
4

用到了几个节点，节点上都要放上全局表，不然会出问题。

<table name ="t_province" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3"/>
1

重启mycat服务
插入数据

INSERT INTO t_province(id,name) values(1001,'浙江')；
INSERT INTO t_province(id,name) values(1001,'江苏')；
INSERT INTO t_province(id,name) values(1001,'上海')；
INSERT INTO t_province(id,name) values(1001,'广东')；
1
2
3
4

三个数据节点库上都同时写入了该数据。

2.分片规则配置

分表规则定义

在conf/rule.xml中定义分片规则

<?xml version=”1.0” encoding=”UTF-8”?>
<!DOCTYPE mycat：rule SYSTEM ”rule.dtd”>
<mycat：rule xmlns：mycat=”http：//io.mycat/”>
    <tableRule name=”rulel”>
        <rule>
            #只能是1列，如果是多列，再加个字段，把多个列的值合并在一起
            <columns>id</columns>
            #分片算法
            <algorithm>funcl</algorithm>
        </rule>
    </tableRule>
    <function name=”funcl” class=”io.mycat.route.function.PartitionByLong”>
        <property name=”partitionCount”>8</property>
        <property name=”partitionLength”>128</property>
    </function>
</mycat：rule>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

tableRule标签说明：

• name属性指定唯一的名字，用于标识不同的表规则。
• 内嵌的rule标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。
  columns内指定要拆分的列名字。
  algorithm使用function标签中的name属性。连接表规则和具体路由算法。当然，多个表规则可以连接到同一个路由算法上。table标签内使用。让逻辑表使用这个规则进行分片。

function标签说明：

• name指定算法的名字。
• class指定路由算法具体的类名字。
• property为具体算法需要用到的一些属性。

3.分库分表原则

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

• 原则一：能不分就不分，1000万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。
• 原则二：分片数量尽量少，分片尽量均匀分布在多个DataHost上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。
• 原则三：分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最常用的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容。
• 原则四：尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题。
• 原则五：查询条件尽量优化，尽量避免Select*的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

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

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

（1）SQL统计分析

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

• 采用特殊的JDBC驱动程序，拦截所有业务SQL，并写程序进行分析
• 采用Mycat的SQL拦截器机制，写一个插件，拦截所欲SQL，并进行统计分析
• 打开MySQL日志，分析统计所有SQL。

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

（2）库内分表说明

对于大家经常提起的同库内分表的问题，这里做一些分析和说明，同库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络。

此外，库内分表的时候，要修改用户程序发出的SQL，可以想象一下A、B两个表各自分片5个分表情况下的JoinSQL会有多么的反人类。这种复杂的SQL对于DBA调优来说，也是个很大的问题。因此，Mycat和一些主流的数据库中间件，都不支持库内分表，但由于MySQL本身对此有解决方案，所以可以与Mycat的分库结合，做到最佳效果，下面是MySQL的分表方案：

• MySQL 分区 ；
• MERGE表(MERGE存储引擎)。

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

MySQL分区支持的分区规则有以下几种：

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

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

MysqlMerge表的优点：

• 分离静态的和动态的数据；
• 利用结构接近的的数据来优化查询；
• 查询时可以访问更少的数据；
• 更容易维护大数据集。

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

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

• 充分结合分布式的并行能力和MySQL分区表的优化；
• 可以灵活的控制表的数据规模；
• 可以两个维度对表进行分片，MyCAT一个维度分库，MySQL一个维度分区。

mycat不建议在库内分表。一个库内还会造成数据库压力大的问题，并不能解决单点压力。

4.数据库拆分原则

（1）达到一定数量级

例如：单表数量达到800万才拆分

（2）大表关联表

不到800万但跟大表(超800万的表)有关联查询的表也要拆分，在此称为大表关联表

大表关联表如何拆分：

• 小于100万的使用全局表；
• 大于100万小于800万跟大表使用同样的拆分策略；
• 无法跟大表使用相同规则的，可以考虑从java代码上分步骤查询，不用关联查询，或者破例使用全局表。

（3）破例的全局表

如item_sku表250万，跟大表关联了，又无法跟大表使用相同拆分策略，也做成了全局表。
破例的全局表必须满足的条件：没有太激烈的并发update，如多线程同时update同一条id=1的记录。虽有多线程update，但不是操作同一行记录的不在此列。多线程update全局表的同一行记录会死锁。批量insert没问题。

（5）拆分字段是不可修改的

拆分字段不能修改，否则容易造成数据不一致。

（6）拆分字段只能是一个字段

拆分字段只能是一个字段，如果想按照两个字段拆分，必须新建一个冗余字段，冗余字段的值使用两个字段的值拼接而成(如大区+年月拼成zone_yyyymm字段)。

（7）拆分算法的选择和合理性评判

按照选定的算法拆分后每个库中单表不得超过800万

（8）能不拆的就尽量不拆。

如果某个表不跟其他表关联查询，数据量又少，直接不拆分，使用单库即可。

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的关系。

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

<table name=”oc_ca11” primaryKey=”ID” dataNode=”distribute(dn1$0-372，dn2$0-372)” rule=”latest-monthcal1date”/>
1

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