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가 더 작고 Auto increment 로 설정되어 있어도 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. 인덱스의 설정방법
인덱스 설정시에는 따로 따로 인덱스를 선언해줘야 제대로 설정이 가능하다.

// 잘못된 방법
INDEX(id, age)

// 올바른 방법
INDEX(id), INDEX(age)


5. Full Scan을 최소화하여 검색을 빠르게 하는 INDEX
인덱스를 적용한 칼럼과 적용되지 않은 칼럼의 차이는 Full Scan 여부 이다.
1억개의 Row를 하나 하나 찾아가는것보다 목차를 보고 한번에 찾는것이 훨씬 빠르다.


6. WHERE 절에서 검색 대상 칼럼이 인덱스여야 한다.
검색할 대상이 책 목차에 나와있어야 책의 페이지를 알고 해당 페이지에서 내용을 볼 수 있다.

SELECT * FROM test WHERE id = '1';


7. 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';


8. 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 = " SELECT * FROM test WHERE id = '1' ";
mysqli_query($sql);
$sql = " SELECT * FROM test WHERE age = '20' ";
mysqli_query($sql);


참고자료
https://www.burndogfather.com/238