Data splitting-create a Mysql Partition Table and perform performance analysis to split mysql

Data splitting-create a Mysql Partition Table and perform performance analysis to split mysql

For how to install Mysql, refer:

Http://blog.csdn.net/jhq0113/article/details/43812895

For more information about Mysql partition tables, see:

Http://blog.csdn.net/jhq0113/article/details/44592865

1. Check whether your Mysql supports partitioning.

Mysql> show variables like '% partition % ';

If the result is as follows, your Mysql supports Table Partitioning:

+ ----------------------- + ------- +

       | Variable_name         | Value |

       +-----------------------+-------+

       | have_partition_engine | YES   |

       +-----------------------+-------+

       1 row in set (0.00 sec)       

How to Create a RANGE partition table:

Drop table if exists 'my _ orders '; create table 'my _ orders' ('id' int (10) unsigned not null AUTO_INCREMENT COMMENT 'table primary key ', 'pid 'int (10) unsigned not null comment 'product id', 'price' decimal (15, 2) not null comment 'unit price', 'num' int (11) not null comment 'purchase qty ', 'uid' int (10) unsigned not null comment 'customer id', 'atime 'datetime not null comment' order time ', 'utime' int (10) unsigned not null default 0 COMMENT 'modification time', 'isdel 'tinyint (4) not null default '0' comment' soft Delete identifi ', primary key ('id', 'atime ')) ENGINE = InnoDB default charset = utf8/********** PARTITION information ***************/partition by range (YEAR (atime )) (PARTITION p0 values less than (2016), PARTITION p1 values less than (2017), PARTITION p2 values less than maxvalue );

The preceding table is a simple order table. The partition field is atime, which is based on the RANGE partition. In this way, when you insert data into the table, Mysql will) value for partition storage.

Check whether the partition is successfully created and run the query statement:

Explain partitions select * FROM 'my _ orders'

If successful, the result is as follows:

Performance analysis:

1) create a table with the same structure but no partitions

Drop table if exists 'my _ order'; create table 'my _ order' ('id' int (10) unsigned not null AUTO_INCREMENT COMMENT 'table primary key ', 'pid 'int (10) unsigned not null comment 'product id', 'price' decimal (15, 2) not null comment 'unit price', 'num' int (11) not null comment 'purchase qty ', 'uid' int (10) unsigned not null comment 'customer id', 'atime 'datetime not null comment' order time ', 'utime' int (10) unsigned not null default 0 COMMENT 'modification time', 'isdel 'tinyint (4) not null default '0' comment' soft Delete identifi ', primary key ('id', 'atime ') ENGINE = InnoDB default charset = utf8;

2). Insert the same data into the two tables

/************************** Insert data into a partitioned table ********* * *****************/insert into my_orders ('pid ', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, CURRENT_TIMESTAMP (); insert into my_orders ('pid ', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, 2016, '2017-05-01 00:00:00 '); insert into my_orders ('pid ', 'price', 'num', 'uid', 'atime') VALUES (1, 12.23, '2017-05-01 00:00:00 '); insert into my_orders ('pid', 'price', 'num', 'uid', 'atime ') VALUES (1, 2017, 2018,-05-01 00:00:00 '); insert into my_orders ('pid', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, 2015,-05-01 00:00:00 '); insert into my_orders ('pid', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, 2016, '2017-05-01 00:00:00 '); insert into my_orders ('pid', 'price', 'num', 'uid ', 'atime ') VALUES (1, 12.23, 2017, '2017-05-01 00:00:00'); insert into my_orders ('pid ', 'price', 'num ', 'uid', 'atime ') VALUES (1, 12.23, 2018, '2017-05-01 00:00:00 '); /************************** insert data into a non-partitioned table ******** * *****************/insert into my_order ('pid ', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, CURRENT_TIMESTAMP (); insert into my_order ('pid ', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, 2016, '2017-05-01 00:00:00 '); insert into my_order ('pid ', 'price', 'num', 'uid', 'atime') VALUES (1, 12.23, '2017-05-01 00:00:00 '); insert into my_order ('pid', 'price', 'num', 'uid', 'atime ') VALUES (1, 2017, 2018,-05-01 00:00:00 '); insert into my_order ('pid', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, 2015,-05-01 00:00:00 '); insert into my_order ('pid', 'price', 'num', 'uid', 'atime ') VALUES (1, 12.23, 2016, '2017-05-01 00:00:00 '); insert into my_order ('pid', 'price', 'num', 'uid ', 'atime ') VALUES (1, 12.23, 2017, '2017-05-01 00:00:00'); insert into my_order ('pid ', 'price', 'num ', 'uid', 'atime ') VALUES (1, 12.23, 2018, '2017-05-01 00:00:00 ');

3). About 0.2 million master-slave replication records (there is a gap between master-slave replication data and the real environment, but it can reflect the performance of table partition queries)

/********************************** Master-slave replication of a large amount of data * * **************************/insert into 'my _ orders '('pid ', 'price', 'num', 'uid', 'atime ') SELECT 'pi', 'price', 'num', 'uid ', 'atime 'FROM 'my _ orders'; insert into 'my _ order' ('pid ', 'price', 'num', 'uid', 'atime ') SELECT 'pid ', 'price', 'num', 'uid', 'atime' FROM 'my _ order ';

4) query Test

