mysql Partition(分区)初探
2024-08-25 00:33:05
mysql Partition(分区)初探
表数据量大的时候一般都考虑水平拆分,即所谓的sharding.不过mysql本身具有分区功能,可以实现一定程度 的水平切分.
mysql是具有MERGE这种引擎的,就是把一些结构相同的MyIASM表作为一个表使用,但是我觉得 MERGE不如partition实用,
www.2cto.com
因为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
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
www.2cto.com
看来性能还是有一定的提升的.
执行
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左右
最新文章
- SQL Server Data Tools – Business Intelligence for Visual Studio 2012安装时提示“The CPU architecture....”的解决方法
- Redis学习笔记六:独立功能之 Lua 脚本
- 【基本算法】 KMP文本串模式串的字符串匹配算法
- UVa 11468 (AC自动机 概率DP) Substring
- Entity Framework中的Migration问题
- 博弈论(二分图匹配):NOI 2011 兔兔与蛋蛋游戏
- xcode设置项目图标玻璃镜效果
- POJ2513 Colored Sticks(欧拉)
- AES - Rijndael 算法(一)
- 《Programming Massively Parallel Processors》Chapter5 习题解答
- 关于tomcat的clean
- 布局常见问题之css实现多行文本溢出显示省略号(…)全攻略
- 【概率论与数理统计】小结3 - 一维离散型随机变量及其Python实现
- javascript中的事件类型
- VC++ 6.0中添加库文件和头文件
- [C++ Primer Plus] 零散知识点(一)、输入函数(cin,cin.get,cin.getline等)+string头文件辨析
- 北大poj- 1006
- 【php增删改查实例】第六节 - 部门管理模块(开始)
- Pig distinct用法举例
- Python isalpha() 方法