정리하게 된 계기

오늘 DB튜닝을 하다가 실행계획을 보았는데, 제가 알고 있던것과 다른점이 있어

믿보 블로그(기억보단 기록을)의 내용과 이번에 쓰면서 알게 된 점을 정리해봤습니다.

그 내용은...

여러개의 컬럼으로 이루어진 복합 인덱스의 컬럼 순서를 지키지 않았는데 인덱스를 타버리네?! 

Index1 : A,B,D
Index2 : C,D
쿼리1 : where A = '조건' and D = '조건' and  B = '조건'
쿼리2 : where C = '조건' and D = 조건''

쿼리 2는 Index2를 타고, 쿼리 1은 Index1과 컬럼 순서가 달라서 당연히 오래걸리겠거니 했는데, 제가 잘못 알고 있었어요.

위처럼 and 조건의 순서가 실제 인덱스와 꼭 같지 않더라도, 실행계획이나, 실제 수행시간이 똑같이 나왔습니다.

컬럼 순서를 지켜야, 인덱스를 탄다고 알고 있었는데, 민망하니까 후딱 찾아봤습니다.

인덱스 생성시

○ 카디널리티(중복을 제외하는 수치) 높은 컬럼 사용
○ 인덱스의 생성시 컬럼 순서는 카디널리티가 높음 -> 낮음 순서가 성능이 좋음

인덱스 사용 조회시

○ 인덱스가 여러 컬럼인 경우, 몇몇 조건 생략 가능
(인덱스 컬럼 앞에서 부터 포함하는게 좋음, index-컬럼1,2,3,4 중 3,4 생략가능)
○ 범위 조건 (Between, like, <,>)은 해당 컬럼까지만 인덱스를 타고, 그 뒤로는 인덱스를 타지 않음
○ In, =는 다음 컬럼도 인덱스 사용 (in은 =의 멀티)
○ Or 보다는 and가 성능상 좋음 (or는 row가 늘어나서 풀스캔 가능)
○ 인덱스의 컬럼을 그대로 사용해야 함 (연산 처리를 하지 않고)
○ 예전처럼 인덱스와 컬럼 순서를 반드시 지키지 않아도 인덱스 사용 가능 (컬럼 재배치 과정이 추가 되지만 거의 같음)

출처 - 기억보단 기록을

실제 사용하면서, 느낀점

• 시간/날짜를 범위로 많이 검색하는 경우
  인덱스 생성시 시간 컬럼을 뒤쪽에 배치하는게 유리 (인덱스의 최대한 많은 컬럼에 대해 인덱스 사용)
• 정확한 시간/날짜로 많이 검색하는 경우
  인덱스 생성시 시간 컬럼을 앞쪽에 배치하는게 유리 (시간으로 필터링 = 카디널리티 높음)
• or , !=, not in 은 비효율적
  or는 row수가 늘어나고, not 이 붙는 연산자는 결국 full-scan을 타게 되는 경우가 대부분이라, 되도록 지양하는게 성능상 유리
• 인덱스의 컬럼 순서를 지키지 않아도 인덱스 사용 가능 (그래도 이왕이면 지키자)

상황

 월별로 데이터가 가장 많이 들어온 날짜를 구하는 쿼리가 필요

방법

Group by & order by & 서브쿼리를 활용
(key = group by시 가장 상위의 데이터로 그룹핑 되므로, order by를 사용하면 내가 원하는 데이터를 위로 올릴수있다)

아이디어

• 그룹핑 (group by yyyymmdd) + 정렬(order by yyyymm), count(데이터수)

• 서브쿼리2 : 위의 내용을  월별로 그룹핑

• 그룹핑 (Group by yyyymm)

• 그룹함수를 이용한 결과 출력

기존 쿼리

select
    max(bill_dt.readDt) readDt,
    max(bill_dt.oneMonthAgo) oneMonthAgo,
    max(bill_dt.twoMonthAgo) twoMonthAgo,
    max(bill_dt.threeMonthAgo) threeMonthAgo
