mysql存储引擎myisam1，创建myisam表mysqlcreatetablet(idint,namevarchar(30),msgvarchar(100))engine=myisam;mysqlshowtablestatusliket\\g;*****************
mysql存储引擎myisam
1，创建myisam表
mysql> create table t (id int , name varchar(30) , msg varchar(100)) engine = myisam;mysql> show table status like t \g ;*************************** 1. row ***************************name: tengine: myisamversion: 10row_format: dynamicrows: 0avg_row_length: 0data_length: 0max_data_length: 281474976710655index_length: 1024data_free: 0auto_increment: nullcreate_time: 2013-09-12 00:39:29update_time: 2013-09-12 00:39:29check_time: nullcollation: utf8_general_cichecksum: nullcreate_options:comment:1 row in set (0.00 sec)
2,auto_increment
当使用这个参数的时候，这个列一定要是主键
mysql> create table tt (id int auto_increment primary key , name varchar(30) , msg varchar(100)) engine = myisam;query ok, 0 rows affected (0.01 sec)mysql> insert into tt(name,msg) values('chenzhongyang','good');query ok, 1 row affected (0.00 sec)虽然我们没有指定名字是chenzhongyang的id是1，但是有了auto_increment这个参数，系统会自动给他加上1mysql> select * from tt;+----+---------------+------+| id | name| msg |+----+---------------+------+| 1 | chenzhongyang | good |+----+---------------+------+1 row in set (0.01 sec)我们还可以设置auto_increment的值，但是这个值设置了的话，就会从这个值开始累积
mysql> alter table tt auto_increment=2000;query ok, 1 row affected (0.02 sec)records: 1 duplicates: 0 warnings: 0mysql> insert into tt(name,msg) values('tianhongyan','baby');query ok, 1 row affected (0.00 sec)mysql> select * from tt;+------+---------------+------+| id | name| msg |+------+---------------+------+| 1 | chenzhongyang | good || 2000 | tianhongyan | baby |+------+---------------+------+2 rows in set (0.00 sec)mysql> insert into tt(name,msg) values('zhongguo','xxxxxxx-yyyyyyyyy-+vvvv');query ok, 1 row affected (0.00 sec)mysql> select * from tt;+------+---------------+-------------------------+| id | name| msg|+------+---------------+-------------------------+| 1 | chenzhongyang | good|| 2000 | tianhongyan | baby|| 2001 | zhongguo| xxxxxxx-yyyyyyyyy-+vvvv |+------+---------------+-------------------------+3 rows in set (0.00 sec)
还有一个函数比较有用last_insert_id()。这个函数可以查出最后一次insert的id
mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+|2001 |+------------------+1 row in set (0.00 sec)
3，存储结构
数据文件(.myd),索引文件(.myi)和结构文件(.frm)
特点:可以在不同服务器上拷贝数据文件和索引文件。
如果我们把索引文件和数据文件放到不同的机器上，那么可以提高系统i/o
4，不支持事务
即使我们关闭autocommit，myisam引擎还是会立即执行我们的命令，这个时候rollback已经没有用了
mysql> show variables like %autocommit%;+---------------+-------+| variable_name | value |+---------------+-------+| autocommit | on |+---------------+-------+1 row in set (0.00 sec)mysql> set autocommit=off ;query ok, 0 rows affected (0.00 sec)mysql> show variables like %autocommit%;+---------------+-------+| variable_name | value |+---------------+-------+| autocommit | off |+---------------+-------+1 row in set (0.00 sec)mysql> delete from tt where id=1;query ok, 1 row affected (0.00 sec)mysql> rollback;query ok, 0 rows affected, 1 warning (0.00 sec)mysql> select * from tt;+------+-------------+-------------------------+| id | name| msg|+------+-------------+-------------------------+| 2000 | tianhongyan | baby|| 2001 | zhongguo | xxxxxxx-yyyyyyyyy-+vvvv |+------+-------------+-------------------------+2 rows in set (0.00 sec)
5，myisam_data_pointer_size
默认的指针大小是6byte，一个字节是8bit那么数据文件的大小就是2的6*8次方byte
也就是1024*1024*1024*1024*256/1024/1024/1024/1024=256tb
mysql> show variables like %pointer%;+--------------------------+-------+| variable_name| value |+--------------------------+-------+| myisam_data_pointer_size | 6|+--------------------------+-------+1 row in set (0.00 sec)我们来做个实验试试
如果myisam_data_pointer_size=2，那么就意味着一个表的最大数据文件是65535/1024=64kmysql> set global myisam_data_pointer_size=2;query ok, 0 rows affected (0.00 sec)mysql> show variables like %pointer%;+--------------------------+-------+| variable_name| value |+--------------------------+-------+| myisam_data_pointer_size | 2|+--------------------------+-------+1 row in set (0.00 sec)我们来创建一个 大表ssmysql> create table ss select * from information_schema.tables ;query ok, 54 rows affected (0.09 sec)records: 54 duplicates: 0 warnings: 0mysql> insert into ss select * from ss;query ok, 108 rows affected (0.01 sec)records: 108 duplicates: 0 warnings: 0mysql> insert into ss select * from ss;query ok, 216 rows affected (0.01 sec)records: 216 duplicates: 0 warnings: 0这个时候出现了表ss满了的错误，我们看看数据文件 是64k，要想继续可以插入数据，那么就要把这个参数调大mysql> insert into ss select * from ss;error 1114 (hy000): the table 'ss' is fullmysql> insert into ss select * from ss;error 1114 (hy000): the table 'ss' is fullmysql> insert into ss select * from ss;error 1114 (hy000): the table 'ss' is full[root@test3 test]# ls -lhtotal 116k-rw-rw----. 1 mysql mysql 9.3k sep 12 06:44 ss.frm-rw-rw----. 1 mysql mysql 64k sep 12 06:44 ss.myd-rw-rw----. 1 mysql mysql 1.0k sep 12 06:44 ss.myimysql> insert into ss select * from ss;error 1114 (hy000): the table 'ss' is fullmysql> alter table ss max_rows=10000000000 ;query ok, 496 rows affected (0.11 sec)records: 496 duplicates: 0 warnings: 0mysql> insert into ss select * from ss;query ok, 496 rows affected (0.02 sec)records: 496 duplicates: 0 warnings: 0
·可以处理固定长度或动态长度记录。
7，加锁和并发