Analysis on the principle of mycat sub-database and table

MyCat is an open source distributed database system and a server that implements the MySQL protocol. Front-end users can regard it as a database agent and use MySQL client tools and command line access, and its backend can use the MySQL native protocol to communicate with multiple MySQL servers, and can also use the JDBC protocol to communicate with most mainstream database servers. Its core function is to divide tables and databases, which will be a large The table is divided horizontally into N small tables and stored in the back-end MySQL server or other databases.

Mycat can realize read-write separation, table and database partitioning

Master-slave replication comes with MySQL~

About the sharding modulo algorithm : Modulo based on id Based on the number of database clusters (or the number of tables, one table in mycat corresponds to one library)

Using MyCat table and database principle analysis

The routing results in Mycat are determined by the shard field and shard method. If there is an id field in the query condition, the query will fall to a specific shard. If you query a field without fragmentation, all dbs will be queried and the results will be encapsulated and sent to the client.

Modify /mycat/conf/log4j2.xml log level to debug

For example:

In query

select * from user_info

Send three A db request

If it is a query (without conditions)

converts to:

select * from db1.user_info
select * from db2.user_info
select * from db3.user_info

Finally, the result set is encapsulated by mycat and returned to the customer End

If you add where id = 1, under such conditions, mycat will convert 1%3=1 on db2! Convert to select * from db2.user_info where id = 1. If the query is sharding, it is very efficient. Just send one and it will be done

If it is not a fragment field, three will be sent! The efficiency is very low

For example, where name = 'jack' will send three queries to each database according to the conditions to return the results

tailf -200f mycat.log: Perform View in real time

Then quickly query at a glance

Pay attention to the paging query:

select * from user_info limit 0,2

Which data shard is it?

Send three select requests to three libraries to obtain three pairs of six results

Randomly select a pair and return it to the client

If added What about the sorting conditions?

 select * from user_info order by id  limit 0,2   （相当于取出最大的两条数据）

First send three selections, each of which is the largest two, and then return it to mycat for comprehensive selection and return the largest two to the client

If it is

 select * from user_info   limit 0,3

What is returned every time the request is changed is random!

db1 takes two random ones of db2 and db3

The above is the detailed content of Analysis on the principle of mycat sub-database and table. For more information, please follow other related articles on the PHP Chinese website!