[MySQL 쿼리 및 인덱스의 이해]Explain 정보에 대한 이해

Explain은 어떤 경우 사용하는가?
 - 하나 또는 그 이상의 테이블에 대해서 MySQL이 쿼리를 어떻게 처리하는지 이해하기 위해 사용
 - 5.0.1이후부터는 Last query cost항목이 추가되어 비교가 편리
 
Explain을 통한 Select문의 최적화
 - 구문
   mysql>EXPLAIN SELECT ...... \G
  
 - 세가지 질문에 대한 답(중요!)
   어떠한 순서로 테이블을 읽는가?
   어떠한 방법으로 읽어오나?
   얼마나 많은 Row를 읽어오나?

 

 - 최적화 방법
   쿼리재작성(서브쿼리는 조인으로)
   가능한 많은 조건절
   테이블 읽어오는 순서 조절(작은 테이블 우선)
   인덱스를 사용하도록 조정(use index, ignore index)
   필드에 새로운 인덱스를 추가

  
Explain 결과필드

 

 - 결과필드 설명
  Table : explain에서 읽은 테이블
  Id : 처리될 순서
  Select_Type : 처리될 select의 타임(simple,union..)
  Type : 조인을 수행하는 타임
  Possible_Key : 사용할 수 있는 키
  Key : 옵티마이저가 사용할 인텍스 수
  Key_length : 사용할 인덱스 길이
  Ref : 선택된 인덱스를 통해 가져오게될 컬럼
  Rows : 해당 쿼리가 테이블에서 검색하게될 Row수
  Extra : 옵티마이저가 제공하는 추가적인 정보
 

Explain에서의 Type

 - Const : 최상의 경우, 결과값을 찾기 위해서 단 하나의 값만 찾으면 되는 경우(예:Unique한 데이터 검색)
 - Eq_ref : 조인시 최상의 경우, 이전 테이블에서 찾은 값중 단 하나의 값만 해당 테이블에 존재하는 경우(예:둘다 Unique)
 - Ref/ref_of_null : 인덱스에 매치되는 값이 많지 않은 경우 나쁘지 않음, 이전 테이블에서 찾은 값중 몇 개의 값을 찾는 경우
 - Index_merge : 두개이상의 인덱스를 사용할 경우, 복합인덱스는 Merge가 되지 않는다
 - Unique_subquery : 서브쿼리에서 나온 값이 유니크한 키값일 경우
 - Index_subquery : 서브쿼리에서 인덱스는 사용하나 유니크는 아님
 - Range : 주어진 범위의 값을 사용, 검색의 범위와 인덱스의 커디널리티에 영향을 받음
 - Index : 모든 인덱스를 스캔하는 경우, 인덱스 스캔한다는 것을 제외하면 All과 같음
 - All : 전체테이블 스캔
 

Extra 필드에서 보이면 좋은것
 - UsingIndex : 매우 좋은 경우, 실제테이블을 읽지 않고 인덱스에만 처리
 - Usingwhere : 만약 where가 없었다면 전체테이블 스캔
 - Distinct : 이전 테이블과 조합되는 경우가 하나씩 밖에 없는 경우
 - Not exists : left join최적화를 사용할 수 있으면 많은 row는 남겨 놓을 수 있는 경우

 

 

Extra 필드에서 보이면 좋지 않은것
 - Usingfilesort : 추가적인 sort작업이 필요
 - Usingtemporary : temp테이블이 필요, OrderBy와 GroupBy에 해당하는 컬럼이 다를 경우
 - Range check for echo record : range쿼리가 nonselective인덱스(순차적이지않은)와 조합되는 경우

 

[MySQL 쿼리 및 인덱스의 이해]쿼리 옵티마이저

