[Mysql] optimize table运行是否支持online ddl


本文总阅读量次

0、相应官方文档

1
2
3
4
（online ddl）官方文档提示当存在fulltext索引表进行优化表操作(optimize table)时是不支持online ddl的
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-table-operations
（optimize table）官方文档提示当存在索引时，对表进行优化不支持在线ddl
https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

下面分别对有主键索引、fulltext索引、无索引情况先查看是否支持online ddl;

1、带有主键索引

查看表结构

1
2
3
4
5
6
7
8
9
10
11
12
root@db 15:44:  [aaaa]> show create table optimize_tb;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                     |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb | CREATE TABLE `optimize_tb` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

插入测试数据

1
2
3
4
5
6
7
8
9
10
11
root@db 15:44:  [aaaa]> insert into optimize_tb (select * from testfororder limit 1000000);
Query OK, 1000000 rows affected (42.98 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@db 15:46:  [aaaa]> select count(*) from optimize_tb;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.28 sec)

查看文件大小

1
2
3
[root@dax-mysql-master aaaa]#  ls -lhtr|grep optimize
-rw-r-----. 1 mysql mysql 8.5K 11月 27 15:44 optimize_tb.frm
-rw-r-----. 1 mysql mysql  48M 11月 27 15:46 optimize_tb.ibd

删除前50万数据

1
2
3
4
5
6
7
8
9
10
root@db 15:47:  [aaaa]> delete from optimize_tb limit 500000;
Query OK, 500000 rows affected (8.72 sec)

root@db 15:47:  [aaaa]> select count(*) from optimize_tb;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.11 sec)

查看数据文件没有任何变化

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize
-rw-r-----. 1 mysql mysql 8.5K 11月 27 15:44 optimize_tb.frm
-rw-r-----. 1 mysql mysql  48M 11月 27 15:47 optimize_tb.ibd

session1执行优化表操作

1
2
3
4
5
6
7
8
root@db 15:48:  [aaaa]>  optimize table optimize_tb;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| aaaa.optimize_tb | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| aaaa.optimize_tb | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (16.34 sec)

session2执行插入语句操作

1
2
3
4
5
6
7
8
root@db 15:47:  [aaaa]> insert into optimize_tb(id,name,age) values(8166638,'lucy1',24);
Query OK, 1 row affected (0.18 sec)
root@db 15:48:  [aaaa]> insert into optimize_tb(id,name,age) values(8166638,'lucy1',24);
ERROR 1062 (23000): Duplicate entry '8166638' for key 'PRIMARY'
root@db 15:48:  [aaaa]> insert into optimize_tb(id,name,age) values(8166639,'lucy1',24);
Query OK, 1 row affected (0.25 sec)
root@db 15:48:  [aaaa]> insert into optimize_tb(id,name,age) values(8166640,'lucy1',24);
Query OK, 1 row affected (0.25 sec)

最后查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize
-rw-r-----. 1 mysql mysql 8.5K 11月 27 15:48 optimize_tb.frm
-rw-r-----. 1 mysql mysql  28M 11月 27 15:48 optimize_tb.ibd

数据插入成功，在有主键的情况下，使用optimize table可支持online ddl。

2、带有fulltext索引

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@db 15:53:  [aaaa]> create table optimize_tb1 like optimize_tb;
Query OK, 0 rows affected (0.65 sec)

root@db 15:53:  [aaaa]> show create table optimize_tb1;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb1 | CREATE TABLE `optimize_tb1` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

删除主键索引

1
2
root@db 15:53:  [aaaa]> alter table optimize_tb1 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

