'myisam'에 해당되는 글 2건

  1. 2008.12.29 MySQL Foreign Key (외래키) 사용하기 (1)
  2. 2008.10.20 MySQL InnoDB Table Space 관리하기
2008.12.29 10:17

MySQL Foreign Key (외래키) 사용하기

MySQL에서 InnoDB 테이블은 Foreign 키 제한 검사를 지원한다.

Foreign 키 제한은 단순히 두 테이블을 조인 (join)하기 위해 필요한 것이 아니다. InnoDB가 아닌 스토리지 엔진의 경우, 컬럼이 REFERENCES tbl_name(col_name) 구문을 사용하도록 정의하는 것이 가능한데, 이것은 실제로 아무런 효과도 없으며, 또한 여러분이 지금 정의하는 컬럼은 다른 테이블에 있는 컬럼을 참조하도록 의도된 것이라는 것을 알려주는 메모 또는 코멘트 역할만 하게 된다. 이 신텍스를 다음과 같은 상황에서 사용할 때는 특히 중요하다:

·         MySQLcol_name 이 실제로 tbl_name 에 있는지 (또는 tbl_name 자체가 존재하는지) 확인하기 위한 어떠한 종류의 CHECK도 실행하지 않는다.   

·         MySQL 은 여러분이 정의한 테이블에서 열을 가져와서 삭제하는 것과 같은 행동을 tbl_name 에서는 전혀 하지 않는다; 다른 말로 표현하면, 이 신텍스는 ON DELETE 또는 ON UPDATE 가 구동하는데 아무런 영향을 미치지 않는다. (여러분이ON DELETE 또는 ON UPDATE 구문을 REFERENCES 구문의 일부분으로 작성한다 하더라도, 이것은 무시되어 버린다.)

·         이 신텍스는 column을 만들어 낸다; 이것은 어떤 종류의 인덱스 또는 키를 만들지는 않는다.

·         이 신텍스는 InnoDB 테이블을 정의하기 위해 사용할 때 에러를 발생시킨다.

여러분은 여기에서 보듯이, 컬럼을 만들어서 조인(join) 컬럼으로 사용할 수 있다:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz'); 
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last); 
SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+ 
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+ 
SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white'; 
+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

이러한 형태로 사용될 경우에, REFERENCES 구문은 SHOW CREATE TABLE 또는 DESCRIBE 구문의 결과에는 나타나지 않는다:

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

여기에서처럼 REFERENCES 를 컬럼 정의문에서 코멘트 형태 또는 리마인더(reminder)”로 사용하는 것은 MyISAM BerkeleyDB 테이블에서도 적용 할 수 있다.


출처 : www.mysqlkorea.co.kr

Trackback 0 Comment 1
  1. 최세민 2009.08.20 17:59 address edit & del reply

    좋은 정보 감사합니다.
    FOREIGN KEY가 적용 되지 않아서 한참 해맸는데 테이블 형식을 innodb로 변경하니깐 바로 적용 되는 군요. ^^

2008.10.20 10:19

MySQL InnoDB Table Space 관리하기

1. What is Innodb Table space  ?

    - 그 동안  Myisam 방식으로 테이블 스페이스를 사용하던 mysql은 rollback 이 되지 않는다.

       다른 데이터베이스 와 달리  autocommit 이 자동으로 실행 하여 

       데이터 베이스의 트렌젝션시 발생되는 lock을 처리 하기 힘들었다.

   -  innodb는 오라클 처럼 shared tablespace ( ibdataN) 와 로그파일 (ib_logfileN ) 로 구성된다.

      로그파일은 트랜젝션들을 처리하는 오라클의 아카이브 로그 같이 생각 하면 된다.

 

2. Mysql의 주요 특징 

   참고 : Mysql Technical Reference fo Ver 5.0 : 16장  InnodB Configureation

  -  rollback 을 구현 할수 있다.

  -  transaction isolation Level

     : READ-UNCOMMITED, READ-COMMITED, REPEATABLE-READ, SERIALIZABLE

  - innodb_buffer_pool_size

    : MyIsam 방식과 달리 buffer poll을 이용하여 인덱스와 raw data를 캐싱한다.

      따라서 사용량이 많아 지면 tablespace  Disk I/O  가 그만큼 줄어 든다

      pool의 용량은 real memory의 80% 까지 줄수 있으나,  다른 어플리 케이션과 사용시

     너무 많이 주게 되면 운영 체제의 paging을 일으키게 된다.

     ( 참고로 오라클은 SGA 를 50%로 권고 한다)

     또 32bit 시스템의 경우 2 ~ 3.5G의 제한되므로 너무 많이 주면 안된다고 한다.

 - innodb_data_file_path

    아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.  

    오직 my.cnf 환경 파일로 생성이 되고 변경 된다.

      형식

     innodb_data_file_path  = [파일위치1/]파일명1:파일1사이즈:autoextend:max:MAX파일 사이즈

     파일 간의 구분자는 ";" 이고 파일의 속성 구분자는 ":" 이며

     autoextend:max:2000M 는 맨마지막 파일에만 해당된다.

