MySQL DBA system learning (MYISAM) MySQL storage engine

MySQL Storage engine MyISAM

1, create MyISAM table

Mysql> CREATE TABLE t (ID int, name varchar (), msg varchar ()) 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

When using this parameter, the column must be a primary key

Mysql> CREATE TABLE TT (ID int auto_increment PRIMARY key  , name varchar, msg varchar (MB)) 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)
Although we did not specify the name is Chenzhongyang ID is 1, but with the auto_increment parameter, the system will automatically add him 1
mysql > select * from TT;
+----+---------------+------+
| id | name          | MSG  |
+----+---------------+------+
|  1 | Chenzhongyang | Good |
+----+---------------+------+
1 row in Set (0.01 sec)

We can also set the value of auto_increment, but if this value is set, it will start accumulating from this value.

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 (' Tian
Hongyan ', ' 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> inserts into TT (NAME,MSG) VALUES (' Zhongguo ', ' xxxxxxx-yy
YYYYYYY-+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)

There is also a function that is more useful last_insert_id (). This function can detect the ID of the last insert

Mysql> Select  last_insert_id ();
+------------------+
| last_insert_id () |
+------------------+
|             2001 |
+------------------+
1 row in Set (0.00 sec)

3, storage structure

Data file (. MYD), index file (. MYI) and Structure file (. frm)

Features: Data files and index files can be copied on different servers.

If we put the index file and the data file on a different machine, we can improve the system I/O