mycat解决mysql的分库分表原理

MyCat

产生背景

随着公司不断发展，公司业务的不断运行，产生的数据也会与日俱增，更何况公司规模和业务的不断扩大，数据库的瓶颈所带来的困扰也越发明：
         数据库的连接(mysql默认连接100个)；
         表数据量()；
         硬件资源限制（QPS(每秒事务数)/TPS(每秒查询量)）；

什么是MYCAT

1. 一个彻底开源的，面向企业应用开发的大数据库集群；
2. 支持事务、ACID、可以替代MySQL的加强版数据库；
3. 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群；
4. 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server；
5. 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品；
6. 一个新颖的数据库中间件产品；

        Mycat是一个强大的数据库中间件，不仅仅可以用作读写分离、以及分表分库、容灾备份，而且可以用于多租户应用开发、云平台基础设施、让你的架构具备很强的适应性和灵活性，借助于即将发布的 Mycat智能优化模块，系统的数据访问瓶颈和热点一目了然，根据这些统计分析数据，你可以自动或手工调整后端存储，将不同的表映射到不同存储引擎上，而整个应用的代码一行也不用改变。

官方图解来一张：

Mycat原理

拦截sql→解析sql→数据资源管理→数据源分配→请求/响应→结果整合

MyCat中概念的理解

逻辑库：通常对实际应用来说，并不需要知道中间件的存在，业务开发人员只需要知道数据库的概念，所以数据库中间件可以被看做是一个或多个数据库集群构成的逻辑库。
逻辑表：对应用来说，读写数据的表就是逻辑表。逻辑表，可以是数据切分后，分布在一个或多个分片库中，也可以不做数据切分，不分片，只有一个表构成。
分片表：分片表，是指那些原有的很大数据的表，需要水平切分到多个数据库的表，这样，每个分片都有一部分数据，所有分片构成了完整的数据。
非分片表：一个数据库中并不是所有的表都很大，某些表是可以不用进行切分的，非分片是相对分片表来说的，就是那些不需要进行数据切分的表。
ER表：子表的记录与所关联的父表记录存放在同一个数据分片上，即子表依赖于父表，通过表分组（Table Group）保证数据Join不会跨库操作。
全局表：所有的分片都有一份数据的拷贝，所有将字典表或者符合字典表特性的一些表定义为全局表。系统表(变动不频繁，规模不大)。
分片节点(dataNode)：数据切分后，每个分片节点（dataNode）不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点（dataNode）所在的机器就是节点主机,为了规避单节点主机并发数限制，尽量将读写压力高的分片节点（dataNode）均衡的放在不同的节点主机（dataHost）。
分片规则(rule)：一个大表被分成若干个分片表，就需要一定的规则。
全局序列号(sequence)：数据切分后，原有关系数据库中的主键约束在分布式条件下将无法使用，因此需要引入外部机制保证数据唯一性标识，这种保证全局性的数据唯一标识的机制就是全局序列号。

MyCat的主要作用

主从复制

Master对外提供写操作，salve对外提供读操作。当数据发生变化时会master将数据同步给slave。

流程：
1.master将操作记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）
2.Slave通过I/O Thread异步将master的binary log events拷贝到它的中继日志(relay log)；
3.Slave执行relay日志中的事件，匹配自己的配置将需要执行的数据，在slave服务上执行一遍从而达到复制数据的目的。

主从配置binarylog延迟产生？

1.当master tps高于sqlve线程所能承受的范围
2.网络延迟
3.磁盘io读写耗时

判断binarylog延迟

1.通过show slave status \G；sends_behind_master 0
2.mk-heartbeat

解决延迟问题

1.配置更好的硬件资源；
2.把iothread改成多线程模式（mysql5.6库进行多线程方式，5.7GTID进行多线程方式）；
3.应用程序自己判断（mycat）；

垂直切分

将一个很多表的大库切分成几个小库分到不同的节点上，具体步骤如下：
        1.收集分析业务模块间的关系；
        2.复制数据库到其他实例；
        3.配置mycat垂直分库；
        4.通过mycat访问db；
        5.删除原库中已迁移表；

优点：  拆分简单；
            应用程序模块清晰整合容易；
            维护方便； 

