mysql(四)分表分库

数据库分表分库策略

数据库分表分库原则遵循垂直拆分与水平拆分。

垂直拆分

垂直拆分就是根据不同的业务，分为不同的数据库，比如会员数据库、订单数据库、支付数据库等，垂直拆分在大型电商系统中用的非常常见。

优点：拆分后业务清晰，拆分规则明确，系统之间整合或扩展容易。

缺点：部分业务表无法join，只能通过接口方式解决，提高了系统复杂度，存在分布式事务问题。

 

水平拆分

把同一个表拆到不同的数据库中。相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某个规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式。

该方式提高了系统的稳定性跟负载能力，但是跨库join性能较差。

 

 

同库内分表

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

 

数据拆分原则

1.达到一定数量级才拆分（800 万）

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

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

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

5.拆分字段是不可修改的

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

7.拆分算法的选择和合理性评判：按照选定的算法拆分后每个库中单表不得超过800万

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

 

MyCat实现水平分片策略

mycat支持10种分片策略

1、求模算法

2、分片枚举

3、范围约定

4、日期指定

5、固定分片hash算法

6、通配取模

7、ASCII码求模通配

8、编程指定

9、字符串拆分hash解析

 

以下只讲解第一二种分片策略。

 

分片枚举

分片枚举这种规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国的省份区县固定，这类业务使用这一规则。配置如下：

1.创建数据库userdb_1、userdb_2、userdb3

2.修改partition-hash-int.txt规则

wuhan=0

shanghai=1

suzhou=2

 

分片枚举算法就是根据不同的枚举（常量），进行分类存储。

 

 

实践

创建三个数据库

分别在三个数据库创建order_info表，表里只有一个name字段、

 

 

 

修改mycat配置

partition-hash-int.txt

wuhan=0
shanghai=1
suzhou=2

 

rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
    - you may not use this file except in compliance with the License. - You
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
    - - Unless required by applicable law or agreed to in writing, software -
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
    License for the specific language governing permissions and - limitations
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
 
    <tableRule name="role">
             <rule>
                  <columns>name</columns>
                <algorithm>hash-int</algorithm>
                </rule>
    </tableRule>
	
	<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
		<property name="mapFile">partition-hash-int.txt</property>
		<property name="type">1</property>
		<property name="defaultNode">1</property>
	</function>
	
</mycat:rule>

 

 

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- TESTDB1 是mycat的逻辑库名称，链接需要用的 -->
    <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	 
	  <table name="order_info"  dataNode="dn1,dn2,dn3" rule="role" /> 
	
	</schema>
        <!-- database 是MySQL数据库的库名 -->
    <dataNode name="dn1" dataHost="localhost1" database="user_db1" />
	<dataNode name="dn2" dataHost="localhost1" database="user_db2" />
	<dataNode name="dn3" dataHost="localhost1" database="user_db3" />
    <!--
    dataNode节点中各属性说明：
    name：指定逻辑数据节点名称；
    dataHost：指定逻辑数据节点物理主机节点名称；
    database：指定物理主机节点上。如果一个节点上有多个库，可使用表达式db$0-99，     表示指定0-99这100个数据库；

    dataHost 节点中各属性说明：
        name：物理主机节点名称；
        maxCon：指定物理主机服务最大支持1000个连接；
        minCon：指定物理主机服务最小保持10个连接；
        writeType：指定写入类型；
            0，只在writeHost节点写入；
            1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个；
        dbType：指定数据库类型；
        dbDriver：指定数据库驱动；
        balance：指定物理主机服务的负载模式。
            0，不开启读写分离机制；
            1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡；
            2，所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡；
-->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- 可以配置多个主从 -->
        <writeHost host="hostM1" url="jdbc:mysql://192.168.0.108:3306?characterEncoding=UTF8" user="root" password="123456">
            <!-- 可以配置多个从库 -->
            <readHost host="hostS2" url="jdbc:mysql://192.168.0.107:3306?characterEncoding=UTF8" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

 

 

 

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
   

   <!-- 读写都可用的用户 -->
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">mycat_testdb</property>

        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <!-- 只读用户 -->
    <user name="user">
        <property name="password">user</property>
        <property name="schemas">mycat_testdb</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

 

 

