본문 바로가기

쓰기

innodb 확인방법

haru 2017.11.28 11:12 조회 수 : 365

mysql innodb엔진은 4.x부터는 mysql설치시 기본적으로 탑재되어있었으나 5.1.3x부터는 기존 configure옵션되로 하면 
기본적으로 innodb가 탑재가 안되며 configure옵션에 --with-plugins=innobase 또는 --with-plugins=max 옵션을 추가해줘야한다.

innodb부분이 YES로 나와있어야함.
mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec) mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

roundcubemail table 같은경우에는 innodb를 사용함
mysql> show table status;
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                                                                   |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------------+
| cache      | InnoDB |      10 | Compact    |    4 |           4096 |       16384 |               0 |        32768 |         0 |             10 | 2009-05-07 16:41:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| contacts   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2009-05-07 16:41:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| identities | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        16384 |         0 |              3 | 2009-05-07 16:41:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| messages   | InnoDB |      10 | Compact    |   49 |           1337 |       65536 |               0 |        32768 |         0 |            273 | 2009-05-07 16:41:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| session    | InnoDB |      10 | Compact    |   11 |           1489 |       16384 |               0 |        16384 |         0 |           NULL | 2009-05-07 16:41:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 11264 kB                                                     |
| users     | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        32768 |         0 |              3 | 2009-05-07 16:41:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 11264 kB                                                     |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------------+
6 rows in set (0.00 sec)


myisam에서 innodb로 변경
ALTER TABLE 테이블명 ENGINE=INNODB
ALTER TABLE 테이블명 TYPE=INNODB

innodb테이블생성시 
create table a(a int not null, b int) engine=innodb    (engine=inodb옵션이 붙어야함)