具有多列索引时是否需要单列索引? [英] Is a single-column index needed when having multicolumn index?

问题描述

我被一个设计不良的系统所吸引.现在，我正在他们的数据库上执行DBA，并且遇到很多类似以下情况(伪代码)的情况:

I've been dropped in a system that was poorly designed. Now I'm doing DBA on their DB, and I have a lot of situation like the following (Pseudo-code):

Table t1{
c1;
c2;
c3;
c4;

key(c1);
key(c2);
key(c1,c2);
key(c1,c2,c3);}

单列索引真的必要吗，因为我已经有一个包含这些列的多列了?

Are the single column indexes really necessary, since I already have a multicolumn one containing those columns?

或者，另一方面-因为我已经有了单列，所以需要多行列吗?

Or on the other hand - is the multiline column needed since I already have the single column ones?

推荐答案

文档页面中有关

如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行.例如，如果在(col1，col2，col3)上具有三列索引，则在(col1)，(col1，col2)和( col1，col2，col3).有关更多信息，请参见第8.3.5节"-列索引" .

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). For more information, see Section 8.3.5, "Multiple-Column Indexes".

最好删除(c1)和(c1,c2)上的索引.它们未使用，但是它们会占用存储空间，并会消耗处理器功率，以便在表数据更改时保持最新状态.

You better remove the indexes on (c1) and (c1,c2). They are not used but they use storage space and consume processor power to be kept up-to-date when the table data changes.

这篇关于具有多列索引时是否需要单列索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！