5.5下对DDL操作提速的测试

时间:2022-09-13 18:30:47

这篇文章里,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的复杂度,尤其的当二级索引很多的时候。