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

Draft missing, published incompletely, rearranged later

MySQL table partition technology can effectively solve the shortcomings of horizontal and vertical splitting, and its operability and efficiency are better. Here is a summary of some of the experiments.

InnoDB engine needs to set innodb_file_per_table=1 in the configuration file first

--innodb : The main table.frm holds.ibd files with the same table structure and number of partitions, which are used to store data and indexes.

--myisam: The main table.frm holds the table structure definition, the main table.par holds the partition information, and the number of partitions is consistent with the.MYD file, which is used to store data, and the.MDI file is used for indexing.

Range partition:

CREATE TABLE a(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME CHAR(20))ENGINE=INNODB CHARSET=utf8

PARTITION BY RANGE(id)(

PARTITION p1 VALUES LESS THAN (100),

PARTITION p2 VALUES LESS THAN (200),

PARTITION p3 VALUES LESS THAN (300),

PARTITION p4 VALUES LESS THAN MAXVALUE);

-------------Establish a partition divided by id interval. When id is less than 100, the data is saved to partition p1, when id is 100 to 199, the data is saved to partition p2, when id is 200 to 299, the data is saved to partition p3, and when id is greater than 300, the data is saved to partition p4;

[root@master test]#dir

a.frm a#P#p1.ibd a#P#p2.ibd a#P#p4.ibd db.opt

List partition:

CREATE TABLE bc(

id INT NOT NULL AUTO_INCREMENT,

par_no INT NOT NULL DEFAULT '1',

a_name CHAR(20) NOT NULL,

PRIMARY KEY(id,par_no)) ENGINE=MYISAM CHARSET=gbk PARTITION BY LIST(par_no)(

PARTITION p0 VALUES IN (10,20,30),

PARTITION p1 VALUES IN (40,50,60),

PARTITION p2 VALUES IN (70,80,100));

---When inserting data, the value of par_no must exist in the partition definition, otherwise it cannot be inserted and an error is reported.

[root@master test]# dir nb*

nb.frmnb.parnb#P#p0.MYD nb#P#p0.MYI nb#P#p1.MYD nb#P#p1.MYI nb#P#p2.MYD nb#P#p2.MYI nb#P#p3.MYD nb#P#p3.MYInb#P#p4.MYD nb#P#p4.MYI

Myisam engine is not supported in versions after mysql partition. Just replace it with innodb.

Warning Code : 1287

The partition engine, used by table 'test.bc', is deprecated and will be removed in a future release. Please use native partitioning instead.

Hash partition:

CREATE TABLE nb(

id INT NOT NULL AUTO_INCREMENT,

par_no INT NOT NULL DEFAULT '1',

a_name CHAR(20) NOT NULL,

PRIMARY KEY(id,par_no)) ENGINE=MYISAM CHARSET=gbk PARTITION BY HASH(id)

PARTITIONS 5; --partition More s , let mysql automatically id Hash value stored in 5 partitions.

Query the amount of data present in the partition table:

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='bcd'

Partition Management--Test Summary: Delete partition: ALTER TABLE tablename REMOVE PARTITIONING ; --Delete partition definition, all data set into one table, data not lost ALTER TABLE bc DROP PARTITION p1; --Deleting partitions deletes data in partitions Modify partitions: ALTER TABLE nb PARTITION BY HASH(id) PARTITIONS 2; --Hash partitions are redefined to two and data merge partitions are redistributed ALTER TABLE bc REORGANIZE PARTITION p1,p0 INTO ( PARTITION p6 VALUES IN (10,20,30,40)); --Merge partitions p0,p1 in BC table into partition p6, and the [list] values of p0,p1 must be included in the new partition, otherwise the data not in the new partition [list] will be lost;ALTER TABLE a REORGANIZE PARTITION p0,p1,p2 INTO (PARTITION p0 VALUES LESS THAN (500),PARTITION p1 VALUES LESS THAN maxvalue); --Redefine partition structure: merge Range partitions p1,p2 into p0 partition. Since the original p2 partition is maxvalue, a new maxvalue partition must be added at the same time, otherwise an error will be reported. Split partition: Add partition: If there is no partition:ALTER TABLE nb PARTITION BY HASH(id) PARTITIONS 2; --Divide the table into two hash partitions with id; if there is a unique key, delete it first.ALTER TABLE a PARTITION BY RANGE(id)( --a table has no partitions.PARTITION p0 VALUES LESS THAN (1000),PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN maxvalue)--------------------------------------------------------------------ALTER TABLE bc PARTITION BY LIST(par_no) (PARTITION p1 ----Add two list partitions to BC table without defined partition table;VALUES IN (10,20,30),PARTITION p2 VALUES IN (40,50,60,70,80)); in case of existing partitions: merge partitions: ALTER TABLE abc REORGANIZE PARTITION p2 INTO (PARTITION p2 VALUES LESS THAN (6000),PARTITION p3 VALUES LESS THAN maxvalue); --Redefine Range partition p2 and add a new partition p3. The value of partition p2 cannot be less than the existing maximum value, and a new partition containing the maximum value must be included.(Maxvalue) new partition, otherwise error ALTER TABLE bc ADD PARTITION (PARTITION p3 VALUES IN (20,30));--Add a list partition ALTER TABLE nb ADD PARTITION PARTITIONS 2;--(Hash partition), the data in the existing partition will be smoothly distributed to the new partition after the new addition,myisam engine can be reflected in the query statement above,innodb engine counts information lost, counting from 0, but the data in the table will not be lost.

Note: Deleting a partition will delete the data in the partition at the same time, and the enumerated list value will also be deleted. The data of this value cannot be inserted into the table later.

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.