Ant의 FileSet을 Source 이용하기

Apache의 Ant에서는 FileSet라는 파일 및 폴더 관리 방식이 있다.

 

매우 유연하게 폴더 및 파일을 접근할 수 있는데 그부분은 Java 소스에서

 

이용하는 방법을 알아보도록 하자.

 

예제1

  String fileSet = "**/F*.jsp,**/T*.jsp";
  String fileSetExclude = "**/S.jsp";
  File ws = new File("D:/Projects/lucy/text-finder/work/jobs/Test/workspace");
 
        FileSet fs = new FileSet();
        org.apache.tools.ant.Project p = new org.apache.tools.ant.Project();
        fs.setProject(p);
        fs.setDir(ws);  // Root경로를 지정
        fs.setIncludes(fileSet);  //포함할 파일 조건
        fs.setExcludes(fileSetExclude);  //제외할 파일 조건
        DirectoryScanner ds = fs.getDirectoryScanner(p);

        // Any files in the final set
        String[] files = ds.getIncludedFiles();
        if (files.length == 0) {
         System.err.println("FileSet Empty");
            throw new Exception();
        }

        for (String file : files) {
            File f = new File(ws, file);
            if (!f.exists()) {
             System.err.println("Error Find File : " + f);
                continue;
            }
            if (!f.canRead()) {
             System.err.println("Error Read File : " + f);
                continue;
            }
           
            System.out.println("File : " + f.getName());
           
        }

 

 

위의 소스가 간단하기 때문에 쉽게 파악할 수 있을것이다.

 

FileSet의 조건을 주는 방식은 ant의 설명을 참고하길 바라며 콤마(,)를 이용해 다수의 경로조건을 입력할 수 있다.

 

IEToy에서 네이버 불펌 방지 해지

네이버에서 블로그나 카폐등을 돌아 다니다 보면 불펌 방지가 되어 있는 글을 볼 수 있다.

 

필요한 내용이 있을 경우, 특히 소스 등의 경우 타이핑으로 복사하는건 너무 힘든일이다.

 

이런 불편을 IEToy에서 해결 하기 위한 방법을 설명해보겠다.

 

 1. http://userscripts.org/scripts/show/61326  에 install버튼을 클릭한다.

 

 2. 파일 이름을 antidisablerfornaver.user.js 로 변경해서 IEToy설치 폴더의 gmm_Scripts폴더에 이동한다.

 

 3. IEToy 환경 설정(Win+I)에서 사용사 스크립트 항목에서 "Anti-Disable for Naver"을 체크한다.

 

 4. 위의 사이트가 문제가 있을 경우 아래의 링크를 다운받아서 2~3번의 절차를 거친다.

 

antidisablerfornaver.user.js

Spring Test Context Framework 사용시 Type mismatch 에러

TDD기반의 프로그래밍 중 스프링의 IC를 사용할때 Spring TestContext Framework를 사용하게 된다.

 

그럴 때 편의를 위해서 어노테이션을 사용하게 되는 경우가 많은데

 

아래와 같이 테스트용 메소드 상단에 기록을 하는 것이 일반적이다.

 

@RunWith(SpringJUnit4ClassRunner.class)
// specifies the Spring configuration to load for this test fixture
@ContextConfiguration(locations={"daos.xml"})
public final class HibernateTitleDaoTests {

    // this instance will be dependency injected by type
    @Autowired   
    private HibernateTitleDao titleDao;

    public void testLoadTitle() throws Exception {
        Title title = this.titleDao.loadTitle(new Long(10));
        assertNotNull(title);
    }
}

그런데 이렇게 설정을 해놓고 실행시에 아래와 같은 오류가 발생할 경우에는

 

Type mismatch: cannot convert from Class<SpringJUnit4ClassRunner> to Class<? extends Runner>

 

설치된 JUnit 의 버전을 확인 해보자.

 

JUnit 4.4 이후의 버전을 사용하게 되면 위의 오류는 사라질 것이다.

 

설치된 JUnit 의 버전을 확인 해보자.

 

 

 

또한 위와 같이 Autowire를 설정시에는 꼭 ApplicationConext.xml 파일의 beans 설정에 defalt-autowire속성이 설정되었는지 확인해야 한다.

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" ... 중략 ...  default-autowire="byName">
 <bean id="boardDAO" class="com.naver.bbs.dao.BoardDAOImpl" />
</beans>

 

 

 

 

Eclipse Code Template 에서 ${user}변수 변경

Eclipse(이클립스) 사용시 Code Template(코드 템플릿)에서 유저명을 변수로 사용할 수 있다.

 

예)

/**
 * @author ${user}
 */

그런데 간혹 이 유저명이 내가 현재 원하는 유저명이 아닌 OS유저명이 적용되는 것을 볼 수 있다.

 

이것을 변경하기 위해서는 이클립스가 설치된 경로의 설정파일에 다음의 한줄을 추가하자

 

eclipse.ini

-Duser.name="사용하고자하는 유저명"

 

JDBC와 DAO

JDBC

 

 1. Java DataBase Connectivity
  - 자바의 표준 DB접속 방법
  - DataBase와 독립적인 구현
 
 2. JDBC Driver Type
  - type1 : JDBC-ODBC 브리지(UserCode -> JDBC -> JDBC-ODBC -> ODBC -> DB)
  - type2 : native API Driver (UserCode -> JDBC -> DB library(native) -> DB)
  - type3 : network-protocol driver (UserCode -> JDBC -> DB Midleware -> DB)
  - type4 : native protocol driver (UserCode -> JDBC -> DB)
  
 3. JDBC Spec.
  - 커넥션, SQL 질의 및 파라미터, 결과의 수신,
  - 기본 매핑(SQL Type & Java Type), 메타데이터 제공, 트랜잭션, 로깅 등
  
 4. 커넥션 방법
  - DriverManager로 접속하는 방법
    Driver 로딩 : Class.forName("com.driver.class.name");
    DriverManager로 커넥션 획득 : DriverManage.getConnection("접속정보");
   
  - javax.sql.DataSource(JDBC 2.0)
    WAS start(DataSOurce Configuration) -> Referenceable -> JNDI
    Context.lookup(UserCode <-> JNDI <-> DataSources)
    DataSource.getConnection(UserCode <-> DataSource)
   
  - PooledDataSource
    UserCode <-> Pooled Connection <-> ConnectionPool <-> PoolingDataSource

 

Data Access Layer

 

 1. DAO FrameWorks
   - JDBC Templates
   - SQL Mappers
   - OR Mappers
  
 2. JDBC Templates
   자주 사용하는 표준적인 DB접근, 질의 등을 템플릿 형태로 사용함
   장점 - 쉽다, 설정필요이 따로 필요 없다
   단점 - 코드안에 모든내용포함 된다, 코드가독성이 낮다, DB 의존적이다
  
 3. SQL Mappers
   쿼리 등을 외부로 빼내고 쿼리에 대한 결과를 매핑해주는 기능으로 bean에 결과를 매핑한다.
   장점 - 코드가 줄어듦, 배우는게 쉽다, 코드와 쿼리가 분리된다
   단점 - XML설정이 필요하다, DB에 의존적이다
  
 4. OR Mappers
   테이블의 Row를 하나의 객체로 인식하고자 함
   설정을 통해서 테이블과 클래스, Row와 인스턴스를 연결하고 객체만을 사용
   장점 - 코드가 줄어듦, 직관적이다, 쿼리와 DB의존적이지 않다
   단점 - XML설정이 필요하다, 배우기 어렵다
  

[MySQL 모니터링 및 엔진 최적화]InnoDB 스토리지 엔진 최적화

InnoDB의 옵션의 개요

 

 

InnoDB의 메모리 관련 옵션

 - Innodb_buffer_pool_size

    가장 중요한 옵션

    데이블의 데이터와 인덱스를 캐싱하기 위해서 사용

    사이즈가 클수록 성능이 향상됨

    OS Cache보다 훨씬 효율적으로 메모리를 사용하며 Write성능에 큰 영향을 미침

    서버메모리 용량의 70~80%정도로 설정하는 것이 적당

    기본값은 8MB이며 반드시 재설정 필요

 - Innodb_additional_mem_pool

    DataDictionary(테이블 스키마 등)를 저장하기 위해서 사용. 필요한 경우 자동으로 증가

 

InnoDB의 로그 관련 옵션

 - Innodb_log_file_size

    InnoDB redo로그 파일 크기

    Write 성능에 매우 큰 영향을 미침

    설정 파일 크기에 따라 복구 시간이 증가될수 있어서 256M 사용권장

 - Innodb_log_files_in_group

    로그 그룹안에 포함될 로그 파일 수

    기본적으로 2이며 3을 권장함

 - Innodb_log_buffer_size

    매우 큰 BLOB를 사용하지 않는한 2-8M의 기본값 사용

 

InnoDB log flush 주기 조절

 - Innodb_flush_log_at_trx_commit(기본값은1)

    0으로 설정하면 1초에 한번씩 디스크에 기록하고 씽크 - MySQL,시스템다운시 1시간 데이터 누락가능성 있음

    1로 설정하면 commit시 디스크에 기록하고 씽크 - 어떤 다운시에도 데이터 유지

    2로 설정하면 commit를 할때마다 디스크에 기록하고 싱크는 1초에 한번만함 - 시스템 다운시 1초간 데이터 누락

 

InnoDB의 로그 사이즈 재조정

 - 일반적인 옵션처럼 수치만 변경해서 조절할 수 없음

    MySQL종료

    Data 디렉터리 안의 ib_log* 파일 삭제

    설정의 innodgb_log_file_size 수정

    MySQL 재시작

 