from (
    select
        case
            when substring(READ_DT,1,6) = date_format(concat( ${read_dt},'01'), '%Y%m') then READ_DT
        end as 'readDt',
        case
            when substring(READ_DT,1,6) = date_format(date_add(concat(${read_dt},'01'),INTERVAL -1 Month), '%Y%m') then READ_DT
        end as 'oneMonthAgo',
        case
            when substring(READ_DT,1,6) = date_format(date_add(concat(${read_dt},'01'),INTERVAL -2 Month), '%Y%m') then READ_DT
        end as 'twoMonthAgo',
        case
            when substring(READ_DT,1,6) = date_format(date_add(concat(${read_dt},'01'),INTERVAL -3 Month), '%Y%m') then READ_DT
        end as 'threeMonthAgo'
    from gnd_meter_billing gmb
    where 조건 1
    and 기간 between 기간1 and 기간2
    group by substring(READ_DT,1,6), substring(READ_DT,7,2)
    order by cnt_readDt desc
    ) bill_dt

수정 쿼리

select
    max(bill_dt.readDt) readDt,
    max(bill_dt.oneMonthAgo) oneMonthAgo,
    max(bill_dt.twoMonthAgo) twoMonthAgo,
    max(bill_dt.threeMonthAgo) threeMonthAgo
from (
    select *
    from (
        select
            count(READ_DT) cnt_readDt,
            substring(READ_DT,1,6) yyyymm, 
            case
                when substring(READ_DT,1,6) = date_format(concat( ${read_dt},'01'), '%Y%m') then READ_DT
            end as 'readDt',
            case
                when substring(READ_DT,1,6) = date_format(date_add(concat(${read_dt},'01'),INTERVAL -1 Month), '%Y%m') then READ_DT
            end as 'oneMonthAgo',
            case
                when substring(READ_DT,1,6) = date_format(date_add(concat(${read_dt},'01'),INTERVAL -2 Month), '%Y%m') then READ_DT
            end as 'twoMonthAgo',
            case
                when substring(READ_DT,1,6) = date_format(date_add(concat(${read_dt},'01'),INTERVAL -3 Month), '%Y%m') then READ_DT
            end as 'threeMonthAgo'
        from gnd_meter_billing gmb
        where 조건 1
        and 기간 between 기간1 and 기간2
        group by substring(READ_DT,1,8)
        order by substring(READ_DT,1,6), cnt_readDt desc
        ) bill_dt_by_yyyymmdd
        group by bill_dt_by_yyyymmdd.yyyymm
    ) bill_dt

MariaDB 쿼리

: 범위 별로 개수 구하기

2가지 방법 (sum & case 사용 방법과 count & case 사용방법)

-- 쿼리1 - sum / case  사용
select
    sum(case when usage < 1 then 1 end) '1',
    sum(case when 1 <= usage and usage < 10 then 1 end) '10',
    sum(case when 10 <= usage and usage < 100  then 1 end) '100',
    sum(case when usage > 100 then 1  end) '100+'
from
    TABLE

-- 쿼리2 - count / case 사용
select
    COUNT(case when age < 10 then 1 end) as '아이',
    COUNT(case when 10 < age and age < 20 then 1 end) as '10대',
    COUNT(case when 20 < age and age < 30 then 1 end) as '20대'
from
    TABLE

1줄 요약

카운트만 필요 할 때는 COUNT(*)가 가장 빠르다

COUNT(*) >  COUNT(컬럼) > COUNT(DISTINCT(컬럼))

 출처 : https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=77484 

본문

누구나 다 알고 있는 지식이긴 하지만 모두들 적게나마 공유를 하고자 글을 남기도록 하겠습니다.

중복 내용이면 죄송합니다. 그럼 시작하겠습니다.

일반 SQL 질의의 SELECT 절에의 *는 모든 컬럼을 가지고 오기 때문에

필요한 컬럼만 가지고 오는 SELECT 절에 비해 속도가 느리고 불필요한 자원을

낭비한다는 사실은 모두가 알고 있을 것입니다.

