MySQL Optimization with indexing

1. indexing?

우리가 책을 볼 때 제일 뒷 페이지에 ‘index’가 있는 것과, DB에서의 indexing은 기본적으로 똑같다.

MySQL은 storage engine이 MyISAM, InnoDB 등 여러 가지가 있다. 그리고,  각 engine에 따라 indexing 방법은 조금씩 다르게 동작하기 때문에 optimization을 위해서는 조금씩 다르게 접근해야 한다.

MySQL 5.5 이후 버전부터 default로 채택되어 널리 이용되고 있는 InnoDB 기준으로 그 index 구조와 performance를 살리기 위한 접근 방법에 대해 살펴보겠다.

2. InnoDB index 구조

InnoDB에서는 clustered index를 지원한다. clustered index는 primary key로 이해할 수 있다. clustered index는 B-Tree 의 leaf node에 index와 row를 같이 저장하는 방식이다. 그리고 node들은 clustered index로 정의한 column에 의해 정렬이 된다.

예를 들어, 다음 table은 에서 clustered index, 즉 primary key를 (first name, last name)으로 정의한 경우를 생각해보자.

id first name last name birthday
1 Akroyd Christian 1958-12-07
2 Akroyd Debbie 1990-03-18
..
10 Akroyd Kirsten 1978-1102
11 Allen Cuba 1960-01-01
12 Allen Kim 1930-07-12
..
20 Allen Meryl 1980-12-12
..
91 Barrymore Julia 2000-05-16
92 Basinger Vivien 1976-12-08
..
100 Basinger Viven 1979-01-24

위 테이블은 clustered index 방식을 이용하면 아래 그림과 같이 저장된다.

clustered_index_innodb

위의 구조를 보면 2가지 특징이 있다.

  1. First name –> last name 순으로 정렬되어 있다. 다시 말해, primary key에 정의된 column들이 leftmost 방식으로 정렬되어 있다.
  2. leaf node는 연결되어 있다. (B+ tree의 특징이다. 엄밀히 말하면 InnoDB의 구조는 B+ tree 방식)

3. Implications

InnoDB의 clustered index로 저장되는 tree 구조를 고려해볼 때, performance를 위해서는 다음과 같은 전략으로 접근하는 것이 유용할 것으로 본다.

1. Select가 많은 경우에는 clustered index를 이용한다.

위의 그림에서 보듯이 여러개의 row를 검색해서 retrieve하기에 clustered index를 이용하면 좋다. 이 때 주의할 점은, primary key 정의 시에 제일 빈번하게 검색하게 될 컬럼을 먼저 선언한다. 앞의 예에서 만약 first name 보다 last name으로 검색하거나 정렬해서 data를 가져오는 것이 빈번하다면, primary key를 (last name, first name) 순으로 정의하는 것이 performance에 좋을 것이다.

2. insert와 update이 비해 select가 적다면, clustered index 사용을 진지하게 다시 고민한다.

위 그림에서도 보듯이 B tree에서 node는 정렬된 상태로 유지되기 때문에, insert나 update에 따라 트리 구조가 많이 변경될 수 있고, 이는 DB 성능에 많은 영향을 줄 수 있다. 만약 insert나 update 1회에 대해 select를 보통 100번정도 한다면 고민 없이 clustered index를 쓰는 것이 맞겠지만, insert와 update에 비해 select가 크게 많지 않다면, 오히려 performance에 독이 될 수 있다.

이에 관해서 좀더 자세한 benchmark를 원한다면 아래 링크를 참고.
http://knielsen-hq.org/presentations/innodb-clustered-index/innodb-clustered-index.pdf

4. References

MySQL Mannual
http://dev.mysql.com/doc/refman/5.5/en/innodb-table-and-index.html

High Performance MySQL, Baron Schwartz, O’Reilly, 2008

SQL Antipatterns, Bill Karwin, Pragmatic Bookself, 2010

One thought on “MySQL Optimization with indexing

  1. Select 시에 Clustered Index 를 쓸수 있는 이유는 아마 압축을 하지 않기 때문일거예요. InnoDB 인덱스는 압축을 않하는걸루 알거든요. MyISAM은 row index 이고 압축저장을 해놔서 저장공간이 훨씬 적다능…

    InnoDB도 단점이 있는데, 총 레코드 수를 몰라요. ㅋㅋㅋ Select count(idx) from table 하면 게시물이 많을 경우 한참 걸린다능… MyISAM은 총 레코드 수를 항 상 알고 있어서 쿼리를 날리면 바로 응답해줘요..

    이게 InnoDB로 게시판 만들때 짜증나는 거드만요.. ㅎㅎㅎ

    근데 일하시는 곳에서 MySQL 쓰시나 봐여. ㅎㅎㅎ

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s