Shulou(Shulou.com)06/01 Report--

This article is about how to use MyCat sub-table sub-library principle analysis. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

MyCat is an open source distributed database system and a server that implements MySQL protocol. Front-end users can regard it as a database agent, which can be accessed by MySQL client tools and command line, while its back-end can communicate with multiple MySQL servers using MySQL native protocol, or with most mainstream database servers using JDBC protocol. Its core function is to divide tables and libraries. Split a large table horizontally into N small tables and store them in a back-end MySQL server or other database.

Mycat can realize the separation of read and write, table and library.

Master-slave replication is included in MySQL.

About the sharding module algorithm: according to the number of database clusters (or the number of tables in mycat, a table corresponds to a library) according to id.

Analysis of the principle of sub-table and sub-database using MyCat

The routing result in Mycat is determined by sharding field and sharding method. If there is an id field in the query condition, the query will fall into a specific shard. If there are no shard fields in the query, all db will be queried and the result level will be encapsulated to the client.

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

For example:

Inquiring

Select * from user_info

Send three db requests

If the query has always been the case (without conditions)

Convert to:

Select * from db1.user_infoselect * from db2.user_infoselect * from db3.user_info

Finally, the result set is encapsulated by mycat and returned to the client.

If you add a where id = 1 in such a conditional case, mycat will convert 1% 3% 1 on db2! Conversion to select * from db2.user_info where id = 1 is very efficient if the query is sharding. Just send one message and do it.

If it is not a sharding field, three messages 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: real-time viewing

Then a quick inquiry is clear at a glance.

Note the paging query:

Select * from user_info limit 0Pol 2

Which data is sliced?

Send three select requests to three repositories to get three on six results

Randomly select a pair and return it to the client.

What if the condition of sorting is added?

Select * from user_info order by id limit 0J2 (equivalent to taking out the two largest pieces of data)

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

If it is

Select * from user_info limit 0pl 3

What is returned in each request is changing and is random!

Db1 takes two db2 and one db3 at random

Thank you for reading! On how to use MyCat sub-table sub-library principle analysis is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.