MySQL分区简介

发布时间：2022-04-01 12:22:11 所属栏目：MySql教程来源：互联网

导读：不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分 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) par

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分
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]> select * from employees;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 2 | Tom   | Carl   | 1970-01-01 | 9999-12-31 |       10 |        1 |
| 2 | Lily | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |
| 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |
| 4 | Bill | Jones | 1970-01-01 | 9999-12-31 |       20 |       15 |
| 5 | Jill | Deco   | 1970-01-01 | 9999-12-31 |       30 |       12 |
| 1 | John | Terry | 1970-01-01 | 9999-12-31 |       10 |      100 |
| 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 |       30 |       20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)

MariaDB [test]> show create table employeesG
*************************** 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)

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MySQL之日志管制	如何使用mysql索引
Anemometer图形化显示	mysql数据库explain命