Kingshard by Time table function Introduction

This is a creation in Article, where the information may have evolved or changed.

Kingshard by Time table function Introduction

In the document, we mainly introduce the kingshard of hash and range, and recently developed the sub-table mode by Time dimension. It is very common to divide the table by Time dimension, and the following describes the time table function of Kingshard

1. Supported Time types

Table fields in Kingshard support three types of time formats in MySQL

• Date type, format: YYYY-MM-DD, for example: 2016-03-04, note: 2016-3-04,2016-03-4,2016-3-4 formats such as Kingshard are not supported.

• DateTime type, Format: Yyyy-mm-dd HH:MM:SS, for example: 2016-03-04, note: 2016-3-04 13:23:43,2016-03-4 13:23:43,2016-3-4 13:23:43 and other formats Kingshard are not supported, must be in strict accordance with the prescribed format, Kingshard only support.

• Timestamp type, integer type, for example: 1457165568, corresponds to: 2016-3-5 16:12:48.

2. Supported time sub-table types

Kingshard supports three types of time in MySQL format

• Date type, format: YYYY-MM-DD, for example: 2016-03-04, note: 2016-3-04,2016-03-4,2016-3-4 formats such as Kingshard are not supported.

• datetime, Format: Yyyy-mm-dd HH:MM:SS, for example: 2016-03-04, note: 2016-3-04 13:23:43,2016-03-4 13:23:43,2016-3-4 13:23:43 and other formats Kingshard are not supported, must be in strict accordance with the prescribed format, Kingshard only support.

• Timestamp, integer type.

3. Function demonstration

The configuration file for Kingshard is as follows:

# Server Listen addraddr:0.0.0.0:9696# server user and passworduser:kingshardpassword:kingshard# if set Log_path, t He SQL Log would write into log_path/sql.log,the system log# would write into Log_path/sys.log#log_path:/users/flike/log# Log Level[debug|info|warn|error],default errorlog_level:debug# If set Log_sql (on|off) off,the SQL log would not OutputLog _sql:on # Only log the query this take more than slow_log_time ms#slow_log_time:100# the path of blacklist SQL file# AL L These sqls in the file would been forbidden by kingshard#blacklist_sql_file:/users/flike/blacklist# = Allow this IP l ist IP to connect kingshard#allow_ips:127.0.0.1# the charset of Kingshard, if you don ' t set this item# the default Charse  T of Kingshard is UTF8. #proxy_charset: gbk# node is a agenda for real remote MySQL server.nodes:-Name:node1 #    Default Max Conns for MySQL server max_conns_limit:32 # all MySQL in a node must has the same user and password    User:kingshard Password:kingshard # Master represents a real MySQL master server master:127.0.0.1:3306 # Slave represents    A real MySQL salve server,and the number after ' @ ' was # 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 has the same user and password User:kingshar D Password:kingshard # Master represents a real MySQL master server master:192.168.59.103:3307 # Slave R Epresents a real MySQL salve server slave: # down MySQL after N seconds noalive # 0 'll no down down_after _noalive:32# schema defines sharding rules, the DB is the sharding table Database.schema:db:kingshard nodes: [n       Ode1,node2] Default:node1 Shard:-Table:test_shard_year key:ctime Type:date_day Nodes: [Node1,node2] Date_range: [2015-2016, 2017-2018] 

3.1 Table by year

3.1.1 Configuration Instructions

The configuration items by year table are set as follows:

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

The configuration represents:

• Sharding key is CTime.

• The type of table by year is: date_year .

• test_shard_year_2015, test_shard_year_2016Two sub-tables fall on Node1, and test_shard_year_2017，test_shard_year_2018 two sub-tables fall on Node2.

• If you have a node that contains only one child table, you can configure this date_range[2015,2017-2018] .

Note: The name format of the child table must be: Shard_table_yyyy,shard_table is the name of the table, followed by the specific year.

3.1.2 Function Demo

Create two sub-tables on Node1 test_shard_year_2015, test_shard_year_2016 and create two seed tables on the Node2 test_shard_year_2017，test_shard_year_2018 . Table SQL is built as follows

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

Insert data:

mysql> insert into test_shard_year(id,name,ctime) values(12,"hello","2015-02-22 13:23:45");Query OK, 1 row affected (0.01 sec)mysql> insert into test_shard_year(id,name,ctime) values(13,"world","2016-03-22");Query OK, 1 row affected (0.00 sec)mysql> select * from test_shard_year where ctime < "2016-03-23";+----+-------+---------------------+| id | name  | ctime               |+----+-------+---------------------+| 12 | hello | 2015-02-22 13:23:45 || 13 | world | 2016-03-22 00:00:00 |+----+-------+---------------------+2 rows in set (0.00 sec)

The corresponding SQL log information is:

2016/03/05 12:06:32 - OK - 1.2ms - 127.0.0.1:56597->127.0.0.1:3306:insert into test_shard_year_2015(id, name, ctime) values (12, 'hello', '2015-02-22 13:23:45')2016/03/05 12:06:59 - OK - 2.0ms - 127.0.0.1:56597->127.0.0.1:3306:insert into test_shard_year_2016(id, name, ctime) values (13, 'world', '2016-03-22')2016/03/05 12:08:30 - OK - 1.6ms - 127.0.0.1:56597->127.0.0.1:3306:select * from test_shard_year_2015 where ctime < '2016-03-23'2016/03/05 12:08:30 - OK - 0.3ms - 127.0.0.1:56597->127.0.0.1:3306:select * from test_shard_year_2016 where ctime < '2016-03-23'

Of course, if you use the ID as a UNIX timestamp to divide the table, Kingshard is also supported. The exact configuration is this:

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

Insert data:

mysql> insert into test_shard_year(id,name,ctime) values(1457410310,"world","2018-03-22");Query OK, 1 row affected (0.01 sec)mysql> select * from test_shard_year where id = 1457410310;+------------+-------+---------------------+| id         | name  | ctime               |+------------+-------+---------------------+| 1457410310 | world | 2018-03-22 00:00:00 |+------------+-------+---------------------+1 row in set (0.00 sec)

1457410310 This UNIX timestamp corresponds to the date: 2016-3-8 12:11:50. Kingshard accurately routed this record to test_shard_year_2016 this sub-table.
The corresponding SQL log is:

2016/03/08 12:12:49 - OK - 1.0ms - 127.0.0.1:56669->127.0.0.1:3306:insert into test_shard_year_2016(id, name, ctime) values (1457410310, 'world', '2018-03-22')2016/03/08 12:13:23 - OK - 0.4ms - 127.0.0.1:56669->127.0.0.1:3306:select * from test_shard_year_2016 where id = 1457410310

3.2 Monthly Table

Configuration Instructions

The configuration items by month table are set as follows:

       table: test_shard_month       key: ctime       type: date_month       nodes: [node1,node2]       date_range: [201512-201602,201609-2016010]

The configuration represents:

• Sharding key is CTime.

• The type of table by year is: date_month .

• test_shard_month_201512, test_shard_month_201601, test_shard_month_201602Two sub-tables fall on Node1, and test_shard_month_201609，test_shard_month_201610 two sub-tables fall on Node2.

• If you have a node that contains only one child table, you can configure this date_range[201501,201609-201610] .

Note: The naming format of a child table must be a shard_table_YYYYMM,shard_table table name followed by a specific year and month.

The function demo references the operation of the yearly table.

3.3 By Talent Table

Configuration Instructions

The configuration items by month table are set as follows:

       table: test_shard_day       key: ctime       type: date_day       nodes: [node1,node2]       date_range: [20151222-20151224,20160901-20160902]

The configuration represents:

• Sharding key is CTime.

• The type of table by year is: date_day .

• test_shard_day_20151222, test_shard_day_20151223, test_shard_day_20151224Two sub-tables fall on Node1, and test_shard_day_20160901，test_shard_day_20160902 two sub-tables fall on Node2.

• If you have a node that contains only one child table, you can configure this date_range[20150101,20160901-20161010] .

Note: The naming format of a child table must be a shard_table_YYYYMMDD,shard_table table name followed by a specific year, month, and day.

The function demo references the operation of the yearly table.

Kingshard Open Source Address: Https://github.com/flike/kingshard