InnoDB의 flush 방법 설정

 - InnoDB가 OS의 FileSystem과 연동하는 방식 설정

 - 윈도우에서는 unbufferedIO가 늘 사용됨

 - UNIX에서는 fsync(), O_SYNC/O_DSYNC를 파일 flush를 위해 사용가능

 - 리눅스에서는 O_DIRECT를 사용하여 unbufferedIO를 사용할 수 있다 (double buffering을 막아줌)

 

InnoDB의 테이블 별 테이블 스페이스

 - Innodb_file_per_table 옵션이 설정 가능

 - 테이블 별로 테이블스페이스를 설정함

 - 테이블 별로 설정해도 공통 테이블스페이스는 필요함

 - 분리시 데이터를 여러개의 디스크로 분산 가능

 - 테이블을 drop하면 디스크의 공간이 반환됨

 - 테이블이 많을 경우 MySQL기동/종료시 속도가 빨라짐

 

그 밖의  InnoDB의 옵션들

 - Innodb_thread_concurrency : 기본값은 8, 동시 사용 쓰레드수로 변경하지 않음

 - FOREIGN_KEY_CHECKS/UNIQUE_CHECKS

    데이터를 입력시 Foreign키와 Unique를 검사하지 않음

    대용량 데이터 입력시 사용함(AUTOCOMMIT을 0으로 하는것도 추천)

 - innodb_fast_shutdown

    종료시 내무 메모리 구조 정리 작업과 버퍼 정리 작업을 건너뜀. 무결성에는 영향없음

  

 

ㅇㄹㅇ

[MySQL 모니터링 및 엔진 최적화]MyISAM 스토리지 엔진 최적화/모니터링

MyISAM의 mysqld옵션

 

 - 전체 mysqld 세팅

    key_buffer_size(기본 8Mb) : 인덱스 캐시, 올리게 되면 성능 향상

    데이터 row에 대한 캐시는 OS에서 핸들링

 

 - 쓰레드별 세팅 : 일반적인 동작에 관계 없음

    Myisam_sort_buffer_size(기본 8Mb)

    Myisam_repair_threads(기본1) - 벌크 임포트와 myisam테이블 복구에 사용

 

 

Key와 관련된 Status 환경 변수

 

 - MyISAM 엔진은 key캐시를 조절하는 것이 성능과 직결됨

 

 - 일반적인 경우 다음과 같은 상황이 바람직함

     낮은 key_reads(물리 디스크를 읽는 것우)

     매우 낮은 key_reads/key_read_request비율(0.03이하)

 

 - 다음방법으로 캐시 사용을 확인

     key_block_used와 key_block_unused는 얼마나 많은 쿼리캐시 공간이 사용중인지 나타냄

     key_cache_block_size로 블록 사이즈를 결정

 

 

key_buffer_size 최적화

 - 값을 높여주면 더많은 메모리를 사용해서 성능이 높아지나 너무 높이게 되면 데이터로딩시 OS캐쉬의 이용을 할 수 없어 성능이 낮아질수 있음

 - 전체메모리의 25%정도로 설정로 보통 512M이하로 설정

 - SHow_status like 'key%' 로 key 사용상황 점검

 

 

대용량 데이터 로딩 및 수리

 - MyISAM_sort_buffer_size

     인덱스 생성에 사용되는 메모리의 양, 대용량 데이터 입력시 성능을 높히기 위해서 할당함

 - Myisam_repair_threads

     1이상으로 설정할 경우 병렬로 인덱스 생성이 가능, 코어캣수만큼 설정, repair시에만 사용됨

 

 

MyISAM모니터링

 

 - MyISAM은 키캐시에 대부분의 성능이 좌우됨

 

 - 키 캐시 쓰기 요청

 - 키 캐시 쓰기

 

 

[MySQL 모니터링 및 엔진 최적화]일반적인 MySQLD 옵션

MySQL의 기본 설정 파일

 

 - 총 5개의 기본설정 파일(windows환경에서는 확장명이 ini임)

    my-small.cnf : 64MB이하의 메모리를 시스템 설정

    my-medium.cnf : 128MB 시스템 설정

    my-large.cnf :  512MB이하 시스템

    my-huge.cnf : 1-2GB 시스템

    my-innodb-heave-4G.cnf : 4GB이상 메모리상에서 InnoDB를 사용하는 경우(주로사용)

 

 - 저장 위치

   Linux/Unix : /etc/(1순위), 설치경로(2순위), 데이터디렉토리(3순위) 뒤쪽의 것이 오버라이트함

   Windows : Windows디렉토리(1순위), 설치경로(2순위), 데이터디렉토리(3순위) 뒤쪽의 것이 오버라이트함

 

 

MySQL의 파라메터 조정

 

 - 옵션중 일부는 동작중 변경 가능

    SESSION : 조정된 값이 현재 커넥션에서만 영향을 미침

    GLOBAL : 조정된 값이 전체 서버에 영향을 미침

    BOTH : 값을 변화시킬때 SESSION/GLOBAL을 반드시 명기해야함

 - 동작중 변경값은 기동이 종료되면 소실됨

 - 모든 옵션의 현재값은 아래 커맨드로 확인가능

SHOW SESSION/GLOBAL VARIABLES

 

 

MySQLD 옵션의 변경

 

 - 변경된 옵션은 전체 서버에 적용됨

 - 대부분의 옵션은 동작중에 아래와 같이 변경 가능(대부분 super권한을 가져야함)

mysql>SET GLOBAL parameter = value

 - 세션별로 변경가능한 옵션은 아래와 같이 적용 가능

mysql>SET SESSION parameter = value

 

 

주요 GLOBAL옵션

 

 - table_cache(기본 64)

    사용하는 테이블에 대한 핸들러를 캐시에 저장,

    동시테이블 사용량이 높으면 높임

 

 - thread_cache(기본 0)

    재사용을 위해서 보관해야할 쓰레드수,

    클라이언트가 커넥션풀을 사용할 경우 의미없음

 

 - max_connections(기본100)

    허용가능한 최대 접속수

    함부로 늘려서는 안됨(각 커넥션의 사용할 메모리 양의 총합이 동접 증가수만큼 늘어날 수 있으므로)

 

 

커넥션 관련 옵션

 

 - Connect_timeout

     connection접속후 요청 후 대기 시간

 - net_buffer_length

     MySQL이 전송하는 초기 메시지의 크기, 기본값 사용 권장

 - max_allowed_packed

     서버/클라이언트간 최대 전송 가능 패킷 크기(디폴트 2M)

     TEXT나 BLOG컬럼이 있는 것우 또는 리플리케이션을 사용하는 경우에는 최소 16M권장

 - back_log

     커넥션이 대량으로 몰리는 경우 대기 가능한 커넥션의 수, 기본값 사용 권장

 

 

커넥션 관리

 - max_connections : 최대 접속수

 - inactive_timeout : 유저와 상호작용을 하는 커넥션 타임아웃, 클라이언트 툴 등에서 접속중 유효시간

 - wait_timeout : 일반적인 서버,클라이언트 환경에서 타입아웃시간.(close를 안하는 경우 문제가 될수 있으므로 적절하게 조절해야함)

 - net_read_timeout/net_write_timeout : 클라이언트의 네트워크를 통한 읽기/쓰기 타임아웃, 기본값 사용 권장

 - net_retry_count/max_connect_error : 통신이 잘못되었을때 몇번만에 블럭되는지 지정, 블럭이 되면 FLUSH HOST명령전에는 접근불가. 최대한 높은 값으로 설정해놓음

 

 

Table Cache 최적화

 - table_cache값을 올리면 OS상의 file descriptor의 수를 증가해줘야함

 

테이블 스캔 성능의 향상

 - 결과 값을 찾기 위해 모든 row를 탐색해야하는 경우에 사용되므로 성능에 큰 영향을 미침

 - 테이블 스캔 디스크 엑세스 감소를 위해 read_buffer를 사용

 - read_buffer_size는 기본 128Kb이고 높일 수록 테이블 스캔 성능이 올라감

 - 테이블 스캔하는 모든 쓰레드에 적용되므로 너무 높이게 되면 메모리 자원에 문제가 발생할 수 있음

 - 풀 테이블 스캔을 사용하는 쿼리 앞단에서 올리고 종료히 내려주는 방법도 사용가능

 

조인 성능의 향상

 - 조인되는 컬럼의 인덱스가 존재하지 않는 경우 조인버퍼를 사용

 - 인덱스를 추가하는 것이 바람직하나 임시적으로 join_buffer_size를 높여주는 것도 가능함

 - 두 테이블간 조인일 경우 하나의 join_buffer가 추가되지면 테이블이 추가될 수록 버퍼수도 늘어남

 

정렬 성능의 향상

 - 대량의 정보를 OrderBy하거나 GroupBy를 처리하게되는 경우 디스크 자원 사용

 - 이러한 작업을 메모리내에서 처리하기 위해 sort_buffer_size를 조절

 - 적절하게 사용하여 디스크를 사용하지 않고 메모리버퍼를 쓰게되면 25%정도의 성능향상 가능

 - sort_buffer에 데이터를 정렬한 후 실제 결과값을 처리하는 것은 read_rnd_buffer_size의 영향을 받음

 - 세션별로 설정해서 임시적으로 사용하는 것을 권장

 

