mysql分表分库中间件-广富强博客

本节就演示如何实现分片表，更好的通过中间件提高多台数据库的读写能力。

分表分库原则

① 分库分库的原则

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

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

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

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

原则四：尽量不要在一个事务中的SQL 跨越多个分片，分布式事务一直是个不好处理的问题【原本的一个事务多条SQL语句跨多个库的上面，这样事务处理就简单一些】。

原则五：查询条件尽量优化，尽量避免select * 的方式，大量数据结果集下，会消耗大量带宽和CPU 资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引【当应用了数据库集群之后，后端的SQL语句写法更加严格，这就在于平常的开发过程中遵守规范。】。

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

总体上来说，分片的选择是取决于最频繁的查询SQL 的条件，因为不带任何Where 语句的查询SQL，会便利所有的分片，性能相对最差，因此这种SQL 越多，对系统的影响越大，所以我们要尽量避免这种SQL 的产生【分片策略是根据定义后，在实践使用过程中，统计分区后决定的】。

② SQL统计分析

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

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

采用Mycat 的SQL 拦截器机制，写一个插件，拦截所欲SQL，并进行统计分析。

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

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

③ 库内分表说明【mycat不建议，在同一个库里面存储同一个表的不同的数据，对于单点压力很大，为什么要分库分表就是为了解决海量数据压力大的问题，如果库内分表，表的数据还是在同一个库。建议使用MyCat分库和MySQL分区的方式】

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

MySQL 分区。

MERGE 表。

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

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

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

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 一个维度分区。

数据拆分原则

达到一定数量级才拆分，不到1000万不用拆，量不大不要拆。

不到800 万但跟大表有关联查询的表也要拆分，在此称为大表关联表【ER拆分】。

大表关联表如何拆：小于100 万的使用全局表；大于100 万小于800 万跟大表使用同样的拆分策略；无法跟大表使用相同规则的，可以考虑从java 代码上分步骤查询，不用关联查询，先拆除子表的数据，然后在去查询关联的数据，或者破例使用全局表。

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

拆分字段是不可修改的【拆分的规则是根据值来进行区别的，改了之后路由选择的是db2，原来是db1，db2就没有关联的表了】。

拆分字段只能是一个字段，如果想按照两个字段拆分，必须新建一个冗余字段，冗余字段的值使用两个字段的值拼接而成。

拆分算法的选择和合理性评判：按照选定的算法拆分后每个库中单表不得超过800 万，【拆分的时候在一个节点，库中的单表不能超过800万】。

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

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 上。

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

MyCat内置的常用分片规则

目前官网一共有17个分片规则，但是这里就说11种规则，其实思路大差不差，有的基本不常用。基本上都是这四大类：范围，列表，hash，混合的。

① 分片枚举

通过在配置文件中配置可能的枚举id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。

# 这个tableRule的名称保持唯一 user_id enum-int    sharding-by-enum.txt0 0

function分片函数中配置说明：

算法实现类为：io.mycat.route.function.PartitionByFileMap。mapFile 标识配置文件名称。type 默认值为0，0 表示Integer，非零表示String。defaultNode defaultNode 默认节点:小于0 表示不设置默认节点，大于等于0 表示设置默认节点为第几个数据节点。默认节点的作用：枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点如果不配置默认节点，碰到不识别的枚举值就会报错。

like this：can’t find datanode for sharding column:column_name val:ffffffff

sharding-by-enum.txt 放置在conf/下，配置内容示例：

10000=0 #字段值为10000的放到0号数据节点 10010=1

实例客户表t_customer，按照省份来分

CREATE TABLE t_customer not null, province int not null );

按省份进行数据分片，表配置

分片规则配置rule.xml，这里注意一个坑，就是tableRule 和 function 如果是多个 tableRule要放一起，function要放一起，否则会报错。

   province sharding-by-province-func   sharding-by-province.txt 0 0

sharding-by-province.txt文件中枚举分片，1.6.6是可以列举多个的，但是1.6的版本只能枚举出来3个！

1001=0 1002=1 1003=2 1004=0

server.xml配置中增加主键的序列化方式

< xml version="1.0" encoding="UTF-8" >       0          1qaz@WSX mydb

配置这个表到sequence_conf.properties，后面会讲主键值的生成，分片肯定要进行主键唯一性的处理，所以上边开启。

T_CUSTOMER.CURID=1005T_CUSTOMER.HISIDS=T_CUSTOMER.MINID=1001T_CUSTOMER.MAXID=2000

测试：先建立表，插入数据！

insert into t_customer values; insert into t_customer values; insert into t_customer values; insert into t_customer values; insert into t_customer values;

1001，1004，1005 db1数据库，1005走默认的数据库1002 db2数据库1003 db3数据库

mycat 查看到的信息

② 范围分片

此分片适用于，提前规划好分片字段某个范围属于哪个分片。

 user_idrang-long range-partition.txt0

配置说明：

mapFile 代表配置文件路径。

defaultNode 超过范围后的默认节点。

所有的节点配置都是从0 开始，及0 代表节点1。mapFile中的定义规则：