그렇기 때문에 이러한 것을 똑같이 생각함으로 인해 집계함수의 COUNT에 대한 COUNT(*) 와

COUNT(컬럼)에 대한 잘못된 인식과 SQL 질의를 실행하여 DB의속도를 느리게 할 수 있는 원인이 될 수 있습니다.

일반 SQL질의와 다르게 COUNT 집계함수에서의 *의 역할은 다른 역할을 수행하고 있습니다.

* MySQL COUNT 함수의 역할을 보면

COUNT(*) : 단순 행을 세는 역할을 합니다. (MySQL 내부적으로 데이터를 읽지않고 행의 갯수만 흝고 지나간다는 뜻을 말합니다.)

COUNT(컬럼) : 행의 값을 세는 역할을 합니다.(데이터를 읽는다는 뜻이 되겠지요)

자 그럼, 컬럼이 VARCHAR(50) 형태의 데이터가 100만건이 있다는 가정하에 실행계획 및 질의를 실행에 보도록 하겠습니다.

* 실행계획

1) COUNT(*) : 우선 단순 행을 세는 COUNT(*)의 실행계획을 보도록 하겠습니다.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Select tables optimized away

Select tables optimized away : 인덱스, 또는 MyISAM용 COUNT(*)을 사용하되 GROUP BY 구문은 사용하지 않은 채로 처리된 집단 함수(MIN(), MAX())만을 가지고 있음.

2) COUNT(컬럼) : 데이터를 읽고 지나가는 COUNT(컬럼)의 실행계획을 보도록 하겠습니다.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE performance1 ALL 1000000

실행계획을 보면 알듯이 type을 보면 알 수 있듯이 ALL로 전체를 스캔하는 것을 알 수 있수 있을 것입니다.

3) COUNT(DISTINCT(컬럼)): DISTINCT에 데이터를 읽고 지나가는 COUNT(DISTINCT(컬럼))의 실행계획을 보도록 하겠습니다.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE performance1 ALL 1000000

COUNT(DISTINCT(컬럼))도 type을 보면 알 수 있듯이 ALL로 전체를 스캔하는 것을 알 수 있을 것입니다.


실행계획을 봤으니 SQL질의를 실행하여 실행시간을 비교해 보도록 하겠습니다.

* 실행결과를 종합해 보면

1) COUNT(*) : 0:00:00.029
2) COUNT(컬럼) : 0:00:00.201
3) COUNT(DISTINCT(컬럼)) : 0:00:02.413

COUNT(DISTINCT(컬럼))  < COUNT(컬럼) < COUNT(*) 순입니다.

그렇습니다. 불필요한 데이터를 셀 필요없이 행의 갯수만 얻고 싶다면 당연히 * 쓰는게 훨씬 속도

가 빠르다는 사실을 알 수 있을 것을 것입니다.

그러면 '혹시 컬럼에 인덱스를 걸어주면 데이터를 세더라도 count(*)보다 빠르지 않을까?' 라는 생각이 문득 들어서

컬럼 하나에 인덱스를 걸고 인덱스를 타게 해서 질의를 실행하여 비교해 보도록 하였습니다.

* 실행계획

1) COUNT(*)

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Select tables optimized away

2) COUNT(컬럼)

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE performance2 index index 153 1000000 Using index

3) COUNT(DISTINCT(컬럼))

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE performance2 index index 153 1000000 Using index


* 실행결과를 다시 종합해보면

1) COUNT(*) : 0:00:00.054
2) COUNT(컬럼) : 0:00:00.592
3) COUNT(DISTINCT(컬럼)) : 0:00:02.955

그렇습니다. 실행결과를 보면 인덱스 거는거에 상관없이 COUNT(*)이 가장 빠르게 질의를 수행한다는 것을 알 수 있

을 것입니다. 누구나 다 아는 사실이지만, 참고가 될 수 있도록 글을 기재합니다.

[ 실행환경 ]

MySQL Version : 5.1.41-community

테이블 형태 : MyISAM

컬럼 형태 : varchar(50)

문의 내용이 있으시면 쪽지로 주시면 감사하겠습니다.