Query Cache

 - SELECT 쿼리와 그 결과를 저장

 - 목적 : 빈번하게 사용되는 SELECT쿼리의 성능 향상

 - 테이블에 변화(INSERT,UPDATE,DELETE)가 일어나게 되면 해당테이블과 관련된 쿼리 캐시내의 쿼리는 초기화

 - Query_cache_size 환경 변수를 통해서 조절(기본은 비활성화)

 - SHOW STATUS LIKE 'Qcache_%' 커맨드로 쿼리 캐시 관련 항목 모니터링

 - RESET QUERY CACHE 커맨드를 통해 수동으로 캐시 삭제 가능

 

 

Query Cache 최적화

 - Query_cache_limit 를 통해서 저장할 쿼리의 사이즈를 제한할 수 있음

 - Query_cache_min_res_unit(blocksize)를 설정하여 쿼리 캐시의 조각화를 줄일 수 있음, 기본4Kb

 - 남는 블록이 있음에도 불구하고 캐시 된 쿼리가 제거된다면 Qcache_free_blocks, Qcache_lowmen_prune을 참고

 - Query_cache_hits와 Com_select를 비교하여 캐시 적중률을 파악할 수 있음

[MySQL 모니터링 및 엔진 최적화]MySQL 환경변수

커넥션 관련 환경 변수

 - max_used_connections : 피크 타임의 동시 접속수 (튜닝시 중요)

 - bytes-receved, bytes-sent : 모든 클라이언트와 전송량

 - connection : 시도된 커넥션의 총합

 - aborted_connects : 접속이 끊어진 커넥션의 총 합 (높을 경우 어플리케이션 커넥션정보 확인필요)

 

쓰레드 관련 환경 변수

 - threads_connected : 현재 열려 있는 커넥션 수

 - threads_cached : 재사용 가능한 동작중이지 않은 커넥션 수

 - threads_created : 서버 시작후 현재까지 만들어진 쓰레드 수

 - threads_running : sleeping가 아닌 동작중인 쓰레드

 - slow_launch_threads : 쓰레드 생성시 시간이 2초이상이 걸린 쓰레드의 수 (0에 가까워야함 높아지면 부하가 높아진다는 뜻)

 - threads_created/connections - 캐시 적중률(적중률이 낮으면 cache사이즈를 증가시켜주는것이 좋음)

 

 

핸들러 관련 환경 변수

 - 모든 handler_xxx 환경 변수들은 내부의 테이블 핸들러의 동작 상황에 대한 정보를 제공

 - 핸들러 관련 환경 변수에 대한 일반적인 해석

 - handler_read_first가 높은 경우 -> 많은 풀 인덱스 스캔이 이루어짐 (메모리)

 - handler_read_next가 높은 경우 -> 풀 인덱스 스캔과 레인지 스캔이 이루어짐 (메모리)

 - handler_read_random가 높은 경우 -> 많은 풀 테이블 스캔과 레인지 스캔이 이루어짐 (디스크)

 - handler_read_key가 높은 경우 -> 인덱스를 읽은 경우가 많음(메모리), 좋은 수치

 

 

성능 관련 문제를 보여주는 항목들

 - MySQL의 느린 응답을 나타내는 항목

     slow_queries, slow_luanch_thread

 

 - 부하가 심하다는 것을 나타내는 항목

     thread_created가 큰경우,

     max_used_connections가 큰경우,

     opend_tables가 큰경우(table_cache를 올리는 것이 좋음),

     handler_read_key가 높은 경우

 

 - 락 경쟁과 관련된 항목(MyISAM에서 중요함)

      table_locks_waited VS table_locks_immediate (락획득시 대기/비대기 수치)

      업데이트가 많아지면 Lock경쟁이 높아진다 -> InnoDB로 변경하는 것이 해결책

 

 

쿼리 관련 문제를 보여주는 항목들

 

 - Created_tmp_disk_tables 환경 변수가 큰 경우

    메모리에 적용할 수 없는 큰 임시 테이블이 많이 만들어졌다는 의미

    -> tmp_table_size를 올려줘서 해결(사용에 주의)

 

 - Select_xxx 환경변수의 값이 큰 경우

    select쿼리가 최적화되지 못했음을 의미

    -> select_full_join과 select_range_check는 일반적으로 더 많은 인덱스를 작성해줘야함

 

 - Sort_xxx 환경변수 값이 큰 경우

    sort_merge_passes가 큰 경우 ordering하는 작업비용이 크다는 의미

    -> sort_buffer_size를 늘리거나 인덱스를 추가

[MySQL 모니터링 및 서버 최적화]서버 모니터링

퍼포먼스 모니터링

 

 - OS자체 도구
   리눅스/유닉스 :vmstat, iostat, mpstat
   윈도우 : 작업관리자 성능 탭
  
 - MySQL자체 도구
   기동 후 메모리 상의 성능 수치를 추적
   SHOW STATUS 커맨드로 확인 가능
   Cricket, SNMP 또는 자체 제작 스크립트 사용 가능
   MySQL Administrator 사용 가능(GUI형태)
  
 - 쿼리는 MySQL 로그를 통해서 추적
   General Log :
     일반적으로 사용안함(IO증가량이 높음, 5.0이전은 설정변경시 재기동 필요),
     모든 사용자의 입력을 로깅,
     액션전에 저장되어 유용
   Slow Query Log :
     느린 쿼리에 대한 로그

 

MySQL에서 Thread 모니터링
 - MySQL은 Thread기반 서버
     SHOW FULLPROCESSLIST
     STATE컬럼을 통해 각 쿼리가 현재 어떻게 수행되고 있는지를 확인가능
 - 성능관련 문제는 아래 작업을 통해 확인 가능
     Processlist 모니터링
     SHOW STATUS의 내용을 확인
 - 급박한 문제는 쓰레드 KILL을 통해 제거 가능
     잘못된 쓰레드는 Processlist로 확인
     해당 프로세스 ID를 KILL

 

 

MySQL STATUS

 - 동작 상태에 대한 항목 수집

 - 서버의 현재 동작 상태를 모니터링

 - 서버를 최적화 할때 가이드로 이용

 - Status항목은 아래 두가지 방법으로 확인 가능

설치경로/bin/mysqladmin extended-status
MySQL Administrator - GUI형태로 제공

기본적인 STATUS 모니터링

 - mysqladmin은 기본적인 관리툴 : 10초마다 갱신

설치경로/bin/mysqladmin -i 10 extended-status 

 

 

3rd Party 모니터링 툴 - MyTOP

 - MySQL의 정보를 TOP과 같이 보여주는 툴

 - 전체 쓰레드 리스트를 보여줌

 - Database나 Host별 필터링 가능

 - 커넥션에 대한 kill을 쉽게 수행

 - QPS를 쉽게 확인 가능

 

3rd Party 모니터링 툴 - innotop

 - InnoDB엔진에 대한 Status는 "show engine innodb status"로 확인 가능

 - 그 결과를 해석하기 편하게 정리해주는 툴

 - http://www.xaprb.com/blog/2006/07/02/innotopmysql-innodb-monitor/

 

 

기본 STATUS 항목

 - 서버의 시작후 동작시간은 uptime으로 확인 가능

 - com_xxx항목은 서버시작 이후 xxx관련 명령이 얼마나 수행되었는지 나타냄

 - Questions는 서버로 전송된 총 쿼리 수를 나타냄

 - 이러한 전체 동작 양상과 select와 U/D/I 비율 등을 추출할 수 있음

 

 

 

 

[MySQL Stored Procedure]Stored Program의 보안

Stored Programs에 사용시 필요한 권한
 - CREATE ROUTINE, ALTER ROUTINE, EXECUTE

 

실행모드 옵션 - DEFINER
 - SQL SECURITY DEFINER
   루틴실행이 DEFINER 지정 유저 권한으로 실행됨
   생성자가 SUPER권한을 가졌으면 다른 계정 지정 가능
 - 테이블의 권한이 없어도 프로시져를 통해서 접근이 가능

 

실행모드 옵션 - INVOKER
 - 루틴을 호출하는 사람의 권한으로 실행
 - Invoker 권한으로 실행시에는 권한이 없을 경우 에러 처리 필요

 

Stored Program의 성능은?
 - 연산작업은 처리하지 않는다
 - 통계작업 등은 네트워크 트래픽을 많이 사용하는 경우에는 성능이 좋다
 - Self Join시 단계적 로직을 이용하여 Self Join을 피해서 처리하면 성능향상
 - Update문 안에 SELECT가 있을경우 커서를 이용해서 분리시 성능향상 가능

[MySQL Stored Procedure]View

CREATE VIEW Syntax

  CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
  [DEFINER={USER|CURRENT_USER}]
  [SQL SECURITY {DEFINER|INVOKER}]
  VIEW view_name [(column list)]
  AS select_statement
  [WITH [CASCADE|LOCAL] CHECK OPTION]

 
ALGORITHM 종류
 - UNDEFINED : MySQL이 자체적으로 선택, 대부분 MERGE
 - MERGE : 뷰와 포함된 쿼리를 효율적인 방식으로 Merge해서 사용
 - TEMPTABLE : 뷰와 연결된 임시테이블을 만들고 사용, 인덱스를 전혀 사용 못함

 

 

뷰와 테이블의 차이
 - 뷰는 정의시점에 고정, 테이블에 컬럼이 추가되어도 반영되지 않음
 - SELECT문에서 system이나 user변수 사용 못함
 - 뷰 정의시 임시테이블 참고 못함
 - 트리거를 뷰에 연결시킬 수 없다
 - 뷰정의시 ORVER BY를 포함할 수 있으나 VIEW사용시 ORDER BY를 지정하면 정의된 ORDER BY는 무시됨