缺点：被拆分成多个节点的数据无法实现跨库查询，跨库查询会报错。
                方案1：通过java程序调用多个数据库后将数据组装返回；
                方案2：将库中的字段冗余避免出现跨库查询；
                方案3：将变化不大的表设置成全局表；
            库切分后但是大表仍然会有查询瓶颈；
            切分后扩展性有限制；  

水平切分

垂直切分解决不了一些大表大量访问数据的问题，因此需要水平切分。所谓的水平切分，就是将某些大表的数据按照规则分别分散到a1,a2,a3....这些表上，然后访问数据就到a1,a2,a3这些表上访问。
分片原则：1.能不切分尽量不要切分；2.选择合适的切分规则和分片键；3.避免使用join跨分片查询（E-R表关联或者全局表）；
当根据分片键进行查询或修改时，mycat会根据分片键运算规则将分片键发送到相应的数据库上。当不是用分片键进行curd时，mycat会将请求发送到所有的数据库上然后返回结果。

水平切分步骤如下：
        1.根据业务状态确定要进行水平切分的表；
        2.分析业务模型选择分片键以及分片算法；
        3.使用mycat部署分片集群；
        4.测试分片集群；
        5.业务及数据迁移；

如何选择分片键
        尽可能的比较均匀分布数据的各个节点上；
        该业务字段是最频繁的或者最重要的查询条件； 
        选择完分片键之后还需要对关联的表进行E-R表操作（就是将有关联其他表数据分配到同一个节点上，避免了join操作）；       

控制数据库连接数量

无论是将数据库水平切分还是垂直切分，都可以在mycat上修改数据库的连接数量，对数据库的请求连接会被发送到不同的节点服务器上。可以提高数据库的连接瓶颈。

安装mycat

下载并解压mycat

下载：wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
解压：tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

安装java运行环境jdk1.7

下载 wget https://download.oracle.com/otn/java/jdk/8u221-b11/230deb18db3e4014bb8e3e8324f81b43/jdk-8u221-linux-x64.tar.gz
解压  tar zxf jdk-8u221-linux-x64.tar.gz

新建mycat运行系统账号

添加mycat账户：useradd mycat
将mycat目录给mycat用户：chown mycat:mycat -R mycat/

配置系统环境变量

修改mycat/conf/wrapper.conf配置文件
        根据需求修改内存配置：wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
添加环境变量（/etc/profile）
        export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mycat/bin:/usr/local/jdk1.8.0_221/bin
        export JAVA_HOME=/usr/local/jdk1.8.0_221
        export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
        export MYCAT_HOME=/usr/local/mycat
使环境变量生效：source /etc/profile
查看jdk是否安装成功：java -version 
启动：mycat start    
查看mycat状态：ps -f|grep mycat
查看运行日志：mycat/logs/wrapper.log 

修改mycat启动参数

server.xml：
        配置系统相关参数。     

<system>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
    <property name="sequnceHandlerType">2</property>
    <property name="useCompression">1</property> <!--1为开启mysql压缩协议-->
    <property name="fakeMySQLVersion">5.6.20</property> <!--设置模拟的MySQL版本号-->
    <property name="processorBufferChunk">40960</property>
    <property name="processors">1</property>
    <property name="processorExecutor">32</property>
    <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
    <property name="processorBufferPoolType">0</property>
    <!--默认是65535 64K 用于sql解析时最大文本长度 -->
    <property name="maxStringLiteralLength">65535</property>
    <property name="backSocketNoDelay">1</property>
    <property name="frontSocketNoDelay">1</property>
    <property name="processorExecutor">16</property>
    <property name="serverPort">8066</property>
    <property name="managerPort">9066</property>
    <property name="idleTimeout">300000</property>
    <property name="bindIp">0.0.0.0</property>
    <property name="frontWriteQueueSize">4096</property>
    <property name="processors">32</property>
    <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
    <property name="handleDistributedTransactions">0</property>
    <!--off heap for merge/order/group/limit      1开启   0关闭 -->
    <property name="useOffHeapForMerge">1</property>
    <!--单位为-->
    <property name="memoryPageSize">1m</property>
    <!--单位为k -->
    <property name="spillsFileBufferSize">1k</property>
    <property name="useStreamOutput">0</property>
    <!--单位为m-->
    <property name="systemReserveMemorySize">384m</property>
    <!--是否采用zookeeper协调切换  -->
    <property name="useZKSwitch">true</property>
