MySQL Partition Table practice

MySQL partition Overview:

Allows you to allocate multiple parts of a single table across file systems based on rules that can be set to any size. In fact, different parts of a table are stored as separate tables in different locations. The user-selected rule for data segmentation is calledPartition functionsIn MySQL, it can be a modulus, or simply matches a continuous value range or value list, an internal HASH function, or a linear HASH function. The function selects the value of the expression provided by the user based on the partition type specified by the user. This expression can be an integer column value, or one that acts on one or more column values andReturns an integer.. [Z1]. The value of this expression is passed to the partition function. The partition function returns a sequence number indicating the partition in which the specific record should be stored. This function cannot be a constant or any number. It cannot contain any queries, but can actually use any available SQL expressions in MySQL, as long as the expression returns a positive value smaller than MAXVALUE (the largest possible positive integer.

Because MySQL does not have the global index concept and only has a local partition index, for this reason, if a table has two or more unique indexes, this table cannot be partitioned. The columns in the database of the Partition Function Application must be the primary key of MySQL; otherwise, the database cannot be partitioned. [Z2]

For tables that have created partitions, you can use any storage engine supported by your MySQL server. In MySQL 5.1, all partitions in the same partition table must use the same storage engine. For example, you cannot use MyISAM for one partition, but InnoDB for the other. However, this does not prevent using different storage engines for different partition tables on the same MySQL server or even in the same database.

Create a MySQL Partition

MySQL can create four partition types:

RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval. For more information, see section 18.2.1 "RANGE partition ".

· LIST partition: similar to partitioning by RANGE, the difference is that LIST partitions are selected based on column values matching a value in a discrete value set. For more information, see section 18.2.2 "LIST partitions ".

· HASH partition: select a partition based on the return value of a user-defined expression. This expression uses the column values of the rows to be inserted into the table for calculation. This function can contain any expressions that are valid in MySQL and generate non-negative integer values. For more information, see section 18.2.3 "HASH partition ".

· KEY partitioning: similar to HASH partitioning, the difference is that KEY partitioning only supports the calculation of one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain integer values. For details, see 18.2.4. KEY partition.

Subpartition: subpartition refers to the re-division of each partition in the partition table. For the writing format, see 18.2.5. subpartition.

(1) Note: Each partition must have the same number of subpartitions.

· (2) if SUBPARTITION is used to define any sub-partition on any partition in a partition table, all sub-partitions must be defined.

When creating a partition, you can specify the data storage location and index location of the partition, so that different data can be stored across disks or file systems. Data can be stored on disks to increase the Data Reading speed to a certain extent, because I/O operations on each disk are reduced when multiple disks are used. In addition, the storage space can be increased by using the specified partition storage location.

No matter what type of partition is used, the partition is always automatically numbered at the time of creation, and records are recorded from 0. This is very important to remember. When a new row is inserted into a partition table, these partition numbers are used to identify the correct partitions. For example, if your table uses four partitions, these partitions are numbered 0, 1, 2, and 3. For the RANGE and LIST partition types, it is necessary to confirm that each partition number defines a partition. For HASH partitions, the user function must return an integer greater than 0. For KEY partitions, this problem is automatically handled through the hash function used inside the MySQL server. Note: partition names are case-insensitive. For RANGE partitions and LIST partitions, the partition names cannot be repeated. These types can be selected based on different requirements. The commonly used RANGE partition is used.

Common MySQL partition management:

RANGE and LIST partition management

Partitions are transparent to programs, and only deletion can be performed at the partition level. partitions cannot be specified for other operations, such as query, modification, or addition.

Alter table... DROPPARTITION .... (Delete partition)

Alter table... Add partition (PARTITION p3 valuesless (...)); [Z3] add partitions

Alter table... reorganize partition ...,... INTO (

PARTITION p0 values less (...)

); [Z4] merge and split partitions.

HASH and KEY partition management

You can add partitions in the same way as RANGE and LIST partitions. You cannot delete partitions in the same way as you delete partitions from tables partitioned by RANGE or LIST, to delete partitions from a HASH or KEY partition table. However, you can use the "ALTERTABLE... coalesce partition" command to merge HASH or KEY partitions.

To view partition information, you can use SQL statements to query

SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema () AND TABLE_NAME = 'xxx'

1 2 3 4 Next Page