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.