Shulou(Shulou.com)06/01 Report--

Mysql supports range partitions (range), list partitions (list), column partitions, hash partitions (hash), key partitions, field list partitions, etc.

There are two ways to partition a range by using the timestamp type field as the partition key:

CREATE TABLE quarterly_report_status (

Report_id INT NOT NULL

Report_status VARCHAR (20) NOT NULL

Report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)

PARTITION BY RANGE (UNIX_TIMESTAMP (report_updated)) (

PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-01-01 00-01 00 UNIX_TIMESTAMP))

PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-04-01 00UNIX_TIMESTAMP))

PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-07-01 00 UNIX_TIMESTAMP))

PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP ('2008-10-01 00 UNIX_TIMESTAMP))

PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-01-01 00))

PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-04-01 00UNIX_TIMESTAMP))

PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-07-01 00 UNIX_TIMESTAMP))

PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP ('2009-10-01 00 UNIX_TIMESTAMP))

PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP ('2010-01-01-01 00))

PARTITION p9 VALUES LESS THAN (MAXVALUE)

);

CREATE TABLE members (

Firstname VARCHAR (25) NOT NULL

Lastname VARCHAR (25) NOT NULL

Username VARCHAR (16) NOT NULL

Email VARCHAR (35)

Joined DATE NOT NULL

)

PARTITION BY RANGE (YEAR (joined)) (

PARTITION p0 VALUES LESS THAN (1960)

PARTITION p1 VALUES LESS THAN (1970)

PARTITION p2 VALUES LESS THAN (1980)

PARTITION p3 VALUES LESS THAN (1990)

PARTITION p4 VALUES LESS THAN MAXVALUE

);

Only the above two formats can be used for timestamp range partitioned tables in mysql5.7, and using to_days may trigger bug.

Partition of type date:

CREATE TABLE members (

Firstname VARCHAR (25) NOT NULL

Lastname VARCHAR (25) NOT NULL

Username VARCHAR (16) NOT NULL

Email VARCHAR (35)

Joined DATE NOT NULL

)

PARTITION BY RANGE COLUMNS (joined) (

PARTITION p0 VALUES LESS THAN ('1960-01-01')

PARTITION p1 VALUES LESS THAN ('1970-01-01')

PARTITION p2 VALUES LESS THAN ('1980-01-01')

PARTITION p3 VALUES LESS THAN ('1990-01-01')

PARTITION p4 VALUES LESS THAN MAXVALUE

);

Use an instance of list partitioning:

CREATE TABLE employees (

Id INT NOT NULL

Fname VARCHAR (30)

Lname VARCHAR (30)

Hired DATE NOT NULL DEFAULT '1970-01-01'

Separated DATE NOT NULL DEFAULT '9999-12-31'

Job_code INT

Store_id INT

)

PARTITION BY LIST (store_id) (

PARTITION pNorth VALUES IN (3, 5, 6, 9, 17)

PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20)

PARTITION pWest VALUES IN (4, 12, 13, 14, 18)

PARTITION pCentral VALUES IN (7, 8, 15, 16)

);

Using the ignore keyword, you can ignore data without matching partitions when inserting multiple pieces of data without reporting errors:

Mysql > CREATE TABLE h3 (

-> C1 INT

-> c2 INT

->)

-> PARTITION BY LIST (C1) (

-> PARTITION p0 VALUES IN (1,4,7)

-> PARTITION p1 VALUES IN (2,5,8)

->)

Query OK, 0 rows affected (0.11 sec)

Mysql > INSERT INTO h3 VALUES (3,5)

ERROR 1525 (HY000): Table has no partition for value 3

Mysql > INSERT IGNORE INTO h3 VALUES (2,5), (6,10), (7,5), (3,1), (1,9)

Query OK, 3 rows affected (0.00 sec)

Records: 5 Duplicates: 2 Warnings: 0

Mysql > SELECT * FROM h3

+-+ +

| | C1 | c2 |

+-+ +

