MySQL: Open Source database sharding technology

From Shard to Sharding

The word "Shard" in English means "fragmentation", and the technical terminology used as a database appears to have been found in large multiplayer online role-playing games (MMORPG). "Sharding" is called "fragmentation".

Sharding is not a new technology, but a relatively simple concept of software. As you know, the data table partitioning feature is available after MySQL 5, so many MySQL potential users are concerned about MySQL extensibility, and partitioning is a key indicator of whether or not a database is scalable (not the only metric). Database extensibility is an eternal topic, and MySQL's promoter is often asked how to do this, such as processing application data on a single database that needs to be partitioned, and so on. The answer is: Sharding.

Sharding is not a feature of a particular database software, but an abstraction over specific technical details, a solution that extends horizontally (Scale out, or horizontally, and outwards), with the primary purpose of breaking the I/O capability limits of a single node database server, Resolves the problem of database extensibility.

A matter of database extensibility

Speaking of database extensibility, this is a very big topic. The current business data has its own expansion solution, in the past relatively mature, but with the rapid development of the Internet, inevitably will bring some calculation mode of evolution, so many mainstream business systems will inevitably expose some deficiencies. For example, Oracle's RAC is a shared storage mechanism, for I/O-intensive applications, bottlenecks easily fall on the storage, such a mechanism determines that subsequent expansion can only be Scale up (upward expansion) type, for hardware costs, developer requirements, maintenance costs are relatively high.

Sharding is basically an extensibility solution for open source databases, and few have heard of the Sharding of business databases. The current trend of the industry is basically to embrace Scale out, gradually liberated from the Scale up.

Sharding's application Scenario

Any technology can play its part in a suitable situation. The same is Sharding. Online games, IM, BSP are more suitable for Sharding application scenarios. The commonality is that the data objects that are abstracted are associated with small data. For example IM, each user if abstracted into a data object, can be stored independently in any place, the data object is Share nothing; for example, the Blog service provider's site content, basically for user-generated content (UGC), can completely isolate different users to different storage sets, and transparent to the user.

This "Share nothing" is borrowed from the database cluster, for example, some types of data granularity is not "Share", such as the history of similar transaction information, if a record contains both seller information and buyer information, if over time, buy , sellers will continue to deal with other users, so that the inevitable two of the information will be distributed to different Sharding DB, and then if the search for buyers and sellers will cross more Sharding, the overhead will be relatively large.

Sharding is not a silver bullet for the database expansion scheme, but also a scenario where it is not appropriate, such as handling transaction-type applications that can be very complex. For transactions across different db, it is difficult to guarantee integrity and outweigh the gains. Therefore, the use of what kind of Sharding form, not mechanically.

The difference between sharding and database partitions (Partition)

Sometimes, Sharding is similar to the horizontal division (horizontal partitioning), many places on the internet also use horizontal zoning to refer to Sharding, but I personally think there is actually a difference between the two. Indeed, the idea of Sharding is from the idea of zoning, but database partitioning is essentially a data object-level process, such as table and index partitioning, each of which can have different physical storage attributes, or a single database-wide operation, while Sharding is able to cross databases, Even across the physical machine. (See comparison table)