#db01
[root@db01 tmp]# systemctl start mysqld3307

#node3
[root@node3 conf]# mysql -uroot -p -h 127.0.0.1 -P8066
Enter password:
#Write operation
mysql> begin; show variables like 'server_id';commit;
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 17    |
+---------------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

#Read operation
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          19 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

9, Vertical sub table

9.1 introduction of vertical sub table

A database consists of many tables, and each table corresponds to different businesses. Vertical segmentation refers to classifying tables according to businesses and distributing them to different databases, so as to share data or pressure to different databases.

The system is divided into several modules: user, order transaction and payment. The overall function of an application system with good architecture design must be composed of many functional modules, and the data required by each functional module corresponds to one or more tables in the database. In the architecture design, the more unified the interaction points between each functional module, the less the coupling degree of the system, and the better the maintainability and expansibility of each module of the system. In such a system, the easier it is to realize the vertical segmentation of data. However, it is often difficult for some tables of the system to be completely independent. There is a case of database expansion join. For such tables, we need to balance whether the database gives way to the business, shares one data source, or is divided into multiple libraries, and the business is called through the interface. In the initial stage of the system, when the amount of data is relatively small or the resources are limited, the common data source will be selected. However, when the data develops to a certain scale and the load is large, it needs to be divided. Generally speaking, it is difficult to segment the business with complex join scenarios, and the business is often independent and easy to segment. How to segment and to what extent is a difficult problem to test the technical architecture.

Advantages and disadvantages of lower vertical segmentation:

advantage:

• Clear business and clear splitting rules after splitting; 

• Easy integration or expansion between systems

• Data maintenance is simple.

Disadvantages:

 • Some business tables cannot be join，It can only be solved by interface, which improves the complexity of the system; 

• Due to the different restrictions of each business, there is a single database performance bottleneck, which is not easy to expand data and improve performance; 

• Complex transaction processing

9.2 configuration description of vertical sub table

Configuration file of vertical table

node3
[root@node3 conf]#cd /app/mycat/conf
[root@node3 conf]#vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
        <table name="user" dataNode="sh1"/>
        <table name="order_t" dataNode="sh2"/>
</schema>
    <dataNode name="sh1" dataHost="ywx1" database= "taobao" />
    <dataNode name="sh2" dataHost="ywx2" database= "taobao" />
    <dataHost name="ywx1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.32.201:3307" user="root" password="123">
            <readHost host="db2" url="192.168.32.201:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.32.202:3307" user="root" password="123">
            <readHost host="db4" url="192.168.32.202:3309" user="root" password="123" />
    </writeHost>
    </dataHost>
    <dataHost name="ywx2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.32.201:3308" user="root" password="123">
            <readHost host="db2" url="192.168.32.201:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.32.202:3308" user="root" password="123">
            <readHost host="db4" url="192.168.32.202:3310" user="root" password="123" />
    </writeHost>
    </dataHost>
</mycat:schema>

9.3 description of configuration file

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
        <table name="user" dataNode="sh1"/>
        <table name="order_t" dataNode="sh2"/>

Put the user table on the sh1 data node and click order_ Table t is placed on the sh2 data node, and other tables are placed on the sh1 data node by default

</schema>
    <dataNode name="sh1" dataHost="ywx1" database= "taobao" />
    <dataNode name="sh2" dataHost="ywx2" database= "taobao" />

Define the data host names of data nodes sh1 and sh2 as ywx1 and ywx2, and associate them with the real database taobao on the back end

<dataHost name="ywx1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.32.201:3307" user="root" password="123">
            <readHost host="db2" url="192.168.32.201:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.32.202:3307" user="root" password="123">
            <readHost host="db4" url="192.168.32.202:3309" user="root" password="123" />
    </writeHost>
    </dataHost>
#Define the read-write information of the data host ywx1    
    
    <dataHost name="ywx2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.32.201:3308" user="root" password="123">
            <readHost host="db2" url="192.168.32.201:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.32.202:3308" user="root" password="123">
            <readHost host="db4" url="192.168.32.202:3310" user="root" password="123" />
    </writeHost>
#Define the read-write information of the data host ywx2    

9.4 testing

Create test libraries and tables:
#db01
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"

#View on mycat
#node3
[root@node3 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@node3 conf]# mysql -uroot -p -h 127.0.0.1 -P8066
Enter password: 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
| user             |
+------------------+
2 rows in set (0.01 sec)
#In mycat, you can see the information of all tables in all taobao libraries

#You can only see order on sh1_ Table information of T
#db01 192.168.32.201:3307
[root@db01 tmp]# mysql -S /data/3307/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| user             |
+------------------+

#You can only see order on sh2_ Table information of T
#db01 192.168.32.201:3308
[root@db01 tmp]# mysql -S /data/3308/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
+------------------+

10, MyCAT core feature - slicing (horizontal split)

10.1 introduction to horizontal split

Because vertical segmentation is to distribute tables to different databases according to the classification of business, some business tables will be too large, and there are bottlenecks in single database reading, writing and storage, so it needs to be solved by horizontal splitting.

Compared with vertical splitting, horizontal splitting does not classify tables, but distributes them into multiple databases according to certain rules of a certain field, and each table contains some data. In short, we can understand the horizontal segmentation of data as the segmentation of data rows, that is, some rows in the table are segmented into one database, while others are segmented into other databases.

To split data, you need to define fragmentation rules. Relational database is a two-dimensional model of rows and columns. The first principle of splitting is to find the split dimension. For example, from the perspective of members, if the merchant order transaction system queries a member's order on a day and a month, it needs to be split according to the member combination date, and different data are grouped according to the member ID, so that all data query join s will be solved in the single database; From the perspective of merchants, if you want to query the number of orders of a certain merchant on a certain day, you need to split them according to the merchant ID; However, if the system wants to split according to both members and business data, there will be some difficulties. How to find an appropriate segmentation rule needs comprehensive consideration and measurement.

Several typical fragmentation rules include:

• By user ID For modeling, the data is scattered to different databases, and the data of users with the same data are scattered to one database;
• According to the date, the data of different months or even days are scattered into different databases;
• According to a specific field, or according to a specific range segment, it is scattered into different libraries.

The principle of segmentation is to find suitable segmentation rules according to the business and distribute them to different libraries. The following example is to calculate the module with user ID:

Since the data is split, there are advantages and disadvantages.

advantage:

• Good abstraction of splitting rules,join The database operation can be basically done;
• There is no performance bottleneck of single database, big data and high concurrency;
• Less transformation of application end;
• The stability and load capacity of the system are improved.

Disadvantages: