基于mycat单库分表的实现与测试

  1. 首页
  2. 数据库
  3. 正文

有个项目单表的记录数量已经高达千万，几秒到几十秒的SQL常用，想尝试下单库分表。
目前上手的中间件是mycat，先在测试环境下进行了相关业务的操作模拟。
总体来讲步骤为
1、部署MYCAT
2、配置MYCAT
3、旧数据表拆分
4、运行MYCAT
5、总数查询、排序LIMIT，写入
前后有不少失败反复，现只写结果就把正常之后的记录下来。

1、部署MYCAT

mycat依赖于java环境，务必确认更新环境。

wget https://www.isres.com/file/jdk-9.0.4_linux-x64_bin.tar.gz
tar -zxvf jdk-9.0.4_linux-x64_bin.tar.gz
cp -r jdk-9.0.4 /usr/local/
vi /etc/profile
#写入
export JAVA_HOME=/usr/local/jdk-9.0.4
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

source /etc/profile
[root@xuniji logs]# which java
/usr/local/jdk-9.0.4/bin/java

部署就是下载解压

wget http://dl.mycat.io/1.6.7.3/20190927161129/Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz
tar -zxvf Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz
mv mycat /usr/local/

2、配置MYCAT
涉及的文件server.xml schema.xml sequence_conf.properties sequence_db_conf.properties rule.xml
如果使用默认dn1这样的命名， sequence_db_conf.properties应该已经一致

/usr/local/mycat/conf

server.xml配置

#主键的设置方式，0为写在本地配置文件中

<property name="sequnceHandlerType">0</property>

#配置访问用户

<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
</user>

<user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
</user>

schema.xml 配置

#配置数据库相关

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!--逻辑数据库名-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- auto sharding by id (long) -->
                <!--如果不分表，逻辑表对应物理表，表名字要一样-->
                <!--<table name="work_data" dataNode="dn1" />-->
                
                <!--单库分表 逻辑表名work_data 分三个表 其物理表要存在一样表名的子表 -->
                <table name="work_data" primaryKey="id" autoIncrement="true" subTables="work_data_$1-3" dataNode="dn1" rule="mod-long" />

                <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> -->
        </schema>
        
        
        <!--注释部分-->
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <!--dn1真实物理数据库-->
        <dataNode name="dn1" dataHost="localhost" database="geci" />
        <!--<dataNode name="dn2" dataHost="localhost1" database="db2" />-->
        <!--<dataNode name="dn3" dataHost="localhost1" database="db3" />-->
        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
         <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
        <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
        <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->
        
        
        
        <dataHost name="localhost" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- 真实数据库连接 -->
                <writeHost host="127.0.0.1" url="localhost:3306" user="root"
                                   password="1fdse901@33">
                </writeHost>
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
        <!--以下全注释掉-->
        <!--
                <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
                <heartbeat>             </heartbeat>
                 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"  password="jifeng"></writeHost>
                 </dataHost>

          <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"   dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
                <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
                <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"       password="123456" > </writeHost> </dataHost>

                <dataHost name="jdbchost" maxCon="1000"         minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
                <heartbeat>select       user()</heartbeat>
                <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

                <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
                <heartbeat> </heartbeat>
                 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"         password="jifeng"></writeHost> </dataHost> -->

        <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
                dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
                url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
                </dataHost> -->
</mycat:schema>

sequence_conf.properties 配置

#主要配置了自增主键的值，也就是在写入的时候，MYCAT会在这里取主键。

#Fri Nov 08 23:27:21 CST 2019
WORK_DATA.MAXID=1000000
WORK_DATA.CURID=665451
WORK_DATA.HISIDS=
WORK_DATA.MINID=1

rule.xml配置

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
            <!-- how many data nodes -->
            <property name="count">50</property>
</function>

3、旧数据表拆分

测试上旧表有60W数据，执行手工拆分

分表设计
1-300000 work_data_1
CREATE TABLE work_data_1 SELECT * FROM work_data where id <=300000

300001-500000 work_data_2
...
500001-800000 work_data_3
...

4、运行MYCAT

/usr/local/mycat/bin/mycat (start|restart|stop)

日志目录

5、测试

写入
13.png
查询与查询计划
99.png
12.png

6、limit默认是限定100的，不指定的话， 本人将继续在生产环境数据级上测试，看能否改善单表慢的问题。

标签: mycat

非特殊说明，本博所有文章均为博主原创。

最新文章

发表评论