MySQL table partition create use learning Notes

Table partitioning test usage, mainly from other blog posts and mysql5.1 reference manuals

MySQL beta version: mysql5.5.28

MySQL Physical storage file (with MySQL configuration datadir decision storage path) format Introduction

Database Engine to MyISAM

FRM table structure file, myd table data file, myi table index file.

INNODB engine corresponding table physical storage file

“

The physical file structure of the InnoDB database is:

. frm file

. ibd files and. ibdata files:

Both files are files that hold InnoDB data, and two files are used to store INNODB data because INNODB data storage can be configured to determine whether to use shared table space to store data, or to store data in a single table space.

Exclusive table Space storage using. ibd files, and each table an IBD file

Shared tablespace storage uses. ibdata files, all tables work with a ibdata file

”

Creating partitions
Some of the benefits of partitioning include:

· More data can be stored than a single disk or file system partition.

· For those data that have lost their meaning, it is often possible to delete those data by deleting the partitions that are relevant to those data. Conversely, in some cases, the process of adding new data can be easily implemented by adding a new partition specifically for those new data.

Other benefits typically associated with partitioning include these listed below. These features in the MySQL partition are not yet implemented, but they are high priority in our priority list, and we want to include them in the 5.1 production release.

· Some queries can be greatly optimized by relying on data that satisfies a given where statement to only exist within one or more partitions, so that you do not have to look for other remaining partitions when looking. Because partitions can be modified after the partition table is created, you can rearrange the data to improve the efficiency of those commonly used queries when you are not doing so when you first configure the partition scheme.

· Queries involving aggregate functions such as SUM () and COUNT () can be easily handled in parallel. A simple example of this query is "select salesperson_id, COUNT (orders) as Order_total from sales GROUP by salesperson_id;". By "parallelism," this means that the query can be done at the same time on each partition, and the result only needs to be the result of a total of all partitions.

· To gain greater query throughput by dispersing data queries across multiple disks.

In short, data management optimization, query faster, data query parallel

Detect whether MySQL supports partitioning

The code is as follows	Copy Code
Mysql> Show variables like "%partition%"; +-------------------+-------+ | variable_name&nbsp&nbsp | Value | +-------------------+-------+ | have_partitioning | yes&nbsp | +-------------------+-------+ 1 row in Set

RANGE partition: Assigns multiple rows to a partition based on a column value that belongs to a given contiguous interval.

