作者:袁广龙976丶 | 来源:互联网 | 2018-07-10 17:59
mysql存储引擎MyISAM1,创建myisam表mysqlcreatetablet(idint,namevarchar(30),msgvarchar(100))engineMyISAM;mysqlshowtablestatusliket\G;*****************
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,加锁和并发