데이터베이스 인덱스의 수가 너무 많습니까?

데이터베이스 인덱스의 수가 너무 많습니까?

저는 상당히 큰 Oracle 데이터베이스를 사용하는 프로젝트를 진행하고 있습니다(다른 데이터베이스에도 마찬가지로 질문이 적용됩니다).웹 인터페이스를 통해 사용자가 가능한 거의 모든 필드 조합을 검색할 수 있습니다.

이러한 검색을 빠르게 수행하기 위해 사용자가 일반적으로 검색할 것으로 생각되는 필드 및 필드 조합에 인덱스를 추가합니다.그러나 고객이 이 소프트웨어를 어떻게 사용할지 잘 모르기 때문에 어떤 인덱스를 작성해야 할지 알 수 없습니다.

공간은 문제가 되지 않습니다. 4테라바이트의 RAID 드라이브를 사용하고 있으며, 그 중 극히 일부만 사용하고 있습니다.그러나 인덱스가 너무 많아 성능 저하가 우려됩니다.행이 추가, 삭제 또는 변경될 때마다 인덱스를 업데이트해야 하기 때문에 한 테이블에 수십 개의 인덱스를 두는 것은 좋지 않을 것으로 생각합니다.

그러면 얼마나 많은 지표가 너무 많은가요? 10? 25? 50? 아니면 정말 흔하고 뻔한 사례를 다루고 다른 건 다 무시해야 할까요?

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

SELECT가 많고 변경 사항이 거의 없는 경우 원하는 모든 인덱스를 작성합니다.이것에 의해, (잠재적으로) SELECT 스테이트먼트의 속도가 향상됩니다.

테이블에 UPDATEs, INSERTs + DELETE의 영향이 큰 경우...이러한 작업 중 하나가 발생할 때마다 모두 수정해야 하기 때문에 많은 인덱스로 인해 속도가 매우 느려집니다.

그렇다고 해도 아무 것도 할 수 없는 테이블에 의미 없는 인덱스를 많이 추가할 수 있습니다.B-Tree 인덱스를 2개의 개별 값이 있는 열에 추가하는 것은 데이터 검색 측면에서 아무것도 추가하지 않으므로 의미가 없습니다.열의 값이 고유할수록 인덱스에서 더 많은 이점을 얻을 수 있습니다.

저는 보통 이렇게 진행해요.

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

모든 최적화와 마찬가지로 요청된 퍼포먼스에 도달하면 정지합니다(이는 포인트0이 특정 퍼포먼스 요건을 충족하고 있음을 의미합니다).

다른 사람들은 모두 당신에게 좋은 조언을 해줬어요.앞으로 나아가면서 제안할 것이 있습니다.어느 시점에서는 최적의 인덱싱 전략을 결정해야 합니다.그러나 결국 가장 계획적인 인덱싱 전략은 결국 사용되지 않는 인덱스를 생성할 수 있습니다.사용하지 않는 인덱스를 찾을 수 있는 한 가지 전략은 인덱스 사용량을 모니터링하는 것입니다.이 조작은 다음과 같이 실시합니다.

alter index my_index_name monitoring usage;

그런 다음 v$object_usage를 쿼리하여 해당 시점부터 인덱스가 사용되는지 여부를 모니터링할 수 있습니다.이에 대한 자세한 내용은 Oracle® 데이터베이스 관리자 가이드를 참조하십시오.

테이블을 업데이트하기 전에 인덱스를 삭제한 후 다시 생성하는 웨어하우징 전략이 있는 경우 인덱스를 모니터링하도록 다시 설정해야 하며 인덱스에 대한 모니터링 기록이 손실됩니다.

데이터 웨어하우징에서는 많은 수의 인덱스를 보유하는 것이 일반적입니다.200개의 컬럼과 190개의 인덱스가 있는 팩트 테이블을 사용해 왔습니다.

여기에는 오버헤드가 있지만 데이터 웨어하우스에서는 일반적으로 행을 한 번만 삽입할 수 있으며 업데이트는 하지 않지만 수천 개의 SELECT 쿼리에 참여할 수 있기 때문에 모든 컬럼에 인덱스를 붙이면 도움이 된다는 점을 이해해야 합니다.

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

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

당신의 질문에 좀 더 직접적으로 대처하기 위해 처음에는 당연한 것을 색인화하는 것이 좋다고 생각합니다만, 테이블에 대한 질의에 도움이 되는지에 대해 인덱스를 추가하는 것을 두려워하지 마십시오.

