into MySQL: SQL

레이블이 SQL인 게시물을 표시합니다. 모든 게시물 표시

2010년 12월 25일 토요일

MyISAM 테이블과 InnoDB 테이블의 통계 정보

MySQL은 각 테이블들에 대해서 INFORMATION_SCHEMA 데이터베이스의 STATISTICS라는 테이블에 통계 정보를 관리하고 있다.

이러한 통계 정보는 쿼리의 실행 시에 최적의 실행 방법을 찾아내기 위한 기초 데이터로 사용된다.

대표적으로 MySQL 옵티마이저가 인덱스를 사용해서 데이터를 조회할 지 또는 테이블 전체를 스캔할 지 등의 결정을 하는 데 사용된다.

MySQL의 통계 정보는 Oracle과 같은 다른 DBMS와는 달리 히스토그램 정보는 없으며 단순히 Cardinality만 관리한다.

여기서 Cardinality라 함은 해당 인덱스 값의 Unique 값의 수를 의미한다.

또한, MySQL은 Oracle과 달리 통계 정보가 상당히 동적이다. 동적이라 함은 여러 가지 Event에 의해서 상당히 자주

업데이트됨을 의미한다. 그래서 MySQL에서는 Oracle과 같이 통계 정보를 마이그레이션한다거나 정확한 통계 정보 수집을

위해서 ANALYZE를 해둔다는 것은 의미가 없다 (라고 자주 이야기되어진다).

MySQL의 통계 정보는 다음과 같은 Event가 발생할 경우, 자동적으로 재 수집을 하게 된다.

MySQL 서버에 의해서 테이블이 처음 Open되는 시점
ANALYZE TABLE TBL_NAME; 명령 실행 시
SHOW TABLE STATUS LIKE ‘TBL_NAME’; 명령 실행 시
SHOW INDEX FROM TBL_NAME; 명령 실행 시
Meta Query (테이블에 대한 DDL 문장) 실행 시 (InnoDB Plug-in에서는 innodb_stats_on_metadata 설정 옵션에 의존)
지정된 량 이상의 데이터가 변경 될 경우
기타 등등...

위와 같은 Event에 의해서 사용자가 눈치채지 못하는 사이, 자동으로 테이블의 통계 정보가 변경되기 때문에, Oracle과 같은 DBMS와는 달리 MySQL에서는 테이블의 통계 정보가 그다지 관심의 대상이 될 수 없는 것이다. (별달리 DBA가 해줄 수 있는 것도 별로 없다. ㅠㅠ)

MyISAM 테이블과 InnoDB 테이블의 통계 정보 또한 수집 방법이 조금씩 다른데,

MyISAM 테이블

인덱스 전체를 읽어서 정확한 Cardinality를 구하게 된다.
작은 테이블이라 하더라도 어느 정도의 시간이 걸리며
통계 정보를 수집하는 동안은 Lock을 걸어서 데이터의 변경이 금지된다.
InnoDB도 마찬가지지만, MyISAM 테이블의 경우에는 특히나 서비스 중에 통계 정보를 수집하는 것은 피하는 것이 좋을 듯 하다.
대신, MyISAM 테이블의 통계 정보는 한번 수집되면 상당히 정확한 Cardinality를 가지게 된다.
MyISAM 테이블의 통계 정보는 InnoDB 보다는 고정된 형태이며, “ANALYZE TABLE TBL_NAME;” 명령을 명시적으로 실행하는 경우에만 통계 정보가 수집됨

InnoDB 테이블

랜덤하게 데이터 페이지 몇 개를 샘플링해서 분석한 뒤 예측된 Cardinality를 구하게 된다.
MyISAM과 동일하게 통계 정보를 수집하는 동안은 Lock으로 변경이 금지된다.
InnoDB Plug-in 이전의 Built-in 버전까지는 무조건 랜덤하게 8개의 인덱스 페이지를 샘플링해서 분석했었지만,
Plug-in 버전부터는 랜덤하게 샘플링할 페이지의 개수를 설정 옵션으로 지정할 수 있다.
그래서, MyISAM 테이블의 통계 정보와는 달리 InnoDB 테이블의 통계 정보는 예측치이며, 실제 데이터와 상당한 차이를 보이게 된다.
MyISAM 보다는 상당히 동적이며, 위에 언급된 대부분의 경우에 통계 정보가 수집됨

그런데,

