너무 많은 데이터베이스 인덱스가 있습니까?

너무 많은 데이터베이스 인덱스가 있습니까?

---

다소 큰 오라클 데이터베이스로 프로젝트를 진행하고 있습니다 (제 질문은 다른 데이터베이스에도 동일하게 적용됩니다). 사용자는 거의 모든 가능한 필드 조합을 검색 할 수있는 웹 인터페이스가 있습니다.

빠른 검색을 위해 사용자가 일반적으로 검색 할 것으로 예상되는 필드 및 필드 조합에 색인을 추가합니다. 그러나 고객이이 소프트웨어를 어떻게 사용할지 실제로 알지 못하기 때문에 어떤 인덱스를 만들지 알기가 어렵습니다.

공간은 문제가되지 않습니다. 우리는 4 테라 바이트의 RAID 드라이브를 가지고 있으며 그 중 일부만 사용하고 있습니다. 그러나 인덱스가 너무 많으면 성능이 저하 될 수 있습니다. 행을 추가, 삭제 또는 수정할 때마다 해당 인덱스를 업데이트해야하므로 단일 테이블에 수십 개의 인덱스가있는 것은 좋지 않은 생각입니다.

그렇다면 얼마나 많은 인덱스가 너무 많은 것으로 간주됩니까? 10? 25? 50? 아니면 정말 일반적이고 명백한 사례를 다루고 다른 모든 것을 무시해야합니까?

---

테이블에서 발생하는 작업에 따라 다릅니다.

SELECT가 많고 변경 사항이 거의없는 경우 원하는대로 색인을 생성하십시오. 이로 인해 SELECT 문이 가속화 될 수 있습니다.

UPDATEs, INSERTs + DELETEs에 의해 테이블이 크게 타격되면 ...이 작업 중 하나가 발생할 때마다 모두 수정해야하기 때문에 많은 인덱스로 인해 속도가 매우 느려집니다

말했듯이 아무것도하지 않는 테이블에 많은 무의미한 인덱스를 명확하게 추가 할 수 있습니다. 2 개의 고유 한 값을 가진 열에 B-Tree 인덱스를 추가하면 데이터 조회 측면에서 아무것도 추가하지 않으므로 의미가 없습니다. 열의 값이 고유할수록 인덱스에서 더 많은 이점을 얻을 수 있습니다.

---

나는 보통 이렇게 진행합니다.

1. 일반적인 날짜에 데이터에서 실행 된 실제 쿼리 로그를 가져옵니다 .
2. 가장 중요한 쿼리가 실행 계획에서 인덱스에 도달하도록 인덱스를 추가하십시오.
3. 업데이트 또는 삽입이 많은 인덱싱 필드를 피하십시오.
4. 몇 번의 색인 후에 새 로그를 가져 와서 반복하십시오.

모든 최적화와 마찬가지로 요청 된 성능에 도달하면 중지합니다 (이것은 분명히 포인트 0이 특정 성능 요구 사항을 얻는다는 것을 의미합니다).

---

다른 사람들은 당신에게 훌륭한 조언을 해왔습니다. 앞으로 나아갈 때 추가 제안이 있습니다. 어떤 시점에서 최상의 인덱싱 전략에 대한 결정을 내려야합니다. 결국 최고의 PLANNED 인덱싱 전략은 결국 사용되지 않는 인덱스를 만들 수 있습니다. 사용되지 않는 인덱스를 찾는 전략 중 하나는 인덱스 사용을 모니터링하는 것입니다. 다음과 같이하십시오.

alter index my_index_name monitoring usage;

그런 다음 v $ object_usage를 쿼리하여 해당 시점부터 인덱스 사용 여부를 모니터링 할 수 있습니다. 이에 대한 정보는 Oracle® Database Administrator 's Guide를 참조하십시오 .

테이블을 업데이트하기 전에 인덱스를 삭제 한 후 다시 작성하는웨어 하우징 전략이있는 경우 다시 모니터링하기 위해 인덱스를 설정해야하며 해당 인덱스에 대한 모니터링 히스토리가 유실됩니다.

---

데이터웨어 하우징에서 많은 수의 인덱스를 갖는 것이 매우 일반적입니다. 나는 200 개의 열과 190 개의 인덱스가있는 팩트 테이블로 작업했습니다.

이것에 대한 오버 헤드가 있지만 데이터웨어 하우스에서 일반적으로 행을 한 번만 삽입하고 업데이트하지는 않지만 수천 개의 SELECT 쿼리에 참여할 수 있다는 맥락에서 이해해야합니다. 열.

