Posts

오픽따려고 유폰을 약 한달전부터 시작했습니다. 어제까지 10회를 마무리했는데 오늘 테스트가 있어서 그 동안의 내용 좀 복습합니다. Level 5 ~ 6 열번하고 정리한 거 바람이 분다 It’s a lot of wind -> It’s windy ...

오늘안에 sql 튜닝 포스팅 모두 끝내고 싶어서 버티면서 작업하다 보니 벌써 4시가 넘었네요.. ㅋㅋ 내일 출근인데 후 얼른 자야지 굿밤~

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. 채번 테이블 순번을 부여하기 위해 어플리케이션에서 채번 테이블이라는 것을 만들어 사용했었다. 구시대 유물이며 문제가 안생기기를 바라는 것이 최선(튜닝할 방법도 없다)

겉으로 보기에 바뀐것은 없어 보이지만 내부적으로 몇가지를 수정했습니다. 1. 빌드 및 deploy 자동화 현재까지 블로그에 새로운 글을 올리기 위한 과정을 다음과 같았습니다. 새로운 포스트를 추가한다. 빌드 한다. 블로그 github에 푸쉬한다. 이 중에서 23번 과정을 자동화 시켰습니다. 새로운 포스트를 추가하고 빌드용 repository에 push하기만 하면 빌드가 되고 블로그용 repository에 push가 되게 수정되었습니다. 이 과정에서 Travis CI 라는 사이트를 이용했습니다. 제일 고생한게 trivis ci에서 어떻게 빌드할지에 대한 스크립트인 .travis.yml 에서 hugo 설치였습니다. 구글에서 검색한 스크립트를 써도 잘안되더라고요. go get github 뭐 이렇게 시작하는 스크립트들이 많아서 따라해봤는데 저는 잘안됐습니다. 그래서 그냥 wget으로 dep받아서 설치하게 작성했습니다. 단순하지만 가장 확실한 방법! ...

자동으로 빌드되어 올라가는지 테스트

오랜만의 근황이네요. 요즘에 회사에 일도 많고, 고민도 많아요. 선택이란게 참 쉬운게 아닌거 같아요. 머리아프지만 잘 해결해 나가려고 노력중이에요. 업무외에도 공부는 계속하고 있는데, 요약한걸 타이핑해서 올리는 것도 일이라 요즘 못올리고 있네요. 시간날 때 몰아서 올려야겠어요 ...

최근 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의 경우 레코드수가 줄기 때문에 더 나은 선택일 수 있다(사전에 결합 레코드수 압축)