InnoDB 테이블의 경우만 간단한 테이블을 생성 후, 레코드를 몇 건 입력하고 SHOW INDEX를 실행해 보았다.

mysql> show index from stat_test;

+-----------+------------+----------+--------------+-------------+..-------------+..

+-----------+------------+----------+--------------+-------------+..-------------+..

| stat_test | 0 | PRIMARY | 1 | fdpk |.. 14 |..

| stat_test | 1 | ix_test | 1 | fd1 |.. 14 |..

| stat_test | 1 | ix_test1 | 1 | fd1 |.. 14 |..

| stat_test | 1 | ix_test1 | 2 | fd2 |.. 14 |..

+-----------+------------+----------+--------------+-------------+..-------------+..

Ix_test1 이라는 인덱스는 2개의 컬럼으로 구성되어 있기 때문에 2개의 레코드로 표현되었으며, 현재 Cardinality가 모두 14 인것으로 표시되었다. 사실 여기 14는 입력된 레코드의 건수이다. (현재 테스트 테이블의 레코드가 전부 한 페이지에 저장될 정도이기 때문에 정확한 레코드 건수가 수집될 수 있었을 것으로 보인다. 하지만 일반적인 서비스 환경에서는 그렇지 않을 것이다.)

분명히, SHOW INDEX 명령에 의해서 한번 통계 정보가 수집되었을 것으로 보이는데, 상당히 부정확하다.

실제 테이블의 데이터를 한번 조회 해보면, 통계 정보와 상당히 다르다는 것을 알 수 있다.

mysql> select count(distinct fd1) as cardinality from stat_test;

+-------------+

| cardinality |

+-------------+

| 2 |

+-------------+

mysql> select count(distinct fd1, fd2) as cardinality from stat_test;

+-------------+

| cardinality |

+-------------+

| 11 |

+-------------+

이 결과로 보아, 정확한 통계 정보는 아래와 같이 표시되었어야 할 것으로 보인다.

정확히 이와 같진 않더라도, 이와 비슷한 값이 나왔어야 할 것으로 보이는데...

+-----------+------------+----------+--------------+-------------+..-------------+..

+-----------+------------+----------+--------------+-------------+..-------------+..

| stat_test | 0 | PRIMARY | 1 | fdpk |.. 14 |..

| stat_test | 1 | ix_test | 1 | fd1 |.. 2 |..

| stat_test | 1 | ix_test1 | 1 | fd1 |.. 2 |..

| stat_test | 1 | ix_test1 | 2 | fd2 |.. 11 |..

+-----------+------------+----------+--------------+-------------+..-------------+..

여기에서 다시 ANALYZE TABLE 명령을 실행 후, 통계 정보를 다시 확인해 보았다.

mysql> analyze table stat_test;

+----------------+---------+----------+----------+

+----------------+---------+----------+----------+

+----------------+---------+----------+----------+

mysql> show index from stat_test;

+-----------+------------+----------+--------------+-------------+..+-------------+..

+-----------+------------+----------+--------------+-------------+..+-------------+..

| stat_test | 0 | PRIMARY | 1 | fdpk |..| 14 |..

| stat_test | 1 | ix_test | 1 | fd1 |..| 4 |..

| stat_test | 1 | ix_test1 | 1 | fd1 |..| 4 |..

| stat_test | 1 | ix_test1 | 2 | fd2 |..| 14 |..

+-----------+------------+----------+--------------+-------------+..+-------------+..

이 결과는 상당히 현실적으로 보인다. 하지만, 데이터가 많아져 페이지 수가 많아지면 이 예측은 상당히 어긋날 가능성도 높아질 것이다.

내부적인 처리는 소스를 확인하지 않는 이상 모르겠지만, 각 Event 별로 수집되는 통계 정보의 수준이 다른 것이 아닌가 생각이 된다.

결론적으로 InnoDB 테이블도, 생성 및 초기 Open된 시점이 오래된 테이블의 경우에는 명시적인 ANALYZE TABLE ...;

또는 ALTER TABLE ... ENGINE=INNODB; 등의 명령으로 통계 정보를 업데이트해 줄 필요는 있어 보인다.

SQL이 아닌 함수(getGeneratedKeys())를 이용한 AutoIncrement 키값 가져오기

통상적인 RDBMS는 Sequence 또는, AutoIncrement 형태의 시리얼한 id 채번 기능을 제공하고 있다.

또한, 이러한 Sequence 또는 AutoIncrement 로 부터 값이 추출되면, 그 세션에서 최종으로 사용된 id값을

