💻 개발/📀 MySQL

[혼자공부하는SQL] 17강

pangil_kim 2025. 2. 26. 04:10
728x90

06-3. 인덱스의 실제 사용

0. 시작하기 전에

1) 인덱스 생성

CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC | DESC]

2) 인덱스 제거

DROP INDEX 인덱스_이름 ON 테이블_이름

1. 인덱스 생성과 제거 문법

1) 인덱스 생성 문법

  • 테이블을 생성할 대 특정 열을 키본 키, 고유 키로 생성하면, 인덱스가 자동 생성된다는 것은 이미 학습했다.
  • Primary Key 문법을 사용하면 클러스터형 인덱스가, Unique 문법을 사용하면 보조 인덱스가 자동으로 생성된다.
  • 그 외에 직접 인덱스를 생성하려면 어떻게 해야 할까?

(1) 직접 인덱스를 생성하는 문법

CREATE [UNIQUE] INDEX 인덱스 이름
    ON 테이블_이름 (열_이름) [ASC | DESC]
  • 이렇게 생긴 인덱스는 ‘보조 인덱스’이다.
  • 특징
    • UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
    • UNIQUE로 인덱스를 생성하려면, 기존에 입력된 값들에 중복이 있으면 안 된다.
    • 또한, 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니, 신중해야 한다.

2) 인덱스 제거 문법

(1) 문법

DROP INDEX 인덱스_이름 ON 테이블_이름

(2) 주의해야 할 점

  • 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
  • ALTER TABLE 문으로 기본 키나 고유 키를 제거하면, 자동으로 생성된 인덱스도 제거할 수 있다.

2. 인덱스 생성과 제거 실습

1) 인덱스 생성 실습

(1) 테이블 상태 정보를 확인

SHOW TABLE STATUS LIKE 'member'
  • 설명
    • SHOW TABLE STATUS : 현재 데이터베이스에 있는 모든 테이블의 상태와 정보를 출력한다.
    • LIKE 'member' : 특정 테이블(member)만 필터링하여 해당 테이블의 상태 정보를 조회한다.
  • 출력 정보 예시
| 필드명 | 설명 |
| --- | --- |
| Name | 테이블 이름 |
| Engine | 사용 중인 스토리지 엔진(InnoDB, MyISAM 등) |
| Version | 테이블의 버전 |
| Row_format | 테이블의 행 저장 형식(Compact, Dynamic 등) |
| Rows | 테이블에 저장된 레코드(행) 수 |
| Avg_row_length | 평균 행 크기(바이트) |
| Data_length | 실제 데이터 크기(바이트) |
| Max_data_length | 최대 데이터 크기 |
| Index_length | 보조 인덱스 크기(바이트) |
| Data_free | 할당되었지만 사용되지 않은 공간 |
| Auto_increment | 현재 AUTO_INCREMENT 값 |
| Create_time | 테이블 생성 시간 |
| Update_time | 테이블 마지막 갱신 시간 |
| Check_time | 마지막으로 CHECK TABLE 실행한 시간 |
| Collation | 테이블의 문자 집합 및 정렬 방식 |
| Comment | 테이블에 대한 주석 또는 추가 정보 |

(2) 보조 인덱스 추가하기

-- addr 열에 보조 인덱스 만들기
CREATE INDEX idx_member_addr 
   ON member (addr);
-- 인덱스 확인하기
CREATE INDEX idx_member_addr 
   ON member (addr);

-- 테이블의 인덱스 정보 조회하기
SHOW INDEX FROM member;

(3) 테이블의 통계 정보를 업데이트

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

  • 보조 인덱스의 길이가 추가된 것을 볼 수 있다.

(4) 회원 수로 UNIQUE 인덱스 만들기

CREATE UNIQUE INDEX idx_member_mem_number
    ON member (mem_number); -- 오류 발생
  • UNIQUE INDEX로 생성했지만, mem_number이 중복되었기 때문에 오류가 발생한다.

(5) 회원 이름으로 UNIQUE 인덱스 만들기

CREATE UNIQUE INDEX idx_member_mem_name
    ON member (mem_name);
  • 현재는 중복되는 행이 없어서 실행이 된다.
INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');
  • mem_name이 UNIQUE 인덱스로 설정되어 있기에, 추가가 되지 않는다.

2) 인덱스 사용하기

(1) 전부 다 검색하는 경우에는 인덱스를 사용하지 않는다. → 인덱스 사용 X

SELECT * FROM member;

(2) WHERE 절에서 인덱스를 사용해야 한다. → 인덱스 사용 O

SELECT mem_id, mem_name, addr 
    FROM member 
    WHERE mem_name = '에이핑크';

(3) 인덱스를 실제로 사용한 경우

-- 새로운 인덱스 생성
CREATE INDEX idx_member_mem_number
    ON member (mem_number);

 -- 인덱스 적용
ANALYZE TABLE member;

-- 인덱스 사용하여 SELECT
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 7;

(4) 인덱스를 사용하지 않는 경우 1 : 많은 건수를 처리해야 하는 경우

-- 문제 : 인덱스가 있더라도 많은 건수가 발생하면 MySQL이 인덱스를 사용하지 않는다.
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 1;

(5) 인덱스를 사용하지 않는 경우 2 : 인덱스에 가공한 경우

-- 문제 : 인덱스에 가공을 하면, 인덱스를 사용하지 않는다.
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number*2 >= 14;     

-- 해결책 : 인덱스를 가공하지 않지만, 인덱스를 사용하는 경우
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 14/2;

3) 인덱스 제거하기

-- 보조 인덱스 제거
DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

-- 클러스터형 인덱스 제거
-- (아래의 예시에서는 오류가 발생 : 참조키로 사용되고 있어서)
ALTER TABLE member 
    DROP PRIMARY KEY;

(1) 특정 데이터베이스(market_db)의 외래 키(Foreign Key) 제약 조건을 조회

SELECT table_name, constraint_name
    FROM information_schema.referential_constraints
    WHERE constraint_schema = 'market_db';
  • information_schema.referential_constraints : MySQL의 시스템 테이블로, 데이터베이스의 외래 키 정보를 저장한다.
  • constraint_schema = 'market_db' : 'market_db' 데이터베이스에서 설정된 외래 키 제약 조건만 필터링한다.
  • table_name : 외래 키가 설정된 테이블 이름을 가져온다.
  • constraint_name : 해당 테이블의 외래 키 제약 조건 이름을 가져온다.

(2) 해결 방법

ALTER TABLE buy 
    DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member 
    DROP PRIMARY KEY;
728x90
댓글수0