After mysql table sharding, how does one paging the table (160 million tables in total) after mysql table sharding, there are 15 million tables and 160 million data records? How does one paging the table?
Previously, we wanted to use union all to merge 160 Table result sets .. However, the data on the direct card is hidden ..
What should I do here? Thank you ~~~
Reply to discussion (solution)
What are the conditions for table sharding?
If the results of paging cannot be set by the table sharding condition, your table sharding fails.
It is not to say that union cannot be used, but not all of union, but some tables filtered by table sharding conditions.
What are the conditions for table sharding?
If the results of paging cannot be set by the table sharding condition, your table sharding fails.
It is not to say that union cannot be used, but not all of union, but some tables filtered by table sharding conditions.
Moderator Xu ~ The main data field in my table is the URL address, which has 1600 domain names. I divided it into 160 tables based on the domain name.
What are the conditions for table sharding?
If the results of paging cannot be set by the table sharding condition, your table sharding fails.
It is not to say that union cannot be used, but not all of union, but some tables filtered by table sharding conditions.
My SQL statement is like this (the * number is used if there are not many fields and they are all useful ):
What are the conditions for table sharding?
If the results of paging cannot be set by the table sharding condition, your table sharding fails.
It is not to say that union cannot be used, but not all of union, but some tables filtered by table sharding conditions.
My SQL statement is like this (the * number is used if there are not many fields and they are all useful ):
Select * from t1 union all select * from t2 union all select * from t3.... union all select * from t160
Since you are table sharding by domain name, it is retrieved by domain name.
As you can imagine, if a domain name is not in another table, this table does not need to be retrieved.
In this way, your search scope will not be much reduced?
When a table contains several different domain names, you need to create a directory table to specify a category of domain names in a table.
When searching, first check the directory tables to find the possible tables and then search for them.
If you use the table sharding function provided by mysql, it is much simpler.
Since you are table sharding by domain name, it is retrieved by domain name.
As you can imagine, if a domain name is not in another table, this table does not need to be retrieved.
In this way, your search scope will not be much reduced?
When a table contains several different domain names, you need to create a directory table to specify a category of domain names in a table.
When searching, first check the directory tables to find the possible tables and then search for them.
If you use the table sharding function provided by mysql, it is much simpler.
The ing between this domain name and the table name has been done, but now the paging list is opened, it should be the data of all the table domain names, and then paging, not a part of the domain name or a part of the table.
Do you have any special sorting conditions? If no, you can directly read the required data from a table by table.
Do you have any special sorting conditions? If no, you can directly read the required data from a table by table.
If no sorting is performed, you only need to list all the data. for example, if 160 tables are used, 15 million data must be obtained by Page... Why not read data from tables by table ?!
It would be easier to display the entire page.
1. the number of records in each table is known and should be recorded in the directory table in each change
2. no matter whether or not the table is sorted (if the order of the table is different), at most two sub-tables can be union.
For example, assume that there are three table shards, with 90,120 records and 290 records respectively.
If 40 entries are displayed on each page
1st pages 1 to 40 of Table 1
2nd Pages 41 to 80 of Table 1
3rd page 1 81 to 90 + 1 to 30 of table 2
4th pages 31 to 70 of table 2
5th to 110 of table 2
6th pages 111 to 120 + 1 to 30 of table 3 of table 2
.....
Easy to compute
It would be easier to display the entire page.
1. the number of records in each table is known and should be recorded in the directory table in each change
2. no matter whether or not the table is sorted (if the order of the table is different), at most two sub-tables can be union.
For example, assume that there are three table shards, with 90,120 records and 290 records respectively.
If 40 entries are displayed on each page
1st pages 1 to 40 of Table 1
2nd Pages 41 to 80 of Table 1
3rd page 1 81 to 90 + 1 to 30 of table 2
4th pages 31 to 70 of table 2
5th to 110 of table 2
6th pages 111 to 120 + 1 to 30 of table 3 of table 2
.....
Easy to compute
Thank you for your patience.