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

How to improve MySQL performance through Partition partition, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

What is a partition?

Database partitioning is a physical technique that DBAs and database modelers are quite familiar with. While partitioning can achieve many things, its primary goal is to reduce the total amount of data read and written in a particular SQL operation to reduce response time.

There are two main forms of partitioning://here must pay attention to the concept of rows and columns (row is row, column is column)

1. Horizontal Partitioning This form of partitioning is partitioning the rows of a table in such a way that data sets partitioned by physical columns within different groupings are combined for individual partitioning (single partition) or collective partitioning (one or more partitions). All columns defined in the table are found in each dataset, so the table properties are preserved.

A simple example: a table containing ten years of invoice records can be partitioned into ten different partitions, each containing records for one of the years. (Peng Yi Note: The specific partitioning method used here will be discussed later. We can say one thing first. It must be divided by a certain attribute column. For example, the column used here is the year.)

2. Vertical partitioning is generally used to reduce the width of the target table by dividing the table vertically, so that certain columns are divided into specific partitions, each of which contains the rows corresponding to the columns.

A simple example: a table containing large text and BLOB columns, these text and BLOB columns are not often accessed, this time it is necessary to divide these infrequently used text and BLOB into another partition, in order to ensure their data correlation at the same time can improve access speed.

As database vendors begin to build partitions (mainly horizontal partitions) into their database engines, DBAs and modelers must design the physical partitioning structure of tables so that they do not store redundant data (different tables contain data from parent tables at the same time) or join them together into a logical parent object (usually a view). This approach disables most of the functionality of horizontal partitioning and sometimes affects vertical partitioning.

Partitioning in MySQL 5.1

Perhaps the most exciting new feature in MySQL 5.1 is support for horizontal partitioning. This is really good news for MySQL users, and it already supports most 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, with data from the 1980s (1980's), data from the 1990s (1990's), and any data from the year 2000 (and beyond).

Hash-This pattern allows DBAs to compute a Hash Key for one or more columns of the table, and finally partition the data regions corresponding to different values of this Hash key. For example, a DBA can create a table that partitions the table's primary key.

Key-An extension of the Hash pattern above, where the Hash Key is generated by MySQL.

List-This pattern allows the system to partition data by rows corresponding to the values of DBA-defined lists. For example, the DBA creates a table spanning three partitions, based on data corresponding to 2004, 2005, and 2006 values.

Composite(Composite mode) -very mysterious, haha, in fact, is the combination of the above modes, do not explain. For example: On a table that has been initialized with Range partitions, we can hash one of the partitions.

The benefits of zoning are too many, too many. How many? I don't know, guess it yourself, don't use it if you don't think it's much, I don't beg you to use it anyway. But here I emphasize two advantages:

Increased performance-During scanning operations, if MySQL's optimizer knows which partitions contain the data needed for a particular query, it can scan those partitions directly instead of wasting a lot of time scanning unnecessary places. Need an example? Well, a million-row table is divided into 10 partitions, each containing 100,000 rows of data, so querying partitions takes only one-tenth of the time of a full table scan, a clear contrast. Also, indexing a 100,000-row table is much faster than indexing a million-row table. If you can set up these partitions on different disks, then the I/O read and write speed is "unimaginable"(no wrong word, really fast, theoretically 100 times faster, this is how fast the response speed ah, so a bit unthinkable).

Simplified data management-Partitioning technology allows DBAs to improve their ability to manage data. With good partitioning, DBAs can simplify how certain data operations are performed. For example, DBAs can delete the contents of some partitions while maintaining the data integrity of the remaining partitions (this is compared to the big action of deleting data from tables).

In addition, partitions are directly managed by MySQL, and DBAs do not need to manually partition and maintain them. For example: this example is meaningless, do not speak, if you are DBA, as long as you divide the partition, you do not have to care about it later.

From the point of view of performance design, we are also interested in the above content drops. By using partitioning and matching design for different SQL operations, database performance can be dramatically improved. Let's take a look at the new features of MySQL 5.1.

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

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

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.