</system>

        配置用户访问权限。

<user name="root">
    <!-- <property name="usingDecrypt">1</property> --><!-- 加密时需要用到此标签 -->
    <property name="password">123456</property>
    <property name="schemas">db_01,db_02,db_03</property><!--能访问的逻辑库-->
    <property name="readOnly">true</property>
    <!-- 表级 DML 权限设置 -->
    <privileges check="true"> <!-- false不生效 -->
            <schema name="db_01" dml="0110" ><!--0110默认-->
                    <table name="tb01" dml="0000"></table> <!--没有任何权限-->
                    <table name="tb02" dml="1111"></table> <!--1111分别表示insert,update,select,delete，1表示有权限，0表示没权限-->
            </schema>
    </privileges>
</user>
可以在mycat/lib中使用java -cp Mycat-server-1.6-RELEASE.jar io.mycat.util.DecryptUtil 0:root:123456获取加密密码。填在上面红色标签中。

        配置sql防火墙及sql拦截功能。

<firewall>
   <whitehost><!--白名单-->
      <host host="127.0.0.1" user="mycat"/>
      <host host="127.0.0.2" user="mycat"/>
   </whitehost>
     <blacklist check="false"><!--黑名单-->
         <host host="127.0.0.3" user="mycat"/>
         <host host="127.0.0.4" user="mycat"/>
     </blacklist>
</firewall>

<system>
    <property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
    <property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property>
    <property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>

schema.xml：
    对于逻辑库逻辑表的配置，是mycat最为关键的配置；
    1.配置逻辑库逻辑表
    2.配置逻辑表所存储的数据节点
    3.配置数据节点所对应的物理数据库服务器信息

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">  <!--name属性定义逻辑库的名字；sqlMaxLimit查询限制，配置-1表示不限制；checkSQLschema属性判断是否检查发给mycat的sql是否含有库名，不建议写的sql语句中含有库名-->
    <table name="travelrecord"  primaryKey="ID" type="global"  dataNode="dn1,dn2,dn3" rule="rule1" /> <!--name逻辑表名称，唯一；primaryKey定义逻辑表的主键，真实表的唯一列；dataNode表示<dataNode>切分的实际节点表；rule表示在rule.xml中的tableRule中的name值；type="global" 表示表示全局表--> </table>
如果表中除了主键还有其他的唯一键，可以将其他的为一间作为mycat的主键，因为mycat会将这个主键和node几点之间做一个缓存，等到下次如果用到这个主键条件查询时，将会直接缓存中找到弄得节点，不用做全节点扫描。
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost2" database="db2" />
<dataNode name="dn3" dataHost="localhost3" database="db3" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"> <!--balance为0不开启读写分离；1时候全部的readHost和stand by writeHost参与select语句的负载均衡（包括多主多从）；2的时候所有的readHost和wroteHost都参与select语句的负载均衡；3所有的readHost参与select语句的负载均衡；writeType为0的时候update数据的时候只update第一个，1的时候可以从多个主节点中任选一个update；switchType为-1的时候表示不自动主从切换，1的时候切换，为2的时候表示当从数据库出现读数据延迟超过slaveThreshold设定的值时，读数据直接从主数据库读取数据；-->    
         <heartbeat>select user()</heartbeat>  
         <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">   <!--写数据库 -->                  
            <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="123456" />  <!--读数据库 -->
         </writeHost>
 </dataHost>

rule.xml
     配置了每一个需要水平分表的分片规则；

<tableRule name="rule1">
        <rule>
           <columns>id</columns>
           <algorithm>func1</algorithm>
        </rule>
  </tableRule>
name属性指定分片规则的名字，这个名字要唯一；<rule>表示分片规则，<columns>表示对表中哪个字段进行分片算法，<algorithm>表示要分片的算法，取<function>的name属性；
 <function name="func1" class="io.mycat.route.function.PartitionByMurmurHash"> <!--name需要唯一，class是java算法类的全名路径-->
                <property name="seed">0</property><!-- 默认是0 -->
                <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
                <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
                <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
                <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
                        用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
 </function>

    分片算法：
        简单取模-PartitionByMod
        哈希取模-PartitionByHashMod
        分片枚举-PartitionByFileMap
        字符串范围取模分片