启动mycat

 

使用navicat连接mycat虚拟数据库，往order_info表插入一条数据为wuhan

 然后分别打开数据库的user_db1、2、3数据库查看，可以发现，只有user_db1有数据。

 

再在mycat虚拟数据库插入一条数据为shanghai，可以发现只有在user_db2数据库有数据。

再在mycat虚拟数据库插入一条数据为suzhou，可以发现只有在user_db3数据库有数据。

 

说明实现了分表分库。

 

这种分库分表方式当在查询某个地区时，mycat会直接查对应的数据库，所以效率会很高。

而如果是一起查询之类的，mycat底层会执行多条语句，效率相对会低一些。

 

 

 

 

求模算法实现

根据id进行十进制求模运算，运算结果为分区索引。此方法试用于提前规划好分片字段某个范围属于哪个分片，数据库节点分片数量无法更改。

 

 

 

 

实践

分别在user_db1、2、3三个数据库创建user_info表

CREATE TABLE `user_info` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

修改mycat配置文件

rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
    - you may not use this file except in compliance with the License. - You
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
    - - Unless required by applicable law or agreed to in writing, software -
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
    License for the specific language governing permissions and - limitations
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
 
   <tableRule name="role">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
 
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!--指定分片数量，不可以被更改-->
        <property name="count">3</property>
</function>

	
</mycat:rule>

 

 

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- TESTDB1 是mycat的逻辑库名称，链接需要用的 -->
    <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	 
	<table name="user_info" dataNode="dn1,dn2,dn3" rule="role"/>
	
	</schema>
        <!-- database 是MySQL数据库的库名 -->
    <dataNode name="dn1" dataHost="localhost1" database="user_db1" />
	<dataNode name="dn2" dataHost="localhost1" database="user_db2" />
	<dataNode name="dn3" dataHost="localhost1" database="user_db3" />
    <!--
    dataNode节点中各属性说明：
    name：指定逻辑数据节点名称；
    dataHost：指定逻辑数据节点物理主机节点名称；
    database：指定物理主机节点上。如果一个节点上有多个库，可使用表达式db$0-99，     表示指定0-99这100个数据库；

    dataHost 节点中各属性说明：
        name：物理主机节点名称；
        maxCon：指定物理主机服务最大支持1000个连接；
        minCon：指定物理主机服务最小保持10个连接；
        writeType：指定写入类型；
            0，只在writeHost节点写入；
            1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个；
        dbType：指定数据库类型；
        dbDriver：指定数据库驱动；
        balance：指定物理主机服务的负载模式。
            0，不开启读写分离机制；
            1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡；
            2，所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡；
-->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- 可以配置多个主从 -->
        <writeHost host="hostM1" url="jdbc:mysql://192.168.0.108:3306?characterEncoding=UTF8" user="root" password="123456">
            <!-- 可以配置多个从库 -->
            <readHost host="hostS2" url="jdbc:mysql://192.168.0.107:3306?characterEncoding=UTF8" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

 

 

启动mycat

1、使用navicat连接mycat虚拟数据库，往user_info表插入一条id为1，name字段为asd的数据。

可以发现只在user_db2数据库有数据。

2、使用navicat连接mycat虚拟数据库，往user_info表插入一条id为2，name字段为qwe的数据。

可以发现只在user_db3数据库有数据。

3、使用navicat连接mycat虚拟数据库，往user_info表插入一条id为3，name字段为zxc的数据。

可以发现只在user_db1数据库有数据。

 

说明实现了分库分表。

 

这种分库分表方式，当根据id查询的时候，mycat会根据id的值取模对应到数据库，然后直接在对应的数据库查，效率会高一些。