How to Create a mysql table Partition

Table partitions are only recently known. I used to split tables to achieve hundreds of millions of data records. Let's introduce how to create and use mysql table partitions, I hope this will help you.
Test and use of table partitions. The main content is from other blog articles and the Reference Manual of mysql5.1.
Mysql test version: mysql5.5.28
Mysql physical storage file (datadir configured in mysql determines the storage path) format Overview
The database engine is MYISAM.

Frm table structure file, myd table data file, and myi table index file.
Physical storage files of tables corresponding to INNODB engine
The physical file structure of the innodb database is as follows:
. Frm File
. Ibd file and. ibdata file:
Both types of files are files that store innodb data. They are used to store innodb data, the reason is that the innodb data storage method can be configured to determine whether to use the shared tablespace to store the stored data or use the exclusive tablespace to store the stored data.
The. ibd file is used for exclusive tablespace storage, and each table has one ibd File
Shared tablespace storage uses the. ibdata file. All Tables use an ibdata file together.
Create a partition
Partition has the following advantages:
· More data can be stored than a single disk or a file system partition.
· For data that has lost its meaning, you can easily Delete the data by deleting the partitions related to the data. On the contrary, in some cases, the process of adding new data can be easily implemented by adding a new partition for those new data.
Other advantages related to partitions are listed below. These features in the MySQL partition are not yet implemented yet, but they have a high priority in our priority list. We hope to include these features in the 5.1 production version.
· Some queries can be greatly optimized. This is mainly because data that satisfies a given WHERE statement can be saved in only one or more partitions, in this way, you do not need to find other remaining partitions. Because partitions can be modified after the partition table is created, data can be re-organized when the partition scheme is not configured for the first time to improve the efficiency of common queries.
· The query involving Aggregate functions such as SUM () and COUNT () can be easily processed in parallel. A simple example of this query is "SELECT salesperson_id, COUNT (orders) as order_total FROM sales group by salesperson_id ;". Through "Parallel", this means that the query can be performed on each partition at the same time, and the final result is only obtained through the total of all partitions.
· Data query is distributed across multiple disks to increase query throughput.
In short, it is Data Management Optimization, faster query, and parallel data query.
Checks whether mysql supports partitioning.
Copy codeThe Code is as follows:
Mysql> show variables like
"% Partition % ";
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Have_partitioning | YES |
+ ----------------- + ------- +
1 row in set

RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval.
Copy codeThe Code is as follows:
Drop table if exists 'P _ range ';
Create table 'P _ range '(
'Id' int (10) not null AUTO_INCREMENT,
'Name' char (20) 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 )*/;

The range partition is partition by range (id), which means that the data by id 1-7 is stored in p0 partition. If the id is greater than 7, the data cannot be written because there is no corresponding Data partition for storage;
Therefore, you need to use the maxvalues keyword when creating a partition.
Copy codeThe Code is as follows:
Partition by range (id)
(
PARTITION p0 values less than (8 ),
PARTITION p1 values less than maxvalue)