유연성을 극대화하기 위해 데이터웨어 하우스는 일반적으로 (압축 된) btree 인덱스를 사용할 수있는 높은 카디널리티 열을 제외하고 단일 열 비트 맵 인덱스를 사용합니다.

인덱스 유지 관리에 대한 오버 헤드는 주로 많은 블록에 쓰는 비용과 관련이 있으며 새 행에 해당 열에 대한 기존 값 범위의 "중간"에 값이 추가되면 블록이 분할됩니다. 분할 및 새로운 데이터로드를 분할 구성표에 맞게 정렬하고 직접 경로 삽입을 사용하여이를 완화 할 수 있습니다.

귀하의 질문을보다 직접적으로 해결하기 위해 처음에는 명백한 색인을 작성하는 것이 좋을 것이라고 생각하지만 테이블에 대한 쿼리가 도움이되는지에 대해 더 많은 색인을 추가하는 것을 두려워하지 마십시오.

---

단순성에 대한 아인슈타인 의 역설에서 필요에 따라 더 많은 색인을 추가하십시오.

그러나 데이터를 테이블에 추가 할 때마다 추가하는 모든 인덱스를 유지 관리해야합니다. 주로 읽기 전용 인 테이블에서는 많은 인덱스가 좋습니다. 매우 동적 인 테이블에서는 더 적은 것이 좋습니다.

내 충고는 일반적이고 명백한 경우를 다루고 특정 테이블에서 데이터를 가져 오는 데 더 빠른 속도가 필요한 문제가 발생하면 그 시점에서 인덱스를 평가하고 추가하는 것입니다.

또한 색인 생성이 필요한 새로운 것이 있는지 또는 생성되지 않은 색인이나 색인에 필요한 것이 없는지 확인하기 위해 몇 달마다 색인 생성 체계를 다시 평가하는 것이 좋습니다. .

---

다른 모든 사람들이 제기 한 포인트 외에도 비용 기반 옵티마이 저는 고려할 조합이 더 많기 때문에 더 많은 인덱스가있는 경우 SQL 문에 대한 계획을 작성할 때 비용이 발생합니다. SQL 문이 SQL 캐시에 유지되도록 바인드 변수를 올바르게 사용하여이를 줄일 수 있습니다. 그런 다음 Oracle은 소프트 구문 분석을 수행하고 마지막에 찾은 계획을 재사용 할 수 있습니다.

항상 그렇듯이 간단한 것은 없습니다. 비뚤어진 열과 히스토그램이 있으면 이것은 나쁜 생각 일 수 있습니다.

웹 애플리케이션에서는 허용되는 검색 조합을 제한하는 경향이 있습니다. 그렇지 않으면 언젠가 누군가가 찾을 수있는 숨어있는 문제가 없는지 확인하기 위해 모든 조합의 성능을 테스트해야합니다. 또한 리소스 제한을 구현하여 애플리케이션의 다른 부분에서 문제가 발생하는 경우 이로 인해 문제가 발생하는 것을 막았습니다.

---

실제 프로젝트와 실제 MySql 데이터베이스에서 간단한 테스트를 수행했습니다. 이 주제에서 이미 답변했습니다. 여러 db 열을 인덱싱하는 비용은 얼마입니까?

그러나 여기에 인용하면 더 좋을 것이라고 생각합니다.

실제 프로젝트와 실제 MySql 데이터베이스를 사용하여 간단한 테스트를 수행했습니다.

내 결과는 다음과 같습니다. 평균 인덱스 (인덱스의 1-3 열)를 테이블에 추가하면 삽입 속도가 2.1 % 느려집니다. 따라서 20 개의 인덱스를 추가하면 인서트가 40-50 % 느려집니다. 그러나 선택은 10-100 배 빠릅니다.

많은 인덱스를 추가해도 괜찮습니까? -그것은 달려있다 :) 나는 당신에게 나의 결과를 주었다-당신은 결정한다!

---

궁극적으로 필요한 인덱스 수는 데이터베이스 서버 위에있는 응용 프로그램의 동작에 따라 다릅니다.

일반적으로 삽입이 많을수록 색인이 더 고통 스럽습니다. 삽입을 수행 할 때마다 해당 테이블을 포함하는 모든 인덱스를 업데이트해야합니다.

이제 응용 프로그램에 적절한 양의 판독 값이 있거나 거의 모든 판독 값이면 거의 적은 비용으로 성능이 크게 향상되므로 색인을 사용할 수 있습니다.

---

