MySQL中使用OPTIMIZE TABLE优化表

OPTIMIZE TABLE语句可以重新组织表、索引的物理存储，减少存储空间，提高访问的I/O效率。类似于碎片整理功能。

OPTIMIZE TABLE语法如下：

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] …

默认情况下，MySQL将OPTIMIZE TABLE语句写入二进制日志，以便它们复制到slave服务器。如果不想写二进制日志，使用命令时加上NO_WRITE_To_BINLOG或LOCAL关键字即可。

使用这个语句需要具有对目标表的SELECT、INSERT权限。

在MySQL 5.7里OPTIMIZE TABLE语句支持InnoDB、MyISAM、ARCHIVE引擎的表。它还支持in-memory NDB表的动态列，但不支持固定列，也不支持NDB磁盘表。

我们创建一张InnoDB引擎的表，并插入大量数据。

root@database-one 14:08:  [gftest]> create table testot(id int not null auto_increment primary key,context text) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

root@database-one 14:09:  [gftest]> insert into testot(context) values(repeat('nanjing',100));
Query OK, 1 row affected (0.00 sec)

root@database-one 14:10:  [gftest]> insert into testot(context) values(repeat('shanghai',100));
Query OK, 1 row affected (0.00 sec)

root@database-one 14:10:  [gftest]> insert into testot(context) values(repeat('beijing',100));
Query OK, 1 row affected (0.00 sec)

root@database-one 14:10:  [gftest]> insert into testot(context) values(repeat('shenzhen',100));
Query OK, 1 row affected (0.00 sec)

root@database-one 14:11:  [gftest]> insert into testot(context) select context from testot;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@database-one 14:11:  [gftest]> insert into testot(context) select context from testot;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

root@database-one 14:11:  [gftest]> insert into testot(context) select context from testot;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

root@database-one 14:11:  [gftest]> insert into testot(context) select context from testot;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

root@database-one 14:11:  [gftest]> insert into testot(context) select context from testot;
Query OK, 64 rows affected (0.01 sec)
Records: 64  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 256 rows affected (0.02 sec)
Records: 256  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 512 rows affected (0.03 sec)
Records: 512  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 1024 rows affected (0.03 sec)
Records: 1024  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 2048 rows affected (0.04 sec)
Records: 2048  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 4096 rows affected (0.10 sec)
Records: 4096  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 8192 rows affected (0.28 sec)
Records: 8192  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 16384 rows affected (0.81 sec)
Records: 16384  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 32768 rows affected (1.27 sec)
Records: 32768  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> insert into testot(context) select context from testot;
Query OK, 65536 rows affected (1.94 sec)
Records: 65536  Duplicates: 0  Warnings: 0

root@database-one 14:12:  [gftest]> commit;
Query OK, 0 rows affected (0.00 sec)

root@database-one 14:12:  [gftest]> exit
Bye
[root@database-one ~]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -lh testot*
-rw-r----- 1 mysql mysql 8.4K 3月  10 14:08 testot.frm
-rw-r----- 1 mysql mysql 120M 3月  10 14:12 testot.ibd

表中有超过13万条数据，占磁盘空间大约120M。

我们删除部分数据

root@database-one 14:14:  [gftest]> select count(*) from testot where context like 'beijing%';
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
1 row in set (0.13 sec)

root@database-one 14:15:  [gftest]> delete from testot where context like 'shenzhen%' or context like 'shanghai%';
Query OK, 65536 rows affected (0.88 sec)

root@database-one 14:16:  [gftest]> select count(*) from testot where context like 'beijing%';
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
1 row in set (0.06 sec)

root@database-one 14:16:  [gftest]> exit
Bye
[root@database-one gftest]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -lh testot*
-rw-r----- 1 mysql mysql 8.4K 3月  10 14:08 testot.frm
-rw-r----- 1 mysql mysql 120M 3月  10 14:12 testot.ibd

可以看到，删除后查询速度明显提升，但是表占用的磁盘空间没有明显变化。使用OPTIMIZE TABLE语句进行优化。

root@database-one 14:17:  [(none)]> use gftest;
Database changed
root@database-one 14:18:  [gftest]> optimize table testot;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| gftest.testot | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| gftest.testot | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1.94 sec)

root@database-one 14:18:  [gftest]> select count(*) from testot where context like 'beijing%';
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
1 row in set (0.06 sec)

root@database-one 14:22:  [gftest]> exit
Bye
[root@database-one gftest]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -lh testot*
-rw-r----- 1 mysql mysql 8.4K 3月  10 14:18 testot.frm
-rw-r----- 1 mysql mysql  64M 3月  10 14:18 testot.ibd

可以看到，表占用磁盘空间减少了大约一半，跟我们删除的数据量相匹配。在这里要注意几点：

• 对于InnoDB的表，MySQL实现原理其实是在线重建了表及其索引，并重新收集了统计信息。
• OPTIMIZE TABLE前后查询速度变化不明显，其实是因为本例中表的数据量、索引情况、分散情况等影响，随着这些情况的变化，OPTIMIZE TABLE后的性能优化就能显现出来了。

OPTIMIZE TABLE语句也支持对分区表进行优化。