[MYSQL] 응용 용어1

선택도

테이블의 특정 열을 기준으로 해당 열의 조건절(WHERE 절 조건문)에 따라 선택되는 데이터 비율을 의미

 

'선택도가 높다'의 의미 : 해당 열에 중복되는 데이터가 많다

'선택도가 낮다'의 의미 : 해당 열에 중복되는 데이터가 적다

 

선택도가 낮은 A는 새우가 들어가 있는 한 조각을 고르고, 선택도가 높은 B는 여러 조각 중 새우가 들어가있는 것을 알 수 있음

즉, 낮은 선택도가 오히려 대용량 데이터에서 원하는 데이터만 골라내는 능력이라는 것을 우회적으로 보여줌

 

낮은 선택도를 가지는 열은 데이터를 조회하는 SQL문에서 원하는 데이터를 빨리 찾기 위한 인덱스 열을 생성할 때 주요 고려대상이 됨

선택도를 계산하는 수식

데이터에 접근하고자 특정 열에 대한 조건문을 작성, 해당 조건문에 포함되는 열의 선택도를 산출

선택도 = 선택한 데이터 건수 ÷ 전체 데이터 건수

선택하는 조건절의 데이터 건수를 매번 계산할 수 없고, 데이터의 삭제와 수정, 삽입이 수시로 발생하는 만큼 중복이 제거된 데이터의 건수를 활용하여 선택도를 일반화 함

변형된 선택도 = 1 ÷ DISTICT(COUNT 열명)

 

실제 학생 테이블에서 기본적인 선택도 수식을 활용하여 계산하는 예제 (총 100건 데이터 저장)

 

학번 열의 선택도 =  1 ÷ 100 = 0.01

 

저장된 데이터는 총 100건 → 기본 키에 해당하는 학번 데이터 100건

어떤 학번을 선택하더라도 항상 하나의 유일한 값을 출력하는 0.01의 선택도를 가짐

즉, 학번은 0.01의 낮은 선택도를 가진다. 

 

/* 전체 데이터 건수 : 100 */
SELECT COUNT(*) FROM 학생;

/* 학번 데이터에서 중복을 제외한 개수 : 100 */
SELECT COUNT(DISTINCT 학번) FROM 학생;

/* 선택도 계산 : 0.01 */
SELECT 1 / COUNT(DISTINCT 학번) FROM 학생

 

성별 열에서 '여' 조건에 대한 선택도 (총 100건 중 여성 데이터 50건)

즉, 성별은 0.5의 상대적으로 높은 선택를 가진다.

성별 열의 선택도 = 50 ÷ 100 = 0.5

/* 전체 데이터 건수 : 100 */
SELECT COUNT(*) FROM 학생;

/* 성별 데이터에서 중복을 제외한 개수 : 2 */
SELECT COUNT(DISTINCT 성별) FROM 학생;

/* 선택도 계산 : 0.5 */
SELECT 1 / COUNT(DISTINCT 성별) FROM 학생;

---

카디널리티

하나의 데이터 유형으로 정이되는 데이터 행의 개수

= 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수

= 전체 행에 대한 특정 열의 중복 수치를 나타내는 지표

 

카디널리티 계산 공식

카디널리티 = 전체 데이터 건수 x 선택도

전체 데이터 100건에서 기본 키가 학번인 열을 대상으로 카디널리티를 계산한다고 가정

100 x 0.01 = 1건 → 모든 학번의 데이터 값이 고유한 만큼 1건의 데이터만 출력되리라 예측

 

MYSQL에서 계산하는 방식으로 카디널리티를 정의

→ 중복을 제외한 유일한 데이터 값의 수로 계산

특정 열에 중복된 값이 많다면 카디널리티가 낮음 : 해당 열을 조회하면 많은 데이터를 거르지 못한 채 대량의 데이터가 출력

 

※ 중복도 ↑ 카디널리티 ↓

   중복도 ↓ 카디널리티 ↑

 

TIP 일상생활에서의 카디널리티 적용 사례
- 주민등록번호, 휴대폰 번호, 계좌번호 : 카디널리티 높음 (중복 데이터 없음 - 유일한 값),
- 이름 : 카디널리티 중간 (중복 데이터 중간)
- 성별 카디널리티 낮음 (중복 데이터 많음)

---

힌트

데이터를 빨리 찾을 수 있게 추가 정보를 전달하는 것

 

힌트 사용하는 방법

- 예제 : 학생 테이블의 이름 열에 대한 학생_IDX01 인덱스와 전공코드 열에 대한 학생_IDX-2 인덱스

이름과 이름으로 학번과 전공 코드 데이터를 조회하는 SQL문

SELECT 학번, 전공코드
	FROM 학생
    WHERE 이름 = '유재석'

'유재석'이라는 이름의 학생 정보 가져오기 → 학생 열로 구성된 학생_IDX01 인덱스 이용 (처음부터 끝까지 X, 빠르게 찾을 수 있음)

 

 

인덱스를 사용하겠다는 힌트 USE INDEX 키워드 사용 (크게 두 가지)

 

1. /*! */ 형태의 주석처럼 힌트를 명시하는 방법

SELECT 학번, 전공코드
	FROM 학생 /*! USE INDEX (학생_IDX01) */
    WHERE 이름 = '유재석';

 

2. 주석 표기 없이 쿼리의 일부로 작성하는 방법

SELECT 학번, 전공코드
FROM 학생 USE INDEX (학생_IDX01)
    WHERE 이름 = '유재석';

 

MYSQL & MariaDB에서 실무에 자주 쓰이는 힌트

TIP 강력하지 않은 힌트
명시적으로 힌트를 작성해도 옵티마이저는 무조건 힌트를 참고하지 않음
옵티마이저가 비효율적이라고 예측하면 사용자가 작성한 힌트는 무시될 수 있음

TIP 힌트 사용시 고려사항
힌트가 적용된 서비스 환경에서는 데이터 건수가 수시로 급변할 수 있고, 테이블이나 인덱스/뷰 등에 변화가 생기면 SQL문 실행 시 오류가 발생할 가능성이 있음 → SQL문에 힌트를 작성하면 별도로 관리 필요

(첫째날)

SELECT *
	FROM 학생 USE INDEX(학생_IDX01)
    WHERE 이름 = ?;​

SQL문에 학생_IDX01이라는 인덱스를 사용할 것이라는 USE INDEX 힌트를 작성하는 쿼리

(둘째날)

ALTER TABLE 학생 DROP INDEX 학생_IDX01;​

담당자가 학생_IDX01 인덱스를 불필요한 인덱스라고 판단하고 삭제하는 쿼리

(둘째날 : 인덱스 삭제후)

SELECT *
	FROM 학생 USE INDEX(학생_IDX01)
    WHERE 이름 = ?;
    
ERROR 1176 (42000): Key '학생_IDX01' doesn't exist in table '학생'​

SQL문에 작성된 인덱스가 삭제되었으므로 오류 메세지 출력 → 운영 서비스상 에러 발생(서비스 장애)

MYSQL에서 작성된 힌트의 오브젝트가 존재하지 않으면 에러 발생, 이는 상용 DBMS인 오라클과는 다른 방식임(오라클의 경우 힌트가 부적절하게 작성되었거나 존재하지 않는 오브젝트를 명시하더라도 해당 힌트를 무시하고 SQL문 실행)

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 2장 SQL 튜닝 용어를 직관적으로 이해하기