The code is as follows	Copy Code
DROP TABLE IF EXISTS ' P_range '; CREATE TABLE ' P_range ' ( ' ID ' int (a) not NULL auto_increment, ' Name ' char (' not NULL '), PRIMARY KEY (' id ') ) Engine=myisam auto_increment=9 DEFAULT Charset=utf8 /*!50100 PARTITION by RANGE (ID) (PARTITION p0 VALUES less THAN (8) ENGINE = MyISAM) * * *;

A range partition is a partition by range (ID) that stores data by ID 1-7 in a p0 partition, and if the ID is greater than 7, the data cannot be written because there is no corresponding data partition to store;
So when you create partitions, you need to use the Maxvalues keyword.

The code is as follows	Copy Code
PARTITION by RANGE (ID) ( PARTITION p0 VALUES less THAN (8), PARTITION p1 VALUES less THAN MAXVALUE)

This means that all data records with IDs greater than 7 exist in the P1 partition.

Range partitions are particularly useful in the following situations:

· When you need to delete the "old" data. If you use the partitioning scheme given in the last example above, you simply use "ALTER TABLE employees DROP PARTITION p0" To remove all the lines that correspond to all employees who stopped working 1991 years ago. For a table with a large number of rows, this is much more effective than running a delete query such as "Delete from employees where year (separated) <= 1990."

· You want to use a column that contains a date or time value, or a value that contains values that start with some other series.

· Frequently run queries that depend directly on the columns used to partition the table. For example, when executing a query such as "SELECT COUNT (*) from Employees WHERE Year (separated) = GROUP by store_id", MySQL can quickly determine that only the partition P2 needs to be scanned, This is because the remaining partitions cannot contain any records that conform to the WHERE clause.

List partition: Similar to a range partition, the difference is that the list partition is selected based on a column value matching a value in a discrete-value collection.

The list partition can be understood as storing data by the ID interval of a key, for example, all records of type table 1,2,3,4 are stored in P0, 5,6,7,8 exist in the P1 partition

Here, as with range partitions, if there is a record typeid is 9, then this record is not stored;

Note that the list partition does not have a definition that contains other values, such as "values less THAN MAXVALUE". Any values that will be matched must be found in the list of values.

The code is as follows

Copy Code

DROP TABLE IF EXISTS ' p_list '; CREATE TABLE ' P_list ' ( ' ID ' int (a) not NULL auto_increment, ' typeID ' mediumint not NULL DEFAULT ' 0 ', ' TypeName ' char () DEFAULT NULL, PRIMARY KEY (' id ', ' typeid ') ) Engine=myisam auto_increment=9 DEFAULT Charset=utf8 /*!50100 PARTITION by LIST (typeid) (PARTITION p0 VALUES in (1,2,3,4) ENGINE = MyISAM, PARTITION p1 VALUES in (5,6,7,8) ENGINE = MyISAM) * * *;

Hash partition: A partition that is selected based on the return value of a user-defined expression, calculated using the column values of the rows that will be inserted into the table. This function can contain any expression that is valid in MySQL that produces a non-negative integer value.

A hash partition is primarily used to ensure that data is distributed evenly across predetermined numbers of partitions. In the range and list partitions, you must explicitly specify which partition the given column value or set of column values should be saved in, and in a hash partition, MySQL does this automatically, all you have to do is specify a column value or expression based on the column value that will be hashed. and specifies the number of partitions that the partitioned table will be split into.

To split a table using a HASH partition, add a PARTITION by hash (expr) clause to the CREATE TABLE statement, where "expr" is an expression that returns an integer. It can simply be the name of a column in which the field type is a MySQL integral type. In addition, you will most likely need to add a "partitions num" clause later, where num is a non-negative integer that indicates how much of the table will be divided into partitions. If you do not include a partitions clause, the number of partitions will default to 1.

The code is as follows	Copy Code
DROP TABLE IF EXISTS ' P_hash '; CREATE TABLE ' P_hash ' ( ' ID ' int (a) not NULL auto_increment, ' StoreID ' mediumint not NULL DEFAULT ' 0 ', ' Storename ' char (255) DEFAULT NULL, PRIMARY KEY (' id ', ' storeid ') ) Engine=innodb auto_increment=11 DEFAULT Charset=utf8 /*!50100 PARTITION by HASH (StoreID) Partitions 4 * *;

InnoDB engine

The simple point is that data can be stored in partition by hash (expr); Expr here can be either a key or an expression such as year, or in the case of an expression

"But it should be remembered that every time you insert or update (or possibly delete) a row, the expression is evaluated once; This means that very complex expressions can cause performance problems, especially when performing operations that affect a large number of rows simultaneously, such as bulk inserts. ”

This expression is evaluated once when a delete, write, update is performed.

The distribution of the data uses the modulus based on the result of the user function to determine which numbered partition to use. In other words, for an expression of "expr", the partition number for the record to be saved is N, where "n = MOD (expr, num)".

For example, the above StoreID is 10, then n=mod (10,4); n is equal to 2, then this record is stored in the P2 partition.

If you insert a record with an expression column value of ' 2005-09-15′ to the table, the partition that holds the record is determined as follows: MoD (' 2005-09-01′, 4) = mod (2005,4) = 1; it's stored in the P1 partition.

"MySQL 5.1 also supports a variable called the linear hashing (linear hash function), which uses a more complex algorithm to determine where new rows are inserted into a table that has already been partitioned."

The only difference in syntax between a linear hash partition and a regular hash partition is to add the "LINEAR" keyword in the "PARTITION by" clause; a linear 2 power (POWERS-OF-TWO) algorithm used by the linear hash function

The advantage of linear hash partitioning is that adding, removing, merging, and splitting partitions becomes quicker and facilitates processing of tables that contain extremely large amounts of (1000GB) of data.

The disadvantage is that the distribution of data between partitions is less likely to be balanced than the distribution of data obtained using regular hash partitions. ”

Key partitions: Similar to a hash partition, except that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

The code is as follows	Copy Code
DROP TABLE IF EXISTS ' P_key '; CREATE TABLE ' P_key ' ( ' ID ' int (a) not NULL auto_increment, ' KeyName ' char () DEFAULT NULL, ' Keyval ' varchar (1000) DEFAULT NULL, PRIMARY KEY (' id ') ) Engine=myisam auto_increment=12 DEFAULT Charset=utf8 /*!50100 PARTITION by KEY (ID) Partitions 4 * *;

Partitioning according to key is similar to following a hash partition, except for a user-defined expression used by the hash partition, and the hash function of the key partition is provided by the MySQL server. The MySQL cluster (Cluster) uses function MD5 () to implement the key partition, and for tables using other storage engines, the server uses its own internal hash function, which is based on the same algorithm as password ().

"CREATE TABLE ... The syntax rules for PARTITION by KEY are similar to the rules for creating a table that passes through a hash partition. The only difference is that the keyword used is key instead of hash, and a key partition takes only one list of one or more column names.

The difference from hash is that hash uses user-defined expressions such as year, and key partitions are provided by the MySQL server. The same key can also be used linear linear key, and hash linear is the same algorithm.

Sub-partitions: Is the split again for each partition in the partitioned table.

code is as follows

copy code

DROP TABLE IF EXISTS ' p_subpartition '; CREATE TABLE ' p_subpartition ' ( ' id ' int () DEFAULT null, ' title ' char (255) NOT NULL, ' createtime ' date NO T NULL ) engine=myisam DEFAULT Charset=utf8 /*!50100 PARTITION by RANGE (year (createtime)) Subpartitio N by HASH (MONTH (createtime)) (PARTITION p0 VALUES less THAN () (subpartition s1 ENGINE = MyISAM, Subparti tion s2 ENGINE = MyISAM), PARTITION p1 VALUES less THAN (2013) (subpartition s3 ENGINE = MyISAM, subpartition S4 ENGINE = MyISAM), PARTITION p2 VALUES less THAN MAXVALUE (subpartition s5 ENGINE = MyISAM, Subpartition S6 ENGINE = MyISAM)) */;

