🎯 기수성
컬럼의 유니크한 값의 갯수에 따라 실행계획이 어떻게 수립되는지 실제로 확인해본다.
✅ Ready
2개의 테이블과 2개의 덤프 데이터를 제공합니다.
🔗
DDL.sql: 테이블 생성 쿼리🔗
city-high.dump: 기수성이 높은 데이터🔗
city-row.dump: 기수성이 낮은 데이터🎯 목표에 앞서
MySQL 에 삽입된 2개의 테이블은
city 와 country 컬럼만 존재하며 country 컬럼으로 인덱스를 생성했다.총 10_000 건의 데이터가 있으며
city 는 "city" + i 로 생성되어 city1city2city3...city10000 으로 두 테이블에서 city 컬럼의 데이터는 같지만country 컬럼은 다르다. 기수성은 높은 테이블은 country 의 기수성이 1_000, 기수성이 낮은 테이블은 기수성이 10이다. 요약하자면 다음과 같다.- city-high 데이터 요약
Total: 10_000 > city 컬럼 city1 부터 city10000 까지 존재함. > country 데이터는 1부터 시작해 10_000까지 존재한다. city1, country1, city2, country2, city3, country3, ... cityN, countryN,
- city-row 데이터 요약
Total: 10_000 > city 컬럼 city1 부터 city10000 까지 존재함. > country 데이터는 1부터 시작해 10까지만 존재한다. city1, country1, city2, country2, ... city?, country10, city?, country1, city?, country2, ... city?, country10, city?, country1, city?, country2, ...
두 컬럼의 조회 실행계획을 알아보자.
🎯 실행계획
두 개의 dump 데이터에서 알 수 있듯이 아래의 '???' 부분에 값과 일치하는 데이터가 존재한다면 갯수는 단 1건일 것이다.
- WHERE city = '???' AND country = '???'
이 1건을 찾기위해 불필요한 데이터들을 얼마나 읽고 거르느냐에 대해 효율성이 갈린다.
전체 데이터는 10_000개이므로 기수성이 1_000일 땐 10개의 데이터를 조회할 것이고, 기수성이 10일 땐 1_000개의 데이터를 조회할 것이다.
실행 계획은 다음과 같다.
- tb_city_high 기수성: 1_000
mysql> EXPLAIN SELECT * FROM tb_city_high WHERE city = 'city9' AND country = 'country9'; +----+-------------+--------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | tb_city_high | NULL | ref | ix_country | ix_country | 123 | const | 10 | 10.00 | Using where | +----+-------------+--------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
- tb_city_row 기수성: 10
mysql> EXPLAIN SELECT * FROM tb_city_row WHERE city = 'city9' AND country = 'country9'; +----+-------------+-------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | tb_city_row | NULL | ref | ix_country | ix_country | 123 | const | 1000 | 10.00 | Using where | +----+-------------+-------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
실행 계획에서 눈여겨 볼 부분은 ix_country 인덱스를 활용한 부분과 rows, filtered 부분이다.
- rows: 조건을 만족하기 위해 읽어야할 총 데이터 수
- filterted: 읽은 데이터 중 만족하는 데이터의 비율
위의 조건을 만족하는 데이터는 단 1건이기 때문에 tb_city_high(기수성:1_000) 테이블은 9건의 불필요한 데이터를 읽었지만 tb_city_row(기수성:10) 테이블은 999건의 불필요한 데이터를 읽었다.
읽기 손익분기점
인덱스를 통해 테이블을 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 든다.
테이블의 전체 레코드가 100만건인데 이 중 읽어야할 레코드가 50만건이라고 가정해보자.
100만건을 모두 읽어서 50만건을 버릴지, 인덱스를 사용해 필요한 50만건만 읽을 것인지 어느 상황이 더 효율적인지 판단해야 한다.
일반적인 DBMS의 옵티마이저는 인덱스를 통한 읽기의 성능이 직접 테이블을 읽는 성능보다 4-5배 정도 비용이 많이 든다고 판단한다. (인덱스 > 4-5배 > 테이블)
전체 레코드의 20~25% 를 읽는 쿼리라면 인덱스를 통한 읽기 손익분기점이 손해이므로 인덱스를 사용하지 않고 처리하는 것이 유리하다.