按照InnoDB引擎对象结构修改的原理，对象涉及到结构修改(比如增列删列创建索引等)，均会导致整个对象的重建，超大对象的修改会触发产生大量工作。但是不确认的是，对于删列操作，比如删除表中列时，如果在操作前先将列值清空，是否会对删列的DDL操作效率有提升，下面通过实际测试来检验一番。
测试目的：验证删列前将列值修改为空，是否会起到提升删除速度的效果
测试结果：并无明显助益。
测试步骤如下：
提示：测试机IO性能较差，测试结果可能存在误差
首先初始化环境，按照某大对象的结构，构造测试用的包含大字段的对象：
mysql> desc doc_original;
+-----------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+---------------+------+-----+---------------------+----------------+
| DOC_ID | bigint(12) | NO | PRI | NULL | auto_increment |
| DOC_CLASS | bigint(12) | YES | | NULL | |
| DOC_TITLE | varchar(255) | YES | | NULL | |
| DOC_CREATOR_USER_ID | bigint(16) | NO | | NULL | |
| DOC_CREATOR_USER_NICK | varchar(64) | NO | | NULL | |
| DOC_LATEST_EDITION | bigint(12) | YES | | NULL | |
| DOC_LATEST_URL | varchar(255) | YES | | NULL | |
| DOC_TEXT | mediumtext | YES | | NULL | |
| DOC_CREATED_TIME | timestamp | NO | | 0000-00-00 00:00:00 | |
| DOC_LATEST_EDITION_TIME | timestamp | NO | | 0000-00-00 00:00:00 | |
| DOC_INNER_PIC_COUNT | bigint(12) | YES | | 0 | |
| DOC_OUTER_PIC_COUNT | bigint(12) | YES | | 0 | |
| DOC_KEYWORDS | varchar(512) | YES | | NULL | |
| DOC_CLICK_COUNT | bigint(12) | NO | | 0 | |
| DOC_HIS_COUNT | bigint(12) | YES | | 0 | |
| DOC_ATTENDEE_COUNT | bigint(12) | YES | | 0 | |
| DOC_SUMMARY | varchar(1000) | YES | | NULL | |
| DOC_STATE | tinyint(1) | YES | | NULL | |
| DOC_GOODCOUNT | bigint(12) | YES | | 0 | |
| DOC_BADCOUNT | bigint(12) | YES | | 0 | |
| DOC_FIRST_IMG | varchar(255) | YES | | NULL | |
| DOC_TITLE_UPPER | varchar(255) | NO | UNI | NULL | |
| DOC_IMPORT_TAG | int(4) | YES | | NULL | |
| DOC_TOPIC_COUNT | int(6) | YES | | 0 | |
| DOC_POST_COUNT | int(8) | YES | | 0 | |
| DOC_CREATOR_USER_ID_ENCRYPT | varchar(32) | NO | | NULL | |
| DOC_ID_ENCRYPT | varchar(32) | YES | | NULL | |
| DOC_SCORE | tinyint(2) | YES | | NULL | |
| LAST_EDIT_USER_ID_EN | varchar(32) | YES | | NULL | |
| CHAMPION_USER_NICK | varchar(64) | YES | | NULL | |
| CHAMPION_USER_ID_EN | varchar(32) | YES | | NULL | |
| CHAMPION_CREDIT | int(8) | YES | | NULL | |
+-----------------------------+---------------+------+-----+---------------------+----------------+
32 rows in set (0.00 sec)
mysql> desc doc_new;
+-----------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+---------------+------+-----+---------------------+----------------+
| DOC_ID | bigint(12) | NO | PRI | NULL | auto_increment |
| DOC_CLASS | bigint(12) | YES | | NULL | |
| DOC_TITLE | varchar(255) | YES | | NULL | |
| DOC_CREATOR_USER_ID | bigint(16) | NO | | NULL | |
| DOC_CREATOR_USER_NICK | varchar(64) | NO | | NULL | |
| DOC_LATEST_EDITION | bigint(12) | YES | | NULL | |
| DOC_LATEST_URL | varchar(255) | YES | | NULL | |
| DOC_TEXT | mediumtext | YES | | NULL | |
| DOC_CREATED_TIME | timestamp | NO | | 0000-00-00 00:00:00 | |
| DOC_LATEST_EDITION_TIME | timestamp | NO | | 0000-00-00 00:00:00 | |
| DOC_INNER_PIC_COUNT | bigint(12) | YES | | 0 | |
| DOC_OUTER_PIC_COUNT | bigint(12) | YES | | 0 | |
| DOC_KEYWORDS | varchar(512) | YES | | NULL | |
| DOC_CLICK_COUNT | bigint(12) | NO | | 0 | |
| DOC_HIS_COUNT | bigint(12) | YES | | 0 | |
| DOC_ATTENDEE_COUNT | bigint(12) | YES | | 0 | |
| DOC_SUMMARY | varchar(1000) | YES | | NULL | |
| DOC_STATE | tinyint(1) | YES | | NULL | |
| DOC_GOODCOUNT | bigint(12) | YES | | 0 | |
| DOC_BADCOUNT | bigint(12) | YES | | 0 | |
| DOC_FIRST_IMG | varchar(255) | YES | | NULL | |
| DOC_TITLE_UPPER | varchar(255) | NO | UNI | NULL | |
| DOC_IMPORT_TAG | int(4) | YES | | NULL | |
| DOC_TOPIC_COUNT | int(6) | YES | | 0 | |
| DOC_POST_COUNT | int(8) | YES | | 0 | |
| DOC_CREATOR_USER_ID_ENCRYPT | varchar(32) | NO | | NULL | |
| DOC_ID_ENCRYPT | varchar(32) | YES | | NULL | |
| DOC_SCORE | tinyint(2) | YES | | NULL | |
| LAST_EDIT_USER_ID_EN | varchar(32) | YES | | NULL | |
| CHAMPION_USER_NICK | varchar(64) | YES | | NULL | |
| CHAMPION_USER_ID_EN | varchar(32) | YES | | NULL | |
| CHAMPION_CREDIT | int(8) | YES | | NULL | |
+-----------------------------+---------------+------+-----+---------------------+----------------+
32 rows in set (0.00 sec)
初始化数据，导入百万级记录：
insert into doc_original select * from XXXX.XXXXX limit 1,1000000;
insert into doc_new select * from doc_original;
mysql> select count(0) from doc_new;
+----------+
| count(0) |
+----------+
| 1000000 |
+----------+
1 row in set (37 min 21.72 sec)
mysql> select count(0) from doc_original;
+----------+
| count(0) |
+----------+
| 1000000 |
+----------+
1 row in set (38 min 23.82 sec)
测试1，删除大字段的效率
mysql> alter table doc_original drop column DOC_TEXT;
Query OK, 1000000 rows affected (58 min 32.40 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
费时接近1小时，实际操作数据占用磁盘空间接近6G，io性能果然不是一般的差~
接下来尝试首先将表清空，而后再删队的方式，看看时间是否有提升：
mysql> update doc_new set doc_text=null;
Query OK, 999993 rows affected (1 hour 56 min 19.06 sec)
Rows matched: 1000000 Changed: 999993 Warnings: 0
mysql> alter table doc_new drop column doc_text;
Query OK, 1000000 rows affected (43 min 15.14 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
结论：由于执行时间较长，考虑到该机负载情况及前面清空数据时部分数据被缓存，应是此处时间缩短20%的主要原因，综合来看的话，个人认为先期清空大字段数据对删列的性能应无提升~
测试2，删除普通文本字段的效率
mysql> alter table doc_original drop column DOC_TITLE;
Query OK, 1000000 rows affected (11 min 37.27 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
费时约12分钟，操作的对象占用600M左右空间
mysql> update doc_new set doc_title=null;
Query OK, 1000000 rows affected (7 min 1.56 sec)
Rows matched: 1000000 Changed: 1000000 Warnings: 0
--为避免前步操作中产生缓存对后续语句执行的影响，在中间执行了多次其它大表对象的查询，以保证目标对象不在缓存区
mysql> alter table doc_new drop column doc_title;
Query OK, 1000000 rows affected (11 min 36.43 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
结论：费时约12分钟，与之前直接删除几乎没有区别。
综合：删列操作时，列值是否为空并不会对删除操作本身的效率有根本影响，倒是更新列的操作引入了额外的资源消耗，因此综合来看这种方式并不可取。
FYI~~
阅读(1188) | 评论(0) | 转发(0) |
