하나의 테이블에 모든 로우의 개수를 알고 싶을때 보통 아래와 같이 작성을 많이 한다.
SELECT COUNT(*) FROM TestTable;
뭐 저렇게 사용해도 상관은 없지만 속도측면에서 좀 더 나은 방법이 있어서 소개 한다.
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TestTable') AND indid < 2;
대충 보면 이해가 될 것이니 설명은 패스한다.
속도차이는 COUNT를 하려는 테이블의 총 로우 개수가 100개 미만이라면 COUNT가 더 빠르다.
100개 이상이라면 위에 적어놓은 쿼리가 훨씬 빠르다.
속도 차이가 나는 이유는 COUNT 키워드를 실행하게 되면 table scan을 하게되지만
대체 쿼리에서는 해당 rows값을 가져오기 때문에 더 빠르다.
물론 내가 테스트해본거니 확인은 다들 해보고 적용하기 바란다.
* 해당 쿼리는 테이블의 전체 로우 개수를 가져올때만 유효하다.
- 속도개선 : 1303ms → 23ms
-- 결과 : 세미조인을 이용하기 위한 쿼리 형태 변경과 인덱스 대상 컬럼 변형 되지 않도록 변경하여
적합한 인덱스와 억세스패스를 이용할 수 있도록 함.
억세스 수를 132만건에서 2천건으로 1% 미만이 되게 함으로써
총 수행시간을 1300ms
-> 50ms로 98% 줄임
* 튜닝 포인트
1. convert(varchar(10),o.결제일시,121)<'2021-07-02'
- 인덱스 대상이 될 수도 있는 컬럼을 변형하여 필터링하는 구문
개발자분들이 아주 많이 실수하는 구문으로 결제일시 컬럼에 인덱스가 있더라도
해당 인덱스를 활용할 수 없게 만드는 구문
보통은 특정일자에서 몇일 더하거나 빼는 과정에서 발생하는데
이 쿼리는 아예 그럴 필요가 없음에도 사용됨.
2. exists를 통한 세미 조인
- 원본 쿼리를 보면 특정 배송일자 코드에 배송된 고객들을 추출하여
해당 배송일 이전에 결제를 한적이 있는 고객들의 주문을 전체를 조인하는것을 볼 수 있다.
이후 그룹핑을 해서 카운트를 하는것을 보면
결국 해당 배송일 이전에 결제를 몇번 했는지는 무의미하고 결제를 한적이 있느냐 없느냐만
구분할 수 있으면 된다.
exists나 not exists를 통한 세미조인을 모를 경우 보통 작성하게 되는 방식으로
주문 테이블의 데이터가 적을 경우는 별차이가 없겠지만 1년, 2년 증가하면서
극악으로 치닫게 된다. 해당 테이블의 경우 무려 10년치 데이터.
보통 첫주문 고객인지 여부를 찾는 쿼리를 작성하다가 많이 발생한다.
3. having count(o.userid)>0
- 이 부분은 2번을 통해 자연스럽게 제거되긴 하지만
원본쿼리에서도 전혀 불필요한 부분으로 아마 옵티마이저가 알아서 걸렀을 가능성이 높다.
o.userid는 조인이 되면서 null이 발생할 수가 없기 때문에(이미 not null 컬럼이기도함.)
count(o.userid)의 결과는 0이상일 수 밖에 없으므로 제거되어야할 구문이다.
*
제안사항
- 해당 쿼리의 목적은 해당 날짜 이전에 결제된 적이 있는 배송 고객수를 추출하는 것 인듯 한데
실시간성으로 꼭 필요한게 아니라면 1시간 또는 1일 주기로 반정규화한 형태 데이터를 생성해놓고
생성된 통계용 테이블을 조회하는것이 올바름
1. 생성
(1) DB 생성시
- DB명칭은 해당 서비스를 파악할 수 있도록 명명한다.
(2) USER 생성시
- USER ID는 유관 서비스를 파악할 수 있도록 명명한다.
- Password는 운용팀 DBA의 생성규칙을 따른다.
(3) TABLE 생성시
- (table 길이 + column 길이)가 8K를 넘지 않도록 한다.
- table 및 column 이름은 일관성 있게 준다. (EX. TBL_OOO, TN_OOO)
- PK / FK column은 고정길이 형식을 사용한다. (EX. CHAR TYPE)
- Trigger 사용은 자제한다.
- table 소유자는 항상 DBO가 되도록 한다.
(4) DATA TYPE 정의시
- 컬럼에 필요한 데이터를 저장할 수 있는 데이터타입 중 항상 가장 작은 데이터타입을 선택한다.
- 컬럼에 저장되는 텍스트 데이터의 길이가 매우 가변적이라면 VARCHAR 타입을 사용하는것이 좋다.
- 16비트문자 데이터를 저장할 계획이 아니라면 NVARCHAR, NCHAR 데이터 타입을 사용하지 않는 것이 좋다.
- 긴 문자열 저장할 때, 문자열 길이가 8000자 이하라면 TEXT대신 VARCHAR 데이터타입을 사용하는 것이 좋다.
- 숫자만을 저장하는 컬럼은 VARCHAR, CHAR 대신 INTEGER와 같은 숫자데이터 타입을 사용하는 것이 좋다.
(5) 인덱스 생성시
- WHERE절에서 많이 사용하는 경우 생성한다.
- Covered Index인경우 선택도가 좋은 조건(10%이하)부터 순서대로 생성한다.
- 구간별 선택이 많은 컬럼인 경우 클러스터 인덱스 추천
- PK정의시 non-Cluster index로 정의하되 구간별 선택이 많은 컬럼인 경우 클러스터 인덱스로 생성한다.
2. Query 작성시
(1) 테이블의 모든 컬럼이 아닌 필요 컬럼의 레코드만 반환한다.
- select * 를 사용하는 것은 피한다.
- 사용하지않는 데이터를 호출하는 것만으로도 많은 부하가 걸린다.
- 특히 text 타입 데이터 호출시에 그 정도가 심해진다.
- data type의 byte가 적은 column을 주로 사용하는 것이 좋다.
-- 잘못된 예 SELECT * FROM tbl_member WEHRE f_idx = 101 --올바른 예 SELECT f_idx, f_Name, f_nickname, f_age, f_gender FROM tbl_member WHERE f_idx = 101(2) 테이블 전체 Row 수를 알고싶다면 sys.sysindexes table의 rows 컬럼을 이용한다.
- count(특정 column)으로 호출하는 경우가 있다. 이 경우 해당 컬럼의 null 값을 제외한 count를 가져오게 된다.
- null값을 일일이 체크하면 호출 속도가 저하된다. null을 체크하는 경우가 아닌 대부분 경우 count(*)를 사용한다.
- count(*)는 null값의 경우도 모두 count에 추가하여 계산하므로 그로 인한 성능의 저하가 많이 개선된다.
- 그러나 SELECT count(*)의 경우도 테이블을 스캔해서 전체 Row 수를 반환하기 때문에 큰 테이블에서는 시간이 오래 걸린다. 이 경우에는 sysindexes 시스템 테이블을 사용한다. 이 테이블의 컬럼은 각 테이블의 총 Row 수를 값으로 가지고 있다.
-- 잘못된 예 SELECT count(f_nickname) FROM tbl_member SELECT count(*) FROM tbl_member -- 올바른 예 SELECT f_nickname FROM sys.sysindexes WHERE id OBJECT_ID('tbl_member') AND indid < 2;(3) 단순 SELECT면 WITH NOLOCK 옵션을 사용한다.
-- 잘못된 예 SELECT f_nickname FROM tbl_member -- 올바른 예 SELECT f_nickname FROM tbl_member WITH(NOLOCK)(4) WHERE절을 사용하여 쿼리 결과셋을 제한한다.
- 성능에 가장 영향을 미치는 것으로 클라이언트에 모든 결과가 아니라 꼭 필요한 결과만 반환하도록 한다. 이렇게 하면 쓸모없는 네트워크 트래픽을 감소시킬 수 있으며 쿼리 성능 또한 향상된다.
(5) WHERE 조건문의 왼쪽은 되도록 변형되지않은 순수한 column만을 선언한다.
- WHERE name + " = 조건 " 과 같이 좌항은 변형하지 않고 우항에 조건을 선언한다.
- 조건 일치를 매 Row마다 확인할 떄 좌항을 변형하게 되며 그만큼 부하도 눈에 띄게 증가한다.
(6) WHERE 조건이 최적인지 확인한다.
- WHERE 조건이나 table join에 쓰이는 컬럼이 인덱스로 걸려있도록 해야 join 성능이나 필터링이 좋다.
- 여러 필터링 중 예상되는 결과 레코드 수가 적은 것부터 WHERE조건절에 사용한다.(SQL server 2000부터는 SQL 실행계획이 자동으로 수정하여 실행해줌)
- 여러번의 쿼리를 통해 임시 테이블을 만들고, 조작해야할 때는 SQL 작성자가 유의해서 작성한다.
(7) 가능한 HAVING절의 사용을 피한다.
- HAVING절은 GROUP BY에 의한 결과를 제한할 때 사용한다. GROUP BY에 HAVING절을 사용하였을 경우 GROUP BY에 의해서 결과들을 모두 집계한 다음 HAVING절에 명시한 조건으로 맞지 않는 결과를 버리게 된다. 대부분의 경우 HAVING절의 필요없이 GROUP BY와 WHERE절 만으로 원하는 결과를 얻을 수 있다.
(8) 가능한 DISTINCT문의 사용을 피한다.
- SORT에 따른 성능 하락이 있기 때문에 꼭 필요한 경우에만 사용한다.
(9) 특정 레코드 존재 유무를 파악할때 COUNT를 세지말고 EXISTS를 사용한다.
- COUNT는 모든 레코드 중 관련된 것을 필터링한 후 COUNT 함수를 수행하지만, EXISTS는 필터링 시 하나라도 레코드가 있음을 인지했을 때 반환한다. 테이블의 전체 레코드 수가 적을 때는 구별이 안되지만 많을 때는 EXISTS가 효과적이다.
-- 잘못된 예 SET @v_count = (SELECT count(*) FROM tbl_member WHERE f_age = 20) IF @v_count > 0 BEGIN END -- 올바른 예 IF EXISTS(SELECT f_idx FROM tbl_member WHERE f_age = 20) BEGIN END(10) 처음 몇개의 Row만 필요하다면 TOP 또는 SET ROWCOUNT문을 사용한다.
- 결과 전체가 아닌 일부만 반환하므로 네트워크 트래픽을 감소시킬 수 있다.
(11) 몇개 Row의 빠른 반환이 필요하다면 FAST number_rows 힌트를 사용한다.
- 이를 사용하면 N개의 Row를 빠르게 얻을 수 있으며 이후 쿼리는 계속 실행되서 전체 결과를 만들어낸다.
SELECT f_idx, f_nickname, f_age, f_gener, f_joindate FROM tbl_member WHTH (NOLOCK) WHERE f_age = 20 OPTION (FAST 100)(12) JOIN을 사용하는 경우 INNER JOIN을 되도록 사용하라.
- 동일한 효과를 가지는 쿼리를 작성할 경우 INNER JOIN이 아닌 LEFT OUTER JOIN을 습관적으로 사용하는 경우가 있다. 두개의 조인방식은 확연한 속도 차이가 나므로 되도록이면 INNER JOIN을 사용하는 것이 좋다.
-- 잘못된 예 SELECT a.f_idx, a.f_name, a.f_nickname, a.f_age, a.f_gender, a.f_joindate, b.f_address FROM tbl_member AS a LEFT OUTER JOIN tbl_memberdesc AS b ON a.f_idx = b.f_idx WHERE a.f_age > 19 AND a.f_age < 30 -- 올바른 예 SELECT a.f_idx, a.f_name, a.f_nickname, a.f_age, a.f_gender, a.f_joindate, b.f_address FROM tbl_member AS a INNER JOIN tbl_memberdesc AS b ON a.f_idx = b.f_idx WHERE a.f_age > 19 AND a.f_age < 30(13) 서브쿼리의 사용시 불필요한 SELECT 구문을 줄인다.
- SELECT가 해당 Row를 호출할 때마다 서브 쿼리에 있는 Address를 구하는 쿼리를 호출하기 때문이다. 출력하는 Row가 많으면 많을수록 서브 쿼리의 실행 횟수 또한 증가하게 되며 불필요한 부하를 가져온다.
(14) 가능한 UNION 대신 UNION ALL을 사용한다.
- UNION ALL은 Row의 중복검사를 하지 않는 반면, UNION은 중복행 존재 유무와 관계없이 중복검사를 수행한다.
(15) VIEW 사용은 자제한다.
- 개발 편의상, 보안상 여러가지 이유로 VIEW는 좋은 개념이고 사용할만한 가치가 있다. 다만 성능상의 문제 때문에 무분별한 사용은 자제해야한다.
- (5)번에서 언급한 바와 같이 결과 레코드가 가장 적은 (HIT RATIO가 높은) 문장이 먼저 실행되어야하는데, VIEW를 먼저 가져오는 작업이 그 뒤에 실행되는 WHERE조건절보다 더 많은 결과를 가져오게 하는 것이 대부분이므로, 성능에는 불리할 수 밖에 없다.
- 또한 여러개의 테이블이 조인되는 경우 인덱스 사용에 제약이 많다. 물론 INDEXED VIEW가 있어서 어느정도 성능에는 효과가 있으나, 관련 테이블의 수정이 발생할 경우 문제가 생길 수 있다.
(16) 커서 및 임시 테이블의 사용을 최대한 자제한다.
- 커서 보다는 임시 테이블을 사용하는 것이 좋고, 임시 테이블보다는 테이블 변수를 사용하는 것이 성능에 좋다.
- 커서의 경우 내부적으로는 임시테이블을 사용하지만 임시테이블을 쓴다고 부하가 더 발생하는 것은 아니다. 오히려 커서의 부가적 기능으로 서버 자원을 더 낭비하게 된다.
- 커서로 처리할 수 있는 것은 모두 임시 테이블이나 테이블 변수로도 처리가 가능하므로 되도록 커서를 쓰지 않는다.
- 만약 커서를 반드시 사용해야 한다면 클라이언트 측 커서를 사용하고, 서버의 커서를 사용할 때에는 가능한 작은 결과 셋을 가져오도록 한다.
- 커서를 다 사용한 후에는 그냥 닫는 것(close)이 아니라 반드시 해제(deallocate) 시켜야한다.
(17) 가능한 트리거 대신 제약조건을 사용한다.
- 제약조건은 트리거보다 훨씬 성능면에서 효율적이다.
(18) table hints를 사용한다.
- IGNORE_CONSTRAINT, IGNORE_TRIGGERS, NOWAIT, PAGELOCK, TABLELOCK, ROWLOCK, UPDLOCK, XLOCK, FASTFIRSTROW 등 여러가지 힌트가 존재하며 일반 쿼리문에 적절한 table hints를 사용해서 성능을 높힌다.
(19) 저장 프로시저를 사용한다.
- 저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안문제를 해결해주며 더 나아가 빠른 성능의 매개변수, 출력 매개변수, 리턴 값을 사용할 수 있다.
- 저장 프로시저는 실행 계획이 Plan 캐쉬에 캐싱된다. 즉, 한번 컴파일되면 이것이 캐쉬에 저장되고 재사용이 되면 될수록 재컴파일이 필요없기에 효율이 올라간다.
(20) SET NOCOUNT ON/OFF를 사용한다.
- 저장 프로시저의 경우 결과 셋으로 보내는게 아니라, 내부적으로 중간 단계에서 사용하기 위해 조회를 수행하는 경우가 많다. 이 때 SET NOCOUNT ON을 지정하고 수행해야 Client에게 조회 결과가 전달되지 않는다. 최종 결과만을 Client에게 보내기 위해서 SET NOCOUNT OFF를 설정한 후 최종 결과 조회문을 실행해야한다. 이렇게 하는 것이 쓸데없는 정보로 인한 네트워크 트래픽 증가를 방지할 수 있다.
(21) 되도록 한꺼번에 SQL 문장을 실행한다.
- Connection Pool 이 있어서 Connection Resource에 의한 성능 저하는 별로 없겠지만, 계속된 Network Round-Trip은 성능에 많은 영향을 미친다. 되도록 한꺼번에 요청하고 반환받아 Round-Trip을 최소화 해야한다.
(22) 다른 서버에서 정보를 가져올 경우 연결된 서버를 이용할 때, 4-part name 방식 말고 OPENQUERY를 사용한다.
① 4-part name 방식
- 성능이 나쁘고 자주 사용하지 않는다.
- 쿼리 작성이 간단하다.
- INSERT, UPDATE, DELETE 문장을 보통 쿼리문과 비슷하게 사용한다.
② OPENQUERY 방식
- 성능이 나쁘지 않고, 자주 사용한다.
- 쿼리를 문자열로 조합하기 복잡하다.
- OPENQUERY 내의 문자열은 완성된 형태의 문자열만 가능하며, 변수는 받을 수 없다.
-- 잘못된 예 SELECT count(*) FROM [Linked Server Name].[Database Name].[Owner Name].[Table Name] -- 올바른 예 SELECT * FROM OPENQUERY([Linked Server Name], 'SELECT count(*) FROM [Database Name].[Owner Name].[Table Name]')3. Tunning
(1) MSSQL을 사용하는 경우 예상 실행 계획을 자주 확인한다.
- MSSQL은 쿼리분석기에서 쿼리를 테스트하기 편하다. 좋은 기능 중 하나가 예상 실행계획인데 해당 쿼리가 성능상 어떤 장단점을 가지고 있는지 보기 쉽게 그래픽 실행 계획 아이콘으로 표시해준다.
- 실행계획 내용은 버릴것이 없으므로 꼼꼼히 따져보아야 한다.
- 튜닝의 시작은 성능 분석이다.
(2) Index를 타는지 항상 체크한다.
- Index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다. schema를 잘못 짠 경우 이런 현상이 흔히 발생하는데 이에 대한 점검을 늘 해야한다.
- 흔히 오해하기 쉬운 것 중 WHERE 조건절은 필요한 column에만 존재해야 한다는 생각이다. WHERE 조건절에는 Clustered Index Seek를 타기 위한 Column이 우선 존재해야 하고 그 후에 원하는 데이터를 얻기 위한 조건절이 존재해야 한다. 조건 자체가 Clustered Index Column이면 가장 좋다.
- 조건절에 Index에 해당하는 column들이 존재하는 경우 우선적으로 해당 조건을 만족하는 행을 호출한 후 나머지 조건에 대해 만족하는 행을 다시 호출하게 된다.
(3) Clustered Index Seek를 항상 체크한다.
- Clustered index scan을 타는 것만으로도 속도는 향상 되지만 완전하지 않다. clustered index column의 일정 구간을 타는 seek여야 대량으로 증가하는 data에 대한 부하를 감당할 수 있다. 이를 위해 index의 구간 체크를 해야 한다.
- 만약 검색하는 column이 clustered index column인 경우는 단방향 WHERE 조건문으로도 index scan이 성립된다. 자신의 column에서 그대로 찾아서 시작지점부터 끝까지 index를 타면 되기 때문이다.
- 하지만 일반 non-clustered index의 경우는 clustered를 찾기 위해 해당 column의 clustered index 정보를 호출해야하는 부담이 생긴다. 왜냐하면 결국 호출을 하기 위해서는 해당 데이터의 위치를 찾아야 하고 이 위치를 가장 밀도있게 알고 있는 clustered index에서 해당 데이터의 위치를 찾아 가져오기 때문이다. (바로 찾게 되면 clustered index보다 범위가 크기 때문에 중간에 clustered index를 통해 찾는다. ) 결국 구간 체크가 아닌 non-clustered index의 단방향 WHERE 조건문은 clustered의 전체 스캔을 하게 되는 결과를 가져온다.