Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve horizontal sub-table and vertical sub-table in mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What is a database partition?

Database partitioning is a physical database design technology, which is quite familiar to DBA and database modelers. Although partitioning technology can achieve many effects, its main purpose is to reduce the total amount of data read and write in a particular SQL operation to reduce response time.

There are two main forms of partition: / / be sure to pay attention to the concept of rows and columns here (row is a row and column is a column)

Horizontal partitioning (Horizontal Partitioning) is a form of partitioning in which rows of tables are partitioned so that data sets divided by physical columns in different groups are combined for individual partition (single partition) or collective partition (one or more partitions). All the columns defined in the table can be found in each dataset, so the characteristics of the table are still maintained.

To take a simple example: a table containing ten-year invoice records can be partitioned into ten different partitions, each containing records for one year. (note: the specific partition method used here will be discussed later. We can first say that it must be divided by a certain attribute column, for example, the column used here is the year.)

Vertical partitioning (Vertical Partitioning) generally reduces the width of the target table through vertical partitioning of the table, so that some specific columns are divided into specific partitions, and each partition contains rows corresponding to the columns in it.

To take a simple example: a table that contains large text and BLOB columns, and these text and BLOB columns are not often accessed, so it is necessary to divide these infrequently used text and BLOB into another partition to improve access speed while ensuring their data relevance.

When database vendors begin to set up partitions (mainly horizontal partitions) in their database engines, DBA and modelers must design the physical partitioning structure of the tables and do not save redundant data (different tables also contain data from the parent table) or join each other into a logical parent object (usually a view). This will invalidate most of the functions of horizontal partitions and sometimes have an impact on vertical partitions.

Partitioning in MySQL 5.1

Perhaps the most exciting new feature in MySQL5.1 is support for horizontal partitioning. This is really good news for MySQL users, and she already supports most of the partitioning modes:

The Range (range) C mode allows DBA to divide the data into different ranges. For example, DBA can divide a table into three partitions by year, data from the 1980s (1980s), data from the 1990s (1990s), and any data after 2000 (including 2000).

The Hash (hash) C mode allows DBA to compute the Hash Key of one or more columns of the table, and finally partition the data regions corresponding to different values of the Hash code. For example, DBA can create a table that partitions the primary key of the table.

An extension of Hash mode above Key (key value) C, where Hash Key is generated by the MySQL system.

The List (predefined list) C mode allows the system to split the row data corresponding to the values of the list defined by DBA. For example, DBA creates a table that spans three partitions, based on the data corresponding to the 2004, 2005 and 2006 values.

Composite (composite mode)-it's mysterious, , it's actually a combination of the above patterns, so I don't want to explain it. For example: on a table that initializes a Range range partition, we can perform a hash hash partition on one of the partitions.

There are too many benefits from zoning. How many are there? I don't know. Guess for myself. Don't use it if you don't think it's much. Anyway, I won't ask you to use it. But here I would like to emphasize two advantages:

Performance improvement (Increased performance)-during a scan operation, if MySQL's optimizer knows which partition contains the data needed in a particular query, it can scan those partitions directly without wasting a lot of time scanning places that are not needed. Need an example? Well, a table with millions of rows is divided into 10 partitions, and each partition contains 100, 000 rows of data, so querying partitions takes only 1/10 of the time of a full table scan. At the same time, indexing a table of 100, 000 rows is much faster than that of a million rows. If you can build these partitions on different disks, the read and write speed of Icano is "unimaginable" (right, it's really too fast, it's 100 times faster in theory, how fast it is, so it's a little unimaginable).

Simplification of data management (Simplified data management)-partitioning technology can improve the ability of DBA to manage data. With good partitioning, DBA can simplify the way specific data operations are performed. For example, DBA can ensure the data integrity of the remaining partitions while deleting the contents of some partitions (this is compared to the big action of deleting data from the table).

In addition, partitions are directly managed by the MySQL system, and DBA does not need to be divided and maintained manually. For example: this example is boring, don't talk about it, if you are DBA, as long as you divide it, you don't have to worry about it in the future.

From the point of view of performance design, we are also very interested in the above content. Through the use of partitions and matching designs for different SQL operations, database performance is sure to be greatly improved. Let's take a look at the new features of MySQL 5.1.

All the tests below are on the Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM machine. ), running on Fedora Core 4 and MySQL 5.1.6 alpha.

How to do the actual partitioning

Take a look at the actual effect of partitioning. We build several tables with the same MyISAM engine that contain date-sensitive data, but partition only one of them. The partitioned table (named part_tab) We use the Range range partitioning mode, partitioning by year:

> CREATE TABLE part_tab

-> (C1 int default NULL

-> c2 varchar (30) default NULL

-> c3 date default NULL

->

->) engine=myisam

-> PARTITION BY RANGE (year (c3)) (PARTITION p0 VALUES LESS THAN (1995)

PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (1997)

PARTITION p3 VALUES LESS THAN (1998), PARTITION p4 VALUES LESS THAN (1999)

PARTITION p5 VALUES LESS THAN (2000), PARTITION p6 VALUES LESS THAN (2001)

-> PARTITION p7 VALUES LESS THAN (2002), PARTITION p8 VALUES LESS THAN (2003)

-> PARTITION p9 VALUES LESS THAN (2004), PARTITION p10 VALUES LESS THAN (2010)

-> PARTITION p11 VALUES LESS THAN MAXVALUE)

