CREATE TABLE `20130117date_par` (
`content` varchar(20) NOT NULL,
`create_time` datetime NOT NULL,
KEY `20130117date_idx_date` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(create_time))
(PARTITION p2009 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN (2011),
PARTITION p2011 VALUES LESS THAN (2012),
PARTITION p2012 VALUES LESS THAN (2013),
PARTITION p2013 VALUES LESS THAN (2014))
CREATE TABLE `20130117date` (
`content` varchar(20) NOT NULL,
`create_time` datetime NOT NULL,
KEY `20130117date_idx_date` (`create_time`)
) ENGINE=InnoDB
用sp向分区表和普通表各插入了90w条随机数据.
用mysqlslap进行下测试
不用分区表
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';
引用
Benchmark
Average number of seconds to run all queries: 0.881 seconds
Minimum number of seconds to run all queries: 0.062 seconds
Maximum number of seconds to run all queries: 3.844 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
Average number of seconds to run all queries: 0.703 seconds
Minimum number of seconds to run all queries: 0.062 seconds
Maximum number of seconds to run all queries: 1.922 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
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 seconds to run all queries: 4.032 seconds
Number of clients running queries: 1
Average number of queries per client: 2
用分区表
select SQL_NO_CACHE * from 20130117date_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';
引用
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 queries per client: 2
Benchmark
Average number of seconds to run all queries: 0.250 seconds
Minimum number of seconds to run all queries: 0.031 seconds
Maximum number of seconds to run all queries: 1.078 seconds
Number of clients running queries: 1
Average number of queries per client: 2
Benchmark
Average number of seconds to run all queries: 0.046 seconds
Minimum number of seconds to run all queries: 0.046 seconds
Maximum number of seconds to run all queries: 0.047 seconds
Number of clients running queries: 1
Average number of queries per client: 2
看来性能还是有一定的提升的.
执行
explain PARTITIONS select * from 20130117date_par
where create_time BETWEEN '2012-01-01' and '2012-01-02';
可以看出这个query只扫描了p2012这个分区.
而且分区表的好处在于维护比较方便.比如2009年的数据不需要了,分区表的方法为
alter table 20130117date_par drop PARTITION p2009
不到1s就行了
普通表为
delete from 20130117date
where create_time BETWEEN '2009-01-01' and '2010-01-01'
用了10.25s左右
0
顶
1
踩
分享到:
2013-01-30 19:42
浏览 2515
分类:数据库
评论
2 楼
houyujiangjun
2013-04-04
获益良多,楼主给力
1 楼
guoht
2013-01-31
依赖mysql自身的partions,在小数据量上的处理,看来性能也不错。这是个不错的mysql优化技巧。