[MySQL Stored Procedure]Trigger

트리거 생성 문법

   CREATE DEFINER = {user|CURRENT_USER}] TRIGGER trigger_name
     {BEFORE|AFTER}
     {UPDATE|INSERT|DELETE}
   ON table_name
   FOR EACH ROW
   trigger_statements

 - Definer의 경우 슈퍼권한이 있을 경우 다른 계정이 지정가능
 
컬럼값의 참조
 - NEW : 새로 입력된 값(Insert,Update에서 사용가능)
 - OLD : 삭제된 데이터를 지칭(Delete,Update에서 사용가능)

 

 

BEFORE, AFTER 트리거
 - AFTER의 경우 값의 변경이 불가능

 

Trigger 사용
 - 중요테이블 로깅, 데이터 입력력 트랙킹, 입출력 데이터 검증
 

[MySQL Stored Procedure]Stored Function

Stored Function이란?
 - 하나의 값을 반환하는 stored program
 - OUT, INOUT변수가 아닌 RETURN으로 값을 반환
 - 내장함수와 동일한 형태로 DML에서 사용 가능
 - 복잡한 코딩을 줄여줄 수 있다

 

 

Stored Function만들기
 - Syntax

   CREATE FUNCTION function_name (paramters[,...])
   RETURNS datatype
   [[NOT]DETERMINISTIC]
   [{CONTAINS SQL|NO SQL|MODIFIES SQL DATA|READS SQL DATA}]
   [SQL SECURITY {DEFINER|INVOKER}]
   [COMMENT string]
   function_statement

 - Return절은 필수임
 - 파라메터는 IN으로 처리됨

 

 

Return 문
 - 리턴문은 하나만 있는 것이 좋다(조건문으로 분기시 변수 사용)

 

 

DETERMINISTIC과 SQL절

 - 바이너리 로그를 사용할 경우 반드시 고려해야 함
   바이너리 로그를 사용하는 경우 해당 FUNCTION이 동일한 결과(DETERMINISTIC)를 가지는지 확인해야함(시스템시간등의 사용 유의)
   모든 Stored program을 위한 기본값은 NOT DETERMINISTIC CONTAINS SQL이므로 반드시 명시적으로 표시가 필요
   NOW() 함수 또는 시간을 기반으로 하는 함수와 하나의 랜덤값을 발생시키는 함수는 NON DETERMINISTIC이 아님(사용해도 무방)
   이를 막기 위해서 DETERMINISTIC, NO_SQL또는 READ_SQL_DATA키워드를 정의하거나 log_bin_trust_routine_creater옵션을 1로 설정해야 실행됨

Stored Function 생성시 주의할점

 - 옵티마이저를 사용하지 않으므로 성능 테스트 필요

 - 자주 사용되는 쿼리에는 사용하지 않음

[MySQL Stored Procedure]트랜잭션 관리

Isolation Level

 - READ UNCOMMITED
   Dirty read허용
   속도는 빠르지만 commit 되지 않은 ROW를 다른 세션에서 볼 수 있음

 - READ COMMITED
   Commit된 row만 읽을 수 있음

 - REPEATABLE READ
   트랜잭션이 시작된 시점 기준의 값을 볼 수 있음

   Default 설정

 - SERIALIZABLE
   각 트랜잭션이 독립적으로 동작
   SELECT시에도 락을 사용
 

트랜잭션 관련 Command
 - START TRANSACTION : 시작, AUTO_COMMIT를 0으로 설정
 - COMMIT : 트랜잭션의 변경 내용을 저장, LOCK해제
 - ROLLBACK : 트랜잭션의 변경 취소
 - SAVEPOINT savepoint_name : 저장단계 지정
 - ROLLBACK TO SAVEPOINT savepoint_name : 단계로 롤백
 - SET TRANSACTION : Isolation레벨 지정
 - [LOCK|UNLOCK] TABLE : 테이블에 락을 지정/해제
 
