myisam的引擎的物理结构:包括三种文件:.frm .myd .myi三种。
myisam特有的特性
1.可以支持将数据文件和索引文件放在不同的地方,以达到性能优化的目的。
[root@rhel131 mysql]# mkdir -p /tmp/mysql/data
[root@rhel131 mysql]# chown -R mysql.mysql /tmp/mysql/data
[root@rhel131 mysql]# mkdir -p /tmp/mysql/index
[root@rhel131 mysql]# chown -R mysql.mysql /tmp/mysql/index
mysql> create table t(id int) engine=myisam data directory='/tmp/mysql/data' index directory='/tmp/mysql/index';
Query OK, 0 rows affected (0.10 sec)
mysql> show table status like 't' \G;
*************************** 1. row ***************************
Name: t
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2013-10-24 12:46:07
Update_time: 2013-10-24 12:46:07
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
查看一个产生的文件:
[root@rhel131 test]# pwd
/usr/local/mysql/data/test
[root@rhel131 test]# ll
total 16
-rw-r--r-- 1 mysql mysql 65 Jul 11 00:17 db.opt
-rw-rw---- 1 mysql mysql 8556 Oct 24 12:46 t.frm
lrwxrwxrwx 1 mysql mysql 21 Oct 24 12:46 t.MYD -> /tmp/mysql/data/t.MYD
lrwxrwxrwx 1 mysql mysql 22 Oct 24 12:46 t.MYI -> /tmp/mysql/index/t.MYI
数据文件和索引文件都是软链接文件,接到了我指定的目录。
2.灵活的自动增长列类型
对已存在的表增加一个自动增长的列:
mysql> alter table t add column id1 int not null auto_increment,add primary key (id1);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | YES | | NULL | |
| id1 | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into t (id) values(1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t (id) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t (id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+-----+
| id | id1 |
+------+-----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
+------+-----+
4 rows in set (0.00 sec)
3.不支持事务的特性:
mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t(id int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
查询是否自动提交,将自动提交给关闭
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set session autocommit=off;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)
插入一条数据
mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
做回滚操作后,如果支持事务操作的话,应该后取消我刚刚插入的一笔数据。
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
不过做了回滚之后,这笔数据还在,说明myisam不支持事务操作,在插入数据后直接写到了磁盘上。
mysql> select * from t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)