Sql 튜닝

9장. 갱신과 데이터 모델 - 망치와 못 26강. 갱신을 효율적으로 갱신을 효율적으로 수행하는 SQL을 케이스 스터디 1. NULL 채우기 1 2 3 4 5 6 7 8 9 UPDATE OmitTbl SET val = (SELECT val FROM OmitTbl OT1 WHERE OT1.keycol = (SELECT MAX(seq) FROM OmitTbl OT2 WHERE OT2.keycol = OmitTbl.keycol AND OT2.seq < OmitTbl.seq AND OT2.val IS NOT NULL)) WHERE val IS NULL; 2. 반대로 NULL을 설정 1 2 3 4 5 6 7 8 9 10 11 12 UPDATE OmitTbl SET val = CASE WHEN val = (SELECT val FROM OmitTbl 01 WHERE 01.keycol = OmitTbl.keycol AND 01.seq = (SELECT MAX(seq) FROM OmitTbl 02 WHERE 02.keycol = OmitTbl.keycol AND 02.seq < OmitTbl.seq)) THEN NULL ELSE val END; 27강. 레코드에서 필드로의 갱신 1. 필드를 하나씩 갱신 1 2 3 4 5 6 7 8 9 10 11 12 13 UPDATE ScoreCols SET score_en = (SELECT score FROM ScoreRow SR WHERE SR.studend_id = ScoreCols.studend_id AND subject = '영어'), score_nl = (SELECT score FROM ScoreRow SR WHERE SR.studend_id = ScoreCols.studend_id AND subject = '국어'), score_mt = (SELECT score FROM ScoreRow SR WHERE SR.studend_id = ScoreCols.studend_id AND subject = '수학'); 명확하지만 항목별로 서브쿼리를 필요로하기 때문에 비효율적이다. 2. 다중 필드 할당 여러 개의 필드를 리스트화하고 한번에 갱신 1 2 3 4 5 6 7 8 9 10 11 12 13 UPDATE ScoreCols SET (score_en, score_nl, score_mt) = (SELECT MAX(CASE WHEN subject = '영어' THEN score ELSE NULL END) AS score_en, MAX(CASE WHEN subject = '국어' THEN score ELSE NULL END) AS score_nl, MAX(CASE WHEN subject = '수학' THEN score ELSE NULL END) AS score_mt FROM ScoreRows SR WHERE SR.student_id = ScoreCols.student_id); 상관 서브쿼리가 하나로 정리된 대신, ScoreRows 테이블에 대한 접근이 INDEX UNIQUE SCAN -> INDEX RANGE SCAN, MAX 함수의 정렬리 추가되므로 트레이드오프가 있다. 1) 다중 필드 할당 2) 스칼라 서브쿼리 MAX함수를 적용해 집약시킴으로써 가능해진다. 3. NOT NULL 제약이 걸려있는 경 1) UPDATE 구문 사용 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 UPDATE ScoreColsNN SET score_en = COALESCE((SELECT score FROM ScoreRow SR WHERE SR.studend_id = ScoreCols.studend_id AND subject = '영어'), 0), score_nl = COALESCE((SELECT score FROM ScoreRow SR WHERE SR.studend_id = ScoreCols.studend_id AND subject = '국어'), 0), score_mt = COALESCE((SELECT score FROM ScoreRow SR WHERE SR.studend_id = ScoreCols.studend_id AND subject = '수학'), 0); WHERE EXISTS (SELECT * FROM ScoreRows WHERE student_id = ScoreColsNN.studend_id); 테이블 사이에 일치하지 않는 레코드는 제거 학생은 존재하지만 과목이 없는 경우 처리 2) MERGE 구문 사용 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 MERGE INTO ScoreColsNN USING (SELECT student_id, COALESCE(MAX(CASE WHEN subject = '영어' THEN score ELSE NULL END), 0) AS Score_en, COALESCE(MAX(CASE WHEN subject = '국어' THEN score ELSE NULL END), 0) AS Score_nl, COALESCE(MAX(CASE WHEN subject = '수학' THEN score ELSE NULL END), 0) AS Score_mt FROM ScoreRows GROUP By studend_id) SR ON (ScoreColsNN.student_id = SR.student_id) WHEN MATCHED THEN UPDATE SET ScoreColsNN.score_en = SR.score_en, coreColsNN.score_nl = SR.score_nl, ScoreColsNN.score_mt = SR.score_mt; 결합조건을 ON 구 하나에 묶을 수 있다. ScoreRows 테이블 풀 스캔 1회 + 정렬 1회로 고정된다. 나은 선택지로 고려해볼만 하다. 28강. 필드에서 레코드로 변경 1 2 3 4 5 6 7 8 9 UPDATE ScoreRows SET Score = (SELECT CASE ScoreRows.subject WHEN '영어' THEN score_en WHEN '국어' THEN score_nl WHEN '수학' THEN score_nt ELSE NULL END FROM ScoreCols WHERE student_id = ScoreRow.student_id); 29강. 같은 테이블의 다른 레코드로 갱신 1. 상관 서브쿼리 사용 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 INSERT INTO Stocks2 SELECT brand, sale_date, price, CASE SIGN(price - (SELECT price FROM Stocks S1 WHERE brand = Stocks.brand AND sale_date = (SELECT MAX(sale_date) FROM Stocks S2 WHERE brand = Stocks.brand AND sale_date < Stock.sale_date))) WHEN -1 THEN '아래화살표' WHEN 0 THEN '오른쪽화살표' WHEN 1 THEN '위쪽화살표' ELSE NULL END FROM Stocks S2; 상관서브쿼리 때문에 테이블에 여러번 접근해야 한다. 2. 윈도우 함수 적용 1 2 3 4 5 6 7 8 9 10 11 12 13 INSERT INTO Stocks2 SELECT brand, sale_date, price, CASE SIGN(price - MAX(price) OVER (PARTITION BY brand ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) WHEN -1 THEN '아래화살표' WHEN 0 THEN '오른쪽화살표' WHEN 1 THEN '위쪽화살표' ELSE NULL END FROM Stocks S2; 매우 간단하고 효율적이 된다. 3. INSERT와 UPDATE 어떤 것이 좋을까? INSERT SELECT 장점 처리가 더 빠름 자기참조를 허가하지 않는 DB에서도 사용가능 단점 같은 데이털르 두개 만들어야하므로 저장소를 2배 이상 소비 뷰로 만들어도 되나 트레이드 오프가 있다 30강. 갱신이 초래하는 트레이드오프 주문일과 배송 예정일 차이가 3일 이상 차이나는 것 1. SQL을 사용하는 방법 1 2 3 4 5 6 7 SELECT O.order_id, O.order_name, ORC.delivery_date - O.order_date AS diff_days FROM Orders O INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id WHERE ORC.delivery_date - O.order_date >= 3; 주문별로 최대 지연일을 알고 싶은 경우 1 2 3 4 5 6 7 8 SELECT O.order_id, MAX(O.order_name), -- SELECT 하기 위해 MAX를 사용 MAX(ORC.delivery_date - O.order_date) AS max_diff_days FROM Orders O INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id WHERE ORC.delivery_date - O.order_date >= 3; GROUP BY O.order_id; 2. 모델 갱신을 사용하는 방법 쿼리를 통해 찾는게 아니라 필드를 하나 추가해 해결할수도 있을 것 코딩 외의 방법도 해결수단이 된다. 31강. 모델 갱신의 주의점 3가지의 트레이드 오프가 있다 1. 높아지는 갱신 비용 배송 지연 플래그 필드를 갱신하는 비용이 든다 2. 갱신까지의 시간 랙 발생 갱신 되기 전까지의 시간차이가 발생 3. 모델 갱신 비용 발생 모델의 수정은 대단히 큰 수정이 요구됨, 특히나 이미 운영을 시작했다면 더욱 더 32강. 시야 협착 : 관련 문제 시야 협착에 빠지기 쉬운 경우를 살펴본다 31강의 문제를 그대로 사용한다 주문번호마다 몇개의 상품이 주문되었는지 확인(주문번호, 주문자 이름, 주문일, 상품수) 1. 다시 SQL을 사용한다면 1 2 3 4 5 6 7 8 SELECT O.order_id, MAX(O.order_name) AS order_name, MAX(O.order_date) AS order_date, COUNT(*) AS item_count FROM Orders O INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id GROUP BY O.order_id; 1 2 3 4 5 6 7 SELECT O.order_id, O.order_name, O.order_date, COUNT(*) OVER (PARTITION BY O.order_id) AS item_count FROM Orders O INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id; 집약을 쓰든 윈도우 함수를 쓰든 모두 결합과 집약을 수행하기 때문에 실행 비용은 비슷하다. 가독성과 확장성 측면에서 윈도우 함수를 쓰는 것이 더 좋다. 2. 다시 모델 갱신을 사용한다면 주문이 등록될 때 수를 알 수 있으므로 필드를 추가해 개수를 넣기 쉽다. 다만, 주문 변경이 일어나는 경우의 처리를 고려해야 한다. 3. 초보자보다 중급자가 경계해야 33강. 데이터 모델을 지배하는 자가 시스템을 지배한다. 현명한 데이터 구조와 멍청한 코드의 조합이 멍청한 데이터 구조와 현명한 코드의 조합보다 좋다. 엔지니어의 사명은 전략적 실패를 만회하는 전술을 찾는 것이 아닌 올바른 전략을 고려하는 것 34강. 인덱스와 B-tree RDB의 인덱스 구조, 1. B-tree, 2. 비트맵, 3. 해시 1. 만능형 : B-tree 대부분 DB의 B+tree 라는 수정 버전을 사용한다. B+tree는 리프노드에만 키값을 저장한다. B+tree는 루트와 리프의 거리를 가능한 일정하게 유지하려고 하기 때문에 검색 속도가 안정적이다. 2. 기타 인덱스 비트맵 인덱스 : 데이터를 비트 플래그로 변환해서 저장하는 형태의 인덱스로 카디널리티가 낮은 필드에 대해 효과를 발휘한다. 하지만 갱신할 때 오버헤드가 키 BI/DWH 용도로 사용된다. 해시 인덱스 : 검색 외에 효과가 없어 거의 사용되지 않는다. 35강. 인덱스를 잘 활용하려면 B+tree는 양이 증가해도 검색이 안정적이고, 범위 검색도 쉬움 1. 카디널리티(값의 균형)와 선택률(전체중에서 몇개가 선택되는지) 클러스터링 팩터(저장소에 같은 값이 어느정도 물리적으로 뭉쳐 존재하는지)가 낮을수록 좋다. 하지만 이것은 구현에 의족한다. 2. 인덱스를 사용하는 것이 좋은지 판단하려면 카디널리티가 높은 것 선택률이 낮은 것 5 ~ 10 % 이하, 저장소의 성능향상과 반비례한다. 36강. 인덱스로 성능 향상이 어려운 경우 인덱스 설계는 테이블 정의와 SQL만 봐서는 할 수 없다. 1. 압축 조건이 존재하지 않음 WHERE 구가 없이 SELECT 하는 경우 2. 레코드를 제대로 압축하지 못하는 경우 WHERE 구 조건의 선택률이 너무 높아 인덱스를 만들기 비효율적이다. 1) 입력 매개변수에 따라 선택률이 변동하는 경우 - 1 2) 입력 매개변수에 따라 선택률이 변동하는 경우 - 2 3. 인덱스를 사용하지 않는 검색 조건 1) 중간 일치, 후방 일치 LIKE 연산자 LIKE를 사용하는 경우 인덱스는 전방일치에만 적용 가능하다. 2) 색인 필드로 연산하는 경우(함수를 적요하는 경우에도 안됨) 다음과 같이 바꾸면 됨 WHERE col_1 * 1.1 > 100 -> col_1 > 100/1.1 3) IS NULL을 사용하는 경우 색인 필드 데이터에는 NULL이 존재하지 않기 때문에 인덱스를 사용불가 4) 부정형을 사용하는 경우 <>, !=, NOT IN 37강. 인덱스를 사용할 수 없는 경우 대처법 1. 외부 설정으로 처리 - 깊고 어두운 강 건너기 1) UI 설계로 처리 사용자가 선택할 수 있는 조건을 제한한다. 2. 외부 설정을 사용한 대처 방법의 주의점 개발전에 합의해야 한다. 서로의 이해관계를 파악하는 것이 중요! 3. 데이터 맡로 대처 특정한 쿼리에서 필요한 데이터만을 저장하는, 상대적으로 작은 크기의 테이블이다. 서브셋 원래 대규모의 데이터를 다뤄야 하는(성능 조건이 중요함) BI/DWH 분야에 사용되는 기술이다. 접근 대상 테이블의 크기를 작게해서 I/O양을 줄이는 것이 목적이다. 4. 데이터 마트를 채택할 시 주의점 1) 데이터 신선도 동기 시점에 따라 신선도와 성능간의 트레이드 오프 고려 할 것 2) 데이터 마트 크기 I/0 양을 줄이는 것이 목적이므로 테이블의 크기가 딱히 줄어들지 않는다면 필요없다. GROUP BY절을 미리 사용해 집계를 마치고 데이터 마트로 만들면 필드 수와 레코드수를 크게 줄일 수 있다. 3) 데이터 마트수 목적에 맞게 잘 만들어 관리해야, 용량과 성능문제가 안생긴다. 빠르다고 무작점 만들어서 쓰면 안됨 4) 배치 윈도우 데이터 마트도 만드는데 시간이 걸리고 변경에 따른 갱신도 발생하므로 배치 윈도우를 압박한다. 배치윈도우와 Job Net도 고려할 것 5. 인덱스 온리 스캔으로 대처 I/O 감소를 목적으로하는 데이터 마트와 접근이 같고, 데이터 동기 문제를 해결할 수 있다. 풀 스캔을 할 때 검사대상을 테이블이 아닌 인덱스로 바꿀 수 있다. 압축 요건이 존재하지 않는 경우 CREATE INDEX CoveringIndex ON Orders (order_id, receive_date); SQL 구문에서 필요한 필드를 인덱스만으로 커버할 수 있는 경우, 테이블 접근을 생략하는 기술 이다. 레코드를 제대로 압축하지 못하는 검색 조건 CREATE INDEX CoveringIndex_1 ON Orders (process_flg, order_id, receive_date); 압축은 되지만 인덱스를 사용하지 않는 검색 조건 CREATE INDEX CoveringIndex_2 ON Orders (shop_name, order_id, receive_date); 로우 지향 DB를 컬럼 지향 DB로 만드는 방법이라고 생각하면 된다. 6. 인덱스 온리 스캔의 주의사항 1) DBMS에 따라 사용할 수 없는 경우도 있다. 2) 한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있다. 3) 갱신 오버 헤드가 커진다. 커버링 인덱스는 성질살 필연적으로 필드 수가 많아 크기가 큰 인덱스가 되기 쉽다. 따라서 테이블을 갱신할 때의 오버헤드도 일반적인 인덱스에 비해 커진다. 4) 정기적인 인덱스 리빌드가 필요 인덱스에만 접근한다는 것은 성능이 인덱스의 크기에 의존한다는 것이다. 따라서 정기적인 모니터링과 리빌드를 필요로 한다. 5) SQL에 새로운 필드가 추가되면 사용할 수 없다. 일반적인 인덱스에 비해 어플리케이션 유지 보수에 약한 타입의 튜닝이라고 할 수 있다.

