[MySQL]InnoDB VS MyISAM

InnoDB

 
InnoDB 및 BDB 스토리지 엔진은 트랜젝션에 안전한 테이블을 제공하는 트랜잭션-세이프 스토리지 엔진이다.
InnoDB는 모든 5.0 바이너리 배포판에 디폴트로 포함되어 있다.
MyISAM과 비슷하지만 ORACLE처럼 많은 기능을 지원한다.

commit, rollback, 장애복구, row-level locking, 외래키 등
다수의 사용자 동시접속과 퍼포먼스가 증가하여 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었습니다.
CPU 효율은 어느 디스크 기반의 데이터 베이스와 비교해도 우수하고, 자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)을 관리한다.
 
MyISAM 과 다른 점으로 테이블과 인덱스를 테이블 스페이스에 저장을 하고 테이블 스페이스는 몇개의 서버파일이나 디스크 파티션으로 구성되어있습니다.

MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리
InnoDB 테이블은 OS의 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있다.
InnoDB는 높은 퍼포먼스가 필요한 대용량 사이트에 적합합니다.
 
InnoDB의 장점으로는 MyISAM의 단점으로 있었던 row level locking이 지원된다.
그렇기 때문에 트랜잭션 처리가 필요한 대용량의 데이터에 유리한 점이 있어서, 사용자의 CRUD가 많은 서비스에 유리하다.
 

MyISAM

 
MyISAM은 비-트랜젝션-세이프(non-transactional-safe) 테이블을 관리한다.
전체 문장 검색 능력 뿐만 아니라, 고-성능 스토리지 밀 복구 기능을 제공한다.
모든 MySQL 구성에서 지원되며, 다른 타입의 엔진으로 디폴트 구성하지 않는 한 이 엔진이 디폴트 스토리지 엔진으로 구성된다.
 
MyISAM은 블로그라던지, 게시판 처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에 최적의 성능을 발휘한다.
MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리한다.
 
MyISAM의 장점으로는 항상 테이블에 ROW COUNT를 가지고 있기 때문에 SELECT count(*) 명령시 빠르고, SELECT 명령시에도 빠른 속도를 지원한다.
MyISAM은 풀텍스트 인덱스를 지원하는데 그렇기 때문에 Read Only기능이 많은 서비스일 수록 MyISAM이 효율적이라 할 수 있다.
풀텍스트 인덱스 : 검색 엔진과 유사한 방법으로 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장
단점으로는, row level locking을 지원하지 못해, SELECT INSERT UPDATE DELETE시 해당 Table 전체에 Locking이 걸린다.
 
그래서 row의 수가 커지면 커질수록 CRUD 속도는 엄청나게 느려진다.
 

실제 비교

 
테이블 마다 다른 스토리지 엔진을 부여 하여 테스트 하였다.
 

 

    @Test
    void SELECT_WHERE_QUERY_INNODB(){
        stopWatch.start();

        restaurantRepository.findAllByRoadNamefullAddress("대구광역시 남구 명덕로68길 38-2");

        stopWatch.stop();
        System.out.println("몇초 : " + stopWatch.getTotalTimeSeconds());
    }
    
    @Test
    void SELECT_WHERE_QUERY_MYISAMDB(){
        stopWatch.start();

        restaurantIsamRepository.findAllByRoadNamefullAddress("대구광역시 남구 명덕로68길 38-2");

        stopWatch.stop();
        System.out.println("몇초 : " + stopWatch.getTotalTimeSeconds());
    }

 

InnoDB 결과

StopWatch 결과로는 3.567333초가 나왔다.
 

MyISAM 결과

StopWatch 결과로는 1.101988201초가 나왔다.
 

---

    @Test
    void SELECT_COUNT_QUERY_INNODB(){
        stopWatch.start();

        System.out.println(restaurantRepository.count());

        stopWatch.stop();
        System.out.println("몇초 : " + stopWatch.getTotalTimeSeconds());
    }

    @Test
    void SELECT_COUNT_QUERY_MYISAMDB(){
        stopWatch.start();

        System.out.println(restaurantIsamRepository.count());
        
        stopWatch.stop();
        System.out.println("몇초 : " + stopWatch.getTotalTimeSeconds());
    }

 

