'Database/MySQL' 카테고리의 글 목록

Database/MySQL에 해당되는 글 1건

MySQL 최적화 20

MySQL 최적화

개요

MySQL, MSSQL, Oracle 등 여러 Database 를 써 보았지만, 대부분 레코드의 양이 많치 않은 경우 였습니다. 그래서 Schema 를 신경써서 개발했고, query 는 신경써서 만들기는 했지만 실질적으로 프로파일링 해 가면서 한적은 없었네요.

그런데 이번에는 조금 데이터가 많은 사이트를 개발하게 되어, 성능을 체크하면서 개발중입니다. 개발하다보니 table 속성에 따른 생각치 못한 부분들이 있어서, 포스팅 하고자 합니다. :)

웹 개발을 시작한게 97년이니까... 꽤 오랫동안 해와서 자신이 있었는데, 조금 충격적이네요 ㅠ.ㅠ

포스팅은 생각해보니 최적화라고 하기보다는, 놓치고 있던 부분들을 되짚는다는 표현이 맞을듯 합니다.

시작에 앞서, 해당 포스팅은 MySQL 에 대한 이야기입니다. 타입은 InnoDB 고요.

Index 에 대해서

검색을 빠르게 하기 위해서는 index 를 쓰라는 말을 많이 듣고, 그렇게 하고 있습니다. 그런데 이 index 가 정말 내가 원하는대로 잘 동작하고 있는걸까요? 아래 테이블을 봐 주세요.

[Table #1]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16),

INDEX(id, age)

);

index 가 id 와 age 에 걸려있는걸 주의해서 봐 주세요.

더미 데이터를 넣어 봅시다.

insert into crystalcube values(default, 10, 'john', 'my friend', '13');

insert into crystalcube values(default, 30, 'Emily', 'my girl friend', '12');

insert into crystalcube values(default, 15, 'Alexis', 'my daughter, my family', '13');

insert into crystalcube values(default, 15, 'Michael', 'my son, my family', '12');

insert into crystalcube values(default, 27, 'Ashley', 'my wife, my family', '1');

보시는것처럼 테이블과 레코드가 있습니다. 필드명은 테스트를 위한 것이기에 큰 의미는 없으니 그냥 봐 주세요 ^^;

select 결과 위와 같이 테이블/레코드가 생성되었습니다.

이제 Query 를 테스트 해 봅시다.

SELECT * FROM crystalcube WHERE id = 3;

이 경우 몇 개의 row 를 탐색할까요?

네, 예상대로 1번 만에 검색이 완료됩니다.

위 graph 는 제가 쓰고있는 MySQL Workbench 에서 지원하는 기능이고, 직접 DB 에서 보시려면 explain 을 하시면 됩니다.

그냥 그래프로 보기좋게 나오는 차이 밖에는 없습니다. :)

EXPLAIN SELECT * FROM crystalcube WHERE id = 3;

Quiz # 1

그렇다면, age 에 대해서 검색하면 어떻게 될까요?

SELECT * FROM crystalcube WHERE age = 10;

네, 쉬운 문제였습니다. 당연히 index 를 타지않고 Full Table Scan 하게 됩니다.

테이블을 생성할때, INDEX(id, age) 라고 했는데 이것은 id 를 첫번째 index로, 그리고 두번째 index 로 age 컬럼을 쓰겠다는 이야깁니다. 의도한것처럼 id 도 index 로 하고, age 도 index 로 하려면 따로따로 선언해 주어야 합니다. 아래처럼 말이죠.

[Table #2]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16),

INDEX(id),

INDEX(age)

);

Quiz # 2

그렇다면, 아래 query 는 index 를 탈까요? 아니라면 어떻게 table 을 만들어야 할까요?

SELECT * FROM crystalcube WHERE age = 15 AND extra = '13'

Table #2 인 경우에는 index 를 타지 않고, Full Table Scan 이 됩니다. 위 경우를 처리하려면, INDEX(age, extra) 를 추가시켜 주어야 합니다.

아래 테이블을 만들어 보도록 하죠.

[Table #3]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16),

INDEX(extra)

);

위 테이블에서 extra 컬럼은 VARCHAR 이라는 점에 주목해 주세요.

Quiz # 3

아래 query 의 경우, 탐색 결과는 어떻게 될까요?

