DataBase 인덱스 - 잘못사용하면 생기는일

MSSQL에서 인덱스는 일반적으로 조회 성능 향상을 위한 것이지만, 다음과 같은 경우에는 오히려 성능 저하를 초래할 수 있습니다.

---

🔍 상황 분석

• AD_HIST 테이블에 이미 Primary Key(PK) 가 설정되어 있음
• → PK에는 자동으로 클러스터드 인덱스(기본적으로) 또는 유니크 인덱스가 생성됨.
• 그 위에 [IDX_AD_HIST_HistId] 같은 별도 인덱스가 존재함
• → HistId가 PK 컬럼이라면, 동일한 컬럼에 대해 중복 인덱스가 생긴 것임.

---

❗ 중복 인덱스의 단점

1. 쓰기 성능 저하:
   • INSERT / UPDATE / DELETE 시, 모든 인덱스를 동시에 유지관리해야 함.
   • 불필요한 인덱스가 많을수록 부하 증가.
2. 디스크 공간 낭비:
   • 중복 인덱스는 실제 데이터를 따로 저장하므로 공간만 낭비.
3. 쿼리 옵티마이저의 혼란:
   • 옵티마이저가 최적 인덱스를 고르기 어려워지거나, 잘못된 인덱스를 선택하는 경우도 있음.
   • 이런 경우 성능이 오히려 느려질 수 있음.

---

✅ 인덱스를 제거해도 되는 경우

다음 조건을 만족하면 [IDX_AD_HIST_HistId]는 제거해도 됩니다:

• 해당 인덱스가 HistId 단일 컬럼 인덱스이고,
• HistId가 PK 컬럼이며,
• 해당 인덱스를 사용한 쿼리 패턴이 대부분 PK 기반 조회인 경우.

즉, select * from AD_HIST where HistId = ? 이런 쿼리는 PK 인덱스로 충분히 커버됨.

---

📌 제거 방법

DROP INDEX [IDX_AD_HIST_HistId] ON [dbo].[AD_HIST];

주의: 인덱스 제거 전에 실제 사용 여부를 확인하세요.

---

🔧 확인 방법 (실제 사용 여부 확인)

-- 최근 실행된 쿼리 중 해당 인덱스 사용 여부 확인
SELECT TOP 1000
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qt.text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qt.text LIKE '%AD_HIST%' AND qt.text LIKE '%HistId%'
ORDER BY qs.execution_count DESC;

또는

-- 인덱스 사용 통계 보기
SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.index_id,
    user_seeks,
    user_scans,
    user_lookups,
    user_updates
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECT_NAME(i.object_id) = 'AD_HIST';

user_seeks, user_scans가 0이라면 거의 사용되지 않은 인덱스일 가능성이 높습니다.

---

✅ 결론

• [IDX_AD_HIST_HistId]가 PK와 같은 컬럼에만 구성된 중복 인덱스라면 → 제거하는 것이 좋음.
• 제거 전에는 사용 여부를 꼭 확인하세요.
• 인덱스가 많다고 무조건 좋은 게 아니라, 정확한 용도에 맞는 최소한의 인덱스가 중요합니다.

 

인덱스 사용 통계

---

📊 인덱스 사용 통계 분석

IndexName user_seeks user_scans user_lookups user_updates

IDX_AD_HIST_HistId	0	44094	0	42042
PK_AD_HIST	1050	847	0	2

의미 요약:

• IDX_AD_HIST_HistId: Seek 없이 Scan만 44,094회, 업데이트도 많음 → 효율적인 인덱스로 활용되지 않음
• PK_AD_HIST: Seek이 활발 (1,050회) → 효율적으로 활용되고 있음
• 두 인덱스의 user_updates 수치 차이는 그만큼 중복으로 관리되고 있는 비용을 의미

---

✅ 결론

IDX_AD_HIST_HistId 인덱스는:

• Seek을 단 한 번도 사용하지 않았고,
• Scan만 자주 일어나며,
• PK_AD_HIST에 의해 동일한 컬럼이 이미 잘 커버되고 있음

👉 성능상 이득이 없고, 오히려 불필요한 유지 관리 비용만 발생하고 있는 인덱스입니다.

---

🧹 권장 작업

DROP INDEX [IDX_AD_HIST_HistId] ON [dbo].[AD_HIST];

이후에도 성능 이슈가 없다면, 완전히 제거해도 괜찮습니다.

