MySQL에는, 스토리지 엔진이라고 불리는 각종 스토리지를 사용할수 있다. 데이터는 디스크, 메모리 및 네트워크에 저장할수 있다. 데이터베이스의 각 테이블에는, 사용가능한 임의의 스토리지엔진을 사용할수 있다. 디스크 스토리지는 저가로 수명이 길지만, 메모리는 보다 고속이다.

스토리지 엔진의 특징은 다음과 같다.

1.     InnoDB:트랜잭션이 사용되기 때문에, 읽기 쿼리와 쓰기 쿼리를 모두 쓰는 경우에 적합.

InnoDB는, MySQL의 테이블향 디폰트의 다목적(읽기집중형, 읽기/쓰기, 트랜잭션)스토리지 엔진이다.

2.     MyISAM:데이터의 읽기가 대부분으로, 갱신은 거의 없는 경우에 적합.

3.     MEMORY:모든 데이터가 메모리에 저장된다.

4.     NDB:이중화되고 확장성이 있는 토폴로지에 의해 데이터의 가용성을 높이기 위해, MySQL Cluster로 사용된다.

주의:스토리지 엔진은 , 단순한 스토리지 레이어를 넘어, 스토리지 이외의 것도 구성할수 있다. 그외의 구조와 실전메커니즘도 준비하고 있다.

다음특성은 스토리지 엔진에 의존한다.

1.     스토리지 미디어:스토리지엔진에는, 디스크,메모리,네트워크등의 각종미디어에 데이터를 저장하도록 선택할수 있다.

2.     트랜잭션기능:ACID트랜잭션기능을 완전히 서포트하고 있는 스토리지 엔진도 있으면, 트랜잭션 서포트가 없는 스토리지 엔진도 있다.

3.     락:스토리지엔진에는 , 각종 락정도(테이블레벨의 락과 행레벨의 락등)및 메커니즘을 사용해서, 동시트랜잭션과의 일관성을 확보할수 있다.

4.     백업 및 리커버리:스토리지 엔진에 의해 데이터의 저장/조작방법의 영향을 받는 경우가 있다.

5.     최적화:각종 인덱스 설정의 실전이 최적화에 영향을 줄수 있다. 스토리지엔진에는 ,퍼포먼스를 최적화가기 위해, 여러방법으로 내부캐쉬, 버퍼 및 메모리를 사용합니다.

6.     특별한 기능:일부 엔진 타입에 한정해서, 전문검색, 참조정합성, 공간데이터처리등의 각종 기능이 준비되어있다.

옵티마이져는 스토리지 엔진에 따라서 각종 선택을 할 필요가 있으나, 각 스토리지엔진이 서포트하고 있는 표준화된 인터페이스(API)에 의해 모두 처리됨.

주의:스토리지엔진의 상세와 관련개념은, 이 코스의 후속장에서 설명하고 있다.

우선 테이블을 작성한다고 치자.여기에 매월 10만건이상의 레코드가 들어올 예정이다.
1레코드가 57byte이므로, 월에 5.7Mbyte, Primary Key를 넣으면60Mbyte정도가 들어온다. 
연간으로 하면 720Mbyte이므로, 데이터양적으로는 여유라고 생각되지만,

100만레코드를 넘으면 응답이 느려지는 현상이 있다.

그런 이유로, MySQL에 있는 파티셔닝 기능을 사용해서, 데이터를 나누고자 생각한다.

테이블의 작성

주의할 점으로써, 파티셔닝의 키로 하고싶은 칼럼을, Primary Key에 포함시킬 필요가 있다.

그러므로, Auto Increment의 컬럼이 있는 테이블이면 힘들다.구성을 다시하는것이 좋을지도..

