mysql optimizetable,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语句也支持对分区表进行优化。