유의사항
 - 아래 구문들은 트랜잭션 처리가 되지 않음

   ALTER [FUNCTION|PROCEDURE|TABLE]
   CREATE [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
   DROP [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
   LOCK TABLES/UNLOCK TABLES
   RENAME TABLE/TRUNCATE TABLE
   BEGIN, SET AUTOCOMMIT=1, START TRANSACTION
   LOAD MASTER DATA

 

 

LOCK의 종류별 발생 Case
 - UPDATE : 변경되는 모든 ROW에 락설정
 - INSERT : PK, Unique Key레코드에 락설정
 - LOCK TABLES : 전체 테이블에 락설정
 - SELECT ... FOR UPDATE : Select 결과 row에 대해 Exclusive 락 설정, Read/Write 불가능
 - SELECT ... LOCK IN SHARE MODE : Select 결과 row에 대해 Shared Lock 설정, Read는 가능

 

Deatlock
 - InnoDB는 DeadLock상황을 탐지해서 트랜잭션들을 강제로 Rollback함

 

 

LockTimeout
 - 지정한 시간동안 Lock를 획득하지 못하면 Rollback처리

 

 

Locking 전략
 - Pessimistic Locking Strategy
   트랜잭션에서 읽는 row에 미리 락을 설정
   concurrent update가 빈번하다 가정
   단순하고 견고한 코드
   트랜잭션 처리 시간이 길어지는 경우 성능 저하 유발

 - Optimistic Locking Strategy
   마지막으로 업데이트 하는 순간 Lock를 확인하고 처리
   update 빈도가 낮다고 가정

 

 

트랜잭션 디자인 가이드라인
 - 최대한 작게 유지
 - Rollback는 최대한 자제 - Retry할 수 있도록 예외 처리를 하는 것이 좋음
 - Savepoint는 사용 자제
 - Pessimistic Locking Strategy를 기본으로 하고 처리량이 중요한 경우 Optimistic 고려

 

[MySQL Stored Procedure]장애처리(Handler)

Condition Handler
 - Syntax

   DECLARE [CONTINUE|EXIT] HANDLER FOR
   [SQLSTATE sqlstate_code|MySQL error code|condition name]
   handler_actions

  
핸들러의 종류
 - EXIT핸들러
   현재 실행중인 블록 중단, outer블럭일 경우 종료됨
 - CONTINUE
   에러를 발생시킨 문장이 계속 실행됨

 

핸들러의 조건
 - MySQL Error Code : 에러코드를 조건으로 받음

   DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1

 - ANSI-Standard SQLSTATE code : ANSI SQL 2003 표준 SQLSTATE 코드를 조건으로 받음

   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1

 - Named Conditions
   사용자가 임의로 에러코드에 이름을 부여하여 코드의 가독성을 높인다

   DECLARE foreign_key_error CONDITION FOR 1216;
   DECLARE CONTINUE HANDLER FOR foreign_key_error SET duplicate_key=1

 

SQL 2003에서 빠진 부분
 - SQLCODE 또는 SQLSTATE에 대한 직접 접근
 - SIGNAL문의 사용
  

[MySQL Stored Procedure]커서의 사용

INTO절에서 SELECT
 - 오직 하나의 값만 나오게 처리해야함

 

커서의 생성
 - 하나이상의 결과를 return하기 위해서 사용
 - 커서의 선언은 모든 변수를 생성한 이후에 선언해야함
 - Syntax

DECLAER cname CURSOR FOR SELECT * from dep;

  

커서의 사용
 - OPEN : 커서를 사용하기 위해서 fetch전에 반드시 처리
 - FETCH : 커서가 다음 ROW로 이동
 - CLOSE : 커서를 꼭 닫아줘야함
 - 전체 결과를 FETCH하는 경우 LOOP를 사용하며 이때 마지막 row을 fetch할때 "no data to fetch"에러를 발생한다 이것을 피하기 위해서 error handler을 정의해서 해결

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

 

커서의 Loop
 - 예시

 OPEN dept_csr
 dept_loop : LOOP
   FETCH dept_csr INTO l_id, l_name, l_location;
   IF no_more_departments=1 THEN
     LEAVE dept_loop;
   END IF
   SET l_count=l_count+1
   CLOSE dept_csr;
   SET no_more_departments=0;
 END LOOP;

 

Nested Cursor Loops
 - 하나의 커서 종료후 not found변수를 reset한다.
 - NOT FOUND의 경우 커서별로 지정할 수 있다(같은 블럭내에서는 하나의 not found변수만 활성화)

 

 

 

[MySQL Stored Procedure]블록, 조건절, 반복구문의 처리

Stored Program의 Block구조

 - 명시적으로 BEGIN, END구문 사용

 - BEGIN 구문전, END 구문후에 이름을 지정

 - LEAVE 를 통해서 블럭을 나갈 수 있음

   outer : BEGIN
     LEAVE outer;
   END : outer

 - Nested Blocks구조
   블럭 내부에서 선언한 변수는 외부에서 사용불가
   반대는 사용 가능

 - 조건문

   IF THEN
     statements...  
     ELSEIF THEN
     statements...    
     ELSE
     statements...    
   END IF;

 

   CASE exp
     WHEN val THEN
     statements...    
     ELSE
     statements...    
   END CASE;

 

 - 반복구문 : LOOP

   label : LOOP
     statements...  
     LEAVE label;
   END LOOP label;

 - 반복구문 : UNTIL
   exp가 참일 경우 계속 Loop, REPEAT는 최소한 한 번 수행 보장  

   label : REPEAT
     statements...
   UNTIL exp
   END REPEAT;

 

 - 반복구문 : WHILE

   exp가 참일 경우 계속 Loop, 한번도 수행되지 않을 수 있음

   label : WHILE exp DO
     statements...  
   END WHILE label;

 

[MySQL Stored Procedure]Language Fundamentals

Variables
 - Syntax
   DECLARE variable_name[,variable_name..] datatype [DEFAULT value];
 - 값 할당시에는 SET사용
 - 모든 데이터 타입 사용 가능

Literals
 - Numeric literals 가능
   10진수(200), e지수(2e5), 16진수(OxA) 사용 가능
 - Date literals
   SET datetime = '1999-12-31 23:59:59';
 - String literals
   'String', "String"로 표현
   상수안에 '," 사용시 백슬래쉬 사용
   escape문자 사용가능 \t, \n, \\
   ANSI_QUOTES모드일때에는 single quotes만 가능

Parameters - IN
 - Syntax
   CREATE PROCEDURE name ([IN|OUT|INOUT] parameter_name data_type...)
 - IN 파라미터 값을 프로시져 안으로 전달
 - 변경은 가능하지만 리턴은 불가(Call by Value 형태)

Parameters - OUT
 - 프로시져 호출시 return value 지정
 - 값이 반환 될 수는 있지만 프로시져 안으로는 전달이 안됨

Parameters - INOUT
 - 값이 입력, 출력이 가능하다

User Variables
 - Syntax
   SET @name = 'YourName';
 - 세션단위의 사용자 지정 광역변수
 - 동일세션내에서 다른 프로시져에서 호출 가능
 - 관리가 힘들다

Comment
 - '--' 또는 '/* */'으로 사용 가능

Data Types - ENUM
 - Syntax
   CREATE PROCEDURE name (in_option ENUM('Yes','No','Maybe'))
   CALL name('Yes')
  
   CREATE PROCEDURE name (in_option SET('YES','No','Maybe'))
   CALL name('Yes,No')  
 - 다른 DB와는 호환이 되지 않음

Strict Mode
 - 잘못된 값이 컬럼에 삽입되거나 업데이트 될때 Error을 반환
 - STRICT_TRANS_TABLES, STRICT_ALL_TABLES의 차이점
   STRICT_TRANS_TABLES은 트랜잭션 지원하지 않는 테이블에서 두번째 줄에서 오류시 첫번째 줄은 처리됨
   STRICT_ALL_TABLES의은 전체가 무시됨
 - 프로시저 생성시 지정된 sql_mode환경에 따라 동작함(생성후 Mode를 변경해도 프로시져는 반영안됨)
 - 프로시저 작성전에는 Strict모드로 지정하여 잘못된 데이터의 삽입,수정을 막아야함

 

 

[MySQL Stored Procedure]Stored Procedure 개요

Stored Program이란?
 - MySQL5 버전부터 지원
 - ANSI SQL 2003을 기준으로 채택(블럭구조기반)
 - 3가지 타입
   Stored procedure : 가장 일반적인 타임
   Stored functions : 프로시져와 비슷하지만 단일값만 return
   Triggers : 데이터베이스 activity발생시 반응되는 action정의

 

Stored Program 사용이유
 - 데이터베이스 보안 강화
 - 코드의 유지보수를 더욱 쉽게
 - 네트웍 트래픽 감소
 - 어플리케이션의 이동성을 강화
 
Procedure의 기초

 - 기본 샘플

 DELIMITER $$
 DROP PROCEDURE IF EXISTS HellloWorld$$
 CREATE PROCEDURE HelloWorld()
 BEGIN
   SELECT 'Hello World';
 END$$
 DELIMITER ;

 

 - 변수

 BEGIN
   DECLARE my_integer INT;
   DECLARE my_dob     DATE DEFAULT '1960-06-21'
   SET my_integer=20;
 END;

 
 - 파라미터의 모드

 DELIMITER $$
 CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT)
 BEGIN
   SET out_number=SQRT(input_number);
 END$$
 DELIMITER ;

 IN - 프로시저의 기본모드, 값을 전달받음
 OUT - 값을 할당할 수 있고 호출할 프로그램에 반환
 INOUT - 값을 전달할 수 있고 변경된 값 역시 확인 가능

 

 - 조건절(IF, CASE)

   IF(price>500) THEN
     SET discount=10
   ELSEIF (price>100) THEN
     SET discount=5
   ELSE
     SET discount=3
   END IF

   
 - LOOP의 사용(WHILE/END WHILE, REPEAT/UNTIL절 가능)

   my_simple_loop : LOOP
     SET counter = counter+1;
   END LOOP my_simple_loop;

 - SELECT INTO(변수에 값을 SELECT로 넣을 경우 사용)

   DECLARE total NUMERIC(8,2);
   SELECT SUM(sale) INTO total
   FROM sales

 

 - Store Function : IN파라미터만 사용가능, OUT, INOUT는 사용 불가, SQL내에서도 호출 가능

 - Trigger : DML문에 의하여 테이블이 변경되었을때만 실행됨

 

  

[MySQL 아키텍처]MySQL 5.1의 새로운 기능

파티셔닝의 지원
 - Range파티셔닝 : 특정 Row의 지정한 범위를 기준으로 처리
 - List파티셔닝 : 일련의 값들중 특정값들을 기준으로 처리
 - Hash파티셔닝 : 정해진 파티셔닝 개수에 따라 해시 함수를 이용해서 균등하게 처리
 - Key파티셔닝 : 정의된 키를 기준으로 처리
 - Sub파티셔닝 : 상기파티셔닝된것을 다시한번 파티셔닝 하는 방법

 

 

Rnage파티셔닝
 - 지정한 값의 Row를 기준으로 처리, 주로 기간 분할에 사용
 - Range의 법위는 연속적이여야하고 겹쳐서는 안됨
 - VALUES LESS THAN 구문 이용
 - MAXVALUE는 가능한 최대값을 이용
 - Alter table로 추가적인 파티션 생성 가능
 - 칼럼이 Date나 Time을 가지고 있을때 유용

 

 

List파티셔닝
 - Range와 흡사하나 List는 값들의 목록의 일부를 지정, 사원정보를 부서코드별로 파티셔닝
 - 5.1버전에서는 List는 정수값만 가능
 - 정수값 외에는 NULL가능
 - List에 해당하지 않는 값을 INSERT시 에러 발생

 

 

Hash파티셔닝
 - 정해진 파티셔닝 개수에 따라 데이터를 균등하게 배분
 - 개수만 지정해주면 나머진 알아서 처리됨
 - PARTITION BY HASH(exp)를 사용하는데 exp는 항상 정수형 값을 반환해야함
 - 테이블이 UniqueKey를 가진 경우 HASH함수에서 사용되는 컬럼은 반드시 그 키의 일부여야함
 - LINEAR HASH 파티셔닝 지원

 

 

KEY파티셔닝
 - HASH파티셔닝과 거의 흡사하나 유저가 지정한 키에 따라 처리
 - KEY파티셔닝을 위한 해시 함수는 MySQL서버에서 제공
 - Cluster는 MD5 사용 그외는 Password()와 같은 알고리즘 사용
 - PK있으면 사용하고 없으면 not null로 정의된 Unique Key를 사용
 - Key값이 정수형이나 Null이 아니어야하는 제한 없음

 

 

Sub파티셔닝
 - 파티션으로 분리된 파티션을 다시 분리
 - 모든 파티션은 동일한 개수의 서브 파티션을 지정해야함
 - 5.1.8이후 서브파티션의 이름은 유니크 해야함

 

XML 기능
 - 5.1.5버전 부터 XPath를 위한 기능 추가
 - 아직까지는 미약함

 

 

이벤트 스케줄러
 - 데이터베이스 배치 등의 반복잡업을 위한 스케줄러
 - 이벤트는 일회성 이벤트와 반복 이벤트로 구분됨
 - 이벤트 스케줄러 쓰레드가 실행

 - DB가 비정상종료시 스케줄도 무효가 되므로 사용에 유의

 

 

Row 기반 리플리케이션
 - 기존 Statement기반과 함께 추가적으로 Row기반이 추가됨
 - Row에 대한 이미지를 전송되어 처리
 - 세션별로 Statement, Row 구분 처리가능
 - Mixed 방식으로 비용이 낮은 것으로 처리 가능
 - 트랜잭션에 대한 안정성 보장(시간 등의 경우 서버별 시간차이발생 가능)
 - 슬레이브에서는 Lock를 적게 사용
 - 작은단위 트랜잭션의 경우 더 빠르게 처리가능

 

 

데이터베이스화된 로그
 - 로그를 DB화 시켜 저장시킬수 있게 되어서 기동중 On/Off를 처리 가능
 - 로그 처리 방법이 편해질 수 있음

 - 단 DB부하가 높아질 수 있어 사용에 주의

 

 

디스크 기반 MySQL 클러스터
 - 메인메모리 기반에 디스크 기반의 클러스터 기능 추가
 - 인덱싱되지 않은 데이터의 경우 디스크에 저장 가능
 - 리플리케이션 기능이 추가됨

 

[MySQL 아키텍처]Lock & Transaction

트랜잭션이란 무엇인가?

 - ACID 기준으로 정의된다.
  Atomic - 원자성, 하나의 작업단위로 하나의 명령으로 수행되거나 모두 수행되지 않아야 함
  Consistency - 일관성, 트랜잭션전 후에 모두 일관된 데이터여야 함
  Isolation - 분리성, 트랜잭션은 다른 트랜잭션과 완전하게 분리되어야 함
  Durablity - 완전성, 처리완료된 트랜잭션은 영구적으로 보존되어야 함

 

 

MySQL에서의 트랜잭션
 - AUTOCOMMIT옵션은 트랜잭션을 어떻게 묶을까 결정됨(기본은 설정)
 - AUTOCOMMIT=1(Default)
   예외는 BEGIN 또는 BEGIN WORK로 시작하고 COMMIT나 ROLLBACK를 완료해야함
 - AUTOCOMMIT=0
   각각의 COMMIT또는 ROLLBACK는 하나의 트랜젹션을 완료하며 동시에 새로운 트랜잭션을 시작

 

스토리지 엔진별 트랜잭션 지원
 - InnoDB : Row레벨 락킹, Select는 락없이 수행, 테이블별 테이블스페이스 사용가능
 - BDB : 페이지 레벨 락킹, 테이블별 하나의 파일
 - MyISAM : 지원안함
 - NDB Cluster : Row레벨 락킹, 클러스터 테이블

 

 

락킹이란 무엇인가?
 - 데이터에 대한 동시접근을 제어
   UPDATE는 READ를 제한함, 락의 종류에 따라 성능,일관성 차이
 - 각각의 스토리지 엔진은 락킹에 관련한 각각의 알고리즘 보유

 

 

락의 종류
 - 테이블레벨 락킹(MyISAM,Memory) : 업데이트 중 테이블의 접근 불가
 - 페이지레벨 락킹(BDB) : 업데이트 중 동일 테이블 해당 페이지 접근 불가
 - Row레벨 락킹(InnoDB, NDB) : Commit전에는 해당 Row에 접근 불가

 

 

락의 특성 비교
 - 테이블레벨 락킹 장점
   오버해드가 적음,매우 빠른 읽기,데드락이 없음,읽기가 많고 키 기반 업데이트 어플리케이션에 적합
 - 테이블레벨 락킹 단점
   트랙잭션 지원불가, UDATE가 많으면 성능 저하

 - Row레벨 락킹의 장점
   동접이 많아도 충돌이 없음, 롤백을하는 경우 변경이 적음, 좀 더 긴시간 락 설정 가능, 변화가 많은 어플리케이션에 적합
 - Row레벨 락킹의 단점
   메모리양의 따라 성능 영향받음, 많은 ROW작업시 느림, GROUP BY와 테이블스캔 성능이 낮음, 데드락 발생 가능성 있음

 

 

MyISAM의 트랜잭션과 락킹
 - 트랜잭션 지원안함, 즉각 커밋
 - WRITE 또는 READ 락 사용
 - LOCK가 이미 걸려 있으면 LockQueue이용
 - WRITE가 우선권
 - 많은 경우 락킹없이 INSERT와 SELECT 사용 가능(충돌이 없다고 판단 되면 동시 수행)

 

 

MyISAM의 테이블락킹
 - LOCK TABLES를 통해서 수동으로 걸수 있음
 - LOCK가 걸린 테이블은 UNLOCK TABLES를 쓰거나 쓰레드가 죽지 않는한 유지
 - READ Lock가 걸리면 어떠한 내용도 쓸수 없음(읽기는 가능)
 - READ LOCAL LOCK은 충돌이 없을 경우 INSERT LOCK이 있어도 처리 가능
 - WRITE LOCK는 LOCK를 걸고 있는 쓰레드만이 테이블에 접근 가능(UPDATE시)

 

 

InnoDB의 트랙잭션 MVCC(Multiversion Concurrency Control)
 - 테이블 스페이스는 동일 데이터에 대한 여러가지 버전 보유
 - 각각의 트랜잭션이 각각의 테이블 스페이스를 가지고 처리(isolation)
 - Roll Pointer은 예전 데이터를 가리킴(Select시에는 이전 데이터를 보게됨)
 - 로그파일용량을 넘어서는 트랜잭션 처리를 위해서 테이블스페이에 기록할 경우도 있음

 

 

InnoDB의 Isolation레벨
 - Read Uncommitted(Dirty Read) : Isolation이 깨진 상태
   쿼리는 진행되거나 커밋된 모든 트랜잭션의 결과를 볼수 있음
   읽기는 락을 걸지 않으며 쓰기에 경우에만 row에 락을 설정
   최고의 성능

 - Read Committed
   읽기 쿼리는 그 커밋된 트랜잭션의 결과만을 볼 수 있음

 - Repeatable Read
   InnoDB의 표준
   새로 시작된 트랜잭션은 그 트랜잭션이 시작되기 전에 커밋된 트랜잭션의 결과만을 보게됨
   트랜잭션안에 포함된 모든 읽기 쿼리는 동일함
   팬텀리드의 문제가 있을 수 있음

 - Serializable
   모든 읽기 쿼리도 해당 row에 락을 설정
   실제하지 않는 유령값이 존재할 수 없음
   Tow phase commit - 커밋이 수행될 것임을 확인할 수 없음

 

 

[MySQL 아키텍처]아키텍쳐

MySQL의 내부구조
 - Connection Pool 이 접속 제어
 - SQL Support, Parser, Optimizer, Caches&Buffers 등의 앞단에서 쿼리를 처리
 - Storage
 - MySQL은 Pluggable Storage Engines를 사용 - 다양한 스토리지 엔진을 사용할수 있다
   예) InnoDB, MyISAM, 등.
 - 어플리케이션에 따라서 스토리지 엔진을 선택하여 사용할 수 있다.

 

 