CREATE TABLE `list_rtx` (
 `member_id` varchar(40) CHARACTER SET sjis COLLATE sjis_bin NOT NULL,
 `platform` varchar(10) NOT NULL,
 `year` smallint(5) unsigned NOT NULL,
 `month` tinyint(2) unsigned NOT NULL,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`member_id`,`year`,`month`,`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8

파티션의 작성

1년째의 파티션을 나누고자 한다.

년을 기준으로 범위를 정해서, 나눈다.

그러므로, 이번은 RANGE를 사용한다.

ALTER TABLE `list_rtx` PARTITION BY RANGE (YEAR(`year`)) (
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);

파티션을 나중에 조작하는것은, 서비스가 가동하고있는 경우는 유지보수를 하지않으면 안된다.

그러므로, 할수있는한 처음부터 Usecase를 구체적으로 상정해서, 상정보다 조금더 많은 파티션을 작성한다.

파티션의 확인

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

어느 파티션이 사용되고있는지는 이하. EXPLAIN PARTITION을  상기에 추가

EXPLAIN PARTITIONS
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

파티션의 삭제

이 파티션내의 데이터도 지워진다／(^o^)＼

ALTER TABLE list_rtx DROP PARTITION p2015;

 파티션의 추가,재구성

파티션의 추가는 기본적으로 지금 있는 파티션의 뒤에밖에 생기지않는다.

그러므로, maxvalue를 사용해서 나눈경우, 파티션을 추가할수 없게된다.

그렇기 때문에, 추가가아닌, 파티션전체를 재구성시킨다.

단, 데이터는 지워지지않는다.

예: 2013년보다 이전의 데이터를 보존하는 것이되었기 대문에, 파티션을 추가하고 싶다.

ALTER TABLE mau_list_rtx REORGANIZE PARTITION p2013 INTO (
    PARTITION p2010 VALUES LESS THAN (2010),
    PARTITION p2011 VALUES LESS THAN (2011),
    PARTITION p2012 VALUES LESS THAN (2012),
    PARTITION p2013 VALUES LESS THAN (2013)
);

복합파티셔닝

예를 들면 [매월]로 파티션을 나누고 싶어, 테이블에는 [년]과 [월]이 다른 컬럼에 있는 경우.

서브 파티션(복함파티셔닝)을 사용한다.

ALTER TABLE `list_rtx`
PARTITION BY RANGE (YEAR(`year`))
SUBPARTITION BY HASH (MONTH(`month`))
SUBPARTITIONS 12 (
    PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
    PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
    PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
    PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
    PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
    PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
    PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

MySQL에의 접속이 블록되었을 경우

DB Firewall 그룹에서 모든 액세스를 허가하면, 외부로부터 부정한 액세스에의해 접속에러가 빈발해서, MySQL의 접속이 블럭되는 경우가 있다.

접속이 블록되었을때 하기의 에러가 표시된다.

이런 경우, DB 파라메터 그룹을 이용해서 , max_connect_errors의 값을 일시적으로 늘리면(1000정도),      다시접속을 할수 있다.

접속이 되면, FLUSH HOSTS를 실행하거나, mysqladmin flush-hosts명령어를 실행하는것으로, 접속에러회수를 리셋할수 있다.

MySQL에서 Store Procedure를 mysql.proc을 보면 쓰여져있다고 하지만 실재없는 프로시져도 있다.

하기의 방법으로 알아볼수 있다.

각각의 정의문은

MySQL커넥터에 의해, 클라이언트 프로그램과 MySQL서버와의 접속이 확립됩니다.

API에 의해서, MySQL프로토콜 및 MySQL리소스의  하위레벨에의 액세스가 제공됩니다.

커넥터와 API의 양쪽을 사용하는 것으로, 다른 언어 또는 환경에서 접속해서, MySQL문을 실행할수 있습니다.

MySQL Connector/MXJ가 제공되고 있습니다만, 현 시점에서는 개발중이므로, 완전히 서포트되고 있지는 않습니다.

MySQL에는, 다음 Third-Party connector가 제공되고 있습니다.

• PHP: mysqli, ext/mysqli, PDO_MYSQLND, PHP_MYSQLND

• Perl: DBD::mysql

• Python: MySQLdb

• Ruby: DBD::MySQL, ruby-mysql

• C++ Wrapper: For MySQL C API (MySQL++)

임베디드 MySQL 서버 라이브러리(libmysqld)도 제공되고 있습니다. 

Libmysqld를 사용하면, 클라이언트 어플리케이션내에 풀기능의 MySQL서버를 실행할수 있습니다. 주요 이점은, 임베디드 어플리케이션의 속도향상과 관리의 간략화입니다.