What the hell is Mycat? Vertical segmentation? Horizontal segmentation?

What the hell is Mycat?

Mycat is an open source database middleware developed by the Chinese people, his role is to disguise into a real database, and then behind it, even a variety of types, cluster distributed real database. His architecture is like the following:

Several concepts of mycat need to be understood.

Schema: A logical library that corresponds to the database in MySQL, and a logical library defines the included table.
Table: Tables, that is, a table stored in a physical database, unlike a traditional database, where the table needs to declare the logical data node Datanode it stores, which is implemented by the partitioning rule definition of the table, which defines the "child table (childtable)" that it belongs to. The fragment of the child table depends on the specific fragment address of the "parent table", simply put, all records of a child table belonging to a record a in the parent list are stored on the same fragment as a.
Fragmentation rule: is a field and function of the binding definition, according to the value of this field to return the stored fragment (Datanode) ordinal, each table can define a fragment rule, fragmentation rules can be flexible extension, the default provided by the number of piecewise rules, strings, such as fragmentation rules.
The logical data node of DATANODE:MYCAT is the concrete physical node that holds the table, also called the Fragment node, which is connected to a specific database on the back end through DataSource, in general, for high availability, Each datanode is set to two DataSource, one master from the other, when the primary node is down, the system automatically switches to the from node.
Datahost: Defines the access address of a physical library to bind to Datanode.

Mycat currently defines logical libraries and related configurations through the configuration file:
Mycat_home/conf/schema.xml in the definition of logical library, table, fragment node, etc. content;
Partitioning rules are defined in Mycat_home/conf/rule.xml;
Mycat_home/conf/server.xml defines user and system-related variables, such as ports.

Say so much, perhaps you look at the following diagram to understand:

The good thing about Mycat is that you can use him just like you use a real mysq database, JDBC How to, the command line how to knock on how to write the SQL statement how to write, it appears to make your business code persistence layer does not move, and behind, but can help build a MySQL table, sub-Library, Read-write separation, cluster distributed.

The following is a simple use of mycat to help us to vertical segmentation and horizontal segmentation of the Business data table to build

Vertical segmentation is actually based on the different business, the different business tables into different databases, such as our order form, user table, user Comment table, because their business is different, you can put them into three databases. OK, so let's use three databases, install three tables separately, and use the MySQL schema as follows:

1, modify the Mycat_home/conf/schema.xml, the contents are as follows:

<?xml version= "1.0"?> <! DOCTYPE mycat:schema SYSTEM "Schema.dtd" > <mycat:schema xmlns:mycat= "http://org.opencloudb/" > <!--Schema Logical database--> <schema name= "MYCAT1" checksqlschema= "false" sqlmaxlimit= "" datanode= "Mycat1"/> <schema Name= the "Mycat2" Checksqlschema= "false" sqlmaxlimit= "datanode=" mycat2/> "", "<schema, name= A= "false" sqlmaxlimit= "datanode=" Mycat3/> <!--use Datanode to map real and logical databases--> <datanode name= "Myca T1 "datahost=" Mycat "database=" Mycat1 "/> <datanode name=" mycat2 "datahost=" Mycat "database=" Mycat2 "/>" Lt;datanode name= "MYCAT3" datahost= "Mycat" database= "Mycat3"/> "<datahost name=" Mycat "maxcon=" 1000 "minCon=" 10 "balance=" 0 "writetype=" 0 "dbtype=" MySQL "dbdriver=" native ">  
2, modify Mycat_home/conf/server.xml, add link mycat account information, etc.

<?xml version= "1.0" encoding= "UTF-8"?> <!
DOCTYPE mycat:server SYSTEM "Server.dtd" >
<mycat:server xmlns:mycat= "http://org.opencloudb/" >
    <system>
    <property name= "Defaultsqlparser" >druidparser</property>
    </system>
    <!--account password and the linked logical library-->
    <user name= "Test" >
        <property name= "password" >test</ property>
        <property name= "schemas" >mycat1,mycat2,mycat3</property>
    </user>

    <!--read-only user information-->
    <user name= "user" >
        <property name= "password" >user</property>
        <property name= "schemas" >mycat1,mycat2,mycat3</property>
        <property name= "ReadOnly" > true</property>
    </user>
