你怎么知道什么是好的指数？ [英] How do you know what a good index is?

问题描述

在Oracle中使用表时，您如何知道何时设置好的索引和坏的索引？

When working with tables in Oracle, how do you know when you are setting up a good index versus a bad index?

推荐答案

这取决于你是什么意思的好和坏。基本上你需要意识到，添加的每个索引将提高该列的任何搜索的性能（因此添加索引到人员表的lastname列将提高在其中有lastname =的查询的性能），但是

This depends on what you mean by 'good' and 'bad'. Basically you need to realise that every index you add will increase performance on any search by that column (so adding an index to the 'lastname' column of a person table will increase performance on queries that have "where lastname = " in them) but decrease write performance across the whole table.

这样做的原因是当您添加或更新行时，它必须添加或更新表本身和每个索引该行是的成员。因此，如果一个表上有五个索引，每个添加必须写入六个位置 - 五个索引和表 - 在最坏的情况下，更新可能会触及六个位置。

The reason for this is when you add or update a row, it must add-to or update both the table itself and every index that row is a member of. So if you have five indexes on a table, each addition must write to six places - five indexes and the table - and an update may be touching up to six places in the worst case.

索引创建是查询速度和写入速度之间的平衡操作。在某些情况下，如在一个隔夜作业中每周只加载数据一次，但每天查询数千次的数据集市，使用重载索引和尽可能加快查询速度是非常有意义的。然而，在在线事务处理系统的情况下，您想要尝试在它们之间找到一个平衡。

Index creation is a balancing act then between query speed and write speed. In some cases, such as a datamart that is only loaded with data once a week in an overnight job but queried thousands of times daily, it makes a great deal of sense to overload with indexes and speed the queries up as much as possible. In the case of online transaction processing systems however, you want to try and find a balance between them.

简而言之，将索引添加到很多使用选择查询，但尝试避免添加太多，因此首先添加最常用的列。

So in short, add indexes to columns that are used a lot in select queries, but try to avoid adding too many and so add the most-used columns first.

之后，它是一个负载测试，以了解性能如何反应生产条件，以及大量的调整，以找到一个协调的平衡。

After that its a matter of load testing to see how the performance reacts under production conditions, and a lot of tweaking to find an aceeptable balance.

这篇关于你怎么知道什么是好的指数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！