The MyISAM of the MySQL engine

MySQL commonly used engine mainly have a few, Mrg_myisam, CSV, MYISAM, InnoDB, Memory, NDB, MYISAM, InnoDB is the most commonly used in MySQL storage engine, today the main discussion MYISAM, InnoDB follow up and continue ...

First, the characteristics of the MyISAM engine

1. Do not support things (things refer to a logical set of actions that make up each unit of this set of operations, either full success or all failures)

2, table-level lock, the data update lock the entire table, its locking level is table-level locking, although the implementation of the cost of the lock can be very small but also greatly reduce its concurrency performance

3, read and write each other blocking: not only at the time of writing blocking read, MyISAM will also block the write while reading, but read itself does not block the other read

4, only cache index: MyISAM can be key_buffer_size cache index, to greatly improve the read time block write, but read itself does not block additional read. (key_buffer_size = 16M)

5. The reading speed is brisk and the resource is relatively small.

6. Foreign KEY constraints are not supported, but full-text indexing is supported

II. production business scenarios used by MyISAM engines

1, do not need things to support the business

2, generally for reading data more applications, reading and writing are frequent scenes are not suitable, read more or write more suitable for

3, read and write concurrent access to a relatively low business. (Read-only write-high concurrency is also possible)

4, data modification of relatively small business (blocking problems)

5, to read the main business, such as WWW, blog, picture information database, user database, commodity library and other business

PS: Single operation of the database can use MyISAM, the so-called single is as far as possible to read, write-only (insert update deleter)

Third, MyISAM also supports 3 different storage formats

static [Fixed length] table (the default format, the length of the table field is fixed, the advantages: fast storage, easy to cache, failure easy to recover; OK: Occupy disk space relative dynamic table large, PS: The program when fetching data, the space behind the content will be automatically removed, and the space before the content is not)

Dynamic Tables (table fields are variable, advantages: small footprint, OK: Frequent updates to delete records can result in fragmentation, need to execute Optimeze table statements or myisamchk-r to improve performance, if failure recovery is relatively difficult)

Compressed tables (compressed tables created by the Myisampack tool, occupy very small disk space, each record is compressed separately, access expenses are small)

OPTIMIZE table works only on MyISAM, BDB, and InnoDB tables.

Note that MySQL locks the table during the Optimize table run.

OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

Optimize table tbl_name;

SHOW INDEX from ' tbl_name ';

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with varchar, blob, or text columns), you should use optimize table. The deleted records are kept in the linked list, and subsequent insert operations re-use the old record location. You can use the Optimize table to reuse unused space and defragment the data files.

In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, only for specific tables.

Iv. MyISAM Engine Storage file structure

The engine corresponds to a table with 3 files

myisam.frm [table structure definition],

MyISAM. myd[data file],

MyISAM. myi[Index]

Where data files and index Ask prices can be placed in different directories, the average allocation of Io, can get faster speed.

When you create a table, you specify the directory where the file resides, and the path must be an absolute path with access rights.

DATA DIRECTORY = ' Data_dir '

INDEX DIRECTORY = ' Index_dir '

Instance:

CREATE TABLE ' Myisamdata ' (

' Host ' char (DEFAULT NULL),

' Db ' char (+) DEFAULT NULL,

' User ' char (+) DEFAULT NULL

) Engine=myisam DEFAULT Charset=utf8 DATA directory = '/home/myisamdata/' INDEX directory = '/home/myisamdata/';

Five, MyISAM Engine tuning essentials

1, set the appropriate index (caching mechanism)

2, adjust the read and write priority, according to the actual needs to ensure that important operations more priority implementation

3. Enable deferred insertion improves bulk write performance (reduces write frequency, as many data writes at once)

4, as far as possible sequential operation let insert data are written to the tail, reduce blocking

5, decomposition of large operations, reduce the blocking time of a single operation

6, reduce the number of concurrent (reduce MySQL access), some high concurrency scenarios through the application of queuing queue mechanism

7, for relatively static (change infrequently) database data, make full use of query cache or memcached cache service can greatly improve access efficiency

My.cnf

Query_cache_size =256m

Query_cache_limit =256m

Query_cache_size =2m

Query_cache_min_res_unit =256m

8, MyISAM count only when the full table scan is particularly funny, with other conditions count requires actual data access

9, the master-slave synchronization of the main library using InnoDB, from the library using the MyISAM engine (not recommended)

This article is from the "Silent Dialogue" blog, please be sure to keep this source http://chbinmile.blog.51cto.com/6085145/1873501

The MyISAM of the MySQL engine