사용되지 않는 인덱스 탐지

Oracle

-- Start monitoring:
ALTER INDEX customer_city_idx MONITORING USAGE;
-- Check after a while (12c+, all schemas):
SELECT owner, index_name, used FROM dba_object_usage;

USED = NO이면 모니터링 기간 동안 해당 인덱스가 사용되지 않은 것이다. 모니터링은 NOMONITORING USAGE로 끈다. 기존 v$object_usage는 세션 사용자의 인덱스만 보여 주며 12c부터 deprecated다. 12.2+에서는 인덱스 사용 현황이 DBA_INDEX_USAGE에 자동으로 기록된다.

PostgreSQL

SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

idx_scan = 0은 통계가 마지막으로 초기화된 이후 인덱스가 사용되지 않았음을 뜻한다. UNIQUE/PK 인덱스는 삭제하지 않는다.

MySQL

SELECT * FROM sys.schema_unused_indexes;

performance_schema가 활성화되어 있어야 하고 서버가 일정 기간 가동된 상태여야 한다. UNIQUE 제약 조건은 제외된다.

SQL Server

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
  ON i.object_id = s.object_id AND i.index_id = s.index_id
  AND s.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
  AND COALESCE(s.user_seeks + s.user_scans + s.user_lookups, 0) = 0;

usage_stats는 서버 재시작 시 초기화된다. 충분한 관찰 기간을 둔다.

MongoDB

db.customer.aggregate([{ $indexStats: {} }])
// Indexes with low/0 accesses.ops are candidates

accesses.ops는 accesses.since 시점 이후의 접근 횟수다. _id 인덱스는 삭제할 수 없다.

Redis

# Not applicable: core Redis has no secondary indexes.
# If the RediSearch module is used, check statistics with FT.INFO <index>.

데이터 모델이 키-값이므로 전통적인 인덱스 개념이 없다.