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

This article shows you how to achieve partition in MySQL, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. InnoDB logical storage structure

First of all, we should introduce the concept of InnoDB logical storage structure and extent, all its data are logically stored in the table space, and the table space is composed of segments, extents and pages.

Segment

The segment is the segment area of the above figure, and the common segments are data segment, index segment, rollback segment and so on. In the InnoDB storage engine, the management of the segment is completed by the engine itself.

Zone

An area is the extent area in the image above. An area is a space composed of consecutive pages. No matter how the size of the page changes, the size of the area is always 1MB by default.

In order to ensure the continuity of pages in the zone, the InnoDB storage engine requests 4-5 extents from disk at a time, and the size of InnoDB pages defaults to 16kb, that is, a zone has a total of 64 (1MB/16kb=16) consecutive pages.

At the beginning of each segment, 32 page-sized fragmented pages are used to store data, and only after using these pages is a 64-page application. The goal is that for small tables or segments of the undo class, you can start applying for less space and save disk overhead.

Page

The page is the page area of the image above, which can also be called a block. Pages are the smallest unit of InnoDB disk management. The default size is 16KB, which can be set by the parameter innodb_page_size.

Common page types are: data page, undo page, system page, transaction data page, insert buffer bitmap page, insert buffer free list page, uncompressed binary large object page, compressed binary large object page and so on.

two。 Zoning Overview

Zoning

The partition we are talking about here is that the records of different rows in the same table are assigned to different physical files, and there are several .idb files in several partitions, which is not the zone we just mentioned. MySQL added support for horizontal partitioning at 5.1.

Partitioning is the decomposition of a table or index into smaller, more manageable parts.

Each zone is independent and can be handled independently or as part of a larger object. This is a feature supported by MySQL, and the business code does not need to be changed. You should know that MySQL is OLTP-oriented data, unlike other DB like TIDB.

Then you should be very careful about the use of partitions, because not knowing how to use partitions can have a negative impact on performance.

The partition of a MySQL database is a local partition index, in which both data and indexes are stored. That is, the clustered and nonclustered indexes for each zone are placed in its own zone (different physical files). Global partitioning is not currently supported in MySQL databases.

Regardless of the type of partition, if there is a primary key or unique index in the table, the partitioned column must be part of the unique index.

three。 Partition Typ

At present, MySQL supports several types of partitions, RANGE partition, LIST partition, HASH partition, KEY partition.

If the table has a primary key or unique index, the partitioned column must be part of the unique index. Nine times out of ten, RANGE is used in actual combat.

RANGE partition

RANGE partitioning is the most commonly used type of partition in practice, and row data is partitioned based on column values that belong to a given contiguous interval.

Remember, however, that an exception is thrown when the inserted data is not in a value defined in a partition. RANGE partition is mainly used for date column partition, such as transaction table, sales table and so on. Data can be stored according to the year and month.

If you partition data of type date in a unique index, note that the optimizer can only optimize functions such as YEAR (), TO_DAYS (), TO_SECONDS (), and UNIX_TIMESTAMP (). Int type can be used in actual combat, so just store yyyyMM. And you don't have to worry about functions.

CREATE TABLE `mroomtestdb`.`Order` (`id` INT NOT NULL AUTO_INCREMENT, `partition_ key` INT NOT NULL, `amt` DECIMAL (5) NULL, PRIMARY KEY (`id`, `Order`) PARTITION BY RANGE (partition_key) PARTITIONS 5 (PARTITION part0 VALUES LESS THAN (201901), PARTITION part1 VALUES LESS THAN (201902), PARTITION part2 VALUES LESS THAN (201903), PARTITION part3 VALUES LESS THAN (201904), PARTITION part4 VALUES LESS THAN (201905))

At this time, let's insert some data.

INSERT INTO `mroomtestdb`.`Order` (`id`, `partition_ key`, `amt`) VALUES ('1million,' 201901terrain, '1000'); INSERT INTO `mroomtestdb`.Order` (`id`, `partition_ key`, `amt`) VALUES (' 2percent, '201902efficiency,' 800'); INSERT INTO `mroomtestdb`.`Order` (`id`, `partition_ key`, `amt`) VALUES ('399,' 201903mm, `amt`)

Now let's make a query. Through the EXPLAIN PARTITION command, we find that the SQL optimizer only needs to search the corresponding areas, not all the partitions.

If there is a problem with the sql statement, then all zones will be taken. It can be dangerous. So after partitioning the table, the select statement must use the partition key.

The following three are not very commonly used, so they will be mentioned at once.

LIST partition

LIST partitions are similar to RANGE partitions, except that the values of partition columns are discrete rather than contiguous. LIST partitions use VALUES IN because the values for each partition are discrete, so only values can be defined.

HASH partition

Speaking of hashes, the goal is clear: to distribute data evenly among predefined partitions, ensuring that the number of each partition is roughly the same.

KEY partition

KEY partitions are similar to HASH partitions, except that HASH partitions use user-defined functions, while KEY partitions use functions provided by the database.

four。 Partition and performanc

A technology does not necessarily bring benefits when it is used. For example, explicit locks are more powerful than built-in locks, and if you don't play well, it may lead to a very bad situation.

The same is true of partitioning, which does not run faster when the partitioned database is started. Partitioning may improve the performance of some sql statements, but partitioning is mainly used for database high availability management.

Database applications are divided into two categories, one is OLTP (online transaction processing), the other is OLAP (online analytical processing).

For OLAP application partitioning can indeed improve query performance, because the general analysis needs to return a large amount of data, if you partition by time, such as a month of user behavior and other data, you only need to scan the response partition.

In OLTP applications, partitioning should be more careful. It usually does not get 10% of the data of a large table, but mostly returns a few pieces of data through the index.

For example, for a table with 1000W data, if a select statement goes to the secondary index, but does not go to the partition key. Then the result will be awkward.

If the height of the 1000W B + tree is 3, there are now 10 partitions. So it's not a logical IO that needs (3 times 3) * 10 times? (3 clustered indexes, 3 secondary indexes, 10 partitions). So please be careful when using partition tables in OLTP applications.

In daily development, if you want to see the partition query results of the sql statement, you can use explain partitions + select sql to get it, and partitions identifies which partitions have gone.

Mysql > explain partitions select * from TxnList where startTime > '2016-08-25 00001 and startTime

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.