Before using database middleware, I thought that the operation of the sub-database table could be very complex for the operation of the table. Because if the data is shard-stored, if the data in the primary table is 1 shards, and the data from the table is in 2 shards, then this one-time query needs to cross-node, and if the business is a little more complicated, it will obviously cause unnecessary performance loss. Today, when studying the MYCAT documentation, I saw that mycat solved the problem skillfully:
"Mycat borrowed from the design ideas of the Rookie Foundation DB in the Newsql field, and the foundation DB innovative proposed the concept of table group, which relies on the primary table for the storage location of the child table, and physically adjacent to the store, Therefore, the problem of efficiency and performance of jion is completely solved, according to this idea, a data slicing strategy based on e-r relationship is proposed, and the record of the child table is stored on the same data shard with the associated parent table record. “
There are currently two tables: Tab_user (Id,name) and Tab_orders (id,user_id), and the ID in Tab_user as the foreign key in orders.
To configure in Schema.xml:
< table name = "Tab_user" DataNode = "DN1,DN2" rule = "Mod-long" > < childtable name = "Tab_orders" Joinkey = "user_id" Parentkey = "id" /> </ table >
This configuration code tells Mycat to associate Tab_orders's user_id with the Tab_user ID, and when the Orders table is inserted, it will determine which shard the user_id of the inserted data is in, and then insert the data into that shard. This enables the purpose of storing the associated data in a shard. Of course, if more tables are associated, continue to configure childtable under ChildTable.
Mycat Learning Diary: About Union Table query