8장. SQL의 순서 - 깨어나는 절차 지향 sql은 관계 모델의 이론에 기초하고 있기 때문에 순번을 다루기 위한 기능이 없다. 하지만 레코드에 순번을 붙여 처리하는 경우가 많아 관련기능(시퀀스 객체, ID 필드, 윈도우 함수)을 추가하고 있다. 23강. 레코드에 순번 붙이기 1. 기본키가 한 개의 필드일 경우 1) 윈도우 함수로 사용 ROW_NUMBER 함수를 사용 1 2 3 SELECT student_id, ROW_NUMBER() OVER (ORDER BY student_id) AS Seq FROM Weights; 2) 상관 서브쿼리 사용 1 2 3 4 5 SELECT student_id, (SELECT COUNT(*) FROM Weight W2 WHERE W2.student_id <= W1.student_id) AS Seq FROM Weights W1; 기능은 동일하지만 1) 방법의 성능이 좋다. 스캔 횟수 1):1회, 2):2회 2. 기본 키가 여러 개의 필드로 구성되는 경우 1) 윈도우 함수를 사용 1 2 3 SELECT class, student_id, ROW_NUMBER() OVER (ORBER BY class, stduent_id) AS Seq FROM Weight2; 2) 상관 서브쿼리를 사용 다중 필드 비교하기(문자, 숫자, 3개 비교도 가능) 1 2 3 4 5 6 SELECT class, student_id, (SELECT COUNT(*) FROM Weight W2 WHERE (W2.class, W2.student_id) <= (W1.class, W1.stduent_id) AS Seq FROM Weight W1; 3. 그룹마다 순번을 붙이는 경우 1) 윈도우 함수를 사용 class 필드에 PARTITION BY 적용 1 2 3 SELECT class, student_id, ROW_NUMBER() OVER(PARTITION BY class ORDER BY student_id) AS Seq FROM Weight2; 2) 상관서브쿼리를 사용 1 2 3 4 5 6 SELECT class, student_id, (SELECT COUNT(*) FROM COUNT(*) WHERE W2.class = W1.class AND W2.student_id <= W1.student_id) AS Seq FROM Weight2 W1; 4. 순번과 갱신 1) 윈도우 함수를 사용 셀렉트 쿼리를 SET에 넣으면 됨 1 2 3 4 5 6 7 8 UPDATE Weights3 SET Seq = (SELECT Seq FROM (SELECT class, student_id, ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS Seq FROM Weights3) SeqTbl WHERE Weights3.class = SeqTbls.class AND Weights3.student_id = SeqTbl.student_id) 2) 상관 서브쿼리를 사용 1 2 3 4 5 UPDATE Weight3 SET seq = (SELECT COUNT(*) FROM Weight3 W2 WHERE W2.class = Weights3.class AND W2.student_id <= Weight3.student_id) 24강. 레코드에 순번 붙이기 응용 1. 중앙값 구하기 1) 집합 지향적 방법 1 2 3 4 5 6 7 8 SELECT AVG(weight) FROM (SELECT W1.weight FROM Weights W1, Weights W2 GROUP BY W1.weight HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END) >= COUNT(*)/2) TMP; 코드가 복잡하다 성능이 나쁘다. w1과 w2간에 결합이 발생 2) 절차 지향적 방법 1 - 세계의 중심을 향해 sql에서 자연수의 특징을 활용하면 ‘양쪽 끝부터 숫자 세기’를 할 수 있다 1 2 3 4 5 6 SELECT AVG(weight) AS median FROM (SELECT weight, ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) AS hi, ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) AS lo FROM Weights) TMP WEHRE hi IN(lo, lo+1, lo+2); RANK 또는 DENSE_RANK를 사용해서는 안된다. 순위가 겹치거나 빌 수 있다. 테이블 접근 1회로 감소, 대신 정렬이 2회로 늘었다. ROW_NUMBER에서 사용하는 정렬이 오름/내림차순 2개라서 그렇다. 3) 절차 지향적 방법 2 - 2빼기 1은 1 성능적으로 개선하기 1 2 3 4 5 6 SELECT AVG(weight) FROM (SELECT weight, 2 * ROW_NUMBER() OVER(ORDER BY weight) - COUNT(*) OVER() AS diff FROM Weights) TMP WHERE diff BETWEEN 0 AND 2; 정렬리 1회로 줄어든다. 이 방법이 SQL 표준으로 중앙값을 구하는 가장 빠른 방법이다. 2. 순번을 사용한 테이블 분할 비어있는 자리 출력하기 1) 집합 지향적 방법 - 집합의 경계선 1 2 3 4 5 6 7 SELECT (N1.num + 1) AS gap_start, '~', (MIN(N2.min - 1) AS gap_end FROM Number N1 INNER JOIN Numbers N2 ON N2.num > N1.num GROUP BY N1.num HAVING (N1.num + 1) < MIN(N2.num); 코드도 간단하며 집합 지향적인 방식이라 좋다. 다만, 자기 결합을 사용해야 한다(Nested Loop). 2) 절차 지향적 방법 - 다음 레코드와 비교 컨셉 : 현재 레코드와 다음 레코드를 비교해 차이가 1이 아니면 1 2 3 4 5 6 7 8 9 10 SELECT NUM+1 AS gap_start, '~', (num + diff - 1) As gap_end FROM (SELECT num, MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num FROM numbers) TMP (num, diff) WHERE diff <> 1 테이블 접근 1회, 정렬 1회로 안정적 성능 3. 테이블에 존재하는 시퀀스 찾기 친구 또는 가족 인원수에 맞게 자리를 예약하는 경우 활용됨 1) 집합 지향적 방법 - 다시, 집합의 경계선 1 2 3 4 5 6 7 8 9 SELECT MIN(NUM) AS low, '~', MAX(num) AS high FROM (SELECT N1.num, COUNT(N2.num) - N1.num FROM Numbers N1 INNER JOIN Numbers N2 ON N2.num <= N1.num GROUP BY N1.num) N(num, gp) GROUP BY gp; 2) 집합 지향적 방법 - 다시, 다음 레코드 하나와 비교 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 SELECT low, high FROM(SELECT low, CASE WHEN high IS NULL THEN MIN(high) OVER (ORDER BY seq ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ELSE high END AS high FROM (SELECT CASE WHEN COALESCE(prev_diff, 0) <> 1 THEN num ELSE NULL END AS low, CASE WHEN COALESCE(next_diff, 0) <> 1 THEN num ELSE NULL END As high, seq FROM (SELECT num, MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS next_diff, num - MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_diff, ROW_NUMBER() OVER (ORDER BY num) AS seq FROM Numbers) TMP1 ) TMP2 ) TMP3 WHERE low IS NOT NULL; 25강. 시퀀스 객체, IDENTIFY 필드, 채번 테이블 표준 SQL에는 순번을 다루는 기능으로 시퀀셜 객체나 IDENTIFY 필드가 존재한다. 하지만 사용하는 것을 권장하지는 않고, 사용한다면 시퀀스 객체를 권한다. 1. 시퀀스 객체 테이블 또는 뷰처럼 스키마 내부에 존재하는 객체 중 하나 1 2 3 4 5 6 CREATE SEQUENCE testseq START WITH 1 INCREMENT BY 1 MAXVALUE 10000 MINVALUE 1 CYCLE; INSERT 구문에서 흔히 사용된다. 1) 시퀀스 객체의 문제점 표준화가 늦어서, 구현에 따라 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있다 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다. 성능적인 문제를 일으킨다 2) 시퀀스 객체로 발생하는 성능 문제 순서성(순번의 대소 관계가 유지됨), 유일성, 연속성 사용자 A가 시퀀스 객체에서 NEXT VALUE를 검색할 때의 처리 시퀀스 기개체에 배타 락을 적용 NEXT VALUEfmf rjator CURRENT VALUE를 1만큼 증가 시퀀스 객체에 배타 락을 해제 3) 시퀀스 객체로 발생하는 성능 문제의 대처 (1) CACHE 읽어들일 변수를 메모리에 설정하는 것, 다만 시스템 장애시 정상동작을 담보할 수 없다. (1) NOORDER 순서성을 담보하지 않음으로써 오버 헤드를 줄인다. 4) 순번을 키로 사용할 때의 성능 문제 Hot spot 과 관련된 문제임 DBMS는 비슷한 데이털르 연속적으로 INSERT하면 물리적으로 같은 영역에 저장한다. 이 때 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화가 발생 = Hot spot, Hot block 시퀀스 객체를 사용해 INSERT를 반복하는 경우 발생하고, 대처가 불가능 5) 순번을 키로 사용할 때의 성능 문제에 대처 (1) Oracle의 열 키 인덱스 연속된 값을 도입하는 경우라도 DBMS 내부에서 변화를 주어 제대로 분산할 수 있는 구조를 사용하는 것, 다만 SELECT 성능이 나빠질 수 있다. (2) 인덱스에 복잡한 필드를 추가해서 데이터의 분산도를 높인다. 논리적으로 좋은 설계가 아님 2. IDENTIFY 필드 ‘자동 순번 필드’라고도 한다. 테이블의 필드로 정의하고, INSERT 발생할때마다 자동을 순번을 붙여주는 기능이다. 시퀀스 객체에 비해 단점이 많다. 시퀀스 객체는 여러 테이블에서 사용가능하지만, IDENTIFY 필드는 특정 테이블에 국한된다. CACHE, NOORDER를 지정할 수도 없다. 이점이 거의 없다. 3. 채번 테이블 순번을 부여하기 위해 어플리케이션에서 채번 테이블이라는 것을 만들어 사용했었다. 구시대 유물이며 문제가 안생기기를 바라는 것이 최선(튜닝할 방법도 없다)

