Real MySQL - 2

인덱스

• where절, order by절, group by절에도 원본 값을 검색하거나 정렬할때는 b-tree에 정렬된 인덱스를 사용한다.
• 반드시 인덱스된 칼럼의 값은 변환이 이루어지지 않고 그대로 사용되어야 한다.

  	select * from table where num * 10 > 10000; - x
  	select * from table where num > 10000 / 10; - o

• 인덱스를 사용하면 실행 계획에 ref or range라 뜨고 index라 뜨면 풀스캔
• where절에 인덱스를 사용하기 위해선 비교 타입이 같아야 한다.
  • age = varchar(10), where age = 20, 이러면 인덱스 적용 x

where절 인덱스 사용

• where절에 인덱스를 사용하는 방법은 크게 작업 범위 결정 조건, 체크 조건 두 가지 방식으로 구분
• 작업 범위 결정 조건은 where 절에서 동등 비교 조건이나 in 으로 구성된 조건에 사용된 컬럼들이 인덱스의 컬럼 구성과 좌측에서부터 얼마나 일치하는가에 따라 달라진다.
  • where절의 컬럼 순서가 인덱스와 다르더라도 옵티마이저는 인덱스를 사용할 수 있는 조건들을 뽑아 최적화를 수행

    ex) 
    인덱스 순서 a1 -> a2 -> a3 -> a4
    where절 순서 a2 -> a3 -> a4 -> a1
    a1, a2는 동등 비교조건, a3는 범위 비교 조건, 따라서 a4는 체크 조건
    이유는 a3가 범위 비교 조건이라 a4 체크 조건
    

• where절의 조건 절은 순서를 변경해도 결과의 차이가 없다.
• 중요한건 순서가 아닌 인덱스를 구성하는 컬럼에 대한 조건의 여부이다.
• and 연산자만을 사용했을 때는 위의 얘기가 성립
• or 연산자가 포함되면 약간 바뀐다.
  • where a = 1 or b = 2;
  • 만약 이렇게 되어있으면 a 조건은 인덱스를 적용할 수 있어도 b는 풀스캔 방식
  • 그래서 결국 옵티마이저는 풀스캔 방식으로 조회, 인덱스 + 풀스캔 보단 풀스캔이 빨라서
  • 각각에 인덱스가 설정되어 있으면 index_merge를 사용가능, 그래도 하나의 인덱스보다 느림
  • 따라서 where절에 or가 있다면 주의해야 한다.

Group by절 인덱스 사용

• 여러 개의 컬럼으로 구성된 다중 컬럼 인덱스를 가정, 단일 컬럼 인덱스도 가능
• group by절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같아야 한다.
• group by절에 명시된 컬럼이 하나라도 인덱스에 없으면 인덱스를 전혀 이용 x
• 인덱스 구성 컬럼 중 뒤쪽에 있는 컬럼은 group by절에 명시되지 않아도 사용 가능
• 인덱스 구성 컬럼 중 앞쪽에 있는 컬럼은 group by절에 명시되지 않으면 사용 불가능
• 만약 where절에서 동등 비교 조건으로 사용된다면 앞에 컬럼 없이도 뒤에 컬럼 만으로 그룹핑 가능
  • where a1 = 1, a2 = 2 group by a3, a4 -> o
  • where a1 = 1, a2 = 2 group by a1, a2, a3, a4 -> o
  • 위의 두개의 쿼리는 동일한 결과가 나온다.
  • 동일한 결과가 나오면 where절과 group by절이 모두 인덱스를 사용할 수 있는 쿼리

Order by절 인덱스 사용

• group by보다 하나의 조건만 더 있다.
• 정렬되는 각 컬럼의 asc, desc 옵션이 인덱스와 같거나 정반대인 경우에만 사용가능

where + order(group) by 절 인덱스 사용

• 동시에 같은 인덱스 사용
  • 성능이 가장 좋아서 가능하면 해당 방식으로
• where절만 인덱스 사용
  • where 절의 조건에 일치하는 레코드 건수가 많지 않을 때 효율
• order by 절만 인덱스 사용
  • 주로 아주 많은 레코드를 조회해서 정렬해야 할때 효율