| | 7 | 5 |

| | 1 | 9 |

| | 2 | 5 |

+-+ +

3 rows in set (0.00 sec)

You can specify partition properties when defining the table, or you can modify them using alter table:

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) (

PARTITION p0 VALUES LESS THAN ('g')

PARTITION p1 VALUES LESS THAN ('m')

PARTITION p2 VALUES LESS THAN ('t')

PARTITION p3 VALUES LESS THAN (MAXVALUE)

);

Range columns does not support expressions, only one or more column names.

Because the character sets character sets and collations are arranged in different order, when data migration or modifying the character sets of libraries, tables, and columns

It is possible to report an error as a result. For example, collation,and, which is case-insensitive, ranks before Andersen.

But not for case-sensitive collation.

When using multiple field partitions, the comparison is made in the order of the fields, and the following statement is correct:

CREATE TABLE rc4 (

An INT

B INT

C INT

)

PARTITION BY RANGE COLUMNS (a _

PARTITION p0 VALUES LESS THAN (0p25p50)

PARTITION p1 VALUES LESS THAN (10pm 20100)

PARTITION p2 VALUES LESS THAN (100.30j.50)

PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

);

However, this partitioning method is not recommended.

Hash partition instance:

CREATE TABLE employees (

Id INT NOT NULL

Fname VARCHAR (30)

Lname VARCHAR (30)

Hired DATE NOT NULL DEFAULT '1970-01-01'

Separated DATE NOT NULL DEFAULT '9999-12-31'

Job_code INT

Store_id INT

)

PARTITION BY HASH (store_id)

PARTITIONS 4

Restrictions:

The partition key must be of numeric type (integer)

The partition key must be part of the primary key and all unique keys

The number of partitions needs to be specified, otherwise the default is 1

The partition into which the data is put is fixed and can be calculated in advance. For example:

CREATE TABLE T1 (col1 INT, col2 CHAR (5), col3 DATE)

PARTITION BY HASH (YEAR (col3))

PARTITIONS 4

If the value of col3 is' 2005-09-15, the formula for which partition the data is put into is:

MOD (YEAR ('2005-09-01), 4)

= MOD (2005 and 4)

= 1

That is, put into the first partition

Linear partition (LINER HASH PARTITION) is different from ordinary hash partition in that it uses linear quadratic power algorithm with the following formula:

V = POWER (2, CEILING (LOG (2, num)

Sentence example:

CREATE TABLE employees (

Id INT NOT NULL

Fname VARCHAR (30)

Lname VARCHAR (30)

Hired DATE NOT NULL DEFAULT '1970-01-01'

Separated DATE NOT NULL DEFAULT '9999-12-31'

Job_code INT

Store_id INT

)

PARTITION BY LINEAR HASH (YEAR (hired))

PARTITIONS 4

Key partition

Similar to hash partitioning, except that the algorithm is different. For NDB cluster, use the md5 () function, and other engines use a similar password () function for partitioning.

For example:

CREATE TABLE tm1 (

S1 CHAR (32) PRIMARY KEY

)

PARTITION BY KEY (S1)

PARTITIONS 10

The key key must be part of the primary key. It can also be empty when there is a primary key or a non-empty unique key.

Compound partition / subpartition

The partition type in mysql5.7 is range or list, and sub-partitions can use hash or key partitions.

There are the following restrictions:

The number of subpartitions in each partition must be the same

The subpartition name cannot be duplicated.

# #

Processing of NULL in Partition Table

In the range partition table, NULL is considered to be less than all values and is stored in the first partition

In the LIST partition table, you must specify that a partition contains a null value

In hash or key partitioned tables, NULL is treated as zero.

# #

Zoning management

Range and list partition tables can add, delete, merge and split partitions.

The logic and writing of adding and deleting partitions is basically the same as that of oracle. Syntax for split / split partitions:

ALTER TABLE members

REORGANIZE PARTITION p0 INTO (

PARTITION n0 VALUES LESS THAN (1970)

PARTITION N1 VALUES LESS THAN (1980)

);

Hash and key partition tables cannot be deleted, but can be merged, such as:

ALTER TABLE clients COALESCE PARTITION 4

Where 4 is the number of partitions to be deleted.

Add 6 partitions, such as:

ALTER TABLE clients ADD PARTITION PARTITIONS 6

Partition swapping (for range partitions or subpartitions)

Similar to oracle, a partition of a partition table is swapped with a regular table. For example:

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

Restrictions:

Consistent table structure, including consistent index

Ordinary tables do not contain foreign keys and are not referenced by other tables as foreign keys

The range of regular table data does not exceed the partition table definition

In the case of innodb engine, consistent row format is required.

The data directory option is not used

The permission to add, delete, modify and query the table is required.

This process does not trigger the trigger

Self-increment will be reset when switching

Invalid ignore option

When using the without validation option, the data is no longer checked one by one

Partition reconstruction, which is equivalent to deleting all data and then reinserting:

ALTER TABLE t1 REBUILD PARTITION p0, p1

#

Maintenance of zoning

Optimized partitioning, which is used to reclaim space and defragment after modifying or deleting a large amount of data

It is equivalent to running check partition, analyze partition, repair partition.

It can be executed on multiple partitions at once:

Alter table t1 optimize partition p0,p1

Note: innodb does not support the optimize operation of a single partition and will be upgraded to rebuild the whole table, such as:

Mysql > alter table T4 optimize partition p1

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| tl.t4 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | |

| | tl.t4 | optimize | status | OK | |

+-+

2 rows in set (4.00 sec)

You can use ALTER TABLE... REBUILD PARTITION and ALTER TABLE... ANALYZE PARTITION substitutes to avoid this problem.

Analyze, read, and store key attribute information for partitions:

Alter table t1 analyze partition p3

Repair partition

Alter table t1 repair partition p0,p1

If there is a duplicate key value during normal execution, an error will be reported.

Starting from 5.7.2, you can use the alter ignore table option to delete values automatically when duplicates occur

Check the partition

Alter table trb3 check partition p1

Check whether the data and index of the p1 partition are interrupted. If there is a duplicate value, the check operation reports an error.

Starting with 5.7.2, you can use the alter ignore table option to report when duplicate values occur.

# # #

Get partition information

Show create table

Show table status = > whether to partition or not

Information_schema.partitions

Explain select

For example:

Mysql > show table status from tl like't%'

->

+- -+- -- +

| | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | |

+- -+- -- +

| | T1 | InnoDB | 10 | Dynamic | 0 | 0 | 65536 | 0 | 65536 | 0 | 1 | 2020-06-18 15:35:43 | NULL | NULL | utf8_general_ci | NULL | partitioned |

| | T2 | InnoDB | 10 | Dynamic | 12 | 6826 | 81920 | 0 | 81920 | 0 | 30 | 2020-06-18 15:57:08 | 2020-06-18 16:01:59 | NULL | utf8_general_ci | NULL | partitioned |

| | T3 | InnoDB | 10 | Dynamic | 9 | 1820 | 16384 | 0 | 16384 | 0 | 1 | 2020-06-18 15:55:24 | 2020-06-18 16:01:59 | NULL | utf8_general_ci | NULL |

| | T4 | InnoDB | 10 | Dynamic | 21 | 3900 | 81920 | 0 | 81920 | 0 | 30 | 2020-06-19 18:23:20 | NULL | NULL | utf8_general_ci | NULL | partitioned |

| | T5 | InnoDB | 10 | Dynamic | 0 | 0 | 49152 | 0 | 49152 | 0 | 30 | 2020-06-18 16:20:24 | 2020-06-18 16:27:30 | NULL | utf8_general_ci | NULL | partitioned |

| | tt | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | NULL | 2020-06-17 23:23:00 | 2020-06-17 23:28:35 | NULL | utf8_general_ci | NULL |

+- -+- -- +

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.