최근 sql 튜닝에 대해서 책도 보고 유투브에서 동영상도 몇편 봤었는데 그 중에서 저에게 가장 큰 통찰?을 줬던 영상이 있어서 링크 올립니다 [SQL Unplugged 2013] 쉽고 재미있는 인덱스 이야기/ 씨퀄로 이장래 ...

6. 결합(결합을 지배하는 자가 SQL을 지배한다) ch 18. 기능적인 관점으로 구분하는 결합의 종류 크로스 결합 내부 결합 외부 결합 자기 결합 등가 결합/비등가 결합 자연 결합 위에서 3개는 배타적 결합이다. 컬럼) 자연 결합 구문 자연결합 = 내부 결합 + 등가 결합 1) 크로스 결합 - 모든 결합의 모체 데카르트 곱 (1) 실무에서 사용하지 않음 그런 결과가 필요없다 비용이 크다 (2) 실수로 사용한 크로스 결합 SELECT * FROM Employees, Departments; 2) 내부 결합 - 왜 ‘내부’라는 말을 사용할까? (1) 내부 결합의 작용 크로스 결합 결과의 부분집합 (2) 내부 결합과 같은 기능을 하는 상관 서브쿼리 스칼라 서브쿼리 = 리턴값이 하나인쿼리(SELECT의 필요 조건) 상관 서브쿼리보다 결합이 우수하다 3) 외부 결합 - 왜 ‘외부’라는 말을 사용할까? (1) 외부 결합의 작동 왼쪽/오른쪽/완전 외부 결합 키를 모두 가진 레이아웃의 리포트를 만들때 사용 4) 외부 결합과 내부 결합의 차이 외부 결합은 NULL을 생성한다 5) 자기 결합 - ‘자기’란 누구일까? ch 19. 결합 알고리즘과 성능 Nested Loops Hash Sort Merge 1) Nested Loops 이중 반복 바깥 반복 테이블(구동 테이블, 외부 테이블) <-> 내부 테이블 접근하는 레코드 수 R(A) * R(B)이며 실행 시간은 레코드수에 비례한다. 구동 테이블을 작게 만드는 것이 중요하다 (1) 구동 테이블의 중요성 (내부 테이블의 결합키 필드에 인덱스가 존재) 구동 테이블을 작게 내부 테이블의 반복을 줄일 수 있음 이상적으로 구동 테이블의 레코드 한개에 내부 테이블의 레코드 한개가 대응하고, 해당 레코드를 내부 테이블의 인덱스로 사용해 찾을 수 있는 경우 레코드 레코드 수는 R(A) * 2 (2) Nested Loops 의 단점 결합키로 내부 테이블에 접근할 때 히트되는 레코드가 너무 많기 때문이다. 해결 역설적이게 구동 테이블을 큰것으로 Hash 2) Hash (1) Hash의 작동 작은 테이블을 스캔해 결합키 Hash (워키 메모리 사용량을 줄이기 위해) 큰 테이블에 접근해 Hash하고 매칭 (2) Hash의 특징 결합 테이블로부터 해시 테이블을 만드므로 Nested Loops에 비해 메모리를 많이 사용 메모리가 부족하면 저장소 사용으로 지연 출력되는 해시값은 입력값의 순서를 알지 못하므로, 등치 결합에만 사용 가능 (3) Hash가 유용한 경우 Nested Loops에서 적절한 구동 테이블이 존재하지 않는 경우 적절한 구동 테이블이 있지만, 내부 테이블에서 히트되는 레코드 수가 많은 경우 Nested Loops의 내부 테이블에 인덱스가 존재하지 않는 경우 (4) 유의사항 OLTP에서는 사용하면 안된다. 풀스캔(해시를 위해)의 비용도 고려할 것 3) Sort Merge (1) Sort Merge의 작동 결합키로 테이블들을 정렬하고, 일치하는 것 끼리 결합 대상 테이블을 모두 정렬해야하므로 메모리가 많이 사용 Hash와 달리 부등호를 사용해 결합 가능 테이블이 정렬되어 있다면 정렬을 생략 가능 테이블을 정렬하므로 테이블을 모두 스캔한 시점에 결합 완료 (2) Sort Meger가 유효한 경우 테이블 정렬을 생략 가능한 경우 4) 의도하지 않은 크로스 결합 삼각 결합 : 결합 조건이 A-B, A-C인 경우 B-C 사이에 크로스 결합이 일어날 수 있다. 옵티마이저가 B,C의 크기가 충분히 작다고 판단하는 경우 (1) 의도하지 않은 크로스 결합을 회피하는 방법 불필요한 결합 조건 추가 ch 20. 결합이 느리다면

