MySQL Learning--storage engine (MYISAM)

View current MySQL storage engine: Show engines \g or show variables like ' have% ';

　　

Both of these methods see which storage engines are supported by the current database, where disavled records indicate support for the storage engine, but are disabled when the database is started

When creating a new table, the engine keyword can be used to set the new table's storage engine, not set the default current database default engines, you can set the

You can modify the engine with the ALTER TABLE DATABASE definition statement: ALTER TABLE T1 engine = InnoDB;

The difference between storage engines

Characteristics	Myisam	InnoDB	Menory	MERGE	NDB
Storage limits	Yes	64TB	Yes	No	Yes
Transaction security		Support
Lock mechanism	Table lock	Row lock	Table lock	Table lock	Row lock
B-Tree Index	Support	Support	Support	Support	Support
Hash index			Support		Support
Full-Text Indexing	Support
Cluster index		Support
Data caching		Support	Support		Support
Index cache	Support	Support	Support	Support	Support
Data can be compressed	Support
Space use	Low	High	N/A	Low	Low
Memory usage	Low	High	Medium	Low	High
Speed of BULK Insert	High	Low	High	High	High
Support for foreign keys		Support

MyISAM Engine:

Myisam is the default engine for MySQL, Myisam does not support transactions, does not support foreign keys, its advantage is the speed of access , there is no requirement for transactional integrity, or Select,insert-based applications can basically use this engine to create tables

Each MyISAM engine is stored as 3 files on disk with the same file name as the table name, but the extension is

. frm (storage definition)

. MYD (myddata, storing data)

. MYI (myindex, storage index)

Data files and index files can be placed in different directories, evenly distributed IO, get faster speed

To establish the path of the index file and data file, you need to create the table through the data directory and the index directory statement, that is, the different MyISAM table index files and data files can be placed under different paths, the file path requires an absolute path, and has access rights

Note: Tables of type MyISAM can be corrupted for many reasons, the damaged table cannot be accessed, you will be prompted to repair or return an error result after access, you can use the Check Table statement to check the health of the MyISAM table, and with repair table Statement to fix a corrupted MyISAM table.

The MyISAM engine table also supports 3 different storage formats

static (fixed-length) Table: Static table is the default storage format, static table fields are non-variable long field, are fixed length, the advantage is that storage is very fast, easy to cache, failure is easy to reply, the disadvantage is that the space occupied more than dynamic, static table data will be based on the width of the column to complement the space, But you won't get these spaces when the app is accessed ~

The most important thing to note is that if you need to save the content that has a space behind it, it will be removed when you return.

　　　　　

Dynamic tables contain variable-length fields, records are not fixed-lengths, so storage has the advantage of relatively little space, but long-term frequent updates and delete records are fragmented and require periodic execution of optimize table statements or MYISAMCHK-R commands to improve performance. and recovery is relatively difficult in the event of a failure

The Compact Table Myisampack tool is created to occupy very small disk space because each record is individually locked, so access is only very small access expense.

　　　　

　　　

MySQL Learning--storage engine (MYISAM)