06-3. 인덱스의 실제 사용
👉🏻 인덱스 생성과 제거 문법
🧩 인덱스 생성
✅ 자세한 문법
✅ 간단한 문법
- UNIQUE는 생략 가능
- ASC나 DESC중에 선택, 기본은 ASC
❗UNIQUE는 주의
- UNIQUE를 사용할 경우 해당 열의 데이터 값이 중복을 허용하지 않음
- Ex.이름은 중복된 사람이 있기 때문에 중복 허용해야함
이메일은 중복 허용하지 않아도 됨
🧩 인덱스 제거
✅ 간단한 문법
✅ 자동으로 생성된 인덱스
- CREATE자동으로 생성되는것이 아니라 내가 추가로 인덱스를 만드는 것
- PRIMARY KEY와 UNIQUE KEY로 지정하면 자동으로 인덱스가 생성됨
- 자동으로 생성된 인덱스는 DROP으로 지우는 게 아니라 해당 KEY를 제거해야 한다
🧩 인덱스 생성및 제거 실습
🎮 market_db 초기화
- ID 열로 정렬되어있음
🎮 인덱스 확인해보기
- PRIMARY KEY 가 mem_id열에 지정되어있다
🎮 테이블의 인덱스 정보 확인
- Data_length: 한 페이지 크기 16384 = 16KB
- 즉 지금 만든 테이블은 한 페이지짜리 책이라고 보면 됨
- index_length: 보조 인덱스 크기
- 만들어져 있지 않아 0으로 나옴
🎮 보조 인덱스 만들어주기
- 인덱스 이름은 내가 지정
- member 테이블의 addr열에 인덱스를 만들겠다
- 추가됨
- Non_unique: 중복 허용 여부
- 1이기 때문에 중복을 허용한다는 뜻
- index_length : 0으로 나옴
- 인덱스를 만들고 난 뒤에는 ANALYZE TABLE로 실제 인덱스를 적용시켜줘야한다
- 보조 인덱스도 16KB, 한 페이지짜리 인덱스가 만들어짐
- (데이터 양이 얼마 안돼서) 인덱스 양이 얼마 안 되기 때문에 한 페이지 안에 다 들어가지만
인덱스 양이 늘어나면 Data_length 값도 늘어날 것
🎮 인원수로 UNIQUE INDEX 만들기
- UNIQUE는 고유 인덱스라서 데이터 중복 허용하지 않음
- 하지만 멤버 수에는 중복된 데이터가 있음
- 꼭 멤버 수로 인덱스를 만들고 싶다면 UNIQUE를 제거하고 만들면 된다
🎮 회원 이름으로 인덱스 만들기
- 회원 이름은 중복되는 것이 없어서 만들어지긴 함
- 하지만 위험! 혹시라도 같은 이름의 데이터가 새로 들어올 경우
- 이미 UNIQUE INDEX로 만들어 중복되는 이름의 데이터는 들어올 수 없다
* 현재 데이터에 중복이 없다고 무작정 UNIQUE인덱스로 만들어버리면 안된다!
🎮 모든 인덱스 확인해보기
🎮 인덱스 사용
- 여기서는 인덱스를 사용하지 않음
- 전부 다 조회하기 때문에 인덱스를 사용하지 않음
- 예를 들어 '책을 전부 읽어보세요'라고 했는데 책 뒤의 찾아보기를 왔다갔다하며 읽는 사람은 없음
- Execution Plan을 확인해보면
- Full Table Scan = 인덱스를 사용하지 않고 테이블 전체를 뒤졌다
- 인덱스 만든 것들 조회해보기
- EXECUTION PLAN을 확인해보면
- 인덱스를 적용한 열을 조회했음에도 불구하고 FULL TABLE SCAN을 하고 있음
* 이유: SELECT 다음에 나오면 INDEX와 관련이 없다!
* WHERE절에 나와야만 INDEX를 사용하게 된다
- EXECUTION PLAN을 확인해보면
- 인덱스를 사용해서 검색했음을 알 수 있다
* 인덱스를 만들고, WHERE 조건에서 해당 열을 활용하면 인덱스를 사용한것
🎮 멤버수에 인덱스 만들어보기
- UNIQUE 없이 해서 성공
- 7명 이상인 경우 조회
- EXECUTION PLAN을 통해 인덱스를 사용했는지 확인해보면
- 인덱스를 사용했음
🎮 멤버 수가 1명 이상인 경우 조회
- 모든 그룹이 멤버 수가 한 명 넘음
- 인덱스를 사용하지 않음
* 인덱스가 있더라도 MySQL이 자체적으로 효율성을 따져서 인덱스를 활용할지 말지 판단한다
🎮 다른 조건 활용해보기
- mem_number >= 7 과 같은 얘기임
- 하지만 인덱스를 사용하지 않는다!!
* 실제 인덱스를 사용하려면 WHERE절에서 인덱스가 만들어진 열을 사용하되, 아무런 가공을 하면 안된다
- 가공이 되면 인덱스를 사용하지 않는다
- 해당 열을 건드리지 않으면 인덱스를 사용한다
* 결과는 같지만 인덱스를 사용하느냐 안 하느냐의 차이가 있기 때문에 열 자체에는 가공하지 않는게 좋다
🎮 인덱스 제거
- 직접 만든 인덱스는 DROP으로 지우고
- 키 지정을 통해 자동 생성된 인덱스는 키를 DROP해서 지운다
- 하지만 오류발생
- PK, FK로 회원 테이블과 구매테이블이 연결되어있기 때문
- 꼭 지우고 싶다면 외래키를 먼저 지워야 한다
- 외래키를 지우려면 외래키 이름을 알아야 한다
- 외래키 이름을 알려면 위와 같은 구문을 활용한다
- DB이름만 넣어주면 DB의 외래키를 볼 수 있다
- 외래키 먼저 지우고
- PRIMARY KEY를 지운다
출처: https://youtu.be/aiMSluMNzI8?si=puBukOPUBIVcKjzW
'MySQL > 혼공SQL' 카테고리의 다른 글
| 19강. 스토어드 함수와 커서의 개념, 커서(cursor)의 단계별 실습 방법 (0) | 2023.12.11 |
|---|---|
| 18강. 스토어드 프로시저(stored procedure)의 개념과 사용 방법(입출력 매개변수의 활용 방법) (1) | 2023.12.06 |
| 16강.인덱스의 내부 작동 원리와 구조, 인덱스에서 데이터 검색하기 (2) | 2023.12.06 |
| 15강. 인덱스의 개념과 장단점, 클러스터형 인덱스와 보조 인덱스 (0) | 2023.11.29 |
| 14강. 가상의 테이블: 뷰(생성, 수정, 삭제) (2) | 2023.11.27 |