Mysql 5.5分区特性增强深度解析

 

 

 原文链接：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