Connection Pool
 - 실제적으로는 Thread Pool 임(Thread Reuse 가능)
 - 유저의 접속시 쓰레드를 할당함. 프로세스기반의 오라클보다 성능상 이익
 - 각각의 쓰레드에 메모리 캐쉬를 할당함
 - 유저의 접근 인증을 처리함
 - 트래픽이 많은 웹사이트와 어플리케이션에 유리함.

 

 

SQL Support
 - SELECT, DML, DDL, 스토어드 프로시져, 트리커, 뷰 등을 지원
 - SQL에 대한 함수 지원

 

 

Parser/Optimizer
 - 유저의 오브젝트 접근 권한 확인
 - SQL을 데이터베이스 내부 언어로 변환
 - 사용자의 SQL요청의 최적화된 처리를 결정

 

 

메모리 캐시
 - 빈번하게 사용되는 인덱스와 데이터를 메모리에 캐시
 - 쿼리 캐시는 Select문과 결과의 조합을 캐시하여 매우 빠른 응답을 지원
 - 데이터베이스 메타데이터 메모리 캐쉬를 제공
 - 서버전체, 엔진별, 유저별 캐시를 포함
 

관리도구
 - 데이터베이스 관리(MySQLAdmin), 쿼리 브라우징(Query Browser), 데이터 베이스 디자인(WorkBench), 마이그레이션등을 위한 GUI제공
 

엔터프라이즈 서비스
 - 인증,오브젝트별 권한관리, 데이터 암호화의 보안 제공
 - 백업,복구,복제,클러스터링 등의 제공

 

 

플러그인 스토리지 엔진 구조
 - 사용중인 어플리케이션에 맞은 엔진 선택
 - 단일 어플리케이션 안에서 여러 스토리지 엔진을 쉽게 조합하여 사용가능

 

 

스토리지엔진의 종류별 특징
 - 표 참고

MySQL 스토리지엔진별 특징

MyISAM - 기본적인 특징
 - 비활성화 할수 없는 기본 스토리지 엔진
 - 데이터 저장에 실제적인 제한이 없음(파일시스템의 제한과 동일)
 - 데이터를 매우 효율적으로 저장
 - 빈번한 데이터 사용(Read)의 부하를 잘 소화
 - B-tree,R-Tree, Full-Text 인덱스 지원
 - 특정 인덱스에 대한 메모리 캐시지원(데이터는 캐시하지 않음)
 - 데이터 압축 옵션 제공(압축하게 되면 ReadOnly가 되지만 디스크효율이 높아짐)
 - 지리적 데이터 지원
 - 테이블 레벨의 락
 - 트랜적션 미지원
 - 백업 및 특정 시점으로의 복구 지원

 - 적합한 사용처 : 트레픽이 많은 웹사이트(Read트래픽), 데이터 웨어하우스

MyISAM - 테이블의 구성
 - 하나의 테이블은 .frm .MYI .MYD 총 3개 파일 사용
 - Merge테이블은 .frm이라는 테이블 정의 파일과 .mrg라는 테이블 목록 파일로 이루어짐
   예) 동일한 구조의 '월,화...일' 테이블이 있고 '일주일'이라는 통합 테이블이 있는 경우
 - .MYI 인덱스 저장파일, 파일의 크기는 1024byte의 배수로 증가
 - .MYD 데이터 저장파일, 레코드 길이의 배수로 증가
 - .MRG merge테이블에 포함되는 테이블의 목록을 담고 있는 텍스트 파일(트릭사용가능)

 

MyISAM - Merge
 - 여러 동일한 구조의 테이블을 하나의 테이블로 사용
 - ALTER TABLE로 추가하거나 삭제 가능
 - 테이블에 포함되는 모든 테이블에 대한 권한 필요
 - SELECT, INSERT, UPDATE, DELETE 작업을 지원
   (INSERT 작업시에는 INSERT_METHOD를 지정해야함)

 

InnoDB - 기본적인 특징
 - ACID 트랜잭션 지원
 - 테이블스페이스당 64TB 저장 지원
 - MyISAM보다 데이터 저장 비율이 낮음
 - 다른 엔진들에 비해서 느린 데이터 로드 속도
 - MVCC/Snapshot read 지원
 - B-tree, clustered 인덱스 지원
 - 데이터와 인덱스 메모리 캐시 지원
 - 외부키 지원
 - 데이터 압축 옵션을 제공하지 않음
 - row레벨 락을 지원 하며 isolation 레벨 지원
 - 자동 에러 복구 기능
 - 백업 및 특정 시점으로 복구 지원

 

