MariaDB社区

标题: 分区是把双刃剑,用得好效率高,用不好被坑爹。 [打印本页]

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



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

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

这是我的表结构:
  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. mysql> explain partitions select * from p1 where (`date` between '2010-1-1' and '2010-12-31') and name ='lisi';
  9. +----+-------------+-------+------------+------+-------+--------------------------+
  10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra
  11. +----+-------------+-------+------------+------+------+--------------------------+
  12. | 1 | SIMPLE | p1 | p1 | ref | IX_name | IX_name | 12 | const | 1 | Using where; Using index |
  13. +----+-------------+-------+------------+------+-------------------------------+
  14. 1 row in set (0.00 sec)
复制代码
注:使用分区,where后面的字段必须是分区字段,这样才会使用到分区,这里09年的使用的是分区p010年的使用的是分区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