分区是把双刃剑，用得好效率高，用不好被坑爹。

squall

——《MySQL INNODB技术内幕》姜承尧

今天有人说到分区，特注明一下注意事项，MySQL和Oracle这块应该差不多，如果采用分区，表设计上一定要斟酌。

这是我的表结构：

1. mysql> show create table p1\G;
   
2. *************************** 1. row ***************************
   
3. Table: p1
   
4. Create Table: CREATE TABLE `p1` (
   
5. `id` int(11) NOT NULL,
   
6. `date` datetime NOT NULL,
   
7. PRIMARY KEY (`id`)
   
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
   
9. 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)

1. mysql> explain partitions select * from p1 where (`date` between '2009-1-1' and '2009-12-31') and name ='zhangsan';
   
2. +----+-------------+-------+------------+------+-----------+-------------------------+
   
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra
   
4. +----+-------------+-------+------------+------+----+--------------------------+
   
5. | 1 | SIMPLE | p1 | p0 | ref | IX_name | IX_name | 12 | const | 1 | Using where; Using index |
   
6. +----+-------------+-------+------------+------+-------------------------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. mysql> explain partitions select * from p1 where (`date` between '2010-1-1' and '2010-12-31') and name ='lisi';
   
10. +----+-------------+-------+------------+------+-------+--------------------------+
    
11. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra
    
12. +----+-------------+-------+------------+------+------+--------------------------+
    
13. | 1 | SIMPLE | p1 | p1 | ref | IX_name | IX_name | 12 | const | 1 | Using where; Using index |
    
14. +----+-------------+-------+------------+------+-------------------------------+
    
15. 1 row in set (0.00 sec)

复制代码

注：使用分区，where后面的字段必须是分区字段，这样才会使用到分区，这里09年的使用的是分区p0，10年的使用的是分区p1，

那么如果我去掉`date`字段，直接写name='zhaoqi'，看行不行？

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

复制代码

扫描了全部的分区，分区在这里没有一点意义，反而拖累了性能。

原因如下：