extra 가 문자열인데도 불구하고, quote(') 로 묶지 않았다는 점을 잊지 마시기 바랍니다.

SELECT * FROM crystalcube WHERE extra = 1;

에러가 날까요? 아닙니다. MySQL 에서 내부적으로 변환해서 제대로 검색해 줍니다.

아래처럼 잘 결과가 나옵니다.

Quiz # 4

그렇다면 아래 두 쿼리의 효율은 어떻게 될까요?

하나는 위와 마찬가지로 quote 로 감싸지 않은 경우고, 하나는 감싼 경우입니다. 나머진 동일합니다.

SELECT * FROM crystalcube WHERE extra = 1;

SELECT * FROM crystalcube WHERE extra = '1';

결과는 완전 다릅니다.

처음처럼 quote 로 감싸지 않은 경우, Full Table Scan 을 하게 됩니다. 하지만 아래처럼 quote 로 감싸주면 index 를 타게 됩니다.

좌측은 quote 로 감싸지 않은 경우, 우측은 감싼 경우 입니다.

Quiz # 5

다음 테이블을 봅시다.

[Table #4]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16),

INDEX(age),

INDEX(extra)

);

아래 쿼리의 경우 효율이 어떻게 될까요?

SELECT * FROM crystalcube WHERE age = 15 AND extra = 12;

1번만에 검색이 될까요? 틀렸습니다. 놓치셨군요. 앞서 말씀드린데로 varchar 의 경우 quote 로 묶어주지 않으면 index 를 사용하지 않게 됩니다. 그러므로 아래처럼 됩니다.

그림을 보시면 age 만 사용된 것을 보실 수 있습니다. 그럼 quote 를 써서 확인해 보죠

SELECT * FROM crystalcube WHERE age = 15 AND extra = '12';

이번에는 제대로 age 와 extra 가 사용되었네요.

하지만 이렇게 index 두개가 모두 사용되는 경우는 보장할수 없는것 같습니다.

위 경우에서, 레코드를 늘려보면, 아래처럼 explain 결과가 변경됩니다.

보시는것처럼 age 하나만 index 로 활용되고 있네요. 아마두 경우에 따라 내부 알고리즘에 의해 효율적인 녀석이 index 로 사용되는듯 합니다. 사실 위 경우라면 age 가 아니라 extra 를 index 로 써도 되거든요. 하지만 extra 를 index 로 쓰면 아래처럼 4개의 row 가 아니라, 8 개의 row 가 access 됩니다.

SELECT * FROM crystalcube FORCE INDEX(extra) WHERE age = 15 and extra = '12';

Quiz # 6

자, 아래 쿼리는 어떻게 될까요?

SELECT * FROM crystalcube WHERE age = 15 OR extra = '13';

AND 가 아닌 OR 인 경우입니다.

정답은 Full Table Scan 을 하게 됩니다. index 를 타지 않는다는 것입니다.

분명 age 와 extra 모두 index 인데 동작하지 않네요. 이유는 간단합니다. age 로만 검색한 경우 age 로 index 화된 곳에서 검색하면 됩니다. extra 로만 검색할 경우에는 extra 로 index 화된 곳에서 검색하면 되고요. 하지만 age 와 extra 둘다 동시에 index 화 시킬수 없지요. 그래서 Full Table Scan 을 하게 됩니다.

그렇다면 어떻게 이 문제를 해결해야 할까요? 제가 지금까지 찾아본 바로는 Union 을 사용하는게 그나마 효율적인 것 같고, 정석인듯 합니다. 아래처럼 말입니다.

SELECT * FROM crystalcube WHERE age = 15

UNION

SELECT * FROM crystalcube WHERE extra = '13';

이 경우 아래처럼 explain 됩니다.

최종 결과물에 대한 레코드는 3개인데, 검색은 4개의 row 이네요.

UNION 이 기본적으로 DISTINCT 시켜준다는 것을 생각해보면 쉽게 이해가 가실듯 합니다. UNION ALL 이 아니라는 거죠. 결과는 4개고 중복된게 1개 있어서, 레코드는 실제로 3개가 리턴됩니다.

paging 에 대해서

페이징을 여러분은 어떻게 하시나요? 가장 기본적인 방법으로는 limit 와 offset 을 사용하는 방법이 있습니다. 사실 대부분 심플하게 그렇게 구현하는 경우가 많지요.

페이징 테스트를 위해서 로우를 좀 더 추가해 보도록 하지요.

[Table #5]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16),

INDEX(age),

INDEX(extra)

);

insert into crystalcube values(default, 10, 'john', 'my friend', '13');

insert into crystalcube values(default, 30, 'Emily', 'my girl friend', '12');

insert into crystalcube values(default, 15, 'Alexis', 'my daughter, my family', '13');

insert into crystalcube values(default, 15, 'Michael', 'my son, my family', '12');

insert into crystalcube values(default, 27, 'Ashley', 'my wife, my family', '1');

insert into crystalcube values(default, 1, 'david', 'my friend', '13');

insert into crystalcube values(default, 2, 'Alli', 'my friend', '12');

insert into crystalcube values(default, 3, 'cony', 'my friend', '13');

insert into crystalcube values(default, 4, 'brown', 'my friend', '12');

insert into crystalcube values(default, 5, 'Jack', 'my friend', '1');

각 페이지마다 레코드가 3개씩 보여진다고 가정해 봅시다. 레코드 갯수가 총 10개니까, 4페이지가 나오겠네요. :)

두번째 페이지를 가져와 봅시다. 가장 기본적인 query 입니다.

SELECT * FROM crystalcube LIMIT 3, 3;

두번째 페이지라는건, 앞에 페이지의 레코드가 3개(즉, offset 이 3)이고, 그 뒤로 3개를 가져오라는 이야기죠. 그러므로 위처럼 query 가 됩니다. 만약 세번째 페이지라면, LIMIT 6, 3 이 되겠지요.

Quiz # 7

위 경우 explain 해 보면 어떤 결과가 나올까요??

안타깝지만 Full Table Scan 이 되어 버립니다.

왜냐면 기준이 없으니까요. 우리가 select * 을 했을때 나오는 값은 모든 데이터를 가져온 경우고, limit 를 사용할때는 기준을 주어야 합니다. 어떤 기준으로 정렬을 했을때, offset 이 몇이며, limit 가 몇인지를 알아야 겠지요.

Quiz # 8

이번에는 그렇다면 primary key 를 가지고 order 를 해 보도록 합시다.

과연 원하는대로 빠르게 Query 가 동작할까요?

SELECT * FROM crystalcube ORDER BY id LIMIT 3, 3;

결과는 만족스럽지 않네요

처음보다는 좋아졌지만, 6개의 rows 가 뽑아집니다. 이 6개라는것은 offset + limit 의 갯수입니다. LIMIT 2, 3 인 경우에는 5 rows 가 됩니다.

이 이야기는 무슨 뜻일까요? 첫 페이지는 엄청 빠르게 탐색이 됩니다. 하지만 페이지가 뒤로 가면 갈수록, 점점 느려진다는 이야깁니다. 1000 페이지에서 100 개를 가져온다면, 탐색은 1100 개가 될것입니다. 첫페이지는 100 일텐데 말이죠.

해결방법으로 많은 곳에서 아래 방식을 사용하라고 권장합니다.

SELECT * FROM crystalcube WHERE id >3 ORDER BY id LIMIT 3;

당연히 id 는 index 이거나 primary 이어야 합니다. 2 페이지는 id 가 4부터 시작할테니, 위 처럼 쓰라는 것이죠.

하지만 여기에 문제가 있습니다. 1페이지부터 순차적으로 다음 페이지로 넘어갈때는 효율적입니다. 네, 문제가 없습니다.

만약 id 값이 2 인 레코드가 삭제되어, 1페이지의 id 가 1, 3, 4 인 경우가 발생할 수 있습니다. 중간에 게시물이 삭제된 경우 말입니다. 이때는 아래처럼 Query 가 되어야 합니다.

결방법으로 많은 곳에서 아래 방식을 사용하라고 권장합니다.

SELECT * FROM crystalcube WHERE id >4 ORDER BY id LIMIT 3;

즉, 순차적인 탐색이 아닌 Page Random Access 인 경우, 방법이 없습니다. =_=;

이 부분에 대해서는 추가적으로 방법을 연구해 봐야 겠네요.

LIMIT 에 대해서

단순한 LIMIT 에 대해서 살펴보도록 하겠습니다.

[Table #6]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16),

INDEX(id),

INDEX(age)

);

Quiz # 9

위 경우에 LIMIT 1 을 통해서 한개만 뽑아오는 경우 어떻게 될까요?

SELECT * from crystalcube LIMIT 1;

네, 안타깝게도 Full Table Scan 이 발생합니다.

제 생각에는 LIMIT 1 을 하기 위해서는 어떤 기준이 필요한데 이 값이 없어서 발생되는 문제로 생각됩니다.

그렇다면 index 인 id 를 가지고 정렬해서 LIMIT 1 을 해 보겠습니다.

SELECT * from crystalcube ORDER BY id LIMIT 1 ;

보시는것 처럼 1 개의 row 를 바로 가지고 왔습니다.

Quiz # 10

테이블을 보면, id 뿐만 아니라, age 역시도 index 입니다.

age 를 내림차순으로 정렬해서 첫번째 값을 가져와 보겠습니다. 어떻게 될까요?

SELECT * from crystalcube ORDER BY age DESC LIMIT 1 ;

결과는 Full Table Scan 이 됩니다.

왜 그럴까요? 사실 저도 모르겠습니다 =_= MySQL 공식 문서에 보면, 해결 방법은 명시적으로 index 를 정해주면 된다고 합니다.

SELECT * from crystalcube FORCE INDEX(age) ORDER BY age DESC LIMIT 1 ;

이렇게 하면 아래처럼 됩니다 :)

FULLTEXT 에 대해서

자, 이제 검색 문제입니다.

대부분 사이트에 보면, 제목이나 사용자 이름으로 검색하는 기능을 많이 지원합니다.

[Table #7]

CREATE TABLE crystalcube

(

id INT(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

age INT(11) NOT NULL DEFAULT 0,

name VARCHAR(32) NOT NULL,

comment VARCHAR(64),

extra VARCHAR(16)