MySQL 성능 죽이는 잘못된 쿼리 습관

Posted on 2012-05-11 | by gywndi | 4 Comments

Overview

안정적인 서비스 유지를 위해서는 쿼리 작성이 상당히 중요합니다. 잘못된 쿼리 하나가 전체적인 퍼포먼스를 크게 저해하기도 하고 최악의 경우 장애 상황까지 치닫기 때문이죠

단일 코어에서 Nested Loop Join으로 데이터를 처리하는 MySQL 특성 상 쿼리 구문에 큰 영향을 받습니다. (반드시 알아야할 MySQL 특징 세 가지 참고)

그래서 오늘은 쿼리 작성 시 기피해야 하는 사항 세 가지정도 골라봅니다.

Case 1

SELECT @RNUM:=@RNUM+1 AS RNUM, ROW.*
FROM (SELECT @RNUM:=0) R,
(
    SELECT
        M.MASTER_NO,
        M.TITLE,
        MI.PATH,
        M.REGDATE,
        CM.TYPE
    FROM MAIN AS M
    LEFT OUTER JOIN TAB01 AS MI
        ON M.MASTER_NO = MI.MASTER_NO
    INNER JOIN TAB02      AS CM
        ON M.MASTER_NO = CM.MASTER_NO
    WHERE M.DEL_YN = 'N'
    ORDER BY M.MASTER_NO DESC
) ROW
LIMIT 10000, 10

SQL Plan Case1-1

오라클 쿼리에 익숙하신 분들이 흔히 하는 실수입니다.

오라클 rownum 효과를 내기 위해 (SELECT @RNUM:=0) 로 번호를 붙이다 보니 결과적으로 필요없는 데이터를 스캔합니다. Nest Loop Join으로 데이터를 처리하기 때문에 퍼포먼스가 상당히 떨어집니다.

Row 번호는 어플리케이션 서버에서 생성하고, 다음과 같이 쿼리를 작성하는 것이 좋습니다.

SELECT
    M.MASTER_NO,
    M.TITLE,
    MI.PATH,
    M.REGDATE,
    CM.TYPE
FROM MAIN AS M
LEFT OUTER JOIN TAB01 AS MI ON M.MASTER_NO = MI.MASTER_NO
INNER JOIN TAB02      AS CM ON M.MASTER_NO = CM.MASTER_NO
WHERE M.DEL_YN = 'N'
ORDER BY M.MASTER_NO DESC
LIMIT 10000, 10

SQL Plan Case1-2

변환 전/후 쿼리 프로파일링을 해보면 다음과 같습니다. 변환 후에 필요없는 데이터 스캔에 소요되던 Sending Data가 사라지고, 단순하게 처리됩니다.

SQL Profile Case1

Case 2

Where 조건 Left Value에 함수 적용하여 결과적으로 Full Scan이 발생하는 경우입니다. 서비스 구현 단계에서는 쉽고 직관적으로 보일지는 몰라도, DB 내부 데이터 처리에서 엄청난 자원을 소모합니다.

이런 습관은 DBMS 상관없이 기피해야 합니다.

SELECT *
FROM VIEW_MASTER_LOG_GROUP TAB01
WHERE DATE_FORMAT(ST_LAST_DATE, '%Y-%m-%d') LIKE DATE_FORMAT(NOW(), '%Y-%m-%d');

SQL Plan Case2-1

Where 조건 날짜 검색 로직을 살펴보면 결과적으로 오늘 0시 이후 데이터를 가져오는 구문입니다. 그렇다면 다음과 같이 변환해 봅시다.

인덱스를 타게 Left Value에서 불필요한 Function을 제거하고, Between으로 0시 이후 데이터를 가져옵니다.

SELECT *
FROM VIEW_MASTER_LOG_GROUP TAB01
WHERE ST_LAST_DATE BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d') AND NOW();

SQL Plan Case2-2

Full Scan이 아닌 Range Scan이며 정상적으로 인덱스를 탑니다.

Case 3

데이터 추가 조회를 위한 Outer Join 사용 시 주의할 점입니다. 바로 위 1차 변환된 쿼리를 기준으로 말씀 드리겠습니다.

하단 쿼리는 Outer Join이 조건 검색에 영향을 미치지 않고 추가 정보 조회만을 위한 역할로 사용될 때 입니다.

SELECT
    M.MASTER_NO,
    M.TITLE,
    MI.PATH,
    M.REGDATE,
    CM.TYPE
FROM MAIN AS M
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;

SQL Plan Case3-1

데이터를 10,000번째 위치부터 10 건을 가져온다면 결과적으로 불필요한 10000번 Outer Join이 발생합니다. 쿼리 성능이 상당이 안좋습니다.

물론 데이터가 적을 경우에는 큰 문제가 없지만, 데이터가 누적됨에 따라 서버에 큰 영향을 미칠 수 있습니다. 아래와 같이 수정을 해보죠.

