[MySQL] 색인(index)

색인

• 데이터의 위치를 빠르게 찾아주는 역할 → 고속 검색가능
• 테이블에 index를 생성하게 되면, index table을 생성해 index들을 관리한다.
  
  
  

단점

조회 성능은 향상되지만, insert, update, delete시에는 index 테이블의 데이터도 갱신 되기 대문에 속도가 저하된다.

색인 사용 시기

• 시스템의 속도가 느려질 때 사용
• SLOW QUERY 라는 느린 쿼리 필터링 기술을 이용하면, 쉽게 쿼리튜닝을 통한 속도향상을 할 수 있다.
  
  
  

인덱스 종류

• PRIMARY KEY : 주키 인덱스
  - 유니크 + NOT NULL

• UNIQUE KEY : 유니크 인덱스
  - 유니크

• NORMAL KEY : 일반 인덱스
  - 유니크 없는 일반 인덱스

• FULL TEXT : 풀텍스트 인덱스
  - 본문검색에 사용할 수 있ㄴ는 인덱스
  - 한글을 지원하지 않는다. (엘라스틱 서치가 필요한 이유)
  
  
  

EXPLAIN

• SQL 앞에 EXPLAIN 을 붙이면, 해당 SQL을 실행하기 위한 실행계획을 확인할 수 있다.
• 내가 만든 SQL이 실행될 때 INDEX를 이용하는지 여부를 알 수 있다.

---

실습

# 데이터베이스 a4가 존재하면 삭제
DROP DATABASE IF EXISTS a4;

# 데이터베이스 a4 생성
CREATE DATABASE a4;

# 데이터베이스 a4 선택
USE a4;

# 회원 테이블 생성, loginId, loginPw, `name`
## 조건 : loginId 칼럼에 UNIQUE INDEX 없이
CREATE TABLE `member` (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';

INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';

# 회원 2배 증가 쿼리만들고 회원이 백만명 넘을 때 까지 반복 실행
## 힌트1 : INSERT INTO `tableName` (col1, col2, col3, col4)
## 힌트2 : SELECT NOW(), UUID(), 'pw', '아무개'

INSERT INTO `member` (regDate, loginId, loginPw, `name`)
SELECT NOW(), UUID(), 'pw', '아무개'
FROM `member`;

# 회원수 확인
SELECT COUNT(*)
FROM `member`;

# 검색속도 확인
## 힌트 : SQL_NO_CACHE 는 쿼리캐시를 끄도록 명시합니다.(버전에 따라 작동 안할 수 도 있음)
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 유니크 인덱스를 loginID 칼럼에 걸기
## 설명 : mysql이 loginId의 고속검색을 위한 부가데이터를 자동으로 관리(생성/수정/삭제) 한다.
## 설명 : 이게 있고 없고가, 특정 상황에서 어마어마한 성능차이를 가져온다.
## 설명 : 생성된 인덱스의 이름은 기본적으로 칼럼명과 같다.
ALTER TABLE `member` ADD UNIQUE INDEX (`loginId`); 

## 인덱스 확인
SHOW INDEX FROM `member`;

# 검색속도 확인, loginId 가 'user1' 인 회원 검색
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;

## 인덱스 확인
SHOW INDEX FROM `member`;

# 회원 테이블 삭제
DROP TABLE `member`;

# 회원 테이블을 생성하는데, loginId에 uniqueIndex 까지 걸어주세요.
CREATE TABLE `member` (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) UNIQUE NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

## 인덱스 확인
SHOW INDEX FROM `member`;

# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';

INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';

# 회원 2배 증가 쿼리만들고 회원이 1만명 넘을 때 까지 반복 실행
## 힌트1 : INSERT INTO `tableName` (col1, col2, col3, col4)
## 힌트2 : SELECT NOW(), UUID(), 'pw', '아무개'

INSERT INTO `member` (regDate, loginId, loginPw, `name`)
SELECT NOW(), UUID(), 'pw', '아무개'
FROM `member`;

# 회원수 확인
SELECT COUNT(*)
FROM `member`;

# 인덱스 쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;

## 인덱스 확인
SHOW INDEX FROM `member`;

# 인덱스 안쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';