mysql-Partition Table-1

MySQL Database Learn folder structure:

Look at the definition of the table sales:

show create  table sales \G

1. Row ***************************table:salesCreate Table:CREATE TABLE ' Sales '(' id ' int( One) not NULLAuto_increment,' Amount ' Double  not NULL,' Order_day 'Datetime not NULL,PRIMARY KEY(' id ',' Order_day ')) Engine=innodbDEFAULTcharset=latin1/*!50100PARTITION byRANGE ( Year(Order_day)) (PARTITION p_2010VALUESLess THAN ( .) ENGINE = InnoDB, PARTITION p_2011VALUESLess THAN ( .) ENGINE = InnoDB, PARTITION p_2012VALUESLess THAN ( -) ENGINE = InnoDB, PARTITION p_catchallVALUESLess THAN MAXVALUE ENGINE = InnoDB) */1 Row inch Set(0.00Sec

Definition of Table P_key

show create  table p_key \G

1. Row ***************************table:p_keyCreate Table:CREATE TABLE ' P_key '(' id ' int(Ten) not NULLAuto_increment,' KeyName ' Char( -)DEFAULT NULL,' Keyval ' varchar( +)DEFAULT NULL,PRIMARY KEY(' id ')) Engine=myisam auto_increment= A DEFAULTcharset=utf8/*!50100PARTITION by KEY(ID) Partitions4*/1 Row inch Set(0.01Sec

For the MyISAM engine, a table is stored for 3 files. FM storage table structure. Myi stores the index and myd the data.

But p_key the corresponding other file P_key.par.

Once again, create a range partition of table Fuhui_log to experience partitioned queries:

 DROP TABLE IF EXISTS fuhui_log;CREATE TABLEFuhui_log (object_idint( One), titlevarchar( -) not NULL, contentvarchar( -) , Time int( One),Primary Key(object_id)) PARTITION byRange (object_id) (PARTITION p1VALUESLess Than ( the), PARTITION p2VALUESLess Than (10000), PARTITION P3VALUESless than MAXVALUE);

Define the stored procedure yourself and insert 20,000 data into the database:

Delimiter//Create  procedure fun_fuhui_log() begindeclare i int;        Seti =1; whileI <20000  DoInsert intoFuhui_log (Object_id,title,content,time) VALUES (i,concat(' Title_ ', i),' test content ', i);Seti = i+1;End  while;End//

Call the stored procedure to insert the data:

delimiter ;call fun_fuhui_log();

Get Insert Data result:

select count(*) from fuhui_log;

The result of the query is 19999, time: 1 row in Set (0.01 sec);

select * from fuhui_log where object_id = 13588;

Time 0.00 sec

Based on the steps above. Create a basic table and change the stored procedure to 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));   

Data structure design is too simple, the amount of data is too small. If you don't see the effect, change the stored procedure first. Insert 80,000 data:

whilei80000 do        replace into fuhui_log2(object_id,title,content,time) values (i,concat(‘title_‘,i),‘test content‘,i);        ii+1;endwhile;

select count(*) from fuhui_log2;

Run Result: 1 row in Set (0.02 sec)

select count(*) from fuhui_log;

Run Result: 1 row in Set (0.03 sec) "not logically out of hand"

This example is very unsuccessful and changes the table structure. Remove PRIMARY Key

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

The sample is still relatively unsuccessful, the efficiency of the operation is very difficult to find

selectfromwhere56770 \G

Time: 0.05sec

selectfromwhere56770 \G

Time Consuming 0.06sec

For Count statistics, Fuhui_log is more time-consuming than fuhui_log2. Count's parallel computations, I've defiled them.

Change the partition structure and compute again:

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);

To view the results after another partition:

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

And then calculate again:

select count(*) from fuhui_log ;

The running effect is 0.04sec, and the statistic time of fuhui_log2 is equal. But

selectfromwhere56770 \G

Run time becomes 0.02sec

It's been written so long, today it's 罢笔

mysql-Partition Table-1