7. 서브쿼리(곤란한 부분은 분할해야만 할까?) ch 21. 서브쿼리가 일으키는 폐해 1) 서브쿼리의 문제점 성능적 문제는 서브쿼리가 실체적인 데이터를 저장하고 있지 않다는 것에 있다. (1) 연산 비용 추가 서브쿼리 = SELECT 이므로 실행할때마다 SELECT 하는 것 (2) 데이터 I/O 비용 발생 연살결과가 커 저장소를 쓰게 되는 경우 급격한 속도 저하 발생 (3) 최적화를 받을 수 없음 서브쿼리의 결과에는 메타 정보가 없어 최적화가 불가능 2) 서브쿼리 의존증 (1) 서브쿼리를 사용한 방법 코드가 복잡해 읽기 어렵다 성능 결과가 일시적인 영역에 확보되므로 오버헤드 발생 최적화 불가 결합을 필요로 하기 때문에 비용이 높고 실행계획 변동 리스크가 존재 recipts 테이블 두번 스캔 필요 (2) 상관 서브쿼리는 답이 될 수 없다 어쨋든 테이블에 2번 접근해야 한다 (3) 윈도우 함수로 결합 해결 목표는 테이블 접근 1회로 줄이기 ROW_NUMBER를 사용해 구매 이력 번호를 붙이고, 이력이 1인 레코드 추출 3) 장기적인 관점에서의 리스크 관리 결합을 사용한 쿼리의 불안정 요소(상관 서브쿼리도 유사) 결합 알고리즘의 변동 리스크 환경 요인에 의한 지연 리스크(인덱스, 메모리, 매개변수 등) (1) 알고리즘 변동리스크 상황에 따라 변하는 결합 알고리즘 (2) 환경 요인에 의한 지연 리스크 결합을 사용한다는 것 = 장기적인 관점에서의 리스크 증가 4) 서브쿼리 의존증 - 응용편 (1) 다시 서브쿼리 의존증 (5) 서브쿼리는 정말 나쁠까? 생각하기는 쉬우나 RDB와는 맞지 않다 ch 22. 서브쿼리 사용이 더 나은 경우 결합쿼리는 최대한 결합 대상 레코드수를 줄이는 것이 중요한데, 옵티마이저가 잘 판단하지 못하는 경우 직접 연산 순서를 명시하는 용도로 힌트 사용 1) 결합과 집약 순서 (1) 두 가지 방법 결합 -> 집약 집약 -> 결합 (2) 결합 대상의 레코드 수 2의 경우 레코드수가 줄기 때문에 더 나은 선택일 수 있다(사전에 결합 레코드수 압축)

