MySQL 인덱스 확인 및 최적화 방법: SHOW INDEX, EXPLAIN, 성능 튜닝을 포함한 완전 가이드

1. 소개

MySQL은 많은 웹 애플리케이션 및 데이터 관리 시스템에서 널리 사용되는 관계형 데이터베이스입니다. 데이터 검색 속도를 향상시키기 위해 “인덱스”라는 메커니즘이 존재합니다. 그러나 인덱스를 제대로 관리하지 않으면 실제로 성능이 저하될 수 있습니다.

인덱스 확인이 중요한 이유

데이터베이스의 인덱스는 책의 색인 섹션과 유사합니다. 적절히 설계된 인덱스는 검색 쿼리의 실행 속도를 향상시킵니다. 하지만 다음과 같은 문제가 발생할 수 있습니다:

• 인덱스가 제대로 생성되지 않음 → 검색이 느려질 수 있음
• 불필요한 인덱스가 존재함 → 업데이트 및 삽입이 느려짐
• 어떤 인덱스가 사용되고 있는지 불확실함 → 사용되지 않는 인덱스를 제거할지 결정하기 어려움

이 문서에서 배우게 될 내용

• MySQL 인덱스의 기본 메커니즘
• 현재 인덱스를 확인하는 방법 (SQL 명령 사용)
• 인덱스를 관리하고 최적화하는 방법
• 인덱스 사용량을 분석하는 기법

이제 MySQL 인덱스를 체계적으로 학습하고 이 지식을 활용해 데이터베이스 성능을 향상시켜 봅시다.

2. MySQL 인덱스란 무엇인가?

인덱스는 데이터베이스 성능을 향상시키는 필수 기능입니다. 이 섹션에서는 인덱스의 기본 개념, 유형, 장점 및 단점을 설명합니다.

인덱스의 기본 개념

데이터베이스 인덱스는 특정 컬럼의 값을 빠르게 검색할 수 있게 해주는 메커니즘입니다. 예를 들어, 대량의 데이터를 포함한 테이블에서 특정 레코드를 검색할 때 인덱스가 없으면 데이터베이스는 모든 레코드를 스캔해야 합니다(전체 테이블 스캔). 인덱스를 적용하면 데이터 검색이 효율적으로 이루어지고 처리 속도가 크게 향상됩니다.

MySQL 인덱스 유형

MySQL은 여러 종류의 인덱스를 지원하며, 각각은 특정 사용 사례에 적합합니다.

1. PRIMARY KEY (기본 키 인덱스)

• 테이블당 한 번만 설정 가능
• 테이블의 고유성을 보장
• 클러스터형 인덱스로 작동

1. UNIQUE Index

• 지정된 컬럼의 값이 중복되지 않도록 보장
• NULL 값 허용 (여러 NULL 허용)

1. INDEX (일반 인덱스)

• 검색 속도 향상에 사용
• 중복 데이터 허용

1. FULLTEXT Index (텍스트 검색용)

• 텍스트 검색 최적화
• MATCH ... AGAINST 구문과 함께 사용

1. SPATIAL Index (지리 데이터용)

• 공간(GIS) 데이터를 위해 설계

인덱스의 장점과 단점

장점

• 쿼리 검색 속도 향상
• JOIN 연산 및 WHERE 절의 성능 향상
• 특정 데이터 검색 효율성 증대

단점

• 인덱스가 많을수록 INSERT, UPDATE, DELETE 작업이 느려짐
• 디스크 공간 차지
• 설계가 부실한 인덱스는 성능을 저하시킬 수 있음

3. MySQL 인덱스 확인 방법

MySQL 인덱스를 올바르게 관리하려면 테이블에 현재 정의된 인덱스를 확인하는 것이 중요합니다. 이 섹션에서는 SHOW INDEX 명령, INFORMATION_SCHEMA.STATISTICS, mysqlshow 명령을 사용하여 인덱스를 확인하는 방법을 설명합니다.

SHOW INDEX 명령 (기본 방법)

MySQL에서는 SHOW INDEX 명령을 사용하여 특정 테이블에 정의된 인덱스 목록을 가져올 수 있습니다. 이 명령을 통해 인덱스 이름, 인덱스가 포함하는 컬럼, 고유 제약 조건 존재 여부와 같은 세부 정보를 확인할 수 있습니다.

기본 구문

SHOW INDEX FROM table_name;

예시

예를 들어, users 테이블에 정의된 인덱스를 확인하려면 다음 SQL을 실행합니다:

SHOW INDEX FROM users;

예시 출력

INFORMATION_SCHEMA.STATISTICS 사용 (자세한 정보 얻기)

INFORMATION_SCHEMA.STATISTICS 시스템 테이블을 사용하면 SHOW INDEX와 동일한 정보를 보다 유연하게 가져올 수 있습니다.

특정 테이블의 인덱스 확인

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

전체 데이터베이스에서 인덱스 가져오기

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

mysqlshow 명령 (CLI에서 확인)

MySQL 명령줄 도구를 사용하여 인덱스 정보를 가져올 수도 있습니다. 이는 MySQL 서버에 SSH로 접속하여 작업할 때 특히 유용합니다.

명령 실행 방법

mysqlshow -u username -p password database_name table_name

예시

mysqlshow -u root -p my_database users

인덱스가 없을 경우 해야 할 일

SHOW INDEX를 실행하거나 INFORMATION_SCHEMA.STATISTICS를 조회했을 때 인덱스가 표시되지 않으면, 해당 테이블에 적절한 인덱스가 없을 수 있습니다. 이 경우 필요에 따라 인덱스를 생성하여 검색 성능을 향상시킬 수 있습니다.

새 인덱스 생성

CREATE INDEX idx_column ON users (email);

기본 키 설정 (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

불필요한 인덱스 삭제

ALTER TABLE users DROP INDEX idx_column;

4. 인덱스 사용 여부 확인

인덱스가 올바르게 작동하는지 확인하는 것은 MySQL 성능 최적화에서 중요한 단계입니다. 이 섹션에서는 EXPLAIN 명령과 Performance Schema를 활용하여 쿼리가 어떤 인덱스를 사용하는지 확인하는 방법을 설명합니다.

EXPLAIN을 사용한 쿼리 분석

EXPLAIN 명령은 특정 SQL 쿼리가 어떻게 실행될지를 시각화하는 데 사용됩니다. 이를 통해 사용된 인덱스, 접근 방법, 실행 계획을 분석할 수 있어 인덱스가 의도대로 작동하는지 확인하는 데 유용합니다.

기본 구문

EXPLAIN SELECT * FROM table_name WHERE condition;

예시

예를 들어, email 컬럼을 조건으로 하여 users 테이블을 검색하려면:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

예시 출력

핵심 포인트

• type = ALL이면, 쿼리가 전체 테이블 스캔을 수행하고 있으므로 인덱스가 필요할 가능성이 높습니다.
• key에 인덱스 이름이 나타나면 해당 인덱스가 사용되고 있는 것입니다.
• rows 값이 너무 크면 쿼리 최적화가 필요할 수 있습니다.

Performance Schema 사용

MySQL의 performance_schema를 사용하면 쿼리 실행 중에 어떤 인덱스가 사용되는지, 그리고 사용 빈도를 자세히 분석할 수 있습니다.

쿼리 실행 통계 가져오기

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

특정 테이블의 인덱스 사용 여부 확인

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

인덱스가 사용되지 않을 경우 해야 할 일

1. 쿼리 검토

인덱스가 사용되지 않는 경우, 쿼리 구조에 문제가 있을 수 있습니다. 예를 들어, 다음과 같은 패턴은 인덱스 사용을 방해할 수 있습니다.

❌ 잘못된 예시 (함수가 인덱스 사용을 비활성화)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ LOWER(email) 때문에 email에 대한 인덱스가 무시될 수 있습니다.

✅ 수정된 예시 (함수 사용을 피함)

SELECT * FROM users WHERE email = 'test@example.com';

2. 인덱스 재생성

기존 인덱스가 제대로 작동하지 않을 경우, 해당 인덱스를 삭제하고 다시 생성하면 성능이 향상될 수 있습니다.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. 통계 업데이트

테이블 통계가 오래되면 MySQL이 인덱스를 최적화해서 사용하지 않을 수 있습니다. 다음 명령으로 통계를 새로 고칠 수 있습니다:

ANALYZE TABLE users;

5. 인덱스 관리

MySQL 인덱스는 데이터 검색 성능을 향상시키는 데 필수적입니다. 그러나 적절히 관리되지 않으면 전체 데이터베이스 성능을 저하시킬 수 있습니다. 이 섹션에서는 인덱스를 생성, 삭제 및 불필요한 인덱스를 식별하는 방법을 자세히 설명합니다.

인덱스 생성

적절한 인덱스를 생성하면 데이터 검색 속도를 크게 높일 수 있습니다. MySQL에서는 CREATE INDEX 또는 ALTER TABLE을 사용하여 인덱스를 추가할 수 있습니다.

기본 구문

CREATE INDEX index_name ON table_name(column_name);

예시

users 테이블의 email 열에 인덱스를 추가하려면:

CREATE INDEX idx_email ON users(email);

다중 열 인덱스 (복합 인덱스)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

고유 인덱스

CREATE UNIQUE INDEX idx_unique_email ON users(email);

기본 키 설정 (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

인덱스 삭제

불필요한 인덱스를 제거하면 데이터베이스 오버헤드를 줄이는 데 도움이 됩니다.

기본 구문

ALTER TABLE table_name DROP INDEX index_name;

예시

예를 들어, idx_email이라는 인덱스를 삭제하려면:

ALTER TABLE users DROP INDEX idx_email;

불필요한 인덱스 식별 및 제거

사용되지 않는 인덱스 확인

SELECT * FROM sys.schema_unused_indexes;

테이블 상태 확인 (인덱스 영향)

SHOW TABLE STATUS LIKE 'users';

불필요한 인덱스 제거

ALTER TABLE users DROP INDEX idx_unused;

삭제 후에는 ANALYZE TABLE을 사용하여 통계를 업데이트하는 것이 권장됩니다.

ANALYZE TABLE users;

6. 인덱스 최적화 (성능 향상)

적절한 인덱스 관리는 MySQL 쿼리 성능을 크게 향상시킬 수 있습니다. 그러나 단순히 인덱스를 생성하는 것만으로는 충분하지 않으며, 적절한 설계, 관리 및 모니터링이 최적의 성능을 유지하는 데 필요합니다.

적절한 인덱스 설계

잘 설계된 인덱스는 MySQL에서 검색 속도를 크게 향상시킬 수 있습니다.

인덱스를 적용해야 하는 경우

인덱스를 적용하면 안 되는 경우

슬로우 쿼리 로그 사용

슬로우 쿼리 로그를 사용하면 장시간 실행되는 쿼리를 식별하고 어떤 인덱스가 적용되지 않았는지 분석할 수 있습니다.

슬로우 쿼리 로그 활성화

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

슬로우 쿼리 로그 확인

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

슬로우 쿼리 분석

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

인덱스 적용 예시

CREATE INDEX idx_last_login ON users(last_login);

통계 업데이트 (ANALYZE & OPTIMIZE)

ANALYZE TABLE (통계 업데이트)

ANALYZE TABLE users;

OPTIMIZE TABLE (조각 모음)

OPTIMIZE TABLE users;

7. 인덱스 FAQ (자주 묻는 질문)

MySQL 인덱스는 데이터베이스 성능을 향상시키는 필수 메커니즘입니다. 그러나 적절히 관리되지 않으면 반대 효과를 낼 수 있습니다. 이 섹션에서는 MySQL 인덱스와 관련된 자주 묻는 질문(FAQ)과 답변을 정리했습니다.

인덱스를 더 많이 추가하면 검색 속도가 향상될까요?

A. 반드시 그렇지는 않습니다.

인덱스는 쿼리 성능을 향상시키기 위해 설계되었지만, 너무 많은 인덱스를 추가하면 실제로 데이터베이스 성능이 저하될 수 있습니다.

• 쓰기 오버헤드 증가 (INSERT, UPDATE, DELETE)
• 인덱스 사용 여부는 쿼리에 따라 달라짐
• 불필요한 인덱스는 메모리를 차지함

어떤 컬럼에 인덱스를 적용해야 할까요?

A. 다음 유형의 컬럼에 인덱스를 적용하는 것이 효과적입니다:

인덱스가 자동으로 생성되나요?

A. 일부 인덱스는 자동으로 생성되지만, 다른 인덱스는 수동으로 추가해야 합니다.

자동 생성된 인덱스

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

수동 생성된 인덱스

CREATE INDEX idx_email ON users(email);

B-Tree, Hash, FULLTEXT 인덱스 중 어떻게 선택해야 할까요?

인덱스 크기를 확인하려면 어떻게 해야 하나요?

SHOW TABLE STATUS LIKE 'users';

인덱스가 사용되고 있는지 확인하려면 어떻게 해야 하나요?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

언제 인덱스를 제거해야 할까요?

불필요한 인덱스 제거

ALTER TABLE users DROP INDEX idx_unused;

8. 요약

이 기사에서는 MySQL 인덱스를 기본 개념부터 검증 방법, 관리, 최적화, 자주 묻는 질문까지 포괄적으로 다루었습니다. 여기서는 각 섹션의 핵심 포인트를 검토하고 MySQL 인덱스 관리 최적화를 위한 모범 사례를 요약합니다.

핵심 요점

MySQL 인덱스란?

• 인덱스는 데이터 검색을 빠르게 하는 메커니즘입니다.
• B-Tree, Hash, FULLTEXT 인덱스 등 여러 종류가 있습니다.
• WHERE 절, JOIN, ORDER BY에 사용되는 컬럼에 적용할 때 가장 효과적입니다.

MySQL 인덱스 확인 방법

• SHOW INDEX 명령을 사용하여 인덱스 목록을 확인합니다.
• INFORMATION_SCHEMA.STATISTICS를 사용해 상세 정보를 조회합니다.

인덱스 사용 여부 확인 방법

• EXPLAIN을 사용해 쿼리가 어떤 인덱스를 사용하는지 확인합니다.
• Performance Schema를 활용해 인덱스 사용 빈도를 분석합니다.

인덱스 관리

• CREATE INDEX를 사용해 적절한 컬럼에 인덱스를 적용합니다.
• ALTER TABLE DROP INDEX로 불필요한 인덱스를 제거합니다.
• SHOW TABLE STATUS로 인덱스 크기를 확인하고 필요에 따라 최적화합니다.

인덱스 최적화 (성능 향상)

• 자주 사용되는 WHERE, JOIN, ORDER BY 컬럼에 인덱스를 적용합니다.
• Slow Query Log를 사용해 느린 쿼리를 식별하고 최적화합니다.
• ANALYZE TABLE 및 OPTIMIZE TABLE로 통계를 업데이트합니다.

MySQL 인덱스 관리 모범 사례

1. 인덱스를 적용하기 전에 쿼리 병목 현상을 파악합니다.
2. 적절한 인덱스를 선택합니다.

• 단일 컬럼 인덱스와 복합 인덱스를 올바르게 사용합니다.
• 고유 제약이 필요할 경우 UNIQUE INDEX를 사용합니다.

1. 불필요한 인덱스를 정기적으로 제거합니다.

• SHOW INDEX와 schema_unused_indexes를 활용해 사용되지 않는 인덱스를 식별합니다.

1. 통계를 정기적으로 업데이트합니다.

• ANALYZE TABLE로 통계를 업데이트합니다.
• 삭제 및 업데이트로 인한 조각화를 해결하기 위해 OPTIMIZE TABLE을 실행합니다.

다음 단계

✅ 실무 체크리스트
✅ SHOW INDEX로 현재 인덱스를 확인했나요?
✅ Slow Query Log를 활성화하고 느린 쿼리를 식별했나요?
✅ EXPLAIN으로 쿼리 실행 계획을 분석했나요?
✅ 불필요한 인덱스를 제거하고 적절한 인덱스를 생성했나요?
✅ ANALYZE TABLE로 통계를 업데이트했나요?

최종 요약

• 적절히 관리된 MySQL 인덱스는 검색 성능을 크게 향상시킵니다.
• Slow Query Log와 EXPLAIN을 활용해 인덱스 효율성을 분석하고 최적화합니다.
• 통계를 정기적으로 업데이트하고 테이블을 최적화해 데이터베이스 성능을 유지합니다.

이로써 MySQL 인덱스 관리에 대한 완전한 가이드를 마칩니다.

이 지식을 활용해 더 빠르고 효율적인 데이터베이스 시스템을 구축하세요. 💡🚀