InnoDB - 적합한 사용처
 - 온라인 트랜잭션을 지원하는 어플리케이션

 

InnoDB - 구조

InnoDB 구조

 - Buffer pool이 MySQL의 성능을 좌우
 - Additional Memory Pool - 메타데이터 저장
 - 다수의 로그 파일 - 로그작성시 병목 제거

 

 

MySQL의 기본적인 DataFile 구조
 - Data - Hostname.err(에러로그), Hostname-slow.log(슬로우쿼리로그), Ibdata1 - innodb기본 테이블 스페이스, Ib_logfile0x(innodb redo log파일)
 - Data/mysql - 기본적으로 존재
 - Data/test - 기본 생성되는 DB 유저 관련 데이터와 인증관련 데이터
 - Data/유저생성MyISAM - 테이블명.frm(테이블정보파일), 테이블명.MYI(인덱스), 테이블명.MYD(데이터)
 - Data/유저생성InnoDB - 위와 동일, 테이블명.ibd(file per table옵션일 경우 테이블별로 생성되는 스페이스)

 

InnoDB - 테이블의 구성
 - 최소한 하나의 테이블 스페이스와 redo로그 파일
 - MyISAM과 호환을 위해 DB별로 디렉토리 생성
 - 테이블별 .frm파일 존재
 - 테이블별 테이블 스페이스 생성시 .idb라는 테이블 스페이스 파일 생성
 - redo로그는 기본적으로 두개의 파일 생성
 - 테이블별로 테이블스페이스를 생성하더라도 Undo로그는 테이블이 고유하는 기본 테이블 스페이스에 생성되어 테이블 스페이스 복사로 복제는 불가능

 

 

InnoDB와 MyISAM의 성능상 차이
 - InnoDB가 디스크 공간은 많이 사용
 - InnoDB가 PK기반 Select는 우수함
 - InnoDB가 Index Rebuild속도가 느림
 - InnoDB가 Count(*)은 MyISAM에 비해 느림
 - InnoDB는 Fixed Length row가 없음
 

InnoDB의 제약
 - 최소테이블스페이스 크기 10M(최대 64TB)
 - SHOW TALBE STATUS 결과는 근사치결과
 - 풀 텍스트 인덱스 지원 안함
 - 데드락은 자동으로 검출되며 그런경우 트랜잭션 자동 롤백

Cluster(NDB) - 기본적인 특징
 - 트랜잭션 지원
 - 모든 데이터와 인덱스 메모리에 저장
 - 매우 빠른 데이터 로드 속도
 - MVCC/Snapshop read를 지원
 - B-tree인덱스 지원
 - 프라이머리 키 사용시 최상의 속도
 - 99.999% uptime제공
 - 클러스터간 어떤것도 공유하지 않는 구조(Shared Nothing)
 - SQL API와 함께 고속적근 API제공(이걸 사용해야 최적의 성능)
 - 온라인 백업과 특정 시점 복구 지원

 

Cluster(NDB) - 적합한 사용처
 - 고가용성이 필요한 어플리케이션
 - 고속의 데이터/키 룹업이 필요한 어플리케이션
 - 대량의 사용자의 인증서버
 
Archive - 기본적인 특징
 - 5.0버전에 도입
 - 자동적인 데이터 압축지원
 - 다른 스토리지 엔진 대비 20%저장공간
 - 저장용량 제한 없음
 - 가장 빠른 로드 속도(벌크 인서트 최적화)
 - MVCC/Snapshot read 제공
 - 인덱스 미지원
 - 빠른 Insert 속도를 위한 입력 버퍼 지원
 - row레벨락 지원
 - 백업 및 복구 지원

 

Archive - 적합한 사용처
 - 해를 두고 계속되는 데이터를 위한 데이터 워어하우스
 - 데이터 저장 어플리케이션
 - 데이터 감사

 

Federated - 기본적인 특징
 - 5.0에 새롭게 도입
 - 원격의 물리적 데이터베이스에 대한 논리적 데이터베이스로 사용(DB Link)
 - 하나의 데이터베이스에서 다른 타겟오브젝트로의 '포인터'역할
 - 원격접근을 위한 미들웨어 필요 없음
 - 실행속도는 네트워크 요소에 좌우됨
 - SSL보안 처리 가능
 - 모든 SQL 오퍼레이션 지원

 

Federated - 적합한 사용처
 - 분산데이터베이스 환경

 

 

Memory - 기본적인 특징
 - 완전하게 메모리에 존재
 - 갑작스런 종료시 데이터 소실
 - 너무 많은 메모리 사용제한을 위해서 MAX_ROW를 설정하는 것이 현명함
 - Auto_increment 지원
 - B-Tree 인덱스 지원
 - varchar칼럼 지원(내부적으로는 char와 동일한 형태로 지원)
 - 대량의 데이터 로드시 MyISAM에 비해 30% InnoDB에 비해 50% 성능향상

 

Memory - 테이블 제약
 - fixed length레코드만 지원
 - BLOG나 Text사용불가
 - Hash인덱스는 하나의 row를 찾기 위해 모든 키를 사용
 - Hash인덱스는 Where 조건절을 사용할 경우 '='이나'<>'인 경우만 사용가능(하지만 매우빠름)

리플리케이션 아키텍쳐
 - 마스터 서버 : Binlog활성화, Slave에서의 접근을 위한 유저 생성
 - 슬레이브 서버 : 두개의 쓰레드가 추가 생성
   IO-Thread : Binlog모니터링 후 RelayBinlog에 복제
   SQL-Thread : RelayBinlog의 내용을 실행
 - 구성이 쉽다
 - 동작 자체가 명쾌하다
 - ASync 동작

 

다양항 형태의 리플리케이션
 - 하나의 마스터 다수의 슬레이브 : 리드 부하분산 및 백업
 - 양방향마스터 : 서로서로 마스터 슬레이브 관계, 양방향 장애 대응
 - 링형 : 특별한 목적이 있지 않는 한 피해야함
 - 피라미드형 : 마스터, 슬레이브/마스터, 슬레이브 형태의 다단계 구조, 넓은 지역을 커버하기 위한 구조(다중 IDC)

 

[MySQL 쿼리 및 인덱스의 이해]기타 SQL

High_Priority
 - 높은 우선순위로 결과를 받아오도록 질의함
 - mysql> SELECT HIGH_PRIORITY * FROM USER

 

 

Query_Cache
 - 쿼리결과를 캐슁
 - 해당 테이블이 변경(Insert,Update)가 처리되면 모든 캐쉬 Flush

 

 

Low_Priority
 - INSERT 시 옵션을 주면 큐의 모든 Select가 완료될때까지 기다림
 - 슬레이브(백업)서버의 경우 서버옵션으로 지정해놓으면 동기화는 느려도 Select는 빨라짐

 

 

대용량 INSERT
 - MultiRow를 사용하면 성능이 높아짐
 - MyISAM의 경우 8배, InnoDB에서는 30배 성능 향상
 - 대용량 처리시 disable_key를 사용해서 인덱스 생성을 멈추고 완료후 활성화
 

DELETE 성능향상
 - DELETE QUICK : MyISAM에서만 사용가능. 내부적인 인덱스 정리작업 처리안함
 - 모든 데이터를 삭제할 경우에는 Drop Table 처리
 - MyISAM 경우 Truncate Table를 사용가능(내부적으로 Drop Table처리)

 

Count(*)은 위험하다
 - Count를 위해서 별도의 테이블을 만드는 것이 좋은 해결책
 

Count(*) 쿼리가 필요할까?
 - Google같이 대략적인 COUNT로 해결
 - Limit를 사용하여 1000개 이하는 정확한 수를 1000개 이상은 1000+라고 표시

 

 

Count(*)의 속도 높히기
 - MyISAM는 전체 테이블 카운터를 가지므로 빠른 결과
 - InnoDB는 풀스캔처리
 - Count결과에 영향을 주지 않는 Join을 제거
 - 인덱스를 사용하도록 처리(UsingIndex)가 explain의 extra의 결과에 나오도록 처리

 

 

높은 Limit값 다루기
 - SELECT * FROM users ORDER BY last_online DESC limit 100000,10
 - 처음10만row 검색후 버리게 되므로 비용이 높다
 - DDos공격대상
 - 검색엔진 봇은 높은 Page를 자추 찾아들어감
 - 빠르게 동작하는걸 보장하지 못할경우 제한하는 방법 : Google 등

 

 

높은 Limit : 가능한 결과를 미리 생성
 - SELECT * FROM sites ORDER BY visits DESC LIMIT 100,10
 - 아래와 같이 레이팅을 처리하는 테이블을 추가하는 방법
 - SELECT * FROM sites_rating WHERE position BETWEEN 101 and 110 ORDER BY position
 - 실시간이 필요 없는 경우 통계테이블을 추가하면 성능 향상이 있음

 

 

배치작업에서의 LIMIT의 사용
 - 배치작업에서는 Limit n,10000 보다 Where id Between n and n+99999 형태로 변경
 - 정확한 개수는 필요 없을 경우
 

파생테이블(Derived Table)사용하기
 - 파생테이블이란 : WHERE id IN (SELECT ID FROM tb)
 - 부분적으로 인덱스를 사용할 수 있도록 함
 - 파생테이블 두개를 조인하게 되면 MySQL에서는 풀조인을 하므로 주의

 

 