</mycat:server>
 
3, then you can use the MySQL client, or direct command line way into the MySQL client, view the database.


You can see Mycat the contents of the three databases are included in the complete vertical segmentation. Database Horizontal Segmentation

In addition to being able to divide the data tables into different databases according to the business, the vertical segmentation many times, when the user is large, such as a user table has hundreds of billions of data, then a one-time check data must be very slow, so we can, this table according to some rules of the data stored in different databases, For example, according to the user province, the data can be divided into Guangdong, Beijing, Shanghai and other databases. Below if we have a student table, because the amount of data is too much, according to the ID of the remainder rule, the data stored in two databases, the structure is as follows:


Here's how to configure:

1, modify the Mycat_home/conf/schema.xml, the contents are as follows:

<?xml version= "1.0"?> <! DOCTYPE mycat:schema SYSTEM "Schema.dtd" > <mycat:schema xmlns:mycat= "http://org.opencloudb/" > <!--TB_CLA SS and Tb_student have foreign key associations that can test join--> <schema name= "test" checksqlschema= "false" sqlmaxlimit= "M" > <ta ble name= "Tb_class" datanode= dn1,dn2 "rule=" Rule1 "primarykey=" id "/> <table name=" tb_student "datanode=" DN1, DN2 "rule=" Rule1 "primarykey=" id "/> </schema> <datanode name=" dn1 "datahost=" mycat101 "database=" Myca T101 "/> <datanode name= dn2" datahost= "mycat103" database= "mycat103"/> <datahost name= "mycat101" Ma xcon= "1000" mincon= "balance=" 0 "writetype=" 0 "dbtype=" MySQL "dbdriver=" native ">  
2, modify Mycat_home/conf/server.xml, add link mycat account information, etc.

<?xml version= "1.0" encoding= "UTF-8"?> <!
DOCTYPE mycat:server SYSTEM "Server.dtd" >
<mycat:server xmlns:mycat= "http://org.opencloudb/" >
    <system>
    <property name= "Defaultsqlparser" >druidparser</property>
    </system>
    <!--account password and the linked logical library-->
    <user name= "Test" >
        <property name= "password" >test</ property>
        <property name= "schemas" >test</property>
    </user>

    <!--read-only user information- >
    <user name= "user" >
        <property name= "password" >user</property>
        <property Name= "schemas" >test</property>
        <property name= "readOnly" >true</property>
    </ User>
</mycat:server>
 
3, define slicing rules, modify mycat_home/conf/rule.xml, here is the installation ID for slicing

<?xml version= "1.0" encoding= "UTF-8"?> <!
DOCTYPE mycat:rule SYSTEM "Rule.dtd" >
<mycat:rule xmlns:mycat= "http://org.opencloudb/" > 
 < Tablerule name= "Rule1" >
    <!--Installation ID rules, will be the ID in addition to 1024 and then take the remainder, if the rest of the grumble in the 0~512 will write the data to the first database, if it is in the 512~1024 to the second database-->
    <rule>
      <columns>id</columns>
      <algorithm>func1</algorithm>
    </ rule>
 </tableRule>

 <!--into two slices, each with a range of 512, which must be multiplied by 1024-->
 <function name= "Func1" class= "Org.opencloudb.route.function.PartitionByLong" >
    <property name= "Partitioncount" >2</ property>
    <property name= "partitionlength" >512</property>
 </function>
</ Mycat:rule>
 
4, to test, first look at the Mycat database performance.


Test is our logical library, Tb_class and tb_student are our tables, inserting a piece of data into the Tb_class, the ID is 1,1 between the 0~512, should be inserted into the first database,


Then insert a data that is ID 513, then it should be in the second database.


Then in the Mycat performance is a table, you can arbitrarily delete and change check.


The vertical segmentation and horizontal segmentation is done.