SELECT
    A.MASTER_NO,
    A.TITLE,
    MI.PATH,
    A.REGDATE,
    A.TYPE
FROM(
    SELECT
        M.MASTER_NO,
        M.TITLE,
        M.REGDATE,
        CM.TYPE
    FROM MAIN AS M
    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;

SQL Plan Case3-2

SQL Plan 정보는 더 안좋은 것처럼 보이지만, SQL을 프로파일링 해보면 다음과 같이 좋은 성능을 확인할 수 있습니다.

SQL Profile Case3

변환 후 프로파일은 더욱 길어지기는 했지만, Outer Join을 위한 Sending Data 시간만큼 단축되었습니다.

Conclusion

3가지 간단한 사례이기는 하지만, SQL 튜닝 시 확인을 해보면 종종 걸리는 문제들입니다. 쿼리 특성에 따라 성능이 좌우되는 만큼 SQL도 서비스 로직을 정확히 파악하여 작성한다면 서버 자원을 효율적으로 배분할 수 있겠죠.

잊지 마세요. MySQL에서는 단일 코어에서 Nested Loop Join 방식으로 데이터를 처리한다는 사실을..

재미있는 사례로 다음에 인사 드리겠습니다. ^^

MySQL 사용 시 주의해야 할 몇 가지

Posted on 2012-05-04 | by gywndi | 1 Comment

안녕하세요. 오늘은 MySQL을 사용할 때 지켜야할 사항 몇 가지 정리합니다.

나름 혼자서 정리를 해 본 것들인데, MySQL로 서비스를 준비 중이라면 한 번쯤은 고려를 해봤으면 하는 내용입니다.^^

테이블 설계 시 유의 사항

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

로그 성 테이블에도 기본적으로 PK 생성을 원칙으로 함
InnoDB에서 PK는 인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

2. 테이블 Primary Key는 auto_increment를 사용한다.

InnoDB에서는 기본 키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능
InnoDB의 PK는 절대 갱신되지 않도록 유지
(갱신 시 갱신된 행 이후 데이터를 하나씩 새 위치로 옮겨야 함)

3. 데이터 타입은 최대한 작게 설계한다.

시간정보는 MySQL데이터 타입 date/datetime/timestamp 활용
IP는 INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용
정수 타입으로 저장 가능한 문자열 패턴은 최대한 정수 타입으로 저장

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

NULL을 유지를 위한 추가 비용 발생
(NULL 허용 칼럼을 인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

데이터 및 인덱스 파일이 커질수록 성능이 저하되므로Partitioning 유도
PK 존재 시 PK 내부에 반드시 Partitioning 조건이 포함되어야 함

인덱스 설계 시 유의 사항

1. 인덱스 개수를 최소화 한다.

현재 인덱스로 Range Scan이 가능한지 여부를 사전에 체크
인덱스도 서버 자원을 소모하는 자료구조이므로 성능에 영향을 줌

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

인덱스 칼럼 데이터의 중복이 줄어들수록 인덱스는 최대의 효과를 가짐
하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함
```
SELECT count(distinct INDEX_COLUMN)/count(*)
FROM TABLE;
```

3. 커버링 인덱스(Covering Index)를 활용한다.

쿼리 조건이 인덱스 안에 포함된 경우 인덱스에서만 연산 유도
인덱스는 일반적으로 행 전체보다 작으므로 불필요한 Disk I/O 회피 가능
“MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!” 편 참고

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

InnoDB에서 데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐
MyISAM은 PK와 일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용
(MyISAM 엔진에서 ORDER BY 시 DESC는 가급적 지양)

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

긴 문자열 경우 Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용
```
CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))
```
Prifix Size는 앞 글자 분포도에 따라 적절하게 설정
(하단 결과가 1에 가까울 수록 최적의 성능 유지, 0.9이상 권고)
```
SELECT count(distinct LEFT(INDEX_COLUMN,3))/count(*)
FROM TABLE;
```

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

URL/Email같이 문자 길이기 긴 경우 유용
INSERT/UPDATE 발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱

CRC32 결과값을 저장할 칼럼 추가 및 인덱스 생성

alter table user_tbl add email_crc int unsigned not null;
create index idx01_email_crc on user_tbl (email_crc);

Insert Trigger 생성

create trigger trg_user_tbl_insert
before insert on user_tbl
for each row
begin
    set new.email_crc = crc32(lower(trim(new.email)));
end$

Update Trigger 생성

create trigger trg_user_tbl_update
before update on user_tbl
for each row
begin
    if old.email <> new.email then
        set new.email_crc = crc32(lower(trim(new.email)));
    end if;
end$

검색 쿼리
```
select *
from user_tbl
where email_crc = crc32(lower(trim('mail@domain.com')))
and email= 'mail@domain.com'
```
CRC32 결과가 중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

7. 중복 인덱스 생성 회피

MySQL은 동일한 인덱스를 중복 생성해도 에러를 발생하지 않음
Primary Key로 구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의

MySQL DB 데이터 이관 자동화 구현하기

Posted on 2012-04-27 | by gywndi | 1 Comment

Overview

DB를 운영하다 보면, 한 개의 MySQL 인스턴스에 여러 개의 데이터베이스를 모아서 보관하는 경우가 있습니다. 그러면 가끔 DB명이 충돌나는 경우도 발생하죠. 오늘은 Dump/Rename/Import 등 모든 프로세스를 자동화할 수 있는 방안을 제시해 봅니다.

요구사항

무조건 자동으로 동작해야 하고, 기억력이 나쁜 제가 나중에 사용하기 쉽게 재사용성도 좋아야한다는 것입니다. 그리고 사용 방법을 잊어도 쉽게 상기할 수 있는 방안도 있어야겠죠.ㅋ (제가 정한 요구사항입니다. ㅋ)

모든 프로세스는 자동화되어야 한다.
스크립트 수정 없이 재사용이 가능해야 한다.
사용 매뉴얼이 있어야 한다.

자동화 구현

프로세스 순서는 다음과 같고 2단계부터는 파이프( “|” )로 한번에 처리합니다.

인자 수 체크
로컬 데이터베이스 Drop & Create (Rename)
원격 데이터 Dump
Dump 파일에서 DB명 Rename
로컬 데이터 베이스로 Import

SHELL 스크립트

작성한 SHELL 스크립트 원본이고, 이관 작업은 데이터베이스 레벨로 이루어집니다. 테이블 레벨로의 확장은 하단 스크립트를 약간만 변경하면 될 것 같네요. (따로 구현은 안했습니다. ㅎ)

참고로 예전 포스팅 “리눅스에 MySQL 설치하기(CentOS 5.6)“대로 서버를 설치를 했으면, .bash_profile 에 mysql root 패스워드가 명시되어 있을 것입니다. 아래 스크립트는 .bash_profile 이 필요합니다.

#!/bin/sh

if [ $# -ne 5 ]; then
echo "Usage: ${0} <REMOTE_HOST> <REMOTE_DB> <REMOTE_DB_ID> <REMOTE_DB_PW> <LOCAL_TARGET_DB>"
echo "<Example>"
echo "REMOTE_HOST : 192.168.100.10"
echo "REMOTE_DB : snsdb"
echo "REMOTE_DB_ID : sns"
echo "REMOTE_DB_PW : sns12#"
echo "LOCAL_TARGET_DB : kth_snsdb"
echo "==> ${0} targetdb01 snsdb sns sns12# kth_snsdb"
exit 1
fi

## Exec profile for mysql user
. ~/.bash_profile

## Declare Valiables
REMOTE_HOST=${1}
REMOTE_DB=${2}
REMOTE_DB_ID=${3}
REMOTE_DB_PW=${4}
LOCAL_TARGET_DB=${5}

echo ">> Start Migration $db database"

echo ">> Drop and Create Database at Local Host"
mysql -uroot -p${ADMIN_PWD} -e"DROP DATABASE IF EXISTS ${LOCAL_TARGET_DB};CREATE DATABASE ${LOCAL_TARGET_DB};"

echo ">> Dump, Rename DB name, Insert Data"
mysqldump -u${REMOTE_DB_ID} -p${REMOTE_DB_PW} --host=${REMOTE_HOST} --single-transaction --no-create-db --databases ${REMOTE_DB} | sed -r 's/^USE `(.*)`;$/USE `'${LOCAL_TARGET_DB}'`/g' | mysql -uroot -p${ADMIN_PWD} ${LOCAL_TARGET_DB}

echo ">> Finish Migration $db database"

실제 사용은 다음과 같습니다.

$ ./mig_service_data 192.168.10.111 dbname dbuser dbpass dbname_renamed
>> Start Migration database
>> Drop and Create Database at Local Host
>> Dump, Rename DB name, Insert Data
>> Finish Migration database

모를 때는 아래와 같이 Script만 실행하면 매뉴얼이 나와요~

$ ./mig_service_data 
Usage: ./mig_service_data <REMOTE_HOST> <REMOTE_DB> <REMOTE_DB_ID> <REMOTE_DB_PW> <LOCAL_TARGET_DB>
<Example>
REMOTE_HOST : 192.168.100.10
REMOTE_DB : snsdb
REMOTE_DB_ID : sns
REMOTE_DB_PW : sns12#
LOCAL_TARGET_DB : kth_snsdb
==> ./mig_service_data targetdb01 snsdb sns sns12# kth_snsdb

Conclusion

위 프로세스는 Dump파일을 직접 열고, 데이터베이스 명을 원하는 이름으로 변경하여 진행해도 상관없습니다. 그러나 작은 운영이라도 최대한 자동화하여 운영 이슈를 최소화 하는 것을 저는 지향합니다. 설혹, 구현한 스크립트가 단 한번만 사용될 지언정^^;;