InnoDB 결과

StopWatch 결과로는 0.9708418초가 나왔다.
 

MyISAM 결과

StopWatch 결과로는 0.4366006초가 나왔다.
 

---

10개 데이터를 삽입하는것을 구현했다.

    @Test
    void INSERT_QUERY_INNODB() {

        int num = 10000001;
        List<Restaurant> restaurantList = new ArrayList<>();

        for (int i = 1; i < 10; i++) {
            restaurantList.add(Restaurant.builder()
                    .num(num+i)
                    .open_service_name("일반음식점")
                    .open_service_id("07_24_04_P")
                    .open_local_government_code("3000000")
                    .management_number("3000000-101-2022-001")
                    .date_of_license("2022-05-17")
                    .date_of_license_cancellation(null)
                    .business_status_classification_code("03")
                    .business_status_name("폐업")
                    .detailed_business_status_code("02")
                    .detailed_business_status_name("폐업")
                    .date_of_closure("2022-09-01")
                    .closed_start_date(null)
                    .closed_date(null)
                    .reopening_date(null)
                    .location_phone("")
                    .material_area("20.40")
                    .address_zip_code("110-390")
                    .full_address_of_location("서울특별시 종로구 봉익동 52 1층")
                    .roadNamefullAddress("서울특별시 종로구 서순라길 27-4,")
                    .road_name_zip_code("03138")
                    .business_name("술 연구소")
                    .last_modified_time("2022-09-01 13:40:34")
                    .classification_of_data_renewal("U")
                    .data_update_date("2022-09-01 13:40:34")
                    .clear_business_type("기타")
                    .coordinate_information_x("199367.405972534")
                    .coordinate_information_y("452186.6690747")
                    .name_of_hygiene_business("기타")
                    .number_of_male_employees("0")
                    .number_of_female_employees("0")
                    .classification_around_the_business_place("")
                    .classification_name("")
                    .classification_of_water_supply_facilities("")
                    .total_number_of_employees("0")
                    .number_of_head_office_employees("0")
                    .no_of_factory_office_workers("0")
                    .number_of_factory_sales_employees("0")
                    .number_of_factory_production_workers("0")
                    .classification_of_building_ownership("")
                    .guarantee_amount("0")
                    .monthly_rent("0")
                    .whether_it_is_a_multi_use_business("N")
                    .total_facility_scale("20.4")
                    .traditional_business_designation_number("")
                    .traditional_business_staple_food("")
                    .home_page("")
                    .build());
        }

        stopWatch.start();

        for (Restaurant restaurant1 : restaurantList){
            restaurantRepository.save(restaurant1);
        }

        stopWatch.stop();
        System.out.println("몇초 : " + stopWatch.getTotalTimeSeconds());
    }

    @Test
    void INSERT_QUERY_MYISAMDB() {

        int num = 10000001;
        List<RestaurantIsam> restaurantIsams = new ArrayList<>();

        for (int i = 1; i < 10; i++) {
            restaurantIsams.add(RestaurantIsam.builder()
                    .num(num+i)
                    .open_service_name("일반음식점")
                    .open_service_id("07_24_04_P")
                    .open_local_government_code("3000000")
                    .management_number("3000000-101-2022-001")
                    .date_of_license("2022-05-17")
                    .date_of_license_cancellation(null)
                    .business_status_classification_code("03")
                    .business_status_name("폐업")
                    .detailed_business_status_code("02")
                    .detailed_business_status_name("폐업")
                    .date_of_closure("2022-09-01")
                    .closed_start_date(null)
                    .closed_date(null)
                    .reopening_date(null)
                    .location_phone("")
                    .material_area("20.40")
                    .address_zip_code("110-390")
                    .full_address_of_location("서울특별시 종로구 봉익동 52 1층")
                    .roadNamefullAddress("서울특별시 종로구 서순라길 27-4,")
                    .road_name_zip_code("03138")
                    .business_name("술 연구소")
                    .last_modified_time("2022-09-01 13:40:34")
                    .classification_of_data_renewal("U")
                    .data_update_date("2022-09-01 13:40:34")
                    .clear_business_type("기타")
                    .coordinate_information_x("199367.405972534")
                    .coordinate_information_y("452186.6690747")
                    .name_of_hygiene_business("기타")
                    .number_of_male_employees("0")
                    .number_of_female_employees("0")
                    .classification_around_the_business_place("")
                    .classification_name("")
                    .classification_of_water_supply_facilities("")
                    .total_number_of_employees("0")
                    .number_of_head_office_employees("0")
                    .no_of_factory_office_workers("0")
                    .number_of_factory_sales_employees("0")
                    .number_of_factory_production_workers("0")
                    .classification_of_building_ownership("")
                    .guarantee_amount("0")
                    .monthly_rent("0")
                    .whether_it_is_a_multi_use_business("N")
                    .total_facility_scale("20.4")
                    .traditional_business_designation_number("")
                    .traditional_business_staple_food("")
                    .home_page("")
                    .build());
        }

        stopWatch.start();

        for (RestaurantIsam isam : restaurantIsams){
            restaurantIsamRepository.save(isam);
        }

        stopWatch.stop();
        System.out.println("몇초 : " + stopWatch.getTotalTimeSeconds());
    }