끝까지 읽어주셔서 감사합니다.

오라클에서 특정 날짜 사이가 며칠인 구하는 방법

1. Contract 테이블의 StartTime과 오늘 사이에 며칠이 지났는지 확인

SELECT
	TRUNC(SYSDATE - TO_DATE(StartTime,'YYYYMMDDHHMISS'))
FROM
	CONTRACT

2. Contract 테이블의 Duration에 지정한 날짜가 얼마나 남았는지 확인

(ex. Duration:30일, StartTime이 7일전 => 유효기간은 23일 남음 )

SELECT
	Duration - TRUNC(SYSDATE - TO_DATE(StartTime,'YYYYMMDDHHMISS')) Remaining_Days
FROM
	CONTRACT

데이터가 많아질수록 해당 데이터를 검색해서 출력해주는 퍼포먼스가 중요합니다.

같은 내용을 가져오더라도, 쿼리를 어떻게 작성했는지에 따라 쿼리의 성능이 차이가 생기겠죠.

데이터가 많아질수록 그 영향은 더 커질 겁니다.

그렇다면 쿼리를 작성할 때 기본적으로 고려하면 좋은 점들을 쿼리 초보자 입장에서 정리해보았습니다.

1. Select * 보다는 필요한 컬럼만 불러온다
   => 서비스에서 나중에 쓰일 것 같은 컬럼을 일단 가져오고 서비스에서는 쓰지 않는 경우가 종종 있습니다.
   => 하지만, 모든 컬럼을 조회하면 그만큼 DB에 부담을 주기 때문에 필요한 컬럼만 가져오는 게 성능에 좋습니다.
2. Index를 타고 있는지 확인하자
   => 정의된 index가 있는지, 있다면 index를 탈수 있도록 where절의 조건과 순서를 조정해서, index를 활용하는 편이 당연히 성능이 좋겠죠.
3. 쿼리의 실행 순서를 기억하자.
   => From, On, Join, Where, Group, Having, Select, Order by 순서대로 실행됩니다.
   => 따라서 From, On, Join, Where, Having 순으로 Query가 확장될 때, 해당하는 대상을 줄여줄 수 있도록 작성해야 합니다.
   => 같은 조건이라도, having에 적용할 때보다, where절에서 필요한 만큼만 가져오는 게 성능이 더 좋다고 합니다.
   그리고, 쿼리의 실행 순서가 ALIAS 순서이니까 알아두는 게 좋겠죠,
   (From 절에서 alias 하면 where절에서 사용 가능, Select에서 alias하면 where 절에서 사용 불가능)
4. Join 되는 결과 테이블의 크기를 줄여야 한다.
   => JOIN시 on절에 의해 추려지는 데이터는 메모리에서 관리하게 되므로, on으로 추릴 때 이왕이면 데이터의 중복이나, 양을 최대한 줄일 수 있도록 설정해야 성능에 도움이 된다고 합니다.
   =>그래서 Left outer join 보다는 inner join을 사용하자
   같은 데이터를 뽑아낼 거면 Left outer join으로 null로 join 되는 걸 가져온 후 다시 제외하는 것보다,
   한 번에 inner join을 사용하는 게 성능상 좋겠죠.
5. Where절에서 데이터 타입을 일치해야 한다.
   => Where id = '1'과 Where id = 1 이 있는데, id의 데이터 타입이 문자형이라면 인덱스를 활용할 수 있으므로 더 성능이 좋다고 합니다.
6. Distinct는 자제하자
   =>  Distinct는 정렬에 따른 성능에 하락이 발생하기 때문에 필요한 경우에만 사용하는 게 좋다고 합니다.
7. Union 보다 Union all을 사용하자
   => Union 은 중복 검사를 하기 때문에 성능 차이가 꽤 크게 납니다... 참고!

다시 한번 정리하면서 느낀거지만..

DB도 결국 프로세스 대로 실행이 되기 때문에.

필요한 만큼만, 최대한 사이즈를 줄여서 가져오는게 성능상 좋은것 같네요.

이상입니다 :)