mysql分区

一、查看mysql是否支持分区

MySQL5.1开始支持分区

1.MySQL5.6以及之前版本

show variables like '%partition%';

YES 则支持分区

2.MySQL5.7以后

show plugins;

如下则支持分区

二、分区详解

1.分区表分类：

RANGE分区、LIST分区、HASH分区、KEY分区、复合分区

2.分区表限制：

1. 分区键必须包含在表的所有主键、唯一键中。
2. MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。
3. 最大分区数： 不使用NDB存储引擎的给定表的最大可能分区数为8192（包括子分区）。如果当分区数很大，但是未达到8192时提示 Got error … from storage engine: Out of resources when opening file,可以通过增加open_files_limit系统变量的值来解决问题，当然同时打开文件的数量也可能由操作系统限制。
4. 不支持查询缓存： 分区表不支持查询缓存，对于涉及分区表的查询，它自动禁用。 查询缓存无法启用此类查询。
5. 分区的innodb表不支持外键。
6. 服务器SQL_mode影响分区表的同步复制。 主机和从机上的不同SQL_mode可能会导致sql语句; 这可能导致分区之间的数据分配给定主从位置不同，甚至可能导致插入主机上成功的分区表在从库上失败。 为了获得最佳效果，您应该始终在主机和从机上使用相同的服务器SQL模式。
7. ALTER TABLE … ORDER BY： 对分区表运行的ALTER TABLE … ORDER BY列语句只会导致每个分区中的行排序。
8. 全文索引。 分区表不支持全文索引，即使是使用InnoDB或MyISAM存储引擎的分区表。
9. 分区表无法使用外键约束。
10. Spatial columns： 具有空间数据类型（如POINT或GEOMETRY）的列不能在分区表中使用。
11. 临时表： 临时表不能分区。
12. subpartition问题： subpartition必须使用HASH或KEY分区。 只有RANGE和LIST分区可能被分区; HASH和KEY分区不能被子分区。
13. 分区表不支持mysqlcheck，myisamchk和myisampack。

3、创建分区表

1）range分区

创建表不带分区

CREATE TABLE `test_range` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
	`time` datetime NOT NULL ,
  PRIMARY KEY (`id`, `time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现有表更新表支持分区

ALTER TABLE test_range PARTITION by RANGE (YEAR(time)) (
	PARTITION p2018 VALUES LESS THAN (2019),
	PARTITION p2019 VALUES LESS THAN (2020),
	PARTITION p2020 VALUES LESS THAN (2021)
)

创建表带分区

CREATE TABLE `test_range` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(time))
(
	PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
	PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
	PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB
 );

取消分区
注意：使用REMOVE移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

ALTER TABLE test_hash REMOVE PARTITIONING;

ALTER TABLE test_hash drop PARTITIONING;

删除分区

ALTER TABLE test_range DROP PARTITION p2018, p2018;

新增分区

ALTER TABLE test_range ADD PARTITION (PARTITION p2021 VALUES LESS THAN (2021));

2）LIST分区

创建表不带分区

CREATE TABLE `test_list` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
	`time` datetime NOT NULL ,
  PRIMARY KEY (`id`, `time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现有表更新表支持分区

ALTER TABLE test_list PARTITION by LIST (YEAR(time)) (
	PARTITION p2000 VALUES IN (2000, 2001, 2002, 2003, 2004,  2005, 2006, 2007, 2008, 2009),
    PARTITION p2010 VALUES IN (2010, 2011, 2012, 2013, 2014,  2015, 2016, 2017, 2018, 2019),
    PARTITION p2020 VALUES IN (2020, 2021, 2022, 2023, 2024,  2025, 2026, 2027, 2028, 2029)
);

创建表带分区

CREATE TABLE `test_list` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST (YEAR(time))
(
	PARTITION p2000 VALUES IN (2000, 2001, 2002, 2003, 2004,  2005, 2006, 2007, 2008, 2009) ENGINE = InnoDB,
    PARTITION p2010 VALUES IN (2010, 2011, 2012, 2013, 2014,  2015, 2016, 2017, 2018, 2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES IN (2020, 2021, 2022, 2023, 2024,  2025, 2026, 2027, 2028, 2029) ENGINE = InnoDB
 );

取消分区
注意：使用REMOVE移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

ALTER TABLE test_hash REMOVE PARTITIONING;

ALTER TABLE test_hash drop PARTITIONING;

删除分区

ALTER TABLE test_list DROP PARTITION p2000 , p2010;

新增分区

ALTER TABLE test_list ADD PARTITION (PARTITION p2020 VALUES IN (2020, 2021, 2022, 2023, 2024,  2025, 2026, 2027, 2028, 2029));

3）HASH分区

创建表不带分区

CREATE TABLE `test_hash` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
	`time` datetime NOT NULL ,
  PRIMARY KEY (`id`, `time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现有表更新表支持分区

ALTER TABLE test_hash PARTITION by HASH (YEAR(time)) PARTITIONS 4;

创建表带分区

CREATE TABLE `test_hash` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (YEAR(time))
PARTITIONS 4;

取消分区
注意：使用REMOVE移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

ALTER TABLE test_hash REMOVE PARTITIONING;

ALTER TABLE test_hash drop PARTITIONING;

删除分区(实质是合并分区)

Alter TABLE test_hash coalesce partition 1;

新增分区

ALTER TABLE test_hash add PARTITION partitions 4;

4）线性HASH(LINEAR HASH)分区

LINEAR HASH和HASH的唯一区别就是

PARTITION BY LINEAR HASH

5）key分区

key分区和hash分区基本一致

创建表不带分区

CREATE TABLE `test_key` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
	`time` datetime NOT NULL ,
  PRIMARY KEY (`id`, `time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现有表更新表支持分区

ALTER TABLE test_key PARTITION by KEY (YEAR(time)) PARTITIONS 4;

创建表带分区

CREATE TABLE `test_key` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (YEAR(time))
PARTITIONS 4;

取消分区
注意：使用REMOVE移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

ALTER TABLE test_key REMOVE PARTITIONING;

ALTER TABLE test_key drop PARTITIONING;

删除分区(实质是合并分区)

Alter TABLE test_key coalesce partition 1;

新增分区

ALTER TABLE test_key add PARTITION partitions 4;

6）线性KEY(LINEAR KEY)分区

LINEAR KEY和KEY的唯一区别就是

PARTITION BY LINEAR KEY