概述

维护数据库的一项关键工作就是管理表。通常，你需要修改一个很大的表或克隆一个表。在本章中，将学习如何管理大表（big table）。

在本章中，将学习以下内容：

安装Percona工具包
修改表结构
在数据库之间移动表
使用一种在线模式更改工具来修改表
归档表
克隆表
为表分区
分区截断和选择
分区管理
分区信息
有效地管理生存时间和软删除行

安装Percona工具包

Percona工具包是一个高级开源命令行工具集，它是由Percona开发和使用的，用于执行各种不易手动执行的困难或复杂的任务。

安装(CentOS7系统)

先安装官方YUM源

[root@www yum.repos.d]# yum install https://www.percona.com/redir/downloads/percona-release/redhat/1.0-13/percona-release-1.0-13.noarch.rpm

列出percona-toolkit工具包的可用版本

[root@www ~]# yum list percona-toolkit --showduplicates | sort -r
 * updates: mirrors.163.com
 * remi-safe: mirrors.tuna.tsinghua.edu.cn
 * remi: mirrors.tuna.tsinghua.edu.cn
percona-toolkit.x86_64           3.1-1.el7               percona-release-x86_64 
percona-toolkit.x86_64           3.1.0-2.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.9-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.8-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.7-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.6-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.5-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.4-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.4-1.el7             @percona-release-x86_64
percona-toolkit.x86_64           3.0.3-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.2-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.13-1.el7            percona-release-x86_64 
percona-toolkit.x86_64           3.0.12-1.el7            percona-release-x86_64 
percona-toolkit.x86_64           3.0.1-1.el7             percona-release-x86_64 
percona-toolkit.x86_64           3.0.11-1.el7            percona-release-x86_64 
percona-toolkit.x86_64           3.0.10-1.el7            percona-release-x86_64 
percona-toolkit.noarch           2.2.20-1                percona-release-noarch 
percona-toolkit.noarch           2.2.19-1                percona-release-noarch 
percona-toolkit.noarch           2.2.18-1                percona-release-noarch 
percona-toolkit.noarch           2.2.17-1                percona-release-noarch 
percona-toolkit.noarch           2.2.16-1                percona-release-noarch 
percona-toolkit.noarch           2.2.15-2                percona-release-noarch 
percona-toolkit.noarch           2.2.15-1                percona-release-noarch 
percona-toolkit.noarch           2.2.14-1                percona-release-noarch 
percona-toolkit.noarch           2.2.13-1                percona-release-noarch 
percona-toolkit.noarch           2.2.12-1                percona-release-noarch 
percona-toolkit.noarch           2.2.11-1                percona-release-noarch 
Loading mirror speeds from cached hostfile
Loaded plugins: fastestmirror
Installed Packages
 * extras: mirrors.cn99.com
 * epel: mirrors.aliyun.com
 * base: mirrors.cn99.com
Available Packages

安装3.0.4版本工具包

[root@www ~]# yum install percona-toolkit-3.0.4-1.el7.x86_64
[root@www ~]# pt-online-schema-change --version
pt-online-schema-change 3.0.4

修改表结构

ALTER TABLE语句用于改变表的结构。例如，可以添加或删除列、创建或销毁索引、更改现有列的类型，或者对列或这个表重命名。

在执行某些修改（alter）操作时（如更改列的数据类型、添加SPATIAL INDEX、删除主键、转换字符集、添加/删除加密等），对表的DML操作会被阻塞。如果表很大，则需要花费更多的时间来执行alter操作，并且在此期间应用程序无法访问表，这是无法接受的。在这种情况下，采用pt-online-schema更改（change）就很有用，它允许使用DML语句。

修改（alter）表的操作有以下两种算法

In-place(默认)：不需要复制整个表的数据
Copy：将数据复制到一个临时的磁盘文件中并重新命名

只有特定的alter操作可以在本地完成。在线DDL操作的性能很大程度上取决于该操作是否能就地执行，或者需要复制和重建整个表。具体信息可以参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html 以查看哪些操作可以就地执行，以及为了避免表复制操作有哪些要求。

