关于我

叶金荣（yejr）早年曾混迹于linuxforum、linuxsir、chinaunix等社区。

2006年建本站至今，是国内最早的一批MySQL技术博客之一。

从事过LAMP开发，后成为专职MySQL DBA，擅长MySQL优化、数据库架构设计及对比基准压测。

2012年被提名成为ORACLE MySQL ACE。

2015年创办靠谱、优质的在线培训品牌知数堂专注培养优秀互联网从业人才以及企业服务。

2018年发起「3306π」社区

微信公众号：老叶茶馆(imysql_wx)

微博：@叶金荣

QQ群： 579036588

邮箱：yejr@qq.com

神器： 自动生成my.cnf

搜索引擎中的我：谷歌Google、360搜索、百度、搜狗。

买茶找我： 自家茶叶店

【老叶茶馆】微信公众号二维码

[MySQL优化案例]系列 -- OPTIMIZE的威力

Submitted by yejr on 周二, 2007/08/14 - 21:39

1、先来看看多次删除插入操作后的表索引情况

mysql> SHOW INDEX FROM `tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | FlowID     |            1 | FlowID      | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerCount |            1 | WagerCount  | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            1 | WagerID     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            2 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            3 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            1 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            2 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            1 | WagerID     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            2 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            3 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.01 sec)

2、优化表

mysql> optimize table tbl_name;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.tbl_name | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (40.60 sec)

3、再来看看优化后的效果

mysql> SHOW INDEX FROM `tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |      172462 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | FlowID     |            1 | FlowID      | A         |       86231 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerCount |            1 | WagerCount  | A         |        4311 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            1 | WagerID     | A         |       86231 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            2 | StepType    | A         |      172462 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            3 | ParamResult | A         |      172462 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            1 | StepType    | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            2 | ParamResult | A         |       86231 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            1 | WagerID     | A         |       86231 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            2 | StepType    | A         |      172462 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            3 | ParamResult | A         |      172462 |      255 | NULL   |      | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

最后，来看看手册中关于 OPTIMIZE 的描述：

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

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用
OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新
利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次
即可，只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。