Mysql 5.5分区特性增强深度解析
来源：互联网 发布：中国2015年失业率数据 编辑：程序博客网 时间：2024/05/17 02:58
原文链接：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 expensesPARTITION 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: t2Create 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: t2Create 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=MyISAMPARTITION 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: SIMPLEtable: employees partitions: p01 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024Extra: Using whereselect 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: SIMPLEtable: employees partitions: p01,p02,p03,p04 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024Extra: 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: SIMPLEtable: employees partitions: p01,p02,p03,p04,p05,p06,p07 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024Extra: 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: t2Create 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 原文链接：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 expensesPARTITION 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: t2Create 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: t2Create 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=MyISAMPARTITION 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: SIMPLEtable: employees partitions: p01 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024Extra: Using whereselect 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: SIMPLEtable: employees partitions: p01,p02,p03,p04 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024Extra: 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: SIMPLEtable: employees partitions: p01,p02,p03,p04,p05,p06,p07 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 300024Extra: 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: t2Create 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 5.5分区功能增强
- 深入了解MySQL 5.5分区功能增强
- mysql 分区特性
- Oracle11新特性：分区功能增强-Oracle新增复合分区
- 深度解析DDR3内存特性
- Mysql 5.1分区特性及5.5的关于分区的升级
- Oracle11新特性：分区功能增强-Oracle新增复合分区 (转载)
- 深度解析DDR3内存新特性
- css3新特性之深度解析
- 12C新特性之对表分区维护的增强
- 深入解析MySQL分区（Partition）功能
- MySQL按日期月份分区实例解析
- 深入解析MySQL分区（Partition）功能
- 深入解析MySQL分区（Partition）功能
- 深入解析MySQL分区（Partition）功能
- 深入解析MySQL分区（Partition）功能
- 深入解析MySQL分区（Partition）功能
- sybase 基本语法,存储过程,触发器
- KMP hdu-2594 Simpsons’ Hidden Talents
- 自己写非阻塞代理服务器 proxy 之C语言版
- [举重若轻]android开发环境
- Apache配置多域名
- Mysql 5.5分区特性增强深度解析
- jQuery的Autocomplete
- 在Java中利用set特性删除重复的数组元素
- 笔记1-3: 从标准输入读取命令并执行
- mysql 重启一直出现省略号"....................." 无法重启
- [举重若轻]gnome3远程桌面nx安装
- oracle的shared server模式和dedicated server模式
- usb开发笔记之三：如何编写应用程序与 USB HID 设备通讯（读写USB HID 设备）
- Struts2再爆远程代码执行漏洞
