MySQL database partitioning concept with 2 great benefits (1)

We all know that MySQL database partitioning (Partition) can improve the performance of MySQL database, so what is the MySQL database partition? And what are the benefits of its practical application? The following article is a description of the content.

What is a database partition?

Database partitioning is a physical database design technique that is quite familiar to DBAs and database modelers. Although partitioning technology can achieve many effects, its primary purpose is to reduce the amount of data read and write in specific SQL operations in order to decrease response time. There are two main forms of partitioning://You must pay attention to the concept of rows and columns (row is rows, column is columns)

Horizontal partitioning (horizontal partitioning) This form of partitioning is the partitioning of rows in a table, in such a way that the data set divided by the physical columns within different groupings can be combined for individual segmentation (single partitioning) or collective partitioning (one or more partitions). All the columns defined in the table can be found in each dataset, so the attributes of the table remain.

A simple example: a table with a ten-year invoice record can be partitioned into 10 different partitions by the MySQL database, each of which contains records for one year. (In this case, we'll talk about the partitioning method here, we can say it first, it must be separated by an attribute column, for example, the column used here is the year)

Vertical partitioning (Vertical partitioning) This partitioning method typically reduces the width of the target table by dividing the table vertically, allowing certain columns to be partitioned into specific partitions, each containing the rows of the columns.

A simple example: A table that contains large text and BLOB columns, which are not often accessed, is the time to partition these infrequently used text and blobs into another partition, while guaranteeing their data dependencies while increasing access speed.

When the database vendor begins to establish partitions (primarily horizontal partitions) in their database engine, DBAs and modelers must design the physical partition structure of the table, do not save redundant data (both contain data from the parent table in different tables), or join each other as a logical parent (typically a view). This practice invalidates most of the functionality of the horizontal partition and sometimes affects vertical partitioning.

Partitioning in MySQL 5.1

One of the most exciting new features in MySQL5.1 is the support for horizontal MySQL database partitioning. This is really good news for MySQL users, and she already supports most of the partitioning patterns:

Range – This mode allows DBAs to divide data into different ranges. For example, DBAs can divide a table into three partitions by year, data for the 80 's (1980 's), data for the 90 's (1990 's), and any data after 2000 (including 2000).

Hash (hash) – This mode allows the DBA to calculate the hash key for one or more columns of a table, and finally to partition the data region of the hash code with different values. For example, a DBA can create a table that partitions a table's primary key.

Key (key value) – an extension of the hash pattern above, where the hash key is generated by the MySQL system.

List (pre-defined list) – This mode allows the system to be segmented by the row data corresponding to the values of the DBA-defined list. For example, the DBA establishes a table spanning three partitions, based on data corresponding to the values for 2004 2005 and 2006 respectively.

Composite (composite mode)-very mysterious, haha, in fact, the above pattern of the combination of use, it does not explain. Example: on a table where the range partition has been initialized, we can then hash out one of the partitions.

Two points of benefit from partitioning:

Performance boost (increased performance)-In a scan operation, if the MySQL optimizer knows which partition contains the data that is needed in a particular query, it can directly scan the data for those partitions without wasting much time scanning for unwanted places. Need to give an example? Well, the million-row table is divided into 10 partitions, and each partition contains 100,000 rows of data, so the query partition takes only one-tenth of the full-table scan, a clear contrast.

Indexing a table of 100,000 rows at the same time is much faster than millions of rows. If you can set these partitions on a different disk, this time the I/O Read and write speed "unimaginable" (useless words, really too fast, theoretically 100 times times the speed of Ascension Ah, this is how fast response speed ah, so a bit unthinkable).

The simplification of data management (simplified data management)-partitioning technology enables DBAs to improve their data management capabilities. With excellent MySQL database partitioning, DBAs can simplify the way specific data operations are performed. For example, DBAs can guarantee the data integrity of the remaining partitions while deleting the contents of certain partitions (this is compared to the big act of data deletion on the table). In addition, the partition is managed directly by the MySQL system, and the DBA does not need to be manually partitioned and maintained. For example: This is not interesting, not to mention, if you are a DBA, as long as you divide the partition, you will not have to control it.

On the point of view of performance design, we are also very interested in the above content. By using partitioning and matching designs for different SQL operations, the performance of the database must be greatly improved. Let's take a look at this new MySQL 5.1 feature.

All of the following tests are on the Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM machine (show off ...) ), Fedora Core 4 and MySQL 5.1.6 Alpha run through.

How to do the actual partitioning to see the actual effect of the partition it. We build several tables of the same MyISAM engine, containing date-sensitive data, but only one of the partitions. Partitioned tables (table named Part_tab) we use the range-range partitioning model for MySQL database partitioning through the year:

2. MySQL> CREATE TABLE part_tab
4. -> (c1 int default NULL,
6. -> C2 varchar (+) default NULL,
8. -> C3 Date Default NULL->
10. ->) engine=MyISAM
12. -> PARTITION by RANGE (year (C3))
14. (PARTITION p0 VALUES less THAN (1995),
16. -> PARTITION p1 values less THAN (1996), PARTITION P2 values less THAN (1997),
18. -> PARTITION P3 values less THAN (1998), PARTITION P4 values less THAN (1999),
20. -> PARTITION P5 values less THAN (+), PARTITION P6 values less THAN (2001),
22. -> PARTITION P7 values less THAN (2002), PARTITION P8 values less THAN (2003),
24. -> PARTITION p9 values less THAN (2004), PARTITION P10 values less THAN,
26. -> PARTITION p11 VALUES less THAN MAXVALUE);
28. Query OK, 0 rows Affected (0.00 sec)