You can see that the p_subpartition has three partition p0,p1,p2, and each of these three partitions is further divided into 2 partitions. Then the whole table is divided into 6 small partitions;

You can see that the file on behalf of P_sobpartitionp0.myd is gone and replaced by P_subpartition#p#p0#sp#s1.myd

In MySQL 5.1, it is possible to repartition a table that has been partitioned by range or list.

A child partition is a second division of each partition in a partitioned table, and a child partition can use either a hash or a key partition. This is also known as a composite partition (composite partitioning).

1, if you create a child partition in a partition, the other partitions also have child partitions

2, if you create a partition, the number of sub partitions in each partition must be the same

3, the same partition within the sub-partition, the name is not the same, different partitions within the sub-partition name can be the same (5.1.50 not applicable)

Zoning attention points
1, repartition, if the original partition inside the MaxValue, the new partition must also contain maxvalue otherwise it is wrong.
ALTER TABLE P_RANGE2X
Reorganize partition P1,P2
into (partition P0 values less than (5), partition P1 values less than);

[ERR] 1520–reorganize of range partitions cannot change total ranges except to last partition where it can extend the R Ange

2, the partition is deleted, the data will also be deleted

ALTER TABLE P_range drop partition p0;

3, if there is no maxvalue in the range partition list, if the new data is greater than the current partition range data value then this data cannot be written to the database table.

4, modify the table name does not need to delete the partition after the change, modify the table name after the partition store myd myi corresponding will also automatically change.

If you want to remove all the data from all partitions, but keep the table definition and the table partition mode, use the TRUNCATE TABLE command. (see section 13.2.9, "truncate syntax").

If you want to change the partition of a table without losing data, use ALTER TABLE ... REORGANIZE PARTITION "statement. See the following, or refer to the reorganize partition information in "ALTER table Syntax" in section 13.1.2.

5. When partitioning a table, whichever partitioning method is used if a primary key exists in the table, the primary key must be in the partition column. The limitations of table partitioning.

6, the list mode is not similar to the range that less than maxvalue, which means that all the data in the list partition table must be in the list of values in the partition field.

7. In MySQL version 5.1, all partitions of the same partition table must use the same storage engine, for example, you cannot use MyISAM for one partition, and another uses InnoDB.

8, the name of the partition is case-insensitive, MYP1 and MYP1 are the same.

Management of partitions
The change of range and list partition cannot be applied to the partitioning of hash and key methods. Delete and add actions are all available.

In the following example

code is as follows

copy code