인용하는 그림은 다양한 곳에서 가져왔음을 밝힙니다 5. 반복된(절차 지향형의 속박) ch 14. 반복문 의존증 RDB는 관계 전체를 조작의 대상으로 삼기 때문에 설계상에서 반복을 제외했다 ch 15. 반복계의 공포 record at a time 사고 방식 반복계의 장점은 생각하기 쉽고 단순하다는 것 1) 반복계의 단점 성능 (1) SQL 실행의 오버헤드 전처리 a. sql 구문을 네트워크로 전송 b. DB 연결 c. sql 구문 파스 d. sql 구문의 실행 계획 생성 또는 평가 후처리 e. 결과 집합을 네트워크로 전송 a, e는 동일한 본체에 있거나 분리되어 있어도 고만고만함 b는 요즘에 커넥션 풀이라는 기술로 오버헤드를 감소시킴 c와 d가 주된 오버헤드이다. 그중에서도 c가 성가시다 c는 db가 sql을 받을때 마다 실행하므로 반복계에서는 오버헤드의 비중이 커진다 (2) 병렬 분산이 힘들다 반본계는 하나씩만 처리하기 때문에 병렬처리가 힘들다 저장소의 분산 효율이 낮다(하나씩 처리하다보니 한번에 처리하는 데이터가 얼마안됨) (3) 데이터 베이스의 진화로 인한 혜택을 받을 수 없다 대규모의 데이터를 효율적으로 다루기 위해 진화하고 있으나, 반복계를 사요하면 그 혜택을 받을 수 없다 포장계 sql이 반복계에 비해 복잡하므로 튜닝을 잘해야 하는 단점도 있는 반면 제대로만 튜닝하면 현격한 성능차이가 발생한다 반복계는 단순해 튜닝포인트도 적다 2) 반복계를 빠르게 만드는 방법은 없다 (1) 반복계를 포장계로 다시 작성 애플리케이션의 수정을 의미 (2) 각각의 sql을 빠르게 수정 너무 단순해 튜닝한 건덕지가 없음 (3) 다중화 처리 리소스 여유가 있고, 처리를 나눌 수 있는 키가 있고, 순서가 중요하지 않다면 다중화 가능 3) 반복계의 장점 sql이 단순하다 (1) 실행 계획의 안정성 실행계획이 바뀌어 느려지는 경우가 없다 (2) 예상 처리 시간의 정밀도 (3) 트랜잭션 제어가 편리 ch 16. sql에서는 반복을 어떻게 표현할까? 1) 포인트는 CASE식과 윈도우 함수 1 2 3 4 5 6 7 8 9 10 11 12 INSERT INTO Sales2 SELECT company, year, sale, CASE SIGN(sale - max(sale) OVER(PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECENDING AND 1 PRECENDING) WHEN 0 THEN '=' WHEN 1 THEN '+' WHEN -1 THEN '-' ELSE NULL END AS Var FROM Sales; 1 ROWS BETWEEN 1 PRECENDING AND 1 PRECENDING 현재 레코드에서 1개 이전부터 1개 이전까지의 레코드 범위 지정 ...

4. 집약과 자르기 ch 12. 집약 COUNT, SUM, AVG, MAX, MIN(Aggregate function) 1) 여러 개의 리코드를 한 개의 레코드로 집합 필드 수가 다르면 UNION 적용이 불가능하다. 또한 UNION으로 여러개의 쿼리를 머지하는 것은 성능적으로 안티패턴이다. GROUP BY 구로 집약을 했을 때 SELECT 구에 입력할 수 있는 것은 상수 GROUP BY 구에서 사용한 집약 키 집약함수 집약함수가 적용되면 여러 요소가 있는 집합으로부터 연산결과가 나옴 집약, 해시, 정렬 집약시에는 해쉬 알고리즘을 사용한다(때로는 정렬) GROUP BY 구에 지정된 필드를 해쉬 함수로 사용해 결과를 만들고, 같은 결과로 그룹을 만들어 집약한다. 고전적인 방법보다 효율적 해쉬와 정렬 모두 메모미를 많이 사용하기 때문에, 충분한 워킹 메모리가 확보되지 않으면 스왑이 발생한다(극단적 성능저하 발생) 2) 합쳐서 하나 문제 : 연령대 별로 가격이 다른 제품중에서 0 ~ 100세가 이용가능한 제품 고르기 hint : 각 범위의 상수를 합해 101인 제품 선택하기 1 2 3 4 SELECT product_id FROM PriceByAge GROUP BY product_id HAVING SUM(high_age - low_age + 1) = 101; ch 13. 자르기 집약 이외에도 중요한 자르기 라는 기능이 있다 1) 자르기와 파티션 1 2 3 4 SELECT SUBSTRING(name, 1, 1) AS label, COUNT(*) FROM Persons GROUP BY SUBSTRING(name, 1, 1); GROUP BY 구로 잘라 만든 하나 하나의 부분 집합을 ‘파티션’이라고 한다. ...