단순성에 대한 아인슈타인의 말을 바꾸어 말하면, 필요한 만큼의 인덱스를 추가하고 더 이상 추가하지 마십시오.

그러나 실제로 추가하는 모든 인덱스는 데이터가 테이블에 추가될 때마다 유지보수가 필요합니다.주로 읽기 전용 테이블에서는 많은 인덱스를 사용하는 것이 좋습니다.매우 동적인 테이블에서는 적은 것이 좋습니다.

일반적인 케이스와 명백한 케이스에 대해 설명하고 특정 테이블에서 데이터를 더 빨리 가져와야 하는 문제에 직면했을 때 해당 시점에서 인덱스를 평가하고 추가하는 것이 좋습니다.

또한 인덱싱 체계를 몇 개월마다 다시 평가하는 것이 좋습니다. 인덱싱이 필요한 새로운 항목이나 새로 만든 인덱스가 아무 용도로도 사용되지 않으므로 삭제해야 하는 항목이 있는지 확인하는 것이 좋습니다.

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

언제나 그렇듯이, 간단한 것은 없습니다.기울어진 열과 히스토그램이 포함되어 있는 경우 이는 잘못된 생각일 수 있습니다.

당사의 웹 응용 프로그램에서는 허용하는 검색 조합을 제한하는 경향이 있습니다.그렇지 않으면 말 그대로 모든 조합을 테스트하여 언젠가 누군가가 발견하게 될 잠재적인 문제가 없는지 확인해야 합니다.또, 애플리케이션내의 다른 장소에서 문제가 발생했을 경우에 발생하는 것을 막기 위해서, 자원의 제한도 실장하고 있습니다.

저는 실제 프로젝트와 실제 MySql 데이터베이스에 대해 간단한 테스트를 몇 가지 했습니다.이 항목에서 이미 답변했습니다.여러 DB 열을 인덱싱하는 데 드는 비용은 얼마입니까?

하지만 여기서 인용하는 것이 더 나을 것 같습니다.

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

그 결과 평균 인덱스(인덱스의 1-3열)를 테이블에 추가하면 삽입 속도가 2.1% 느려집니다.따라서 인덱스를 20개 추가하면 삽입 속도가 40~50% 느려집니다.하지만 선택 속도가 10-100배 빨라집니다.

그럼 인덱스를 많이 추가해도 될까요? - 상황에 따라 다릅니다. - 결과를 알려드렸습니다. - 결정하세요!

최종적으로 필요한 인덱스 수는 데이터베이스 서버를 기반으로 하는 응용 프로그램의 동작에 따라 달라집니다.

일반적으로 삽입을 많이 할수록 인덱스가 더 어려워집니다.삽입할 때마다 해당 테이블을 포함하는 모든 인덱스를 업데이트해야 합니다.

어플리케이션의 판독치가 적당하거나 그 이상이라면 인덱스는 퍼포먼스가 대폭 향상되므로 매우 적은 비용으로 퍼포먼스를 크게 향상시킬 수 있습니다.

제 생각에는 고정적인 답변은 없습니다. 이런 것은 '퍼포먼스 튜닝'에 해당됩니다.

어플리케이션의 모든 작업을 프라이머리 키로 검색하거나 쿼리가 제한되지 않은 필드 조합에 대해 수행되며 특정 시간에 사용할 수 있다는 점에서 oposite일 수 있습니다.

단순한 색인화 외에도 계산된 검색 필드, 테이블 분할 등을 포함하도록 DB를 다시 정리할 수 있습니다. 이는 로드 모양과 쿼리 매개 변수, 쿼리에 의해 "실제로" 재검색해야 하는 데이터의 양과 종류에 따라 달라집니다.

전체 DB가 스토어드 프로시저에 의해 처리되는 경우, 모든 애드혹 쿼리에 대해 걱정할 필요가 없기 때문에 전환이 조금 더 쉬워집니다.또는 DB에 도달하는 쿼리의 종류를 잘 이해하고 있으며, 이러한 쿼리로 조정을 제한할 수 있습니다.

SQL Server의 경우 Database Engine Tuning Advisor는 '표준' 워크로드를 설정하여 인덱스 및 통계 추가/삭제에 대한 권장사항을 제공할 수 있습니다.다른 DB들도 '공식' 또는 '서드파티'와 유사한 도구를 가지고 있을 것입니다.

