[mySQL5.5] 10장. Other Storage Engines

n  Storage Engines

 

n  MyISAM
- web에서 주로 사용
- dw나 fulltext search 나 spatial data type 지원

- row format : fixed, dynamic , compressed

- compressed : myisampack사용

 

  CREATE TABLE t (i INT) ENGINE = MYISAM;

 

InnoDB

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

 

 

n  MERGE

-      known as the MRG_MyISAM engine

-      MyISAM 테이블을 합친 것

 -  .frm file stores the table format, and an .MRG

 

1) t1 테이블을 생성한다.

 

mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

        message CHAR(20)) ENGINE=MyISAM;

 

2) t2 테이블을 생성한다.

mysql> CREATE TABLE t2 (

        a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

        message CHAR(20)) ENGINE=MyISAM;

 

3)  DML

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

Query OK, 3 rows affected (0.00 sec)

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

Query OK, 3 rows affected (0.00 sec)

 

 

4) Engine = MERGE 로 생성한다.

mysql> CREATE TABLE total (

        a INT NOT NULL AUTO_INCREMENT,

        message CHAR(20), INDEX(a))

        ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

 

mysql> SELECT * FROM total;

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

| a | message |

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

| 1 | Testing |

| 2 | table   |

| 3 | t1      |

| 1 | Testing |

| 2 | table   |

| 3 | t2      |

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

6 rows in set (0.00 sec)

 

n  MEMORY

- 테이블당 32 인덱스까지, 16 columns/인덱스당,maximum key length <= 500 bytes.
- both HASH and BTREE indexes
- fixed-length row format

- cannot contain BLOB or TEXT columns

- 최대크기 <= max_heap_table_size , default : 16MB

- INSERT INTO ... SELECT or LOAD DATA INFILE

CREATE TABLE t (i INT) ENGINE = MEMORY;

 

n  FEDERATED

-      MySQL 5.1.26이후

-      --federated 옵션으로 시작해야 한다

 

CREATE TABLE federated_table (

    id     INT(20) NOT NULL AUTO_INCREMENT,

    name   VARCHAR(32) NOT NULL DEFAULT '',

    other  INT(20) NOT NULL DEFAULT '0',

    PRIMARY KEY  (id),

    INDEX name (name),

    INDEX other_key (other)

)

ENGINE=FEDERATED

DEFAULT CHARSET=latin1

CONNECTION='localhost://root:oracle@localhost:3306/world/city';

** connection string : the server name, login credentials, port number and database/table information

 

n  ARCHIVE

- 대량의 데이터 저장에 적합
- 인덱스없이 사용.

- INSERT 와 SELECT 지원, DELETE, REPLACE, UPDATE 지원 안 됨

-  It does support ORDER BY operations, BLOB columns

-  Rows are compressed as they are inserted. zlib

- On retrieval, rows are uncompressed on demand;

- ARCHIVE engine uses row-level locking.

 

CREATE TABLE customers (a INT, b CHAR (20)) ENGINE=ARCHIVE;

 

 

n  CSV

- stores data in text files using comma-separated values format.

 

 CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;

 

INSERT INTO test VALUES(1,'record one'),(2,'record two');

 

 

n  Blackhole

- “black hole” 역할
- 데이터를 accept하지만, 저장은 하지 않는다.

- empty result을 반환합니다.

 

 CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;

 

INSERT INTO test VALUES(1,'record one'),(2,'record two');

Query OK, 2 rows affected (0.00 sec)

 

 

SELECT * FROM test;

Empty set (0.00 sec)

 

 

 

n  Falcon

- 64-bit architectures are ideal platforms

- designed to work within high-traffic transactional applications

- Flexible lockin

- multiple concurrent transactions.

- Advanced B-Tree indexes

- Within Falcon, all data within one database is stored within a tablespace file within the MySQL directory structure.

CREATE TABLESPACE tablespace_name

    ADD DATAFILE 'file_name'

    ENGINE [=] Falcon

CREATE TABLE names (

    id    INT,

    fname VARCHAR (20),

    lname VARCHAR (20)

) ENGINE=Falcon

·                      

CREATE TABLE ids (

    id INT,

    INDEX (id)

) ENGINE=Falcon;

 

CREATE TABLE names (id INT, fname VARCHAR (20), lname VARCHAR (20))

    TABLESPACE my_big_tables ENGINE=Falcon;