3. SQL의 조건 분기 ch 8. UNION을 사용한 쓸데없이 긴 표현 UNION을 사용한 조건 분기는 좋지 않다. UNION은 내부적으로 여러개의 SELECT 구문을 실행하는 실행계획으로 해석된다 (= 테이블에 접근하는 횟수(I/O)가 늘어난다) 1) UNION을 사용한 조건 분기와 관련된 간단한 예제 UNION을 사용한 조건 분기 1 2 3 4 5 6 7 SELECT item_name, year, price_tax_ex AS price FROM Items WHERE year <= 2001 UNION ALL SELECT item_name, year, price_tax_in AS price FROM Items WHERE year >= 2002 단점 : 1. 길다, 2. 테이블에 2회 접근한다. 2) WHERE 구에서 조건 분기를 하는 사람을 초보자 1 2 3 4 SELECT item_name, year CASE WHEN year <= 2001 TEHN price_tax_ex WHEN year >= 2002 THEN price_tax_in END AS price FROM Items 3) SELECT 구를 사용한 조건 분기의 실행 계획 테이블 1회 접근으로 끝난다 구문 => 식, UNION => CASE ch 9. SELECT 구를 사용한 조건 분기의 실행 계획 1) 집계 대상으로 조건 분기 UNION을 사용한 방법 1 2 3 4 5 6 7 8 9 SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom FROM(SELECT prefecture, pop AS pop_men null AS pop_wom) FROM Population WHERE sex = '1' # 남성 UNION SELECT prefecture, null AS pop_men, pop AS pop_wom FROM Population WHERE sex = '2') TMP # 여성 GROUP BY prefecture 풀스캔이 2회 수행된다. ...

