mysql isam 食物_MySQL的存储引擎之MyISAM

MyISAM不支持事务、也不支持外键，其优势是访问速度快。对事务完整性没有要求或以SELECT、INSERT为主的应用可以考虑使用这个引擎。

每个使用MyISAM引擎的表在磁盘上都存储成3个文件，文件名都和表名相同，扩展名分别是：

.frm，存储表定义

.MYD，存储数据

.MYI，存储索引

root@database-one 09:03: [gftest]> create table testmyisam(id int,name varchar(10),primary key(id)) engine=myisam;

Query OK, 0 rows affected (0.01 sec)

root@database-one 09:03: [gftest]> exit

Bye

[root@database-one ~]# cd /home/mysql/gftest/

[root@database-one gftest]# ls -l testmyisam.*

-rw-r----- 1 mysql mysql 8586 2月 29 09:03 testmyisam.frm

-rw-r----- 1 mysql mysql 0 2月 29 09:03 testmyisam.MYD

-rw-r----- 1 mysql mysql 1024 2月 29 09:03 testmyisam.MYI

[root@database-one gftest]#

数据文件和索引文件可以放在不同的目录，分散I/O，获得更好的性能，创建表时通过DATA DIRECTORY和INDEX DIRECTORY指定。

root@database-one 09:14: [gftest]> create table testmyisam1(id int,name varchar(10),primary key(id)) engine=myisam data directory='/tmp' index directory='/tmp';

Query OK, 0 rows affected (0.20 sec)

root@database-one 09:14: [gftest]> exit

Bye

[root@database-one gftest]# cd /tmp

[root@database-one tmp]# ls -l testmyisam*

-rw-r----- 1 mysql mysql 0 2月 29 09:14 testmyisam1.MYD

-rw-r----- 1 mysql mysql 1024 2月 29 09:14 testmyisam1.MYI

[root@database-one tmp]# cd /home/mysql/gftest/

[root@database-one gftest]# ls -l testmyisam1*

-rw-r----- 1 mysql mysql 8586 2月 29 09:14 testmyisam1.frm

lrwxrwxrwx 1 mysql mysql 20 2月 29 09:14 testmyisam1.MYD -> /tmp/testmyisam1.MYD

lrwxrwxrwx 1 mysql mysql 20 2月 29 09:14 testmyisam1.MYI -> /tmp/testmyisam1.MYI

从上面可以看到，真实数据文件和索引文件放到了指定的目录，但是表定义文件继续放在默认位置，同时MySQL在默认位置还创建了指向真实数据文件和索引文件的链接文件。

MyISAM的表还支持3种不同的存储格式：

Static (Fixed-Length) Table，即静态表(固定长度)。这是MyISAM表的默认格式，每行使用固定字节数存储。当表不包含可变长度列(VARCHAR、VARBINARY、BLOB或TEXT)时使用。

Dynamic Table，即动态表。如果MyISAM表包含任何可变长度的列(VARCHAR，

VARBINARY、BLOB或TEXT)，或者使用ROW_FORMAT=DYNAMIC选项建表，均会使用动态方式存储。

Compressed Table，即压缩表。压缩存储格式是一种只读格式，由myisampack工具生成，用myisamchk解压缩。

root@database-one 09:31: [gftest]> select * from information_schema.tables where TABLE_SCHEMA='gftest' and TABLE_NAME='testmyisam' \G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: gftest

TABLE_NAME: testmyisam

TABLE_TYPE: BASE TABLE

ENGINE: MyISAM

VERSION: 10

ROW_FORMAT: Dynamic

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 0

MAX_DATA_LENGTH: 281474976710655

INDEX_LENGTH: 1024

DATA_FREE: 0

AUTO_INCREMENT: NULL

CREATE_TIME: 2020-02-29 09:03:55

UPDATE_TIME: 2020-02-29 09:03:55

CHECK_TIME: NULL

TABLE_COLLATION: utf8_general_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT:

1 row in set (0.06 sec)

root@database-one 09:32: [gftest]> create table testmyisam2(id int,name char(10),primary key(id)) engine=myisam;

Query OK, 0 rows affected (4.16 sec)

root@database-one 09:34: [gftest]> select * from information_schema.tables where TABLE_SCHEMA='gftest' and TABLE_NAME='testmyisam2' \G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: gftest

TABLE_NAME: testmyisam2

TABLE_TYPE: BASE TABLE

ENGINE: MyISAM

VERSION: 10

ROW_FORMAT: Fixed

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 0

MAX_DATA_LENGTH: 9851624184872959

INDEX_LENGTH: 1024

DATA_FREE: 0

AUTO_INCREMENT: NULL

CREATE_TIME: 2020-02-29 09:34:00

UPDATE_TIME: 2020-02-29 09:34:00

CHECK_TIME: NULL

TABLE_COLLATION: utf8_general_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT:

1 row in set (0.02 sec)

可以看到，testmyisam表因为name列是varchar类型，所以存储格式为动态的(ROW_FORMAT: Dynamic)，testmyisam2表因为name列是char类型，所以存储格式为静态的(ROW_FORMAT: Fixed)。

静态表中字段都是非变长字段，每条记录都是固定长度，存储速度非常快，容易缓存，出现故障容易恢复，缺点是占用空间通常比动态表多。静态表的数据在存储时会按照列宽补足空格，但在数据读取时会去掉空格，这对那些内容后面本来就有空格的场景需要特别注意，否则可能会引发问题。

root@database-one 09:44: [gftest]> insert into testmyisam2 values(1,'abc'),(2,'abc '),(3,' abc'),(4,' abc ');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

root@database-one 09:45: [gftest]> select id,name,length(name) from testmyisam2;

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

| id | name | length(name) |

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

| 1 | abc | 3 |

| 2 | abc | 3 |

| 3 | abc | 4 |

| 4 | abc | 4 |

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

4 rows in set (0.03 sec)

而动态表就不会存在这个问题：

root@database-one 09:47: [gftest]> insert into testmyisam values(1,'abc'),(2,'abc '),(3,' abc'),(4,' abc ');

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

root@database-one 09:47: [gftest]> select id,name,length(name) from testmyisam;

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

| id | name | length(name) |

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

| 1 | abc | 3 |

| 2 | abc | 4 |

| 3 | abc | 4 |

| 4 | abc | 5 |

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

4 rows in set (0.04 sec)

动态表中因为包含变长字段，记录长度是不固定的，占用空间相对较少，但是频繁更新和删除会产生碎片，影响性能，需要定期进行optimize table或myisamchk -r来改善性能。

root@database-one 09:47: [gftest]> optimize table testmyisam;

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

| Table | Op | Msg_type | Msg_text |

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

| gftest.testmyisam | optimize | status | OK |

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

1 row in set (0.06 sec)