Home > database, mysql > mysql给同一个表添加多个索引的测试

mysql给同一个表添加多个索引的测试

October 25th, 2010 Leave a comment Go to comments

分别给xc表添加ind_name和ind_status的索引:

root@test 11:44:13>create index ind_name on xc(name);
Query OK, 6815744 rows affected (1 min 43.75 sec)
Records: 6815744  Duplicates: 0  Warnings: 0

root@test 01:53:31>create index ind_status on xc(status);
Query OK, 6815744 rows affected (2 min 4.26 sec)
Records: 6815744  Duplicates: 0  Warnings: 0

root@test 11:15:47>alter table xc add index ind_name(name);
Query OK, 6815744 rows affected (1 min 44.40 sec)
Records: 6815744  Duplicates: 0  Warnings: 0

root@test 11:20:01>alter table xc add index ind_status(status);
Query OK, 6815744 rows affected (2 min 11.50 sec)
Records: 6815744  Duplicates: 0  Warnings: 0
共:3 min 55.90 sec

将两个ddl语句合并起来的添加或删除索引:

root@test 11:32:18>alter table xc add index ind_name(name),add index ind_status(status);
Query OK, 6815744 rows affected (1 min 58.07 sec)
Records: 6815744  Duplicates: 0  Warnings: 0

root@test 11:40:34>alter table xc drop index ind_name,drop index ind_status;
Query OK, 6815744 rows affected (1 min 28.48 sec)
Records: 6815744  Duplicates: 0  Warnings: 0
可以看到在添加或者删除secondary index的时候,合并为一个子句的和分开写的ddl语句的执行时间差不多为两倍,原因在于add index的时候,innodb扫描原表,在内存或者临时文件中对行进行排序,合起来的ddl语句只要对原表扫描一次完成操作,分开写的ddl语句需要完成两次扫描操作。在对大表进行索引的添加和删除的时候,这种方法屡试不爽。

Categories: database, mysql Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.