Query OK, 0 rows affected (0.00 sec)

Did you notice the last line here? The year range that does not belong to the previous year division is included here, so as to ensure that the data will not go wrong. We should remember it later, otherwise you will be happy if the database goes wrong for no reason. So let's create a table without partitions (table name no_part_tab):

Mysql > create table no_part_tab

-> (C1 int (11) default NULL

-> c2 varchar (30) default NULL

-> c3 date default NULL) engine=myisam

Query OK, 0 rows affected (0.02 sec)

Let's write a stored procedure (thanks to Peter Gulutzan for the code, and if you need a Chinese translation of Peter Gulutzan's stored procedure tutorial, you can also ask me, chenpengyi ◎ gmail.com), which can insert an average of 8 million different pieces of data into each partition into the partitioned table I just created. When filled, we insert the same data into the unpartitioned clone table:

Mysql > delimiter / /

Mysql > CREATE PROCEDURE load_part_tab ()

-> begin

-> declare v int default 0

-> while v

< 8000000 ->

Do

-> insert into part_tab

-> values (v values testing partitions',adddate ('1995-01-01), (rand (v) * 36520) mod 3652))

-> set v = v + 1

-> end while

-> end

-> / /

Query OK, 0 rows affected (0.00 sec)

Mysql > delimiter

Mysql > call load_part_tab ()

Query OK, 1 row affected (8 min 17.75 sec)

Mysql > insert into no_part_tab select * from part_tab

Query OK, 8000000 rows affected (51.59 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

The watches are all ready. Let's start with a simple range query for the data in these two tables. First partitioned, then unpartitioned, followed by execution process parsing (MySQL Explain command parser), you can see what MySQL has done:

Mysql > select count (*) from no_part_tab where

-> c3 > date '1995-01-01' and c3

< date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (38.30 sec) mysql>

Select count (*) from part_tab where

-> c3 > date '1995-01-01' and c3

< date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (3.88 sec) mysql>

Explain select count (*) from no_part_tab where

-> c3 > date '1995-01-01' and c3

< date '1995-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: no_part_tab type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8000000 Extra: Using where 1 row in set (0.00 sec) mysql>

Explain partitions select count (*) from part_tab where

-> c3 > date '1995-01-01' and c3

< date '1995-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: part_tab partitions: p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 798458 Extra: Using where 1 row in set (0.00 sec) 从上面结果可以容易看出，设计恰当表分区能比非分区的减少90％的响应时间。而命令解析Explain程序也告诉我们在对已分区的表的查询过程中仅对第一个分区进行了扫描，其他都跳过了。 哔厉吧拉，说阿说……反正就是这个分区功能对DBA很有用拉，特别对VLDB和需要快速反应的系统。 对Vertical Partitioning的一些看法 虽然MySQL 5.1自动实现了水平分区，但在设计数据库的时候不要轻视垂直分区。虽然要手工去实现垂直分区，但在特定场合下你会收益不少的。例如在前面建立的表中，VARCHAR字段是你平常很少引用的，那么对它进行垂直分区会不会提升速度呢？咱们看看测试结果： mysql>

Desc part_tab

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | C1 | int (11) | YES | | NULL |

| | c2 | varchar (30) | YES | | NULL | |

| | c3 | date | YES | | NULL |

+-+ +

3 rows in set (0.03 sec)

Mysql > alter table part_tab drop column c2

Query OK, 8000000 rows affected (42.20 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

Mysql > desc part_tab

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | C1 | int (11) | YES | | NULL |

| | c3 | date | YES | | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > select count (*) from part_tab where

-> c3 > date '1995-01-01' and c3 < date '1995-12-31'

+-+

| | count (*) |

+-+

| | 795181 |

+-+

1 row in set (0.34 sec)

After removing the VARCHAR field in the design, not only you, but also I found another 90% time savings in query response speed. So when designing a table, you must consider whether the fields in the table are really relevant and whether they are useful in your query.

Supplementary explanation

Such a simple article certainly can't cover all the benefits and key points of the full MySQL 5.1 partitioning mechanism (although you are confident in your own writing skills), here are a few things you are interested in:

Support for all storage engines (MyISAM, Archive, InnoDB, etc.)

Indexes are supported on partitioned tables, including the local index local indexes, which is mirrored on an one-to-one view. If a table has ten partitions, its local index also contains ten partitions.

The table about the partitioned metadata Metadata can be found in the INFORMATION_SCHEMA database with the name PARTITIONS.

The All SHOW command supports returning indexes for partitioned tables and metadata.

The commands for its operation and the maintenance functions implemented are (more than the operation on the whole table):

ADD PARTITION

DROP PARTITION

COALESCE PARTITION

REORGANIZE PARTITION

ANALYZE PARTITION

CHECK PARTITION

OPTIMIZE PARTITION

REBUILD PARTITION

REPAIR PARTITION

The above is how to achieve horizontal table and vertical table in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.