不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分 mysql> create table t1( -> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2)) partition by hash(col3) partitions 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> create table t2( -> col1 int null, -> col2 date null, -> col3 int null, -> col4 int null -> ) engine=innodb -> partition by hash(col3) -> partitions 4; Query OK, 0 rows affected (0.40 sec) mysql> create table t3( -> col1 int null, -> col2 date null, -> col3 int null, -> col4 int null, -> key (col4) -> ) engine=innodb -> partition by hash(col3) -> partitions 4; Query OK, 0 rows affected (0.23 sec)
--查看数据库是否支持分区 MariaDB [test]> show plugins; +-------------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +-------------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | ..... | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +-------------------------------+----------+--------------------+---------+---------+
--范围分区 MariaDB [test]> CREATE TABLE members ( -> firstname VARCHAR(25) NOT NULL, -> lastname VARCHAR(25) NOT NULL, -> username VARCHAR(16) NOT NULL, -> email VARCHAR(35), -> joined DATE NOT NULL -> ) -> PARTITION BY RANGE COLUMNS(joined) ( -> PARTITION p0 VALUES LESS THAN ('1960-01-01'), -> PARTITION p1 VALUES LESS THAN ('1970-01-01'), -> PARTITION p2 VALUES LESS THAN ('1980-01-01'), -> PARTITION p3 VALUES LESS THAN ('1990-01-01'), -> PARTITION p4 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.45 sec)
MariaDB [test]> 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 NOT NULL, -> store_id INT NOT NULL -> ) -> PARTITION BY RANGE (store_id) ( -> PARTITION p0 VALUES LESS THAN (6), -> PARTITION p1 VALUES LESS THAN (11), -> PARTITION p2 VALUES LESS THAN (16), -> PARTITION p3 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.49 sec)
MariaDB [test]> show create table employees/G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job_code` int(11) NOT NULL, `store_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)
按照年进行分区 mysql> create table sales( -> money int unsigned not null, -> date datetime -> ) engine=innodb -> partition by range (year(date)) ( -> partition p2008 values less than (2009), -> partition p2009 values less than (2010), -> partition p2010 values less than (2011) -> ); Query OK, 0 rows affected (0.31 sec)
优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择 下面这个例子中的分区创建有问题，在分区扫描的时候会扫描多个分区 按照每年每月来进行分区 mysql> create table sales2( -> money int unsigned not null, -> date datetime -> ) engine=innodb -> partition by range (year(date)*100+month(date)) ( -> partition p201001 values less than (201002), -> partition p201002 values less than (201003), -> partition p201003 values less than (201004) -> ); Query OK, 0 rows affected (0.20 sec)