DROP TABLE IF EXISTS ' p_list '; CREATE TABLE ' p_list ' ( ' id ' int ' () not NULL auto_increment, ' typeid ' Mediumint (a) NOT null DEFAULT ' 0 ', ' TypeName ' char (#) Default NULL, PRIMARY KEY (' id ', ' typeid ') ) Engine=myisam auto_increment=9 DEFAULT Charset=ut F8 /*!50100 PARTITION by LIST (typeid) (PARTITION p0 values in (1,2,3,4) ENGINE = MyISAM, PARTITION p1 values In (5,6,7,8) ENGINE = MyISAM) */;

Management of range and list partitions
Delete Partition
ALTER TABLE tr DROP PARTITION p1;
Note that after you delete the partition, all the data for that partition is gone. At the same time, there is a significant impact of the deletion of typeID as 5,6,7,8 records can not be written to the table!

Emptying data

If you want to preserve the table structure and partition structure, you can use TRUNCATE table to empty tables

Change partition retention data

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list into (partition_definitions); If you want to preserve data for partition changes

ALTER TABLE p_list REORGANIZE PARTITION p0 into (
PARTITION S0 VALUES in (1,2),
PARTITION S1 VALUES in (3,4),
So that you can merge the partitions, so how do you split them?

ALTER TABLE p_list REORGANIZE PARTITION s0,s1 into (
PARTITION p0 VALUES in (1,2,3,4),
); With REORGANIZE partition data is merged and split, the data is not lost.
When you use reorganize to repartition, you need to be aware of a few things:
1. The partition clause used to determine the new partition pattern uses the same rules as the partition clause used to determine the partitioning pattern in the CREATE table. (the partition partition clause must be the same as the rule when the original partition was created)
2. The partition_definitions of a partition in a list should have the same interval or set of values as the collection of named partitions in Partition_list. (whether merging or splitting, the range or value of S0,S1 to P0;p0 to S0,s1 must be the same)
3. For a table that is partitioned by range, you can only rearrange adjacent partitions, and you cannot skip the range partition. (for example, by range year P0 1990,P1, p2 20,133 partitions; partition p0,p2 into () at merge
This is not possible because the two partitions are not adjacent partitions;
4. You cannot use reorganize partition to change the partition type of a table, that is, for example, you cannot turn a range partition into a hash partition, or vice versa. You cannot use this command to change a partition expression or column.

adding partitions
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES in (9, 10, 11));
But you can't use
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES in (9, 14));
So MySQL generates an error 1465 (HY000): In the list partition, multiple definitions of the same constant
The management of hash and key partitions are very similar to each other in terms of changing partition settings, but they differ from tables in range or list partitions in many ways.
About adding and removing partitions for a table that is partitioned by range or list
You cannot remove partitions from a table in a hash or key partition in the same way that you delete a partition from a table that is in a range or list partition. However, you can use the ALTER TABLE ... COALESCE PARTITION command to merge the hash or key partition.
View source code printing help 1 DROP TABLE IF EXISTS ' P_hash ';  2 3 CREATE TABLE ' P_hash ' (4 ' id ' int (ten) NOT NULL auto_increment, 5 ' StoreID ' Mediumint (a) NOT null DEFAULT ' 0 ', 6 ' storename ' char (255) Default NULL, 7 PRIMARY KEY (' id ', ' StoreID ') 8) Engine=innodb auto_increment=11 DEFAULT CHARSET =utf8 9/*!50100 PARTITION by HASH (StoreID) Partitions 4 * *;
For example, the number of partitions for P_hash is 4;
To reduce the number of partitions to 2
ALTER TABLE P_hash coalesce PARTITION 2;
Coalesce can play the same role for tables that follow Hash,key,linear HASH, or LINEAR KEY partitions. Coalesce can not be used to increase the number of partitions, if you try to do so, the result will appear similar to the following error:

Mysql> ALTER TABLE clients coalesce PARTITION 18;
Error 1478 (HY000): Cannot move all partitions, use drop table instead of the number of partitions to increase the Customer table from 12 to 18, using ALTER table ... ADD PARTITION ", specific as follows:

ALTER TABLE clients ADD PARTITION partitions 18; NOTE: "ALTER TABLE ... REORGANIZE PARTITION "cannot be used on tables that are partitioned by hash or hash.

Partition Maintenance
Rebuilding partitions

This and delete all the records saved in the partition, and then reinsert them, with the same effect. It can be used to defragment partitions.

ALTER TABLE T1 REBUILD PARTITION (P0, p1);

Optimizing Partitions If you delete a large number of rows from a partition, or if you make many changes to a row with a variable length (that is, a column with Varchar,blob, or text type),

You can use the ALTER TABLE ... OPTIMIZE PARTITION "to reclaim unused space and defragment the partitioned data file.

ALTER TABLE T1 OPTIMIZE PARTITION (P0, p1);

Profiling partitions
Read and save the key distribution of the partition
ALTER TABLE T1 ANALYZE PARTITION (p3);

Patching partitions: Patching broken partitions.
ALTER TABLE T1 REPAIR PARTITION (P0,P1);

Check partitions
You can use almost the same way that you use a check table for a non-partitioned table to check the partitions.
ALTER TABLE trb3 CHECK PARTITION (p1);
This command can tell you whether the data or indexes in the partition P1 of the table T1 have been corrupted. If this happens, use the ALTER TABLE ... REPAIR PARTITION "to patch the partition. Get partition information
The partitions in the MySQL server information database holds the partition information for all tables in the server.

--

The code is as follows

Copy Code

Explain partitions command Explain partitions select * from P_hash +----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+ | ID | Select_type | table  | partitions  | Type | Possible_keys | key  | Key_len | ref  | Rows | Extra | +----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+ |  1 | simple&nbsp&nbsp  | P_hash | P0,P1,P2,P3 | all  | null&nbsp&nbsp&nbsp&nbsp  | NULL | null&nbsp  | NULL |&nbsp |&nbsp&nbsp&nbsp | +----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+

--Gets the partition details for the P_list table.

Select * from INFORMATION_SCHEMA. ' Partitions ' WHERE table_name = ' p_list ';
--Create information for partitions
Show CREATE TABLE P_list