필요 시, 제거 전 백업이나 스크립트로 저장해두면 언제든 복구 가능합니다:

CREATE NONCLUSTERED INDEX [IDX_AD_HIST_HistId]
ON [dbo].[AD_HIST] ([HistId]);

---

 

 

 

datadog에서 파악한 바로는 이쿼리가 레이턴시가 느린것으로 판단돼. 어떻게하면 속도를 올릴수 있을까?

올려주신 Datadog 스크린샷과 이전 인덱스 분석을 종합하면, 지연이 큰 쿼리는 다음과 같습니다:

select
  adhist0_.HIST_ID,
  adhist0_.AD_HIST_TY,
  adhist0_.AD_NO,
  adhist0_.AD_TY,
  adhist0_.DEVICE_NO,
  adhist0_.LANG_CD,
  adhist0_.RG_DT
from AD_HIST adhist0_
where adhist0_.HIST_ID = ?

이 쿼리는 HIST_ID 기준 단건 조회이므로 정상적이라면 아주 빠르게 나와야 합니다. 하지만 Datadog에서는 이 쿼리가 171ms ~ 3분까지 걸리는 것을 보여주고 있습니다. 이는 심각한 성능 저하를 의미합니다.

---

✅ 현재까지 상황 요약

• HIST_ID는 PK임 → 클러스터드 인덱스로 Seek 가능한 상황
• 그런데 PK_AD_HIST는 seek이 발생하고 있음에도 레이턴시가 길다
• IDX_AD_HIST_HistId는 seek 없이 scan만 발생 중

---

🧠 가능한 원인 분석 및 개선 방안

1. 실제로 PK 인덱스가 사용되고 있는지 실행 계획 확인

MSSQL에서 실행 계획을 확인하여 PK 인덱스를 제대로 사용하고 있는지 확인하세요.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 문제의 쿼리 실행
select *
from AD_HIST
where HIST_ID = ?

그리고 SSMS에서 실행 계획(Estimated or Actual Execution Plan) 을 열어보세요.

• Index Seek로 되어 있어야 정상입니다.
• Index Scan 또는 Table Scan이 뜨면 → 인덱스가 무시되고 있는 것입니다.

---

2. 파라미터 값에 따른 실행 계획 고정(파라미터 스니핑)

MSSQL은 처음 실행된 파라미터 값을 기준으로 실행 계획을 캐싱합니다. 이게 잘못된 경우에 성능 저하가 발생할 수 있습니다. 이를 파라미터 스니핑 문제라고 합니다.

해결 방안:

OPTION (RECOMPILE)

→ 매 실행마다 새로운 실행 계획을 생성:

select *
from AD_HIST
where HIST_ID = ?
OPTION (RECOMPILE)

또는 저장 프로시저라면 내부에서 OPTION (RECOMPILE)을 쓰거나 OPTIMIZE FOR UNKNOWN을 사용합니다.

---

3. 통계 정보 최신화

오래된 통계는 옵티마이저가 잘못된 실행 계획을 만드는 주요 원인입니다.

-- 통계 업데이트 (전체 테이블 기준)
UPDATE STATISTICS dbo.AD_HIST WITH FULLSCAN;

---

4. 인덱스 조각화 확인 및 재구성

인덱스가 심하게 조각나 있다면 성능 저하가 발생할 수 있습니다.

-- 인덱스 조각화 확인
SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

조각화가 심하면:

-- 조각화가 심할 경우
ALTER INDEX [PK_AD_HIST] ON [dbo].[AD_HIST] REBUILD;

---

5. I/O 병목 또는 락 경합

• 해당 테이블에 많은 동시 쓰기/읽기 트래픽이 있는 경우에도 성능 저하가 발생할 수 있습니다.
• 이 경우에는 데이터베이스 락 정보 또는 I/O wait stats 분석이 필요합니다.
• sys.dm_os_wait_stats, sys.dm_tran_locks 등을 활용해서 진단 가능합니다.

---

✅ 최종 권장 조치 순서

1. 해당 쿼리의 실행 계획 확인 (Index Seek 여부)
2. OPTION (RECOMPILE) 적용하여 파라미터 스니핑 회피 테스트
3. UPDATE STATISTICS로 통계 갱신
4. 인덱스 조각화 확인 및 재구성
5. 필요시, PK만 유지하고 IDX_AD_HIST_HistId는 DROP
6. (Scan만 발생 중이므로 해로운 인덱스일 가능성 높음)