归档表
不想保留旧数据并希望删除它，该如何做呢？
如果想删除一个月前最后一次访问的所有行。如果表很小（<10000行），则可以直接使用以下方法：
DELETE FROM <TABLE> WHERE last_accessed < DATE_ADD(NOW(),INTERVAL -1 MONTH)
如果表很大，会怎样？InnoDB创建了一个UNDO日志来恢复失效的事务，因此，所有被删除的行都会被保存在UNDO日志空间中，以便在DELETE语句的执行中止时恢复它们。不幸的是，如果DELETE语句在执行时被中止，InnoDB将从UNDO日志空间将行复制到表中，这可能使表无法访问。
为了克服这种弊端，可以限制删除的行数然后提交事务，循环做这个操作，直到删除所有不需要的行。
以下是一个伪代码示例：
WHILE count<=0:
DELETE FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH) LIMIT 10000;
count=SELECT COUNT(*) FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH);
如果last_accessed上没有索引，则会锁定该表。在这种情况下，需要找到要删除的行的主键，并且基于主键删除这些行。
下面是伪代码（假设id是主键）：
WHILE count<=0:
SELECT id FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH) LIMIT 10000;
DELETE FROM <TABLE> WHERE id IN ('ids from above statement');
count=SELECT COUNT(*) FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH);
可以使用Percona的pt-archiver工具来代替编写删除行的代码，不仅可以完成上面的任务，这个工具还提供了许多其他选项，比如将行保存到另一个表或文件中，对加载和复制延迟进行精细的控制，等等。
如何操作
pt-archiver中有许多选项，下面从简单的清除操作（purge）开始。
清除数据
如果想要删除employees表中hire_date超过30年的所有行，可以执行以下操作：
#先复制一个employees表
mysql> create table test1.tb02 as select * from employees.employees;
#然后删除符合条件的行
[root@www ~]# pt-archiver --source h=localhost,D=test1,t=tb02 -uroot -pCom.123456 \
> --where="hire_date<DATE_ADD(NOW(), INTERVAL -30 YEAR)" --no-check-charset \
> --limit 10000 --commit-each --purge
如果报以下错误，说明表没有索引，需要添加索引。
Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3216.
添加索引：
mysql> alter table tb02 add index (last_name);
Query OK, 0 rows affected (1.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
- –source选项传递主机名、数据库和表名；
- –limit选项限制在批处理操作中删除的行数；
- –progress选项：其输出是一个标题行，外加持续的状态输出。状态输出中的每一行都会列出当前的日期和时间、pt-archiver已经运行了多少秒，以及它已经归档了多少行；
- –statistics选项：pt-archiver会输出定时时间（timing）和其他信息，以帮助确定归档过程的哪一部分花的时间最多；
- –check-slave-lag选项：pt-archiver将暂停归档，直到从库的延时少于–max-lag。
归档数据
如果想把删除后剩余的行保存到一个单独的表或文件中，可以指定–dest选项。
如果想将test1.tb02表中所有行移到test2.test01表中，可以执行以下操作：
- 首先创建新表结构：
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> use test2;
Database changed
mysql> create table test01 like test1.tb02;
Query OK, 0 rows affected (0.01 sec)
- 如果指定–where=“1=1”,则将复制所有的行。
[root@www ~]# pt-archiver --source h=localhost,D=test1,t=tb02 \
> --dest h=localhost,D=test2,t=test01 -uroot -pCom.123456 \
> --where="1=1" --no-check-charset --commit-each
复制数据
如果想将数据从一个表复制到另一个表，可以使用mysqldump或mysqlpump来备份某些行，然后将它们加载到目标表中。
也可以使用pt-archiver来复制数据。如果指定–no-delete选项，pt-archiver将不会从源表中删除行：
[root@www ~]# pt-archiver --source h=localhost,D=test2,t=test02 \
> --dest h=localhost,D=test1,t=tb02 -uroot -pCom.123456 \
> --where="1=1" --no-check-charset --limit 60000 --commit-each --no-delete
克隆表
如果想克隆一个表，有许多方法可以选择
如何操作
- 使用INSERT INTO SELECT语句：
mysql> create table employees_clone like employees;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into employees_clone select * from employees;
Query OK, 300025 rows affected (8.55 sec)
Records: 300025 Duplicates: 0 Warnings: 0
但是如果表中有任何生成的列，上述语句将不起作用。在这种情况下，应该给出完整的insert语句，但要排除生成的列。
在大表上执行上面的语句是非常慢且危险的。如果语句执行失败，为了恢复表的状态，InnoDB会将所有的行保存在UNDO日志中。\2. 使用mysqldump或mysqlpump，并对单个表进行备份，在目标位置恢复它。如果表很大，这个操作可能需要很长时间。\3. 使用Innobackupex对特定的表进行备份，并将数据文件恢复到目标位置。\4. 在使用pt-archiver时带上–no-delete的选项，它会将所需要的的行或所有行复制到目标表。\5. 还可以使用可传输的表空间来克隆表。
为表分区
我们的数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也受到严重影响，出现了数据库性能瓶颈。
mysql 中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。当出现这种情况时，我们可以考虑分表或分区。
分区定义
分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。程序读写的时候操作的还是表名字，数据库自动去组织分区的数据。
用户所选择的划分数据的规则被称为分区函数（partitioning funcation）,它可以是模量（modulu）,与一组范围或值列表、一个内部哈希函数或一个线性哈希函数简单匹配。
表的不同行可以被分配给不同的物理分区，称为水平分区。MySQL不支持垂直分区，在垂直分区中，表的不同列被分配给不同的物理分区。
分区方法
RANGE
这种类型的分区根据落在给定范围内的列值，将行分配给分区
LIST
类似于按RANGE分区，不同的是其分区是基于与一组离散值匹配的列来选择的
HASH
在这种类型的分区操作中，一个分区是根据用户定义的表达式返回的值来选择的，该表达式对插入到表的行中列值进行操作。HASH函数可以包含任何在MySQL中具有非负整数值的有效表达式。
KEY
这种类型的分区类似于HASH分区，只是它仅提供一个或多个列，而且MySQL服务器提供自己的哈希函数。这些列可以包含除整数值以外的其他值，因为MySQL提供的哈希函数保证不管列数据是什么类型，结果都为整数。
上述每一个分区类型都有一个扩展。RANGE的扩展为RANGE COLUMNS，LIST的扩展为LIST COLUMNS，HASH的扩展为LINEAR HASH，KEY的扩展为LINEAR KEY。
对于[LINEAR] KEY、RANGE COLUMNS和LIST COLUMNS分区，分区表达式包含一个或多个列的列表。
在RANGE、LIST和[LINEAR] HASH分区中，分区的列的值被传递给分区函数，该函数返回一个整数值，表示该特定记录应该被存储在第几个分区。这个函数的返回值必须为既非常数也非随机数。
数据库分区的一个非常常见的用途是按照日期分割数据。
注意：分区仅适用于InnoDB表，并且外键不能与分区结合使用。
如何操作
你可以在创建表时指定分区，也可以通过执行ALTER TABLE命令来指定分区。分区列应该是表中所有唯一键的一部分。
如果基于created_at列定义了分区，并且id是主键，则应该将created_at列作为PRIMARY KEY的一部分包含在内，即（id, created_at）。
以下示例假定该表的外键没有被引用。
如果希望在MySQL8.0中基于范围或时间间隔实施分区计划，有两种选择：
- 按RANGE对表进行分区。对于分区表达式，应用在DATE、TIME或DATETIME列上运行的函数，并返回一个整数值。
- 使用DATE或DATETIME列作为分区列，通过RANGE COLUMNS对表进行分区。
RANGE分区
如果想要根据emp_no对employees表分区，并希望在一个分区中保留100000名员工，可以这样创建分区：
先查看employees表的创建语句
mysql> show create table employees
-> ;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
`address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`emp_no`),
KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建一个测试样例表：
mysql> CREATE TABLE employees_partition ( \
-> emp_no int(11) NOT NULL, \
-> birth_date date NOT NULL,\
-> first_name varchar(14) NOT NULL, \
-> last_name varchar(16) NOT NULL,\
-> gender enum('M','F') NOT NULL,\
-> hire_date date NOT NULL,\
-> address varchar(100) DEFAULT NULL,\
-> PRIMARY KEY (emp_no),\
-> KEY name (first_name,last_name)\
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci \
-> PARTITION BY RANGE (emp_no) \
-> (PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,\
-> PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB,\
-> PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB,\
-> PARTITION p3 VALUES LESS THAN (400000) ENGINE = InnoDB,\
-> PARTITION p4 VALUES LESS THAN (500000) ENGINE = InnoDB,\
-> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.07 sec)
查看创建的表文件
[root@www ~]# cd /var/lib/mysql/employees/
[root@www employees]# ls -l employees_partition*
-rw-r----- 1 mysql mysql 131072 Sep 28 09:18 employees_partition#P#p0.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 09:18 employees_partition#P#p1.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 09:18 employees_partition#P#p2.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 09:18 employees_partition#P#p3.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 09:18 employees_partition#P#p4.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 09:18 employees_partition#P#pmax.ibd
所有emp_no小于100000的员工将被划入分区p0，所有emp_no小于200000和大于等于100000的员工将被划入分区p1，依次类推。
如果员工编号大于500000,因为没有为这些编号定义分区，所以这时插入操作将会失败，并报错。为了避免这种情况，必须定期检查并添加分区或创建一个MAXVALUE分区，以捕获所有类似这种情况的异常：
插入数据后进行查看：
mysql> insert into employees_partition select * from employees;
Query OK, 300025 rows affected (3.29 sec)
Records: 300025 Duplicates: 0 Warnings: 0
mysql> select count(*) from employees_partition;
+----------+
| count(*) |
+----------+
| 300025 |
+----------+
1 row in set (0.03 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300025 |
+----------+
1 row in set (0.03 sec)
如果你想基于hire_date分区，可以使用YEAR（hire_date）函数作为分区表达式：
mysql> CREATE TABLE employees_partition02 ( \
-> emp_no int(11) NOT NULL, \
-> birth_date date NOT NULL,\
-> first_name varchar(14) NOT NULL, \
-> last_name varchar(16) NOT NULL,\
-> gender enum('M','F') NOT NULL,\
-> hire_date date NOT NULL,\
-> address varchar(100) DEFAULT NULL,\
-> PRIMARY KEY (emp_no, hire_date),\
-> KEY name (first_name,last_name)\
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci \
-> PARTITION BY RANGE (YEAR(hire_date)) \
-> (PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB,\
-> PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB,\
-> PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB,\
-> PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,\
-> PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,\
-> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.04 sec)
插入数据并验证：
mysql> insert into employees_partition02 select * from employees;
Query OK, 300025 rows affected (4.06 sec)
Records: 300025 Duplicates: 0 Warnings: 0
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300025 |
+----------+
1 row in set (0.06 sec)
MySQL中的分区被广泛应用于date、datetime或timestamp列。如果你想在数据库中存储一些事件，并且所有的查询都基于一个时间范围，则可以像这样使用分区。
如果希望将现有的一个表转换为分区的表，如果分区键不是主键的一部分，则需要删除（drop）主键，并将分区键作为主键和所有唯一键的一部分添加进来。否则，你将收到一条报错的消息：
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
你可以这样做：
mysql> ALTER TABLE employees DROP PRIMARY KEY, ADD PRIMARY \
-> KEY(emp_no,hire_date);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees PARTITION BY RANGE (YEAR(hire_date))\
-> (PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB,\
-> PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB,\
-> PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB,\
-> PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,\
-> PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,\
-> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 300025 rows affected (3.29 sec)
Records: 300025 Duplicates: 0 Warnings: 0
删除分区
如果希望删除分区，可以执行REMOVE PARTITIONING语句：
mysql> ALTER TABLE employees_partition02 REMOVE PARTITIONING;
Query OK, 300025 rows affected (3.18 sec)
Records: 300025 Duplicates: 0 Warnings: 0
#查看分区文件
[root@www employees]# ls -l employees_partition02*
-rw-r----- 1 mysql mysql 75497472 Sep 28 17:45 employees_partition02.ibd
RANGE COLUMNS分区
RANGE COLUMNS分区类似于RANGE分区，但是它允许使用基于多个列值的范围来定义分区。此外，你可以使用非整数类型的列来定义范围。两者之间主要区别如下：
- RANGE COLUMNS不接受表达式，只接受列的名称
- RANGE COLUMNS接受一个或多个列的列表
- RANGE COLUMNS的分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列。
在RANGE COLUMNS中可以直接使用hire_date列，而不是使用to_days()或year()函数。
首先创建一个employees表的副本表
mysql> create table employees_partition03 like employees;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into employees_partition03 select * from employees;
Query OK, 300025 rows affected (8.05 sec)
Records: 300025 Duplicates: 0 Warnings: 0
然后修改其使用RANGE COLUMNS方法分区
mysql> ALTER TABLE employees_partition03 DROP PRIMARY KEY,\
-> ADD PRIMARY KEY(emp_no,hire_date);
Query OK, 0 rows affected (2.73 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees_partition03 \
-> PARTITION BY RANGE COLUMNS (hire_date) \
-> (PARTITION p0 VALUES LESS THAN ('1970-01-01'),\
-> PARTITION p1 VALUES LESS THAN ('1980-01-01'),\
-> PARTITION p2 VALUES LESS THAN ('1990-01-01'),\
-> PARTITION p3 VALUES LESS THAN ('2000-01-01'),\
-> PARTITION p4 VALUES LESS THAN ('2010-01-01'),\
-> PARTITION p5 VALUES LESS THAN (MAXVALUE));
Query OK, 300025 rows affected (3.98 sec)
Records: 300025 Duplicates: 0 Warnings: 0
查看分区文件：
[root@www employees]# ls -l employees_partition03*
-rw-r----- 1 mysql mysql 131072 Sep 28 21:40 employees_partition03#P#p0.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 21:40 employees_partition03#P#p1.ibd
-rw-r----- 1 mysql mysql 18874368 Sep 28 21:40 employees_partition03#P#p2.ibd
-rw-r----- 1 mysql mysql 13631488 Sep 28 21:40 employees_partition03#P#p3.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 21:40 employees_partition03#P#p4.ibd
-rw-r----- 1 mysql mysql 131072 Sep 28 21:39 employees_partition03#P#p5.ibd
或者也可以根据员工的姓氏（last_name）来划分员工。这么做不能保证员工在各个分区之间均匀分布：
mysql> ALTER TABLE employees_partition03 \
-> PARTITION BY RANGE COLUMNS (last_name) \
-> (PARTITION p0 VALUES LESS THAN ('b'),\
-> PARTITION p1 VALUES LESS THAN ('f'),\
-> PARTITION p2 VALUES LESS THAN ('l'),\
-> PARTITION p3 VALUES LESS THAN ('q'),\
-> PARTITION p4 VALUES LESS THAN ('u'),\
-> PARTITION p5 VALUES LESS THAN ('z'));
-> partition pmax values less than (maxvalue));
使用RANGE COLUMNS,可以在分区函数中放置多个列：
mysql> CREATE TABLE range_columns_example ( \
-> a INT,\
-> b INT, \
-> c INT, \
-> d INT, \
-> e INT, \
-> PRIMARY KEY(a,b,c) \
-> ) \
-> PARTITION BY RANGE COLUMNS(a,b,c) (\
-> PARTITION p0 VALUES LESS THAN (0,25,50),\
-> PARTITION p1 VALUES LESS THAN (10,50,100),\
-> PARTITION p2 VALUES LESS THAN (10,100,200),\
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));
Query OK, 0 rows affected (0.10 sec)
如果插入值a=10,b=20,c=100,d=100,e=100，则它将被分配到p1分区。在通过RANGE COLUMNS设计表分区时，可以利用mysql客户端比较所需的元组来测试连续分区的定义，如下所示：
mysql> SELECT (10,20,100) < (0,25,50) p0, (10,20,100) < (10,50,100) p1,\
-> (10,20,100) < (10,100,200) p2;
+----+----+----+
| p0 | p1 | p2 |
+----+----+----+
| 0 | 1 | 1 |
+----+----+----+
1 row in set (0.00 sec)
在本例中，插入语句将被分配到p1分区。
LIST和LIST COLUMNS分区
LIST分区与RANGE分区类似，其每个分区都是根据一组值列表中的一个列值的成员来定义和选择的，而不是在一组连续的值范围内进行。
你需要通过PARTITION BY LIST ()来定义它，其中expr是一个列值或基于列值的表达式，并返回一个整数值。
分区定义包含VALUES IN (<value_list>),其中value_list是一个用逗号分割的整数列表，而不是VALUES LESS THAN ()。
如果希望使用除整数以外的数据类型，可以使用LIST COLUMNS。
与RANGE分区的情况不同，这里没有像MAXVALUE之类的catch-all,分区表达式期望的所有值都应该包含在PARTITION表达式中。
假设有一张带有邮政编码和城市信息的客户表。例如，如果想在分区中按照特定的邮政编码来划分客户，可以使用LIST分区：
mysql> CREATE TABLE customer ( \
-> customer_id INT,\
-> zipcode INT,\
-> city varchar(100),\
-> PRIMARY KEY (customer_id, zipcode) \
-> )\
-> PARTITION BY LIST (zipcode) (\
-> PARTITION pnorth VALUES IN (560030,560007,560051,560084),\
-> PARTITION peast VALUES IN (560040,560008,560061,560085),\
-> PARTITION pwest VALUES IN (560050,560009,560062,560086),\
-> PARTITION pcentral VALUES IN (560060,560010,560063,560087));
Query OK, 0 rows affected (0.11 sec)
如果希望直接使用列而不是整数，则可以使用LIST COLUMNS来分区：
mysql> CREATE TABLE customer ( \
-> customer_id INT,\
-> zipcode INT,\
-> city varchar(100),\
-> PRIMARY KEY (customer_id, city) \
-> )\
-> PARTITION BY LIST COLUMNS (city) (\
-> PARTITION pnorth VALUES IN ('city1','city2','city3'),\
-> PARTITION peast VALUES IN ('city4','city5','city6'),\
-> PARTITION pwest VALUES IN ('city7','city8','city9'),\
-> PARTITION pcentral VALUES IN ('city10','city11','city12'));
Query OK, 0 rows affected (0.11 sec)
HASH和LINEAR HASH分区
HASH分区主要是为了确保数据均匀地分布在数量预先确定的一组分区中。使用RANGE或LIST分区的话，必须明确指定应该将给定的列值或列值集合存储在哪一个分区中；而如果使用HASH分区，这个决定将由你来做，你只需要根据要进行哈希的列值指定一个列值或表达式，以及分区表要分为多少个分区即可。
如果希望员工在分区中均匀地分布，可以指定分区的数量，并根据YEAR(hire_date)进行HASH分区，而不是根据YEAR(hire_date)进行RANGE分区。当使用PARTITION BY HASH时，存储引擎会根据该表达式结果的模来确定要使用哪一个分区。
例如，如果hire_date是1987-11-28,YEAR(hire_date)将是1987，MOD(1987,8)的结果是3，所以这一行将被分到第三个分区：
mysql> CREATE TABLE employees_partition04 ( \
-> emp_no int(11) NOT NULL, \
-> birth_date date NOT NULL,\
-> first_name varchar(14) NOT NULL, \
-> last_name varchar(16) NOT NULL,\
-> gender enum('M','F') NOT NULL,\
-> hire_date date NOT NULL,\
-> address varchar(100) DEFAULT NULL,\
-> PRIMARY KEY (emp_no, hire_date),\
-> KEY name (first_name,last_name)\
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci \
-> PARTITION BY HASH (YEAR(hire_date)) \
-> PARTITIONS 8;
Query OK, 0 rows affected (0.15 sec)
效率最高的哈希函数是对单个列进行操作的函数，其值与该列值同步地增加或减少。
在LINEAR HASH分区中，可以使用相同的语法，只不过要添加LINEAR关键字。MySQL不使用MODULUS操作，而是使用2的幂算法来确定分区。
mysql> CREATE TABLE employees_partition05 ( \
-> emp_no int(11) NOT NULL, \
-> birth_date date NOT NULL,\
-> first_name varchar(14) NOT NULL, \
-> last_name varchar(16) NOT NULL,\
-> gender enum('M','F') NOT NULL,\
-> hire_date date NOT NULL,\
-> address varchar(100) DEFAULT NULL,\
-> PRIMARY KEY (emp_no, hire_date),\
-> KEY name (first_name,last_name)\
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci \
-> PARTITION BY LINEAR HASH (YEAR(hire_date)) \
-> PARTITIONS 8;
Query OK, 0 rows affected (0.12 sec)
KEY和LINEAR KEY分区
KEY分区与HASH分区类似，不同之处在于，HASH分区使用用户定义的表达式，KEY分区的哈希函数由MySQL服务器提供。这个内部哈希函数采用的是与PASSWORD()函数相同的算法。
KEY仅包含零个或几个列名称的列表。如果表有主键的话，则用作KEY分区的任何列都必须是主键的一部分或全部。如果没有列名可以被指定为分区键，则使用表的主键（如果有的话）：
mysql> CREATE TABLE employees_partition06 ( \
-> emp_no int(11) NOT NULL, \
-> birth_date date NOT NULL,\
-> first_name varchar(14) NOT NULL, \
-> last_name varchar(16) NOT NULL,\
-> gender enum('M','F') NOT NULL,\
-> hire_date date NOT NULL,\
-> address varchar(100) DEFAULT NULL,\
-> PRIMARY KEY (emp_no, hire_date),\
-> KEY name (first_name,last_name)\
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci \
-> PARTITION BY KEY() \
-> PARTITIONS 8;
Query OK, 0 rows affected (0.07 sec)
分区修剪和指定
MySQL不扫描没有匹配值的分区，这是自动的操作，称为分区修剪（partition prunnig）。对给定的值，MySQL优化器会计算分区表达式，以确定哪个分区包含该值，并且只扫描这个分区。
SELECT、DELETE和UPDATE语句支持分区修剪。INSERT语句目前不能被裁剪。
mysql> SELECT last_name,birth_date FROM employees WHERE hire_date='1999-02-01' AND first_name='Mariangiola';
分区修剪是基于WHERE子句的自动选择。如果在WHERE子句中没有给出表达式，那么MySQL必须扫描整个表。
可以在查询中显示地指定要扫描的分区。PARTITION选项用于从给定的表中选择分区，应该在所有其他选项之前、表名（包括所有表别名）之后，执行关键字PARTITION<分区名>。例如：
mysql> SELECT emp_no,hire_date FROM employees PARTITION (p1990) LIMIT 10;
管理分区
当涉及管理分区时，最重要的事情就是在基于时间的RANGE分区中事先添加足够数量的分区。如果不这样做，就会导致插入错误；或者，如果定义了MAXVALUE分区，所有的插入操作就都会在MAXVALUE分区执行。
另一个重要的事情是，如果数据超出保留日期(cross retention)，则删除它。
要执行管理分区的这些操作，需要执行ALTER命令。
添加分区
要添加新分区，请执行ADD PARTITION()语句：
mysql> ALTER TABLE event_history ADD PARTITION (\
-> PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB, \
-> PARTITION p20171017 VALUES LESS THAN (736984) ENGINE = InnoDB
);
此语句会在很短的时间内锁定整个表。
重组分区
如果存在MAXVALUE分区，则不能在MAXVALUE之后添加分区。在这种情况下，你需要将MAXVALUE分区用REORGANIZE MAXVALUE语句分为两个分区：
mysql> ALTER TABLE event_history REORGANIZE PARTITION pmax INTO (\
->PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB, \
-> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
记住，在重新组织分区时，MySQL要移动大量的数据，并且在此期间将锁定表。还可以将多个分区重组为一个分区：
mysql> ALTER TABLE event_history REORGANIZE PARTITION \
-> p20171001,p20171002,p20171003,p20171004,p20171005,p20171006,p20171007 \
-> INTO (PARTITION p2017_oct_week1 VALUES LESS THAN (736974));
删除分区
如果数据已超出保留日期，则可以删除（DROP）整个分区，与传统的DELETE FROM TABLE语句相比，这种操作是超级快的，在高效存档数据时非常有用。
mysql> ALTER TABLE event_history DROP PARTITION p20170930;
删除（DROP）分区会从表中删除指定分区。
TRUNCATE分区
如果希望在表中保留PARTITION DEFINITION且仅删除数据，则可以执行TRUNCATE PARTITION命令：
mysql> ALTER TABLE event_history TRUNCATE PARTITION p20171001;
管理HASH和KEY分区
在HASH和KEY分区上执行的操作完全不同。只能减少或增加分区的数量。
假设employees表是基于HASH进行分区的，开始分区数是8，要将分区数从8减少到6，可以执行COALESCE PARTITION语句，并指定要减少的分区数，即8-6=2
mysql> ALTER TABLE emplyees COALESCE PARTITION 2;
要将分区数从6增加到16，可以执行ADD PARTITION语句并指定要增加的分区数，即16-6=10
mysql> ALTER TABLE employees ADD PARTITION PARTITIONS 10;
其它操作
对于特定的分区，还可以执行其他操作，例如REBUILD、OPTIMIZE、ANALYZE和REPAIR语句。例如：
mysql> ALTER TABLE event_history REPAIR PARTITION p20171009,p20171010;
分区信息
如果要获取现有分区的信息，可以通过多种方式获得。
使用SHOW CREATE TABLE
要知道一张表是否已分区，可以执行show create table \G语句，该语句会列出表的定义和表的分区。例如：
mysql> SHOW CREATE TABLE employees_partition05 \G
*************************** 1. row ***************************
Table: employees_partition05
Create Table: CREATE TABLE `employees_partition05` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
`address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`emp_no`,`hire_date`),
KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LINEAR HASH (year(`hire_date`))
PARTITIONS 8 */
1 row in set (0.00 sec)
使用SHOW TABLE STATUS
可以执行SHOW TABLE STATUS命令，并在输出中查看Create_options:
mysql> show table status like 'employees_partition05' \G
*************************** 1. row ***************************
Name: employees_partition05
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 131072
Max_data_length: 0
Index_length: 131072
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-04 11:46:50
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options: partitioned
Comment:
1 row in set (0.00 sec)
使用EXPLAIN
explain计划会显示一条查询所扫描的所有分区。例如：
mysql> explain select * from employees_partition05\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees_partition05
partitions: p0,p1,p2,p3,p4,p5,p6,p7
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
查询INFORMATION_SCHEMA.PARTITIONS表
与上面的方法相比，INFORMATION_SCHEMA.PARTITIONS表提供了关于分区的更多信息,要了解有关表的分区的更多详细信息，可以如下操作：
mysql> SELECT partition_name FROM information_schema.partitions \
-> WHERE table_schema='employees' AND table_name='employees_partition05';
+----------------+
| PARTITION_NAME |
+----------------+
| p0 |
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
| p6 |
| p7 |
+----------------+
8 rows in set (0.00 sec)
可以进一步获取指定分区的PARTITION_METHOD、PARTITION_EXPRESSION、PARTITION_DESCRIPTION和TABLE_ROWS等详细信息：
mysql> SELECT * FROM information_schema.partitions \
-> WHERE table_schema='employees' AND table_name='employees_partition05'\
-> AND partition_name = 'p4'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: employees_partition05
PARTITION_NAME: p4
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 5
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: LINEAR HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(`hire_date`)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 16384
DATA_FREE: 0
CREATE_TIME: 2019-10-04 11:46:50
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
1 row in set (0.00 sec)
- 原文作者：老鱼干🦈
- 原文链接：//www.tinyfish.top:80/post/Linux_%E8%BF%90%E7%BB%B4/%E6%95%B0%E6%8D%AE%E5%BA%93/MySql/8.-MySQL%E8%A1%A8%E7%BB%B4%E6%8A%A42/
- 版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议. 进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。