分區是把雙刃劍，用得好效率高，用不好被坑爹。

650) this.width=650;" border="0" src="http://img1.51cto.com/attachment/201212/102038943.jpg" alt="" />

                                                                                      ——《MySQL INNODB技術內幕》薑承堯

這是我的表結構：

 

  
mysql> show create table p1\G; 
  
*************************** 1. row *************************** 
  
       Table: p1 
  
Create Table: CREATE TABLE `p1` ( 
  
  `id` int(11) NOT NULL, 
  
  `date` datetime NOT NULL, 
  
  PRIMARY KEY (`id`) 
  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  
1 row in set (0.00 sec) 
 

我如果對`date`欄位進行分區，你說，會成功嗎？呵呵，很遺憾，不會的。

 

  
mysql> alter table p1 partition by range columns(date)( 
  
partition p0 values less than ('2010-01-01'),  
  
partition p1 values less than ('2011-01-01'),  
  
partition p2 values less than ('2012-01-01'),  
  
PARTITION p3 VALUES LESS THAN MAXVALUE);     
  
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
 

答案已經告訴你了，這個欄位必須是主鍵。

 

  
mysql> alter table p1 drop primary key,add primary key(`id`,`date`); 
  
Query OK, 0 rows affected (0.03 sec) 
  
Records: 0  Duplicates: 0  Warnings: 0 
  
 
  
mysql> alter table p1 partition by range columns(date)( 
  
partition p0 values less than ('2010-01-01'),  
  
partition p1 values less than ('2011-01-01'),  
  
partition p2 values less than ('2012-01-01'),  
  
PARTITION p3 VALUES LESS THAN MAXVALUE); 
  
Query OK, 0 rows affected (0.05 sec) 
  
Records: 0  Duplicates: 0  Warnings: 0 
 

 

  
mysql> show create table p1\G; 
  
*************************** 1. row *************************** 
  
       Table: p1 
  
Create Table: CREATE TABLE `p1` ( 
  
  `id` int(11) NOT NULL, 
  
  `date` datetime NOT NULL, 
  
  PRIMARY KEY (`id`,`date`)QFVW 
  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  
/*!50500 PARTITION BY RANGE  COLUMNS(`date`) 
  
(PARTITION p0 VALUES LESS THAN ('2010-01-01') ENGINE = InnoDB, 
  
 PARTITION p1 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB, 
  
 PARTITION p2 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB, 
  
 PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ 
  
1 row in set (0.01 sec) 
 

我現在要增加一個欄位name並建立索引，然後插入幾條記錄測試

 

  
mysql> alter table p1 add name varchar(10) not null; 
  
Query OK, 0 rows affected (0.04 sec) 
  
Records: 0  Duplicates: 0  Warnings: 0 
  
 
  
mysql> alter table p1 add index IX_name(name); 
  
Query OK, 0 rows affected (0.07 sec) 
  
Records: 0  Duplicates: 0  Warnings: 0 
  
 
  
mysql> insert into p1 values(1,'2009-10-1','zhangsan'); 
  
Query OK, 1 row affected (0.05 sec) 
  
 
  
mysql> insert into p1 values(2,'2010-05-05','lisi');    
  
Query OK, 1 row affected (0.01 sec) 
  
 
  
mysql> insert into p1 values(3,'2011-07-08','wangwu'); 
  
Query OK, 1 row affected (0.00 sec) 
  
 
  
mysql> insert into p1 values(4,'2012-04-27','xuliu');  
  
Query OK, 1 row affected (0.00 sec) 
  
 
  
mysql> insert into p1 values(5,'2013-02-14','zhaoqi'); 
  
Query OK, 1 row affected (0.01 sec) 
  
 
  
mysql> select * from p1; 
  
+----+---------------------+----------+ 
  
| id | date                | name     | 
  
+----+---------------------+----------+ 
  
|  1 | 2009-10-01 00:00:00 | zhangsan | 
  
|  2 | 2010-05-05 00:00:00 | lisi     | 
  
|  3 | 2011-07-08 00:00:00 | wangwu   | 
  
|  4 | 2012-04-27 00:00:00 | xuliu    | 
  
|  5 | 2013-02-14 00:00:00 | zhaoqi   | 
  
+----+---------------------+----------+ 
  
5 rows in set (0.01 sec) 
 

 

  
mysql> explain partitions select * from p1 where (`date` between '2009-1-1' and '2009-12-31') and name ='zhangsan';      
  
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | Extra                    | 
  
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
|  1 | SIMPLE      | p1    | p0         | ref  | IX_name       | IX_name | 12      | const |    1 | Using where; Using index | 
  
 
  
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
1 row in set (0.00 sec) 
  
 
  
mysql> explain partitions select * from p1 where (`date` between '2010-1-1' and '2010-12-31') and name ='lisi';   
  
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | Extra                    | 
  
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
|  1 | SIMPLE      | p1    | p1         | ref  | IX_name       | IX_name | 12      | const |    1 | Using where; Using index | 
  
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
1 row in set (0.00 sec) 
 

註：使用分區，where後面的欄位必須是分區欄位，這樣才會使用到分區，這裡09年的使用的是分區p0，10年的使用的是分區p1，

那麼如果我去掉`date`欄位，直接寫name='zhaoqi'，看行不行？

 

  
mysql> explain partitions select * from p1 where name='zhaoqi';                                            
  
+----+-------------+-------+-------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
 
  
| id | select_type | table | partitions  | type | possible_keys | key     | key_len | ref   | rows | Extra                    | 
  
 
  
+----+-------------+-------+-------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
 
  
|  1 | SIMPLE      | p1    | p0,p1,p2,p3 | ref  | IX_name       | IX_name | 12      | const |    2 | Using where; Using index | 
  
 
  
+----+-------------+-------+-------------+------+---------------+---------+---------+-------+------+--------------------------+ 
  
1 row in set (0.01 sec) 
 

掃描了全部的分區，分區在這裡沒有一點意義，反而拖累了效能。

原因如下：

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1T94B338-1.jpg" alt="" />

 

本文出自 “賀春暘的技術專欄” 部落格，請務必保留此出處http://hcymysql.blog.51cto.com/5223301/1094699