本文结合实验说明MyISAM表的几个特性:
MyISAM表使用三种文件:
.frm文件:存储表的结构信息。
.MYD:存储表的行信息,即数据信息。
.MYI:存储索引信息。
实验
mysql> create table t1(id int auto_increment,msg varchar(20),primary key(id));
插入三条数据:
mysql> insert into t1(msg) values('a');
mysql> insert into t1(msg) values('b');
mysql> insert into t1(msg) values('c');
mysql> select * from t1;
+----+------+
| id | msg |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
与自增列相关的函数last_insert_id(),可查出最后一次插入的第一个值。例如:
插入多条:
mysql> insert into t1(msg) values('d'),('e');
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 4 |
+------------------+
指定自增开始序号:
mysql> alter table t1 auto_increment=1000;
MyISAM提供了使数据文件和索引文件分开的机制:
查看当前默认存储引擎:
mysql> show variables like 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
创建表指定数据存储文件和索引存储文件位置:
[root@kim tmp]# mkdir -p mysql/data mysql/index
[root@kim tmp]# chown -R mysql:mysql mysql
mysql> create table t1(id int) engine=myisam data directory='/tmp/mysql/data' index directory='/tmp/mysql/index';
则再/tmp/mysql/data和/tmp/mysql/index下会分别生成t1.MYD和t1.MYI文件。
在数据库目录下生成:
t1.MYD -> /tmp/mysql/data/t1.MYD和
t1.MYI -> /tmp/mysql/index/t1.MYI 软链接文件。
给表t1增加一个自增列:
自增列必须为主键:
mysql> alter table t1 add column id1 int not null auto_increment,add primary key(id1);
mysql> desc t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | YES | | NULL | |
| id1 | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
验证MyISAM表不支持事务特性,实验如下:
mysql> create table t1(id int) engine=myisam;
查看自动提交设置变量是否开启:
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
将其设置为OFF:
mysql> set session autocommit=off;
插入数据:
mysql> insert into t1 values(1);
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
回滚:
mysql> rollback;
mysql> select * from t;
+------+
| id |
+------+
| 1 |
+------+
故即使回滚,仍然提交了,故不支持事务特性。
MyISAM表还可以转换为压缩的、只读的表,用来节省空间。载入内存,故速度快,但需解压缩,故会耗CPU。
MyISAM压缩实验如下:
向表中插入大量数据:
mysql> insert into t1 select * from t1;
Query OK, 2097152 rows affected (4.36 sec)
查看其数据文件大小:
[root@kim test1]# ls –lh
-rw-rw---- 1 mysql mysql 28M 08-15 11:15 t1.MYD
压缩,/bin目录下查看此工具用法:
[root@kim bin]# myisampack –help
执行压缩:
[root@kim test1]# myisampack t1
Compressing t1.MYD: (4194304 records)
- Calculating statistics
- Compressing file
85.71%
查看压缩后大小:
[root@kim test1]# ls –lh
-rw-rw---- 1 mysql mysql 4.1M 08-15 11:15 t1.MYD
压缩后变为只读表。
重启后,查看表:
mysql> check table t1;
+----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test1.t1 | check | status | OK |
+----------+-------+----------+----------+
正常,但推荐压缩完后做备份。