DWQA QuestionsCategory: DatabaseAfter MySQL is divided into tables, how to query the records in all tables?
Senior typist asked 9 months ago

After a table is divided into n tables, how to query all the data with table type = 1?
(a user table, divided into 10 tables, I want to get all the data with user type = 1)

Senior typist replied 9 months ago

This is the existing structure. We have divided n tables. We have the right to adjust the DB in our business. Now we need to find out all the data. When we divide the tables, we split them by another field uid. The application is also based on this uid for retrieval. It seems that we can only do it by @ YJ. Lee’s method, @ Samoa’s method. We have the right to do it, and prepare for the operation and maintenance of the student guide. Come out, thank you!

Senior typist replied 9 months ago

I’ve invited some dada to see if they can help.

10 Answers
Best Answer
ex90rts answered 9 months ago

MySQL has a merge storage engine dedicated to this task. Learn from this blog:MySQL merge engine sub table

Fwolf answered 9 months ago

The number of sub tables will affect which scheme to adopt.
From the database level to solve:

  • MySQL merge engine, @ Samoa answers
  • MySQL table partition is only supported after version 5.1, @ yj.lee replied

From the SQL level:
The standard usage is union, @ YJ. Lee also replied.
There may be a large number of sub tables in the main topic, so the SQL method may not be suitable, so the solution at the database level can be considered. It can be solved from the application layer by multiple queries and merging results, or by building a simple redundant table / cache based on type, or by using any method but caching query results, etc.
All of the above are sub table situations. If it is sub database, the database level method is basically not feasible and can only be solved from the application.

Yj_Lee answered 9 months ago
(SELECT xx FROM table1 WHERE type=1) union
(SELECT xx FROM table1 WHERE type=1) union
(SELECT xx FROM table1 WHERE type=1) union
(SELECT xx FROM table1 WHERE type=1) 

If you need a second extraction

  • Or write your own program to extract it from the union result set above.
  • Or subquery
SELECT xxx FROM (
    (SELECT xx FROM table1 WHERE type=1) union
    (SELECT xx FROM table1 WHERE type=1) union
    (SELECT xx FROM table1 WHERE type=1) union
    (SELECT xx FROM table1 WHERE type=1) 
) t WHERE t.xxx....

However, you don’t need to look at those I wrote above. Now some general programs use the sub table because MySQL 5.1 does not support partition. At least, what’s appropriate for your scenario ispartition, instead of the sub table.

mcfog answered 9 months ago

If the amount of data can’t be disassembled, it can be disassembled directly. After MySQL is disassembled, it will be regarded as the persistence layer of key value. Do not want to cross tables and libraries. (except for the demand of statistics / data warehouse, a query can be done for a long time at no cost to block the whole server.)
Carefully choose the basis for splitting. For example, the type is very balanced, and the application scenario always has a known type. You may as well split by type directly.
Secondly, if there is a large amount of data, and there are different retrieval requirements from the split latitude, what you need is a search service, or index service. I have no practical experience in this field, so I can only give a string of keywords Lucene Solr Sphinx elasticsearch

zwan0518 answered 9 months ago

In this case, the table should be stored according to the route, but if you want to query a table that is not by the route key, you need to query the whole database.

cevin answered 9 months ago

Visually, you need partitions instead of tables.
The best way to split tables is the merge engine, which is applicable to most scenarios, depending on the specific data volume.
In MySQL deployment, please refer to:
partition
Sub table
sharding
However, MySQL tens of millions table index is properly designed and the query speed is very fast. I don’t know why to divide the watch.

Official East NP answered 9 months ago

Make a view

liuyix answered 9 months ago

It is better to have middleware to support the split table architecture to shield these details.

Mocha Steven answered 9 months ago

A bad thing about the merge engine is that the sub table storage engine must be MyISAM, and MyISAM does not support transactions!!! , if the storage engine of your table is InnoDB in advance, there will be a big problem after changing it to MyISAM.
In my opinion, the split table is still your original scheme, but there is a problem in the search. You can consider to store the search field in a separate table, in NoSQL (mongodb), or in redis. First, retrieve the properties of the split table field (that is, determine the split table ID) through the search criteria, and then query the detailed data.

tonni answered 9 months ago

Is there a mature scheme for sub database and sub table?