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

MysqlPartition topic study

1. Date Time partition

When partitioning by KEY or LINEAR KEY, youcan use a DATE, TIME, or DATETIME column as the

Partitioning column without performing any modification of the column value. Forexample, this table creation statement is perfectly valid in MySQL:

If you consider not converting column values, you can take key for partitioning.

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 BYKEY (joined)

PARTITIONS 6

In this way, there is no need to convert column values.

In MySQL 5.7, it is also possible to use aDATE or DATETIME column as the partitioning column using RANGE COLUMNS and LISTCOLUMNS partitioning. MySQL's other partitioning types, however, require apartitioning expression that yields an integer value or NULL. If you wish touse date-based partitioning by RANGE, LIST, HASH, or LINEAR HASH, you can simplyemploy a function that operates on a DATE, TIME, or DATETIME column and returnssuch a value, as shown here:

If you want to use a partition based on date RANGE, LIST, HASH, or LINEAR HASH, you can use the time function to partition.

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 VALUESLESS THAN (1960)

PARTITION p1 VALUESLESS THAN (1970)

PARTITION p2 VALUESLESS THAN (1980)

PARTITION p3 VALUESLESS THAN (1990)

PARTITION p4 VALUESLESS THAN MAXVALUE

);

MySQL partitioning is optimized for usewith the TO_DAYS (), YEAR (), and TO_SECONDS ()

Functions. However, you can use other dateand time functions that return an integer or NULL, such

As WEEKDAY (), DAYOFYEAR (), or MONTH ().

You can use time and date functions (which return integers or null) for partition optimization, such as TO_DAYS (), YEAR (), and TO_SECONDS (), WEEKDAY (), DAYOFYEAR (), orMONTH ()

PS: partition table partition name, case-insensitive.

Mysql > CREATE TABLE T2 (val INT)

-> PARTITION BY LIST (val)

-> PARTITION mypart VALUES IN (1pm 3pm 5)

-> PARTITION MyPart VALUES IN (2pm 4pm 6)

->)

ERROR 1517 (HY000): Duplicate partitionname MyPart

2. Range partition

Case 1--maxvalue:

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 NOT NULL

Store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (6)

PARTITION p1 VALUES LESS THAN (11)

PARTITION p2 VALUES LESS THAN (16)

PARTITION p3 VALUES LESS THAN MAXVALUE

);

Case 2--range date:

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 RANGE (YEAR (separated)) (

PARTITION p0 VALUES LESS THAN (1991)

PARTITION p1 VALUES LESS THAN (1996)

PARTITION p2 VALUES LESS THAN (2001)

PARTITION p3 VALUES LESS THAN MAXVALUE

);

Case 3--range timestamp:

CREATE TABLE quarterly_report_status (

Report_id INT NOT NULL

Report_status VARCHAR (20) NOT NULL

Report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPON 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-0100)

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)

);

Case column 4--range columns (date or datetime):

For this part of the case, please refer to the document MySQL Partition-Column Partition.docx

CREATE TABLE members (

Firstname VARCHAR (25) NOT NULL

Lastname VARCHAR (25) NOT NULL

Username VARCHAR (16) NOT NULL

Email VARCHAR (35)

Joined datetime NOTNULL

)

PARTITION BY RANGE COLUMNS (joined) (

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

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

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

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

PARTITION p4 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 NOTNULL

)

PARTITION BY RANGE COLUMNS (joined) (

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

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

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

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

PARTITION p4 VALUES LESS THAN MAXVALUE

);

3. List partition

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)

);

4. Columns Partitioning

The permitted data types are shown in the followinglist: supported data types are as follows

(integer) All integer types: TINYINT, SMALLINT,MEDIUMINT, INT (INTEGER), and BIGINT. (This is the

Same as with partitioning by RANGE andLIST.)

Other numeric data types (such as DECIMALor FLOAT) are not supported as partitioning columns.

(date) DATE and DATETIME.

Columns using other data types relating todates or times are not supported as partitioning columns.

