分区是把双刃剑，用得好效率高，用不好被坑爹。
本帖最后由 squall 于 2012-12-19 11:10 编辑——《MySQL INNODB技术内幕》姜承尧
今天有人说到分区，特注明一下注意事项，MySQL和Oracle这块应该差不多，如果采用分区，表设计上一定要斟酌。
这是我的表结构：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: 0Duplicates: 0Warnings: 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: 0Duplicates: 0Warnings: 0
mysql> show create table p1\G;*************************** 1. row *************************** Table: p1Create 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 RANGECOLUMNS(`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: 0Duplicates: 0Warnings: 0
mysql> alter table p1 add index IX_name(name);Query OK, 0 rows affected (0.07 sec)Records: 0Duplicates: 0Warnings: 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)扫描了全部的分区，分区在这里没有一点意义，反而拖累了性能。
原因如下：
页:
[1]