7. Partitioning

7. Partitioning
上一頁	第 5 章 DDL - Data Definition Language	下一頁

18.5.1. Partitioning Keys, Primary Keys, and Unique Keys
This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:


SQL code:
mysql> create table tx (
    ->     id int not null ,
    ->     info_time date,
    ->     primary key(id,info_time)
    -> )
    -> PARTITION BY RANGE(info_time div 100)
    -> (
    ->     PARTITION p_2008_11 VALUES LESS THAN (200812),
    ->     PARTITION p_2008_12 VALUES LESS THAN (200901),
    ->     PARTITION p_2009_01 VALUES LESS THAN (200902),
    ->     PARTITION p_2009_02 VALUES LESS THAN (200903),
    ->     PARTITION p_2009_03 VALUES LESS THAN (200904),
    ->     PARTITION p_2009_04 VALUES LESS THAN (200905),
    ->     PARTITION p_catch_all VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql>

	
mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

7.1. EXPLAIN PARTITIONS

EXPLAIN PARTITIONS

		
mysql> EXPLAIN PARTITIONS SELECT * FROM users\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: p0,p1,p2,p3,p4,p5,p6
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: 
1 row in set (0.03 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM users WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: p0,p1,p2,p3,p4,p5,p6
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set (0.00 sec)

7.2. SHOW CREATE TABLE

SHOW CREATE TABLE

		
mysql> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (id,username)
PARTITIONS 7 */
1 row in set (0.00 sec)

7.3. range

		
CREATE TABLE t1 (
    year_col  INT,
    some_data INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2002),
    PARTITION p4 VALUES LESS THAN (2006),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

e.g.2

				
CREATE TABLE rc (
    a INT NOT NULL,
    b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (10,5),
    PARTITION p1 VALUES LESS THAN (20,10),
    PARTITION p2 VALUES LESS THAN (MAXVALUE,15),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

		
CREATE TABLE part_tab
(
	c1 int default NULL,
	c2 varchar(30) default NULL,
	c3 date default NULL

) engine=myisam
PARTITION BY RANGE (year(c3)) (
      PARTITION p0 VALUES LESS THAN (2000) , 
      PARTITION p1 VALUES LESS THAN (2001) ,
      PARTITION p2 VALUES LESS THAN (2002) , 
      PARTITION p3 VALUES LESS THAN (2003) ,
      PARTITION p4 VALUES LESS THAN (2004) , 
      PARTITION p12 VALUES LESS THAN (2012),
      PARTITION p13 VALUES LESS THAN MAXVALUE 
);

7.4. list

				
CREATE TABLE client_firms (
    id   INT,
    name VARCHAR(35)
)
PARTITION BY LIST (id) (
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);

				
CREATE TABLE lc (
    a INT NULL,
    b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
);

				
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/var/appdata/96/data'
    INDEX DIRECTORY = '/var/appdata/96/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/var/appdata/97/data'
    INDEX DIRECTORY = '/var/appdata/97/idx',
  PARTITION p2000 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/var/appdata/98/data'
    INDEX DIRECTORY = '/var/appdata/98/idx'
);

7.5. HASH

		
CREATE TABLE `test` (
  `userid` int(10) unsigned NOT NULL auto_increment,
  `username` int(10) unsigned NOT NULL DEFAULT '0',
  `password` int(10) unsigned NOT NULL DEFAULT '0',
 
  primary key (`userid`),
  KEY `userid` (`username`)
) ENGINE=InnoDB
PARTITION BY HASH(userid)
PARTITIONS 8;

		
CREATE TABLE stuff (
	id INT AUTO_INCREMENT, 
	name varchar(50),
	password varchar(50),
	created DATE, 
	PRIMARY KEY (id, created)
)
PARTITION BY RANGE(year(created)) (
	PARTITION p0 VALUES LESS THAN (2010), 
	PARTITION p1 VALUES LESS THAN (2012), 	
	PARTITION p2 VALUES LESS THAN MAXVALUE
);

更好的方法

CREATE TABLE stuff (
	id INT AUTO_INCREMENT, 
	name varchar(50),
	password varchar(50),
	created DATE, 
	PRIMARY KEY (id, created)
)
PARTITION BY HASH (year(created)) PARTITIONS 10;

我們演示一下	

mysql> CREATE TABLE stuff (
    -> id INT AUTO_INCREMENT, 
    -> name varchar(50),
    -> password varchar(50),
    -> created DATE, 
    -> PRIMARY KEY (id, created)
    -> )
    -> PARTITION BY HASH (year(created)) PARTITIONS 10;
Query OK, 0 rows affected (0.08 sec)


mysql> insert into stuff (name,password,created) values('neo','test','2010-10-1');
Query OK, 1 row affected (0.06 sec)

mysql> insert into stuff (name,password,created) values('neo1','test','2012-2-1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stuff (name,password,created) values('neo2','test','2012-3-5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stuff (name,password,created) values('neo4','test','2011-1-5');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   partition_name part,
    ->   partition_expression expr,
    ->   partition_description descr,
    ->   table_rows
    -> FROM
    ->   INFORMATION_SCHEMA.partitions
    -> WHERE
    ->   TABLE_SCHEMA = schema()
    ->   AND TABLE_NAME='stuff';
+------+---------------+-------+------------+
| part | expr          | descr | table_rows |
+------+---------------+-------+------------+
| p0   | year(created) | NULL  |          1 |
| p1   | year(created) | NULL  |          1 |
| p2   | year(created) | NULL  |          2 |
| p3   | year(created) | NULL  |          0 |
| p4   | year(created) | NULL  |          0 |
| p5   | year(created) | NULL  |          0 |
| p6   | year(created) | NULL  |          0 |
| p7   | year(created) | NULL  |          0 |
| p8   | year(created) | NULL  |          0 |
| p9   | year(created) | NULL  |          0 |
+------+---------------+-------+------------+
10 rows in set (0.02 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM stuff WHERE created='2011-01-05'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stuff
   partitions: p1
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
1 row in set (0.08 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM stuff WHERE created='2012-03-05'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stuff
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

7.5.1. LINEAR HASH

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

7.6. 添加分區

mysql 5.5+

		
CREATE TABLE expenses (
  expense_date DATE NOT NULL,
  category VARCHAR(30),
  amount DECIMAL (10,3)
);

ALTER TABLE expenses
PARTITION BY LIST COLUMNS (category)
(
  PARTITION p01 VALUES IN ( 'lodging', 'food'),
  PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),
  PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),
  PARTITION p04 VALUES IN ( 'communications'),
  PARTITION p05 VALUES IN ( 'fees')
);

		
SELECT
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = schema()
  AND TABLE_NAME='employees';

		
select
  partition_name part,
  partition_expression expr,
  from_seconds(partition_description) descr,
  table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
    TABLE_SCHEMA = 'test'
    AND TABLE_NAME='t2';

		
/* 在MySQL 5.1中*/
CREATE TABLE t2
(
  dt DATE
)
PARTITION BY RANGE (TO_DAYS(dt))
(
  PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),
  PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),
  PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),
  PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */



 /*在MySQL 5.5中*/
CREATE TABLE t2
(
  dt DATE
)
PARTITION BY RANGE COLUMNS (dt)
(
  PARTITION p01 VALUES LESS THAN ('2007-01-01'),
  PARTITION p02 VALUES LESS THAN ('2008-01-01'),
  PARTITION p03 VALUES LESS THAN ('2009-01-01'),
  PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p01 VALUES LESS THAN ('2007-01-01') ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */

7.7. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html

上一頁	上一級	下一頁
6. Trigger	起始頁	第 6 章 SQL Statement Syntax