InnoDB の insert buffer

INSERT 処理のスループットについて、ユニーク制約のないインデックスについては、ディスクに落ちてても大きな問題にならないらしい。
逆に言うと、セカンダリインデックスについては、無駄に UNIQUE とかつけるべきではない。

9.4.4. Issues with the Insert Buffer


Secondary indexes are usually non-unique, and insertions into secondary indexes happen in a relatively random order. This would cause a lot of random disk I/O operations without a special mechanism used in InnoDB called the insert buffer.


When a record is inserted into a non-unique secondary index page that is not in the buffer pool, InnoDB will insert the record into a special B-tree: the insert buffer. Periodically, the insert buffer is merged into the secondary index trees in the database. A merge will also occur whenever a secondary index page is loaded to the buffer pool.

MySQL