Sharding databases and tables
I’ve been working with php for 5 years, and most scenarios are at the business layer. Put plainly, it’s just CRUD. I also know that large-scale data processing in databases involves sharding databases and tables, generally horizontal sharding and vertical sharding, but actually doing it was impossible. Today I read an article 《Sharding databases and tables? How to never migrate data and avoid hot spots?》, and my thinking suddenly became smooth.
Generally speaking, once a mysql table reaches around 1M rows the query speed can’t keep up.If the daily data volume is 1M then this data area needs to be handled. My previous thinking was like this:
Fix 1M rows of data per table, and assign each table a range based on the data id. Then when querying, find the table based on the id, but this has a problem: if the same user’s data is in different tables, then to query all of that user’s data you’d need to join tables, and in reality sharded table queries have time and range limits, there’s no case where you pull out all the data at once. (And when I attended an interview, an interviewer directly gave me this kind of question:
“I have 100 million rows of data, pull them all out at once, how do you do it with mysql.” I was instantly dumbfounded, there was no way to do it; it might be doable at the code layer, but with database queries I really don’t know.)
According to the article I read above, this is the range scheme
Besides the query problem, there’s also the hot spot problem. In some time period the data volume surges, and one table alone can’t hold up.
_The article also introduced the hash modulo method _
The hash modulo method easily leads to data migration problems; if the data volume is large, the migration cost is relatively high.
According to the article, you can first do range grouping, and then use hash modulo to shard the tables within the group
_The final table design is as _