The following string types: CHAR, VARCHAR,BINARY, and VARBINARY.

COLUMNS Partitioning

TEXT and BLOB columns are not supported aspartitioning columns.

1.

two。

3.

4.

1.

two。

3.

4.

1. Range Columns

Experiment

Create table rcx

(aint

Bint

Cchar (10)

Dint)

Partition by range columns (a, b, c)

(partition p0 values less than (5,10, 'aaa')

Partition p1 values less than (10,20, 'nnnn')

Partition pmax values less than (maxvalue, maxvalue, maxvalue))

Insert into rcx values (5,9, 'aaa', 1);-- insert into p0

Insert into rcx values (5,10, 'aaa', 1);-- insert into p1

Insert into rcx values (5,11, 'aaa', 1);-- insert into p1

Insert into rcx values (4,12, 'aaa', 1)-- insert into p0

Select (5,9)

< (5, 10), (5, 10) SELECT (0,25,50) ALTER TABLE e EXCHANGE PARTITIONp0 WITH TABLE e2; ERROR 1707 (HY000): Found row that does notmatch the partition The IGNORE keyword is accepted, but has noeffect when used with EXCHANGE PARTITION, as shown here: mysql>

ALTER IGNORE TABLE e EXCHANGEPARTITION p0 WITH TABLE e2

ERROR 1707 (HY000): Found row that does notmatch the partition

Only the WITHOUT VALIDATION option wouldpermit this operation to succeed:

Mysql > ALTERTABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION

Query OK, 0 rows affected (0.02 sec)

Exchange partition data without confirmation.

With validation, this is not commonly used.

Subpartition data:

ALTER TABLE es EXCHANGEPARTITION p3sp0 WITH TABLE es2

P3sp0 is a subpartition, and tables with subpartitions do not support partition swapping

Mysql > ALTER TABLE es EXCHANGE PARTITIONp3 WITH TABLE es2

ERROR 1704 (HY000): Subpartitioned table,use subpartition instead of partition

You also need to keep the storage engine of the partition to be swapped the same as that of the regular table.

9.4. Maintenance of Partitions

Maintenance of partition tables.

Partition refactoring:

Alter table t rebuildpartition p0, p1

Function partition refactoring, defragment partition, delete partition data, reinsert data.

Optimization of partitions:

ALTER TABLE t1 OPTIMIZEPARTITION p0, p1

When deleting large amounts of data or modifying variable-length fields in the partition, such as the varchar,blob,text field, unused space is reclaimed and defragmented.

Equivalent to check partition, analyze partition, repair patition

Some MySQL storage engines, including InnoDB, do not support per-partition optimization; in

These cases, ALTERTABLE... OPTIMIZE PARTITION analyzes and rebuilds the entire table

