데이터베이스는 대부분의 소프트웨어 시스템에서 핵심적인 역할을 수행합니다. 그러나 이 데이터베이스를 효과적으로 활용하기 위해서는 SQL 쿼리를 잘 작성하는 것이 매우 중요하기에 MySQL 효율적인 SQL 작성 방법을 알아보도록 하겠습니다.
효율적으로 작성된 SQL 쿼리는 데이터베이스에서 빠르게 실행됩니다. 반대로, 비효율적인 쿼리는 성능 저하를 초래하고 이는 사용자 경험을 저하시키거나 심지어 전체 시스템의 성능에 영향을 줄 수도 있습니다.
명확하고 간결한 SQL은 다른 개발자들이 코드를 이해하고 수정하는데 도움이 됩니다. 복잡하거나 혼란스러운 쿼리는 버그를 발생시키기 쉽고 문제 해결도 어렵게 만들 수 있습니다.
또한, 올바르게 작성된 SQL은 원하는 결과를 정확하게 제고을 해주며 잘못된 로직 또는 구조가 있는 쿼리는 잘못된 결과를 반환할 수 있으며, 이로 인해 비즈니스 결정에 악영향을 줄 수 있습니다.
마지막으로 효율적인 SQL 작성은 대량의 데이터 처리에 매우 효과적이지만 효율적으로 작성되지 않은 SQL은 큰 규모의 데이터에서 성능 문제를 일으킬 가능성이 크기에 SQL 작성방법을 살펴보도록 하겠습니다.
MySQL 효율적인 SQL 작성 3가지 방법
- WHERE 조건 이해
- SQL 레벨에서의 접근법
- 스키마 레벨에서의 접근법
WHERE 조건 이해
- 묵시적 형변환
- 잘못 사용된 함수
- LIKE 검색
묵시적 형변환
묵시적 형변환이란 조건절의 데이터 타입이 다를 때 우선순위가 높은 타입으로 형이 내부적으로 변환되는 것을 말한다. 정수 타입이 문자열 타입보다 우선순위가 높다. 만약 문자열과 정수값을 비교하는 쿼리가 들어오면 두 개의 컬럼 중 우선순위가 낮은 문자열은 자연스럽게 정수 타입으로 형 변환되어 처리된다. 하지만 주의할 것은 때로는 예기치 않은 결과값이나올 수도 있다. 특히 문자열 사이에 보이지 않는 문자가 포함된 경우에는 문제를 찾기도 어렵다. 그러므로 묵시적 형변환 함정에 빠지지말고 반드시 조건절에는 컬럼 타입을 맞춰서 질의하는 것을 권한다.
잘못 사용된 함수
대부분의 DBMS에서는 함수 기능을 제공한다. 내장 함수뿐만 아니라 사용자 정의 함수도 제공한다. 함수는 복잡한 연산을 사람이 알아보기 쉽고 사용하기 편리하지만 잘못 쓰면 불필요한 시스템 부하를 야기한다. SQL을 논리적으로 작성하는 것도 중요하지만 DBMS가 이해하기 쉽게 SQL을 작성하는 것도 DB 튜닝에 중요하다.
SELECT userid, count(*) AS cnt
FROM user_access_log
WHERE DATE_FORMATI(reg_date, '%Y%m%d') = '2016-10-10'
AND DATE_FORMATI(reg_date, '%H') >= '10'
AND DATE_FORMATI(reg_date, '%H') < '21'
GROUP BY userid;
위와 같이 SQL 작성 하면 어떤 목적의 쿼리 인지를 쉽게 알아볼 수 있다. 그러나 DB 내부적으로 테이블 풀스캔을 수행할 수밖에 없다. 설령 적절한 인덱스 또는 테이블 파티셔닝이 적용되어 있을지라도 말이다. 옵티마이저는 컬럼의 분포도를 기준으로 데이터를 추출하는 가장 빠른 방법을 도출한다. 그런데 위 쿼리와 같이 reg_date 컬럼 검색 시 DATE_FORMAT 함수를 사용하면 옵티마이저는 reg_date와 연관된 데이터 분포도를 알 수 없게 된다. DATE_FORMAT 함수로 인해 변경될 결과값을 옵티마이저가 예상하지 못하기 때문이다. 물론 인덱스가 없는 경우에도 시스템적으로 부하가 있기는 마찬가지다. DATE_FORMAT 함수를 쓸데없이 많이 호출하기 때문이다.
LIKE 검색
데이터를 조회할 때 특정 단어가 들어 있는 데이터를 검색하기 위해 LIKE 검색을 사용한다. LIKE 검색은 상당히 편리하지만 대용량 테이블인 경우에는 위험하다. LIKE 검색은 ‘%’ 문자 위치에 따라 다르게 수행된다. [col LIKE ‘abc%’] 이면 abc로 시작하는 문자를 조회한다. [col LIKE ‘%abc%’]이면 abc를 포함한 데이터를 가져온다. 그리고 [col LIKE ‘abc’]는 abc로 끝나는 데이터를 가져온다. LIKE 검색 시 ‘%’ 위치에 따라서는 컬럼에 해당 인덱스가 있을지라도 의미가 없을 수도 있으니 SQL 작성 시 유의해야 한다.
LIKE ‘xxx%’ 테스트
[LIKE ‘xxx%’]는 인덱스가 있으면 해당 인덱스를 사용하여 쿼리가 빠르게 처리된다. 테스트 한 MySQL Explain 은 다음과 같다.
EXPLAIN
SELECT * FROM test WHERE s LIKE ‘1311%’;
id table Type Key Key_len Rows
------ ------- ------- ------ ---------- --------
1 test RANGE s 130 24
정상적으로 인덱스를 잘 활용하며 결과 또한 빠르게 나온다. 즉, LIKE로 데이터를 검색하더라도 뒷부분에 % 를 붙이면 인덱스를 적절하게 활용할 수 있다.
EXPLAIN
SELECT * FROM test WHERE s LIKE ‘1%’;
id table Type Key Key_len Rows
------ ------- ------- ------ ---------- --------
1 test ALL NULL NULL 131072
하지만 위의 결과는 앞과 동일한 쿼리지만 LIKE 조건을 ‘1311%’ 에서 ‘1%’로 변경했을 뿐인데, 테이블 풀스캔 변경되었다.
대부분의 DBMS에는 옵티마이저가 있다. 옵티마이저는 데이터 분포도를 따져서 내부적으로 SQL을 최적으로 처리한다. 앞의 예제는 1로 시작하는 데이터 비율이 전체적으로 20% 이상을 차지하므로, 인덱스를 읽고 다시 데이터 파일로 가는 것보다 처음부터 전체 데이터를 읽고 필요한 데이터를 선별하는 것이 더 빠르다고 옵티마이저가 판단했기 때문에 풀스캔을 한 것이다.
인덱스는 데이터가 위치한 곳을 지칭한다. 인덱스도 데이터고 지칭하는 데이터로 차아가려면 실 데이터에 대한 데이터를 다시 읽어야 하기 때문에 중복된 데이터 처리 비용보다는 테이블 풀스캔으로 접근하는 것이 훨씬 빠르다고 옵티마이저가 판단한 것이다. 다음은 1~9의 각 숫자로 시작되는 데이터의 비율이다.
SELECT left(s, 1) str, count(*) cnt
FROM test
GROUP BY left(s, 1);
Str Cnt
--- --------
1 27525
2 27460
3 27261
4 27652
5 10292
6 2692
7 2619
8 2839
9 2732
LIKE ‘%xxx%’ 테스트
인덱스는 순차적으로 비교되므로 당연히 테이블 풀스캔이 발생한다.
EXPLAIN
SELECT * FROM test WHERE s LIKE ‘%1311%’;
id table Type Key Key_len Rows
------ ------- ------- ------ ---------- --------
1 test ALL NULL NULL 131072
LIKE ‘%xxx’ 테스트
[LIKE %xxx%]의 결과와 마찬가지로 테이블 풀스캔이 발생한다.
EXPLAIN
SELECT * FROM test WHERE s LIKE ‘%1311’;
id table Type Key Key_len Rows
------ ------- ------- ------ ---------- --------
1 test ALL NULL NULL 131072
결론
LIKE 검색을 별생각 없이 사용하다 보면 DBMS 성능이 전체적으로 크게 저하된다. 그렇다고 이런 편리한 기능을 무조건 사용하지 않을 수는 없다. 다음과 같이 SQL 작성 팁 지켜도 LIKE 검색의 성능을 향상시킬 수 있다.
- LIKE 조건이 ‘검색어%’ 와 같이 검색어가 앞 단에 있다면 데이터 분포도를 따져서 수행한다.
- LIKE 조건이 ‘%검색어’와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적응 활용하여 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.
인덱스는 필요한 데이터를 지칭한다는 점에서 데이터에 접근하기 위한 효율적 요소인 것은 분명하다. 그러나 인덱스 또한 메모리를 차지하고, 디스크를 소모하며, CPU 연산이 필요한 데이터라는 것을 기억해야 한다. DB에서 처리하는 데이터 범위를 MySQL Explain 활용하여 최대한 줄이는 것이 성능 최적화의 가장 기본적인 요소이다.
SQL 레벨에서의 접근법
MySQL 효율적인 SQL 작성 방법으로 Join은 여러 테이블에서 하나의 결과셋를 가져올 수 있지만 무분별하게 조인을 사용하면 데이터가 누적됨에 따라 쿼리 성능이 저하된다.
데이터 연산을 최소한으로 유도
MySQL에서는 모든 처리를 단일 코어에서 Nested Loop Join 방식으로만 데이터를 처리한다. 병렬 처리라는 것은 없다. 그렇기 때문에 MySQL 입장에서는 CPU 코어 개수를 늘리는 Scale-Out보다는 오히려 단위처리량이 좋은 CPU로 Scale-Up을 하는 것이 훨씬 유리하다. Nested Loop Join이란 선행 테이블(A)의 조건 검색 결과 값 하나하나를 엑세스 하면서 연결할 테이블(B)에 대입하여 조인하는 방식이다. 상용 DBMS에서 흔히 제공하는 Hash Join과 Merge Join 같은 조인 알고리즘이 MySQL에는 구현되어 있지 않다. 그러므로 확인하여 Nested Loop Join 횟수를 최대한 줄이는 것이 쿼리 성능을 향상시키는 첫걸음이다.
불필요한 조인을 피하기
Foreign key는 테이블에서 다른 테이블의 Primary Key와 대응된다는 것을 의미한다. 따라서 Foreign Key가 정의된 테이블에는 반드시 다른 테이블과 연관된 데이터가 있어야 한다. 그러므로 Foreign key 존재 유무를 파악하여 불필요한 조인을 피하는 것이 유용하다.
Semi Join으로 인한 비효율 제거
MySQL 5.5 버전까지는 Semi Join (‘세미 조인’ 또는 ‘부분 조인’)의 성능이 최적화 되어 있지 않았다. Semi Join이란 Exists와 IN 같은 조인 안에 SELECT가 있는 경우이다. 아래와 같은 경우를 예를 들어보자.
SELECT *
FROM Country
WHERE
Continent = 'Europe' AND
Country.Code IN (SELECT City.country
FROM City
WHERE City.Population > 1*1000*1000);
서브 쿼리 실행 후 WHERE 조건을 수행하는 것이 아니라 매번 데이터를 Nested Loop Join 탐색하면서 서브 쿼리를 수행하기 때문에 불필요한 부하가 발생한다. Oracle에서는 IN 구문 안의 SELECT를 먼저 수행한 후 결과 값을 Hash 형식으로 만들어서 데이터를 처리한다고 예상할 것이다. 그러나 아쉽게도 MySQL에서는 위와 같이 Country 테이블의 Continent가 Europe인 조건을 먼저 검색하고, 그 결과를 IN 조건의 서브 쿼리를 일일이 반복 수행하여 최종 데이터를 가져온다.
Outer Join이 반드시 필요한지 파악
MySQL 효율적인 SQL 작성 방법으로 무분별하게 Outer Join (‘아우터 조인’ 또는 ‘외부 조인’)을 수행하면 성능이 상당히 떨어진다. 추가적인 데이터 조회를 위해 Outer Join을 사용할 때는 특히 주의하여 SQL 작성 방법을 파악하고 작성해야 한다.
<수정 전>
SELECT
M.MASTER_NO,
M.TITLE,
MI.PATH,
M.REGDATE,
CM.TYPE
FROM MAIN AS MAIN
INNER JOIN TAB01 AS CM
ON CM.MASTER_NO = M.MASTER_NO
LEFT OUTER JOIN TAB02 AS MI
ON M.MASTER_NO = MI.MASTER_NO
WHERE M.DEL_YN = 'N'
ORDER BY M.MASTER_NO DESC
LIMIT 10000, 10;
데이터를 10,000번째 위치부터 10건을 가져온다면 결과적으로 불필요한 10,000번의 Outer Join이 발생한다. 물론 데이터가 적을 경우에는 문제가 없지만 데이터가 누적되면 서버에 큰 영향을 미칠 수 있다.
<수정 후>
SELECT
M.MASTER_NO,
M.TITLE,
MI.PATH,
M.REGDATE,
CM.TYPE
FROM (
SELECT
M.MASTER_NO,
M.TITLE,
MI.PATH,
M.REGDATE,
CM.TYPE
FROM MAIN AS MAIN
INNER JOIN tab01 AS CM
ON CM.MASTER_NO = M.MASTER_NO
ORDER BY M.MASTER_NO DESC
LIMIT 10000, 10
) A
LEFT OUTER JOIN tab02 AS MI
ON A.MASTER_NO = MI.MASTER_NO;
서브쿼리 활용
MySQL 효율적인 SQL 작성 방법으로 서브 쿼리는 DB 내부적으로 Temporary Table을 생성하여 데이터를 처리하기 때문에 과용 하면 좋지 않다. 쿼리 안에 또 다른 쿼리를 넣어서 데이터를 질의할 수 있기 때문에 데이터를 조회하는 것이 상당히 편하게 변모하였다. 그러나 서브 쿼리가 편하다고 해서 무분별하게 사용한다면 서버 성능이 급격히 저하되는 사태에 이르는 원인이 될 수도 있다. 그러나 필요한 부분에 적극 사용하면 SQL 작성 및 성능 향상에 큰 도움이 된다.
스키마 레벨에서의 접근법
MySQL 효율적인 SQL 작성 방법으로 InnoDB에서 Primary Key 관련 영향 여부 및 불필요한 인덱스 삭제
Primary Key 직접적인 영향 여부
MySQL 효율적인 SQL 작성 방법으로 MySQL의 InnoDB에서는 Primary Key 순서로 데이터가 저장된다. Primary Key는 데이터에 접근하는 물리적인 주소로 사용된다고 봐도 된다. MS-SQL의 클러스터 인덱스 (Cluster Index), 오라클의 IOT (Index Organized Table) 라는 개념이 MySQL에서는 Primary Key로 구현된 것이다 (오라클의 클러스터 테이블과는 다른 개념). 즉, ‘인덱스 순서’로 데이터가 정렬되어 디스크에 저장된다. 문자열 컬럼이 많은 테이블에서 Primary Key를 무작위로 넣을 때와 순차적으로 넣을 때의 성능을 비교한 것이며 1회 테스트 시 데이터를 200만 건씩 넣은 예제이다.
<Primary Key 를 무작위로 테이블에 넣을 때와 순차적으로 테이블에 넣을 때의 성능 비교>
| 구분 | 수행 | 소요 시간 |
| 무작위로 Primary Key를 넣을 때 | 3회 | 41.81초 |
| 4회 | 4분 8초 | |
| 5회 | 24분 8초 | |
| 순차적으로 Primary를 넣을 때 | 3회 | 21.17초 |
| 4회 | 21.64초 | |
| 5회 | 22.75초 |
불필요한 인덱스 삭제
MySQL 효율적인 SQL 작성 방법으로 보통 인덱스가 많으면 DB 성능이 무조건 좋아진다고 생각한다. 인덱스가 있으면 데이터를 빨리 가져올 수 있고 관련 DISK I/O 혹은 메모리 연산 작업을 최소화해주기 때문일 것이다. 하지만 기억해야 할 것은 인덱스도 바로 데이터라는 것이다. 일반 데이터와 다르게 인덱스는 수시로 메모리 상에서 구조가 변화하는 동적인 데이터다. 때로는 불필요한 인덱스가 생성되어 있는 것도 볼 수 있는데, 이러한 인덱스는 아예 사용이 안 되는 경우도 다수이다.
CREATE TABLE `test_0_index` (
`i` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`j` INT(10) UNSIGNED NOT NULL,
`s` VARCHAR(64) NOT NULL,
`d` DATETIME NOT NULL,
PRIMARY KEY (`i`),
INDEX `idx_j` (`j`),
INDEX `idx_d` (`d`),
INDEX `cidx_jd` (`j`, `d`),
INDEX `cidx_dj` (`d`, `j`)
);
먼저 불필요한 인덱스 idx_j와 idx_d는 불필요한 인덱스이다. cidx_jd와 cidx_dj 인덱스에서 각 칼럼을 복합 인덱스 형식으로 구성하였기 때문에 j 혹은 d만으로 데이터를 조회하는 쿼리 요청이 와도 정상적으로 데이터를 빠르게 추출한다. 그렇다고 무조건 idx_j와 idx_d를 제거해서는 안 된다. 만약 d로 데이터를 검색한 결과를 i컬럼 (Primary Key) 순서(혹은 역순)로 가져오는 게 목적이라면, cidx_dj가 오히려 필요 없는 인덱스이다. idx_d로 검색한 결과는 Primary Key 순으로 나오지만 cidx_dj 인덱스에서 d로 검색한 결과는 j 순서로 나오기 때문이다. 따라서 인덱스를 정리하기 전에 인덱스의 사용 목적을 정확히 하며 모니터링 해야 한다.
MySQL 효율적인 SQL 작성 방법 외에도 MySQL 스토리지 엔진에 대한 정보는 여기를 참고하시면 되겠습니다. 지금까지 MySQL 효율적인 SQL 작성 3가지 방법 알아보았으며 위의 3가지 조건 (WHERE 조건 이해 / SQL 레벨에서의 접근법 / 스키마 레벨에서의 접근법) 에 유의하여 쿼리를 작성하면 효율적인 SQL 작성에 도움이 될 것으로 보여집니다.
- MySQL Explain 참고자료 : https://www.mysql.com