-
--检查是否支持 partition
红线部分，支持分区
mysql> select plugin_name, plugin_version, plugin_status from information_schema.plugins;
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.17 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.17-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
mysql> show variables like 'have_symlink';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink | YES |
+---------------+-------+
innodb_file_per_table 将每个存储引擎为 InnoDB 的表进行独立存储: have_symlink 是对于存储引擎为 MyISAM 的表，可以设置数据或索引文件存储到不同的位置，是以软链接的形式在原配置目录data中连接到具体的存储文件位置。若不启用 have_symlink , 创建 MyISAM 的表指定的数据和索引位置将无效, 默认存储在 %datadir% 中。
对于不同的版本和不同的操作系统, 创建表指定的存储路径也有不同限制，如下为其中的一个 bug ：
Table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored (see Bug #32091)
On Windows, the DATA DIRECTORY and INDEX DIRECTORY options are not supported for individual partitions or subpartitions of MyISAM tables. However, you can use DATA DIRECTORY for individual partitions or subpartitions of InnoDB tables.
启用变量,重启服务
# vi /etc/my.cnf.
[mysqld]
innodb_file_per_table = 1
symbolic-links = 1
创建分区文件夹, 用于分区测试
mkdir -p /opt/mysql/{idx0,idx1,idx2,idx3,idx4,idx5,data0,data1,data2,data3,data4,data5}
chown mysql:mysql /opt/mysql /opt/mysql/{idx0,idx1,idx2,idx3,idx4,idx5,data0,data1,data2,data3,data4,data5}
测试创建表指定另外的存储目录
--测试创建表指定另外的存储目录(文件 <table_name>.ibd 已经包含数据页和索引页)
CREATE TABLE tab_innodb ( id INT NOT NULL, adddate DATE NOT NULL )
ENGINE=InnoDB DATA DIRECTORY = '/opt/mysql/data0';
CREATE TABLE tab_myisam ( id INT NOT NULL, adddate DATE NOT NULL )
ENGINE=MyISAM DATA DIRECTORY = '/opt/mysql/data0' INDEX DIRECTORY = '/opt/mysql/idx0';
若未启用 have_symlink 的话, 存储引擎为 MyISAM 的表将报错：
<DATA DIRECTORY> option ignored
<INDEX DIRECTORY> option ignored
/*
DROP TABLE tab_innodb;
DROP TABLE tab_myisam;
*/
=======================================================
=======================================================
分区创建：
对于 InnoDB ，可以把 “INDEX DIRECTORY”去掉；当前默认为存储引擎为 InnoDB 。
------------------------------------------------------------------
-- 范围分区（RANGE）
------------------------------------------------------------------
CREATE TABLE tab_rangpart ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY RANGE(YEAR(adddate)) (
PARTITION p0 VALUES LESS THAN (2015),
PARTITION p1 VALUES LESS THAN (2016),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
CREATE TABLE tab_rangpart ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY RANGE (YEAR(adddate)) (
PARTITION p0 VALUES LESS THAN (2015)
DATA DIRECTORY = '/opt/mysql/data0'
INDEX DIRECTORY = '/opt/mysql/idx0',
PARTITION p1 VALUES LESS THAN (2016)
DATA DIRECTORY = '/opt/mysql/data1'
INDEX DIRECTORY = '/opt/mysql/idx1',
PARTITION p2 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/opt/mysql/data2'
INDEX DIRECTORY = '/opt/mysql/idx2'
);
------------------------------------------------------------------
-- 列表分区（LIST）
------------------------------------------------------------------
CREATE TABLE tab_listpart ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY LIST(MONTH(adddate))(
PARTITION p0 VALUES IN (1,2,3),
PARTITION p1 VALUES IN (4,5,6),
PARTITION p2 VALUES IN (7,8,9),
PARTITION p3 VALUES IN (10,11,12)
);
CREATE TABLE tab_listpart ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY LIST(MONTH(adddate))(
PARTITION p0 VALUES IN (1,2,3)
DATA DIRECTORY = '/opt/mysql/data0'
INDEX DIRECTORY = '/opt/mysql/idx0',
PARTITION p1 VALUES IN (4,5,6)
DATA DIRECTORY = '/opt/mysql/data1'
INDEX DIRECTORY = '/opt/mysql/idx1',
PARTITION p2 VALUES IN (7,8,9)
DATA DIRECTORY = '/opt/mysql/data2'
INDEX DIRECTORY = '/opt/mysql/idx2',
PARTITION p3 VALUES IN (10,11,12)
DATA DIRECTORY = '/opt/mysql/data3'
INDEX DIRECTORY = '/opt/mysql/idx3'
);
------------------------------------------------------------------
-- 哈希分区（HASH）
------------------------------------------------------------------
CREATE TABLE tab_hashpart ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY HASH(id) PARTITIONS 4;
CREATE TABLE tab_hashpart ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY HASH(id) PARTITIONS 4(
PARTITION p0
DATA DIRECTORY = '/opt/mysql/data0'
INDEX DIRECTORY = '/opt/mysql/idx0',
PARTITION p1
DATA DIRECTORY = '/opt/mysql/data1'
INDEX DIRECTORY = '/opt/mysql/idx1',
PARTITION p2
DATA DIRECTORY = '/opt/mysql/data2'
INDEX DIRECTORY = '/opt/mysql/idx2',
PARTITION p3
DATA DIRECTORY = '/opt/mysql/data3'
INDEX DIRECTORY = '/opt/mysql/idx3'
);
------------------------------------------------------------------
-- 键值分区（KEY）
------------------------------------------------------------------
CREATE TABLE tab_keypart ( id INT NOT NULL PRIMARY KEY, adddate DATE NOT NULL )
PARTITION BY KEY(id) PARTITIONS 4;
CREATE TABLE tab_keypart ( id INT NOT NULL PRIMARY KEY, adddate DATE NOT NULL )
PARTITION BY KEY (id) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/opt/mysql/data0'
INDEX DIRECTORY = '/opt/mysql/idx0',
PARTITION p1
DATA DIRECTORY = '/opt/mysql/data1'
INDEX DIRECTORY = '/opt/mysql/idx1',
PARTITION p2
DATA DIRECTORY = '/opt/mysql/data2'
INDEX DIRECTORY = '/opt/mysql/idx2',
PARTITION p3
DATA DIRECTORY = '/opt/mysql/data3'
INDEX DIRECTORY = '/opt/mysql/idx3'
);
------------------------------------------------------------------
-- 子分区
------------------------------------------------------------------
CREATE TABLE ts ( id INT , adddate DATE )
PARTITION BY RANGE( YEAR(adddate) )
SUBPARTITION BY HASH( TO_DAYS(adddate) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
CREATE TABLE ts (id INT, adddate DATE)
PARTITION BY RANGE( YEAR(adddate) )
SUBPARTITION BY HASH( TO_DAYS(adddate) )
(
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
CREATE TABLE ts (id INT, adddate DATE)
PARTITION BY RANGE( YEAR(adddate) )
SUBPARTITION BY HASH( TO_DAYS(adddate))
(
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
DATA DIRECTORY = '/opt/mysql/data0'
INDEX DIRECTORY = '/opt/mysql/idx0',
SUBPARTITION s1
DATA DIRECTORY = '/opt/mysql/data1'
INDEX DIRECTORY = '/opt/mysql/idx1'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
DATA DIRECTORY = '/opt/mysql/data2'
INDEX DIRECTORY = '/opt/mysql/idx2',
SUBPARTITION s3
DATA DIRECTORY = '/opt/mysql/data3'
INDEX DIRECTORY = '/opt/mysql/idx3'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
DATA DIRECTORY = '/opt/mysql/data4'
INDEX DIRECTORY = '/opt/mysql/idx4',
SUBPARTITION s5
DATA DIRECTORY = '/opt/mysql/data5'
INDEX DIRECTORY = '/opt/mysql/idx5'
)
);
分区管理：
创建测试分区：
-- 范围分区（RANGE）
CREATE TABLE tab ( id INT NOT NULL, adddate DATE NOT NULL )
PARTITION BY RANGE(YEAR(adddate)) (
PARTITION p0 VALUES LESS THAN (2015) DATA DIRECTORY = '/opt/mysql/data0',
PARTITION p1 VALUES LESS THAN (2016) DATA DIRECTORY = '/opt/mysql/data1',
PARTITION p2 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/opt/mysql/data2'
);
--测试数据
INSERT INTO tab VALUES(1,'2013-06-01'),(2,'2014-06-01'),(3,'2015-06-01');
INSERT INTO tab VALUES(4,'2016-06-01'),(5,'2017-06-01'),(6,'2018-06-01');
SELECT * FROM tab;
-- 查询指定分区数据
SELECT * FROM tab PARTITION (p0);
SELECT * FROM tab PARTITION (p0, p1, p2) WHERE adddate < '2016-06-01';
查看分区信息：
--查看哪些表进行了分区
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,CREATE_TIME,TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS='partitioned';
+--------------+------------+--------+------------+---------------------+-------------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_ROWS | CREATE_TIME | TABLE_COLLATION |
+--------------+------------+--------+------------+---------------------+-------------------+
| test | tab | InnoDB | 6 | 2018-03-15 14:53:16 | latin1_swedish_ci |
+--------------+------------+--------+------------+---------------------+-------------------+
--查看分区信息
SELECT PARTITION_NAME AS Name
,PARTITION_ORDINAL_POSITION AS Position
,PARTITION_METHOD AS Method
,PARTITION_EXPRESSION AS Expression
,PARTITION_DESCRIPTION AS Description
,TABLE_ROWS AS Rows
,CREATE_TIME AS CreateTime
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA='test' AND TABLE_NAME='tab';
+------+----------+--------+---------------+-------------+------+---------------------+
| Name | Position | Method | Expression | Description | Rows | CreateTime |
+------+----------+--------+---------------+-------------+------+---------------------+
| p0 | 1 | RANGE | YEAR(adddate) | 2015 | 2 | 2018-03-15 14:52:06 |
| p1 | 2 | RANGE | YEAR(adddate) | 2016 | 1 | 2018-03-15 14:52:06 |
| p2 | 3 | RANGE | YEAR(adddate) | MAXVALUE | 3 | 2018-03-15 14:52:06 |
+------+----------+--------+---------------+-------------+------+---------------------+
--查看分区文件位置
SELECT p.TABLE_SCHEMA,p.TABLE_NAME,p.PARTITION_NAME,PATH
FROM information_schema.PARTITIONS p
INNER JOIN information_schema.INNODB_SYS_TABLESPACES ist
ON ist.NAME LIKE CONCAT(p.TABLE_SCHEMA, '/', p.TABLE_NAME, '%', p.PARTITION_NAME)
INNER JOIN information_schema.INNODB_SYS_DATAFILES isdf
ON ist.SPACE = isdf.SPACE
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'tab';
+--------------+------------+----------------+------------------------------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PATH |
+--------------+------------+----------------+------------------------------------+
| test | tab | p | /opt/mysql/data5/test/tab#P#p.ibd |
| test | tab | p0 | /opt/mysql/data0/test/tab#P#p0.ibd |
| test | tab | p1 | /opt/mysql/data1/test/tab#P#p1.ibd |
| test | tab | p2 | /opt/mysql/data2/test/tab#P#p2.ibd |
| test | tab | p3 | /opt/mysql/data3/test/tab#P#p3.ibd |
+--------------+------------+----------------+------------------------------------+
分区维护：
-- 添加/重组分区：正确
ALTER TABLE tab REORGANIZE PARTITION p0 INTO (
PARTITION p VALUES LESS THAN (2014) DATA DIRECTORY = '/opt/mysql/data5',
PARTITION p0 VALUES LESS THAN (2015) DATA DIRECTORY = '/opt/mysql/data0'
);
ALTER TABLE tab REORGANIZE PARTITION p2 INTO (
PARTITION p2 VALUES LESS THAN (2017) DATA DIRECTORY = '/opt/mysql/data3' ,
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/opt/mysql/data4'
);
-- 添加/重组分区：正确
ALTER TABLE tab PARTITION BY RANGE(YEAR(adddate)) (
PARTITION p VALUES LESS THAN (2014) DATA DIRECTORY = '/opt/mysql/data5',
PARTITION p0 VALUES LESS THAN (2015) DATA DIRECTORY = '/opt/mysql/data0',
PARTITION p1 VALUES LESS THAN (2016) DATA DIRECTORY = '/opt/mysql/data1',
PARTITION p2 VALUES LESS THAN (2017) DATA DIRECTORY = '/opt/mysql/data2',
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/opt/mysql/data3'
);
-- 添加分区：错误 (若分区边界无 MAXVALUE, 则执行正确)
ALTER TABLE tab ADD PARTITION (PARTITION p5 VALUES LESS THAN (2018) DATA DIRECTORY = '/opt/mysql/data5');
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
-- 合并分区
ALTER TABLE tab REORGANIZE PARTITION p2,p3 INTO (PARTITION p2 VALUES LESS THAN (MAXVALUE));
ALTER TABLE tab REORGANIZE PARTITION p,p0 INTO (PARTITION p0 VALUES LESS THAN (2015));
-- 清空指定分区数据
ALTER TABLE tab TRUNCATE PARTITION p1;
-- 删除分区及数据
ALTER TABLE tab DROP PARTITION p;
-- 重建分区
ALTER TABLE tab REBUILD PARTITION p0;
-- 分析分区
ALTER TABLE tab ANALYZE PARTITION p0;
-- 优化分区
ALTER TABLE tab OPTIMIZE PARTITION p2;
-- 检查分区是否错误
ALTER TABLE tab CHECK PARTITION p2;
-- 修复损坏分区
ALTER TABLE tab REPAIR PARTITION p2;
-- HASH, KEY 分区，减少4个分区
ALTER TABLE tab COALESCE PARTITION 4;
-- 切换分区
CREATE TABLE tab2 AS SELECT * FROM tab WHERE 1<>1;
ALTER TABLE tab EXCHANGE PARTITION p2 WITH TABLE tab2;
drop table tab2;
-- 移除表的分区
ALTER TABLE tab REMOVE PARTITIONING;
-- 升级表分区
ALTER TABLE tab UPGRADE PARTITIONING;
981
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
