决定何时在数据库中的表列上创建索引？ [英] Decision when to create Index on table column in database?

问题描述

我不是db家伙。但我需要创建表并对它们执行CRUD操作。如果我默认在
上创建所有列的索引，我会感到困惑吗？这是我在创建索引时考虑的理解。

I am not db guy. But I need to create tables and do CRUD operations on them. I get confused should I create the index on all columns by default or not? Here is my understanding which I consider while creating index.

索引基本上包含内存位置范围（起始内存位置，其中第一个值存储到最终内存位置，最后一个值是
存储）。因此，当我们在表索引中插入任何值时，需要更新列，因为它还有一个值，但更新列
值不会对索引值产生任何影响。 对吗？所以底线是当我的列用于两个表之间的连接时，我们应该考虑
在连接中使用的列上创建索引但是可以跳过所有其他列，因为如果我们创建索引当在列中插入新值时，它将涉及额外成本
更新索引值。对吗？

Index basically contains the memory location range ( starting memory location where first value is stored to end memory location where last value is stored). So when we insert any value in table index for column needs to be updated as it has got one more value but update of column value wont have any impact on index value. Right? So bottom line is when my column is used in join between two tables we should consider creating index on column used in join but all other columns can be skipped because if we create index on them it will involve extra cost of updating index value when new value is inserted in column.Right?

考虑这种情况table mytable 包含两列三列，即 col1 ， col2 ， COL3 。现在我们触发此查询

Consider this scenario where table mytable contains two three columns i.e col1,col2,col3. Now we fire this query

select col1,col2 from mytable

现在有两种情况。在第一种情况下，我们在 col1 和 col2 上创建索引。在第二种情况下，我们不创建任何索引。**根据我的理解，
情况1将比case2快，因为在情况1中我们oracle可以快速找到列内存位置。所以这里我没有使用任何连接列，但
仍然索引在这里帮助。那么我应该考虑在这里创建索引吗？**

Now there are two cases here. In first case we create the index on col1 and col2. In second case we don't create any index.** As per my understanding case 1 will be faster than case2 because in case 1 we oracle can quickly find column memory location. So here I have not used any join columns but still index is helping here. So should I consider creating index here or not?**

如果在上面的相同情况下我们开火怎么办

What if in the same scenario above if we fire

select * from mytable

而不是

select col1,col2 from mytable

这里有索引帮助吗？

推荐答案

但是更新列值不会对索引值产生任何影响。对吗？

but update of column value wont have any impact on index value. Right?

没有。更新索引列会产生影响。 Oracle 11g 性能手册指出：

No. Updating an indexed column will have an impact. The Oracle 11g performance manual states that:

修改索引列的UPDATE语句和修改索引表的INSERT和DELETE
语句所需的时间比$ b更长$ b没有索引。此类SQL语句必须修改
表中的索引和数据中的数据。他们还创建了额外的撤消和重做。

UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes and data in tables. They also create additional undo and redo.

---

---

所以底线是当我的列用于两个表之间的连接时，我们应该考虑在连接中使用的列上创建索引但是可以跳过所有其他列，因为如果我们在它们上创建索引，则当新值为新值时将涉及更新索引值的额外成本插入列中。对吗？

So bottom line is when my column is used in join between two tables we should consider creating index on column used in join but all other columns can be skipped because if we create index on them it will involve extra cost of updating index value when new value is inserted in column. Right?

不只是插入而是任何其他数据操作语言语句。

Not just Inserts but any other Data Manipulation Language statement.

考虑这种情况。 。 。这里索引会有帮助吗？

Consider this scenario . . . Will index help here?

关于这最后一段，为什么不构建一些具有代表性数据量的测试用例，以便证明或反驳你应该索引哪些列的假设？

With regards to this last paragraph, why not build some test cases with representative data volumes so that you prove or disprove your assumptions about which columns you should index?

这篇关于决定何时在数据库中的表列上创建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！