[기술적 선택] 쿼리 성능 개선을 위한 INDEX 적용

프로젝트에서 쿼리 성능을 높이기 위해 아래와 같은 방식을 도입하여 로컬에서 성능 테스트를 하였다. 

해당 테스트를 통해 쿼리 성능의 차이를 보고 프로젝트에 적용하게 되었다.  

 

저장 프로시저 사용하기 

 

프로시저란 

SQL 문장을 선언해서 MySQL에 저장하고 해당 SQL 을 마치 함수처럼 사용하는 것이다. 

즉, 만들어 두면 함수 호출하듯이 편하게 사용하는 것이다. 

 

프로시저 활용

create database academy;
use academy;

 

먼저, academy 라는 데이터베이스를 만들고 해당 데이터베이스를 사용한다. 

 

create table user (
id int not null,
name varchar(100),
campus varchar(100),
class int, 
gi int,
PRIMARY KEY (id)
);

 

user 라는 간단한 테이블 쿼리문을 날려 테이블을 만든다. 

 

/* 사용자 등록 프로시저 */ 
DELIMITER $$
CREATE PROCEDURE proc_user_insert (
	in id INT, 
	IN name VARCHAR(100), 
	IN campus VARCHAR(100), 
	IN class INT, 
	IN gi INT
)
BEGIN
  INSERT INTO user (id, name, campus, class, gi)
  VALUES (id, name, campus, class, gi);
END $$
DELIMITER ;

 

proc_user_inser 라는 프로시저를 생성한다. 

해당 프로시저는 단순히 user 테이블에 전달받은 값들을 insert 하는 프로시저라고 생각하면 된다. 

 

 

DELIMITER 는 왜 사용할까

저장 프로시저 내부에 사용하는 SQL문은 일반 SQL문이기때문에 세미콜론(;)으로 문장을 끝맺어야 한다.
이 때, 저장 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 위험을 막기 위해 구분자(;)를 다른 구분자로 바꿔주어야하는데 이 때 사용하는 명령어가 DELIMITER 이다.

따라서 저장 프로시저 생성 전에 구분자(DELIMITER)를 $$ 으로 바꿔주고 프로시저 작성이 끝났을 때 END $$ 로 저장 프로시저의 끝을 알려준다. 마지막으로 구분자를 원래대로 되돌리기 위해 구분자(DELIMITER)를 세미콜론(;)으로 바꿔준다.

 

프로시저 호출

CALL proc_user_insert( 84227, '김씨', '서울' , 4, 8); 
CALL proc_user_insert( 84237, '이씨', '서울' , 3, 8); 
CALL proc_user_insert( 84247, '강씨', '서울' , 1, 8); 
CALL proc_user_insert( 84257, '은씨', '서울' , 2, 8);
select * from user;

 

 

proc_user_inser 에게 전달한 값들을 입력하여 CALL 키워드로 호출한다.

그 후 select 쿼리문을 날리면 user 테이블에 정상적으로 호출한 것이 보인다. 

 

 

DELIMITER $$
CREATE PROCEDURE GetUser()
BEGIN
  SELECT * 
  FROM user 
  order by class;
END $$
DELIMITER ;

CALL GetUser();

 

 

이번에는 GetUser 프로시저를 만들어봤는데 해당 프로시저에서는 class 컬럼값에 대한 오름차순으로 정렬한 쿼리문을 호출한 프로시저이다. 이처럼, 저장 프로시저를 활용하면 쿼리문을 일일히 작성하지 않아도 함수처럼 사용하여 손쉽게 쿼리문과 동일한 결과를 조회 할 수 있다.

 

 

---

 

쿼리 성능 확인하기

 

 

미니 프로젝트를 진행하면서 공공 데이터 사이트에서 제공하는 더미 데이터를 테이블에 넣었던 경험이 있는데 데이터가 2만개가 넘기 때문에 어떻게 하면 쿼리 성능을 최적화 시킬 수 있을까에 대한 고민에서부터 해당 글을 작성한다. 

 

지금껏 쿼리문을 실행하기만 했었지, 어떻게 최적화를 해야하는지 생각해보지 않았다. 단지, 눈 앞에 보이는 결과물에만 충실했던터라 그때의 과거를 반성하고자 쿼리 성능을 확인하여 인덱스를 한 번 활용해보는 시간을 가져봤다. 

 

 

 

-- dongcode 테이블 검색 
select * from dongcode;

 

위와 같이 데이터들이 나오는데 행의 개수는 총 20551개이다. 

 

 