가져올 수 있는 방법들을 제공하고 있다. (Oracle의 SEQUENCE.nextval 또는 MySQL의 LAST_INSERT_ID() 등)

많은 프로그램들에서 이러한 최종 채번된 id값을 가져오기 위해서 “SELECT LAST_INSERT_ID()” 쿼리 문장을

이용하여 조회하고 있는 것으로 보인다.

하지만, 이 방식은 또 한번의 서버 쿼리(Network round-trip)를 발생시키는 방식이며, JDBC 3.0 이상에서는

데이터베이스 서버까지 조회하지 않고 그냥 값을 가져오는 API를 제공하고 있다.

Network 비용이 그리 비싼 건 아니지만,

사용하는 JDBC Driver의 버전이 3.0 이상이라면 (물론 JDK 1.4 이상에서),

아래와 같이 Statement.getGeneratedKeys() 라는 함수를 이용하여 또 한번의 네트워크 통신 없이 바로 가져올 수 있다.

이 방식을 사용하기 위해서는 아래와 같이 Statement.executeUpdate() 나 PreparedStatement.prepareStatement()

함수 호출 시 별도의 설정 항목이 필요합니다.

l Statement 객체 이용할 경우
int affectedRowCount = stmt.executeUpdate(

"insert into tb_ai (fdpk, fddata) values (NULL, 'test')",Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();

String autoInsertedKey = (rs.next()) ? rs.getString(1) : null;

l PreparedStatement 객체 이용할 경우
PreparedStatement pstmt = conn.prepareStatement(

"insert into tb_ai (fdpk, fddata) values (NULL, ?)", Statement.RETURN_GENERATED_KEYS);

ResultSet rs = pstmt.getGeneratedKeys();

String autoInsertedKey = (rs.next()) ? rs.getString(1) : null;

이러한 방식은 DBMS 의존적인 부분이 아니라(물론 Vendor에서 지원하지 않으면 안되겠지만),

JDBC Driver Version 3.0 의 Spec이기 때문에 기본적인 DBMS(Oracle, MySQL, MSSQL, …)에서는

모두 지원되는 기능이므로 DB Framework에서 지원되지 않는다면, 기능 보완 요청을 통해서 해결이 가능할 듯 함

MySQL JDBC Connector는Connector-J 3.0.17 부터 JDBC 3.0을 지원하고 있습니다

---------------------------------------------------------------------------------------

/**

* create table tb_ai(

* fdpk bigint not null auto_increment,

* fddata varchar(100),

* primary key(fdpk)

* );

* Get Auto generated insert key

* // Every version of JDBC driver

* 1. rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

* // Over JDBC driver version 3.0

* 2. rs = pstmt.getGeneratedKeys();

public class GetAutoIncrementKeyTester {

public static void main(String[] args) throws Exception{

GetAutoIncrementKeyTester tester = new GetAutoIncrementKeyTester();

String autoInsertedKey = tester.insertWithLiteralStatement();

System.out.println(">> Auto inserted key : " + autoInsertedKey);

autoInsertedKey = tester.insertWithLiteralStatement();

System.out.println(">> Auto inserted key : " + autoInsertedKey);

}

protected String insertWithLiteralStatement() throws Exception{

Connection conn = getMasterConnection();

Statement stmt = conn.createStatement();

stmt.executeUpdate(

"insert into tb_ai (fdpk, fddata) values (NULL, 'test')",Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();

String autoInsertedKey = (rs.next()) ? rs.getString(1) : null;

rs.close();stmt.close();conn.close();

return autoInsertedKey;

}

protected String insertWithPreparedStatement() throws Exception{

Connection conn = getMasterConnection();

PreparedStatement pstmt = conn.prepareStatement(

"insert into tb_ai (fdpk, fddata) values (NULL, ?)",Statement.RETURN_GENERATED_KEYS);

pstmt.setString(1, "data");

pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys();

String autoInsertedKey = (rs.next()) ? rs.getString(1) : null;

rs.close();pstmt.close();conn.close();

return autoInsertedKey;

}

protected Connection getMasterConnection() throws Exception{

String driver = "com.mysql.jdbc.Driver";

String url = "jdbc:mysql://test_db_host_ip:20306/test_db_name";

String uid = "userid";

String pwd = "password";

Class.forName(driver).newInstance();

Connection conn = DriverManager.getConnection(url , uid, pwd);

conn.setAutoCommit(false);

return conn;

}

SYSDATE() 와 NOW()의 차이점

MySQL 내부적으로 현재 날짜 및 시간 정보를 리턴해주는 Built-in함수로 SYSDATE()와 NOW() 2개가 있는데,

내부적으로 SYSDATE()와 NOW()의 작동 방식은 쿼리의 실행 계획에 상당한 영향을 미칠 정도로 크다.

메뉴얼의 내용을 다시 한번 확인해보자.

-- // -- MySQL 메뉴얼 --------------------------------------------------------------------

SYSDATE() returns the time at which it executes.

This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute.

(Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW() | SLEEP(2) | NOW() |

+---------------------+----------+---------------------+

| 2010-12-13 14:52:34 | 0 | 2010-12-13 14:52:34 |

+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE() | SLEEP(2) | SYSDATE() |

+---------------------+----------+---------------------+

| 2010-12-13 14:52:43 | 0 | 2010-12-13 14:52:45 |

+---------------------+----------+---------------------+

-- // -- MySQL 메뉴얼 --------------------------------------------------------------------

메뉴얼에도 자세히 적혀 있듯이,

SYSDATE() 함수는 트랜잭션이나 쿼리 단위에 전혀 관계 없이, 그 함수가 실행되는 시점의 시각을 리턴해주지만,

NOW()는 하나의 쿼리 단위로 동일한 값을 리턴하게 된다.

즉, 아래와 같은 쿼리가 있다고 가정하면,

SELECT fd1, fd2, fd3 FROM tab1 WHERE fd_dt>SYSDATE();

만약, 이 테이블이 레코드가 너무 많아서 처음 레코드부터 끝까지 스캔하는데 1시간이 걸린다면,

처음 레코드를 비교할 때에는 SYSDATE()의 리턴값이 '2010-12-13 00:00:00' 였다면,

마지막 레코드를 비교할 때에는 SYSDATE()의 리턴값이 '2010-12-13 -1:00:00'가 되어 첫번째 레코드와 마지막 레코드의 비교 시점이 달라지게 된다.

(참고로, SYSDATE()와 NOW()의 이러한 차이는 Stored-Function의 DETERMINISTIC 과 NOT DETERMINISTIC의 차이와 거의 흡사해 보인다.)

즉, 쿼리의 비교 값으로 사용된 NOW() 함수는 상수(Constant)이지만, SYSDATE() 함수는 상수가 아닌 것이다.

이로 인해서, 아래와 같은 전체적인 쿼리의 실행 계획을 바꿔버리게 된다.

CREATE TABLE user (

id mediumint(9) NOT NULL AUTO_INCREMENT,

name varchar(20) DEFAULT NULL,

age tinyint(3) unsigned DEFAULT NULL,

sex enum('MALE','FEMALE') DEFAULT NULL,

regdt datetime NOT NULL,

PRIMARY KEY (id),

KEY ix_regdt (regdt)

) ENGINE=InnoDB;

mysql> explain select * from user where regdt>now();

+----+-------------+-------+-------+----------+---------+------+------+

+----+-------------+-------+-------+----------+---------+------+------+

+----+-------------+-------+-------+----------+---------+------+------+

1 row in set (0.00 sec)

mysql> explain select * from user where regdt>sysdate();

+----+-------------+-------+------+------+---------+------+------+

+----+-------------+-------+------+------+---------+------+------+

+----+-------------+-------+------+------+---------+------+------+

1 row in set (0.00 sec)

SYSDATE()로 비교되는 컬럼에 인덱스가 준비되어 있든지 아니든지에 관계없이 FULL-TABLE-SCAN을 사용할 수 밖에 없는 구조인 것이다.

이러한 예상하지 못한 문제를 해결하기 위해서,

SYSDATE()의 사용 금지
또는 --sysdate-is-now 옵션을 설정하여 MySQL Server 기동

--sysdate-is-now 옵션이 활성화되면, SYSDATE()는 NOW() 함수와 동일하게 작동하게 된다.

아마도, 초기 SYSDATE()의 작동 방식은 대부분의 SQL 문장을 작성하는 사람의 의도와는 어긋나는 경우가 많을 것으로 생각되며,

기본적으로는 SYSDATE()의 작동 방식을 비활성화(--sysdate-is-now 옵션 활성화)하는 것이 예측하지 못한 문제를 막는 방법일 것으로 보인다.

INSERT INTO ... SELECT ... FROM 형태의 쿼리 사용시 주의 사항

INSERT INTO target_table

SELECT ... FROM source_table1, source_table2 WHERE ...

이 형태의 쿼리는 간단한 통계나 집계를 생성할 때 자주 사용된다.

일반적인 MySQL InnoDB 테이블에 대한 SELECT 쿼리는 Lock을 걸지 않으며,

LOCK IN SHARE MODE 또는 FOR UPDATE 가 사용된 SELECT 문장에 대해서만 Lock이 필요하다.

하지만, 이 쿼리를 실행하게 되면 source_table1과 source_table2의 조회 대상 레코드에도 Read Lock이 걸리게 된다.

만약, 하위 SELECT 쿼리의 조회 범위나 처리 작업이 복잡하다면, 실시간 서비스에 영향을 미칠 수 있게 된다.

또한, 이 쿼리는 Lock 모드에서 SELECT를 실행하기 때문에 부분적으로 MVCC를 무시하고 최종으로 Commit된

레코드를 읽게 되기 때문에 REPATABLE_READ 모드에서 실행되어도 실제적으로는 READ_COMMITED 모드로 작동하게 된다.

이러한 문제점은 MySQL의 GapLock과 연관이 있으며, 아래와 같이 2가지 방법으로 이러한 조회 테이블의 Lock을 피할 수 있다.

innodb_locks_unsafe_for_binlog 시스템 변수 값을 ON으로 설정
ROW-based replication과 READ-COMMITTED Isolation level 사용

하지만, 두 가지 대안 모두 현재로써는 좋은 해결책이 아닌 것으로 보인다.

첫 번째는 Master와 Slave의 데이터 부정합을 유발하게 될 것이며, 두 번째 Row-based replication은 아직 적용하기에는 시기 상조인듯하다.

(참고로 두 번째 방법은 이 쿼리를 위한 전용 옵션이 아니라, MySQL에서 GapLock을 제거하는 방법이기도 하다.)

그래서 이러한 부분을 해결하기 위한 방법으로는 아래와 같이 SELECT INTO OUTFILE과 LOAD DATA INFILE을

혼용해서 사용하는 방법을 사용해보는 것이 좋을 듯 하다.

아래 예제에서는 article이라는 테이블을 내용을 group1_id, group2_id 컬럼을 이용하여 group by 한 결과를

다른 집계 테이블에 넣어 두고, 필요시 간단히 조회해서 사용할 수 있도록 하는 시나리오를 가정한 것이다.

아래의 쿼리는 article 테이블에 read lock을 사용하게 되므로, 실시간 변경 트랜잭션에 영향을 미치게 된다.

insert into temp$summary (summary_id, group1_id, group2_id, ...)

select null, group1_id, group2_id, ...

from article al

group by al.group1_id, al.group2_id

order by NULL;

그래서, 아래와 같이 SELECT -> Disk 파일 -> LOAD INTO -> RENAME TABLE과 같은 방법으로 해결할 수 있다.

-- // 임시 작업용 테이블 생성

create temp$summary(

summary_id integer unsigned not null auto_increment,

group1_id bigint not null,

group2_id bigint not null,

article_count integer default 0 not null,

...

primary key(summary_id)

);

-- // Grouping 결과를 임시 파일로 저장

SELECT al.group1_id, al.group2_id, count(*) as article_count, ...

INTO OUTFILE '/tmp/temp_aritcle_summary.dat'

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

from article al

group by al.group1_id, al.group2_id

order by NULL;

-- // 저장된 임시 파일을 테이블로 적재

LOAD DATA INFILE '/tmp/temp_aritcle_summary.dat'

INTO TABLE temp$summary

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

(group1_id, group2_id, article_count, ...)

SET summary_id=null;

-- // 데이터 적재후 추가적으로 필요한 인덱스 생성

alter table temp$summary add index ix_group1id_group2id (group1_id, group2_id);

-- // 준비된 임시 테이블을 서비스용 테이블로 이름 변경

-- // 아래와 같이 한 명령으로 필요한 테이블의 이름 변경을 한꺼번에 실행하게 되면,

-- // 실시간 서비스라 하더라도 순단 현상(일시적으로 TABLE NOT FOUND)을 피할 수 있다.

rename table summary_yesterday to summary_old,

summary to summary_yesterday,

temp$summary to summary;

-- // 이틀 전 데이터를 가지고 있는 테이블은 삭제

drop table summary_old;