上次我們介紹了：MySQL 5.5 分區(qū)之truncate分區(qū)詳解，本文我們主要介紹一下MySQL 5.5 分區(qū)之高級(jí)分區(qū)實(shí)例及性能分析，接下來我們就開始一一介紹這部分內(nèi)容。

為了幫助你更好地掌握新功能，我們?cè)賮砜匆粋€(gè)更高級(jí)一點(diǎn)的例子，對(duì)于比較務(wù)實(shí)的讀者更有意義，下面是表的定義腳本：

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)  

和上面的例子不同，這個(gè)例子更好理解，***個(gè)分區(qū)用來存儲(chǔ)雇傭于1990年以前的女職員，第二個(gè)分區(qū)存儲(chǔ)股用于1990-2000年之間的女職員，第三個(gè)分區(qū)存儲(chǔ)所有剩下的女職員。對(duì)于分區(qū)p04到p06，我們策略是一樣的，只不過存儲(chǔ)的是男職員。***一個(gè)分區(qū)是控制情況。

看完后你可能要問，我怎么知道某一行存儲(chǔ)在那個(gè)分區(qū)中的？有兩個(gè)辦法，***個(gè)辦法是使用與分區(qū)定義相同的條件作為查詢條件進(jìn)行查詢。

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提供的統(tǒng)計(jì)信息。

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 |    
+------+------------------+-------------------+------------+  

如果存儲(chǔ)引擎是InnoDB，上面的值就是一個(gè)近似值，如果你需要確切的值，那你就不能信任它們。

另一個(gè)問題是它的性能，這些增強(qiáng)觸發(fā)了分區(qū)修整嗎？答案毫不含糊，是的。與MySQL 5.1有所不同，在5.1中日期分區(qū)只能與兩個(gè)函數(shù)工作，在MySQL 5.5中，任何使用了COLUMNS關(guān)鍵字定義的分區(qū)都可以使用分區(qū)修整，下面還是測(cè)試一下吧。

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  

使用定義***個(gè)分區(qū)的條件，我們獲得了一個(gè)非常優(yōu)化的查詢，不僅如此，部分條件也將從分區(qū)修整中受益。

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 

它和復(fù)合索引的算法一樣，如果你的條件指的是索引最左邊的部分，MySQL將會(huì)使用它。與此類似，如果你的條件指的是分區(qū)定義最左邊的部分，MySQL將會(huì)盡可能修整。它和復(fù)合索引一起出現(xiàn)，如果你只使用最右邊的條件，分區(qū)修整不會(huì)工作。

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 

如果不用分區(qū)定義的***部分，使用分區(qū)定義的第二部分，那么將會(huì)發(fā)生全表掃描，在設(shè)計(jì)分區(qū)和編寫查詢時(shí)要緊記這一條。

關(guān)于MySQL 5.5 分區(qū)之高級(jí)分區(qū)實(shí)例及性能分析的知識(shí)就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

原文出處：http://lujia35.iteye.com/blog/718899。

【編輯推薦】

1. MySQL 5.5 分區(qū)之多列分區(qū)詳解
2. MySQL 5.5 分區(qū)之非整數(shù)列分區(qū)詳解
3. MySQL數(shù)據(jù)庫MVCC多版本并發(fā)控制簡(jiǎn)介
4. 適合初學(xué)者的MySQL學(xué)習(xí)筆記之MySQL管理心得
5. MySQL數(shù)據(jù)庫將多條記錄的單個(gè)字段合并成一條記錄