MySQL series eight: differences between Mycat and Sharding-jdbc, Mycat shard join, mycat pits in pagination, mycat annotations, Catlet use

The difference between Mycat and SHARDING-JDBC

1) Mycat is a third-party application of middleware, SHARDING-JDBC is a jar package

2) You do not need to change the code when using Mycat, and you need to modify the code when using SHARDING-JDBC

Mycat (proxy middleware layer ):

SHARDING-JDBC (Tddl for the application layer represented ):

Second, Mycat Shard join

In the previous article MySQL series four: Database sub-Library sub-table basic theory, has said that the sub-database table needs to deal with the technical challenges are as follows:

1.) distributed Global Unique ID

2.) Shard rules and policies

3.) Cross-shard technical issues

4.) Cross-sharding things issues

Let's take a look at how Mycat solves the problem of cross-shard technology--fragmentation Join

1. Resolving cross-Shard join problems using global tables

1.1 First in Server.xml. Global Table Consistency detection

<name= "Useglobletablecheck">1</ Property >  <!--   -

1.2 Configuring global tables in Schema.xml

<name= "Company"  primaryKey= "ID"  type= "global"  dataNode= "dn1,dn2,dn3"/>

Global Table Description:

1) Global table INSERT, UPDATE operation will be executed on all nodes in real time, maintain the consistency of each shard data

2) Query operations on global tables are only obtained from one node

3) Global tables can be join operations with any table

2. Solve cross-shard join problems using share Join method

Share join is a simple cross-shard join, implemented based on HBT (Human Brain Tech).

Principle: Parse SQL statements, split into single-table SQL statement execution, then collect data from each node.

Example:

/*!mycat:catlet=io.mycat.catlets.sharejoin*/ Select *  from where = b.id;

Description: Currently, only two shard tables are supported Join, if you want to support multiple tables you need to retrofit the program code or transform the source code of MYCAT

corresponding Mycat Source:

Io.mycat.catlets.ShareJoin

Io.mycat.catlets.Catlet
public class Sharejoin implements Catlet

3. Resolving cross-Shard join problems using ER join method

Er tables are also called parent-child tables, where the child table is stored on which Shard depends on where the parent table is stored, and the parent table is stored on the same shard, that is, the records of the child tables are stored on the same data shard as the associated parent table records, thus resolving cross-Library join issues
The configuration inside the Schema.xml

<Tablename= "Customer"PrimaryKey= "ID"DataNode= "DN1,DN2"Rule= "Sharding-by-intfile">    <childtablename= "Orders"PrimaryKey= "ID"Joinkey= "customer_id"Parentkey= "id">            <childtablename= "Order_items"Joinkey= "order_id"Parentkey= "id" />    </childtable>    <childtablename= "Customer_addr"PrimaryKey= "ID"Joinkey= "customer_id"Parentkey= "id" /></Table>

Description

ChildTable: Tags are used to declare child tables:

Joinkey: The field that declares the child table is associated with the parent table

Parentkey: Declaring the parent table's associated primary key

PrimaryKey: Primary key of the parent table itself

Three, mycat the hole in the page

Mycat The big hole of the page must pay attention to:

When querying the paging data on the corresponding shards, the scan is started from the first record, and then the corresponding paging data is removed, as

SELECT *  from ORDER  by 1000100 ;

After this SQL statement was Mycat converted

 1  ->  dn1{< Span style= "COLOR: #0000ff" >select  *  from  customer order  by  ID LIMIT 0 , 1000100   " 2  ->  dn2{select  *  from  customer order  by  ID LIMIT 0 , 1000100 } 

So use non-heap memory in Mycat's SERVER.XM. Otherwise the memory will explode.

<name= "Useoffheapformerge">1</ Property >

Optimization:

1) identify the ID first

SELECT  from ORDER  by 1000100 ;

After this SQL statement was Mycat converted

1  -dn1{SELECT  ID from  ORDERby01000100 }2 -dn2{SELECT  ID from  ORDER  by 0 1000100}

2) Get all the IDs and retrieve the required data

SELECT *  from where inch (1,2,3...);

After this SQL statement was Mycat converted

1  -dn1{SELECT *  fromCustomerwhereIdinch(1,2,3....);}2  -dn2{SELECT *  fromCustomerwhereIdinch(1,2,3....);}

Iv. mycat Annotations 1. SQL statements not supported by Mycat:

1) Some SQL syntax, such as Insert Into......select .....

2) Cross-Library correlation query

3) Stored Procedure creation

4) Stored Procedure call

So Mycat provides mycat annotations to address the above unsupported SQL statements

Mycat Solutions: Mycat annotations

Grammar:

/*! MYCAT:SQL=MYCAT Annotated SQL statement */actual executed SQL ! Number mode

/*#MYCAT:SQL=MYCAT Annotated SQL statement */SQL #号方式actually executed

/* *mycat:sql=mycat Annotated SQL statement * * The actual SQL * Number method executed

Principle:

Replace MYCAT supported SQL with SQL that is not supported by MYCAT, run SQL that is not supported by Mycat

MYCAT Annotation Specification:

1) Annotated SQL uses the SELECT statement, which does not allow statements such as delete/update/insert, while statements such as Delete/update/insert can be used in annotations, but these statements have additional logical judgments in SQL processing, from performance considerations Please use the SELECT statement.
2) Note SQL Disables Table association statements.
3) Note SQL tries to use the simplest SQL statements, such as select ID from tab_a where id= ' 10000 ' (preferably, if necessary, to specify shards in annotations)
4) Suppress DDL statements, either raw SQL or annotated SQL
5) can not use the annotation as far as possible

2. Mycat annotation resolution does not support insert Into......select .....

/* !mycat:sql=select 1 */ Insert  into Travelrecord (ID,user_idSelect3,'Tom', ' 20180826 ',8;

3. Mycat Annotation Creation Table

/**/Createtableint);

4. mycat annotations Creating stored Procedures

/**/Createprocedure'test_proc ()' begin End;

5. mycat Annotations Call stored Procedures

/**/Call Test_proc ();

6. Mycat annotation Read-write separate data source selection

/**/Select*from Travelrecord; (forced to go to Main Library)/*    */Select* from Travelrecord; (forced walk from library)

V. Use of Catlet

Supports cross-shard complex SQL implementations, stored procedure support, and more through Catlet

How to use: Execute by mycat annotation method

1. Cross-Shard Federated query Annotation Support

/**/Select o.id,u.*fromorder O,userwhere o.user_id=u.id;

2. Batch insert with ID self-growth combined support

/** *insertintouservalues('Tom  '), (' Cat'), ('Alan');