[혼자 공부하는 SQL] 인덱스의 생성과 제거 문법 (CREATE INDEX, DROP INDEX)

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

 

인덱스의 생성과 제거

인덱스 생성

CREATE INDEX [인덱스 이름]

CREATE UNIQUE INDEX [인덱스 이름]

이렇게 두 가지로 분류가 된다.

CREATE UNIQUE INDEX로 만들어진 INDEX를 갖은 테이블은

INSERT 시 중복이 허용이 되지 않는다.

인덱스 제거 문법

인덱스를 제거하는 문법은 DROP INDEX [인덱스 이름] ON [테이블 이름]

입니다.

주의할 점은 자동 생성된 INDEX들은 DROP INDEX로 제거하지 못한다는 점입니다.

자동 생성된 인덱스들은 ALTER TABLE문으로 기본 키나 고유 키를 제거하여, 자동 생성된 인덱스를 제거할 수 있습니다.

SHOW INDEX FROM

SHOW INDEX FROM member; 명령어로

member 테이블의 mem_id에 자동 클러스터형 인덱스로, PRIMARY KEY가 생성되어 있는 것을 볼 수 있습니다.

인덱스 목록 확인이 가능하다.

테이블의 인덱스 정보 보기 (SHOW TABLE STATUS LIKE)

SHOW TABLE STATUS LIKE 'member';

명령어를 통해서 인덱스의 세부 정보를 볼 수 있다.

다음과 같이 Rows, Avg_row_length, Data_length 등 페이지의 크기나 생성일자 등 세부정보를 볼 수 있다.

Data_length를 보면 16384인데, 16KB를 의미한다.

한 페이지가 16KB로 되어있다. 실제로는 엄청나게 많은 양이다. 한 페이지짜리 책이라고 보면 된다.

Index_length는 책 뒤의 찾아보기이다. 보조 인덱스의 크기를 의미한다.

현재 보조 인덱스가 만들어져있지 않기 때문에 0이라고 표현이 되어있다.

보조 인덱스 추가

주소 열에 INDEX를 만들겠다.

단순 INDEX를 생성했다.

SHOW INDEX FROM member;를 하면 

인덱스의 세부 정보를 확인할 수 있다.

member 테이블의 addr 컬럼에

idx_member_addr라는 이름의 인덱스가 생성된 것을 확인할 수 있다.

Non_unique에 1(True)이 되어 있는 것을 볼 수 있다.

중복이 허용되는 단순 INDEX이다.

인덱스 적용

SHOW TABLE STATUS LIKE 'member'; 명령어를 통해서 인덱스의 세부정보를 확인한다.

또한 ANALYZE TABLE member; 명령어를 통해서

member 테이블에 지금까지 만들어진 인덱스를 적용한다.

그 결과로, Index_length의 값이 기존에는 0이었는데, Index_length의 값이 16384(16KB) 즉 한 페이지가 만들어진 것을 볼 수가 있다. 

인덱스 적용이 성공한 것을 볼 수 있다.

지금은 소규모 데이터량이어서 괜찮지만

예를 들어 데이터의 양이 100만건을 넘어가면 

Data_length, Index_length의 값이 커진다.

CREATE UNIQUE INDEX

member 테이블의 회원명으로 UNIQUE INDEX를 만들면

다음과 같이 오류가 발생하는 것을 볼 수 있다.

mem_number의 컬럼에 중복된 값이 있기 때문이다.

UNIQUE를 제거한다면 만들 수 있다.

회원명으로 UNIQUE INDEX를 만들었다.

성공한 것을 확인할 수 있다.

하지만 회원명은 언제든지 겹칠 수 있으므로

위험한 것이다.

SHOW INDEX FROM member; 명령어를 통해서

member 테이블에 지금까지 만든 INDEX 생성이 성공한 것을 볼 수 있다.

하지만

더 이상 같은 회원 이름은 회원가입이 불가능하다.

말이 안되는 이야기다.

mem_name에 진짜로 중복이 안 되냐 이런 경우에만 UNIQUE를 만들 수 있다.

예를 들어 email, 주민등록번호는 UNIQUE 인덱스를 만들어도 무방하다.

다시 지금까지 만든 인덱스를 적용시킨다.

member 테이블에 INDEX가 정상적으로 3개가 만들어진 것을 볼 수 있다.

