MySQL partition, mysql table Partition

Source: Internet
Author: User

MySQL partition, mysql table Partition

In mysql, the learn directory structure is as follows:

Let's take a look at the definition of sales in the table:

show create  table sales \G
*************************** 1. row ***************************Table: salesCreate Table: CREATE TABLE `sales` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `amount` double NOT NULL,  `order_day` datetime NOT NULL,  PRIMARY KEY (`id`,`order_day`)) ENGINE=InnoDB DEFAULT CHARSET=latin1/*!50100 PARTITION BY RANGE (YEAR(order_day))(PARTITION p_2010 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION p_2011 VALUES LESS THAN (2011) ENGINE = InnoDB, PARTITION p_2012 VALUES LESS THAN (2012) ENGINE = InnoDB, PARTITION p_catchall VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */1 row in set (0.00 sec)

Definition of table p_key

show create  table p_key \G
*************************** 1. row ***************************Table: p_keyCreate Table: 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 */1 row in set (0.01 sec)

For the MyISAM engine, a table stores three files, the fm storage table structure, the myi storage index, and the myd storage data. But there is another file p_key.par corresponding to p_key.

Re-create a Range Partition Table fuhui_log to experience partition query:

DROP TABLE IF EXISTS fuhui_log;CREATE TABLE fuhui_log (    object_id int(11),    title varchar(20) NOT NULL  ,    content varchar(20) ,    time int(11),    primary key (object_id))PARTITION BY range (object_id)(    PARTITION p1 VALUES less than (5000),    PARTITION p2 VALUES less than (10000),    PARTITION p3 VALUES less than MAXVALUE);

A custom Stored Procedure inserts 20000 data records into the database:

delimiter //create procedure fun_fuhui_log() begin    declare i int;    set i = 1;    while i < 20000 do        insert into fuhui_log(object_id,title,content,time) values (i,concat('title_',i),'test content',i);        set i = i+1;    end while;end//

Call the stored procedure for data insertion:

delimiter ;call fun_fuhui_log();

Obtain the inserted data result:

 select count(*) from fuhui_log;

The query result is 19999, and the time consumed is 1 row in set (0.01 sec );

select * from fuhui_log where object_id = 13588;

Time consumed: 0.00 sec

Follow these steps to create a basic table, modify the stored procedure, and insert the same data:

DROP TABLE IF EXISTS fuhui_log2;CREATE TABLE fuhui_log2 (    object_id int(11),    title varchar(20) NOT NULL  ,    content varchar(20) ,    time int(11),    primary key (object_id));

The data structure design is too simple, the data size is too small, and the results cannot be seen. Re-Modify the stored procedure and insert 80000 pieces of data:

while i < 80000 do        replace into fuhui_log2(object_id,title,content,time) values (i,concat('title_',i),'test content',i);        set i = i+1;end while;
select count(*) from fuhui_log2;

Execution result: 1 row in set (0.02 sec)

select count(*) from fuhui_log;

Execution result: 1 row in set (0.03 sec) [not played by logic]

This example fails. Modify the table structure and remove the primary key.

 alter table fuhui_log drop primary key; alter table fuhui_log2 drop primary key;

The example still fails, making it difficult to find the execution efficiency.

select * from fuhui_log where object_id = 56770 \G

Time consumed: 0.05sec

select * from fuhui_log2 where object_id = 56770 \G

Time consumed: 0.06sec

For count statistics, fuhui_log consumes more time than fuhui_log2, and count parallel computing is corrupted by me.

Modify the partition structure and recalculate:

 alter table fuhui_log reorganize partition p3 into ( partition p3_1 values less than (30000), partition p3_2 values less than (50000), partition p3_3 values less than MAXVALUE);

View the result after partitioning:

select table_schema,table_name,partition_name,PARTITION_METHOD from information_schema.partitions where table_name='fuhui_log';

Then recalculate:

select count(*) from fuhui_log ;

The execution result is 0.04sec, which is equivalent to the statistical time of fuhui_log2. However

select * from fuhui_log where object_id = 56770 \G

Execution time changed to 0.02sec

I have been writing it for so long. Let's stop it today.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.