具有相同列的两个重复索引 [英] Two duplicate indexes with the same columns

问题描述

我正在查看数据库中的一个表(我没有创建该表)，并且看到有两个完全相同的索引(我不知道为什么这样做)，只是命名不同，这会对桌子产生负面影响吗?

I am looking at a table in our database (I didn't make the table), and I see that there are 2 indexes that are exactly the same (I don't know why this was done) just named differently, can this have any negative affect on the table?

使用此示例表:

create table mytable(
    mytable_id int unsigned primary key auto_increment,
    user_id int unsigned,
    amount decimal(12,2),
    index user_id_idx(user_id),
    index user_id_2(user_id)
);

推荐答案

是的，它可能会起作用.

Yes, it can have an effect.

当然，如果使用这两个索引，它们会占用磁盘和内存的额外空间.

Of course the two indexes take extra space on disk and also in memory if they are used.

但是它们还会使查询优化器做更多的工作来计算每个SELECT期间每个索引的收益.您拥有的索引越多，需要比较的案例就越多.因此，消除真正的冗余索引很有意义.

But they also cause the query optimizer to do more work to calculate the benefit of each index during every SELECT. The more indexes you have, the more cases it has to compare. So it pays off to eliminate truly redundant indexes.

正如其他人也指出的那样，索引是在INSERT/UPDATE/DELETE操作期间更新的，因此拥有的索引越多，表示的开销就越大.经常使用的索引证明了它们自己的开销是合理的，但是重复的索引会占用更多的开销，而没有其他好处可以匹配.

As others have also noted, indexes are updated during INSERT/UPDATE/DELETE operations, so the more indexes you have, the more overhead this represents. Indexes that get a lot of use justify their own overhead, but duplicate indexes take more overhead with no additional benefit to match.

如果您有兴趣，Percona Toolkit会提供一个工具 pt-duplicate-key-checker ，它会在您的所有表格中搜索这种情况.

If you're interested, Percona Toolkit has a tool pt-duplicate-key-checker that searches all your tables for cases like this.

这篇关于具有相同列的两个重复索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！