1) 인덱스 없이 작은 규모의 데이터를 조회하는 나쁜 SQL 문
(1) 현황분석
explain select * from 사원
where 이름 = 'Georgi'
and 성 = 'Wielonsky';- 사원 테이블 풀 스캔 및 스토리지 엔진에서 가져온 전체 데이터 중 조건절 Extra : Using where 출력
(2) 문제점
- 1 건의 데이터를 가져오고자 테이블 풀 스캔 방식 비효율적
- 이름 열과 성 열을 대상으로 인덱스를 생성하기 전에 먼저 더 다양한 값이 있는 열이 무엇인지 파악
-> 이름 열의 데이터에는 1200개 값이 있고 성 열의 데이터에는 1600개의 값이 있으므로, 데이터 범위를 더 축소할 수 있는 성 열을 선두 열로 삼아 인덱스 생성
(3) 튜닝 수행
alter table 사원
add index I_사원_성_이름 (성, 이름);
show index from 사원;- I-사원-성-이름 인덱스로 인덱스 스캔 수행
- 스토리지 엔진에서 성 열과 이름 열에 조건절을 써서 데이터에 접근하므로 1건의 데이터만 최종 반환
2) 인덱스를 하나만 사용하는 나쁜 SQL 문
(1) 현황분석
explain select *
from 사원
where 이름 = 'Matt'
or 입사일자 = '1987-03-01';- 사원 테이블 풀 스캔, 스토리지 엔진으로 모든 데이터 가져온 뒤 2개의 조건절 필터링
(2) 문제점
- 이름 = 'Matt' 조건 230건 입사일자 = '1087-03-31' 조건은 111건으로 전체 데이터가 약 30만 건에 달하는 사원 테이블의 데이터 건수와 비교 시 매우 적음
- 소량의 데이터 가져올 시 테이블 풀 스캔보다 인덱스 스캔이 효율적
(3) 튜닝 수행
alter table 사원
add index I_이름(이름);- 이름 인덱스 생성
- 2개의 조건절이 인덱스 스캔으로 수행되고 각 결과는 병합된다.(type: index_merge)
- 조건 결과가 합쳐진 뒤 Extar : Using union 최종 결과 출력
3) 큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문
(1) 현황분석
update 사원출입기록
set 출입문 = 'X'
where 출입문 = 'B';- MySQL에서 DML 문을 수행 시 커밋은 기본적으로 자동 저장된다. 따라서 set@@autocommit=0;으로 자동 커밋되지 않도록 임의 설정한다.
- UPDATE 문 역시 수정할 데이터에 접근한 뒤 SET 절의 수정값으로 변경하므로, 인덱스로 데이터에 접근한다는 측면에서 인덱스 존재 여부 중요
(2) 문제점
- 데이터 변경 시 테이블 뿐만 아니라 인덱스도 포함되므로, 인덱스가 많은 테이블의 데이터 변경 시 성능적으로 불리
(3) 튜닝 수행
alter table 사원출입기록
drop index I_출입문;- 출입문 인덱스 제거
- PRIMARY로 데이터에 접근
4) 비효율적인 인덱스를 사용하는 나쁜 SQL 문
(1) 현황분석
select 사원번호, 이름, 성
from 사원
where 성별 = 'M'
and 성 = 'Baba';- 성별_성 인덱스로 사원 테이블 접근, 성별과 성 열에 고정된 값으로 조건절(ref항목: const, const)을 작성하여, 스토리지 엔진에서 인덱스 스캔으로 원하는 데이터를 가져와 출력
(2) 문제점
- 성은 남자/여자 두개의 개수가 존재하므로, 다양한 종류의 값을 지닌 성 열을 먼저 활용하면 데이터 접근 범위를 줄일 수 있다.
(3) 튜닝 수행
alter table 사원
drop index I_성별_성,
add index I_성_성별(성, 성별);- 성_성별 인덱스 순서 성, 성별로 변환
참고: 업무에 바로쓰는 SQL 튜닝 / 양바른 저
Pay it forward