MySQL 인덱스 성능 비교 — PK vs 보조 vs 없음 완전 정리

 

 

MySQL 실습 정리

인덱스 성능 비교

복사 테이블에 PK와 보조 인덱스를 추가해 상태와 조회 차이를 비교한다.

1 핵심 개념

복사 테이블에 PK와 보조 인덱스를 추가해 상태와 조회 차이를 비교한다. 인덱스는 SELECT 속도를 높일 수 있지만, INSERT/UPDATE/DELETE 비용과 저장 공간을 추가로 사용한다.

PRIMARY KEY, UNIQUE, 일반 INDEX는 모두 인덱스와 관련되지만 목적이 다르다. 기본키는 식별, 유니크는 중복 방지, 일반 인덱스는 검색 성능 개선에 초점을 둔다.

인덱스를 만들었으면 SHOW INDEX와 실제 조회 조건을 함께 확인해야 한다. 많이 만든다고 항상 좋은 것은 아니다.

2 원본 예제 코드

CREATE DATABASE IF NOT EXISTS indexdb; USE indexdb; SELECT COUNT(*) FROM employees.employees; CREATE TABLE emp SELECT * FROM employees.employees ORDER BY RAND(); CREATE TABLE emp_c SELECT * FROM employees.employees ORDER BY RAND(); CREATE TABLE emp_se SELECT * FROM employees.employees ORDER BY RAND(); ALTER TABLE emp_c ADD PRIMARY KEY(emp_no); ALTER TABLE emp_se ADD INDEX idx_emp_no(emp_no); ANALYZE TABLE emp, emp_c, emp_se; SHOW INDEX FROM emp; SHOW INDEX FROM emp_c; SHOW INDEX FROM emp_se; SHOW TABLE STATUS;

3 용어 카드

INDEX검색 성능을 높이기 위해 만드는 자료 구조

PRIMARY KEY행을 고유하게 식별하고 InnoDB에서 클러스터드 기준이 되는 키

UNIQUE중복을 허용하지 않는 보조 제약조건

SHOW INDEX테이블 인덱스 정보를 확인하는 명령

ANALYZE TABLE테이블 통계 정보를 갱신하는 명령

INFORMATION_SCHEMADB 메타데이터를 조회하는 시스템 스키마

클러스터드 인덱스데이터 자체의 저장 순서와 가까운 기본키 인덱스

보조 인덱스기본키 외 컬럼에 추가하는 검색용 인덱스

 

반응형

 

4 실행 흐름

1

테이블 상태 확인SHOW INDEX와 SHOW TABLE STATUS로 현재 상태를 본다.

2

인덱스 생성조회 조건에 자주 쓰는 컬럼에만 인덱스를 추가한다.

3

통계 갱신ANALYZE TABLE로 옵티마이저 통계를 최신화한다.

4

조회 조건 비교WHERE 조건이 인덱스 컬럼을 실제로 쓰는지 확인한다.

5

불필요 인덱스 제거중복되거나 사용하지 않는 인덱스는 DROP INDEX로 정리한다.

5 인덱스 비교

종류	목적	중복 허용
PRIMARY KEY	대표 식별자	불가, NULL 불가
UNIQUE	중복 방지	불가, NULL은 DB 정책 영향
일반 INDEX	조회 성능 향상	가능
복합 INDEX	여러 컬럼 조건 최적화	정의 방식에 따름
클러스터드 인덱스	데이터 저장 기준	보통 PK 기준

6 SHOW INDEX 읽기

컬럼	의미	보는 이유
Key_name	인덱스 이름	DROP할 대상 확인
Non_unique	중복 허용 여부	0이면 UNIQUE 성격
Seq_in_index	복합 인덱스 컬럼 순서	왼쪽 컬럼 기준 확인
Column_name	인덱스 대상 컬럼	WHERE 조건과 비교
Cardinality	대략적인 고유값 분포	선택도 판단 참고

7 복합 인덱스 기준

복합 인덱스는 컬럼 순서가 중요하다. (name, birthYear) 인덱스는 name 조건에 유리하고, name 없이 birthYear만 조건으로 쓰면 기대만큼 활용되지 않을 수 있다.

CREATE INDEX idx_usertbl_name_birthYear ON usertbl(name, birthYear); SELECT * FROM usertbl WHERE name = '윤종신' AND birthYear = 1969;

8 PK와 UNIQUE 차이

구분	PRIMARY KEY	UNIQUE
역할	대표 식별자	보조 중복 방지
개수	테이블당 하나	여러 개 가능
NULL	허용 안 함	DB 설정과 컬럼 정의 영향
클러스터 기준	InnoDB에서 주로 기준	보조 인덱스 성격
예시	userID	email, name

9 INFORMATION_SCHEMA 활용

