InnoDB复合索引顺序和INSERT性能 [英] InnoDB composite indexing order and INSERT performance

问题描述

我有一个包含3列的MySQL表，我想在该表上使用多列索引.列A为TINYINT，B为SMALLINT，C为VARBINARY(16).我是否应该将索引用作A，B，C，因为A的粒度低于B，B的粒度低于C，以实现最大的INSERT速度?

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?

推荐答案

(注意:此答案澄清或不同意已经写过的一些评论.)

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

DELETEs由于删除索引条目而减慢了速度. UPDATEs 可能会变慢-这取决于是否更改了索引列.

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和DELETEs，但不是INSERTs，需要查找该行；为此，索引可能会有所帮助.

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

如果要检查UNIQUE索引，则INSERT会受到额外的伤害.

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

次要密钥(在InnoDB中)，UNIQUE密钥的除外，在延迟"中被更新(通常是由于INSERT和DELETE，但可能是由于UPDATE)通过称为更改缓冲区"的方式.这有效地推迟了更新索引的时间，但仍保持索引完全可用.

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.

所有这些都不受索引中列顺序的影响.但是，如果索引大于可在RAM中缓存的索引，则缓存"将起作用，并且可能会或可能不会涉及I/O.但这是另一个话题.

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.

这篇关于InnoDB复合索引顺序和INSERT性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！