Sharding means splitting the data across multiple physical servers. Usually, every database and table exists on every server. However, one (or more) table is split up as you suggest -- 1&2 to server A, 3,4,5 to server B, etc.
External to MySQL, some code is used to connect to the server that has the desired contents. For example, your client code would look at
org to decide which machine to connect to.
The reasons for Sharding (as opposed to other scaling techniques):
- The amount of data is too big for a single server,
- A single server cannot handle the write load of all the traffic.
Typically when you need to shard the database, you will also have multiple servers to handle the load on the client. And each client machine can connect to each database machine. The number of each does not need to be the same.
The tables that are not keyed by your
org may need to be duplicated on every shard. Again, this synchronization is not available in MySQL; you have to handle it.
As for which
org goes on which server, there are (at least) 3 ways to do it:
org to come up with a number that maps directly to one of the shards. (This gets messy when you realize you need to grow the number of shards.)
- A combo of the other two. (I prefer this.)
- A lookup table that is a mapping (many:1) from each
org to the shard it is on. (But, where to put this? Possibly on a separate db server. Possibly in a file on each client. Etc.)
The terms "Shard" and "Partition" and "Database" are often misused.
PARTITION splits up one table into multiple 'sub'-tables. There are very few advantages to this.
DATABASE is the one level of structure that MySQL gives you for organizing
TABLEs. It is about the same as deciding whether to put your drawers and socks in the same dresser drawer.
It sounds like you are launching into a product that will need to scale tremendously. I suggest you implement whatever is simplest for now. Then, in a few months plan on rethinking the design. And set aside time to completely rewrite the schema. The time you saved in throwing all your underwear in a single draw gives you the time to carefully decide how to scale the dresser drawers. Plus you will have more insight then.