4. 쿼리 튜닝의 매커니즘 이해하기 (3)

4-6. 액세스 조건 vs 필터 조건

❔ 원하는 데이터를 찾기 위한 조건문이 많을 경우, 각 조건문은 내부적으로 어떻게 처리될까?

Where절 조건의 유형

• 액세스 조건 (Access Condition)
• 필터 조건 (Filter Condition)

SELECT *
  FROM TAB
 WHERE ID = 1
   AND CODE = 'A'

참고 : TAB 테이블의 인덱스 - Table2_Index : TAB(ID)

1. 옵티마이저는 SQL문의 특정 조건문을 통해 디스크/스토리지에 있는 데이터를 가져옴
2. SQL을 실행하여 스토리지엔진에서 MySQL엔진으로 가져온 데이터를 필터조건에 의해 필터링
3. 필터조건에 의해 덜어낼 데이터가 없도록 작성하는 것도 SQL 튜닝의 포인트

SELECT emp_id, gender, first_name, last_name, hire_date
  FROM emp
 WHERE emp_id BETWEEN 50000 AND 60000 --필터 조건
   AND gender = 'F' --엑세스 조건
   AND last_name IN ('Kroft', 'Colorni') --엑세스 조건
   AND hire_date >= '1990-01-01' --필터 조건

• 실행 계획 수행 결과
  • type : range
  • key (사용된 인덱스) : I_GENDER_LAST_NAME

---

4-7. 정량적 지표(선택도, 카디널리티)

선택도 (Selectivity)

테이블의 특정 컬럼을 기준으로 where절에 작성한 조건문에 따른 선택된 레코드의 비율

• where절의 컬럼
  • 중복 데이터가 많다 👉 선택도가 높다
  • 중복 데이터가 적다/없다 👉 선택도가 낮다

ex) 학생테이블(총 100건)

학번(PK)	이름	성별
10001	홍길동	남
10002	신사임당	여
10003	이순신	남
10004	유관순	여
..	…	…

1. 학번(pk) 기준 선택도 = 1%
2. 성별(남 : 50 / 여: 50) 기준 선택도 = 50%

• 인덱스 생성 where절의 조건문으로 자주 사용하는 학번 컬럼과 성별 컬럼을 사용한 인덱스 생성

  1. 학번 + 성별 조합

     ALTER TABLE 학생
       ADD INDEX I_학번_성별 (학번, 성별);

  2. 성별 + 학번 조합

      ALTER TABLE 학생
        ADD INDEX I_성별_학번 (성별, 학번);

     → 인덱스 생성 시, 선택도가 낮은 컬럼을 먼저 사용하면 접근 범위를 줄여 더 효율적

카디널리티 (Cardinality)

테이블의 특정 컬럼에서 가질 수 있는 고유한 값의 개수

• 컬럼
  • 중복 데이터가 많다 👉 카디널리티가 높다
    → 인덱스 선두컬럼에 불리
  • 중복 데이터가 적다/없다 👉 카디널리티가 낮다
    → 인덱스 선두컬럼에 유리

학번(PK)	이름	성별
10001	홍길동	남
10002	신사임당	여
10003	이순신	남
10004	유관순	여
..	…	…

1. 학번(pk) 컬럼 카디널리티
   = 1/0.01 = 100건
2. 성별(남 : 50 / 여: 50) 컬럼 카디널리티
   = 1/0.5 = 2건

→ where 절의 조건문을 통해 access 범위를 줄일 수 있는 컬럼이 선두컬럼으로 위치

→ 많은 종류의 값으로 구성된 컬럼을 인덱스로 생성하는 것이 유리

선택도가 낮으면 카디널리티는 높다
선택도가 높으면 카디널리티는 낮다

---

4-8. 응용 용어(1)

힌트(Hint)

옵티마이저에게 빠른 경로를 알려주는 정보
→ 옵티마이저가 힌트를 무조건적으로 따르는 것은 아님

--학생_IDX01 : 이름을 기준으로 생성한 인덱스
SELECT 학번, 전공코드
  FROM 학생 /*! USE INDEX (학생_IDX01) */ 
 WHERE 이름 = '유재석';

• 힌트 종류

힌트	설명
STRAIGHT_JOIN	FROM 절에 나열된 테이블 순으로 조인을 유도하는 힌트
USE INDEX	특정 인덱스를 사용하도록 유도하는 힌트
FORCE INDEX	특정 인덱스를 사용하도록 강하게 유도하는 힌트
IGNORE INDEX	특정 인덱스를 사용하지 못하도록 유도하는 힌트

--예시
SELECT /*! STRAIGHT_JOIN */e.FIRST_NAME, e.LAST_NAME --먼저 emp 테이블에 접근
  FROM emp e, 
       manager m /*! USE INDEX (PRIMARY) */ --해당 테이블 접근시 pk 사용
 WHERE e.EMP_ID = m.EMP_ID

SELECT /*! STRAIGHT_JOIN */e.FIRST_NAME, e.LAST_NAME --먼저 emp 테이블에 접근
  FROM emp e, 
       manager m /*! IGNORE INDEX (I_DEPT_ID) */ --해당 인덱스 사용시 비효율적
 WHERE e.EMP_ID = m.EMP_ID

---

4-9. 응용 용어(2)

콜레이션(Collation)

데이터베이스에 저장된 문자값을 비교하거나 정렬하는 규칙

utf8mb3_bin	utf8mb3_general_ci
A	A
B	a
a	B
b	b

• utf8mb3_bin
  • a < A
  • a < B
• utf8mb3_general_ci
  • a < A
  • a > B

--1. 테이블 생성
CREATE TABLE coll_table ( 
	bin_col VARCHAR(10) NOT NULL,
	ci_col VARCHAR(10) NOT NULL COLLATE 'utf8mb3_general_ci'
)
COLLATE = 'utf8mb3_bin'
ENGINE=INNODB;

--2. 데이터 입력
INSERT INTO `tuning`, `coll_table` (`bin_col`, `ci_col`)
			 VALUE ('A', 'A'), ('B', 'a'), ('a','B'), ('b', 'b');
			 
--3. 결과 확인
SELECT * FROM coll_table
ORDER BY bin_col, ci_col;

통계정보

데이터베이스 오브젝트(테이블, 인덱스 등) 에 대한 특징을 수집한 정보

옵티마이저는 통계정보를 기반으로 sql을 실행

• mysql.innodb_table_stats
• mysql.innodb_index_stats