And causes an appropriate warning to beissued. (Bug # 11751825, Bug # 42822) Use ALTER

TABLE... REBUILD PARTITION and ALTER TABLE... ANALYZE PARTITION instead, to

Avoid this issue.

Analysis of zones:

ALTER TABLE t1 ANALYZEPARTITION p3

This reads and stores the key distributionsfor partitions.

Read the distribution and storage of partitions.

Repair of partitions:

ALTER TABLE t1 REPAIRPARTITION p0,p1

Repair the crashed partition.

Normally, REPAIRPARTITION fails when the partition contains duplicate key errors. In MySQL

5.7.2 and later, you can use ALTER IGNORETABLE with this option, in which case all rows that

Cannot be moved due to the presence ofduplicate keys are removed from the partition (Bug

# 16900947).

Check for partitions:

ALTER TABLE trb3 CHECKPARTITION p1

It works the same as check TABLE with nonpartitioned tables.

Will check whether the partitioned data and index are normal, using ALTER TABLE. REPAIR PARTITION repair.

Normally, CHECKPARTITION fails when the partition contains duplicate key errors. In MySQL 5.7.2

And later, you can use ALTER IGNORE TABLEwith this option, in which case the statement returns

The contents of each row in the partitionwhere a duplicate key violation is found. Note that only the values for thecolumns in the partitioning expression for the table are reported. (Bug#16900947)

Be careful

The use of mysqlcheckand myisamchk is not supported withpartitioned tables.

Mysqlcheck and myisamchk do not support partitioned tables.

In MySQL 5.7, you can also truncatepartitions using ALTER TABLE... TRUNCATE PARTITION.

This statement can be used to delete allrows from one or more partitions in much the same way that TRUNCATE TABLEdeletes allrows from a table.

You can use ALTER TABLE... TRUNCATE PARTITION.

ALTER TABLE... TRUNCATEPARTITION ALL truncates all partitions in the table.

Prior to MySQL 5.7.2, ANALYZE, CHECK,OPTIMIZE, REBUILD, REPAIR, and TRUNCATE operations

Were not permitted on subpartitions (Bug#14028340, Bug# 65184).

Prior to version 5.7.2, subpartition operations (ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE) are not supported.

9.5. Obtaining Information AboutPartitions

Get partition information.

Using the SHOW CREATE TABLEstatement to view the partitioning clauses used in creating a

Partitioned table.

Using the SHOW TABLE STATUSstatement to determine whether a table is partitioned.

Querying the INFORMATION_SCHEMA.PARTITIONStable.

Using the statement EXPLAIN SELECTto see which partitions are used by a given SELECT.

Mysql > show create table t\ G

Mysql > show table status like 't'\ G

EXPLAIN SELECT * FROM t WHERE id

< 5 \G 10.Partitionspruning 意思，就是mysql optimizer根据条件，能准确定位数据在哪个（或哪些）分区，达到性能提升的目的。亲，可以理解为mysql服务器自己的查询优化。 注意： When pruning is performed on a partitionedMyISAM table, all partitions are opened, whether or not they are examined, dueto the design of the MyISAM storage engine. 当使用MyISAM分区表时，所有分区都将被打开，无论分区是否被检查。 MySQL can apply partition pruning toSELECT, DELETE, and UPDATE statements. INSERT statements currently cannot be pruned. Mysql优化器能将分区裁剪应用于SELECT, DELETE, and UPDATE语句中，但是insert语句不支持（version5.7.17） 补充： Pruning can also be applied for tablespartitioned on a DATE or DATETIME column whenthe partitioning expression uses the YEAR() or TO_DAYS()function.In addition, in MySQL 5.7, pruning can beapplied for such tables when the partitioning expression uses the TO_SECONDS()function. 案例1--range： CREATE TABLE t2 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) PARTITION BY RANGE( YEAR(dob) ) ( PARTITION d0 VALUES LESS THAN (1970), PARTITION d1 VALUES LESS THAN (1975), PARTITION d2 VALUES LESS THAN (1980), PARTITION d3 VALUES LESS THAN (1985), PARTITION d4 VALUES LESS THAN (1990), PARTITION d5 VALUES LESS THAN (2000), PARTITION d6 VALUES LESS THAN (2005), PARTITION d7 VALUES LESS THAN MAXVALUE ); SELECT * FROM t2 WHERE dob = '1982-06-23'; UPDATE t2 SET region_code = 8 WHERE dobBETWEEN '1991-02-15' AND '1997-04-25'; DELETE FROM t2 WHERE dob >

= '1984-06-21'AND dob 2AND region_code

< 6; SELECT * FROM t4 WHERE region_code BETWEEN3 AND 5; 11.PartitionsSelection 显式分区选择： SQL statements supporting explicit partition selection are listed here: SELECT DELETE INSERT REPLACE UPDATE LOAD DATA. LOAD XML. SELECT * FROM employeesPARTITION (p1, p2)，中间以逗号分隔，这种写法在oracle里不支持的。 When a table is created using [LINEAR] HASHor [LINEAR] KEY partitioning and the names of the partitions are not specified,MySQL automatically names the partitions p0, p1, p2, ..., pN-1, where N is thenumber of partitions. For subpartitions not explicitly named, MySQL assignsautomatically to the subpartitions in each partition pX the names pXsp0, pXsp1,pXsp2, ..., pXspM-1, where M is the number of subpartitions. 当表以[LINEAR] HASH or [LINEAR] KEY分区时并且分区名字未被指定，MySQL自动命名分区p0, p1,p2, ..., pN-1。对于没有显式定义的子分区，MySQL自动命名子分区pXsp0, pXsp1, pXsp2, ..., pXspM-1。 案例1： mysql>

CREATE TABLE employees_sub (

-> id INT NOT NULL AUTO_INCREMENT

-> fname VARCHAR (25) NOT NULL

-> lname VARCHAR (25) NOT NULL

-> store_id INT NOT NULL

-> department_id INT NOT NULL

-> PRIMARY KEY Competition (id, lname)

->)

-> PARTITION BY RANGE (id)

-> SUBPARTITION BY KEY (lname)

-> SUBPARTITIONS 2 (

-> PARTITION p0 VALUES LESS THAN (5)

-> PARTITION p1 VALUES LESS THAN (10)

PARTITION p2 VALUES LESS THAN (15)

-> PARTITION p3 VALUES LESS THANMAXVALUE

->)

Query OK, 0 rows affected (1.14 sec)

Mysql > SELECT id, CONCAT (fname,', lname) AS name

-> FROM employees_sub PARTITION (p2sp1);-- it's just changed to the subpartition name here.

Case 2 Mel-using explicit partitioning in join

Mysql > SELECT

-> e.id AS 'Employee ID',CONCAT (e.fname,', e.lname) AS Name

-> s.city AS City, d.name AS department

Partition Selection

3436

-> FROM employees AS e

-> JOIN storesPARTITION (p1) AS s ON e.store_id=s.id

-> JOIN departmentsPARTITION (p0) AS d ON e.department_id=d.id

-> ORDER BY e.lname

Case 3 Murray-multiple partition inserts

For statements that write multiple rows toa partitioned table that uses the InnoDB storage engine:

If any row in the list following VALUEScannot be written to one of the partitions specified in the

Partition_names list, the entire statementfails and no rows are written.

If multiple rows of records are inserted into the partition table with InnoDB engine at the same time, if a record does not match the partition name explicitly specified, the whole statement will fail and no rows will be written.

Mysql > SHOW CREATE TABLE employees\ G

* 1. Row**

Table: employees

Create Table: CREATE TABLE `employees` (

`id`int (11) NOT NULL AUTO_INCREMENT

`fname` varchar (25) NOT NULL

`lname` varchar (25) NOT NULL

`store_ id` int (11) NOT NULL

`department_ id` int (11) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULTCHARSET=latin1

/ * 50100 PARTITION BY RANGE (id)

(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB

PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB

PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB

PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB

PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB

PARTITION p5 VALUES LESS THAN MAXVALUEENGINE = InnoDB) * /

1 row in set (0.00 sec)

Mysql > INSERT INTO employees PARTITION (p3, p4) VALUES

-> (24, 'Tim',' Greene', 3, 1), (26 Mills', 2, 1)

ERROR 1729 (HY000): Found a row not matching the given partition set

Mysql > INSERT INTO employees PARTITION (p3, p4. P5) VALUES

-> (24, 'Tim',' Greene', 3, 1), (26 Mills', 2, 1)

Query OK, 2 rows affected (0.06 sec)

Records: 2 Duplicates: 0 Warnings: 0

twelve。 Restrictions on partitioned tables

Disabled structures in partition expressions:

Stored procedures,stored functions, UDFs, or plugins.

Declared variables or user variables.

Arithmetic and logical operators:

Use of thearithmetic operators +, -, and * is permitted in

Partitioning expressions. However, the result must be aninteger value or NULL

Handler statements:

Previously, theHANDLER statement was not supported with partitioned

Tables. This limitation is removed beginning with MySQL5.7.1.

Server SQL mode:

Just to say the most important point: SQL_MODE please specify and do not change at the beginning of the database establishment.

Performance considerations:

File system operations.

Partitioning and repartitioning operations (such as ALTER TABLE with PARTITION BY..., REORGANIZE PARTITION, or REMOVEPARTITIONING) depend on

File systemoperations for their implementation. This means thatthe speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space,file handling efficiency of the operating system, and MySQL serveroptions and variables that relate to file handling. In particular, you shouldmake sure that large_files_supportis enabled and that open_files_limit is setproperly. For partitioned tables using the MyISAM storage engine, increasing myisam_max_sort_file_sizemay improve performance; partitioning and repartitioning operations involvingInnoDB tables may be made more efficient by enablinginnodb_file_per_table.

Tablelocks. Theprocess executing a partitioning operation on a table takesa write lock on the table. Reads from such tables are relativelyunaffected; pending INSERT and UPDATE operations are performed as soon as thepartitioning operation has completed.

Storage engine. Partitioning operations,queries, and update operations generally tend to be fasterwith MyISAM tables than with InnoDB or NDB tables.

Indexes; partition pruning. As with nonpartitionedtables, proper use of indexes can speed up queries onpartitionedtables significantly. In addition, designing partitionedtables and queries on these tables to take advantage of partition pruning canimprove performance dramatically.

Performancewith LOAD DATA. In MySQL 5.7, LOAD DATA uses buffering to improve

Performance. You should be aware that the buffer uses 130 KB memory per partition to achievethis.

Maximum number of partitions.

The maximum possible number of partitionsfor a given table not using the NDB storage engine is 8192. This numberincludes subpartitions.

When creating tables with a largenumber of partitions (but less than the maximum), you encounter an errormessage such as Got error... From storage engine: Out of resources

When opening file, you may be able toaddress the issue by increasing the value of the

Open_files_limit system variable.However, this is dependent on the operating system, and may not be possible oradvisable on all platforms; see Section B.5.2.18, "File Not Found and SimilarErrors", for more information. In some cases, using large numbers (hundreds) ofpartitions may also not be advisable due to other concerns, so using morepartitions does not automatically lead to better results.

When creating a partitioned table with a large number of partitions, you may encounter Got error... From storage engine: if you report an error like Out of resources when openingfile, you can increase the open_files_limit system parameter to solve this problem. However, this parameter depends on the operating system and is not feasible and recommended on all operating systems. In some cases, creating a partitioned table with a large number of partitions is not recommended because other considerations are not recommended, so using more partitions does not lead to better results.

Query cache not supported.

The query cache isnot supported for partitioned tables, and isautomatically disabled for queries involving partitioned tables. The querycache cannot be enabled for such queries.

Query caching is not supported for partitioned tables.

Per-partition key caches.

In MySQL 5.7, key caches are supported for partitionedMyISAM tables, using the CACHE INDEX and LOAD INDEX INTO CACHE statements.Key caches may be defined forone, several, or all partitions, and indexes forone, several, or all partitions may be preloaded into key caches.

Foreign keysnot supported for partitioned InnoDB tables.

Partitioned tables using the InnoDB storage engine do not supportforeign keys. More specifically, this

Means that the following two statements are true:

1. No definition of an InnoDB table employing user-defined partitioningmay contain foreign key references; no InnoDB table whose definition containsforeign key references may be partitioned.

User-defined InnoDB partitioned tables cannot contain foreign key associations; no InnoDB tables with foreign key associations can be partitioned.

2. No InnoDB table definition maycontain a foreign key reference toa user-partitioned table; no InnoDB table with user-defined partitioning maycontain columns referenced by foreign keys.

There are no InnoDB tables with foreign key associated partition tables; no InnoDB partition tables contain foreign key associated columns.

ALTER TABLE... ORDER BY.

An ALTER TABLE... ORDER BY column statement run against a partitioned table causes ordering ofrows only within each partition.

FULLTEXT indexes.

Partitioned tables do not support FULLTEXT indexesor searches, even for partitioned tables employing theInnoDB or MyISAM storage engine.

Spatial columns.

Columns with spatialdata types such as POINT or GEOMETRY cannot be used inpartitioned tables.

Temporary tables.

Temporary tables cannot be partitioned. (Bug # 17497)

Log tables.

It is not possible topartition the log tables; an ALTER TABLE... PARTITION BY... Statement on sucha table fails with an error.

Issues with subpartitions.

Subpartitions must use HASHor KEY partitioning. Only RANGE andLIST partitions may be

Subpartitioned; HASH and KEYpartitions cannot be subpartitioned.

SUBPARTITIONBY KEY requires that the subpartitioning column or columns be specifiedexplicitly

Unlikethe case with PARTITION BY KEY, where it can be omitted (in which case thetable's primary

Keycolumn is used by default). Consider the table created by this statement:

SUBPARTITIONBY KEY requires that child partition columns be explicitly specified, unlike PARTITIONBY KEY partitions, which can be omitted using the default primary key.

CREATETABLE ts (

Id INTNOT NULL AUTO_INCREMENT PRIMARY KEY

NameVARCHAR (30)

)

PARTITIONBY KEY ()

PARTITIONS4

CREATETABLE ts (

Id INTNOT NULL AUTO_INCREMENT PRIMARY KEY

NameVARCHAR (30)

)

PARTITIONBY KEY (id)

PARTITIONS4

Both of the above statements can be created successfully.

Mysql > CREATE TABLE ts (

-> idINT NOT NULL AUTO_INCREMENT PRIMARY KEY

> name VARCHAR (30)

->)

-> PARTITION BY RANGE (id)

-> SUBPARTITION BY KEY ()

-> SUBPARTITIONS 4

-> (

-> PARTITION p0 VALUES LESS THAN

-> PARTITION p1 VALUES LESS THAN (MAXVALUE)

->)

ERROR1064 (42000): You have an error in your SQL syntax; check the manual that

Correspondsto your MySQL server version for the right syntax to use near')

Mysql > CREATE TABLE ts (

-> idINT NOT NULL AUTO_INCREMENT PRIMARY KEY

> name VARCHAR (30)

->)

-> PARTITION BY RANGE (id)

-> SUBPARTITION BY KEY (id)

-> SUBPARTITIONS 4

-> (

-> PARTITION p0 VALUES LESS THAN

-> PARTITION p1 VALUES LESS THAN (MAXVALUE)

->)

QueryOK, 0 rows affected (0.07 sec)

Inaddition, you can use ALTER TABLE... REBUILD PARTITION to rebuild one or morepartitions

Of apartitioned table; ALTER TABLE... REORGANIZE PARTITION also causes partitionsto be

Rebuilt.

Startingin MySQL 5.7.2, ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE operations are

Supportedwith subpartitions.

Starting from 5.7.2, ANALYZE,CHECK, OPTIMIZE, REPAIR, and and TRUNCATE can be used on subpartitions.

Mysqlcheck,myisamchk, and myisampack are not supported with partitioned tables.

Mysqlcheck,myisamchk and and myisampack do not support partition tables.

10.

11.

twelve。

1. Partition key, primary key, unique key

In this section, please refer to the Chinese PDF file and search for key "MySQL* Partition key"

12.2. Partition restrictions related to the storage engine

InnoDB storage engine. InnoDB foreign keysand MySQL partitioning are not compatible.

Partitioned InnoDB tables cannot haveforeign key references, nor can they have columns referenced by foreign keys.InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.

InnoDB partitioned tables cannot have foreign key associations, nor can columns be associated with foreign keys. InnoDB tables with foreign key associations and associated by foreign keys cannot be partitioned.

InnoDB does not supportthe use of multiple disks for subpartitions. (This iscurrently supported only by MyISAM.)

In addition, ALTER TABLE... OPTIMIZE PARTITION does not work correctly with partitioned

Tables that use theInnoDB storage engine. Use ALTER TABLE... REBUILD PARTITION and

ALTER TABLE... ANALYZEPARTITION, instead, for such tables.

In the InnoDB partition table, you do not need to use ALTER TABLE. OPTIMIZE PARTITION, using ALTER TABLE... REBUILD PARTITION and ALTER TABLE... ANALYZE PARTITION .

12.3. Partition restrictions related to functions

Only the MySQL functions shown in thefollowing table are allowed in partitioning expressions.

In MySQL 5.7, partition pruning issupported for the TO_DAYS (), TO_SECONDS (), YEAR (), and

UNIX_TIMESTAMP () functions. See Section21.4, "Partition Pruning", for more information.

CEILING () and FLOOR (). Each of these functions returns an integeronly if it is passed an argument

Of an exact numeric type, such as one ofthe INT types or DECIMAL. This means, for example, that the

Following CREATE TABLE statement fails withan error, as shown here:

Mysql > CREATE TABLE t (c FLOAT) PARTITION BY LIST (FLOOR (c)) (

-> PARTITION p0 VALUES IN (1pm 3pm 5)

-> PARTITION p1 VALUES IN (2pm 4pm 6)

->)

ERROR 1490 (HY000): The PARTITION functionreturns the wrong type

EXTRACT () function with WEEKspecifier. The value returned by theEXTRACT () function, when

Used as EXTRACT (WEEK FROM col), depends onthe value of the default_week_format system

Variable. For this reason, EXTRACT () is notpermitted as a partitioning function when it specifies the

Unit as WEEK. (Bug # 54483)

12.4. Partitions and locks

In MySQL 5.7, partition lock pruningeliminates unneeded locks in many cases

And most statements reading from orupdating a partitioned MyISAM table cause only the effected

Partitions to be locked.

Effectson DML statements

SELECT statements (including thosecontaining unions or joins) lock only those partitions that actually need to beread. This also applies to SELECT... PARTITION.

The Select statement locks only the partitions that need to be read, while using SELECT. PARTITION.

An UPDATE prunes locks only for tables onwhich no partitioning columns are updated.

The UPDATE statement does not lock as long as the partition column is not being updated.

REPLACE and INSERT lock only thosepartitions having rows to be inserted or replaced. However, if an AUTO_INCREMENTvalue is generated for any partitioning column then all partitions are locked.

REPLACE and INSERT locks only the partitions involved, and if it contains an AUTO_INCREMENT column, all partitions of the entire table are locked.

INSERT... ON DUPLICATE KEY UPDATE ispruned as long as no partitioning column is updated.

This statement is not locked as long as the partition column is not updated.

INSERT... SELECT locks only thosepartitions in the source table that need to be read, although all

Partitions in the target table are locked.

INSERT... SELECT locks only the partitions of the source table that need to be read and locks the entire target table.

Locks imposed by LOAD DATA statements onpartitioned tables cannot be pruned.

Triggers and partitions

The presence of BEFORE INSERT or BEFOREUPDATE triggers using any partitioning column of a

Partitioned table means that locks onINSERT and UPDATE statements updating this table cannot

Be pruned, since the trigger can alter itsvalues: A BEFORE INSERT trigger on any of the table's

Partitioning columns means that locks setby INSERT or REPLACE cannot be pruned, since the BEFORE INSERT trigger maychange a row's partitioning columns before the row is inserted, forcing the rowinto a different partition than it would be otherwise. A BEFORE UPDATE triggeron a partitioning column means that locks imposed by UPDATE or INSERT... ONDUPLICATE KEY UPDATE cannot be pruned.

AffectedDDL statements

CREATE VIEW does not cause any locks.

ALTER TABLE... EXCHANGE PARTITION pruneslocks; only the exchanged table and the exchanged partition are locked. Only lock swap tables and swap partitions.

ALTER TABLE... TRUNCATE PARTITION pruneslocks; only the partitions to be emptied are locked.

Lock only part of the truncate partition.

In addition, ALTERTABLE statements take metadata locks on the table level.

Otherstatements

LOCK TABLES cannot prune partition locks.

CALLstored_procedure (expr) supports lock pruning, butevaluating expr does not.

DO and SET statements do not supportpartitioning lock pruning.

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.