Differences between MySQL partitions and shards

To understand the difference between partitions and shards, first we need to know what shards and partitions are.
Shard: Shard Sharding is not a new technology, but a relatively simple concept of software. It is well known that after MySQL 5, the data table partitioning feature, before this, many of the potential users of MySQL has a concern for MySQL scalability, and whether the partitioning function is a measure of the scalability of a database is a key indicator (not the only indicator). Database extensibility is an eternal topic, and MySQL advocates often ask: how do you handle processing of application data on a single database that needs to be partitioned and so on?  The answer is: sharding. Sharding is not a feature attached to a particular database software, but an abstraction on top of specific technical details, a solution for horizontal scaling (scale out, or scale-out, and scale-out), with the main purpose of exceeding the I/O capability limits of a single-node database server. Resolve database extensibility issues.

The data is distributed horizontally to different DB or table through a series of sharding rules, and the specific DB or table that needs to be queried is found through the corresponding DB route or table routing rule for query operations. "Sharding" is usually referred to as "horizontal segmentation",This is also the focus of this discussion. What are the specific ways of slicing and routing? With this in hand, the reader will inevitably have some questions, and here's a simple example: we explain the logs in a blog app, such as the log article (article) table, which has the following fields: article_id (int), title (varchar), content (varchar (1024x768)), user_id (int)
The so-called specific user, the field representation of the database is "user_id". This is the "user_id", which is the basis for the basis and rules of the sub-Library we need.we can do this by putting user_id all the article information in the 1~10000 into the article table in DB1, user_id all the article information in 10001~20000 into the DB2 table, and so on, until article. In this way, the article data is naturally divided into various databases, to achieve the purpose of data segmentation.
To get the target data, we use the DB Routing feature to find the server where the corresponding data is stored.
See here, do you think this is similar to the table partition? There are many places where partitions and shards have a lot of similarities, but what difference do they have?

The difference between sharding and database partitioning (Partition)
Sometimes, sharding is similar to horizontal partitioning (horizontal partitioning), and many places on the web also use horizontal zoning to refer to sharding, but I personally think there is a difference between the two. Indeed, the idea of sharding is from the idea of partitioning, but the database partition is basically a data object-level processing, such as table and index partitions, each child dataset can have different physical storage properties, or a single database-scoped operation, and Sharding is able to cross the database, Even across the physical machine.

650) this.width=650; "class=" Size-medium wp-image-56 "src=" http://lijianjun.me/wp-content/uploads/2015/09/ 1-300x100.jpg "width=" "height=" style= "margin:0px 5px;padding:0px;border:none;"/>

The difference between MySQL shards and partitions

The partitioning (Partition) functionality provided by MySQL5.1 does enable partitioning of the table, but this partition is limited to a single database, and it cannot span the limits of the server. If you can guarantee that the amount of data is difficult to exceed the physical capacity of the existing database server, then simply use the partitioning (Partition) feature provided by MySQL5.1 to improve database performance; otherwise, consider the application sharding idea,spider storage The engine is a good choice.

This article is from the "do the most dedicated MySQL DBA" blog, so be sure to keep this source http://lijianjun.blog.51cto.com/11930278/1945515

Differences between MySQL partitions and shards