이것은 실제로 실용적이기보다는 이론적인 질문이다.성능에 미치는 인덱스는 사용하는 하드웨어, Oracle 버전, 인덱스 유형 등에 따라 달라집니다.어제 Oracle이 HP가 만든 전용 스토리지를 발표했다고 들었습니다.이 스토리지는 11g 데이터베이스로 10배 빠른 성능을 발휘합니다.고객의 경우는, 몇개의 해결 방법이 있습니다.1 .대량의 인덱스를 보유하여(>20), 매일(야간) 재구축한다.이는 테이블이 매일 수천 건의 업데이트/삭제를 받는 경우에 특히 유용합니다. 2. 테이블을 분할합니다(데이터 모델이 적용되는 경우).3. 신규/업데이트된 데이터는 별도의 테이블을 사용하여 야간에 데이터를 조합하는 프로세스를 수행합니다.이를 위해서는 애플리케이션 로직을 변경해야 합니다.4 .데이터가 이를 지원하는 경우 IOT(Index Organized Table)로 전환합니다.

물론 이러한 경우에 대한 해결 방법은 더 많을 수 있습니다.먼저 DB를 개발 환경에 복제하고 그에 대한 스트레스 테스트를 실행하는 것이 좋습니다.

인덱스는 기본 테이블이 업데이트될 때 비용을 부과합니다.인덱스는 쿼리를 spping하는 데 사용될 때 이점을 제공합니다.각 인덱스에 대해 비용과 편익의 균형을 맞춰야 합니다.인덱스를 사용하지 않으면 쿼리가 얼마나 느리게 실행됩니까?실행 속도가 빨라지는 이점은 어느 정도입니까?인덱스가 없을 때 사용자나 사용자가 느린 속도를 견딜 수 있습니까?

업데이트를 완료하는 데 추가 시간이 걸릴 수 있습니까?

비용과 이점을 비교해야 합니다.그것은 당신의 상황에 특유하다."너무 많은" 임계값을 초과하는 인덱스의 매직 수는 없습니다.

인덱스를 저장하는 데 필요한 공간 비용도 있지만, 현재 상황에서는 문제가 되지 않는다고 말씀하셨습니다.디스크 공간이 얼마나 저렴해졌는지를 고려할 때 대부분의 경우 이와 같습니다.

대부분 읽기를 하고 업데이트를 거의 하지 않는다면 인덱싱해야 할 모든 것을 인덱싱하지 않을 이유가 없습니다.자주 업데이트할 경우 인덱스 수에 주의해야 할 수 있습니다.확실한 숫자는 없지만, 상황이 느려지기 시작하면 알게 될 것이다.데이터를 기반으로 가장 적합한 인덱스가 클러스터된 인덱스인지 확인하십시오.

한 가지 고려 사항은 검색의 표준 조합을 대상으로 인덱스를 작성하는 것입니다.column1이 일반적으로 검색되고 column2가 자주 사용되며 column3이 column2 및 column1과 함께 사용되는 경우 column1, column2 및 column3의 인덱스는 이러한 세 가지 상황 중 하나에 사용할 수 있습니다. 단, 유지해야 하는 인덱스는 1개뿐입니다.

몇 개의 열이 있습니까?저는 항상 다중 열 인덱스가 아닌 단일 열 인덱스를 만들라고 들었습니다.즉, IMHO라는 열의 양보다 더 많은 인덱스는 없습니다.

요약하자면 인덱스가 업데이트되는 것보다 훨씬 더 자주 사용된다는 것을 알지 못하면 인덱스를 추가하지 마십시오(그리고 이는 종종 사용 통계를 수집하는 것을 의미함).

이 기준을 충족하지 못하는 인덱스는 사용된 이상한 경우에 인덱스를 보유하지 않으면 성능 저하보다 재구축 비용이 더 많이 듭니다.

SQL 서버는 실제로 어떤 인덱스가 사용되고 있는지 확인할 수 있는 몇 가지 유용한 도구를 제공합니다.이 기사, http://www.mssqltips.com/tip.asp?tip=1239,에서는 업데이트되는 인덱스의 양이 아니라 사용되는 인덱스의 양을 더 잘 파악할 수 있는 몇 가지 쿼리를 제공합니다.

그것은 전적으로 Where Clause에서 사용되고 있는 컬럼에 기초하고 있다.또한 규칙의 엄지로서 DEALDOCKS를 피하기 위해 Foreign Key Columns에 인덱스를 작성해야 합니다.AWR 보고서는 정기적으로 분석하여 인덱스의 필요성을 파악해야 합니다.

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