FROM절의 서브쿼리
 - SELECT ... FROM (SELECT ...) WHERE ...
 - 서브쿼리를 완전 별개로 처리함. 즉 Temp Table로 사용. Temp Table은 인덱스 사용암함
 - Join으로 분할 해야함

 

 

Sorting시 File Sort 피하기
 - SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5
 - MySQL에서는 정렬시 인덱스 사용못함(모든 컬럼에서 = 비교를 하는 경우를제외)
 - 정렬작업을 위해서 File Sort함
 - 데이터 양이 많을 경우 시간이 오래 걸림
 - UNION으로 쪼개면 인덱스만 사용하게 됨
 - (
   SELECT * FROM TBL WHERE A=1 ORDER BY B LIMIT 5
   UNION
   SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5
   ) ORDER BY B LIMIT 5

 

[MySQL 쿼리 및 인덱스의 이해]Status값을 활용한 Query성능 평가

MySQL STATUS
 - 수집된 정보를 메모리상에 관리 : SHOW STATUS를 통해서 모니터링 가능
 - 각항목을 확인하여 서버동작 상황 모니터링
 - 성능을 최적화할 때 기본적인 가이드 제공
 - 아래 두가지 방법으로 확인 가능
   mysqladmin -i 10 extended-status //10초마다 로그출력
   MySQL Administrator에서 GUI로 확인 가능

 

기본적인 STATUS 모니터링
 - mysql> show [session,global] status
 - uptime : 서버가동시간
 - com_xxx : 서버시작이후 xxx관련 명령이 얼마나 수행되었나(기본은 세션별)

 

 

MySQL Handler Status값의 의미
 - mysql> show session status like 'Handler_read%';
 - MYSQL이 수행하는 논리적 row 단위 operation은 handler변수에 의해 카운트 됨
 - 값의 의미

   Handler_read_first : Index의 첫번째 Node Access, FULL_INDEX_SCAN
   Handler_read_key : 특정값으로 Tree Search를 통해 index Node를 선택하는 경우, INDEX_SEEK
   Handler_read_next : 인덱스의 leaf노드들의 링크를 Range스캔할 경우
   Handler_read_prev : 위와 반대로 역순 Range스캔
   Handler_read_random : Sorting같은 Buffer에 저장된 row에 대한 Access
   Handler_read_random_next : 데이터Block나 Temp Table에서 순차적으로 row를 읽는 경우,FULL_TABLE_SCAN

 - Index를 통한 RowAccess : first + key + next + read_prev
 - Table scan을 통한 RowAccess : random + random_next
 - 가급적 Index를 통한 Access가 일어나도록 유도하는 것이 바람직함

 

 

SHOW XXX_STATISTICS의 사용
 - 기본적으로 MySQL에서 제공하지 않는 User,Table, Index와 관련된 정보를 보여줌
 - Google에서 제작한 패치
 - SHOW USER_STATISTICS
 - SHOW TABLE_STATISTICS
 - SHOW INDEX_STATISTICS

 

 

MYSQL XXX-STATISTICS의 사용
 - mysql> show index_statistics like '%SERVERDB%'

 

[MySQL 쿼리 및 인덱스의 이해]조인(Join)의 종류와 수행속도

Nested Loop

 - 순차적 처리(첫번째 테이블 처리후 두번째 테이블 처리)
 - 종속적(먼저 처리되는 테이블의 처리범위에 따라 처리량 결정)
 - 랜덤(Random) 엑세스 위주
 - 연결고리 상태에 따라 영향이 큼
 - 좁은 범위에 처리에 적합

 

 

Sort Merge
 - 두개의 테이블을 따로 처리후 정렬된 결과를 가지고 Join처리
 - 전체 범위 처리에 유리
 - 연속적인 엑세스 위주
 - MySQL에서는 지원안함

 

 

조인의 수행속도 결정 요소(Driving)
 - 테이블A(1만개) > 테이블B(1천개) > 테이블C(2개) : 최소 10,000회 이상 엑세스
 - 테이블C(2개) > 테이블B(1천개) > 테이블A(1만개) : 최대 6회 이하 엑세스
 - 옵션으로 사용자가 지정가능
 

조인의 수행속도 결정 요소(Join순서)
 - 조인된 결과의 수가 다음 연결에 대한 일량에 영향을 미침

조인의 수행속도 결정 요소(Index)
 - 연결되는 컬럼의 양쪽에 인덱스가 있으면 성능이 높다

[MySQL 쿼리 및 인덱스의 이해]인덱스 사용 전략

인덱스 사용전략
 - Mysql> SHOW INDEX FROM index_name \G
 
 - 필드 해석
   Table : 테이블명
   Non_unique : 중복이면 1 아니면 0
   Key_name : 인덱스에 할당된 키 이름
   Seq_in_index : 멀티컬럼인덱스일 경우 순서
   Column_name : 컬럼이름
   Collation : 인덱스의 정렬방식 A(Asc), D(Desc-지원안함) 이나
   Cardinality : 인덱스에서 유니크한 값의 개수. 값이 낮다는 것이 중복이 많다는 것(예:성별은 남,여로 2)
   Sub_part : 컬럼이 부분적으로 인덱싱 되었을때 길이
   Packed : 인덱스의 압축여부. 압축하지 않았을때 Null
   Null : 컬럼이 Null값을 가질수 있으면 Yes, 아니면 NO
   Index_type : 인덱스의 타임(RTREE,FULL TEXT,HASH,BTREE)

 

 

인덱스와 관련된 로그
  - 로그를 통해서 추가적인 정보획득 가능
  - "--log_queries-not-using-indexes" 슬로우 쿼리 로그에 인덱스를 사용하지 않은 쿼리 로그를 남김
  - 슬로우 로그사용시 "-log-slow_queries"옵션을 통해서 활성화 해야함

 

MyISAM의 인덱스 구조
 - PrimaryKey : Leaf 노드에는 ROW Number가 들어 있다
 - Secondary Index : Leaf 노드에는 ROW Number가 들어 있다.
 - Key Cache :
    인덱스를 메모리에 저장.
    인덱스를 디스크에서 읽을때보다 10배빠름.
    없을경우 시스템 캐쉬사용.
    전체물리메모리의 512M-1G정도 지정.
   

InnoDB의 인덱스 구조
 - 내부적으로 Clustered인덱스라는 구조를 통해 저장. 인덱스의 순서에 따라 물리적 데이터 저장.
 - 프라이머리키가 존재하면 : Clustered인덱스
 - 프라이머리키가 없으면 : 유니크 인덱스를 자동으로 지정
 - 프라이머리키, 유니크 없으면 : 자체적으로 rowID라는 6바이트 유니크 컬럼을 생성해서 입력순서에 따라 생성. show index로 보이지 않음
 - Clustered 인덱스 : Leaf노드에 데이터가 저장됨
 - 다른 모든 인덱스는 Leaf노드에 Clustered인덱스 주소를 가짐
 - 어떠한 컬럼이 Clustered 인덱스가 되느냐가 중요 (크기가 크지 않으며 자주사용되는 컬럼을 이용)

 

 

인덱스 선정 절차
 - 해당 테이블 엑세스 유형 조사
 - 대상 컬럼의 선정 및 분포도 분석
 - 반복 수행되는 엑세스 경로의 해결
 - 인덱스 컬럼의 조합 및 순서 결정
 - 시험생성 및 테스트
 - 수정한 필요한 애플리케이션 조사 및 조사
 - 일괄 적용

 

 

인덱스의 선정기준
 - 분포도가 좋은 컬럼은 단독적으로 생성
 - 자주 조합되어 사용되는 경우 결합인덱스
 - 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역활분담
 - 가능한 수정이 빈번하지 않는 컬럼
 - 기본키 및 외부키(조인의 연결고리 컬럼)
 - 결합 인덱스의 경우 컬럼 순서 주의
 - 반복수행 되는 조건은 가장 빠른 수행속도를 내게 할 것

 

 

인덱스의 활용시 고려사항
 - 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음
 - 지나치게 많은 인덱스는 오버헤드 발생
 - 넓은 범위를 인덱스 처리시 많은 오버헤드발생
 - 옵티마이저를 위한 통계데이터를 주기적으로 갱신
 - 인덱스의 개수는 적절히 생성
 - 분포가 양호한 컬럼도 처리범위에 따라 분포도가 다를수 있음
 - 인덱스 사용원칙을 준수해야 인덱스가 사용되어진다
 - 조인시 인덱스 사용여부에 주의
 

Index Cardinality
 - SHOW INDEX에서 확인 가능
 - 가능하면 높은 값을 유지하도록 하는 것이 좋음
 - 높은 컬럼에 인덱스가 필요한 경우 복합 인덱스를 고려
 

문자형 인덱스 VS 숫자형 인덱스
 - 문자형 인덱스 성능 < 숫자형 인덱스 성능
 - 숫자형 컬럼이 작은 싸이즈

최적의 컬럼 타입 선택을 위한 툴 - Procedure Analyse
 - 사용법
   SELECT * FROM 데이블명 PROCEDURE Analyse(처리할컬럼수)

 

 

다중컬럼사용시 빈번한 실수
 - Index(columnA, columnB) 일 경우 WHERE columnB='ABCD' 일경우 인덱스 사용안함
 - WHERE columnA LIKE '%rane' 일 경우 와일드카드가 앞에 있으므로 인덱스 사용안함
 - ORDER BY columnB, columnA 일 경우 인덱스 사용안함

 

[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)] ...);