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

최근 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.8 네트워크 융합 전화와 정보 기술의 통합을 의미 애플리케이션 융합 기업의 최종 사용자 관점이다. 음성 통화, 음성 메일, 이메일, 인스터스 메신저와 같은 통신 애플리케이션을 작업 그룹 공동 작업, 고객 관계 관리(CRM : Customer Relationship Manager), 백오피스 (금융 기관의 후선 지원 입무) 기능 등의 비즈니스 애플리케이션과 통합한다. 융합된 애플리케이션은 체계적으로 통합한 음성, 데이터, 동영상에서 부가가치를 만드는 다채로운 기능을 제공한다. ex) 멀티미디어 메신저 기업 서비스 정보 네트워크를 다루는 관리자의 관점이다. 네트워크 관리자는 융합 애플리케이션을 위한 개인 인즉 정보 정책과 인증서비스를 제공한다. 모바일 사용자의 원격 프린트와 네트워크 저장 장치 서비스를 위해 사용자 위치 파악 애플리케이션 서비스를 위한 공동 환경 설정, QoS 인프라 스트럭처 기업의 통신 선로, LAN, WAN, 인터넷 회선 등으로 구성된다 사설/공용 클라우드로부터 대용량 데이터 스토리지와 웹 서비스를 호스팅 하는 데이터 센터까지의 연결도 포함하는 추세이다 핵심은 데이터 트래픽을 보내도록 설계된 네트워크와 음석 트래픽용 네트워크 간의 융합이다 간단히 기업의 음성, 동영상, 이미지 트래픽을 단일 네트워크 인프라 스트럭처로 보내는 것이다 융합의 기반은 IP 패킷 전송 기술이다 이점으로는 네트워크 관리의 단순화, 효율성의 증가, 애플리케이션의 유연성 증가(동영상, 데이터, 음성을 활용한 새로운 애플리케이션 발견 가능) 등이 있다 비용 절감 효율성 변화하기 쉬움 단일 장애점을 같는 문제가 있다. 이를 보완하기 위해 각 구성 요소와 백업 시스템을 다중화한다. 1.9 통합 커뮤니케이션(UC Unified Communication) 네트워크 통합에 연관된 개념 UC는 비즈니스 프로세스 최적화를 위한 실시간 커뮤니케이션 서비스의 통합에 초점을 둔다 마찬가지로 IP기술이 기초가 된다 핵심 다양한 장비와 미디어에 통합된 UI/UX를 제공 실시간 커뮤니케이션 서비스와 비실시간 서비스 및 비즈니스 프로세스 애플리케이션을 통합한다. 장점 개인 생산성 향상 작업 그룹성과 개선 기업 프로세스 개선 ...

인용하는 그림은 다양한 곳에서 가져왔음을 밝힙니다 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) 데이터에 접근하는 방법은 어떻게 결정할까? ...