innodb_file_per_table

   를 사용하면 이전 isam 같이 데이터 베이스 폴더 안에 table_name.ibd 라는 파일이 생긴다.

   사용예는 다음과 같다.

    innodb_data_home_dir=/export/DATABASE/MyData

   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:200M:autoextend:max:2000M

 

3. Innodb 관리

  3.1 테이블 rename

      RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

  3.2 데이블 복구

       이전 백업 된 데이터 파일을 이용하여 복구 하는방법

     가) ALTER TABLE tbl_name DISCARD TABLESPACE; 

          -> 현재 tbl_name.ibd 를 삭제한다.

     나) 백업된 tbl_name.ibd 를 복사해온다.

     다) ALTER TABLE tbl_name IMPORT TABLESPACE;

  3.3 SHOW INNODB STATUS

  3.4 Adding and Removing InnoDB Data and Log Files

     >my.cnf<

        innodb_data_home_dir=/export/DATABASE/MyData

        innodb_data_file_path = ibdata1:500M:ibdata2:500M:ibdata3:500M:ibdata4:200M:autoextend:max:2000M

  3.5  Shared Table Space 용량 조정

       가) mysqldump로 innodb table을 백업 받는다.

       나) 서버를 중지

       다) 현재  사용중인  ibdata1  .. ibdataN 을 삭제 한다.

       라) 위의 환경 파일을 수정한다.

       마) 서버를 재구동 한다.

       바) dump 파일을 import 한다.

   3.6  로그파일 관리

    보통 다음 과 같은 디폴트 값을 사용하며

      # Set buffer pool size to 50-80% of your computer's memory
      set-variable = innodb_buffer_pool_size=70M
      set-variable = innodb_additional_mem_pool_size=10M

      # Set the log file size to about 25% of the buffer pool size
      set-variable = innodb_log_file_size=20M
      set-variable = innodb_log_buffer_size=8M

  3.2 테이블 스페이스 관리

    아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.  

    오직 my.cnf 환경 파일로 생성이 되고 변경 된다.

    대량의 데이터 베이스 의 경우 3 ~ 4G 이상이 되는경우

    디폴트로 ibdata1:10M:autoextend 를 쓴다면 곤욕을 치르게 될것이다.

 

    다음과 같이 여러개의 파일을 이용하여 분산 하는 것을 추천 합니다.

    파일은 path을 이용 할수 도 있고 raw 디바이스를 이용 할수도 있습니다.

    /data1 .. /data4 를 각각 다른 디바이스라고 한다면 아래와 같이 사용하면 된다.

     innodb_data_home_dir=

    innodb_data_file_path = /data1/ibdata1:500M;/data2/ibdata2:500M;/data3/ibdata3:500M;/data4/ibdata4:200M:autoextend:max:2000M

  

    보통 백업을 위해서 오라클의 경우 500MB ~ 1GB로 데이터 파일 을 생성 하였다.

    그리고 200MB는 현재 사용량이 얼마인지 모르기 때문에 200MB -> 300MB가 되면

    다시 환경 파일을 수정하고 데이터 파일을 추가 하기 위한 방법이다.

      그러나 이것도 문제 가 많다는 것을 독자들도 알것이다.

    암튼 아직은 미약한 부분이 많다는 것을 착안 하기 바란다.

    데이처 파일을  500MB ~ 1GB로 생성 하는이유는

    예전에 OS에서 제한이 있어서 그런 적도 있었지만 , 파일이 크면 그 만큼  I/O분산이 안된다.

    그리고 파일당 백업 시간이 증가하거나 문제가 발생 하면  복구도 그만큼  힘들다.

   예로 500MB데이터 파일 하나가 사라진 경우와 1GB 데이터 파일 1개가 사라진 경우

    그안에 속한 데이터의 량은 곱절이 된다.

   즉 재난 을 최소화 하기 위해서라고 1GB이하로 쪼개는 것이 낳기 때문이다.

 

  그렇다고 3GB 되는 것을 100MB로 쪼개면  어떨까 performance에서  문제가 될것이다.

   그러므로 DBA의 적절한 선택이 필요하다.


Trackback 0 Comment 0