쿼리 수행경로

   일반쿼리 > Update,Delete일경우       > Parser > Optimizer > Excutor > QueryCache
              > Select일경우 > QueryCache > Parser
   mysql_prepared_statment                              > Optimizer

 - Parser는 질의를 바이너리형태로 변경해서 Optimizer에 전달
 - Prepared는 QueryCache를 현재는 사용하지 않음.
 - Prepared는 10%정도의 성능향상이 있으나 Select에서 캐쉬를 이용하지 않으므로 업데이트나 입력이 많을때만 사용
 - Executor은 Having, Order By, Group By, Limit 등의 존재여부에 따라 공간 할당
 - 처리된 결과는 QueryCache에 저장됨

옵티마이저란?
 - 가장 빠른 길을 찾아가는 네비게이션
 - MySQL은 Cost-Based(비용기반)사용. 예전 DB들은 Rule_Based(룰기반)를 사용
 - 옵티마이저는 항상 최적의 답을 찾는것은 아님
 - 질의문의 Logic Fomula를 Data statistics와 Metadata를 참고해서 최적의 방법을 도출
 

비용기반 옵티마이져
 - 비용이란것은 디스크 엑세스라고 볼수 있음
 - 비용의 단위 = 페이지(4Kbyte) 단위, 랜덤한 읽기
 - 비용계산을 위한 데이터 통계 : 데이터의 수, 데이터의 카디널리티, 키분포도, row와 key의 길이
 - 비용계산을 위한 스키마의 요소 - 유니크, Null유무

 

옵티마이저 진단 및 튜닝
 - 사용하는 통계데이터의 업데이트는 자동으로 처리함
 - 유저가 수동업데이트 할때는 "Analyze Table" 커맨드 사용
 - 정기적으로 실행(단 대용량에서는 ReadLock이 걸리므로 사용주의가 필요)
 - MyISAM, InnoDB, BDB에서 사용가능

 

데이터베이스(DB)의 효율적인 SQL 사용법

고려사항
 - SQL의 성능을 악화시키는 최대 요인은 불필요한 I/O
 - 최소의 Block Read를 통해서 조회
 - 결과 UI의 페이징처리로 데이터 범위를 최소화
 - 간결한 Query

 

 

효율적인 사용법

 - 조건절에 사용되는 컬럼에 외부적 변형 금지

  WHERE SUBSTR(DNAME,1,3)='ABC'   ===> WHERE DNAME LIKE 'ABC%'
  WHERE SAL*12=1200   ===>  WHERE SAL=1200/12
  WHERE TO_CHAR(HIREDATE,'YYMMDD')='940101' ===> WHERE HIREDATE=TO_DATE('940101','YYMMDD')

 

 - 컬럼비교시 같은 데이터 타입으로 비교
  CHA CHAR(10)
  NUM NUMBER(2,3)
  VAR VARCHAR2(20)
  DAT DATE

 

  아래와 같이 자동으로 변경됨으로 주의!!

  WHERE CHA=10 ==자동==> WHERE TO_NUMBER(CHA)=10
  WHERE VAR=10 ==자동==> WHERE TO_NUMBER(VAR)=10
  WHERE NUM LIKE '9410%' ==자동==> WHERE TO_CHAR(NUM) LIKE '9410%'

 

 - NULL값의 비교시
  WHERE ENAME IS NOT NULL ===> ENAME>'' /*SPACE*/
  WHERE COMM IS NOT NULL ===> COMM>0  /*숫자형이며 양수만 가능*/
  WHERE COMM IS NULL ===> COMM의 디폴트값을 0으로 하고 COMM=0으로 검색

 

 - 부정형 비교시
  WHERE EMPNO<>'1234' ===> WHERE NOT EXISTS (SELECT 'X' FROM EMP WHERE EMPNO='1234')

 

 - 기타
  힌트 사용 제한
  SELECT 절에는 반드시 필요한 컬럼만 나열
  반드시 필요한 경우에 대해서만 Outer Join 사용
  불필요한 Distinct 사용제한
  가능하다면 UINON 보다 UNION ALL을 사용
  복잡한 OR 사용은 IN 이나 UNION ALL로 변경
 

데이터베이스(DB)의 인덱스(Index)란?

INDEX란?
 - 테이블에 저장된 데이터를 빠르게 조회하기 위한 데이터베이스 객체
 - B-Tree구조를 가짐(B-Tree Index의 경우)
 - Index는 논리적/물리적으로 테이블과 독립적임

 