This indicates that all data records with IDs greater than 7 exist in the p1 partition.
RANGE partitioning is particularly useful in the following scenarios:
· To delete "old" data. If you use the PARTITION scheme provided in the latest example above, you just need to simply use "alter table employees drop partition p0; to delete all the rows corresponding to the employees who stopped working before January 1, 1991. For tables with a large number of rows, this is 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 contains a value that increases from some other levels.
· Regular operations depend directly on the query of columns used to split tables. For example, when a query such as "select count (*) FROM employees where year (separated) = 2000 group by store_id;" is executed, mySQL can quickly determine that only partitions p2 need to be scanned, because the remaining partitions cannot contain any records that match the WHERE clause.
LIST partition: similar to partitioning by RANGE, the difference is that LIST partition is selected based on the column value matching a value in a discrete value set.
List partitions can be understood as data storage by the id interval of a key. For example, all records of Type table 1, 2, and 4 are stored in p0, and records 5, 6, 7, and 8 are stored in p1 partitions.
Similar to the range partition, if the typeid of a record is 9, this record cannot be saved;
Note that LIST partitions do not have definitions similar to other VALUES such as "values less than maxvalue. Any value to be matched must be found in the Value List.
Copy codeThe Code is as follows:
Drop table if exists 'P _ list ';
Create table 'P _ List '(
'Id' int (10) not null AUTO_INCREMENT,
'Typeid' mediumint (10) not null default '0 ',
'Typename' char (20) 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: 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.
HASH partitions are mainly used to ensure that data is evenly distributed in pre-defined partitions. In the RANGE and LIST partitions, you must specify the partition in which a given column value or column value set should be stored. In the HASH partition, MySQL automatically completes these tasks, all you need to do is specify a column value or expression based on the column value to be hashed, and specify the number of partitions to be split into in the partitioned table.
To use HASH partitions to split a TABLE, add a "partition by hash (expr)" clause to the create table statement. "expr" is an expression that returns an integer. It can only be the name of a column whose field type is MySQL integer. In addition, you may need to add a "PARTITIONS num" clause later. num is a non-negative integer, which indicates the number of PARTITIONS to be split in the table. If a partition clause is not included, the number of PARTITIONS is 1 by default.
Copy codeThe Code is as follows:
Drop table if exists 'P _ hash ';
Create table 'P _ hash '(
'Id' int (10) not null AUTO_INCREMENT,
'Storeid' mediumint (10) 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
Simply put, data can be stored by partition by hash (expr); expr can be a key name or expression, such as YEAR (time ).
"But remember that this expression is calculated once every time a row is inserted or updated (or possibly deleted). This means that a very complex expression may cause performance problems, especially when a large number of rows (such as batch insert) are affected during execution. "
This expression is calculated once when deletion, write, or update is executed.
The data distribution uses the modulus based on the user function result to determine which number partition is used. In other words, for an expression "expr", the Partition Number of the record to be saved is N, where "N = MOD (expr, num )".
For example, if the storeid above 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 '2017-09-15 'into the table, the partition for storing the record is determined as follows: MOD (YEAR ('2017-09-01'), 4) = MOD () = 1; it is stored in the p1 partition.
MySQL 5.1 also supports a variable called "linear hashing (linear hash function)", which uses a more complex algorithm to determine the location of a new row inserted into a partitioned table.
The only difference in syntax between a LINEAR hash PARTITION and a conventional hash PARTITION is that the "LINEAR" keyword is added to the "partition by" clause; the linear hash function uses a linear power 2 (powers-of-two) algorithm.
The advantage of linear hash partitioning is that adding, deleting, merging, and splitting partitions will become faster, facilitating processing tables with extremely large amounts of (1000 GB) data.
Its disadvantage is that the distribution of data in each shard is unlikely to be balanced compared with the data distribution obtained by conventional HASH partitions ."
KEY partitioning: similar to HASH partitioning, the difference is that KEY partitioning only supports computing one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain integer values.
Copy codeThe Code is as follows:
Drop table if exists 'P _ key ';
Create table 'P _ key '(
'Id' int (10) not null AUTO_INCREMENT,
'Keyname' char (20) 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 by KEY is similar to partitioning by HASH. Except for the User-Defined expression used by HASH partition, the HASH function of KEY partition is provided by the MySQL server. MySQL Cluster uses the MD5 () function to implement KEY partitioning. For tables using other storage engines, the server uses its own internal hash function, which is based on () the same algorithm.
"Create table... The partition by key syntax is similar to creating a table using HASH partitions. The only difference between them is that the KEY keyword is used instead of HASH, and the KEY partition uses only one or more column names.
The difference with hash is that hash uses a user-defined expression such as YEAR (time), while the key partition is provided by the mysql server. Similarly, the KEY can also use the linear key, which is the same as the hash linear algorithm.
Subpartition: it is the re-division of each partition in the partition table.
Copy codeThe Code is as follows:
Drop table if exists 'P _ subpartition ';
Create table 'P _ subpartition '(
'Id' int (10) default null,
'Title' char (255) not null,
'Createtime' date NOT NULL
) ENGINE = MyISAM default charset = utf8
/*! 50100
Partition by range (YEAR (createtime ))
Subpartition by hash (MONTH (createtime ))
(PARTITION p0 values less than (2012)
(SUBPARTITION s1 ENGINE = MyISAM,
SUBPARTITION 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 ))*/;

We can see that p_subpartition has three partitions: p0, p1, and p2. Each of these three partitions is further divided into two partitions. The entire table is divided into six small partitions;

The file representing p_sobpartition1_myd disappears, replacing p_subpartition.