MariaDB社区

标题: 分区是把双刃剑，用得好效率高，用不好被坑爹。 [打印本页]
作者: squall    时间: 2012-12-19 11:02
标题: 分区是把双刃剑，用得好效率高，用不好被坑爹。
本帖最后由 squall 于 2012-12-19 11:10 编辑



——《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)

  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)
复制代码
扫描了全部的分区，分区在这里没有一点意义，反而拖累了性能。

原因如下：




欢迎光临 MariaDB社区 (http://123.56.88.72/) Powered by Discuz! X3.2