생성방법
 - 자동생성 : PK나 Unique제약 조건을 정의할 경우 Unique Index가 자동으로 생성됨

 

생성 Syntax
 CREATE [UNIQUE] INDEX index_name ON table_name(column1[,column2]);

 

종류
 - Oracle : Bitmap, FunctionBased
 - MySQL : FullText, Spatial
 - SQL Server : Cluster, Non_Cluster

 

생성지침
 - Where절의 조회조건이나 조인 조건에 자주 사용되는 경우
 - 컬럼값이 Unique한 컬럼
 - Update가 자주 발생하지 않는 컬럼
 - 해당 컬럼을 통한 Select연산의 결과가 전체 데이터 수의 5%이내인 큰 테이블

 

정보확인
 - Oracle : USER_INDEXS : 테이블의 인덱스 정보,
            User_IND_COLUMNS :  각 인덱스의 컬럼 정보
 - MySQL : Show INDEX From table_name
 - SQLServer : SP_HELPINDEX 테이블명.컬럼명

 

인덱스 Rebuild
 - 특정 영역의 데이터 대량 작업후 Rebuild처리함
 - Oracle : ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;
 - MySQL : ALTER TABLE table_name;
 - SQLServer : DBCC INDEXDEFRAG(db_name,table_name,index_name)
               DBCC DBREINDEX(table_name,index_name)

 

인덱스 삭제
 - DROP INDEX index_name
 - MySQL : DROP INDEX index_name ON table_name

 

데이터베이스(DB)의 뷰(View)란?

View란

 - 테이블이나 다른 View를 기반으로한 논리적인 테이블
 - View자체는 데이터를 포함하지 않음
 - Create View 권한이 있어야만 생성가능

 

Simple View
 - 한테이블 데이터만 포함
 - 그룹함수를 포하하지 않음
 - View를 통한 DML 수행 가능

 

Complex View
 - 둘 이상의 테이블 데이터 포함
 - 그룹함수, DISTINCT,GROUP BY절 등이 포함되어 있을 경우
 - View를 통한 DML 수행 불가능

 