没有就地（in-place）执行的ALTER TABLE操作会创建原始表的临时副本。MySQL等待修改表的其他操作完成后，然后继续运行。它将修改合并到这个副本中，删除原始表，并重命名新的表。当ALTER TABLE正在执行时，原始表可供其他会话读。在ALTER TABLE操作开始之后，对表开始做的更新和写操作将停止，直到新表准备就绪，然后这些更新和写操作会被自动重定向到这个没有发生过任何更新失败的新表。原始表的临时副本在新表的数据库目录中创建。ALTER TABLE操作可以改变原始表的数据库目录，该操作将表重命名，放到不同的数据库。

要了解DDL操作是在原地执行还是在表的副本中执行，可以查看命令执行完以后显示的rows affected的值。

更改列的默认值（超快，完全不影响表的数据），输出将是这样的：

Query OK, 0 rows affected (0.07 sec)

添加索引（需要一点儿时间，但如果显示 0 rows affected,则表示这个表未被复制），输出将是这样的：

Query OK, 0 rows affected (21.42 sec)

更改列的数据类型（要花费大量时间，并且需要重新构建表的所有行），输出将是这样的：

Query OK, 1671168 rows affected (1 min 35.54 sec)

更改列的数据类型时需要重新构建表中的所有行，更改varchar大小则例外，它可以使用在线ALTER TABLE命令来实现。

如何操作

如果要向test1.tb01表中添加一个新列，可以执行ADD COLUMN语句：

mysql> desc test1.tb01;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| stuid     | int(11)      | NO   | PRI | NULL    |       |
| stuname   | varchar(20)  | NO   |     | NULL    |       |
| stusex    | char(1)      | NO   |     | NULL    |       |
| cardid    | varchar(20)  | NO   |     | NULL    |       |
| birthday  | datetime     | YES  |     | NULL    |       |
| entertime | datetime     | YES  |     | NULL    |       |
| address   | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> ALTER TABLE test1.tb01 ADD COLUMN country varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test1.tb01;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| stuid     | int(11)      | NO   | PRI | NULL    |       |
| stuname   | varchar(20)  | NO   |     | NULL    |       |
| stusex    | char(1)      | NO   |     | NULL    |       |
| cardid    | varchar(20)  | NO   |     | NULL    |       |
| birthday  | datetime     | YES  |     | NULL    |       |
| entertime | datetime     | YES  |     | NULL    |       |
| address   | varchar(100) | YES  |     | NULL    |       |
| country   | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

可以看到受影响的行数为0，这意味着该表没有被复制，并且操作已经就地完成。

如果想要增加varchar列的长度，可以执行MODIFY COLUMN语句：

mysql> ALTER TABLE test1.tb01 MODIFY COLUMN address varchar(200);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test1.tb01;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| stuid     | int(11)      | NO   | PRI | NULL    |       |
| stuname   | varchar(20)  | NO   |     | NULL    |       |
| stusex    | char(1)      | NO   |     | NULL    |       |
| cardid    | varchar(20)  | NO   |     | NULL    |       |
| birthday  | datetime     | YES  |     | NULL    |       |
| entertime | datetime     | YES  |     | NULL    |       |
| address   | varchar(200) | YES  |     | NULL    |       |
| country   | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

如果感觉varchar(200)不足以存储地址，并希望将其更改为tinytext,那么可以使用MODIFY COLUMN语句。由于正在修改一个列的数据类型，所以应该修改现有表中的所有行，这需要执行复制表的操作，而且DML会被阻塞：

mysql> ALTER TABLE test1.tb01 MODIFY COLUMN address tinytext;
Query OK, 20000 rows affected (0.11 sec)
Records: 20000  Duplicates: 0  Warnings: 0
mysql> desc test1.tb01;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| stuid     | int(11)      | NO   | PRI | NULL    |       |
| stuname   | varchar(20)  | NO   |     | NULL    |       |
| stusex    | char(1)      | NO   |     | NULL    |       |
| cardid    | varchar(20)  | NO   |     | NULL    |       |
| birthday  | datetime     | YES  |     | NULL    |       |
| entertime | datetime     | YES  |     | NULL    |       |
| address   | tinytext     | YES  |     | NULL    |       |
| country   | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

20000行被影响，这就是这个表的大小。

还可以做许多其他操作，比如重命名一个列、更改默认值、对列位置重新排序等。可以参照以下MySQL文档：
（ https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html ）

在数据库之间移动表

可以通过执行RENAME TABLE语句重命名一个表。

