High-performance MySQL (7) Zoning Technology detailed

In my previous 2 blogs, I have briefly introduced the basic theory of partitioning technology after MySQL5.1 and the table storage files and features of partitioning technology, the blog address is as follows:

http://janephp.blog.51cto.com/4439680/1305220

http://janephp.blog.51cto.com/4439680/1305937

Today we are going to introduce some usage scenarios and mechanisms for partitioning technology.

The way MySQL implements partitioned tables--encapsulating the underlying table--means that the index is also defined by the partition's child table, without a global index.

One, in the following scene, the role is very large:

1, the table is very large can not be all put into memory, or the last part of the table has hot data, the other is historical data.

2, the partition's data is easier to maintain, can the entire partition operation, but also can the independent partition optimization, inspection, repair operation.

3, the data of the partition table can distribute on the different physical device

4, you can use partitioned tables to avoid certain special bottlenecks, such as InnoDB single indexed mutex access.

The partition table itself has some limitations:

1, a table can only have 1024 partitions

2, the partition expression must be an integer, or an expression that returns an integer.

3, the partition table cannot use the foreign key

Second, the principle of the partition table

The storage engine manages the underlying tables of the partitions and manages the normal tables, and all underlying tables must use the same engine, and from the storage engine, the underlying table and the normal table are any different.

The partition table is based on the following operational logic:

SELECT:

When querying a partitioned table, the partition layer first opens and locks all the underlying tables, and the optimizer first determines whether some of the partitions can be filtered out and then manipulated.

INSERT:

When writing a record, the partitioning layer first opens and locks all the underlying tables, and then determines which partition receives the record and operates.

DELETE:

When a record is deleted, the partitioning layer first opens and locks all the underlying tables, and then determines which partition receives the record and operates.

UPDATE:

When a record is updated, the partitioning layer opens and locks all the underlying tables, identifies the partitions, then extracts the data and updates it, then determines which partition the updated data is placed in, writes to it, and deletes the underlying table of the original data.

But not every operation locks all the tables, and if the engine has its own row-level locks, such as InnoDB, the corresponding table locks are released at the partition layer.

Third, how to use

There are generally 2 strategies:

1, full volume scan data, do not index any.

2, index data, and separate hot spots.

But you have to be aware of a few things:

1, NULL will make the partition filter invalid.

The first partition is a special partition, assuming that all order_date null or illegal values are stored in the first partition, according to the partition by RANGE year (order_date) partition. So the query where order_date between ' 2012-01-01 ' and ' 2012-12-31 ' will detect 2 partitions, except for 2012, which will also detect the first partition.

To avoid this, you can create a useless first partition, such as PARTITION p_null VALUES less THAN (0), even if the retrieval cost is very small.

2. Partitioning columns and indexed columns do not match, causing partition filtering to be prevented.

If there is an index on a and column B is partitioned because each partition has its own independent index, the index on column B needs to be scanned for the index of each partition.

Especially in an associative query, where the partition table is in the second table of the association order, and the indexed partitioning column does not match, then each table that matches the criteria for the first table needs to be accessed and searched for all the partitions of the second table.

3, select the partition, open and lock the table, maintenance partition can be very expensive.

4. All partitions must have the same storage engine.

5, some engines do not support partitions.

6, the partition function can use the function and the expression also has some restrictions ah.