InnoDB の insert buffer
INSERT 処理のスループットについて、ユニーク制約のないインデックスについては、ディスクに落ちてても大きな問題にならないらしい。
逆に言うと、セカンダリインデックスについては、無駄に UNIQUE とかつけるべきではない。
9.4.4. Issues with the Insert Buffer
MySQL
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.