先创建示例表和数据库。

mysql> create database prod;
Query OK, 1 row affected (0.00 sec)
mysql> create table prod.audit_log (id int NOT NULL, msg varchar(64));
Query OK, 0 rows affected (0.01 sec)
mysql> create database archive;
Query OK, 1 row affected (0.00 sec)

如何操作

例如，如果要把audit_log表重命名为audit_log_archive_2018,可以执行以下操作：

mysql> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> RENAME TABLE audit_log TO audit_log_archive_2018;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------------+
| Tables_in_prod         |
+------------------------+
| audit_log_archive_2018 |
+------------------------+
1 row in set (0.00 sec)

如果希望将这张表从一个数据库移到另一个数据库，可以使用点记法制定数据库的名称。例如，希望将audit_log表从名为prod的数据库移到名为archive的数据库，可以执行：

mysql> RENAME TABLE prod.audit_log_archive_2018 TO archive.audit_log;
Query OK, 0 rows affected (0.01 sec)
mysql> use archive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_archive |
+-------------------+
| audit_log         |
+-------------------+
1 row in set (0.00 sec)

使用在线模式更改工具修改表

本节中，将了解Percona的pt-online-schema-change(pt-osc)工具，该工具用于在DML未阻塞的情况下执行ALTER TABLE操作。

如何运作

pt-online-schema-change会先创建表的空副本，然后根据需要对其进行修改，再将原始表中的行复制到这个新表中。复制完成后，它将移除原来的表并将其替换为新的表。默认情况下，它会删除（drop）原始表。

数据的复制是以小块数据为单位执行，这些数据块的大小可以调整，以便能在指定时间内完成复制。在复制期间对原始表数据的任何修改都将反映在新表中，因为pt-online-schema-change会在原始表上创建触发器以更新新表中的相应行。触发器的使用意味着，如果表中已经定义了触发器，pt-online-schema-change将不起作用。

当pt-online-schema-change将数据复制到新表中时，它使用原子操作RENAME TABLE来同时重命名原始表和新表。完成此操作后，它将删除原始表。

外键会使这个工具的操作复杂化，并引入额外的风险。当外键指向表时，原子重命名原始表和新表的技术不起作用。在模式更改完成后，pt-online-schema-change必须更新外键来引用新表。

如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。没有使用 —alter-foreign-keys-method=rebuild_constraints 指定特定的值，该工具不予执行;
创建一个和源表表结构一样的临时表(_tablename_new)，执行alter修改临时表表结构;
在原表上创建3个于inser delete update对应的触发器;（用于copy 数据的过程中，在原表的更新操作更新到新表）
从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表;
修改外键相关的子表，根据修改后的数据，修改外键关联的子表;
rename源数据表为old表，把新表rename为源表名，并将old表删除;
删除触发器。

如何操作

修改列数据类型可以像如下这般操作：

[root@www ~]# pt-online-schema-change D=test1,t=tb01,h=localhost -u root \
> --ask-pass --alter="MODIFY COLUMN address VARCHAR(100)" --alter-foreign-keys-method=auto \
> --execute
Enter MySQL password:
No slaves found.  See --recursion-method if host www.lnmp.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `test1`.`tb01`; ignoring --alter-foreign-keys-method.
Altering `test1`.`tb01`...
Creating new table...
Created new table test1._tb01_new OK.
Altering new table...
Altered `test1`.`_tb01_new` OK.
2019-09-23T16:49:48 Creating triggers...
2019-09-23T16:49:48 Created triggers OK.
2019-09-23T16:49:48 Copying approximately 19544 rows...
2019-09-23T16:49:48 Copied rows OK.
2019-09-23T16:49:48 Analyzing new table...
2019-09-23T16:49:48 Swapping tables...
2019-09-23T16:49:48 Swapped original and new tables OK.
2019-09-23T16:49:48 Dropping old table...
2019-09-23T16:49:48 Dropped old table `test1`.`_tb01_old` OK.
2019-09-23T16:49:48 Dropping triggers...
2019-09-23T16:49:48 Dropped triggers OK.
Successfully altered `test1`.`tb01`.

修改一个带外键的表：employees.employees表（提前增加address列）

mysql> alter table employees add column address tinytext;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后修改address列类型

