mycat 离散分片 -> 程序指定分区的分片-白红宇

mycat 离散分片 -> 程序指定分区的分片

阅读量：5778 次

发布时间：2019-06-18

本文共 7965 字，大约阅读时间需要 26 分钟。

1。程序指定分区的分片

此规则是在运行阶段有应用自主决定路由到那个分片。

此方法为直接依据字符子串（必须是数字）计算分区号（由应用传递參数。显式指定分区号）。

2，加入配置文件

在function.xml里面进行配置：


                      
       
        0
                       
       
        1
                       
       
        8
                       
       
        0
               
          比如id=0-100000020在此配置中代表依据id中从startIndex=0，開始，截取siz=1位数字即0，0就是获取的分区，假设没传默认分配到defaultPartition        
                       
                                
        
         CODE
                                  
        
         sharding-by-substring-040302

在schema.xml里面进行配置：

3，建表而且录入数据

CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME);    mysql> CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME);    Query OK, 0 rows affected (0.05 sec)    mysql>     INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());    INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());    mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());    +-----------+----------------------------------------------------------------------------------------------------+    | DATA_NODE | SQL                                                                                                |    +-----------+----------------------------------------------------------------------------------------------------+    | dn21      | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()) |    +-----------+----------------------------------------------------------------------------------------------------+    1 row in set (0.00 sec)    mysql>     mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());    Query OK, 1 row affected (0.01 sec)    mysql>     mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());    +-----------+-----------------------------------------------------------------------------------------------------+    | DATA_NODE | SQL                                                                                                 |    +-----------+-----------------------------------------------------------------------------------------------------+    | dn22      | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()) |    +-----------+-----------------------------------------------------------------------------------------------------+    1 row in set (0.00 sec)    mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());    Query OK, 1 row affected (0.00 sec)    mysql>

4，对insert操作流程进行分析，參考mycat.log对路由过程做完整的分析

查看后台mycat.log分析例如以下：

開始获取连接：    02/13 00:40:18.173  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())開始路由匹配：    02/13 00:40:18.175  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()), route={       1 -> dn22{
      INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())}匹配好路由后，直接往相应的dn21节点開始运行insert数据录入操作：    02/13 00:40:18.180  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=17, lastTime=1455295218162, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=199, charset=latin1, txIsolation=3, autocommit=true, attachment=dn22{
      INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())}, respHandler=SingleNodeHandler [node=dn22{
      INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())}, packetId=0], host=10.254.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]连接释放：    02/13 00:40:18.180  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=17, lastTime=1455295218162, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=199, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=10.254.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]    02/13 00:40:24.390  DEBUG [Timer1] (SQLJob.java:85) -con query s

5，运行简单的查询，假设指定分片字段ID字段，则走分片查询单个分片节点

mycat命令台显示结果：

mysql> explain select * from ORDER040302 t1 where CODE= '0-10000020';    +-----------+------------------------------------------------------------------+    | DATA_NODE | SQL                                                              |    +-----------+------------------------------------------------------------------+    | dn21      | SELECT * FROM ORDER040302 t1 WHERE CODE = '0-10000020' LIMIT 100 |    +-----------+------------------------------------------------------------------+    1 row in set (0.00 sec)    mysql> select * from ORDER040302 t1 where CODE= '0-10000020';    +----+------------+---------------------+---------------------+    | ID | CODE       | SN                  | CREATE_TIME         |    +----+------------+---------------------+---------------------+    |  1 | 0-10000020 | beijing_03-10000020 | 2016-02-13 00:40:27 |    +----+------------+---------------------+---------------------+    1 row in set (0.01 sec)    mysql>

查看后台mycat日志信息：

02/13 00:44:46.717  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]explain select * from ORDER040302 t1 where CODE= '0-10000020'    02/13 00:44:46.718  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDB select * from ORDER040302 t1 where CODE= '0-10000020'    02/13 00:44:46.719  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER040302 t1 where CODE= '0-10000020' value:select * from ORDER040302 t1 where CODE= '0-10000020', route={       1 -> dn21{
      SELECT *    FROM ORDER040302 t1    WHERE CODE = '0-10000020'    LIMIT 100}    }

6。假设分片字段范围的查询，则走全部节点去检索。哪怕仅仅有一条数据在一个分片上。route路由也是走全部的分片进行检索查询

mysql> explain select * from ORDER040302 t1 where CODE likt '0-1%';    +-----------+---------------------------------------------------+    | DATA_NODE | SQL                                               |    +-----------+---------------------------------------------------+    | dn21      | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 |    | dn22      | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 |    +-----------+---------------------------------------------------+    2 rows in set (0.00 sec)    mysql>

7，假设不走分片字段的查询。即使是单个数据。也要route路由全部的分片，走全部的分片进行查询

mysql> explain select * from ORDER040302 t1 where SN='beijing_03-10000020';    +-----------+-------------------------------------------------------------------------+    | DATA_NODE | SQL                                                                     |    +-----------+-------------------------------------------------------------------------+    | dn21      | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 |    | dn22      | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 |    +-----------+-------------------------------------------------------------------------+    2 rows in set (0.00 sec)    mysql>

转载地址：http://zhkyx.baihongyu.com/

你可能感兴趣的文章

oracle的substr函数的用法

查看>>

Hadoop HDFS编程 API入门系列之路径过滤上传多个文件到HDFS（二）

查看>>

Nginx反向代理，负载均衡，redis session共享，keepalived高可用

查看>>

CentOS7 yum 安装git

查看>>

三元表达式之理解/jquery源代码分析之$.inArray实现

查看>>