• where a1 > 1 order by a1, a2, a3 -> o
• where a1 > 1 order by a2, a3, a4 -> x
• where a1 in (1,2,3,4) order by a2 -> x

order by + group by 절 인덱스 사용

• 하나의 인덱스를 사용해서 처리하려면 order by 절과 group by 절에 있는 모든 컬럼의 순서와 내용이 일치해야 한다.
• 둘 중 하나라도 인덱스를 이용할 수 없으면 둘다 이용 x

NULL 비교

• IS NULL을 주로 사용하자
• where num IS NULL; - 인덱스 적용 o
• where ISNULL(num); - 인덱스 적용 o
• where ISNULL(num) = 1; - 인덱스 적용 x
• where ISNULL(num) = true; - 인덱스 적용 x

Date or DateTime 문자열 비교

• 문자열 값을 자동으로 DATETIME 값으로 변환해서 비교를 수행하기 때문에 아래 쿼리들은 인덱스가 잘 적용된다.
  • where hire_date > str_to_date('2023-12-28','%Y-%m-%d')
  • where hire_date > '2023-12-28';
  • where DATE_FORMAT(hire_date, '%Y-%m-%d') > '2023-12-28'; - 애는 인덱스를 효율적으로 이용 x
• 가능하면 DATE나 DATETIME 타입 컬럼을 변경하는 것이 아니라 상수를 변경하자

쿼리를 작성함에 있어 복잡한 연산이나 다른 테이블의 레코드를 읽어야 하는 서브쿼리 조건 등은 where 절의 뒤쪽으로 배치하는 것이 성능상 도움이 될 것이다. 물론 인덱스를 사용할 수 있는 조건은 순서에 관계없이 가장 먼저 평가된다.

Distinct

• 남발하면 성능상의 문제도 있고 쿼리의 결과가 의도한 바와 달라질 수 있다.

LIMIT

• 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료한다.
• Group by나 Order by 같이 전체 범위 작업이 선행 되더라도 크진 않지만 나름의 성능 향상이 있을 수 있다.
• 인자를 1개 또는 2개 사용할 수 있다.
• 1개의 경우에는 상위 n개의 코드를 가져온다.
• 2개인 경우 첫번째 인자는 (시작 위치, 오프셋) 0부터 시작된다.
• LIMIT 0, 10는 LIMIT 10과 똑같다.
• LIMIT 10, 10은 상위 11번째부터 10개 레코드 가져온다.
• LIMIT 20000000, 10는 앞에 20000000건은 버리고 10개를 가져오기 때문에 쿼리가 느리다.

COUNT()

• count(1), count(*)은 레코드 그 자체를 가져오라는 것이다.
• 두 개의 성능 차이는 없다.
• where 조건이 있는 count(*) 쿼리는 그 조건에 일치하는 레코드를 읽어보지 ㅇ낳는 이상 알 수 없으므로 큰 테이블에서 count()함수 사용은 주의해야 한다.

DRIVING TABLE

• JOIN시 먼저 엑세스 되어 Access path(접근 경로)를 주도하는 테이블을 드라이빙 테이블(TRIVING TABLE, OUTER TABLE)이라고 한다.

DRIVEN TABLE

• DRIVEN TABLE 엑섹스 된 후 나중에 액세스 되는 테이블을 드리븐 테이블(DRIVEN TABLE, INNER TABLE)이라고 한다.

• 인덱스(INDEX)의 존재 및 우선순위 혹은 FROM절에서의 TABLE 지정 순서에 영향을 받으며, 어느 테이블이 먼저 엑세스 되느냐에 따라 속도의 차이가 크게 날 수 있으므로 많은 양의 데이터를 다룰 때, 드라이빙 테이블은 매우 중요하다.

예를 들어
조건을 만족하는 5000만 건인 A 테이블과 조건을 만족하는 1000건인 B 테이블과 조인 시 드라이빙 순서에 따라 속도의 확연한 차이가 있다.
5000만 건을 먼저 드라이빙 하면 5000만 번을 반복하여 B 테이블을 탐색하며
1000건인 B가 먼저 드라이빙 되면최대 1000번 A 테이블 탐색이 반복된다.
즉, 작업 대상이 되는 행(rows)의 수가 적은 테이블부터 액세스 되어야 전체 탐색이 줄어든다.