인용하는 그림은 다양한 곳에서 가져왔음을 밝힙니다 1. SQL 기초 ch 6. SELECT 구문 1) SELECT 구와 FROM 구 SELECT 1 처럼 상수를 선택하는 경우 FROM이 필요없다. )2) WHERE 구 WHERE 구의 조건이 많을 경우 IN 으로 대체 SELECT 구문은 테이블을 반환하는 읽기 전용 함수 이다. 3) GROUP BY 구 일부 DBMS에서는 지원하지 않음 4) HAVING GROUP BY에 조건을 걸때 WHERE가 레코드에 조건을 거는 것이라면, HAVING은 집합에 조건을 거는 것 5) ORDER BY 6) 뷰와 서브쿼리 자주 사용하는 SELECT 구문을 DB에 저장 = 뷰(view) 뷰는 내부에 데이터러 보유하지 않음(SELECT 구문을 저장할 뿐) 서브쿼리의 실행과 동일하다 WHERE 조건에 서브쿼리를 거면 조건이 바뀌어도 문제없음(조건을 하나하나 하드코딩하는 번거로움을 없앨수있다) ch 7. 조건 분기, 집합 연산, 윈도우 함수, 갱신 1) SQL과 조건 분기 SQL의 조건 분기는 CASE식을 통해 한다. SQL의 조건 분기는 특정한 값을 리턴하는 것이 특징이다. CASE는 식이기 때문에 활용성이 높은 것이 강점이다. 2) SQL의 집합 연산 UNION : 합집합(기본적으로 중복을 제거) INTERSECT : 교집합 EXCEPT : 차집합 3) 윈도우 함수 집약 기능이 없는 GROUP BY 구 PARTITION BY SELECT 구에만 사용됨 윈도우 전용함수로 RANK, ROW_NUMBER가 있다. 4) 트랜잭션과 갱신 INSERT, UPDATE, DELETE

