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!