之前ITJS有提到过：MySQL 5.5 分区之truncate分区详解，该篇文章我们主要介绍一下MySQL 5.5 分区之高级分区实例及性能分析，接下来我们就开始一一介绍这部分内容。
为了帮助你更好地掌握新功能，我们再来看一个更高级一点的例子，对于比较务实的读者更有意义，下面是表的定义脚本：
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)
和上面的例子不同，这个例子更好理解，第一个分区用来存储雇佣于1990年以前的女职员，第二个分区存储股用于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。最后一个分区是控制情况。
看完后你可能要问，我怎么知道某一行存储在那个分区中的？有两个办法，第一个办法是使用与分区定义相同的条件作为查询条件进行查询。
SELECT CASE WHEN gender = 'F' AND hire_date < '1990-01-01' THEN 'p1' WHEN gender = 'F' AND hire_date < '2000-01-01' THEN 'p2' WHEN gender = 'F' AND hire_date < '2999-01-01' THEN 'p3' WHEN gender = 'M' AND hire_date < '1990-01-01' THEN 'p4' WHEN gender = 'M' AND hire_date < '2000-01-01' THEN 'p5' WHEN gender = 'M' AND hire_date < '2999-01-01' THEN 'p6' ELSE 'p7' END as p, COUNT(*) AS rows FROM employees GROUP BY p; +------+-------+ | p | rows | +------+-------+ | p1 | 66212 | | p2 | 53832 | | p3 | 7 | | p4 | 98585 | | p5 | 81382 | | p6 | 6 | +------+-------+
如果表是MyISAM或ARCHIVE，你可以信任由INFORMATION_SCHEMA提供的统计信息。
SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='employees'; +------+------------------+-------------------+------------+ | part | expr | descr | table_rows | +------+------------------+-------------------+------------+ | p01 | gender,hire_date | 'F','1990-01-01' | 66212 | | p02 | gender,hire_date | 'F','2000-01-01' | 53832 | | p03 | gender,hire_date | 'F',MAXVALUE | 7 | | p04 | gender,hire_date | 'M','1990-01-01' | 98585 | | p05 | gender,hire_date | 'M','2000-01-01' | 81382 | | p06 | gender,hire_date | 'M',MAXVALUE | 6 | | p07 | gender,hire_date | MAXVALUE,MAXVALUE | 0 | +------+------------------+-------------------+------------+
如果存储引擎是InnoDB，上面的值就是一个近似值，如果你需要确切的值，那你就不能信任它们。
另一个问题是它的性能，这些增强触发了分区修整吗？答案毫不含糊，是的。与MySQL 5.1有所不同，在5.1中日期分区只能与两个函数工作，在MySQL 5.5中，任何使用了COLUMNS关键字定义的分区都可以使用分区修整，下面还是测试一下吧。
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
它和复合索引的算法一样，如果你的条件指的是索引最左边的部分，MySQL将会使用它。与此类似，如果你的条件指的是分区定义最左边的部分，MySQL将会尽可能修整。它和复合索引一起出现，如果你只使用最右边的条件，分区修整不会工作。
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
如果不用分区定义的第一部分，使用分区定义的第二部分，那么将会发生全表扫描，在设计分区和编写查询时要紧记这一条。
关于MySQL 5.5 分区之高级分区实例及性能分析的知识就介绍到这里了，希望本次的介绍能够对您有所收获！
原文出处：http://lujia35.iteye.com/blog/718899。