내 의견으로는 정답이 없으며, 이런 종류의 것은 '성능 조정'에 속합니다.

앱이 수행하는 모든 것이 기본 키로 조회되거나 제한되지 않은 필드 조합에 대해 쿼리가 수행되고 특정 시간에 특정 필드를 사용할 수 있다는 단점이 될 수 있습니다.

인덱싱 외에도 계산 된 검색 필드, 테이블 분할 등을 포함하도록 DB를 재정리합니다. 실제로로드 모양과 쿼리 매개 변수, 쿼리에서 '실제'데이터의 양 / 무엇을 결정해야하는지에 따라 달라집니다.

전체 DB가 스토어드 프로 시저 파사드에 의해 앞면 모든 임시 쿼리에 대해 걱정할 필요가 없기 때문에 회전이 약간 쉬워집니다. 또는 DB에 영향을 미치는 쿼리 종류에 대해 깊이 이해하고 튜닝을 제한 할 수 있습니다.

SQL Server의 경우 데이터베이스 엔진 튜닝 관리자가 유용하다는 것을 알았습니다. '일반적인'워크로드를 설정하면 인덱스 및 통계 추가 / 제거에 대한 권장 사항을 만들 수 있습니다. 다른 DB에도 '공식'또는 타사와 비슷한 도구가 있다고 확신합니다.

---

이것은 실제적인 것보다 더 이론적 인 질문입니다. 성능에 영향을 미치는 인덱스는 보유하고있는 하드웨어, Oracle 버전, 인덱스 유형 등에 따라 다릅니다. 어제 Oracle은 11g 데이터베이스에서 10 배 더 빠른 성능을 발휘할 수있는 HP 전용 스토리지를 발표했다고 들었습니다. 경우에 따라 여러 가지 해결책이있을 수 있습니다. 1. 많은 양의 인덱스 (> 20)를 가지고 매일 (매일) 다시 작성하십시오. 테이블이 매일 수천 건의 업데이트 / 삭제를 얻는 경우에 특히 유용합니다. 2. 테이블을 분할하십시오 (해당 데이터 모델이 적용되는 경우). 3. 새로운 / 업데이트 된 데이터에 대해 별도의 테이블을 사용하고 데이터를 결합하는 야간 프로세스를 실행하십시오. 이를 위해서는 애플리케이션 로직을 변경해야합니다. 4. 데이터가이를 지원하는 경우 IOT (인덱스 구성 테이블)로 전환하십시오.

Of course there might be many more solutions for such case. My first suggestion to you, would be to clone the DB to a development environment, and run some stress testing against it.

---

If you do mostly reads (and few updates) then there's really no reason not to index everything you'll need to index. If you update often, then you may need to be cautious on how many indexes you have. There's no hard number, but you'll notice when things start to slow down. Make sure your clustered index is the one that makes the most sense based on the data.

---

One thing you may consider is building indexes to target a standard combination of searches. If column1 is commonly searched, and column2 is often used with it, and column3 is sometimes used with column2 and column1, then an index on column1, column2, and column3 in that order can be used for any of those three circumstances, though it is only one index that has to be maintained.

---

An index imposes a cost when the underlying table is updated. An index provides a benefit when it is used to spped up a query. For each index, you need to balance the cost against the benefit. How much slower does the query run without the index? How much of a benefit is running faster? Can you or your users tolerate the slow speed when the index is missing?

Can you tolerate the additional time it takes to complete an update?

You need to compare costs and benefits. That's particular to your situation. There's no magic number of indexes that passes the threshold of "too many".

There's also the cost of the space needed to store the index, but you've said that in your situation that's not an issue. The same is true in most situations, given how cheap disk space has become.

---

How many columns are there? I have always been told to make single-column indexes, not multi-column indexes. So no more indexes than the amount of columns, IMHO.

---

What it really comes down to is, don't add an index unless you know (and this often means gathering usage statistics) that it will be used far more often than it's updated.

Any index that doesn't meet that criteria will cost you more to rebuild than the performance penalty of not having it in the odd case it got used.

---

Sql server gives you some good tools that let you see which indexes are actually being used. This article, http://www.mssqltips.com/tip.asp?tip=1239, gives you some queries that let you get a better insight into how much an index is used, as opposed to how much it is updated.

---

It is totally based on the columns which are being used in Where Clause. And as the Thumb of Rule, we must have indexes on Foreign Key Columns to avoid DEADLOCKS. AWR report should analyze periodically to understand the need of indexes.

참고URL : https://stackoverflow.com/questions/141232/how-many-database-indexes-is-too-many