MySQL INDEX(인덱스)를 이용한 성능 최적화 팁
MySQL 혹은 MariaDB 환경에서 1억개의 Row가 저장되어 있다고 가정하자.
인덱싱이 지정되어 있지 않은 칼럼에서 검색을 수행했을때 데이터베이스는 1억개를 Full Scan을 하게 되어 SELECT 쿼리결과가 나오기까지 오랜시간이 필요로 한다.
이렇게 비교적(?) 대용량의 데이터를 처리하기 위해서는 여러가지 튜닝방법이 존재하는데, 튜닝의 우선순위는 아래와 같다.
1. 가급적 로직을 DB상에서 처리하지 않고 WEB Applicaition 상에서 구현한다.
WEB Application 부하는 DB Server에서 발생하는 부하보다 비교적 쉽게 해결이 가능하다.
Web 서버는 라운드로빈이든 L4를 활용하든 너무나 쉽게 확장이 가능한 반면, DB Server는 Master-Slave Replication 부터 생각해야되고 데이터의 실시간 동기화가 되지 않는 경우의 수가 너무나도 많은게 사실이다.
그리고 SQL상의 JOIN보다는 단순 SELECT로 웹단에서 로직을 구현하면 더 쉽고 추후 유지보수나 NoSQL으로의 전환이 쉽다.
대규모 서비스일수록 RDBMS보다 NoSQL을 사용하고 있고 많은 서비스들이 하둡을 도입하면서 NoSQL로 갈아타고 있는 이유를 생각보면 이해가 될 것이다.
2. 칼럼의 데이터길이를 최대한 작게 보수적으로 설정한다.
Bigint 보다는 int가 더 작고 Autoincrement 로 설정되어 있어도 int를 대부분 다 활용 못한다.
성명을 저장하는 칼럼이 길어봤자 8자리미만인데 varchar(255)로 설정하고 있는것은 아닌지확인한다.
3. Insert 보다는 Select 횟수가 많기 때문에 Select에 보수적인 로직을 구성한다.
DB의 처리과정에서 가장 느린로직이 INSERT와 UPDATE이다. 하지만 실제 서비스에서는 로그를 실시간으로 무지막지하게 쌓지 않는 이상 보통의 서비스에서는 INSERT 횟수보다 SELECT횟수가 훨씬 많다.
따라서 우리는 SELECT에 초첨을 맞춰 로직을 구성해야한다.
아예 처음부터 Mysql Connection을 선언할때 SELECT 로직용, INSERT로직용으로 나눠두면 추후 DB서버 분산화할때도 유리하다.
그리고 대부분 SELECT * FROM 을 사용하지만 실제로 Output 될 칼럼을 지정하여 보수적으로 작성하는것이 좋다.
//이거보다 SELECT * FROM test; //이게 더 빠르다. SELECT id FROM test;
4. 파티션 테이블
테이블하나의 모든 데이터를 저장하지 않고 테이블1, 테이블2, 테이블3 와 같이 테이블을 쪼개어 저장하는 방식을 말한다.
관리하기 참 그지같으면서 다중테이블 조회도 감안해야되는 문제점이 있으며, 어떤 기준으로 테이블을 쪼갤것인지 심도있게 생각해야한다.
하지만 파티션 테이블은 테이블자체가 분리되기 때문에 데이터 훼손 가능성을 감소시켜주며 I/O성능 향상을 가져다 줄수 있다. 각 테이블별로 독립적인 복구도 가능하다.
지역단위 / 월단위 / 요일단위 / 5000개씩 끊기 등등이 있지만, 파티셔닝의 단점은 테이블마다 제대로 분배가 힘든것이 있다.
무엇보다 파티셔닝이 많은 상태에서 급격한 DB사용이 발생될경우 I/O성능의 한계로 오히려 부하가 발생되기도 한다.
5. 물리적인 HW나 Instant 증설
최후의 수단이다.
이것도 답이 안나오면 다른 RDBMS를 생각하거나 NoSQL을 생각한다.
(하지만 그걸 생각하는 상황이 되면 마이그레이션을 어떻게 진행할 것인지 마이그레이션에 최적화된 친구가 어떤것인지를 우선적으로 살펴보게 될 것이다.)
위 내용들은 사실 기본적인 내용이다.
부하감소와 속도향상을 위해서는 데이터베이스내 적절한 인덱스를 생성하여 인덱스를 최대한 활용한 SQL활용이 필요하다.
인덱스를 사용하지 않는다면 RDBMS 성능의 천만분의 1도 사용하지 않은 것이다.
사실, 튜닝의 끝은 순정이라고...
MySQL Basic Document 문서대로 한다면 최적화할 필요도 없는게 함정. ㅋ
(Document대로 정석으로 설계하고 로직을 구현해도 답이 안나오는 상황이라면 그 시스템은 다른 데이터베이스를 고려해야한다.)
인덱스의 설정방법
인덱스 설정시에는 따로 따로 인덱스를 선언해줘야 제대로 설정이 가능하다.
//이러면 안됨 INDEX(id, age)//이래야됨 INDEX(id), INDEX(age)
Full Scan을 최소화하여 검색을 빠르게 하는 INDEX
인덱스를 적용한 칼럼과 적용되지 않은 칼럼의 차이는 Full Scan이냐 이다.
1억개의 Row를 하나 하나 찾아가는것보다 목차를 보고 한번에 찾는것이 훨씬 빠르다.
우리가 백과사전을 본다고 생각하면 이해가 더욱 쉬울것이다.
WHERE 절에서 검색 대상 칼럼이 인덱스여야 한다.
당연한 소리이다. 우리가 검색할 대상이 책 목차에 나와있어야 책의 페이지를 알고 해당 페이지에서 내용을 볼 수 있다.
SELECT * FROM test WHERE id = '1';
AND절 검색시 하나의 칼럼에만 인덱스 조회가 된다.
인덱스가 지정된 여러개의 칼럼을 검색할때는 다수의 인덱싱 조회가 불가능하기 때문에 내부적인 로직에 의해 알아서 최적화된 인덱스만 검색한다. 하지만, 우리는 보수적인 AND 조건을 위해 다수 인덱싱 조회가 필요할때가 있다.
아래 예시처럼 FORCE INDEX(칼럼) 을 지정하면 해당 칼럼도 인덱스 조회가 한번 더 진행된다.
//id 기준으로만 인덱싱 조회 SELECT * FROM test WHERE id = '15' and age = '24'; //id와 age 모두 인덱싱을 조회하기 때문에 2번 조회한다. SELECT * FROM test FORCE INDEX(age) WHERE id = '15' and age = '24';
OR절 검색은 무조건 Full SCAN 된다.
AND와 달리 OR을 사용하는 SELECT는 무조건 Full Scan을 하는 노답상황이 발생한다.
따라서 OR 절은 UNION을 사용하거나 Web Application 단에서 처리하는게 답이다.
//인덱싱을 걸어도 걍 무조건 Full Scan SELECT * FROM test WHERE id = '1' OR age = '20'; //UNION을 이용한 인덱싱 조회 SELECT * FROM test WHERE id = '1'; UNION SELECT * FROM test WHERE age = '20'; //Web Application단에서 SELECT 두번 사용 $sql_id = "SELECT * FROM test WHERE id = '1'"; mysqli_query($sql_id); $sql_age = "SELECT * FROM test WHERE age = '20'"; mysqli_query($sql_age);
LIMIT를 사용할때는 무조건 LIMIT가 되는 기준을 정해준다.
보통 페이징을 구현할때 빠르게 Access를 제공하기 위해서 페이징을 구현하는데, 사실 LIMIT를 잘못 사용하게 되면 Full SCAN하게 되어 페이징을 하는 의미가 사라진다.
LIMIT를 사용할때는 LIMIT가 되는 기준을 무조건 정해주는것이 원칙이며, 사실 LIMIT를 정해주는 기준을 정해줘도 페이지가 백번째, 천번째... 등으로 넘어가게되면 조회 횟수가 증가하게 된다. 후반부 페이지로 넘어가도 빠르게 조회하기 위해서는 PK값을 사용하는 WHERE 절과 함께 사용하면 최대한 적게 조회하여 빠르게 엑세스가 가능하다.
//Full SCAN SELECT * FROM test LIMIT 10, 5; //기준을 정해서 검색해서 조금 더 빨라짐 SELECT * FROM test ORDER BY id LIMIT 10; //기준을 정했으나 실제로는 10x100 = 1000번을 조회해야함. SELECT * FROM test ORDER BY id LIMIT 10, 100; //기준 + WHERE절 사용으로 조회횟수 최소화 (권장하는 방법) SELECT * FROM test WHERE id > '100' ORDER BY id LIMIT 10;중간에 데이터 하나를 삭제하게 된다면 삭제된 횟수를 카운트해야 권장하는 방법으로 페이징이 제대로 작동하는것을 유념해야한다.
id가 1부터 1000까지 있다고 가정한 상황에서 1~10의 id를 삭제한 상태에서는 아래와 같이 검색되어야 한다.
여기서부터 머리가 아파온다. ㅋㅋㅋ
//삭제전 SELECT * FROM test WHERE id > '100' ORDER BY id LIMIT 10; //1~10까지 삭제후 SELECT * FROM test WHERE id > '90' ORDER BY id LIMIT 10; //100~110가 삭제되었을때는 상관없음 SELECT * FROM test WHERE id > '100' ORDER BY id LIMIT 10;
LIKE % 보다는 FULLTEXT를 활용한다.
제목이나 내용등을 검색하기 위해 LIKE 절을 많이들 사용하는데 LIKE절은 칼럼안에 데이터를 확인해야하기 때문에 무조건 Full SCAN이 이뤄진다. 따라서 자연어 검색처리를 위한 FULLTEXT를 사용해야한다. MySQL문서를 보면 이 FULLTEXT는 단어별로 트리를 미리 구성해놓아 검색엔진처럼 빠른 검색을 제공합니다.
//Full SCAN SELECT * FROM test WHERE name LIKE '%홍길동%'; //FULLTEXT를 활용한 검색 SELECT * FROM test WHERE MATCH(name) AGAINST('홍길동');
다중 LIKE 조회에서 포함되는 내용들을 검색할 때
SELECT * FROM test WHERE MATCH(name) AGAINST("+'홍길동'+'강감찬'+'박명수'" IN BOOLEAN MODE)
다중 LIKE 조회에서 누구는 포함되고 누구는 포함되지 않는 내용들을 검색할 때
SELECT * FROM test WHERE MATCH(name) AGAINST("+'홍길동'-'넌빼고'-'난빼고'" IN BOOLEAN MODE)