5.2 인덱스 조정으로 착한 쿼리 만들기

인덱스 변경, 삭제, 추가로 튜닝해보자

---

인덱스 없이 작은 규모의 데이터를 조회하는 나쁜 SQL 문

1. 튜닝 전

1건 데이터출력 약 0.25초 소요 

SELECT * FROM 사원
WHERE 이름 = 'Georgi'
AND 성 = 'Wielonsky';

 

2. 튜닝 전 실행 계획 분석

type : ALL

EXPLAIN
SELECT *
FROM 사원
WHERE = 'Georgi'
AND 성 = 'Wielonsky';

 

WHERE 조건절에 사용한 열의 DISTINCT데이터 개수 확인

성_개수가 더 컸음

SELECT 
COUNT(DISTINCT(이름)) 이름_개수,
COUNT(DISTINCT(성)) 성_개수,
COUNT(1) 전체
FROM 사원;

 

3. 튜닝

더 큰 개수를 가진 성_개수가 선두인 index 추가

ALTER TABLE 사원 
ADD INDEX I_사원_성_이름(성,이름);

 

4. 결과

0.25 -> 0.00

type : ref

---

인덱스를 하나만 사용하는 나쁜 SQL 문

1. 튜닝 전

소요 시간 0.25초

SELECT * 
FROM 사원
WHERE 이름 = 'Matt'
OR 입사일자 = '1987-03-31';

 

2. 튜닝 전 실행 계획 분석

type : ALL

EXPLAIN
SELECT * 
FROM 사원
WHERE 이름 = 'Matt'
OR 입사일자 = '1987-03-31';

 

WHERE조건절에 있는 조건 하나씩 조회 => 데이터 건수가 작았음 => 인덱싱이 효율적임

SELECT CNT(1)
FROM 사원
WHERE 이름='Matt';

SELECT CNT(1)
FROM 사원
WHERE 입사일자 = '1987-03-31';

 

인덱스 확인 => 이름 INDEX가 없었음

SHOW INDEX FROM 사원;

 

3. 튜닝

이름 INDEX 추가

ALTER TABLE 사원
ADD INDEX I_이름(이름);

 

4. 결과 

소요 시간 0.25 -> 0.00

type : index_merge

---

큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문

1. 튜닝 전

30만건 데이터 변경 시 소요 시간 34.13초

UPDATE 사원출입기록
SET 출입문 = 'X'
WHERE 출입문 = 'B';

 

2. 튜닝 전 실행 계획 분석

변경하는 열에 index가 있는지 확인 => I_출입문 이 있었음

 

3. 튜닝

자주변경하는 열의 index 제거 (자주 사용안하는 거면 삭제해도 됨, 배치성 작업이면 삭제하고 후에 추가해줌)

ALTER TABLE 사원출입기록
DROP INDEX I_출입문;

 

4. 결과

30만건 데이터 변경 시 소요 시간 34.13초 -> 3.82초

---

비효율적인 인덱스를 사용하는 나쁜 SQL문

1. 튜닝 전

소요 시간 0.04초

SELECT 사원번호, 이름, 성
FROM 사원
WHERE 성별 = 'M'
AND 성 = 'Baba';

 

2. 튜닝 전 실행 계획

데이터 개수 분석해보기

성_개수는 1637, 성별_개수는 2

SELECT
COUNT(DISTINCT(성)) 성_개수
COUNT(DISTINCT(성별)) 성별_개수
FROM 사원;

 

인덱스 확인

I_성별_성 이 있었음 => 비효율적 

show index from 사원;

 

 

3. 튜닝

ALTER TABLE 사원
DROP INDEX I_성별, 성
ADD INDEX I_성_성별(성,성별);

 

4. 결과

소요 시간 0.04초 -> 0.01초

---

기타

auto commit 확인 => 1이면 자동 커밋

select @@automcommit;

 

 

auto commit 0으로 설정해서 자동 커밋되지 않도록 설정

set autocommit=0;