mysql dba系统学习（20）mysql存储引擎MyISAM

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: t Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2013-09-12 00:39:29 Update_time: 2013-09-12 00:39:29 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_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这个参数，系统会自动给他加上1 mysql> 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: 0 mysql> 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=64K mysql> 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) 我们来创建一个 大表ss mysql> create table ss select * from information_schema.tables ; Query OK, 54 rows affected (0.09 sec) Records: 54 Duplicates: 0 Warnings: 0 mysql> insert into ss select * from ss; Query OK, 108 rows affected (0.01 sec) Records: 108 Duplicates: 0 Warnings: 0 mysql> 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 full mysql> insert into ss select * from ss; ERROR 1114 (HY000): The table 'ss' is full mysql> insert into ss select * from ss; ERROR 1114 (HY000): The table 'ss' is full [root@test3 test]# ls -lh total 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.MYI mysql> insert into ss select * from ss; ERROR 1114 (HY000): The table 'ss' is full mysql> alter table ss max_ROWS=10000000000 ; Query OK, 496 rows affected (0.11 sec) Records: 496 Duplicates: 0 Warnings: 0 mysql> insert into ss select * from ss; Query OK, 496 rows affected (0.02 sec) Records: 496 Duplicates: 0 Warnings: 0

·可以处理固定长度或动态长度记录。

7，加锁和并发