mysql Partition(分区)初探_MySQL

bitsCN.com

mysql partition(分区)初探

表数据量大的时候一般都考虑水平拆分,即所谓的sharding.不过mysql本身具有分区功能,可以实现一定程度 的水平切分. 

mysql是具有MERGE这种引擎的,就是把一些结构相同的MyIASM表作为一个表使用,但是我觉得 MERGE不如partition实用, 

因为MERGE会在所有的底层表上查询,而partition只在相应的分区上查询. 

建立了两个表,分别为分区和未分区的,分区表按年进行分区. 

Sql代码  

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进行下测试 

不用分区表 

Sql代码  

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 

citySHOP多用户商城系统

citySHOP是一款集CMS、网店、商品、分类信息、论坛等为一体的城市多用户商城系统，已完美整合目前流行的Discuz! 6.0论坛，采用最新的5.0版PHP+MYSQL技术。面向对象的数据库连接机制，缓存及80%静态化处理，使它能最大程度减轻服务器负担，为您节约建设成本。多级店铺区分及联盟商户地图标注，实体店与虚拟完美结合。个性化的店铺系统，会员后台一体化管理。后台登陆初始网站密匙：LOVES

0

查看详情

        Number of clients running queries: 1 

        Average number of queries per client: 2 

用分区表 

Sql代码  

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 

看来性能还是有一定的提升的. 

执行 

Sql代码  

explain PARTITIONS select * from 20130117date_par  

where create_time BETWEEN '2012-01-01' and '2012-01-02';  

可以看出这个query只扫描了p2012这个分区. 

而且分区表的好处在于维护比较方便.比如2009年的数据不需要了,分区表的方法为 

Sql代码  

alter table 20130117date_par drop PARTITION p2009  

不到1s就行了 

普通表为 

Sql代码  

delete from 20130117date  

where create_time BETWEEN '2009-01-01' and '2010-01-01'  

用了10.25s左右