log4j2.xml：
    大多情况下保持默认就可以了，输出log日志格式配置如下：

<PatternLayout>
       <Pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] (%l) - %m%n</Pattern>
</PatternLayout>
%d{yyyy-MM-dd HH:mm:ss.SSS}：表示日期格式 ；%5p表示5个字符的日志级别，如：debug info 等等；[%t]表示当前执行线程；%m：表示我们写的程序的输出信息；
<asyncRoot level="info" includeLocation="true">：日志级别；mycat中有八种日志级别；从高到低分别是all<trace<debug<info<warn<error<fatal<off;

配置mycat全局序列

由于水平切分表将原本一个表切分成多个表，当insert的时候，每个表之间的自增长id没有关联将导致多个表的自增长id重复。可以用以下方式解决：
    1.本地文件方式        
            server.xml配置文件中：sequncehandlerType=0
            配置sequence_conf.properties
            使用new value for MYCATSEQ_XXX
    2.数据库方式
            sequenceHandlerType=1
            配置sequence_db_conf.properties
            使用next value for MYCATSEQ_XXX或者指定autoIncrement
    3.本地时间戳方式
            id=64位二进制（42(毫秒)+5(机器ID)+5(业务编码)+12(重复累加)）
                sequenceHandlerType=2
                配置sequence_time_conf.properties
                指定autoIncrement
    4.程序方式（java）
            Snowflake
            UUID
            redis

MyCat弱XA事物机制

当数据库水平切分成多个数据库时，发送mycat一条update语句会分发到多个切分的数据库上，mycat必须保证所有分发的update语句同时成功或者同时失败，否则将会导致数据库数据不一致的情况。
mycat采用两阶段提交策略解决数据库一致性问题。

    

为什么2PC提交？
    1.是2PC才会有事务管理器统一管理的机会；2尽可能晚地提交事务，让事务在提交前尽可能地完成所有能完成的工作，这样，最后的提交阶段将是耗时极短，耗时极短意味着操作失败的可能性也就降低。
    二阶段提交协议为了保证事务的一致性，不管是事务管理器还是各个资源管理器，每执行一步操作，都会记录日志，为出现故障后的恢复准备依据。
    Mycat 第二阶段的提交没有做相关日志的记录，所以说他是一个弱XA的分布式事务解决方案。

高可用

单点的mycat配置对系统有很大的隐患，搭建一个高可用的mycat才是追求

使用ZK记录mycat配置

    1.建立zookeeper集群(使用zookeeper需要jdk环境)；
    2.初始化mycat的配置到zk集群中；
    3.配置mycat支持zk启动；
    4.启动mycat；

使用HaProxy对MyCat负载均衡

haproxy的性能在四层负载均衡(相对应的七层负载均衡)性能比nginx牛逼
    1.安装HaProxy；
    2.使用keepalived监控Haproxy；
    3.配置HaProxy监控Mycat;
    4.配置应用通过vip访问Haproxy；

Mycat实现读写分离

    1.配置各个节点Mysql主从复制；
    2.配置mycat对后端db进行读写分离；

缺点 

1.非分片字段查询如果并不是路由字段将会把请求发送到所有的分片库表；
2.分页排序可能会产生错误的结果，需要将limit m,n改成limit 0,m+n然后对总结果limitm,n；
3.join表与表之间关联数据在同一分片库表；
4.分布式事务只能保证prepare数据的弱XA事务；
        -prepare确认后commit后某节点失效则无法保证事务的一致性。
5.mycat不支持sql语句；
        -create table like xxx/create table select xxx ——逻辑库和逻辑表无法定位节点位置和具体库；
        -跨库多表关联查询，子查询；
        -select for update/select lock in share mode 只能在一个节点上执行，达不到锁表的效果；
        -多表update或是update分片键；
        -select info outfile/into var_name  导出表语句不支持；
