InnoDB composite indexing order and INSERT perform

问题:

I have a MySQL table with 3 columns on which I'd like to use a multi-column index. Column A is TINYINT, B is SMALLINT and C is VARBINARY (16). Should I use the index as A, B, C, because A has lower granularity than B and B lower than C to achieve maximum INSERT speed?

回答1:

(Note: This answer clarifies or disagrees with some of the comments already written.)

DELETEs are slowed down because of deleting the index entries. UPDATEs may be slowed down -- it depends on whether an indexed column is changed.

SELECTs, UPDATEs, and DELETEs, but not INSERTs, need to find the row(s); for this, an index may help a lot.

An INSERT is hurt an extra amount if there is a UNIQUE index to check.

Secondary keys (in InnoDB), except for UNIQUE keys, are updated (usually due to INSERT and DELETE, but possibly due to UPDATE) in a 'delayed' way via what is called the "Change Buffer". This effectively puts off updating the index, but still keeps the index fully usable.

None of this is impacted by the order of the columns in an index. However, if an index is bigger than can be cached in RAM, "caching" comes into play, and I/O may or may not be involved. But that is another topic.

In general the benefit from an index for reading far outweighs the slowdown for write operations.

回答2:

Indexes actually slow data modification queries (insert, update, delete) down, since the rdbms has to change not only the table itself, but the index(es) as well.

From an insertion speed point of view, the order of fields in an index is not relevant, it is the number of indexes that matter.