Profiling 옵션

select @@profiling;

my sql에서는 실행한 쿼리들이 각 수행 시간이 얼마가 걸렸는지 확일할 수 있는 Query Profiling을 제공한다. 

기본적으로 0으로 세팅이 되어있을텐데, 0이면 OFF 상태이다. 1이면 ON 상태이다. 1로 바꿔보자 

SET profiling = 1;

 

 

성능 측정

select * from dongcode d where d.dongName = "초동";

show profiles ;

 

먼저 dongcode 테이블에 dongName 중 "초동" 이라는 이름을 가진 정보들을 추출해보자. 

이후 show profiles 를 실행하게 되면 아래와 같이 뜬다. 

 

 

해당 쿼리의 수행 시간이 얼마나 걸렸나 측정한 값인 Duration이 보인다. -> 환경에 따라 오차가 발생하니 여러번 측정해서 평균을 구해야한다고 한다. 아무튼 show profiles; 를 활용하면 성능 측정이 가능하다는 것을 알았다. 

 

 

세부 측정 정보 

show profile for query 56;

 

Duration 0.00775600 이 어떻게 해서 나왔는지 세부 측정 정보로 나눠서 설명해준다. 즉, 더 상세한 단위로 얻을 수 있다. 

 

CPU 사용량 체크 

show profile cpu for query 56;

 

CPU 사용량 분석도 가능하다. 

 

---

 

인덱스 조회 생성 수정 삭제 및 쿼리 성능 비교하기 

 

MySQL에서 인덱스는 테이블을 빨리 조회하기 위한 것이다.

책의 목차로 많이 비유를 하는데 특정 컬럼에 인덱스를 지정해주면 테이블 조회 시 인덱스를 이용해 빠르게 조회할 수 있게된다. 

 

기본적으로 테이블을 생성하면 PK 컬럼에 인덱스가 지정되어진다. 

하지만 상황에 따라 다른 컬럼을 지정하여 인덱스를 활용해보는 것이 이번 포스팅의 목적이다. 

즉, 쿼리 성능이 어떻게 달라지는지 확인해보는 것!

 

하지만 인덱스를 남발해버리면 역효과가 날 수 있다. 

이 부분에 대해서는 추후 인덱스 자료구조부터 자세히 정리할 예정이니, 지금은 성능 차이만 비교해보자. 

 

인덱스 조회

SHOW INDEX FROM 테이블명;

 

인덱스 생성

1. CREATE INDEX 인덱스명 ON 테이블명(컬럼명)
2. ALTER TABLE 테이블명 ADD INDEX 인덱스명(컬럼명)
-- 둘 중 아무거나 해도 된다.

 

인덱스 삭제

ALTER TABLE 테이블명 DROP INDEX 인덱스명;

 

 

 

쿼리 성능 측정

 

show index from dongcode;

인덱스 조회를 하면 현재 dongcode 테이블에 인덱스는 하나밖에 존재하지 않는다. 이전 글에서 "select * from dongcode d where d.dongName = "초동""  쿼리문을 날려서 성능 측정을 하였다. 인덱스 컬럼으로 dongName은 지정되어 있지 않다. 

그러므로 dongName을 인덱스 컬럼으로 지정하기 전과 후로 성능 측정을 해보려고 한다. 

 

show profiles ;
-- 성능 측정

 

평균 0.00780000 정도 나온다.

 

-- 인덱스 생성 
create INDEX idx_test ON dongcode (dongName); 
-- 인덱스 보기 
show index from dongcode;

 

현재 dongcode 컬럼을 지정한 인덱스 하나를 추가해주었다. 

다시 한 번 "select * from dongcode d where d.dongName = "초동"" 쿼리문을 날린 후 성능 측정을 해보자. 

 

 

 

평균 0.0003200 정도 나오는데 이전 성능 측정과 비교해보면 확실히 빨라진것이 보인다. 추가적으로, EXPLAIN 키워드를 사용하면 실제 쿼리가 인덱스를 타는지 확인할 수 있다. 

 

 

explain select * from dongcode d where d.dongName = "초동";

인덱스 만들기 전

인덱스 만든 후

 

여기서 보면 Type가 ALL 이고 possible keys 가 NULL 이면 인덱스를 타지않는다는 뜻이다. 반대로 type가 ref 인 상태에서 possible keys 를 보면 해당 쿼리가 idx_test 라는 인덱스를 타고 있다는 것을 볼 수 있다.