1 / 33

이 정 무 의료정보연구소장 jeongmoo_lee@mdsaver MDSaver

SQL Server 2000 튜닝. 이 정 무 의료정보연구소장 jeongmoo_lee@mdsaver.net MDSaver.net. Agenda. 튜닝에 대한 명확한 이해 인덱스에 대한 이해와 적용 RDBMS 에 대한 이해와 제대로 된 SQL 작성하기. 1. 튜닝에 대한 명확한 이해. A. 튜닝이란 ? 요청한 Data 관련 작업을 처리하기 위해 최적의 I/O 처리 및 프로세스 처리 환경을 만들어 주는 것이다 . B. 튜닝은 누가 하는 것인가 ? 옵티마이져 ? 사람 ? C. 튜닝의 영역

maris
Download Presentation

이 정 무 의료정보연구소장 jeongmoo_lee@mdsaver MDSaver

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2000 튜닝 이 정 무의료정보연구소장 jeongmoo_lee@mdsaver.netMDSaver.net

  2. Agenda • 튜닝에 대한 명확한 이해 • 인덱스에 대한 이해와 적용 • RDBMS에 대한 이해와 제대로 된 SQL 작성하기

  3. 1. 튜닝에 대한 명확한 이해 A. 튜닝이란? • 요청한 Data 관련 작업을 처리하기 위해 최적의 I/O 처리 및 프로세스 처리 환경을 만들어 주는 것이다. B. 튜닝은 누가 하는 것인가? • 옵티마이져? • 사람? C. 튜닝의 영역 • 프로그램 관련 • 처리 로직의 튜닝 • 관련 SQL 튜닝 • 시스템 관련

  4. 10000 10 10000 * 100byte  1000M 10000 * 10 10 * 10 10000 * 10 * 100byte  10000M 10 10 10 * 100byte  1M 10 * 10 10 * 10 10 * 10 * 100byte  10M 처리 로직 수정을 통한 튜닝 예 DB Server Web Server Web Server DB Server DB Server Web Server DB Server Web Server

  5. SQL 수정을 통한 튜닝 예 declare @member table( m_id varchar(10) , name varchar(20) , salery int) declare @m_id varchar(10),@name varchar(20),@salery int,@total int DECLARE member_cursor CURSOR FOR SELECT m_id, name, salery FROM member OPEN member_cursor FETCH NEXT FROM member_cursor INTO @m_id, @name, @salery WHILE @@FETCH_STATUS = 0 begin select @total = sum( case l_id when 1 then 10 when 2 then 5 when 3 then 15 end) from act where m_id = @m_id select @salery = @salery + isnull(@total,0) insert @member values( @m_id,@name,@salery) FETCH NEXT FROM member_cursor INTO @m_id, @name, @salery end select * from @member CLOSE member_cursor DEALLOCATE member_cursor select m.m_id,m.name,salery + isnull(Totalbonus,0) as bonus from member m left outer join ( select m_id,sum( case l_id when 1 then 10 when 2 then 20 when 3 then 30 end ) as Totalbonus from act where aday between '20011001' and '20011031' group by m_id ) bonus on m.m_id = bonus.m_id

  6. OPTIMIZER란 무엇인가? OPTIMIZER 결과 Select colA, colB,count(colc) from TabA where colD = 'A01' group by colA,colB SQL 해석 실행 계획 수립 실행 참조 Table, Index View, Function… 가장 최소의 비용으로 처리하는 방법을 찾기 위해 사용할 수 있는 정보(인덱스, 클러스터, 데이터 크기, 각종 통계 자료 등)를 참조하여 내부 알고리즘 규칙에 따라 최적의 방안을 찾아내는 역할. IPTIMEZER는 주어진 조건에서 최적의 방안을 찾아낸다. 주어진 조건을 만드는 것은?  사람

  7. RDMS의 이해

  8. DBMS 의 역사 1960 년대 : 계층형 DBMS (HDB) 1970 년대 : 망형 DBMS (NDB) 1970 Dr. E. F. Codd. “A relational Model for Large Shared Data Banks” 1970년대 중반 Inverted List DBMS상품 : ADABAS, SYSTEM2000 1979 최초의 상용 RDBMS 출연 1986 ANSI SQL 표준 제정 1990 OLTP 사용가능 1994 분산 DB, 병렬처리 등 출현 1997 ORDBMS, OODBMS

  9. DBMS의 특징들 • 메터데이터에 데이터의 관계나 특성들을 기록 - 모든 처리는 DBMS를 통해야 한다 • 사용자 중심의 데이터 처리 - 데이터간의 복잡한 관계들을 DBMS가 처리. 데이터 처리가 용이 • DBMS가 시스템으로부터 구조적 종속과 데이터 종속을 제거 - 데이터베이스 파일에서 발생하는 변경 사항들은 데이터 사전에 자동적으로 기록됨으로써, 변경된 파일에 관계된 모든 프로그램들을 수정해야 하는 부담을 줄임. • 동시성 제어 기능 • 장애가 발생했을 때 복구 시키는 복구(Recovery) 기능 • 사용자의 질의를 효율적으로 처리하는 질의 처리 기능(Query Processing) 기능 • 데이터베이스에 대한 접근의 통제 및 개인 정보를 보호하는 보안(Security) 기능

  10. DBMS 종류(계층형 데이터베이스)-예 Root Segment A Root 1 Segment Root chilren B C Root 2 Segment Root 1 chilren D E F G H Root 3 Segment Root 2 chilren I J 계층형 경로로 접근 : A B E J • 장점 • 일대다(1:M) 관계의 표현이 용이 • 데이터 무결성 처리가 용이 • 단점 • 데이터 저장의 물리적 구조에 대한 지식을 요구 • 데이터베이스 구조상의 변경은 이 구조를 참조했던 모든 응용프로그램들에 대한 수정을 요구 • 계층형 모델은 일대다의 기준에 맞지 않는 일반적인 관계는 구현하기가 매우 어렵다.

  11. 교수번호 학번 학번 과목번호 이름 과목번호 이름 과목명 학과 학점수 성적 학년 DBMS 종류(망형 데이터베이스)-예 교수 강의 지도 학생 과목 신청 개설 수강 Relationship지도 OWNER 교수 MEMBER 학생 …강의, 신청, 개설 엔터티 교수(교수번호,이름,학과) 엔터티 학생(학번,이름,학년) 엔터티 과목(과목번호, 과목명, 학점수) 엔터티 수강(학번, 과목번호,성적) • 장점 • 다대다(m:n) 관계가 계층형 모델보다 더 쉽게 구현 • 데이터 접근에 대한 유연성이 계층형보다 우수 •  단점 • 계층형 모델과 거의 비슷 : 근본적인 문제 해결이 안 됨.

  12. 사원 *사원번호 이름 주소 본봉 선행리스트 *리스트번호 선행내용 보너스 선행한 일을 하다 선행한 일 DBMS 종류(관계형 데이터베이스) - 예 회사는 직원들을 급여, 주소, 이름 등의 정보를 관리한다. 회사에서는 선행 제도에 대한 보너스 제도가 있다. 선행 리스트에 있는 일을 했을 때 그에 적용된 보너스를 한 달 단위로 합계를 내어서 기본 급여에 더해서 지급한다.

  13. 관계형 대수 Product Restrict Project A B C x y A A B B C C X Y X Y X Y Join Intersection Difference Union a1 a2 a3 b1 b1 b2 b1 b2 b3 c1 c2 c3 a1 a2 a3 c1 c1 c2 Divide a a a b c x y z x y x z a

  14. 관계형 데이터베이스의 장점 • 모델 자체가 간단하다 - 이차원 구조, 엔터티와 관계(Relationship)을 이용한 논리적 구성… • 수학적인 이론의 바탕 위에서 설계 - 수학의 집합론의 ‘관계형 이론’, 옵티마이져, 인덱싱.. • SQL의 존재 - 간단하고 효율적인 질의어.. - 업계 표준화(SQL/89,SQL/92,SQL/1999) • 90년대 이후 정보 시스템의 요구 충족할 만큼 기술적인 발전 - 병렬 처리,C/S 등)

  15. INDEX의 이해와 활용

  16. 인덱스의 필요성 관계형 모델은 동적 모델이다. ( OPTIMIZER의 역할은?) 관계형 모델에서 특정 데이터에 바로 접근 할 수 없다.- 포인터가 없다. Set 단위(Column 단위) 작업만 하고 싶다. 조인과 인덱스의 관계는? 인덱스는 RDB의 접근의 한계를 극복할 수 있는 최고의 대안이다. 물리적 저장 구조는 테이블 구조가 아니다. -> 파일

  17. Martin ... 470801 Con ... 470401 Akhtar ... 470601 Phua ... 470802 Funk ... 470402 Funk ... 470602 Jones ... 470803 White ... 470403 Smith ... 470603 Smith ... 470804 Rudd ... 470501 Martin ... 470604 Ganio ... 470901 White ... 470502 Smith ... 470701 Barr ... 470503 Ota ... 470702 Jones ... 470902 Where Index? 1. 특정 값에 접근 하고자 할 때 인덱스 알고리즘을 통해 해당 값의 위치를 알고 그 위치로 가서 바로 값을 가져오는 방식. 2. 작업 단위를 테이블이 아닌 Column 단위로 하는 것이 효율적이다 ( Join, Column 단위 작업) Project Project

  18. Akhtar … Jordan Akhtar … … Jordan … Lang Chai … … … … Dunn Morgan … … Smith Ganio … … Akhtar Chai … … Morgan Smith … … Con Barr … … Smith Nash … … Con Barr … … Nay Smith … … Funk … Borm Cox … … Ota Smith … … Dale Buhl … … Rudd Smith … … 인덱스를 이용한 특정 값 접근 SELECT lastname, firstname, age, sex FROM member WHERE lastname = ‘Funk’ Create index n_in on member(lastname, firsname) Index Pages Non-Leaf Level Leaf Level(Key Value) Dunn … Ganio … Jordan … Lang … Dunn … Hall … Kim … Martin … Fine … Hart … Kim … Martin … Fort … Jones … Koch … Martin … Jones … Koch … Moris … Data Pages

  19. Akhtar … Akhtar Jordan … … Jordan … Lang Chai … … … … Morgan Dunn … … Smith Ganio … … Dunn … Ganio … Jordan … Lang … Dunn … Hall … Kim … Martin … Fine … Hart … Kim … Martin … Fort … Jones … Koch … Martin … Akhtar Chai … … Smith Morgan … … Funk … Jones … Koch … Moris … Barr Con … … Smith Nash … … Barr Con … … Nay Smith … … Cox Borm … … Ota Smith … … Buhl Dale … … Smith Rudd … … 인덱스를 이용한 Column 단위 작업 Create index n_in on member(lastname, firsname) USE credit SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Funk' AND 'Lang' Index Pages Non-Leaf Level Leaf Level(Key Value) Data Pages

  20. id indid = 2 root Martin Akhtar Akhtar Ganio Ganio Martin ... ... Akhtar Akhtar 4:706:01 4:706:01 Ganio Ganio Smith Smith 4:709:01 4:709:01 4:706:03 4:706:03 Barr Barr 4:705:03 4:705:03 Smith Hall Hall Smith 4:708:04 4:709:04 4:709:04 4:708:04 Con Con 4:704:01 4:704:01 Jones Jones Smith Smith 4:709:02 4:709:02 4:707:01 4:707:01 Matey 4:706:04 Funk Funk 4:706:02 4:706:02 Jones White Jones White 4:708:03 4:704:03 4:708:03 4:704:03 Funk Funk 4:704:02 4:704:02 White Jones Jones White 4:707:03 4:705:02 4:705:02 4:707:03 Ota Phua 4:707:02 4:708:02 Rudd 4:705:01 01 01 ... ... Conn Conn 01 01 01 ... ... ... Rudd Rudd Rudd 01 01 ... ... Smith Smith 01 01 ... ... Martin Martin 01 01 ... ... Ganio Ganio 02 02 ... ... Funk Funk 02 02 ... ... White White 02 02 ... ... Ota Ota 02 02 02 02 ... ... ... ... Phua Ota Phua Phua 02 02 ... ... Jones Jones 03 03 ... ... White White 03 03 ... ... Barr Barr 03 03 ... ... Jones Jones 03 03 ... ... Jones Jones 03 03 ... ... Hall Hall ... ... ... ... ... ... ... ... ... ... ... ... 04 ... Matey ... ... ... ... ... ... 04 04 ... ... Smith Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Finding Rows in a Heap with a Nonclustered Index sysindexes Non clustered Index Non Clustered Index Non-Leaf Level Non-Leaf Level Akhtar Akhtar ... ... SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Masters' AND 'Rudd' Martin Martin Page 37 Page 37 Page 28 Page 28 Page 12 - Root Page 12 - Root Martin Martin Smith Smith ... ... Leaf Level(Key Value) Leaf Level(Key Value) Page 41 Page 41 Page 51 Page 51 Page 61 Page 61 Page 71 Page 71 Martin Martin 4:708:01 4:708:01 Matey Matey 4:706:04 4:706:04 Ota Ota 4:707:02 4:707:02 Phua Phua 4:708:02 4:708:02 Rudd Rudd 4:705:01 4:705:01 Heap Heap Page 704 Page 704 Page 705 Page 705 Page 706 Page 706 Page 707 Page 707 Page 808 Page 808 Page 709 Page 709 01 01 ... ... Akhtar Akhtar 02 02 ... ... Funk Funk 03 03 ... ... Smith Smith 04 04 ... ... Matey Matey ... ... ... ... ... ... File ID #4 File ID #4 Data Page

  21. id indid = 1 root sysindexes Akhtar Akhtar … … Martin Martin Akhtar Akhtar Ganio Ganio … … Akhtar Akhtar 2334 2334 ... ... Ganio Ganio 7678 7678 ... ... Martin Martin 1234 1234 ... ... Smith Smith 1434 1434 ... ... Barr Barr 5678 5678 ... ... Hall Hall 8078 8078 ... ... Martin Martin 7778 7778 ... ... Smith Smith 5778 5778 ... ... Con Con 2534 2534 ... ... Jones Jones 2434 2434 ... ... Ota Ota 5878 5878 ... ... Smith Smith 7978 7978 ... ... Funk Funk 1334 1334 ... ... Jones Jones 5978 5978 ... ... Phua Phua 7878 7878 ... ... White White 2234 2234 ... ... ... ... ... ... ... ... ... ... Funk Funk 1534 1534 Jones Jones 2634 2634 Rudd Rudd 6078 6078 White White 1634 1634 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Ota 5878 ... Finding Rows in a Clustered Index Clustered Index Clustered Index Non-Leaf Level Martin Page 140 - Root Page 140 - Root SELECT lastname, firstname FROM member WHERE lastname = 'Ota' Martin Martin Martin Smith Smith … … Leaf Level Page 141 Page 141 Page 145 Page 145 Page 100 Page 100 Page 110 Page 110 Page 120 Page 120 Page 130 Page 130 Data Page

  22. Finding Rows in a Clustered Index with a Nonclustered Index id indid = 2 root Aaron Aaron ... ... Jose Jose Aaron Aaron Jose Jose Deanna Deanna Nina Nina … … … … Aaron Aaron Con Con Jose Jose Lugo Lugo Deanna Deanna Daum Daum Adam Adam Barr Barr Judy Judy Kaethler Kaethler Don Don Hall Hall Amie Amie Baldwin Baldwin Mike Mike Mike Nash Nash Nash Doug Doug Hampton Hampton … … … … … … … … … … … … Barr Barr Kim Kim Nagata Nagata O’Melia O’Melia Barr Barr Adam Adam … … Kim Kim Shane Shane … … Nagata Nagata Susanne Susanne … … Cox Cox Arlette Arlette … … Kobara Kobara Linda Linda … … Nash Nash Nash Mike Mike Mike … … … Daum Daum Deanna Deanna … … LaBrie LaBrie Ryan Ryan … … Nixon Nixon Toby Toby … … … … … … … … … … … … … … … … … … … … sysindexes Nonclustered Index on First Name Nonclustered Index on First Name Non-Leaf Level Non-Leaf Level SELECT lastname, firstname, phone FROM member WHERE firstname = 'Mike' Leaf Level(Clustered Key Value) Leaf Level(Clustered Key Value) Clustered Index On Last Name Clustered Index On Last Name Nagata Non Leaf Level Data Page & Leaf Level

  23. INDEX CLUSTER Cluster Cluster Key Header Index Rowid column Rowid Columns Rowid Columns CLUSTERD INDEX INDEX TABLE TABLE 111 1 . . . . . . . . . 3 BB . . . . . 111 3 . . . . . . . . . . . . . . . . . 1 AB 123 . . . . . . 123 10 10 Cluster Header 4 CA 354 . . . . . . . . . . . 123 1 . . . . 1 AB . . . . . 12 BS . . . . . 10 BD . . . . . . . . . . . . . . 99 DD . . . . . 123 10 12 BS 123 . . 999 . . 123 12 3 BB 217 . . . . . . . 10 BD 123 . . 123 99 9 CS 5 . . . . . . . . . . . . . . . . . . . . . . . . 99 DD 123 . . . . . . . . . . . . . . . . . . . . . . . . 999 . . . 클러스터(클러스터드 인덱스) 와 인덱스 비교 Data Page를 특정 Column Sort 순서로 저장하자  클러스터는 인덱스 개념을 데이터 페이지에 적용한 개념 • 엑세스 기법이 아닌 물리적인 저장 기법 • 넓은 범위의 엑세스와 조인의 수행 속도를 높임 • Insert, Update, Delete 시에는 많은 과부하 • 적절한 클러스터링은 저장 공간을 절약

  24. Indexing Guidelines • 인덱스 고려 Column - Primary and Foreign Key - 자주 범위를 주고 검색 하는 컬럼 - 자주 Sort 된 순서로 검색을 하는 경우 - 자주 집계 동안 그룹 형식으로 작업하는 컬럼 • 인덱스를 피해야 하는 Column - 거의 쿼리에 사용되지 않은 컬럼 - 유일 값들이 거의 없는 경우 - Text.. 인덱스는 전략은 단순 규칙에 의해서 결정할 수 없다. 해당 테이블과 해당 테이블에 요청하는 SQL를 종합적으로 판단하여 최적의 전략 수립을 해야 한다.  그러기 위해서 인덱스에 대한 명확한 이해와 유연한 사고가 필요하다.  잘 만들어진 인덱스는 열 메모리 안 부럽다(?)

  25. 클러스터(클러스터드 인덱스)의 선정 기준 • 다량의 범위를 자주 엑세스해야 하는 경우 • 인덱스를 사용한 처리가 부담이 되는 넓은 분포도 • 여러 개의 테이블이 빈번한 조인을 일으킬 때 • 반복 컬럼이 정규화 작업에 어쩔 수 없이 분할 된 경우 • Union, Distinct, Order by, Group by • 수정이 자주 일어 나지 않는 컬럼 • 처리 범위가 넓어 문제가 발생한 경우는 단일 테이블 클러스터링 • 조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링 • !!!!! 클러스터는 꼭 필요한 경우에만!!!!

  26. 클러스터의 고려 사항 • 데이터 처리(Insert, Update,Delete)에 오버헤드 발생 주의 • 인덱스로도 충분한 범위는 클러스터링 효과가 없슴 –오히려 악재 • 클러스터링 키는 수정이 빈번하지 않아야 한다. • 인덱스와 상호 협조적인 결합 • 클러스터 구성 시 인덱스 수 감소 요인(감소 해야) • 클러스터 고려 시 Column 길이도 중요한 고려 사항 • 클러스터는 분포도 고를 것 주의 사항 : SQL Server 2000의 경우 Primary Key 선택 시 자동적으로 해당 컬럼에 클러스터드 인덱스가 설정이 되는데 이 경우 클러스터 선정 기준을 고려해야 꼭 필요한 상황이 아니라면 Unique Index를 설정하여 클러스터드 인덱스가 남발이 되지 않도록 해야 한다.

  27. Access method Page I/O Table scan 10,417 Clustered index on the charge_amt column 1042 Nonclustered index on the charge_amt column 100,273 Composite index on charge_amt, charge_nocolumns 273 인덱스 전략에 따른 성능 차이 SELECT charge_noFROM chargeWHERE charge_amt BETWEEN 20 AND 30

  28. 인덱스를 이용한 Data Fragmentation 해결 • How Fragmentation Occurs • SQL Server reorganizes index pages when data is modified • Reorganization causes index pages to split • Methods of Managing Fragmentation • Drop and recreate an index and specify a fillfactor value • Rebuild an index and specify a fillfactor value • Business Environment • Data fragmentation can be good for OLTP environment • Data fragmentation can be bad for Analysis Services environment

  29. 제대로 된 SQL 작성하기

  30. SQL이 무엇인가? SQL의 존재 - 간단하고 효율적인 Query Language - 관계형 모델을 구현 - 개발자나 사용자가 쉽게 익힐 수 있다 : 대중화 - 업계 표준화(SQL/89,SQL/92,SQL/1999) - ISO는 그냥 SQL 수학적인 이론의 바탕 위에서 설계 - 수학의 집합론의 ‘관계형 이론’ - 이차원의 행렬(Matrix) 구조를 수학적으로 정의 - 명확한 이론적 기반을 제공 - 다양한 수학적 이론을 통한 기술 개발 가능 : 옵티마이져, 인덱스..

  31. 제대로 된 SQL를 작성하려면? • 항상 I/O를 생각하라 • 결과만 중요한 것이 아니라 처리 과정 역시 중요하다. • RDBMS는 집합 이론에서 출발한 것이다. 즉 데이터 처리는 집합 단위로 처리가 되어야 한다. • SP, View, Trigger, Cursor 등의 명확한 정의와 사용 목적을 이해하라. 그리고 해당 로직 구현에 왜 필요한 것인지 판단하라. • OPTIMIZER는 주어진 조건에서 최선의 처리 방안을 찾는 것이다. 주어진 조건은 결국 사람이 만드는 것이다.

  32. SQL 작성 예- Cursor와 Case declare @member table( m_id varchar(10) , name varchar(20) , salery int) declare @m_id varchar(10),@name varchar(20),@salery int,@total int DECLARE member_cursor CURSOR FOR SELECT m_id, name, salery FROM member OPEN member_cursor FETCH NEXT FROM member_cursor INTO @m_id, @name, @salery WHILE @@FETCH_STATUS = 0 begin select @total = sum( case l_id when 1 then 10 when 2 then 5 when 3 then 15 end) from act where m_id = @m_id select @salery = @salery + isnull(@total,0) insert @member values( @m_id,@name,@salery) FETCH NEXT FROM member_cursor INTO @m_id, @name, @salery end select * from @member CLOSE member_cursor DEALLOCATE member_cursor select m.m_id,m.name,salery + isnull(Totalbonus,0) as bonus from member m left outer join ( select m_id,sum( case l_id when 1 then 10 when 2 then 20 when 3 then 30 end ) as Totalbonus from act where aday between '20011001' and '20011031' group by m_id ) bonus on m.m_id = bonus.m_id

  33. SQL 작성 예- Distinct 와 group by 아래 Query 문에 어떤 문제가 있는가? Select distict colA, colB,count(colc) from TabA where colD = 'A01' group by colA,colB

More Related