在这篇文章里，Peter讲述了一个例子，通过不同的办法做optimize，效率居然相差有20倍。在INNODB里，optimize table 操作会被重写为recreate + analyze，表被重建，这和做alter table的过程相似。下面测试下这个方法对于alter table 是不是同样有效,从理论上来说，plugin innodb 或5.6里增加的fast index creation特性，增加删除索引都不做load data操作，应该会快一些。
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
测试目的：在对大表进行alter table的时候怎么样提速
环境：mysql5.5.20
测试方法2种：
1）正常流程
2）先drop all secondly index,then alter table,then add all secondly index.看看哪种方法快。
测试：添加字段
第一种办法：直接对表做alter table 操作
表结构：
mysql> insert into SMSMT select * from ng30.SMSMT limit 1000000;Query OK, 1000000 rows affected, 2 warnings (22 min 1.87 sec)Records: 1000000 Duplicates: 0 Warnings: 2 mysql> show index from SMSMT; +-------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | SMSMT | 0 | PRIMARY | 1 | ID | A | 998701 | NULL | NULL | | BTREE | | | | SMSMT | 0 | PRIMARY | 2 | BusinessId | A | 998701 | NULL | NULL | | BTREE | | | | SMSMT | 1 | MFCAT | 1 | CampaignId | A | 3579 | NULL | NULL | YES | BTREE | | | | SMSMT | 1 | MFCAT | 2 | MessageTo | A | 3579 | NULL | NULL | YES | BTREE | | | | SMSMT | 1 | idx_Bid_DDelivered | 1 | BusinessId | A | 769 | NULL | NULL | | BTREE | | | | SMSMT | 1 | idx_Bid_DDelivered | 2 | ActualDeliveryTime | A | 998701 | NULL | NULL | YES | BTREE | | | | SMSMT | 1 | idx_Bid_DDelivered | 3 | MessageSent | A | 998701 | NULL | NULL | | BTREE | | | | SMSMT | 1 | CustomerId | 1 | CustomerId | A | 499350 | NULL | NULL | YES | BTREE | | | | SMSMT | 1 | idx_Time | 1 | ConfirmedTime | A | 21 | NULL | NULL | YES | BTREE | | | | SMSMT | 1 | idx_Time | 2 | AppointmentId | A | 21 | NULL | NULL | YES | BTREE | | | | SMSMT | 1 | idx_Time | 3 | ScheduledTime | A | 332900 | NULL | NULL | YES | BTREE | | | +-------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 11 rows in set (0.27 sec)
方法1：
mysql> alter table SMSMT add column bruce int; Query OK, 1000000 rows affected (19 min 53.43 sec) Records: 1000000 Duplicates: 0 Warnings: 0
耗时20分钟
此时processlist按照标准流程做alter table操作
mysql> show processlist; +------+------+-------------------+-------+---------+------+-------------------+----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-------------------+-------+---------+------+-------------------+----------------------------------------+ | 1144 | root | localhost | bruce | Query | 0 | NULL | show processlist | | 1152 | root | localhost | bruce | Query | 462 | copy to tmp table | alter table SMSMT add column bruce int | | 1159 | d3 | 10.20.0.250:53569 | ng6 | Sleep | 2359 | | NULL | +------+------+-------------------+-------+---------+------+-------------------+----------------------------------------+ 3 rows in set (0.07 sec)
方法2：
mysql> alter table SMSMT drop column bruce; Query OK, 1000000 rows affected (18 min 29.24 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> alter table SMSMT drop index MFCAT,drop index idx_Bid_DDelivered,drop index CustomerId,drop index idx_Time; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from SMSMT; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | SMSMT | 0 | PRIMARY | 1 | ID | A | 955211 | NULL | NULL | | BTREE | | | | SMSMT | 0 | PRIMARY | 2 | BusinessId | A | 955211 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.11 sec) 添加字段，添加索引 mysql> alter table SMSMT add column bruce int; Query OK, 1000000 rows affected (16 min 29.96 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> alter table SMSMT add index MFCAT(CampaignId,Messageto),add index idx_Bid_DDelivered(BusinessId,ActualDeliveryTime,MessageSent),add index CustomerId(CustomerId),add index idx_Time(ConfirmedTime,AppointmentId,ScheduledTime); Query OK, 0 rows affected (1 min 35.86 sec) Records: 0 Duplicates: 0 Warnings: 0
从上面的结果看到，方法1用时20分钟，方法2用时16.5+1.5=18分钟。方法2节约2分钟从这个结果来看，方法2节约的2分钟，相对总时间20分钟来说，效率提高并不大，但是增加了DDL的复杂度，尤其的当二级索引很多的时候。