MySQL partition, mysql table Partition

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.