Q:
I read from many places that disabling index before loading a data table can significantly speed up the importing process. But innodb does not support "disable index". Particularly, I got a warning message after running
ALTERTABLE mytable DISABLE KEYS;
+-------+------+-------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------+ | Note | 1031 | Table storage engine for 'mytable' doesn't have this option | +-------+------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
How do I disable index in the innodb engine? Or are there any other alternatives to avoid using index when loading data from an outfile?
A:
There is a very good reason why you cannot execute DISABLE KEYS on an InnoDB. InnoDB is not designed it use it. MyISAM is.
In fact, here is what happens when you reload a mysqldump:
You will see a CREATE TABLE for a MyISAM table following by a write lock.
Before all the bulk inserts are run, a call to ALTER TABLE ... DISABLE KEYS is done.
What this does is turn off secondary indexes in the MyISAM table.
Then, bulk inserts are done. While this is being done, the PRIMARY KEY and all UNIQUE KEYS in the MyISAM table. Before the UNLOCK TABLEs, a call ALTER TABLE ... ENABLE KEYS is done in order to rebuild all nonunique indexes linearly.
IMHO this operation was not coded into the InnoDB Storage Engine because all keys in a nonunique index come with the primary key entry from gen_clust_index (aka Clustered Index). That would be a very expensive operation since building a nonunique index would require O(n log n) running time to retrieve each unique key to attach to a nonunique key.
In light of this, posting a warning about trying to DISABLE KEYS/ENABLE KEYS on an InnoDB table is far easier than coding exceptions to the mysqldump for any special cases involving non-MyISAM storage engines.
参考:
http://stackoverflow.com/questions/9524938/how-to-disable-index-in-innodb/9525780#9525780