如果我为已在覆盖索引中编入索引的单个字段创建单独的索引，我会观察到性能提升吗? [英] Would I observe a performance increase if I created a separate index for a single field that was already indexed in a covering index?

问题描述

我有一个覆盖索引，其中包含字段 a、b 和 c，按顺序排列.

I have a covering index that encompasses field a b and c, in that sequence.

ALTER TABLE table ADD INDEX covering(a, b, c)

以下查询使用覆盖索引，更具体地说，只是覆盖索引中的第一个字段.

The following query uses the covering index, more specifically just the first field in the covering index.

SELECT * FROM table
GROUP BY a

如果我创建另一个专门包含 a 的索引，我会观察到性能提升吗?

Would I observe a performance increase if I created another index that exclusively encompassed a?

ALTER TABLE table ADD INDEX a(a)

或者上述索引的工作方式是否与SELECT语句中的覆盖索引完全相同?

Or does the above index work exactly the same way as the covering index on the SELECT statement?

如果我的查询 GROUP 所查询的字段不是覆盖索引中的第一个字段，该怎么办?

What if my query was GROUP'ing on a a field that was not the first in sequence in the covering index?

SELECT * FROM table
GROUP BY b

我的索引是

ALTER TABLE table ADD INDEX b(b)

推荐答案

如果您在 (a, b, c) 上有复合索引并使用 ORDER BY a，然后它将工作并使用该索引.仅在 (a) 上添加索引将使查询计划器更可取，但它会很浪费，因为您必须在索引中保留更多数据，并且在表更新时还要更新两个索引.使用更窄的索引会带来很小的收益，但可能难以量化这种优势.

If you have compound index on (a, b, c) and use ORDER BY a, then it will work and make use of that index. Adding index on just (a) will make it more preferable to query planner, but it will be wasteful as you have to keep more data in indexes, and also to update both indexes when table gets updated. There will be small gain from using narrower index, but it could be difficult to quantify that advantage.

对于使用ORDER BY b查询，不能使用上述任何索引，必须在(b)上创建索引或在(b, ...) 以获得合理的性能.

As for querying using ORDER BY b, it could not use any of the indexes above, and you must create index on (b) or compound index on (b, ...) to get reasonable performance.

这篇关于如果我为已在覆盖索引中编入索引的单个字段创建单独的索引，我会观察到性能提升吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！