********* * ***************************/SELECT * FROM 'my _ orders 'where' uid' = 89757 AND 'atime' <CURRENT_TIMESTAMP (); /*** 0.084 s ***/SELECT * FROM 'my _ order' WHERE 'uid' = 89757 AND 'atime '<CURRENT_TIMESTAMP (); /*** 0.284 s ****/

The preceding query shows that the query performance of table partitions is better, and the query takes less time.

Analysis and Query Process:

Explain partitions select * FROM 'my _ orders 'where 'uid' = 89757 AND 'atime' <CURRENT_TIMESTAMP ();

Explain partitions select * FROM 'my _ order' WHERE 'uid' = 89757 AND 'atime' <CURRENT_TIMESTAMP ();

The above results show that the my_orders Table query goes through the p0 partition and only scans 49386 rows, while the my_order table does not partition and scans 196983 rows, this is also the key to improving performance.

Of course, the more partitions in a table, the better. When there are too many partitions in the table, finding partitions is a performance bottleneck. We recommend that you set the number of partitions to less than 200.

LIST Partition Table creation method:

/***************** Create a partition table ******************** */create table 'products' ('id' bigint unsigned not null AUTO_INCREMENT COMMENT 'table primary key ', 'name' varchar (64) character set utf8 COLLATE utf8_general_ci not null comment 'product name', 'metrial' tinyint unsigned not null comment' texture ', 'weight' double unsigned not null default 0 COMMENT 'weight', 'vol 'double unsigned not null default 0 comment ', 'C _ id' tinyint unsigned not null comment 'supply company id', primary key ('id', 'c _ id ')) ENGINE = InnoDB default charset = utf8/********** PARTITION information ***************/partition by list (c_id) (PARTITION pA values in (,), PARTITION pB values in (,), PARTITION pC values in (,), PARTITION pD values in, ), PARTITION pE values in ));

It can be seen that LIST partitions are similar to RANGE partitions, so no performance analysis is performed here, which is similar to RANGE.

How to Create a HASH partition table:

'Msgs '('id' bigint (20) unsigned not null AUTO_INCREMENT COMMENT 'table primary key', 'sender' int (10) unsigned not null comment 'sender id', 'reciver' int (10) unsigned not null comment 'receiver id', 'msg _ type' tinyint (3) unsigned not null comment 'message type', 'msg 'varchar (225) not null comment 'message content', 'atime' int (10) unsigned not null comment' sending time', 'sub _ id' tinyint (3) unsigned not null comment 'department id', primary key ('id', 'sub _ id ')) ENGINE = InnoDB default charset = utf8/********** PARTITION information ***************/partition by hash (sub_id) PARTITIONS 10;

The preceding statement indicates that the msgs table is HASH partitioned by sub_id and has a total of 10 partitions.

The Key partition and HASH partition are similar and will not be introduced. For details, refer to the Mysql official documentation.

How to Create a subpartition:

Create table 'msgss' ('id' bigint (20) unsigned not null AUTO_INCREMENT COMMENT 'table primary key', 'sender' int (10) unsigned not null comment 'sender id ', 'civer 'int (10) unsigned not null comment 'receiver id', 'msg _ type' tinyint (3) unsigned not null comment' message type ', 'msg 'varchar (225) not null comment' message content', 'atime' int (10) unsigned not null comment' sending time', 'sub _ id' tinyint (3) unsigned not null comment 'department id', primary key ('id', 'atime ', 'sub _ id ')) ENGINE = InnoDB default charset = utf8/********** PARTITION information ***************/partition by range (atime) subpartition by hash (sub_id) (PARTITION t0 values less than (1451577600) (SUBPARTITION s0, SUBPARTITION s1, SUBPARTITION s2, SUBPARTITION s3, SUBPARTITION s4, SUBPARTITION s5 ), PARTITION t1 values less than (1483200000) (SUBPARTITION s6, SUBPARTITION s7, SUBPARTITION s8, SUBPARTITION s9, PARTITION s10, SUBPARTITION s11), PARTITION t2 values less than maxvalue (SUBPARTITION s12, SUBPARTITION s13, SUBPARTITION s14, SUBPARTITION s15, SUBPARTITION s16, SUBPARTITION s17 ));

Check whether the Sub-partition is successfully created:

Explain partitions select * FROM msgss;

The result is as follows: