인덱스 생성, 조회, 삭제 SQL 명령어 - MySQL

2025년 05월 01일 by __admin

    인덱스 생성, 조회, 삭제 SQL 명령어 - MySQL 목차

인덱스를 실제로 관리하는 SQL 명령어들을 알아보겠습니다. 실제로 MySQL에서 인덱스를 어떻게 만들고(CREATE), 현재 어떤 인덱스가 있는지 확인하고(SHOW), 더 이상 필요 없는 인덱스를 제거하는지(DROP) 구체적인 방법을 알아보겠습니다.

인덱스 생성, 조회, 삭제: 직접 관리해보기

1. 인덱스 생성 (CREATE INDEX / ALTER TABLE)

테이블에 인덱스를 추가하는 방법은 크게 두 가지가 있습니다. 테이블을 생성할 때 정의하는 방법과 이미 존재하는 테이블에 추가하는 방법입니다.

  • 이미 존재하는 테이블에 인덱스 추가: CREATE INDEX 또는 ALTER TABLE 사용
    • idx_column_name, uq_column_name, ft_text_column 등은 사용자가 지정하는 인덱스의 이름입니다. 어떤 테이블의 어떤 컬럼에 대한 인덱스인지 쉽게 알 수 있도록 명명 규칙을 정하는 것이 좋습니다. (예: idx_테이블명_컬럼명)
    • UNIQUE 키워드를 사용하면 해당 컬럼(들)에는 고유한 값만 저장될 수 있도록 제약이 걸리며, 중복 값 입력 시 오류가 발생합니다.
    • FULLTEXT 인덱스는 VARCHAR나 TEXT 타입의 컬럼에만 생성할 수 있습니다.
       
      -- 기본 B-Tree 인덱스 생성
CREATE INDEX idx_column_name ON table_name (column_name);

-- 여러 컬럼으로 구성된 복합 인덱스 생성
CREATE INDEX idx_multi_columns ON table_name (column1, column2);

-- 고유 인덱스 생성 (해당 컬럼(들)에 중복 값 허용 안 함)
CREATE UNIQUE INDEX uq_column_name ON table_name (column_name);

-- 전문 인덱스 생성 (텍스트 검색용)
CREATE FULLTEXT INDEX ft_text_column ON table_name (text_column);

-- ALTER TABLE 문을 이용한 인덱스 추가 (CREATE INDEX와 동일한 기능)
ALTER TABLE table_name ADD INDEX idx_another_column (another_column);
ALTER TABLE table_name ADD UNIQUE INDEX uq_another_column (another_column);
ALTER TABLE table_name ADD FULLTEXT INDEX ft_another_column (another_column);
  • 테이블 생성 시 인덱스 정의: CREATE TABLE 문 내에서 정의
    • PRIMARY KEY를 지정하면 자동으로 PRIMARY라는 이름의 고유 인덱스가 생성됩니다.
    • INDEX, KEY (INDEX의 동의어), UNIQUE INDEX, FULLTEXT INDEX 등을 사용하여 테이블 생성 시 인덱스를 함께 정의할 수 있습니다. 
      CREATE TABLE my_table (
    id INT AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    -- 기본 키 정의 (자동으로 UNIQUE INDEX 생성, InnoDB에서는 클러스터형 인덱스)
    PRIMARY KEY (id),

    -- 일반 인덱스 정의
    INDEX idx_username (user_name),

    -- 고유 인덱스 정의
    UNIQUE INDEX uq_email (email),

    -- 복합 인덱스 정의
    INDEX idx_user_created (user_name, created_at)
);
  • 인덱스 접두사(Prefix) 사용: (선택 사항) VARCHAR나 TEXT 같은 긴 문자열 컬럼 전체를 인덱싱하면 인덱스 크기가 너무 커질 수 있습니다. 이때 컬럼 값의 앞부분 일부만 잘라서 인덱싱할 수 있습니다.
    • 장점: 인덱스 크기를 줄여 저장 공간을 절약하고, DML 성능 저하를 완화할 수 있습니다.
    • 단점: 접두사 부분만 인덱싱되므로, 해당 인덱스로는 정렬(ORDER BY)이나 그룹화(GROUP BY) 작업 시 전체 값을 사용하지 못할 수 있습니다. LIKE 검색 시에도 제한이 있을 수 있습니다. 
      -- content 컬럼의 앞 100자만 인덱싱
