[DB] 테이블 인덱스 설정 (eXERD, HeidiSQL)
인덱스란,
데이터베이스에서 검색 속도를 향상시키기 위해 사용되는 데이터 구조로,
특정 열(또는 열들)의 값을 빠르게 찾을 수 있도록 돕는다.
특징
- 검색 속도 향상
- 쿼리 성능 개선
- 정렬 및 그룹화 용이성
1. exERD에서 인덱스 설정하는 방법
1) 인덱스 추가하기
- 인덱스 설정할 테이블 우클릭 > 특성 > [인덱스]
- 우측 + 버튼 누른 후, 추가할 인덱스 형식 선택(아무것도 체크 안 하면 일반) 후 확인
- 아래 [인덱스 컬럼] 탭에서 컬럼 추가하고 삭제하여 인덱스 생성 (Primary Key는 추가하지 않아도 들어감)
♣ 인덱스에 추가하면 효과적인 컬럼
- 검색 조건으로 자주 사용되는 컬럼(WHERE 절이나 JOIN 조건 등에 사용되는 컬럼 고려)
- 외래 키 컬럼(외래 키를 기반으로 하는 조인이나 검색에 사용되는 경우 인덱스를 추가하면 성능 향상)
- 정렬이나 그룹화에 사용되는 컬럼
(ORDER BY나 GROUP BY 절에 자주 사용되는 컬럼에 추가하면 그룹화 작업 성능 향상)
- 조건에 사용되는 컬럼의 조합
- 날짜 및 시간 관련 컬럼
2) 추가한 인덱스 쿼리 복사하기
- [exERD] > 포워드 엔지니어링
- DDL 생성 옵션 : 인덱스 > 일반 인덱스 생성만 체크
- 모델 선택 : 다이어그램으로 선택 > 해당 테이블 체크박스 선택
- DDL 결과 : 쿼리 잘 작성되었는지 확인 후 클립보드로 복사
2. HeidiSQL에서 쿼리로 인덱스 추가하는 방법
- exERD에서 복사한 쿼리 실행시키기
- 테이블 있는 DB 새로고침
- 해당 테이블 > [인덱스] 탭 들어가서 잘 추가되었는지 확인
3. HeidiSQL에서 인덱스 잘 타고 있는지 확인하는 방법
- 사용할 SELECT 쿼리문 맨 앞에 EXPLAIN 명령어를 붙이고, 쿼리 실행 계획 확인
-- 예시 쿼리
EXPLAIN SELECT * FROM your_table WHERE your_column = 'some_value';
♣ 확인해야 할 열
- type: 어떤 타입의 조인이 사용되었는지를 나타냄. 좋은 성능을 위해서는 "index"나 "range"가 좋음.
- possible_keys: 어떤 인덱스가 사용될 수 있는지를 나타냄. 쿼리에 대해 최적의 인덱스를 사용하고 있는지 확인함.
- key: 실제로 사용된 인덱스를 나타냄. possible_keys와 일치해야 좋은 성능을 얻을 수 있음.
- rows: 예상되는 검색 결과 행 수를 나타냄. 이 값이 작을수록 성능이 좋음.
- extra: 여러 부가 정보를 제공함. "Using index"이나 "Using where" 등이 나오면 인덱스를 잘 활용하고 있는 것임.
4. 데이터베이스 시스템의 인덱스 관련 특징
일반적으로 데이터베이스 시스템은 여러 개의 인덱스를 선택할지 결정하는 역할을 옵티마이저에게 맡기며, 이를 통해 쿼리의 실행 계획을 최적화하고 성능을 향상시킴.
♣ 결정을 고려하는 요소
- 쿼리의 복잡성과 조건
: 어떤 조건이 WHERE 절에 포함되었는지, 그리고 조인이 있는지 여부에 따라 옵티마이저는 적절한 인덱스를 선택함.
- 인덱스 통계
: 인덱스의 통계 정보를 기반으로 각 인덱스의 선택도 및 비용을 분석하여 최적의 실행 계획을 수립함.
- 최신성과 활용도
: 얼마나 자주 인덱스가 업데이트되었는지, 얼마나 자주 해당 인덱스가 활용되는지에 따라서도 선택이 이루어짐.
- 인덱스의 종류
: 다양한 인덱스 유형 중에서 어떤 유형의 인덱스가 사용되는지에 따라 선택이 달라짐.
- 시스템 부하 및 자원 사용
: 어떤 인덱스를 선택하느냐에 따라서 시스템 부하와 자원 사용량이 달라짐. 이를 고려하여 최적의 선택을 함.
개발자는 데이터베이스 설계 단계에서 적절한 인덱스를 설정하고 필요한 경우에는 인덱스를 수정하거나 추가하는 것이 중요함. 너무 많은 인덱스는 성능을 저하시킬 수 있으므로 신중하게 선택하고 유지보수해야 함.