MySQL's MyISAM storage engine

MyISAM is the default storage engine for MySQL, which is stored as three files on disk. MyD is a data file. Myi is the index file. frm is the storage table definition. We can consider the data files and index files to separate disk, to achieve the average IO distribution.

If there is no requirement for transactional integrity, or a SELECT, insert-based operation, you can select the storage engine.
1. MyISAM only caches the index, while the data file uses the operating system cache, and the operating system cache is used when the index data is larger than the key buffer.

2. Use COUNT (*) very quickly, because the number of rows is saved separately.

3, does not support transactions, does not support foreign keys, access fast.

4, using table-level locking.

5, each table a data file, backup time can be directly copied, restore the time can also be directly covered, easy to operate.

6. You can use MYISAMCHK for fault recovery.

MyISAM Table storage format: Static table (fixed length) "Default format", Dynamic table, compressed table

1, fixed format features: Fast, easy to cache, after the crash easy to recover, more than the dynamic table occupies a larger space. But the static table inside the field is fixed length, if the length of the stored data is not enough, then the other bits are filled with spaces, when used, the system will be stripped of space. So this place introduces a problem, if the tail of our data contains spaces, then when using the data, the space will be removed, we need to be very careful about this.

2. Dynamic Tabular features: In addition to columns of less than 4 length, the column lengths of other character types are variable, less space is used than static fixed formats, and if a row becomes very large, shards occur, so it can be fragmented by using optimize table or Myisamchk- R to improve performance, use Myisamchk-ei to get the statistics of the table, and if it crashes, it is harder to recover than the static format table.

Effect--------

root->/tmp# myisamchk-r/var/lib/mysql/test/test_table. MYI
-Recovering (with Keycache) myisam-table '/var/lib/mysql/test/test_table. MYI '
Data records:23068672

root->/tmp# myisamchk-ei/var/lib/mysql/test/test_table. MYI
Checking MyISAM file:/var/lib/mysql/test/test_table. MYI
Data records:23068672 Deleted blocks:0
-Check File-size
-Check record Delete-chain
-Check Key Delete-chain
-Check Index Reference
-Check records and index references
records:23068672 m.recordlength:7 Packed:-40%
Recordspace used:100% Empty space:0% blocks/record:1.00
Record blocks:23068672 Delete blocks:0
Record data:161480704 Deleted data:0
Lost space:0 linkdata:0

User time 1.25, System time 0.12
Maximum resident set Size 1684, Integral resident set size 0
Non-physical pagefaults 516, physical pagefaults 0, Swaps 0
Blocks in 0 off 0, Messages in 0 out 0, signals 0
Voluntary context switches, involuntary context switches 2746
root->/tmp#

3, the characteristics of the compression table: Occupy very little disk space;

root->/var/lib/mysql/test# LS-LH
Total 155M
-RW-RW----1 mysql mysql 8.5K Sep 14:05 err_table.frm
-RW-RW----1 MySQL mysql 612 Sep 14:05 err_table. MYD
-RW-RW----1 mysql mysql 1.0K Nov 16:16 err_table. MYI
-RW-RW----1 mysql mysql 8.4K Jan 23:34 test_table.frm
-RW-RW----1 mysql mysql 154M Jan 11:19 test_table. MYD
-RW-RW----1 mysql mysql 1.0K Jan 11:19 test_table. MYI
root->/var/lib/mysql/test# Myisampack test_table. MYI
Compressing test_table. MYD: (23068672 Records)
-Calculating statistics
-Compressing file
71.43%
root->/var/lib/mysql/test# LS-LH
Total 45M
-RW-RW----1 mysql mysql 8.5K Sep 14:05 err_table.frm
-RW-RW----1 MySQL mysql 612 Sep 14:05 err_table. MYD
-RW-RW----1 mysql mysql 1.0K Nov 16:16 err_table. MYI
-RW-RW----1 mysql mysql 8.4K Jan 23:34 test_table.frm
-RW-RW----1 mysql mysql 45M Jan 11:19 test_table. MYD
-RW-RW----1 mysql mysql 1.0K Jan 11:24 test_table. MYI

Testing the transactional MyISAM

----------------------

mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO err_table (errsql) VALUES (' Ppyy ');
Query OK, 1 row affected (0.03 sec)

Mysql> select * from Err_table;
+---------+--------+--------+
| Errcode | Errsql | Retime |
+---------+--------+--------+
| NULL | Ppyy | NULL |
+---------+--------+--------+
1 row in Set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> select * from Err_table;
+---------+--------+--------+
| Errcode | Errsql | Retime |
+---------+--------+--------+
| NULL | Ppyy | NULL |
+---------+--------+--------+
1 row in Set (0.00 sec)

Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Err_table;
+---------+--------+--------+
| Errcode | Errsql | Retime |
+---------+--------+--------+
| NULL | Ppyy | NULL |
+---------+--------+--------+
1 row in Set (0.00 sec)

mysql> ALTER TABLE err_table ENGINE=INNODB;
Query OK, 1 row affected (0.19 sec)
Records:1 duplicates:0 warnings:0

mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO err_table (errsql) VALUES (' ZZQQQ ');
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Err_table;
+---------+--------+--------+
| Errcode | Errsql | Retime |
+---------+--------+--------+
| NULL | Ppyy | NULL |
| NULL | ZZQQQ | NULL |
+---------+--------+--------+
2 rows in Set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

Mysql> select * from Err_table;
+---------+--------+--------+
| Errcode | Errsql | Retime |
+---------+--------+--------+
| NULL | Ppyy | NULL |
+---------+--------+--------+
1 row in Set (0.00 sec)

Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Err_table;
+---------+--------+--------+
| Errcode | Errsql | Retime |
+---------+--------+--------+
| NULL | Ppyy | NULL |
+---------+--------+--------+
1 row in Set (0.00 sec)

----------------------

View a table-like body

----------------------

Mysql> Show Table status like ' test_table ' \g;
1. Row ***************************
Name:test_table
Engine:myisam
Version:10
Row_format:fixed
rows:46137344
Avg_row_length:7
data_length:322961408
max_data_length:1970324836974591
index_length:1024
data_free:0
Auto_increment:null
Create_time:2015-01-19 23:34:54
Update_time:2015-01-22 11:19:17
Check_time:2015-01-22 11:19:17
Collation:utf8_general_ci
Checksum:null
Create_options:
Comment:
1 row in Set (0.00 sec)

ERROR:
No query specified

----------------------

Common damage of MyISAM table:

1, in the process of writing MySQL process was killed;

2, host downtime (such as hardware failure)

This article is from the "shadow debut" blog, please be sure to keep this source http://woodywoodpecker.blog.51cto.com/4820467/1606984

MySQL's MyISAM storage engine