인덱스(Index) 란?

면접에서 많이 질문을 받는다는 인덱스. 그만큼 현업에서 중요하다는 뜻.

하지만 프로젝트를 진행하면서 한번도 만들어 본 적도 써 본 적도 없다.

프로젝트 경험 횟수가 적었고 당시에는 써 볼 여유가 없었다.

그렇기에 인덱스에 대한 공부 겸 직접 만들어서 성능 테스트를 한번 해보았다.

 

 

---

 

 

인덱스 (Index)

 

위키백과에서는 인덱스를 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조라고 정의되어 있습니다. 그리고 인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다고 적혀있습니다.

 

쉽게 말하자면 테이블에서 특정 데이터를 찾으려고 할 때 쉽게 찾을 수 있도록 만들어 놓은 별도의 테이블 즉, 책으로 예를 들면 맨 뒷장에 있는 찾아보기 영역을 생각하시면 됩니다. 예전에 시험 공부를 할 때 책에서 어떤 특정 단어가 포함되어 있는 페이지를 찾아야 할 경우 맨 뒷장을 펴서 포함된 페이지를 찾은 뒤, 그 페이지로 넘어갔던 경험이 있는데 그걸 떠오르시면 될 것 같습니다.

 

 

 

 

 

 

어떻게 속도를 높여줄까?

 

 

속도를 높여주기 때문에 인덱스를 이용한다고 위에서 말했는데 어떻게 속도를 높여줄까요?

 

우선, 인덱스를 사용하지 않고 테이블의 특정 로우를 찾기 위해 WHERE 절을 사용해 SELECT 하는 경우를 생각해봅시다.

스캔을 시작하다 조건에 맞는 로우가 맨 위에서 나왔다 하더라도 그 밑에도 찾으려는 데이터가 있는지 없는지를 알 수 없으니 테이블 전체를 스캔하게 될 것입니다.

 

그렇다면 인덱스를 생성한다면 어떨까요?

인덱스는 위에서 말한대로 컬럼을 이용하여 생성합니다. 인덱스의 구조는 인덱스로 지정한 컬럼(Key) 값과 해당 컬럼에 해당하는 ROWID로 구성이 되어 있는데요. 인덱스가 있는 상태에서 WHERE 절을 사용하여 SELECT 한다면 해당 데이터가 있는 ROWID 부터 검색해서 스캔하다 다른 데이터가 나오면 스캔을 그만두게 됩니다.

 

테이블이 10건 정도일 때는 전체를 스캔하나 인덱스를 이용하여 해당 블록만 스캔하나 크게 속도에서 차이는 없겠지만 100만 건이라면 말이 달라지겠죠? 속도면에서 크게 차이가 날 것입니다. 

 

 

 

 

 

인덱스 만들어 보기

 

 

데이터 조회시 성능을 높여준다곤 했는데 정말로 풀 스캔을 했을 때와 인덱스를 이용했을 때 차이가 있는지 알아보고 싶었습니다.

 

 

우선 테이블을 만듭니다.

 

create table test_table (
	table_no int not null auto_increment primary key,
	table_content varchar(20) not null
);

 

 

 

다음으로 더미 데이터를 넣어줘야 하는데 넣는 방법에는 여러가지 방법이 있겠지만,

저는 블로그를 참고하여 JDBC의 addBatch를 이용해 데이터를 30만건 정도 넣어줬습니다.

데이터베이스는 MariaDB를 이용하였습니다.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

//출처: https://fruitdev.tistory.com/111

public class TestAddBatch {
	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "Insert Into test_table(table_content) Values(?)" ;

		String[] strArr = {"orange","apple","banana","tomato","watermelon"
							,"melon","blueberry","strawberry","grape","pear"};
		int index=0;
	
		try{
            Class.forName("org.mariadb.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mariadb://localhost:3307/test", "root", "1004");
             
             
            pstmt = con.prepareStatement(sql) ;
             
             
            for(int i=0; i < 100000; i++){
            	
                String str = strArr[index++];
                if(index == strArr.length) index=0;
                
             
                
                pstmt.setString(1, str) ;
                
                // addBatch에 담기
                pstmt.addBatch();
                 
                // 파라미터 Clear
                pstmt.clearParameters() ;
                 
                 
                // OutOfMemory를 고려하여 만건 단위로 커밋
                if( (i % 10000) == 0){
                     
                    // Batch 실행
                    pstmt.executeBatch() ;
                     
                    // Batch 초기화
                    pstmt.clearBatch();
                     
                    // 커밋
                    con.commit() ;
                     
                }
            }
             
             
            // 커밋되지 못한 나머지 구문에 대하여 커밋
            pstmt.executeBatch() ;
            con.commit() ;
             
        }catch(Exception e){
            e.printStackTrace();
             
            try {
                con.rollback() ;
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
             
        }finally{
            if (pstmt != null) try {pstmt.close();pstmt = null;} catch(SQLException ex){}
            if (con != null) try {con.close();con = null;} catch(SQLException ex){}
        }
 
    }
 
}

 

 

 

 

저는 수박을 좋아하기 때문에 컬럼 값이 수박인 데이터를 모두 조회해보겠습니다.

 

우선 인덱스를 사용하지 않고 조회해보겠습니다.

툴은 DBeaver를 사용했습니다.

 

select * from test_table where table_content = 'watermelon';

 

 

총 31141건이 조회되었고 대략 55ms의 시간이 걸린 것을 알 수 있습니다.

 

 

 

다음은 인덱스를 생성하고 조회를 해보겠습니다.

table_content를 이용하여 인덱스를 생성해줍니다.

인덱스 생성 문법은 이렇습니다.

 

CREATE INDEX 인덱스이름
ON 테이블이름 (컬럼이름1, 컬럼이름2, ...)

create index test_index on test_table(table_content);

 

 

그리고 다시 수박을 조회해보겠습니다.

 

 

마찬가지로 31141건이 조회되었고 시간은 15ms 정도의 시간이 걸린 것을 알 수 있습니다.

확실히 인덱스를 사용하기 전보다 시간이 줄었으며 데이터 개수가 많았다면 더 분명한 차이를 볼 수 있을걸로 예상됩니다.

 

 

 

 

 

인덱스는 무조건 사용해야할까?

 

 

인덱스는 조회 시 성능을 향상 시켜주기에 무조건 인덱스를 사용하는 것이 좋을까요?

그건 또 아닙니다.

 

CRUD 중 R을 제외한 나머지 작업을 할 때는 오히려 성능을 떨어뜨리게 합니다.

 

INSERT의 경우 새로운 데이터를 테이블 외에 인덱스에도 추가하기 때문에 성능이 저하될 수 있습니다.

 

DELETE의 경우 테이블에서만 데이터가 삭제되고 인덱스에서는 사용하지 않는다는 작업만 진행하다 보니 늘어난 데이터로 인해 쿼리 수행 속도가 저하될 수 있습니다.

 

마지막으로 UPDATE의 경우 INSERT와 DELETE 작업이 모두 발생(기존의 인덱스를 사용하지 않음으로 처리하고, 갱신된 데이터를 인덱스에 추가함)되어 더 큰 부하를 줄 수 있습니다.

 

그렇기에 데이터베이스를 설계할 때 해당 테이블에서 데이터 조회를 많이 하는지, 데이터 입력이나 삭제가 많이 되는지를 잘 고려하여 인덱스를 생성할 필요가 있습니다.

 

 

 

 

 

References

- lovefor-you.tistory.com/183

- ko.wikipedia.org/wiki/%EC%9D%B8%EB%8D%B1%EC%8A%A4_(%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4)