start <= range <= endrange start-end=data node indexK=1000,M=10000

配置示例

0-500M=0500M-1000M=11000M-1500M=2

或

0-10000000=010000001-20000000=1

演示实例，创建表t_company

CREATE TABLE t_company not null,members int not null);

schema.xml 配置，定义一个公司的表，根据范围来进行分片

rule.xml 配置规则

      members     range-members-count  company-range-partition.txt 0

company-range-partition.txt中分片定义

0-10=011-50=151-100=2101-1000=01001-9999=110000-9999999=2

配置这个表到sequence_conf.properties，后面会讲主键值的生成，分片肯定要进行主键唯一性的处理，所以上边开启。

T_COMPANY.CURID=1005T_COMPANY.HISIDS=T_COMPANY.MINID=1T_COMPANY.MAXID=2000000

测试演示，创建t_company表

sql语句

INSERT INTO t_company VALUES; INSERT INTO t_company VALUES; INSERT INTO t_company VALUES;

查看表内的数据信息跟之前分分库策略文件company-range-partition，放入指定的库中。

③ 按日期范围分片

此规则为按日期段进行分片。

           create_time sharding-by-date               yyyy-MM-dd      2018-01-01      2019-01-02      10

配置说明：

columns ：标识将要分片的表字段。

algorithm ：分片函数。

dateFormat ：日期格式。

sBeginDate ：开始日期。

sEndDate：结束日期。

sPartionDay ：分区天数，即默认从开始日期算起，分隔10 天一个分区。

sBeginDate,sEndDate配置情况说明：sBeginDate,sEndDate 都有指定此时表的dataNode 数量的>=这个时间段算出的分片数，否则启动时会异常：

Exception in thread "main" java.lang.ExceptionInInitializerError at io.mycat.MycatStartup.main Caused by: io.mycat.config.util.ConfigException: Illegal table conf : table [ T_ORDER ] rule function [ shardi partition size : 4 > table datanode size : 3, please make sure table datanode size = function partition size

如果配置了sEndDate 则代表数据达到了这个日期的分片后循环从开始分片插入。

没有指定 sEndDate 的情况数据分片将依次存储到dataNode上，数据分片随时间增长，所需的dataNode数也随之增长，当超出了为该表配置的dataNode数时，将得到如下异常信息：

INSERT INTO t_order VALUES ; [Err] 1064 - Can't find a valid data node for specified node index :T_ORDER -> ORDER_TIME -> 2019-02-05 -> Index : 3

示例演示

配置设置schema.xml

分片规则设置rule.xml

        order_timesharding-by-date    yyyy-MM-dd  2019-01-01   2019-02-02   20

配置这个表到sequence_conf.properties，后面会讲主键值的生成，分片肯定要进行主键唯一性的处理，所以上边开启。

T_ORDER.CURID=1005T_ORDER.HISIDS=T_ORDER.MINID=1T_ORDER.MAXID=2000000

重启mycat，进入mycat，创建表，执行sql语句

CREATE TABLE t_order  );

INSERT INTO t_order VALUES ; INSERT INTO t_order VALUES ; INSERT INTO t_order VALUES ; INSERT INTO t_order VALUES ;

查看数据分布

④ 自然月分片【这个跟③日期范围分片类似】

按月份列分区，每个自然月一个分片。

create_timesharding-by-month    yyyy-MM-dd2014-01-01

配置说明：

columns：分片字段，字符串类型

dateFormat ：日期字符串格式,默认为yyyy-MM-dd

sBeginDate ：开始日期，无默认值

sEndDate：结束日期，无默认值节点从0 开始分片

使用场景：

场景1：默认设置

节点数量必须是12 个，对应1 月~12 月“2017-01-01” = 节点0“2018-01-01” = 节点0“2018-05-01” = 节点4“2019-12-01” = 节点11

场景2 ：仅指定sBeginDate

sBeginDate = “2017-01-01” 该配置表示”2017-01 月”是第0 个节点，从该时间按月递增，无最大节点“2014-01-01” = 未找到节点“2017-01-01” = 节点0“2017-12-01” = 节点11“2018-01-01” = 节点12“2018-12-01” = 节点23

场景3：指定sBeginDate=1月、sEndDate=12月

sBeginDate = “2015-01-01” sEndDate = “2015-12-01” 该配置可看成与场景1 一致。“2014-01-01” = 节点0“2014-02-01” = 节点1“2015-02-01” = 节点1“2017-01-01” = 节点0“2017-12-01” = 节点11“2018-12-01” = 节点11

场景4：sBeginDate = “2015-01-01” sEndDate = “2015-03-01”该配置表示只有3 个节点；很难与月份对应上；平均分散到3 个节点上

“2015-01-01” = 节点0“2015-02-01” = 节点1“2015-03-01” = 节点2“2015-04-01” = 节点0“2015-05-01” = 节点1“2016-06-01” = 节点2

⑤ 取模

此规则为对分片字段进行十进制运算，来分片数据。

 user_id mod-fun     3

