原文链接:http://www.javaarch.net/jiagoushi/552.htm
Mysql 5.5分区特性增强深度解析 1.最直观的部分,用非整数列分区 partition by non-integer columns,mysql5.1只支持基于整数列的分区,如果你要用日期 date或者字符串 string,那么你自己需要写一个函数做转换。 比如我们看下面一个table: CREATE TABLE expenses ( expense_date DATE NOT NULL, category VARCHAR(30), amount DECIMAL (10,3) ); 如果在mysql5.1中,你想用category列来作为分区列,那么你要把category转为整数。但是在mysql5.5版本中,你可以直接用下面的方式来处理分区问题 ALTER TABLE expenses PARTITION BY LIST COLUMNS (category) ( PARTITION p01 VALUES IN ( 'lodging', 'food'), PARTITION p02 VALUES IN ( 'flights', 'ground transportation'), PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'), PARTITION p04 VALUES IN ( 'communications'), PARTITION p05 VALUES IN ( 'fees') ); mysql5.1中是怎么处理date类型的分区呢,你可以直接使用这些date的列,但是你必须把这些column转为YEAR或TO_DAYS /* with MySQL 5.1*/ CREATE TABLE t2 ( dt DATE ) PARTITION BY RANGE (TO_DAYS(dt)) ( PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')), PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')), PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')), PARTITION p04 VALUES LESS THAN (MAXVALUE)); SHOW CREATE TABLE t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `dt` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (TO_DAYS(dt)) (PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM, PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM, PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM, PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 但是要是查询如果不用函数转换下那么就无法利用分区特性来提高性能。 我们看看mysql5.5的写法: /*With MySQL 5.5*/ CREATE TABLE t2 ( dt DATE ) PARTITION BY RANGE COLUMNS (dt) ( PARTITION p01 VALUES LESS THAN ('2007-01-01'), PARTITION p02 VALUES LESS THAN ('2008-01-01'), PARTITION p03 VALUES LESS THAN ('2009-01-01'), PARTITION p04 VALUES LESS THAN (MAXVALUE)); SHOW CREATE TABLE t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `dt` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE COLUMNS(dt) (PARTITION p01 VALUES LESS THAN ('2007-01-01') ENGINE = MyISAM, PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM, PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM, PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */ 支持多列的分区 CREATE TABLE employees ( emp_no int(11) NOT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL, gender char(1) DEFAULT NULL, hire_date date NOT NULL ) ENGINE=MyISAM PARTITION BY RANGE COLUMNS(gender,hire_date) (PARTITION p01 VALUES LESS THAN ('F','1990-01-01') , PARTITION p02 VALUES LESS THAN ('F','2000-01-01') , PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) , PARTITION p04 VALUES LESS THAN ('M','1990-01-01') , PARTITION p05 VALUES LESS THAN ('M','2000-01-01') , PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) , PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE) 那么我们下面的查询就能利用分区的特性: select count(*) from employees where gender='F' and hire_date < '1990-01-01'; +----------+ | count(*) | +----------+ | 66212 | +----------+ 1 row in set (0.05 sec) explain partitions select count(*) from employees where gender='F' and hire_date < '1990-01-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: p01 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024 Extra: Using where select count(*) from employees where gender='F'; +----------+ | count(*) | +----------+ | 120051 | +----------+ 1 row in set (0.12 sec) explain partitions select count(*) from employees where gender='F'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: p01,p02,p03,p04 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024 Extra: Using where 但是如果用第二列的话,那么只能全盘扫描。 select count(*) from employees where hire_date < '1990-01-01'; +----------+ | count(*) | +----------+ | 164797 | +----------+ 1 row in set (0.18 sec) explain partitions select count(*) from employees where hire_date < '1990-01-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: p01,p02,p03,p04,p05,p06,p07 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024 Extra: Using where 截断分区:在5.1中我们可以使用DELETE或者 DROP PARTITION来删除过期的分区,5.5中使用TRUNCATE PARTITION来保留分区数,多余的删除掉。 5.5中我们可以使用TO_SECONDS来把日期转成秒,从第0年开始,以此来细分分区 CREATE TABLE t2 ( dt datetime ) PARTITION BY RANGE (to_seconds(dt)) ( PARTITION p01 VALUES LESS THAN (to_seconds('2009-11-30 08:00:00')) , PARTITION p02 VALUES LESS THAN (to_seconds('2009-11-30 16:00:00')) , PARTITION p03 VALUES LESS THAN (to_seconds('2009-12-01 00:00:00')) , PARTITION p04 VALUES LESS THAN (to_seconds('2009-12-01 08:00:00')) , PARTITION p05 VALUES LESS THAN (to_seconds('2009-12-01 16:00:00')) , PARTITION p06 VALUES LESS THAN (MAXVALUE) ); show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `dt` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE (to_seconds(dt)) (PARTITION p01 VALUES LESS THAN (63426787200) ENGINE = MyISAM, PARTITION p02 VALUES LESS THAN (63426816000) ENGINE = MyISAM, PARTITION p03 VALUES LESS THAN (63426844800) ENGINE = MyISAM, PARTITION p04 VALUES LESS THAN (63426873600) ENGINE = MyISAM, PARTITION p05 VALUES LESS THAN (63426902400) ENGINE = MyISAM, PARTITION p06 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) * 原文:http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html
相关推荐
MySQL 5.5新特性详解及参数优化进阶 中文版,此书MySQL DBA必备
mysql5.5安装包、mysql图形化界面工具
java连接mysql5.5的jar包 mysql-connector-java-5.1.35-bin.jar
MYSQL5.5英文手册,MYSQL5.5手册,MYSQL英文手册,MYSQL手册
MySQL 5.5+图形界面工具MySQL 5.5+图形界面工具MySQL 5.5+图形界面工具MySQL 5.5+图形界面工具
MySQL5.5特性测试报告 MySQL5.5特性测试报告 MySQL5.5特性测试报告
MySQL 5.5的300多个变量详细解释,DBA和MySQL调优必看~
MySQL5.5中文版
这是关于mysql以及其他开发工具在开发中很重要得一个工具,他可以实现数据库的连接,将在mysql中存储的数据在eclipse或myeclipse中连接。
Mysql5.5安装文件,里面有安装教程。教程相当详细,小白都可以按照教程进行安装
自己安装完 MySql5.5 后有点小问题 重新安装出错,整理资料 上传和大家分享 安装包 安装图解 完全卸载
MySql 5.5 + SqlManager
mysql5.5--64位 下载安装 mysql5.5--64位 下载安装 mysql5.5--64位 下载安装 mysql5.5--64位 下载安装
MySQL5.5安装步骤
找了很久才在网上找到的MySQL5.5装包,还自带安装教程,感觉很官方,很给力,推荐下载
mysql5.5免安装版
mysql 5.5 chm 中文手册,学习mysql的好帮手
MySql5.5源码安装包,官网下载被和谐了,放出自己有的版本大家一起学习。
mysql5.5是一款高性能的数据库安装软件,适用于winxp、win7等各种操作系统环境
mysql5.5版本和配置文件的详细说明。非常适合初学者。mysql5.5版本和配置文件的详细说明mysql5.5版本和配置文件的详细说明