Have you noticed the last line here? Here is not the previous year divided into the range of the year is included, so as to ensure that the data will not be wrong, we have to remember Ah, otherwise the database for no apparent error you are cool.

Let's create a table with no MySQL database partition (table named No_part_tab):

1. MySQL> CREATE table No_part_tab
2. -> (c1 int (one) default NULL,
3. -> C2 varchar (+) default NULL,
4. -> C3 Date default NULL)
5. Engine=MyISAM;
6. Query OK, 0 rows affected (0.02 sec)

Let's write a stored procedure that inserts a total of 8 million different data into each partition on average in the partitioned table we just created. When it fills up, we insert the same data into the cloned table that is not partitioned:

2. MySQL> delimiter//
4. MySQL> CREATE PROCEDURE load_part_tab ()
6. -> Begin
8. -> Declare v int default 0;
10. -> While v < 8000000
12. -> do
14. -> INSERT INTO Part_tab
16. -> values (V, ' testing partitions ', adddate (' 1995-01-01 ', (rand (v) *36520) mod 3652));
18. -> Set vv = v + 1;
20. -> End while;
22. -> End
24. ->//
26. Query OK, 0 rows Affected (0.00 sec)
28. MySQL> delimiter;
30. MySQL> Call load_part_tab ();
32. Query OK, 1 row affected (8 min 17.75 sec)
34. MySQL> INSERT INTO no_part_tab select * from Part_tab;
36. Query OK, 8000000 rows Affected (51.59 sec) records:8000000 duplicates:0 warnings:0

The table is ready. Let's start with a simple range query for the data in these two tables. First partitioned, after not MySQL database partition, followed by the execution process parsing (MySQL explain command parser), you can see what MySQL has done:

2. MySQL> select count (*) from No_part_tab where
4. -> C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ';
6. +----------+ | COUNT (*) | +----------+ | 795181 | +----------+ 1 row in Set (38.30 sec)
8. MySQL> select count (*) from Part_tab where
10. -> C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ';
12. +----------+ | COUNT (*) | +----------+ | 795181 | +----------+ 1 row in Set (3.88 sec)
14. MySQL> Explain select COUNT (*) from No_part_tab where
16. -> C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ' \g
18. Id:1 Select_type:
20. Simple Table:no_part_tab
22. Type:all
24. Possible_keys:null
26. Key:null
28. Key_len:null
30. Ref:null
32. rows:8000000
34. extra:using where 1 row in Set (0.00 sec)
36. MySQL> Explain partitions select COUNT (*) from Part_tab where
38. -> C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ' \g
40. Id:1
42. Select_type:simple
44. Table:part_tab
46. Partitions:p1
48. Type:all
50. Possible_keys:null
52. Key:null
54. Key_len:null
56. Ref:null
58. rows:798458
60. extra:using where 1 row in Set (0.00 sec)

From the above results it is easy to see that the design of proper table partitioning can reduce the response time by 90% compared to non-partitioning. The command resolution Explain program also tells us that only the first partition was scanned during the query of the partitioned table, and the others were skipped.

The beep is too tough to say. Anyway, this partitioning feature is useful for DBAs to pull, especially for VLDB and systems that require rapid response.

Some views on vertical partitioning although MySQL 5.1 automatically implements horizontal partitioning, do not underestimate the vertical MySQL database partition when designing a database. Although it is a manual way to achieve vertical partitioning, you will benefit a lot in certain situations. For example, in the previously established table, the varchar field is rarely quoted by you, so does vertical partitioning increase speed? Let's look at the test results:

2. MySQL> desc part_tab, +-------+-------------+------+-----+---------+-------+
4. | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+
6. | C1 | Int (11) | YES | | NULL | |
8. | C2 | varchar (30) | YES | | NULL | |
10. | C3 | Date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in Set (0.03 sec)
12. MySQL> ALTER TABLE part_tab drop column C2;
14. Query OK, 8000000 rows Affected (42.20 sec) records:8000000 duplicates:0 warnings:0
16. MySQL> desc part_tab, +-------+---------+------+-----+---------+-------+
18. | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+
20. | C1 | Int (11) | YES | | NULL | |
22. | C3 | Date | YES | | NULL | | +-------+---------+------+-----+---------+-------+
24. 2 rows in Set (0.00 sec)
26. MySQL> select count (*) from Part_tab where
28. -> C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 '; +----------+
30. | COUNT (*) | +----------+
32. | 795181 | +----------+
34. 1 row in Set (0.34 sec)

After the design has dropped the varchar field, not only you, I also found that the query response speed to get another 90% of the time savings. So when you design a table, be sure to consider whether the fields in the table are really related, and whether they are useful in your query?

Additional Information

Such a simple article certainly cannot say all the benefits and essentials of the full MySQL 5.1 partitioning mechanism (although very confident in writing your own article level), here are a few interesting:

Supports all storage engines (MyISAM, Archive, InnoDB, etc.)

Indexes are supported on partitioned tables, including local indexes locally indexes, which is a one-to-two view mirror, assuming that a table has 10 partitions, its local index also contains 10 partitions.

The table on the metadata metadata for the partition can be found in the INFORMATION_SCHEMA database with the table named partitions.

The all SHOW command supports returning the MySQL database partition table and the index of the metadata.

The command and implementation of its operations has a maintenance function (more than a full table operation):

1. ADD PARTITION
2. DROP PARTITION
3. COALESCE PARTITION
4. REORGANIZE PARTITION
5. ANALYZE PARTITION
6. CHECK PARTITION
7. OPTIMIZE PARTITION
8. REBUILD PARTITION
9. REPAIR PARTITION

The above related content is to the MySQL database partition introduction, hope you can have some gains.

MySQL database partitioning concept with 2 great benefits (1)