mysql 水平分片_使用MYCAT轻松实现MYSQL水平分片

完整文章下载地址：http://download.csdn.net/detail/dreamcode/9383516  简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行切分到其他的数据库中，其中选择合适的切分规则至关重要，因为它决定了后续数据聚合的难易程度。  有几种典型的分片规则包括：  (1)按照用户主键ID求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中。  (2)按照日期，将不同月甚至日的数据分散到不同的库中。  (3)按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。  一、 MYCAT常用分片规则  MYCAT常用的分片规则如下，另外还有一些其他分片方式这里不全部列举：  (1)分片枚举：sharding-by-intfile  (2)主键范围：auto-sharding-long  (3)一致性hash：sharding-by-murmur  (4)字符串hash解析：sharding-by-stringhash  (5)按日期(天)分片：sharding-by-date  (6)按单月小时拆分：sharding-by-hour  (6)自然月分片：sharding-by-month  二、 MYCAT分片配置说明  下文以使用分片枚举规则和主键范围规则分片为例：  1、分片枚举规则(sharding-by-intfile)  (1)使用规则：/src/main/resources/schema.xml

1

2

3

rule="sharding-by-intfile" />

(2)定义规则：/src/main/resources/rule.xml

1

2

3

4

5

6

7

8

9

10

11

12

sharding_id

hash-int

partition-hash-int.txt

(3)规则文件：/src/main/resources/autopartition-long.txt

1

2

310000=0

10010=1

2、主键范围规则(auto-sharding-long)  (1)使用规则：/src/main/resources/schema.xml

1

2

3

4

(2)定义规则：/src/main/resources/rule.xml

1

2

3

4

5

6

7

8

9

10

11

12

id

rang-long

autopartition-long.txt

(3)规则文件：/src/main/resources/autopartition-long.txt

1

2

3

4

5

6# range start-end ,data node index

# K=1000,M=10000.

0-500M=0

500M-1000M=1

1000M-1500M=2

三、 MYCAT分片测试  下文以使用分片枚举规则和主键范围规则分片为例：  1、分片枚举规则(sharding-by-intfile)  (1)插入两条sharding_id=10000的数据，数据被插入到dn1节点  1)语句1：explain insert into employee(id,name,sharding_id) values(1,’test1’,10000);

2)语句2：explain insert into employee(id,name,sharding_id) values(1,’test2’,10000);

(2)插入两条id范围在500M-1000M的数据，数据被插入到dn2节点  1)语句1：explain insert into travelrecord (id,user_id,traveldate,fee,days) values(5000001,’zhao’,’2015-12-10’,510.5,3);

2)语句2：explain insert into travelrecord (id,user_id,traveldate,fee,days) values(6000001,’zhao’,’2015-12-10’,510.5,3);

四、 分片全局序列号(数据库方式)  分库以后MySQL的自增主键就不能使用了，我们可以把SEQUENCE生成功能存储过程放到一个数据库分片中，数据插入前先查询当前的SEQUENCE，然后再执行操作。  1、配置server.xml：  sequnceHandlerType 配置为 1，表示使用数据库库生成 sequence

11

2、创建MYCAT_SEQUENCE 表

1

2

3

4

5

6

7

8

9

10DROP TABLE IF EXISTS MYCAT_SEQUENCE;

CREATE TABLE MYCAT_SEQUENCE ( NAME VARCHAR (50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY (NAME) ) ENGINE = INNODB;

3、创建 function  (1)创建mycat_seq_currval，获取当前 sequence的值

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20DROP FUNCTION IF EXISTS `mycat_seq_currval`;

DELIMITER ;;

CREATE DEFINER = `root`@`%` FUNCTION `mycat_seq_currval` (seq_name VARCHAR(50)) RETURNS VARCHAR (64) CHARSET latin1 DETERMINISTIC BEGIN DECLARE retval VARCHAR (64) ;

SET retval = "-999999999,null" ; SELECT concat( CAST(current_value AS CHAR), ",", CAST(increment AS CHAR) ) INTO retval FROM MYCAT_SEQUENCE WHERE NAME = seq_name ; RETURN retval ;

END

(2)创建mycat_seq_currval，获取下一个sequence值

1

2

3

4

5

6

7

8

9

10

11

12DROP FUNCTION IF EXISTS `mycat_seq_nextval`;

DELIMITER ;;

CREATE DEFINER = `root`@`%` FUNCTION `mycat_seq_nextval` (seq_name VARCHAR(50)) RETURNS VARCHAR (64) CHARSET latin1 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE NAME = seq_name ; RETURN mycat_seq_currval (seq_name) ;

END;;

DELIMITER ;

(3)创建mycat_seq_currval，设置sequence 值

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18DROP FUNCTION IF EXISTS `mycat_seq_setval`;

DELIMITER ;;

CREATE DEFINER = `root`@`%` FUNCTION `mycat_seq_setval` ( seq_name VARCHAR (50), VALUE INTEGER ) RETURNS VARCHAR (64) CHARSET latin1 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = VALUE WHERE NAME = seq_name ; RETURN mycat_seq_currval (seq_name) ;

END;;

DELIMITER ;

(4)增加权限，否则不能执行

1

2

3

4

5

6

7

8mysql > GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY ".";

QUERY OK,

0 rows affected (0.00 sec) mysql > FLUSH PRIVILEGES;

QUERY OK,

0 rows affected (0.00 sec)

(5)插入初始值，测试function

1INSERT INTO MYCAT_SEQUENCE VALUES ('article_seq', 1, 1);

执行以下查询测试：  1) 查询1：SELECT MYCAT_SEQ_CURRVAL(‘article_seq’);

2) 查询2：SELECT MYCAT_SEQ_SETVAL(‘article_seq’, 2);

3) 查询3：SELECT MYCAT_SEQ_CURRVAL(‘article_seq’);

4) 查询4：SELECT MYCAT_SEQ_NEXTVAL(‘article_seq’);

5)查询5：SELECT MYCAT_SEQ_NEXTVAL(‘article_seq’);

6)查询6：SELECT MYCAT_SEQ_CURRVAL(‘article_seq’);