因为存在自增约束，报错，需要删除约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 15:54:  [aaaa]> alter table optimize_tb1 modify id int(7);
Query OK, 0 rows affected (2.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 15:54:  [aaaa]> alter table optimize_tb1 drop primary key;
Query OK, 0 rows affected (1.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 15:54:  [aaaa]> show create table optimize_tb1;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb1 | CREATE TABLE `optimize_tb1` (
  `id` int(7) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据

1
2
3
4
5
6
7
8
9
10
11
root@db 15:55:  [aaaa]> insert into optimize_tb1 (select * from testfororder limit 1000000);
Query OK, 1000000 rows affected (26.04 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@db 15:56:  [aaaa]> select count(*) from optimize_tb1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.48 sec)

创建fulltext 索引

1
2
3
root@db 15:56:  [aaaa]> create fulltext index name_fullind on optimize_tb1(name);
Query OK, 0 rows affected, 1 warning (1 min 12.58 sec)
Records: 0  Duplicates: 0  Warnings: 1

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb1
-rw-r-----. 1 mysql mysql 8.5K 11月 27 15:56 optimize_tb1.frm
-rw-r-----. 1 mysql mysql  92M 11月 27 15:58 optimize_tb1.ibd

删除前50万数据

1
2
3
4
5
6
7
8
9
10
root@db 15:58:  [aaaa]> delete from optimize_tb1 limit 500000;
Query OK, 500000 rows affected (1 min 13.37 sec)

root@db 15:59:  [aaaa]> select count(*) from optimize_tb1;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.50 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb1
-rw-r-----. 1 mysql mysql 8.5K 11月 27 15:56 optimize_tb1.frm
-rw-r-----. 1 mysql mysql  92M 11月 27 15:59 optimize_tb1.ibd

session1对表进行优化

1
2
3
4
5
6
7
8
root@db 15:59:  [aaaa]> optimize table optimize_tb1;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| aaaa.optimize_tb1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| aaaa.optimize_tb1 | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 13.65 sec)

session2对表插入数据

1
2
root@db 16:00:  [aaaa]> insert into optimize_tb1(id,name,age) values(8166638,'lucy1',24);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3查看数据库线程

1
2
3
4
5
6
7
8
9
root@db 16:00:  [(none)]> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                             |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
|  5 | root | localhost | aaaa | Query   |   41 | copy to tmp table               | optimize table optimize_tb1                                      |
|  7 | root | localhost | aaaa | Query   |   40 | Waiting for table metadata lock | insert into optimize_tb1(id,name,age) values(8166638,'lucy1',24) |
|  8 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                                                 |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb1
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:00 optimize_tb1.frm
-rw-r-----. 1 mysql mysql  48M 11月 27 16:02 optimize_tb1.ibd

当表存在fulltext索引是，执行optimize table会锁表，其他会话连接进来无法做增删改操作。

3、表不存在任何索引

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@db 16:04:  [aaaa]> create table optimize_tb2 like optimize_tb;
Query OK, 0 rows affected (0.60 sec)

root@db 16:04:  [aaaa]> show create table optimize_tb2;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb2 | CREATE TABLE `optimize_tb2` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

删除主键索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 16:04:  [aaaa]> alter table optimize_tb2 modify id int(7);
Query OK, 0 rows affected (1.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 16:04:  [aaaa]> alter table optimize_tb2 drop primary key;
Query OK, 0 rows affected (3.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 16:04:  [aaaa]> show create table optimize_tb2;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb2 | CREATE TABLE `optimize_tb2` (
  `id` int(7) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
root@db 16:04:  [aaaa]> insert into optimize_tb2 (select * from testfororder limit 1000000);
Query OK, 1000000 rows affected (11.85 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@db 16:06:  [aaaa]> 
root@db 16:07:  [aaaa]> select count(*) from optimize_tb2;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.41 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb2
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:04 optimize_tb2.frm
-rw-r-----. 1 mysql mysql  56M 11月 27 16:06 optimize_tb2.ibd

删除前50万数据

1
2
3
4
5
6
7
8
9
10
root@db 16:07:  [aaaa]> delete from optimize_tb2 limit 500000;
Query OK, 500000 rows affected (2.50 sec)

root@db 16:11:  [aaaa]> select count(*) from optimize_tb2;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.29 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb2
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:04 optimize_tb2.frm
-rw-r-----. 1 mysql mysql  56M 11月 27 16:11 optimize_tb2.ibd

session1对表进行优化

1
2
3
4
5
6
7
8
root@db 16:11:  [aaaa]> optimize table optimize_tb2;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| aaaa.optimize_tb2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| aaaa.optimize_tb2 | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (22.04 sec

session2对表插入数据

1
2
3
4
5
6
7
8
9
10
root@db 16:11:  [aaaa]> insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24);
Query OK, 1 row affected (0.35 sec)
root@db 16:11:  [aaaa]> insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24);
Query OK, 1 row affected (0.44 sec)
root@db 16:11:  [aaaa]> insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24);
Query OK, 1 row affected (0.24 sec)
root@db 16:11:  [aaaa]> insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24);
Query OK, 1 row affected (0.38 sec)
root@db 16:11:  [aaaa]> insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24);
Query OK, 1 row affected (0.20 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb2
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:11 optimize_tb2.frm
-rw-r-----. 1 mysql mysql  32M 11月 27 16:12 optimize_tb2.ibd

在没有任何索引的情况下，insert语句成功。

4、当mysql环境变量 old_alter_table为enabled（默认为off）或者 mysql启动参数添加了skip-new选项时，运行optimize table命令，会使用table copy的方式重建表，此时online ddl是不可用的，下面添加skip-new参数重新进行测试。

在/etc/my.cnf添加参数skip-new，重新启动mysql：

1
2
3
4
5
[root@dax-mysql-master aaaa]# more /etc/my.cnf|grep skip-new
skip-new
[root@dax-mysql-master aaaa]# /etc/init.d/mysql restart
Shutting down MySQL....... SUCCESS! 
Starting MySQL..... SUCCESS!

删除上面的测试表，再次进行测试

1
2
root@db 16:21:  [aaaa]> drop table optimize_tb,optimize_tb1,optimize_tb2;
Query OK, 0 rows affected (1.58 sec)

5、带有主键索引

创建测试表

1
2
root@db 16:23:  [aaaa]> create table optimize_tb like testfororder;
Query OK, 0 rows affected (1.29 sec)

插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
root@db 16:23:  [aaaa]> insert into optimize_tb (select * from testfororder limit 1000000);
Query OK, 1000000 rows affected (33.65 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@db 16:24:  [aaaa]> show create table optimize_tb;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                            |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb | CREATE TABLE `optimize_tb` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8166638 DEFAULT CHARSET=utf8mb4 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@db 16:25:  [aaaa]>  select count(*) from optimize_tb;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:23 optimize_tb.frm
-rw-r-----. 1 mysql mysql  48M 11月 27 16:24 optimize_tb.ibd

删除前50万数据

1
2
3
4
5
6
7
8
9
10
root@db 16:26:  [aaaa]> delete from optimize_tb limit 500000;
Query OK, 500000 rows affected (9.18 sec)

root@db 16:26:  [aaaa]> select count(*) from optimize_tb;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.13 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:23 optimize_tb.frm
-rw-r-----. 1 mysql mysql  48M 11月 27 16:26 optimize_tb.ibd

session1执行优化表操作

1
2
3
root@db 16:27:  [aaaa]> optimize table optimize_tb;
Query OK, 500000 rows affected (26.01 sec)
Records: 500000  Duplicates: 0  Warnings: 0

session2执行插入操作

1
2
root@db 16:27:  [aaaa]> insert into optimize_tb(id,name,age) values(8166638,'lucy1',24);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3查看mysql线程

1
2
3
4
5
6
7
8
root@db 16:27:  [(none)]> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                            |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
|  2 | root | localhost | aaaa | Query   |   19 | copy to tmp table               | optimize table optimize_tb                                      |
|  3 | root | localhost | aaaa | Query   |   11 | Waiting for table metadata lock | insert into optimize_tb(id,name,age) values(8166638,'lucy1',24) |
|  4 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                                                |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------------------+

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:27 optimize_tb.frm
-rw-r-----. 1 mysql mysql  28M 11月 27 16:27 optimize_tb.ibd

在存在主键索引的情况下，数据插入失败，提示锁等待超时。

6、表中带有fulltext索引

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@db 16:31:  [aaaa]> create table optimize_tb1 like optimize_tb;
Query OK, 0 rows affected (1.07 sec)

root@db 16:31:  [aaaa]> show create table optimize_tb1;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb1 | CREATE TABLE `optimize_tb1` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

删除主键索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 16:31:  [aaaa]> alter table optimize_tb1 modify id int(7);
Query OK, 0 rows affected (1.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 16:31:  [aaaa]> alter table optimize_tb1 drop primary key;
Query OK, 0 rows affected (1.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 16:31:  [aaaa]> show create table optimize_tb1;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb1 | CREATE TABLE `optimize_tb1` (
  `id` int(7) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据

1
2
3
4
5
6
7
8
9
10
11
root@db 16:32:  [aaaa]> insert into optimize_tb1 (select * from testfororder limit 1000000);
Query OK, 1000000 rows affected (14.49 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@db 16:32:  [aaaa]> select count(*) from optimize_tb1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.44 sec)

创建fulltext 索引

1
2
3
root@db 16:33:  [aaaa]> create fulltext index name_fullind on optimize_tb1(name);
Query OK, 0 rows affected, 1 warning (57.22 sec)
Records: 0  Duplicates: 0  Warnings: 1

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb1
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:33 optimize_tb1.frm
-rw-r-----. 1 mysql mysql  92M 11月 27 16:34 optimize_tb1.ibd

删除前50万数据

1
2
3
4
5
6
7
8
9
10
root@db 16:34:  [aaaa]> delete from optimize_tb1 limit 500000;
Query OK, 500000 rows affected (59.90 sec)

root@db 16:35:  [aaaa]> select count(*) from optimize_tb1;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.69 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb1
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:33 optimize_tb1.frm
-rw-r-----. 1 mysql mysql  92M 11月 27 16:36 optimize_tb1.ibd

session1执行对表进行优化操作

1
2
3
root@db 16:36:  [aaaa]> optimize table optimize_tb1;
Query OK, 500000 rows affected (55.85 sec)
Records: 500000  Duplicates: 0  Warnings: 0

session2对表插入数据

1
2
3
4
root@db 16:37:  [aaaa]> insert into optimize_tb1(id,name,age) values(8166638,'lucy1',24);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 16:37:  [aaaa]> insert into optimize_tb1(id,name,age) values(8166638,'lucy1',24);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3查看mysql线程

1
2
3
4
5
6
7
8
9
root@db 16:37:  [(none)]> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                             |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
|  2 | root | localhost | aaaa | Query   |    9 | copy to tmp table               | optimize table optimize_tb1                                      |
|  4 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                                                 |
|  5 | root | localhost | aaaa | Query   |    8 | Waiting for table metadata lock | insert into optimize_tb1(id,name,age) values(8166638,'lucy1',24) |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb1
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:37 optimize_tb1.frm
-rw-r-----. 1 mysql mysql  48M 11月 27 16:38 optimize_tb1.ibd

在存在fulltext索引的情况下，数据插入失败，提示锁等待超时。

7、表中没有任何索引

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
root@db 16:38:  [aaaa]> create table optimize_tb2 like optimize_tb;
Query OK, 0 rows affected (0.44 sec)

root@db 16:39:  [aaaa]> show create table optimize_tb2;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb2 | CREATE TABLE `optimize_tb2` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@db 16:40:  [aaaa]> alter table optimize_tb2 modify id int(7);
Query OK, 0 rows affected (1.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 16:40:  [aaaa]> alter table optimize_tb2 drop primary key;
Query OK, 0 rows affected (1.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db 16:40:  [aaaa]> show create table optimize_tb2;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimize_tb2 | CREATE TABLE `optimize_tb2` (
  `id` int(7) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

插入数据

1
2
3
4
5
6
7
8
9
10
11
root@db 16:40:  [aaaa]> insert into optimize_tb2 (select * from testfororder limit 1000000);
Query OK, 1000000 rows affected (16.74 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@db 16:41:  [aaaa]> select count(*) from optimize_tb2;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.49 sec)

查看数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb2
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:40 optimize_tb2.frm
-rw-r-----. 1 mysql mysql  56M 11月 27 16:41 optimize_tb2.ibd

删除前50万数据

1
2
3
4
5
6
7
8
9
10
root@db 16:42:  [aaaa]> delete from optimize_tb2 limit 500000;
Query OK, 500000 rows affected (3.96 sec)

root@db 16:42:  [aaaa]> select count(*) from optimize_tb2;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.32 sec)

数据文件大小

1
2
3
[root@dax-mysql-master aaaa]# ls -lhtr|grep optimize_tb2
-rw-r-----. 1 mysql mysql 8.5K 11月 27 16:40 optimize_tb2.frm
-rw-r-----. 1 mysql mysql  56M 11月 27 16:42 optimize_tb2.ibd

session1执行优化表操作

1
2
3
root@db 16:42:  [aaaa]> optimize table optimize_tb2;
Query OK, 500000 rows affected (23.61 sec)
Records: 500000  Duplicates: 0  Warnings: 0

session2执行插入数据操作

1
2
root@db 16:43:  [aaaa]> insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3查看mysql线程

1
2
3
4
5
6
7
8
9
root@db 16:43:  [(none)]> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                             |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
|  2 | root | localhost | aaaa | Query   |    7 | copy to tmp table               | optimize table optimize_tb2                                      |
|  4 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                                                 |
|  5 | root | localhost | aaaa | Query   |    6 | Waiting for table metadata lock | insert into optimize_tb2(id,name,age) values(8166638,'lucy1',24) |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

在没有任何索引的情况下，插入数据锁等待

8、结论

当mysql环境变量 old_alter_table为enabled（默认为disabled）或者 mysql启动参数添加了skip-new选项时，运行optimize table命令，会使用table copy的方式重建表，无论表中有无索引，此时online ddl是不可用的；
当mysql环境变量old_alter_table为disabled（默认为disabled）或者 mysql启动参数没有添加了skip-new选项时，运行optimize table命令，当表中不存在fulltext索引时，online ddl可用，当表中存在fulltext索引是，online ddl不可用。

mysql
目录
  1. 1. 0、相应官方文档
  2. 2. 1、带有主键索引
  3. 3. 2、带有fulltext索引
  4. 4. 3、表不存在任何索引
  5. 5. 4、当mysql环境变量 old_alter_table为enabled（默认为off）或者 mysql启动参数添加了skip-new选项时，运行optimize table命令，会使用table copy的方式重建表，此时online ddl是不可用的，下面添加skip-new参数重新进行测试。
  6. 6. 5、带有主键索引
  7. 7. 6、表中带有fulltext索引
  8. 8. 7、表中没有任何索引
  9. 9. 8、结论

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog