MySQL performance Xhark 김재홍

MySQL이란? MySQL is a database management system. 어떻게? => 저장한다. MySQL은 database management system으로, database를 관리합니다. 자료를 처리하여 저장하거나 읽어오는 역할을 수행합니다. 이 세계에는 수 많은 database management system들이 있으며, MySQL은 수 많은 Open source database management system들 중에서 가장 유명합니다.

왜 MySQL performance를? 컴퓨터가 동작하는 데에 시간이 많이 드는 부분? 1. 데이터처리(프로그램 동작) 2. 입력과 출력 3. 네트워크 4. etc… Database!

MySQL이란? 어떻게? MyISAM InnoDB Arara에서는 InnoDB를 사용… 어떻게 데이터를 관리하는가? 그것은 조금씩 차이를 보이는데, 크게 MySQL에는 두가지 방식이 있습니다. MyISAM / InnoDB

MySQL Database의 구조 여러 Table들… MySQL의 Database는 여러 Table들로 이루어져 있습니다.

MySQL Table의 구조 Column, Row, Index…etc Index! Index = PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT Why use index? MySQL의 Table의 구조를 살펴봅시다. Table은 Column과 Row와 Index등으로 이루어져 있습니다. 여기서 우리가 중요하게 여겨 보아야 할 것은 Index입니다.

MySQL에서 Index의 역할 Index는 검색을 위해 존재한다! => 모든 원소를 순서대로 보며 확인하는 수밖에…

Index의 종류 하나의 Column이 Index가 될 수도 있지만, MySQL에서는 여러 개의 Column이 Index가 될 수도 있다. 즉… Multiple-Column-Index가 가능하다는 이야기! For example: CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );

For Example Table “Users” id(PrimaryKey. auto_increment), username(UniqueKey), email(UniqueKey) Table “Users” Columns : id/username/password/nickname/email… Index: 1. SELECT * from Users where username=“NAME” 2. SELECT * from Users where nickname=“NICK” Username은 index에 있으니 B-tree에서 검색해서 저 조건에 맞는 Row를 모두 몹도록하자! Good! 으으… Nickname은 index에 없으니 모든걸 하나하나 비교해 볼 수 밖에…

해결방법? 방금과 같은 문제의 경우, nickname을 Key값으로 등록하면 된다.

For Example2 Table “Article_vote_status” id(PrimaryKey. auto_increment), user_id(Key), board_id(Key), article_id(Key) Table “Article_vote_status” Columns : id/user_id/board_id/article_id… Index: SELECT * from article_vote_status where user_id=UID and board_id=BID and article_id=AID user_id가 UID인 row들과, board_id가 BID인 row들과, article_id가 AID인 row들을 찾는다! 세개의 세트 중 개수가 가장 적은걸 선택해서, 조건에 맞는 것이 있나 하나하나 보도록 하자구!

해결방법? Multiple-Column-Index를 사용하자! (user_id, board_id, article_id)를 Key값으로 잡으면, 바로 원하는 것을 찾아낼 수 있다!

중요한 부분이었놔? Example1과 Example2에서 나온 부분은 소스에 숨겨져 있으니 한번 찾아보도록 하자. 사용 빈도가 적기 때문에 Time-critic한 부분은 아니다.

대체 어떻게 되어있길래? Database를 구성하는 방법에는 여러 가지가 있으나, 기본적으로 Tree구조를 이용한다. DS수업을 들은 사람이라면 B-tree를 기억하라!

대체 어떻게 되어있길래? Index를 이용해 Tree에서 검색하고, (log time)

두 가지 storage engine 1. MyISAM 2. InnoDB 이지만… 구조상의 큰 차이는 없다.

MyISAM Sun Microsystems GNU General Public License MyISAM은 Sun Mircosystems에서 만들었고, GPL입니다.

MyISAM InnoDB보다 빠르다. … 고는 하지만, 큰 차이는 없고 InnoDB가 더 빠른 경우도 존재한다. 저용량이다. BUT, 안정적이지 못하다.

MyISAM MyISAM방식은 transaction을 지원하지 않는다. =>에러가 나면 망한다…

MyISAM 어떤 구조로 되어있길래??? R-trees 4.1 (for the MyISAM storage engine) (R:rectangle)

Simple example of an R-tree for 2D rectangles

Visualization of an R*-tree for 3D cubes using ELKI

Similar to B-trees (by wiki)

MyISAM 추가되는 순서대로 Data를 저장한다. Data를 저장하는 file(.MYD)과 Index를 저장하는 file(.MYI)이 따로있다. 즉, 데이터를 따로 저장하고 Index로만 Tree를 구성한다. Index가 여러 개라면? => 여러 개의 Tree를 갖는다.

MyISAM Performance 삽입과정(INSERT) Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) Inserting indexes: (1 × number of indexes) * log N Closing: (1)

MyISAM Performance 삭제과정(DELETE) The time required to delete individual rows is exactly proportional to the number of indexes

MyISAM Performance 갱신과정(UPDATE) SELECT query with the additional overhead of a write. -> SELECT로 찾아서 바꾸고, Index로 이루어진 Tree들을 갱신해주는 것.

MyISAM Performance 검색과정(SELECT) 한 개의 row를 찾는데 걸리는 시간? log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1. 몇 개의 “Block”을 읽어오는가로 속도가 결정되므로... 여러 개의 Row를 읽어오는 구문에 대해서는 어떻게 처리할까?

MyISAM Performance 검색과정(SELECT) To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows

InnoDB Oracle Corporation GNU General Public License or proprietary InnoDB는 Oracle Coporation에서 만들었고, 이거도 GPL인데 proprietary라서 제공된 기능 이외의 사용은 제한됩니다.

InnoDB 좀 느리다. 고 용량이다. Transaction을 지원한다.

InnoDB InnoDB는 오류가 발생하면, Transaction 기능을 통해 스스로 로그를 통해 빠르게 복구하는 기능을 가지고 있다. 즉, 안정적이다. Database는 서로 query가 충돌하는 경우가 많으므로, 이 기능은 상당히 중요하다. 이것이 InnoDB가 MyISAM보다 자주 쓰이는 이유이다.

InnoDB MyISAM과 비슷한 구조를 하고있다. 뭐가 다를까? -> 1. B-tree를 이용한다. 2. Transaction을 지원한다. 3. 2번을 위해 좀더 용량이 크다. 4. 엔진 스스로 캐시한다. (MyISAM은 OS가 캐시함) 6. InnoDB는 하나의 파일에 저장된다. 등등… InnoDB : Row-level Locking MyISAM : Table-level Locking ->보통 속도는 MyISAM이 빠르지만, 같은 Table을 여러 Thread가 사용한다면? InnoDB가 빠르다! 5. Locking 방법이 다르다.

InnoDB INFORMATION_SCHEMA 위의 테이블에서 여러 옵션들이 조정가능하다. 자세한 사항은 Reference를 참고하시길…

InnoDB 어떤 구조로 되어있길래??? “TableSpace라는 곳에 모든걸 저장한다!” PDF파일 참고

Cache 캐시 기능이란? 특정 쿼리에 대해서 결과를 저장해두는 것. 주의할 점! Select * from t1 캐시에서는 두 개를 다르게 인식한다.

최적화 속도의 중심은 바로 SELECT구문에 있다! 보통 가장 많이 사용되며, 가장 느리다. 어떻게 하면 이걸 빠르게 할 수 있을까?

SELECT 최적화 Removal of unnecessary parentheses: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d) Constant folding: (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5 Constant condition removal (needed because of constant folding): (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6

SELECT 최적화 SELECT COUNT(*) FROM tbl_name; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10; ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10; 빠른 구문의 예

SELECT 최적화 SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2; SELECT key_part2 FROM tbl_name GROUP BY key_part1; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ; ORDER BY key_part1 DESC, key_part2 DESC, ... ; 빠른 구문의 예

SQLAlchemy SQLAlchemy는 무엇을 하는가? 결국 Query를 만들어준다. Example) Qeury.session().filter_by(A).all();

SQLAlchemy 자세한 것은 없음…

Arara의 문제점? 글목록 # XXX: 갖고 와서 빼는군여. 가져올 때 빼세요. article_list = session.query(model.Article).filter_by(root_id=None).order_by(model.Article.id.desc())[offset:last]

수고하셨습니다.

Reference http://dev.mysql.com/doc/refman/5.0/en/ http://en.wikipedia.org/wiki/R-tree http://www.innodb.com/doc/