kingshard实践03-实现kingshard分页

kingshard链接

hash分片

首先关闭主从关系stop slave

kingshard需要自己提前建立好分片

• 3307

mysql>
mysql> create table stu_0000 like user;
Query OK, 0 rows affected (0.04 sec)

mysql> create table stu_0001 like user;
Query OK, 0 rows affected (0.03 sec)

• 3308

mysql>
mysql> create table stu_0002 like user;
Query OK, 0 rows affected (0.04 sec)

mysql> create table stu_0003 like user;
Query OK, 0 rows affected (0.03 sec)

• 修改etc/ks.yaml配置

nodes :
-
    name : node1

    # default max conns for mysql server
    max_conns_limit : 32

    # all mysql in a node must have the same user and password
    user :  root
    password : kingshard

    # master represents a real mysql master server
    master : 127.0.0.1:3307

    # slave represents a real mysql salve server,and the number after '@' is
    # read load weight of this slave.
    #slave : 192.168.59.101:3307@2,192.168.59.101:3307@3
    down_after_noalive : 32
-
    name : node2

    # default max conns for mysql server
    max_conns_limit : 32

    # all mysql in a node must have the same user and password
    user :  root
    password : kingshard
        # master represents a real mysql master server
    master : 127.0.0.1:3308

    # slave represents a real mysql salve server
    slave :

    # down mysql after N seconds noalive
    # 0 will no down
    down_after_noalive: 32
# schema defines sharding rules, the db is the sharding table database.
schema_list :
-
    user: kingshard
    nodes: [node1,node2]
    default: node1
    shard:
    -
        db : ms
        table: stu
        key: id
        nodes: [node1, node2]
        type: hash
        locations: [2,2]

• 运行kingshard：mysql -h127.0.0.1 -ukingshard -pkingshard -P9696;

• 测试语句

mysql> insert into stu(id,age,name) values(4,10,'力5');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu(id,age,name) values(0,11,'s0');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu(id,age,name) values(1,11,'s1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu(id,age,name) values(2,11,'s2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from stu;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  2 |  11 | s2   |
|  0 |  11 | s0   |
|  4 |  10 | 力5  |
|  1 |  11 | s1   |
+----+-----+------+
4 rows in set (0.03 sec)

mysql> update stu set age = 5 where id < 2;
Query OK, 2 rows affected (0.03 sec)

mysql> update stu set age = 6 where id = 1;
Query OK, 1 row affected (0.02 sec)

• 输出日志

2019/03/05 21:21:13 - OK - 12.1ms - 127.0.0.1:57282->127.0.0.1:3307:insert  into stu_0000(id, age, name) values (4, 10, '力5')
2019/03/05 21:22:57 - OK - 9.4ms - 127.0.0.1:57282->127.0.0.1:3307:insert  into stu_0000(id, age, name) values (0, 11, 's0')
2019/03/05 21:23:18 - OK - 6.1ms - 127.0.0.1:57282->127.0.0.1:3307:insert  into stu_0001(id, age, name) values (1, 11, 's1')
2019/03/05 21:23:28 - OK - 22.0ms - 127.0.0.1:57282->127.0.0.1:3308:insert  into stu_0002(id, age, name) values (2, 11, 's2')
2019/03/05 21:23:58 - OK - 2.5ms - 127.0.0.1:57282->127.0.0.1:3307:select * from stu_0000
2019/03/05 21:23:58 - OK - 7.3ms - 127.0.0.1:57282->127.0.0.1:3308:select * from stu_0002
2019/03/05 21:23:58 - OK - 9.7ms - 127.0.0.1:57282->127.0.0.1:3307:select * from stu_0001
2019/03/05 21:23:58 - OK - 9.3ms - 127.0.0.1:57282->127.0.0.1:3308:select * from stu_0003
2019/03/05 21:29:48 - OK - 6.5ms - 127.0.0.1:57282->127.0.0.1:3308:update stu_0002 set age = 5 where id < 2
2019/03/05 21:29:48 - OK - 9.1ms - 127.0.0.1:57282->127.0.0.1:3307:update stu_0000 set age = 5 where id < 2
2019/03/05 21:29:48 - OK - 4.8ms - 127.0.0.1:57282->127.0.0.1:3308:update stu_0003 set age = 5 where id < 2
2019/03/05 21:29:48 - OK - 3.6ms - 127.0.0.1:57282->127.0.0.1:3307:update stu_0001 set age = 5 where id < 2
2019/03/05 21:30:16 - OK - 7.3ms - 127.0.0.1:57282->127.0.0.1:3307:update stu_0001 set age = 6 where id = 1

range分片

• 3307

mysql>
mysql> create table stu_range_0000 like user;
Query OK, 0 rows affected (0.04 sec)

mysql> create table stu_range_0001 like user;
Query OK, 0 rows affected (0.03 sec)

• 3308

mysql>
mysql> create table stu_range_0002 like user;
Query OK, 0 rows affected (0.04 sec)

mysql> create table stu_range_0003 like user;
Query OK, 0 rows affected (0.03 sec)

• 修改etc/ks.yaml配置

-
    user: kingshard
    nodes: [node1,node2]
    default: node1
    shard:
    -
        db : ms
        table: stu_range
        key: id
        type: range
        nodes: [node1, node2]
        locations: [2,2]
        table_row_limit: 100

• range分片范围查询时不会检索所有表

时间分片

• 3307

CREATE TABLE `test_shard_year_2015` (
  `id` int(10) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table test_shard_year_2016 like test_shard_year_2015;

• 3308

CREATE TABLE `test_shard_year_2017` (
  `id` int(10) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table test_shard_year_2018 like test_shard_year_2017;

• 修改etc/ks.yaml配置

    db : ms
    table: test_shard_year
    key: ctime
    type: date_year
    nodes: [node1,node2]
    date_range: [2015-2016,2017-2018]

• 时间分片不同表中的id是可以重复的，需要客户端自己维护自增id