[root@www ~]# pt-online-schema-change D=employees,t=employees,h=localhost -u root --ask-pass \
> --alter="MODIFY COLUMN address VARCHAR(100)" \
> --alter-foreign-keys-method=auto --execute
Enter MySQL password: 
No slaves found.  See --recursion-method if host www.lnmp.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `employees`.`dept_manager` (approx. 24 rows)
  `employees`.`dept_emp` (approx. 331143 rows)
  `employees`.`titles` (approx. 442070 rows)
  `employees`.`salaries` (approx. 2838426 rows)
Will automatically choose the method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
2019-09-23T17:03:32 Creating triggers...
2019-09-23T17:03:32 Created triggers OK.
2019-09-23T17:03:32 Copying approximately 299568 rows...
2019-09-23T17:03:36 Copied rows OK.
2019-09-23T17:03:36 Max rows for the rebuild_constraints method: 168690
Determining the method to update foreign keys...
2019-09-23T17:03:36   `employees`.`dept_manager`: 24 rows; can use rebuild_constraints
2019-09-23T17:03:36   `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2019-09-23T17:03:36 Drop-swapping tables...
2019-09-23T17:03:36 Analyzing new table...
2019-09-23T17:03:36 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2019-09-23T17:03:36 Dropping triggers...
2019-09-23T17:03:36 Dropped triggers OK.
Successfully altered `employees`.`employees`.

可以看到，pt-online-schema-change创建了一个列数据类型为改后的类型的新表，在表中创建了触发器，将行复制到新表，最后，重命名这张新表。

如果你想修改salaries表，而它已经有触发器，那么会出现错误：

[root@www ~]# pt-online-schema-change D=employees,t=salaries,h=localhost -u root --ask-pass --alter="MODIFY COLUMN salary int" --alter-foreign-keys-method=auto --execute
Enter MySQL password: 
No slaves found.  See --recursion-method if host www.lnmp.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
The table `employees`.`salaries` has triggers but --preserve-triggers was not specified.
Please read the documentation for --preserve-triggers.

根据提示，需要指定–preserve-triggers选项：

[root@www ~]# pt-online-schema-change D=employees,t=salaries,h=localhost -u root \
> --ask-pass --alter="MODIFY COLUMN salary int" \
> --alter-foreign-keys-method=auto --execute \
> --preserve-triggers
Enter MySQL password: 
No slaves found.  See --recursion-method if host www.lnmp.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `employees`.`salaries`; ignoring --alter-foreign-keys-method.
Altering `employees`.`salaries`...
Creating new table...
Created new table employees._salaries_new OK.
Altering new table...
Altered `employees`.`_salaries_new` OK.
2019-09-23T17:32:48 Creating triggers...
2019-09-23T17:32:48 Created triggers OK.
2019-09-23T17:32:48 Copying approximately 2838426 rows...
2019-09-23T17:33:12 Copied rows OK.
2019-09-23T17:33:12 Adding original triggers to new table.
2019-09-23T17:33:12 Analyzing new table...
2019-09-23T17:33:12 Swapping tables...
2019-09-23T17:33:12 Swapped original and new tables OK.
2019-09-23T17:33:12 Dropping old table...
2019-09-23T17:33:12 Dropped old table `employees`.`_salaries_old` OK.
2019-09-23T17:33:12 Dropping triggers...
2019-09-23T17:33:12 Dropped triggers OK.
Successfully altered `employees`.`salaries`.

如果此数据库服务器有从库，那么pt-online-schema-change在将现有表复制到新表时可能会使从库产生延时。为了避免这种情况，可以指定–check-slave-lag(默认是启用的)；它会暂停数据的复制，直到此复制的延时小于–max-lag(默认为1秒)。可以通过传递–max-lag选项来指定–max-lag的值。

如果想确保从库的延时不会超过10秒，可以设置–max-lag=10。

pt-online-schema-change仅在有主键或唯一键时才起作用，否则其执行将失败，并显示以下错误：

The new table `employees`.`_employee_names_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

因此，如果表没有唯一键，则不能使用pt-online-schema-change工具。

Linux_运维

2. MySQL表维护1

概述

安装Percona工具包

安装(CentOS7系统)

修改表结构

如何操作

在数据库之间移动表

如何操作

使用在线模式更改工具修改表

如何运作

如何操作