인용하는 그림은 다양한 곳에서 가져왔음을 밝힙니다 1. DBMS 아키텍처 ch 1.DBMS 아키텍처 개요 1) 쿼리 평가 엔진 SQL 구문을 분석하고, 어떤 순서로 기억장치의 데이터에 접근할지 결정 = 실행 계획(Explain plan) Access Method : 실행 계획에 기반을 둬서 데이터에 접근하는 방법 핵심 모듈이며 성능과도 깊은 관련이 있다 2) 버퍼 매니저 특별한 용도로 버퍼라는 메모리 영역을 확보해둔다 디스크 용량 매니저와 함께 작동한다 3) 디스크 용량 매니저 데이터를 어디에/어떻게 읽고 쓸지 관리 4) 트랜잭션 매니저와 락 매니저 동시 접근에 대한 안전성 제공 5) 리커버리 매니저 절대 잃어버리면 안되는 데이터를 장애로 잃었을시에 복구해주는 기능 ch 2. DBMS와 버퍼 1) 공짜 밥은 존재할까? 기억장치에 따른 트레이드오프, 무조건 좋은건 없다 2) DBMS가 기억장치의 관계 DBMS는 데이터 저장을 목적으로 하는 미드뤠어 이며, 사용하는 대표적인 기억장치는 다음과 같다 (1) 하드디스크(HDD) DBMS가 데이터를 저장하는 대부분 (2) 메모리 성능 향상을 위해 올려둠, I/O를 줄이기 위해 (3) 버퍼를 활용한 속도 향상 성능 향상을 목적으로 데이터를 저장하는 메모리를 버퍼, 캐시 버퍼 매니저 : 버퍼에 데이터를 어떻게, 어느 정도 기간 동안 올릴지 결정 3) 메모리 위에 있는 두 개의 버퍼 DBMS가 데이터를 유지하기 위해 사용하는 메모리는 크게. 1. 데이터 캐시, 2. 로그 버퍼 MySQL DBMS의 버퍼 메모리와 제어 매개변수 명칭 버퍼 풀 매개변수 innodb_buffuer_pool_size 초기값 128MB 설정값확인 SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’ 명칭 로그 버퍼 매개변수 innodb_log_buffer_size 초기값 8MB 설정값확인 SHOW VARIABLES LIKE ‘innodb_log_buffer_size’ (1) 데이터 캐시 디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는 영역 SELECT 하는 데이터가 캐시 영역에 있다면 빨라짐 (2) 로그 버퍼 갱신 처리(INSERT, DELETE, UPDATE, MERGE)와 관련 갱신 관련 SQL이 오면 로그버퍼에 변경 정보를 보내고 이후에 디스크 변경을 수행(비동기 처리) 4) 메모리 성질이 초래하는 트레이프오프 (1) 휘발성 장애 발생시 데이터가 날라가버려 부정합이 발생한다. 커밋 시점에 갱신정보를 로그 파일(HDD에 존재)에씀으로 정합성 유지 반대로 커밋시에는 디스크에 동기 저근이 일어나기 때문에 지연이 발생할 가능성이 높아진다 5) 시스템 특성에 따른 트레이드오프 (1) 데이터 캐시와 로그 버퍼의 크기 기본적으로 데이터 캐시에 비해 로그 버퍼의 초기값이 굉장히 작다. 그 이유는 주된 작업이 검색이라고 가정하기 때문이다. (2) 검색과 갱신 중에서 중요한 것 6) 추가적인 메모리 영역 ‘워킹 메모리’ (1) 언제 사용될까? 2개의 버퍼 이외에도, 일반적인 메모리 영역을 하나더 가지고 있다 정렬 또는 해시 관련 처리에 사용되는 작업용 영역이다. 정렬은 ORDER BY, 집합 연산, 원도우 함수 해시는 테이블 결합시 MySQL 에서는 정렬 버퍼, sort_buffuer_size = 257kb 이 영역이 성능적으로 중요한 이뉴는, 이 영역의 양이 부족하며 저장소를 사용하기 때문이다(Swap) (2) 부족하면 무슨일이 일어 날까? ch 3. DBMS와 실행 계획 1) 권한 이양의 죄악 절차가 기초가 되는 언어는 (C, 자바, 루비)는 절차를 책임지고 기술하지만, RDB는 대상(what)을 기술하기 때문에 그 외 작업은 DBMS에 맡겼다. 2) 데이터에 접근하는 방법은 어떻게 결정할까? ...