하지만 INDEX를 만드는 것보다 더 중요한 것은 INDEX를 사용하는 것이다.

Full Table Scan

인덱스를 생성하여도 SELECT * FROM member; 명령어를 실행하면

모든 열을 조회하는 것이기 때문에 인덱스를 찾아볼 필요가 없다.

그래서 Full Table Scan을 한 것이다.

인덱스를 사용하지 않고, 전체 테이블을 뒤진 것이다.

이외에는 인덱스를 사용한다고 볼 수 있다.

이번에는 mem_id, mem_name, addr을 member 테이블에서 조회를 하였다.

그리고 Execution Plan을 확인을 하였는데

또 다시, Full Table Scan을 한 것을 볼 수 있다.

인덱스가 있음에도 불구하고 Full Table Scan을 한 것이다.

WHERE 절에 나와야만 인덱스를 사용한다.

에이핑크를 조회를 하니 

Single Row (constant)를 사용해서 데이터를 조회한 것을 확인할 수 있다.

mem_name 열의 인덱스를 만들고

WHERE 절에 mem_name을 사용을 하면

인덱스를 사용을 한다.

지금은 데이터가 10건이어서 표시가 안 나지만

만약에 데이터가 100만건, 1,000만건이었다면 엄청나게 빠른 조회결과가 된다.

실제 WHERE절에 열이름이 나와야 인덱스를 쓴다는 것을 확인하면 좋다.

이번에는 mem_number에 인덱스를 만들고

ANALYZE TABLE member; 명령어를 통해서 인덱스를 적용을 하였다.

그리고 mem_number >= 7 인 회원을 조회를 하면

Executin Plan에서 확인 시 다음과 같이

Index Range Scan을 사용한 것을 볼 수 있다.

이번에는 mem_number >= 1인 결과를 조회하고 Execution Plan을 확인해보았다.

이번에는 다르게 Full Table Scan을 한 것을 볼 수 있다.

7이상인 데이터는 몇 건 없기 때문에 MySQL이 알아서 인덱스를 쓰는게 더 효과적이겠네해서 인덱스를 사용을 한 것이다.

1이상은 인덱스를 찾아봤자 더 비효율적일 것 같에 해서 Full Table Scan을 한 것이다.

MySQL이 알아서 판단을 한 것이다.

이번에는 mem_number*2 >= 14이면

mem_number >= 7 과 같으므로 인덱스가 사용이 되어야 하지만 Full Table Scan을 한 것을 볼 수 있다.

인덱스를 사용을 하려면 인덱스를 사용하는 컬럼을 아얘 가공을 해서는 안된다.

그러면 다음과 같은 방법으로 조회가 가능하다.

Index Range Scan을 사용한 것을 볼 수 있다.

인덱스를 갖은 컬럼을 가공하지 말고

비교하는 대상이 되는 데이터를 가공을 하는 것이다.

조금 복잡하긴 하지만, 인덱스 조회를 성공한 것을 확인할 수 있다.

결과는 똑같다. 하지만 인덱스를 사용하는 것이 훨씬 효율적이다.

DROP INDEX [인덱스 명] ON [테이블 명];

DROP INDEX 명령어를 통해서 3개의 인덱스를 모두 제거한 것을 확인할 수 있다.

ALTER TABLE member DROP PRIMARY KEY; 를 통해서 PRIMARY KEY를 지우려고 했는데

오류가 발생한다.

member 테이블을 buy 테이블이 참조하고 있기 때문이다.

information_schema.referential_constraints

다음과 같은 명령어를 통해서 market_db의 외래키 정보를 조회할 수 있다.

TABLE_NAME과 CONSTRAINT_NAME을 알아볼 수 있다.

buy 테이블을 통해서 buy_ibfk_1의 이름의 제약조건이 있는 것을 확인할 수 있다.

이 이름을 통해서 buy 테이블의 외래키를 제거가 가능하다.

ALTER TABLE buy DROP FOREIGN KEY buy_ibfk_1; 명령어를 통해서

buy 테이블의 외래키를 삭제했다.

그리고 

ALTER TABLE member DROP PRIMARY KEY; 명령어를 통해서

member 테이블의 기본키를 삭제를 했다.

 

감사합니다.

https://www.youtube.com/watch?v=aiMSluMNzI8&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=18