Considerations for MySQL table partitioning

1. Partition column index constraints
If the table has a primary key or unique key, the partition column of the partition table must be included in the primary key or unique key list to ensure the efficiency of the primary key, otherwise, the same primary key partition is located in partition A and partition B, which is obviously troublesome.

2. Partition type conditions
Range each partition contains rows whose partition expression values are located within a given continuous interval. These intervals must be continuous and cannot overlap with each other.

List only supports integer fields or returns an integer expression. The value list in each partition list must be an integer.

The hash type only supports integer fields or returns an integer expression.

The key type only supports column names (one or more column names), but does not support expressions.

3. Available partition functions
ABS ()

CEILING () (see CEILING () and FLOOR (), immediately following this list)

DAY ()

DAYOFMONTH ()

DAYOFWEEK ()

DAYOFYEAR ()

DATEDIFF ()

EXTRACT ()

FLOOR () (see CEILING () and FLOOR (), immediately following this list)

HOUR ()

MICROSECOND ()

MINUTE ()

MOD ()

MONTH ()

QUARTER ()

SECOND ()

TIME_TO_SEC ()

TO_DAYS ()

WEEKDAY ()

YEAR ()

YEARWEEK ()

Note:

Because the partition function does not include the FROM_UNIXTIME function, you cannot use the timestamp to convert the time to partition. You can only use date or datetime to partition.

For example, we can use:

Partition by range (YEAR (date ))

Monthly:

Partition by range (date div 100)

# Div converts a date to an integer. For example, 2014-12-01-> 20141201 and 100 are two digits removed from the backend. The final result is 201412.

An example of order partitioning:

Create table 'order '(
'Order _ id' bigint (19) not null default '0' comment' order id: year, month, day, hour, minute, second, 12-digit + 7-digit random number ',
'Date' date not null default '2017-00-00 'COMMENT 'Order date ',
'Amount 'int (11) default null comment' payment amount, unit ',
'Status' tinyint (1) DEFAULT '0' COMMENT '0: pending payment 1: payment successful 2: payment failed 3: Verification failed ',
'Addtime' int (10) default null comment' order addition time ',
Primary key ('Order _ id', 'date ')
) ENGINE = MyISAM default charset = utf8;
Because we cannot use timestamps for partitioning by time, we have added a date field. This field and order_id are used as the primary key together. We know that the partition columns must be placed in the primary key. In the following example, we use date to calculate a combination of years and months for partitioning.

Alter table order partition by range (date DIV 100)
(
PARTITION p_2014_06 values less than (201407 ),
PARTITION p_2014_07 values less than (201408 ),
PARTITION p_2014_08 values less than (201409 ),
PARTITION p_2014_09 values less than (201410 ),
PARTITION p_2014_10 values less than (201411 ),
PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);
The preceding less than maxvalue sets the last partition p_catch_all. Therefore, you cannot add partitions using add. The following statement is unavailable:

Alter table order add partition (PARTITION p_2014_11 values less than (201412 ));
You can only split the last p_catch_all partition into two partitions, so that the data in the p_catch_all partition will not be lost. Data is merged and split using reorganize partition.

Alter table order reorganize partition p_catch_all
(
Partition p_2014_11 values less than (201412 ),
Partition p_catch_all values less than maxvalue
);
Merge partitions:

Alter table order reorganize partition p_2014_10, p_2014_11, p_catch_all
(
Partition p_catch_test values less than MAXVALUE
);
Why not go to p_catch_all? Because the partition is reported to exist.

Why should the last partition p_catch_all be included in the merge? Except the last partition, the range of other reorganized partitions cannot change the total range.

Delete partitions but not data:

Alter table name remove partitioning
Note: the preceding statement can be executed at 5.5, and 5.6 seems to be faulty. Test it first.

After partitioning, if the where condition is a range, partitioning does not work, for example, where date> = '2017-01-01 'And date <= '2017-01-31'

Make sure to use the = or in condition where date = '2014-01-01 'or where date in ('2014-01-01', '2014-01-02 ', '2017-01-03 '...)