配置说明：

count 指明dataNode 的数量，是求模的基数。此种在批量插入时可能存在批量插入单事务插入多数据分片，增大事务一致性难度。批量插入的数据是ID

⑥ 取模范围分片

此种规则是取模运算与范围约束的结合，主要为了后续数据迁移做准备，即可以自主决定取模后数据的节点分布。

rule.xml

 user_id sharding-by-pattern   256 2 partition-pattern.txt

partition-pattern.txt

1-32=0 #余数为1-32的放到数据节点0上 33-64=1 65-96=2 97-128=3 129-160=4 161-192=5 193-224=6 225-256=7 0-0=7

配置说明：

patternValue 即求模基数。

defaoultNode 默认节点，如果配置了默认节点，如果id 非数据，则会分配在。

defaoultNode 默认节点。

mapFile 指定余数范围分片配置文件。

⑦ 二进制取模范围分片【前面说过范围，这里还有取模，等于合并起来】

本条规则类似于十进制的求模范围分片，区别在于是二进制的操作，是分片列值的二进制低10位&1111111111。此算法的优点在于如果按照10 进制取模运算，在连续插入1-10 时候1-10会被分到1-10 个分片，增大了插入的事务控制难度，而此算法根据二进制则可能会分到连续的分片，减少插入事务控制难度。二进制低10&1111111111 的结果是 0-1023 一共是1024个值，按范围分成多个连续的片``` xml

user_id

func1

2,1

256,512

> 配置说明：1. partitionCount 分片个数列表。2. partitionLength 分片范围列表>分区长度:默认为最大2^n=1024 ,即最大支持1024 分区> 约束:1. count,length 两个数组的长度必须是一致的。2. 1024 = sum)，count 和length 两个向量的点积恒等于1024> 用法例子:> 本例的分区策略：希望将数据水平分成3 份，前两份各占25%，第三份占50%。 // |<———————1024———————————>|// |<—-256—>|<—-256—>|<———-512————->| // | partition0 | partition1 |partition2 | // | 共2 份,故count[0]=2 | 共1 份，故count[1]=1 |> 如果需要平均分配设置：平均分为4 分片，partitionCount*partitionLength=1024``` xml 4 256

⑧ 范围取模分片

先进行范围分片计算出分片组，组内再求模。优点可以避免扩容时的数据迁移，又可以一定程度上避免范围分片的热点问题。综合了范围分片和求模分片的优点，分片组内使用求模可以保证组内数据比较均匀，分片组之间是范围分片可以兼顾范围查询。最好事先规划好分片的数量，数据扩容时按分片组扩容，则原有分片组的数据不需要迁移。由于分片组内数据比较均匀，所以分片组内可以避免热点数据问题。

 id rang-mod    partition-range-mod.txt 21

配置说明：

mapFile 配置文件路径。

defaultNode 超过范围后的默认节点顺序号，节点从0 开始。

partition-range-mod.txt 以下配置一个范围代表一个分片组，=号后面的数字代表该分片组所拥有的分片的数量。

0-200M=5 // 0-200万的时候数据分到5个节点上面 200M1-400M=1  //200万零1 到400万放到1个节点上面400M1-600M=4 600M1-800M=4 800M1-1000M=6

⑨ 一致性hash

一致性hash 算法有效解决了分布式数据的扩容问题。

 user_id murmur    160  2 8 0

配置说明：

此方法为直接根据字符子串计算分区号。例如id=05-100000002 在此配置中代表根据id 中从startIndex=0，开始，截取siz=2 位数字即05，05 就是获取的分区，如果没传默认分配到defaultPartition。

截取字符ASCII求和求模范围分片

此种规则类似于取模范围约束，只是计算的数值是取前几个字符的ASCII值和，再取模，再对余数范围分片。

 user_id sharding-by-prefixpattern    256 5 partition-pattern.txt

partition-pattern.txtrange start-end =data node index

#ASCII #8-57=0-9 阿拉伯数字#64、65-90=@、A-Z#97-122=a-z 1-4=0 # 余数1-4的放到0号数据节点 5-8=1 9-12=2 13-16=3 17-20=4 21-24=5 25-28=6 29-32=7 0-0=7

配置说明

patternValue 即求模基数。

prefixLength ASCII 截取的位数，求这几位字符的ASCII码值的和，再求余patternValue。

mapFile 配置文件路径，配置文件中配置余数范围分片规则。

PS：其他还有很多分片的规则，不在详细介绍，但是无论那种都离不开那4大分类：范围、散列、列表、复合。如果有自己的需要也是可以实现自己的分片算法的 function中的class，看源码了解一下，个人感觉官网就够了，不需要每个都记住，主要面试的时候有什么样的，在业务需要的能够直接拿来用，不要重复的发明。今天这里没提到主键生成，但是实例讲解的时候用到了主键生成，下次重点说下主键生成。分片的流程：

schema.xml 定义表和分片的策略 table

server.xml 定义了主键规则和登录mycat的用户

rule.xml 定义了分片的策略，分库键是那一列，分库的策略文件。