InnoDB 결과

StopWatch 결과로는 0.1471217초가 나왔다.
 

MyISAM 결과

StopWatch 결과로는 0.131337199초가 나왔다.
 
이론과 테스트결과가 이상하다...
insert 하는 순간 locking 이 걸린다고 했는데 아! 비동기로 테스트 해보면 되지 않을까 해서 비동기로 테스트 해보았다.
 

        List<CompletableFuture<Void>> futures = new ArrayList<>();

        for (RestaurantIsam isam : restaurantIsams) {
            futures.add(saveAsync(isam));
        }

        CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
    
    
    @Async
    public CompletableFuture<Void> saveAsync(RestaurantIsam isam) {
        restaurantIsamRepository.save(isam);
        return CompletableFuture.completedFuture(null);
    }

InnoDB 결과

StopWatch 결과로는 0.158149501초가 나왔다.
 

MyISAM 결과

StopWatch 결과로는 0.148420699초가 나왔다.
 
내 이론이 틀린것 같아서 시니어 개발자분이신 ChatGTP 의 의견을 물어봤다.
 
 

MyISAM vs. InnoDB 특징

• MyISAM은 전체 테이블 잠금을 사용하며, 이로 인해 높은 동시 쓰기 작업에서는 성능 이점이 있을 수 있지만, 단일 쓰기 작업에서는 InnoDB와 차이가 크지 않을 수 있습니다.
• InnoDB는 레코드 기반 잠금을 지원하고, 트랜잭션을 처리할 수 있다는 이점이 있습니다. 단일 또는 소규모 삽입 작업에서는 MyISAM과 큰 차이가 없을 수 있습니다.

해결 방안

1. 데이터 양 증가: 삽입할 데이터의 양을 수천, 수만 건으로 늘려보세요. 많은 데이터가 처리될 때 성능 차이가 더 명확해질 수 있습니다.
2. 동시성 테스트: 다중 스레드를 사용해 더 많은 동시 쓰기 작업을 실행해 보세요. 예를 들어, 1000개 이상의 데이터를 동시에 삽입하거나, 여러 사용자가 동시에 접근하는 상황을 시뮬레이션해 볼 수 있습니다.
3. 복잡한 쿼리 실행: 단순 삽입 외에도 복잡한 SELECT 쿼리나 UPDATE, DELETE 작업을 통해 두 엔진의 성능을 비교해 보세요.
4. 서버 설정 최적화: MySQL 서버의 설정을 조정해보세요. 각 엔진에 맞는 최적화가 필요할 수 있습니다.

이러한 이야기가 나왔다.
하지만 조회 성능에 대해 차이점을 분명히 느낄수 있었기에 만족한 테스트 결과였고
다음 시간이 될때 좀더 복잡한 쿼리로 성능차이를 느껴보고 싶다.