Mysql partition (partition) A preliminary study of the table data volume is generally considered a horizontal split, that is, the so-called sharding. However, MySQL itself has a partitioning function to achieve a certain level of horizontal segmentation . MySQL is the engine with the merge, is to use some of the same structure of the Myiasm table as a table, but I think the merge is not as partition practical, www.2cto.com Because the merge will be queried on all the underlying tables, and partition only queries the corresponding partitions, . has established two tables, partitioned and unpartitioned, partitioned by year. sql Code create table ' 20130117date_par ' ( ' content ' varchar () not NULL, ' Create_time ' datetime is NOT NULL,  &NBSP ; KEY ' 20130117date_idx_date ' (' create_time ') ) Engine=innodb DEFAULT Charset=utf8 partition by RANGE ( Create_time) (PARTITION p2009 values less THAN), partition p2010 values less THAN (+), & nbsp PARTITION p2011 Values less THAN (+), partition p2012 values less THAN, partition p2013 VALUES less THAN) create TABLE ' 20130117date ' ( ' content ' varchar) is not NULL, &nbs p; ' Create_time ' datetime not NULL, KEY ' 20130117date_idx_datE ' (' Create_time ') ) Engine=innodb use SP to insert 90w random data into partition table and normal table . use Mysqlslap to test No partition Table sql code select Sql_no_cache * from 20130117date where create_time between ' 2013-01-01 ' and ' 2013-01- 02 '; select Sql_no_cache * from 20130117date where create_time between ' 2012-12-25 ' and ' 2013-01-05 '; references Benchmark Average number of seconds to run all queries:0.881 seconds Minimum number of seconds to run all queries:0.062 seconds &N Bsp Maximum number of seconds to run all queries:3.844 seconds Number of clients running que ries:1 Average number of queries per client:2 benchmark Average number of seconds to run all queries:0.703 seconds Minimum number of Seco NDS to run all queries:0.062 SECONDS&NBsp; Maximum number of seconds to run all queries:1.922 seconds &NBSP ; Number of clients running queries:1 Average number of queries per Client:2 benchma rk Average number of seconds to run all queries:1.250 seconds Minimum number of seconds to run all queries:0.109 seconds Maximum number of Seco NDS to run all queries:4.032 seconds number of clients running queries:1 &N Bsp Average number of queries per client:2 with partition Table sql code select Sql_no_cache * from 20 130117date_par where create_time between ' 2013-01-01 ' and ' 2013-01-02 '; select Sql_no_cache * from 20130117date_par where create_time between ' 2012-12-25 ' and ' 2013-01-05 '; references Benchmark Average Number of seconds to run all queries:0.068 seconds Minimum number of seconds to run all queries:0.047 seconds Maximum number of seconds to run all queries:0.110 seconds number of clients running queries:1 Average number of Querie s per client:2 benchmark Average number of seconds to run all queries:0.250 second s Minimum number of seconds to run all queries:0.031 seconds & nbsp Maximum number of seconds to run all queries:1.078 seconds Number of clients running que ries:1 Average number of queries per client:2 benchmark Average number of seconds to run all queries:0.046 seconds Minimum number of Seco NDS to run all queries:0.046 seconds Maximum number of seconds to run all queries:0.047 Seconds &nbs P Number of clients running queries:1 Average number of queries per C lient:2 www.2cto.com There is a certain improvement in performance . implementation and nbsp SQL code explain Partitions SELECT * from 20130117date_par where create_time between ' 2012-01-01 ' and ' 2012-01-02 ‘; can see that this query only scans the P2012 partition . and the benefits of partitioned tables are easier to maintain. For example, 2009 data is not required, the partitioning table method is sql code alter table 20130117date_par drop PARTITION p2009 less than 1s on the line plain table for sql code delete from 20130117date where create_time between ' 2009-01-01 ' and ' 2010-01-01 ' used about 10.25s
MySQL Partition (partitioning)
