摘要：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语句也支持对分区表进行优化。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。