创建索引可以使用现有索引吗? [英] Can index creation use existing indexes?

问题描述

我在 A、B 和 C 列上有单独的索引.我想在 A+B+C 三个列上创建一个复合索引.

I have individual indexes on, say, columns A, B and C. I'd like to create a composite index on the three columns A+B+C.

我的现有索引对复合索引创建有什么影响?数据库是否会利用它们，它们是无关紧要的，还是会减慢我的新组合索引的创建速度?

What impact will my existing indexes have on composite index creation? Will the database take advantage of them, are they irrelevant, or will they slow down the creation of my new composite index?

我使用的是 MySql 5.1.

I'm using MySql 5.1.

编辑:顺便说一句，该表有几百万行.

EDIT: BTW the table has several million rows.

EDIT 2:感谢 tster 的建议:我在一个小得多的表(诚然只有 20,000 行)上尝试了这个，但即便如此，当个别索引已经存在.

EDIT 2: thanks to tster for the suggestion: I tried this out on a much smaller table (admittedly just 20,000 rows) but even so the creation of a new composite index took noticeably longer when the individual indexes were already present.

推荐答案

当您添加索引时，MySQL 通常会重建整个表，因此所有现有的也会重建.这可能很慢.

MySQL usually rebuilds the whole table when you add an index, so all the existing ones get rebuilt as well. This can be slow.

唯一的例外是使用 InnoDB 插件添加索引，但不会.

The only exception is adding an index using the InnoDB plugin, which does not.

据我所知，它在构建索引时总是进行全表扫描，但是如果您添加的索引与另一个索引具有相同(或子集)列的索引，则它可以进行索引扫描.此类索引通常仅在列的顺序不同时才有用.

As far as I know, it always does a full table scan when building an index, however it COULD do an index scan if you were adding an index which had the same (or a subset) of columns as another index. Such indexes are normally only useful if the columns are in a different order.

使用库存 mysql，您拥有的索引越多，创建新索引的速度就越慢，因为它也会重建现有索引.

Using stock mysql, the more indexes you have, the slower it will be to make a new one, as it rebuilds existing indexes too.

有了插件，我觉得没什么区别.

With the plugin, I think it makes no difference.

无论哪种方式，如果您打算添加多个索引，都应该一次添加，而不是一次添加一个.

Either way, if you're planning to add several indexes, you should do them all-at-once not one at a time.

这篇关于创建索引可以使用现有索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！