CREATE INDEX idx_content_prefix ON posts (content(100));

 

2. 인덱스 조회 (SHOW INDEX)

테이블에 어떤 인덱스가 생성되어 있는지 확인하려면 SHOW INDEX (또는 SHOW INDEXES) 명령어를 사용합니다.

SHOW INDEX FROM table_name;

 

출력 결과 예시

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type Comment
my_table 0 PRIMARY 1 id A 1000 BTREE  
my_table 0 uq_email 1 email A 980 BTREE  
my_table 1 idx_username 1 user_name A 850 BTREE  
my_table 1 idx_user_created 1 user_name A 850 BTREE  
my_table 1 idx_user_created 2 created_at A 995 BTREE  
  1. Table: 테이블 이름
  2. Non_unique: 0이면 고유 인덱스(Unique 또는 Primary), 1이면 일반 인덱스
  3. Key_name: 인덱스 이름 (기본 키는 'PRIMARY')
  4. Seq_in_index: 복합 인덱스 내에서 해당 컬럼의 순서 (1부터 시작)
  5. Column_name: 인덱스에 포함된 컬럼 이름
  6. Collation: 정렬 방식 (A: 오름차순, NULL: 정렬 안 됨)
  7. Cardinality: 인덱스에 저장된 유니크한 값들의 추정치. 이 값이 높을수록 인덱스의 선택도(Selectivity)가 좋다고 판단하며, 쿼리 옵티마이저가 실행 계획을 세울 때 중요한 참고 자료가 됩니다. (실제 값과 다를 수 있으며, ANALYZE TABLE table_name; 명령으로 갱신할 수 있습니다.)
  8. Index_type: 인덱스 종류 (BTREE, HASH, FULLTEXT, RTREE 등)
  9. Comment: 인덱스에 대한 코멘트

MySQL의 시스템 데이터베이스인 information_schema의 STATISTICS 테이블을 직접 조회하여 인덱스 정보를 얻을 수도 있습니다.

SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'table_name';

 

3. 인덱스 삭제 (DROP INDEX / ALTER TABLE)

더 이상 사용하지 않거나 잘못 생성된 인덱스는 삭제하는 것이 좋습니다. 불필요한 인덱스는 저장 공간을 낭비하고 DML 성능을 저하시키기 때문입니다.

-- 특정 인덱스 삭제
DROP INDEX index_name ON table_name;

-- ALTER TABLE 문을 이용한 인덱스 삭제
ALTER TABLE table_name DROP INDEX index_name;

 

index_name에는 SHOW INDEX로 확인한 인덱스 이름을 정확히 지정해야 합니다. (컬럼 이름이 아님!) 인덱스를 삭제하면 해당 인덱스를 사용하던 쿼리의 성능이 크게 저하될 수 있으므로, 신중하게 결정해야 합니다. 기본 키(Primary Key)는 DROP INDEX로 삭제할 수 없습니다. 기본 키를 삭제하려면 ALTER TABLE 문을 사용해야 합니다.

-- 기본 키 삭제
ALTER TABLE table_name DROP PRIMARY KEY;

단, 해당 기본 키를 참조하는 외래 키(Foreign Key)가 다른 테이블에 존재하면 기본 키 삭제가 실패할 수 있습니다.

이제 인덱스를 직접 생성, 조회, 삭제하는 방법을 알게 되었습니다. 다음으로는 이렇게 생성된 인덱스를 어떻게 효과적으로 활용하고, 인덱스 설계를 통해 쿼리 성능을 최적화하는지에 대해 더 자세히 알아보겠습니다.

 

티스토리툴바