안녕하세요, 혼자 공부하는 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
