1. Horizontal Partitioning What is horizontal partitioning? A rather vivid analogy: when you eat in a canteen there is only one window, and the queue for food gets so long that it coils into an S shape, which easily makes the people in line anxious and prone to chaos. Then a manager steps up, adds several more serving windows, and cuts that long queue in half into several lines. To put it even more vividly: you take a “scalpel” and slash a big table a few times, and the big table turns into several small tables. Horizontal partitioning places data into two or more independent tables according to certain conditions. That is, it partitions by records — different records can be stored separately, and each sub-table has the same number of columns. Horizontal splitting divides a table into multiple tables. Each table contains the same number of columns, but fewer data rows.For example, a table containing a billion rows can be horizontally partitioned into 12 tables, each small table representing one month of data in a specific year. Any query that needs data for a particular month only has to reference the table for that month. The conditions usually used to horizontally partition a table include: the date-time dimension, the region dimension, and so on — and of course more business dimensions. Let me give a few examples to explain: Case 1: A company’s sales record data volume is too large, so we can horizontally partition it by month, with each month’s sales records in a separate table. Case 2: A group has branch companies in various regions, and the group’s order data table is too large, so we can horizontally split it by the region where each branch is located. Case 3: A telecom company’s call detail records, after being horizontally split by date and city, were found to still have too much data, so they then split them horizontally by brand and number segment. Horizontal partitioning is usually used in the following situations: (1) The table has a very large volume of data; after splitting, the data and index pages that must be read during a query are reduced, and the number of index levels is lowered as well, speeding up queries. (2) The data in the table is inherently independent — for example, the table records data for each region or data for different periods separately, especially when some data is used often and some is not. (3) The data needs to be stored on multiple media. (4) Historical data and current data need to be separated. Advantages: 1: It reduces the data and index pages that must be read during a query, and also lowers the number of index levels, speeding up queries. Disadvantages: 1: Horizontal partitioning adds complexity to the application; queries usually need multiple table names, and querying all the data requires a union operation. In many database applications this complexity outweighs the benefits it brings, because as long as the index key isn’t large, when the index is used for queries and the table gains two to three times more data, a query only adds one more disk read of an index level.
2. Vertical Partitioning What is vertical partitioning? A vivid analogy: a small company grows into a large multinational enterprise in just a few short years, and its old departmental structure clearly can no longer satisfy the current business growth, so the CEO splits the company into a finance department, an HR department, a production department, a sales department….. setting up many departments at once, each with its own duties. That analogy is fairly apt, isn’t it? Hehe. When you vertically partition a table (without breaking third normal form), you put the primary key and some columns into one table, then put the primary key and some other columns into another table. The original table is split into multiple tables that each contain fewer columns. If some columns in a table are used often and other columns are not, vertical partitioning can be applied. Advantages: 1: Vertical partitioning makes the row data smaller, so a single data block (Block) can hold more data, which reduces the number of I/Os during queries (fewer Blocks are read per query). 2: Vertical partitioning a table achieves maximum use of the Cache. Disadvantages: 1: After vertical partitioning, the primary key becomes redundant, and the redundant columns need to be managed. 2: It gives rise to table JOIN operations (increasing CPU overhead), which needs to be avoided at the business level.
3. Table Sharding Table sharding is similar to horizontal partitioning, but without as clear a dividing line as horizontal partitioning; a Hash algorithm is used to spread the data across the shard tables, so that the IO is more balanced. Generally speaking, we separate databases by business or functional module, with different modules corresponding to different databases or tables, and then apply a certain strategy to further shard a page or function at a smaller database level — for example, the user table sharded by user ID into 128 tables, which should be able to improve system performance at low cost and offers good scalability. Reference: http://blog.csdn.net/tianlesoftware/article/details/7674501 Original article address: http://www.cnblogs.com/kerrycode/archive/2013/01/25/2876195.html