생성 Syntax

 CREATE [OR REPLACE] [FORCE|NOFORCE]
 [(alias[.alias...])
 VIEW view_name AS subquery
 [WITH CHECK OPTION] [WITH READ ONLY]

 

 Alias : 컬럼명
 [OR REPLACE] : 기존에 존재하면 재생성함(SQL Server제외)
 [FORCE|NOFORCE] : Base Table 존재여부와 상관없이 생성
 [WITH CHECK OPTION] : View에 의해 엑세스 될 수 있는 행만이 입력,갱신됨
 [WITH READ ONLY] : 이 View에서 DML이 수행가능한지 여부(Oracle Only)

 

뷰의 정의 확인

 - 오라클 : "DESC USER_VIEW" 후 "SELECT VIEW_NAME, TEXT FROM USER_VIEWS"
 - MySQL : "SHOW CREATE VIEW 뷰이름"
 - SQLServer : "SP_HELPTEXT 뷰이름"

 

뷰의 수정
 - "OR_REPLACE" 옵션을 사용
 - SQLServer은 "ALTER VIEW"를 사용함

 

WITH CHECK OPTION
 - 데이터를 뷰 생성 조건에 위배되는 데이터를 만들지 못함
   즉, 뷰의 생성조건이 AGE>20 인데 AGE가 20이하인 레코드는 삽입 변경이 불가능하다

 

뷰의 삭제
 - DROP VIEW 뷰이름

데이터베이스(DB)의 각종 시퀀스 요약

Sequence(시퀀스)

 

- 오라클의 데이터베이스 객체
- 생성문법
  CREATE SEQUENCE 시퀀스이름
    Increment by 증가간격
    Start with 시작값
    Maxvalue 최대값   
    Minvalue 최소값
    Cycle|Nocycle 순환여부(default nocycle)
    Cache|Nocache 메모리 캐쉬여부(default 20)
- SELECT sequence.nextval 은 다음값리턴
- SELECT sequence.currval 은 현재값리턴

- 서브쿼리에서는 사용불가
- 주로 INSERT 의 VALUES절이나 UPDATE의 SET절에서 사용
- INSERT문의 DML SELECT문에서는 사용가능   
- 다음 부여번호를 수정할수 없음

 

Auto Increment(오토인크리먼트,자동증가)

 

- MySQL용 자동 증가값
- 테이블의 컬럼 속성

- 설정된 컬럼은 PK이거나 INDEX이어야 함
- "ALTER TABLE 테이블명 auto_increment=99;" 을 통해서 다음 부여번호를 수정가능함
- last_insert_id()를 통해서 가장최근에 성공한 Insert에 의해 생성된 레코드의 첫번째값을 리턴
  즉 다수의 레코드가 하나의 쿼리를 통해서 입력되면 첫번째 입력된 레코드의 값을 리턴한다

- 생성문법

 CREATE TABLE (column int형테이터타입 NOT NULL AUTO_INCREMENT[=value]

   ....

 PRIMARY KEY(column));

 

 

Identity(아이텐티티)

 

- MSSQL의 자동 증가값
- 테이블의 컬럼 속성
- 테이블당 하나만 존재
- 생성문법

  CREATE TABLE (column numeric형테이터타입 IDENTITY[(seed,increament)] ...);

 

엔터프라이즈아키텍쳐

1. 애플리케이션,솔루션,엔터프라이즈의 관계

 

  엔터프라이즈의 기념

  기업,회사의 의미라기 보다는 실제적으로 데이터를 공유할 필요가 있는 영역(다수의 회사일수도 있음)을 지칭하는 개념

 

  애플리케이션과 서비스 차이

  애플리케이션은 개발자를 중심으로 정의된 용어이고, 서비스는 고객 중심의 용어로 이런 차이로 인해서 EAI가 나온뒤 ESB의 개념이 추가되었다.

 

  각종 용어

 

  ERP(Enterprise Resource Planning)

  - 실제적으로 데이터를공유할 필요가 있는 영역의 다양한 Resource(사람,기계,절차 등)에 대한 계획을수행하는 프로그램

 

  EAI(Enterprise Application Integration)

  - 실제적으로 데이터를 공유할 필요가 있는 영역에 있는 애플리케이션의 통합과 관련된 프로그램

 

  ESB(Enterprise Service Bus)

  - 실제적으로 데이터를 공유할 필요가 있는 영역에 있는 다양한 서비스(사용자가 원하는 기능을 제공하기 위한 기본 단위)를 연결하는 프로그램

 

 

  애플리케이션과 솔루션의 관계

  솔루션은 엔터프라이즈의 구성단위, 개발자 관점에서 단독적으로 수행 가능한 기능 단위(보안솔루션,인증솔루션 등)

  애플리케이션은 개발자 관점에서 개발된 단위 작업 수행 프로그램(신규인증유저 등록 등)

 

 

       -----------E1-----------                엔터프라이즈

     --S1--       --S2--     --S3--              솔루션

    A1 A2 A3    A4 A5 A6    A7 A8 A9          애플리케이션

 

 

 

2. 시스템, 소프트웨어 아키텍처의 개념

 

  시스템

   - 유일한 기능을 수행하기 위하여 연관된 다양한 구성 요소의 모임 (회사, 컴퓨터, 모니터 등)

 

  아키텍처

  - 구성요소와 요소간의 연관관계를 정의한 것

  - 컴퓨터 아키텍처는 컴퓨터라는 시스템을 구성하는 요소와 요소 간의 관계를 Top-Down/Bottom-Up의 형태로 서술한 것이다.

 

 

 

3. 소프트웨어 아키텍처의 개념

  개념

  소프트웨어의 구성요소와 기능, 그리고 그들의 상호 관계에 대한 것을 정리

 

  실제적으로 수행하는 일

  - 시스템을 기능별로 분할

  - 분할된 기능을 체계화 하기 위하여 공통 기능 추출 및 관련 작업 수행

  - 준비된 자료를 체계적으로 정리하여 문서화

 

  어떤 필요성?

  개발자와 업무수행자(일반인) 사이의 의사 소통을 위한 방법으로 만들어짐

 

  일반인 - 업무에 대한 이해만 가진 상태로 자신이 사용하는 시스템은 전혀 모르는 상태

  전문가 - 업무에 대한 이해가 낮으며 일반인이 시스템을 모른다는 사실을 간과함

  위의 일바인과 전문가의 의사 소통을 위한 도구가 소프트웨어 아키텍처가 개발되었다

 

  의사 소통을 위해 필요한 다양한 정보를 표현하기위해 다양한 형태로 제작된 자료가 필요하다.

  카네기멜론대학은 다음과 같이 세가지 관점에서 제작하도록 제시했다.

 

  개념적 관점 : 소프트웨어를 구성하는 구성 요소(component)를 식별하고, 각 구성 요소의 역활을 명시하는 단계

  논리적 관점 : 구성 요소 간의 상호 연계성 및 연결 방법을 정의하고, 주고 받는 정보의 상세 내용을 정의하는 단계

  실행적 관점 : 실행 환경에서 구성 요소 인스턴스의 상호 자료 교환, 시스템 자원의 사용, 자원 구송 등에 대한 상세 사항을 정의하는 단계

 

  구성요소(component)란? 개발자 관점에서 시스템을 구성하는데 필요한 기능단위

 

 

  각 단계별 세분화된 자료 가이드라인

 

  개념적 관점

    - 행동적 관점 : Collaboration Trace

    - 구조적 관점 : Architecture Design, Informal Component Spec

 

  논리적 관점

    - 행동적 관점 : Collaboration Diagram

    - 구조적 관점 : Architecture Design with I/F, Interface Spec

 

   실행적 관점

     - 행동적 관점 : Collaboration Diagram Showing process

     - 구조적 관점 : Architecture Diagram Showing Action Component

 

   위의 8개의자료는 일반인과 전문가 사이의 의사 소통을 위한 것으로 필요시 더 늘어날 수도 있고 줄여서 사용할 수도 있다. 중요한 것은 의사 소통이지 자료가 아니란 것을 잊어서는 안된다

 

 

 

4. 엔터프라이즈 아키텍처의 개념

 

  정보시스템 아키텍쳐란?

 

  소프트웨어 아키넥처의 개념을 정립한 후 이것을 전체 전산 환경으로 확장하여 적용하면 "정보 시스템 아키텍쳐"라는 개념이 수립된다.

 

  정의하면?

  전체적인 정보 시스템을 구성하는 구성 요소나 빌딩 블록을 정의한 것.

  그리고 Product(Solution과 동일 수준의 용어, 개발자 시각에서 본 것) 구성을 위한 계획과 이에 따르는 개발 계획을 전체 시스템의 관점에서 수립하는 것

 

  엔터프라이즈 아키텍처란?

  이러한 정보 시스템 아키텍처를 전체조직(enterprise)관점에서 적용한 것이 엔터프라이즈 아키텍처이다.

 

  정의하면?

  "애플리케이션/소프트웨어 아키텍처",  "기술적 아키텍처",  "정보/데이터 아키텍처",  "조직/비즈니스 아키텍처 " 를 포괄하는 개념으로 하부의 모든 아키턱처가 가져야하는 기본을 정의한다. 이를 통하여 시스템의 일치성,통합성,연결성,보안,유연성,재사용성을 높여서 비용 절감과 경쟁력을 향상시킨다.

 

 잘만들어진 엔터프라이즈 아키턱처는 변화의 과정을 신속하게 수행할 수 있으며, 파급되는 다른 문제를 발생시키지 않는다.

 

  관련된 많은 도구들

  Zachman이 발표한 Enterprise Architecture A Framework

 

  엔터프라이즈 아키턱처를 구현할 때 고려사항을 X축과 Y축에 놓고 각각에 항목에서 제작되어야 하는 것을 종합적으로 정리한 것이다.