Shulou(Shulou.com)06/01 Report--

1. Mycat sub-library routing is divided into continuous routing and discrete routing.

1. Continuous routing:

(1) commonly used routing methods: auto-sharding-long, sharding-by-date, sharding-by-month

(2) advantages: there is no need to migrate data for capacity expansion, and the scope condition query consumes less resources.

(3) disadvantages: the possibility of data hotspots; concurrent access is limited by a single or a small amount of DataNode

2. Off-line routing:

(1). Commonly used routing methods: sharding-by-intfile, sharding-by-murmur, mod-long, crc32slot.

(2) advantages: enhanced concurrent access ability.

(3) disadvantages: it is difficult to expand data capacity, which involves data migration; database links consume a lot of resources.

2. Auto-sharding-long:

1. Routing rules:

Userid

Rang-long-userid

Autopartition-long-userid.txt

[root@host01 conf] # more autopartition-long-userid.txt

# range start-end, data node index

# Knights 1000, Manners 10000.

0-1000000

1001-2000,1

2001-3000002

3001-4000,3

4001-5000,4

5001-60005

2. Examples:

CREATE TABLE tb_user_detail_t (

Userid bigint not null primary key

Name varchar (64) DEFAULT NULL

Createtime datetime DEFAULT CURRENT_TIMESTAMP

Moditytime datetime DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Write data

Insert into tb_user_detail_t (userid,name) values (999dint name 999')

Insert into tb_user_detail_t (userid,name) values (1999 Mastername 999')

Insert into tb_user_detail_t (userid,name) values (2999)

Insert into tb_user_detail_t (userid,name) values (3999)

Insert into tb_user_detail_t (userid,name) values (4999)

Insert into tb_user_detail_t (userid,name) values (5999)

3. Sharding-by-date:

1. Routing rules:

Createtime

Partbydate

Yyyy-MM-dd HH:mm:ss

2016-01-01 00:00:00

two

The slicing date starts from 2016-01-01, and there is a slicing every 2 days.

2. Examples:

CREATE TABLE `tb_user_ partbydate` (

`id`varchar (32) NOT NULL

`name` varchar (64) DEFAULT NULL

`createtime` varchar (10)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-01 00) values ('a0001)

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-02 00) values ('a0002)

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-03 00) values ('a0003)

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-04 00) values ('a0004)

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-05 00) values ('a0005)

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-06 00) values ('a0006)

Insert into tb_user_partbydate (id,name,createtime) values ('a0007mm, 01m))

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-08 00) values (2016-01-08 00)

Insert into tb_user_partbydate (id,name,createtime) values (2016-01-09 00) values ('a0006)

Insert into tb_user_partbydate (id,name,createtime) values ('a0007mm, 01m))

4. Sharding-by-month:

1. Routing rules:

Createtime

Partbymonth

Yyyy-MM-dd HH:mm:ss

2015-01-01 00:00:00

DateFormat is the date format and sBeginDate is the start date.

2. Examples:

CREATE TABLE `tb_partbymonth `(

`id`varchar (32) NOT NULL

`name` varchar (64) DEFAULT NULL

`createtime` datetime DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Write data (note that the now function cannot be used here)

Insert into tb_partbymonth (id,name,createtime) values ('a0001Zhenzhongname1ZhenJing 2015-01-010purl 0000')

Insert into tb_partbymonth (id,name,createtime) values ('a0002Zhenzhongname1Ji Ji 2015-02-10VOV 0000')

Insert into tb_partbymonth (id,name,createtime) values ('a0003-031-015-03-01-0015-03-01-0015-03-01-0012)

Insert into tb_partbymonth (id,name,createtime) values ('a0004-0000-0400-0400-0015-04-01-0015-04-01-0015-04-01-00)

Insert into tb_partbymonth (id,name,createtime) values ('a0005-month-old-name-1-month-old', 2015-05-01-10-10-15-01-10-15-05-01-10-15-05-01-10-15-05-01-15-05-01)

Insert into tb_partbymonth (id,name,createtime) values. ('a0006, June, 2015, June, February, 2015, June, and February, 2015, 10, 10, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, and 12, respectively).

Insert into tb_partbymonth (id,name,createtime) values ('a0007mm, 07m, 07m, 07m, 01m, 01m, 01m, 01m, 01m, 01m, 07m, 01m, 07m, 07m, 01m, 07m, 01m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 07m, 01m, 01m, 01m, 07m, 01m, 07m, 01m, 07m, 01m, 01m, 07m, 01m, 07m, 07m, 01m,

Insert into tb_partbymonth (id,name,createtime) values ('a0008Zhenzhongname1ZhenJi 2015-08-01 00VO15-08-01 00VOG 00')

Insert into tb_partbymonth (id,name,createtime) values ('a0009, 2009, September, 2015, September and January 10, 2015-09, 01, 10, 12, 10, 15, 12, 15, 15, 01, 10, 10, 10, 15, 15, 01, 10, 10, 15, 15, 01, 10, 10, 15, 15, 01, 10, 15, 10, 15, 01, 10, 10, 10, 15, 10, 15, 15, 01, 10, 15, 10, 15, 01, 15, 01, 10, 15, 01, 10, and 10))

Insert into tb_partbymonth (id,name,createtime) values ('a0010',''a0010','10','10','10','10','10', 10', 10', 10, 10, 10, 12, 10, 10, 12, 15, 10, 10, 10, 10, 12, 10, 10, 10, 10, 02, 10, 10, 10, 12, 15, 15, 10, 10, 10, 10, 10, 12, 15, 10, 10, 10, 10, 12, 15, 10, 10, 10, 10, 12, 15, 15, 10, 10, 10, 15, 10, 10, 10, 10, 10, 15, 10, 10, 10, 12, 15, 15, 10, 10, 10, 15, 10, 10, 10, 15, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10

Insert into tb_partbymonth (id,name,createtime) values ('a0011', '011', '011','1', 'name 1','15'- 11-01 00)

Insert into tb_partbymonth (id,name,createtime) values (2015-12-01 00 values)

Insert into tb_partbymonth (id,name,createtime) values (2016-01-01 00) values ('a0013)

5. Sharding-by-intfile (enumeration):

1. Routing rules:

Provcode

Hash-int-provcode

Partition-hash-int-provcode.txt

0

Type=0 stands for × × ×

Type=1 stands for string type

[root@host01 conf] # more partition-hash-int-provcode.txt

1: 0

2. 1

3. 2

4. 3.

Five and four

6-5

7 minutes 0

8: 1

9. 2.

10: 3

11: 4

12 years 5

DEFAULT_NODE=0 # # if no province match is found, it will be placed in database 1 by default.

Here are six libraries, serial number 0-5, mapping different provinces to the corresponding libraries. The correspondence between all provinces and libraries should be enumerated.

2. Examples:

CREATE TABLE `tb_user_ t` (

Id bigint auto_increment not null primary key

`name` varchar (64) DEFAULT NULL

Provcode int

`createtime` datetime DEFAULT CURRENT_TIMESTAMP

`moditytime` datetime DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

It is found that defining auto-growing id does not work in the case of sub-libraries, because each library has its own self-growing id, and there will be duplicate id if queried by mycat.

It is as follows:

Mysql > select * from tb_user_t order by id

+-- +

| | id | name | provcode | createtime | moditytime | |

+-- +

| | 1 | name0005 | 5 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |

| | 1 | name0001 | 1 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |

| | 1 | name0004 | 4 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |

| | 1 | name0002 | 2 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |

| | 1 | name0003 | 3 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |

| | 1 | name0006 | 6 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |

| | 2 | name0011 | 11 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |

| | 2 | name0007 | 7 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |

| | 2 | name0010 | 10 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |

| | 2 | name0008 | 8 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |

| | 2 | name0009 | 9 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |

| | 2 | name0012 | 12 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |

| | 3 | name0013 | 13 | 2017-08-09 11:12:17 | 2017-08-09 11:12:17 |

+-- +

6. Sharding-by-murmur:

Murmur algorithm distributes fields to different databases after hash. Field types support int and varchar.

1. Routing rules:

Userid

Murmur

0

six

one hundred and sixty

2. Examples:

CREATE TABLE `tb_user_murmur_string_ t` (

`userid` varchar (32) NOT NULL

`name` varchar (64) DEFAULT NULL

`createtime` datetime DEFAULT CURRENT_TIMESTAMP

`moditytime` datetime DEFAULT CURRENT_TIMESTAMP

PRIMARY KEY (`userid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Write data

Insert into tb_user_murmur_string_t (userid,name) values ('user002','name002')

Insert into tb_user_murmur_string_t (userid,name) values ('user003','name003')

Insert into tb_user_murmur_string_t (userid,name) values ('user004','name004')

Insert into tb_user_murmur_string_t (userid,name) values ('user005','name005')

Insert into tb_user_murmur_string_t (userid,name) values ('user006','name006')

Insert into tb_user_murmur_string_t (userid,name) values ('user007','name007')

Insert into tb_user_murmur_string_t (userid,name) values ('user008','name008')

Insert into tb_user_murmur_string_t (userid,name) values ('user009','name009')

Insert into tb_user_murmur_string_t (userid,name) values ('user010','name010')

7. Crc32slot:

Crs32 algorithm, sub-library field types support int and varchar.

1. Routing rules:

Id

Crc32slot

six

Count=6 specifies the number of sublibraries that are required.

2. Examples:

CREATE TABLE `tb_user_crc32slot_ t` (

`id`varchar (32) NOT NULL

`name` varchar (64) DEFAULT NULL

`createtime` datetime DEFAULT CURRENT_TIMESTAMP

`moditytime` datetime DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Write data:

Insert into tb_user_crc32slot_t (id,name) values ('a0002)

Insert into tb_user_crc32slot_t (id,name) values ('a0003 magic name 1')

Insert into tb_user_crc32slot_t (id,name) values ('a0004)

Insert into tb_user_crc32slot_t (id,name) values ('a0005')

Insert into tb_user_crc32slot_t (id,name) values ('a0006)

Insert into tb_user_crc32slot_t (id,name) values ('a0007)

Insert into tb_user_crc32slot_t (id,name) values ('a0008')

Insert into tb_user_crc32slot_t (id,name) values ('a0009)

Insert into tb_user_crc32slot_t (id,name) values ('a0010')

Insert into tb_user_crc32slot_t (id,name) values ('a0011')

Insert into tb_user_crc32slot_t (id,name) values ('a0012')

Insert into tb_user_crc32slot_t (id,name) values ('a0013')

Insert into tb_user_crc32slot_t (id,name) values ('a0014')

Insert into tb_user_crc32slot_t (id,name) values ('a0015')

8. Mod-long:

1. Routing rules: decimal numbers are modeled according to nodes.

Id

Mod-long

three

9. Mycat sub-database rule Eamp R rule:

1. Routing rules:

After the E Id R rule is set by childTable, the Id with the same parent and child tables will fall in the same library, so as to avoid association across libraries.

JoinKey= "order_id" is the order_id field of the child table

ParentKey= "id" is the id field of the parent table

That is, the child table is associated with the id field of the parent table through the order_id field.

2. Examples

(2.1), create a table statement:

Create table orders

(

Id int not null

Order_name varchar (64)

Createtime datetime DEFAULT CURRENT_TIMESTAMP

Moditytime datetime DEFAULT CURRENT_TIMESTAMP

PRIMARY KEY (id)

);

Create table orders_cargo

(

Order_id int not null

Cargo_name varchar (64)

Createtime datetime DEFAULT CURRENT_TIMESTAMP

Moditytime datetime DEFAULT CURRENT_TIMESTAMP

PRIMARY KEY (order_id)

);

(2.2), customer Custermer and order Order

The orders for each customer and each customer are preferably in the same library.

3. If the parent table is the global table, it can also solve the efficiency problem of join.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.