6.当mycat执行时候一旦执行数据是不可逆的。
7.只能支持分片表的扩缩容。（ER表还得用其他策略）
8.分片规则必须一致，只能节点扩容或者收缩。

面试题总结

1.单表数据达到多少的时候会影响数据库的查询性能？为什么？
    根据分配的运行内存，单表数据库达到5000万左右。
    即使根据主键建立的索引查询，建立索引不会压缩数据，基本多大的数据量就会建立多大的索引，索引的存储机制是硬盘，当命中索引时才将索引加载中内存中，当数据量增大时，索引必然也会增大，文件内存之间的传输和搜索的范围数纬度也会增大，导致搜索范围下降。

2.主从复制的原理描述是怎么样的，常见的形式有那些？
    原理：master将操作记录到二进制的日志binary log中，
              slave通过i/o thread异步将master的binary log events拷贝到它的中继日志relay log
              slave执行relay日志中的事件，匹配自己的配置将需要执行的数据，在slave服务上执行一遍从而达到复制数据的目的

3.垂直拆分和水平拆分有什么不同？
    垂直拆分：通过业务区分不同的表逻辑，将不同逻辑的表配置在不同的服务器中从而达到表过多分离的目的；
    水平拆分：当某个表数据量太大时，比如订单表，可以根据表中的某一个字段按照一定的逻辑将表的数据分发到不同的库表中；

4.分库分表中垂直分库方案会带来哪些问题？
    一：表于表之间的关联查询不能跨库查询。
    二：mycat不支持部分sql语句。
    三：系统复杂度。

5.分布式数据存储中间件mycat的核心流程是什么？
    一.解析sql；
    二.数据源管理；
    三.数据源分配；
    四.请求/相应；
    五.结果整合；

6.概述一下mycat？
    一.开源框架；
    二.解决分布式存储问题(分库分表读写分离)；
    三.属于数据层和业务层中间件（当成数据库来用）；

7.解释一下全局表  ER表  分片表？
    全局表：所有的分片都有一份数据的拷贝，所有将字典表或者符合字典表特性的一些表定义为全局表。系统表(变动不频繁，规模不大)。
    ER表：子表的记录与所关联的父表记录存放在同一个数据分片上，即子表依赖于父表，通过表分组（Table Group）保证数据Join不会跨库操作。
    分片表：分片表，是指那些原有的很大数据的表，需要水平切分到多个数据库的表，这样，每个分片都有一部分数据，所有分片构成了完整的数据。

8.mycat再进行分库分表后它是怎么支持联表查询的？
    分库：用全局表
    分表：E-R表
    通过注解方式实现跨库联表查询。

9.进行库表拆分时，拆分规则怎么取舍？
    1.根据业务模块进行分库；
    2.根据表的大小需求进行分表，分表规则根据具体的数据增长而定；

10.mycat中全局方案有哪些？程序自定义的全局ID有哪些？
    mycat中有  1.本地文件方式(程序一旦重启将会归零)     2.  数据库方式    3.本地时间戳方式    4.zookeeper
    程序方式：snowflake   uuid  redis 

11.一致性hash原理，好处？

    将0-2^32组成一个闭环，将节点平均散落在这个环内，当数据请求过来时，对数据进行hash算法散落在0-2^32次方某个数字A上，将这个数字A存在顺时针转动碰到的第一个节点上。
    优点：节点增减影响范围小。

12.4层负载和7层负载谁的性能更高？区别?
    4层负载性能更高
    4层负载只是做一个转发，不跟前端做一个连接，制作一个转发路由。通过发布三层的IP地址（VIP），然后加四层的端口号，来决定哪些流量需要做负载均衡，对需要处理的流量进行NAT处理，转发至后台服务器，并记录下这个TCP或者UDP的流量是由哪台服务器处理的，后续这个连接的所有流量都同样转发到同一台服务器处理。
    7层会跟前端建立一个连接，在四层的基础上（没有四层是绝对不可能有七层的），再考虑应用层的特征，比如同一个Web服务器的负载均衡，除了根据VIP加80端口辨别是否需要处理的流量，还可根据七层的URL、浏览器类别、语言来决定是否要进行负载均衡。

 

转载于:https://my.oschina.net/yaojianpeng/blog/3093083