제약조건 이름을 모를 때는 INFORMATION_SCHEMA를 조회한다. 외래키를 삭제하려면 정확한 constraint_name이 필요하므로, 시스템 스키마에서 먼저 이름을 찾는 습관이 좋다.

SELECT table_name, constraint_name FROM information_schema.referential_constraints WHERE constraint_schema = 'sqldb';

10 자주 하는 실수

실수	증상	해결
중복 데이터가 있는데 UNIQUE 생성	CREATE INDEX 실패	중복값 정리 후 생성
인덱스만 많이 생성	쓰기 성능 저하	조회 조건 기준으로 최소화
복합 인덱스 순서 무시	인덱스 활용이 약함	자주 쓰는 조건 순서로 설계
FK 이름 모르고 DROP	삭제 실패	INFORMATION_SCHEMA로 이름 확인
ANALYZE 생략	통계가 오래됨	대량 변경 후 ANALYZE TABLE

11 성능 비교 관점

인덱스가 없는 테이블, PRIMARY KEY가 있는 테이블, 보조 인덱스가 있는 테이블을 나누어 같은 조회를 실행하면 차이를 이해하기 쉽다. 단, 작은 테이블에서는 차이가 작게 보일 수 있다.

테이블	구성	비교 포인트
emp	인덱스 없음	전체 스캔 가능성
emp_c	PRIMARY KEY(emp_no)	클러스터드 기준
emp_se	INDEX(emp_no)	보조 인덱스 기준
usertbl	여러 인덱스	SHOW INDEX 비교

12 검산 쿼리

SHOW INDEX FROM usertbl; SHOW TABLE STATUS LIKE 'usertbl'; ANALYZE TABLE usertbl; SELECT * FROM usertbl WHERE name = '윤종신' AND birthYear = 1969; EXPLAIN SELECT * FROM usertbl WHERE mobile1 = '011';

실제 성능 판단에는 EXPLAIN이 더 직접적이다. 이 글의 예제는 SHOW INDEX와 상태 확인 중심이지만, 이후에는 EXPLAIN으로 실행 계획까지 보는 것이 좋다.

13 한 줄 복습

상황	문법	핵심
인덱스 보기	SHOW INDEX FROM 테이블	현재 키 구조 확인
일반 인덱스 생성	CREATE INDEX	중복 허용 검색용
유니크 인덱스 생성	CREATE UNIQUE INDEX	중복 방지
통계 갱신	ANALYZE TABLE	옵티마이저 정보 갱신
인덱스 삭제	DROP INDEX ... ON	불필요 키 정리
제약 이름 조회	INFORMATION_SCHEMA	FK 삭제 전 이름 확인

14 인덱스 설계 질문

질문	판단 기준	예시
이 컬럼으로 자주 검색하는가?	WHERE, JOIN, ORDER BY에 반복 등장	name, mobile1
값이 충분히 다양한가?	고유값이 많을수록 선택도가 좋다	userID는 좋고 성별은 낮음
중복을 막아야 하는가?	업무 규칙상 중복 불가	email UNIQUE
복합 조건이 자주 쓰이는가?	여러 컬럼을 함께 검색	name, birthYear
쓰기 작업이 많은가?	인덱스가 많으면 쓰기 비용 증가	대량 INSERT 테이블

15 변경 전후 판정

작업	전	후
SHOW INDEX	키 목록이 적음	새 인덱스 이름이 보임
SHOW TABLE STATUS	통계가 오래됐을 수 있음	ANALYZE 후 통계 갱신
WHERE 조회	전체 스캔 가능	인덱스 조건 활용 가능
UNIQUE 생성	중복 입력 가능	중복 입력 차단
PK 변경	기존 기준으로 정렬	새 기본키 기준 확인

16 정리 기준

인덱스 실습은 생성보다 판정이 중요하다. SHOW INDEX로 존재를 확인하고, 조회 조건이 해당 컬럼을 실제로 사용하는지까지 봐야 의미가 있다.

인덱스는 검색을 빠르게 하는 대신 쓰기와 저장 공간 비용을 만든다. 필요한 조회 패턴에서만 추가하는 것이 기본 원칙이다.

핵심 요약

주제인덱스 성능 비교

핵심복사 테이블에 PK와 보조 인덱스를 추가해 상태와 조회 차이를 비교한다.

PK대표 식별자이며 클러스터드 기준이 될 수 있다.

UNIQUE중복 방지를 위한 보조 제약조건이다.

검산SHOW INDEX, ANALYZE TABLE, 조회 조건 비교를 함께 본다.

Tags

#MySQL #INDEX #PRIMARY KEY #UNIQUE #SHOW INDEX #SQL

티스토리 태그 입력용

MySQL, INDEX, PRIMARY KEY, UNIQUE, SHOW INDEX, ANALYZE TABLE, INFORMATION_SCHEMA, SQL, DB