InnoDB引擎表删列的简单测试
时间：2010-12-15 来源：junsansi
按照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~~
首先初始化环境，按照某大对象的结构，构造测试用的包含大字段的对象： 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~~
相关阅读 更多 +
