MySQL Storage engine

In order to prevent unscrupulous site crawler crawling articles, hereby identified, reproduced please indicate the source of the article. LAPLACEDEMON/SJQ.

Http://www.cnblogs.com/shijiaqi1066/p/3857808.html

Storage Engine Overview

The storage engine is the implementation of the technology of storing data, indexing, updating query data and so on.

The storage engine is table-based , not library-based. So the storage engine can also be called a table type.

Databases such as Oracle,sqlserver have only one storage engine. MySQL provides a plug-in storage engine architecture. So MySQL has a variety of storage engines that can use the appropriate engine or write the storage engine as needed.

Querying the supported storage engines in a MySQL system

Use the MySQL client command to display the database engine supported by the current MySQL system.

Command:show engines;

View the storage engine currently in use by default

Command:show variables like ' table_type ';

InnoDB

Become the default storage engine for MySQL from Mysql5.5.8,innodb. MySQL supports InnoDB starting from 3.23.34a.

InnoDB supports transactions , primarily for OLTP database applications. Supports row locks , supports foreign keys , and supports line-locked reads similar to Oracle, where the default read operation does not generate locks.

Auto-Grow column auto_increment is supported in the INNODB storage engine. The value of the autogrow column cannot be empty, and the value must be unique and must be the primary key. When you perform an insert operation, if you do not specify a value for the autogrow column, or the value of the autogrow column is 0 or null, the inserted value is the auto-grow value.

If you insert any value, the value does not appear in the column, you can insert it directly.

The structure of the tables created in the INNODB storage engine is stored in the. frm file. Data and indexes are stored in innodb_data_home and Innodb_data_path table spaces.

For table data storage, the InnoDB storage engine is clustered, and each table's storage is stored in the primary key order. If you do not specify a primary key when the table definition is displayed, InnoDB generates a 6-byte rowid for each row, which is used as the primary key.

InnoDB supports foreign keys. The table where the foreign key resides is the child table, and the foreign key depends on the table as the parent table. Fields that are supported by the primary table's foreign key in the parent table must be the primary key. When you delete or update a piece of information on a buoy, the child table must also have a corresponding change.

The INNODB provides high concurrency through multiple versioning concurrency control (MVCC) , and implements 4 isolation mechanisms for the SQL standard, which defaults to the repeatable level. Use a strategy called Next-key locking to avoid phantom reading . InnoDB also provides high performance and high availability features such as insert buffering, two write, Adaptive hash index, read ahead, and more.

MyISAM

MySQL5.5.8 before MyISAM was the default storage engine for MySQL.

MyISAM does not support transactions, foreign keys are not supported, full-text indexing is supported, and processing speed is fast. Primarily for OLAP database applications.

The table for the MyISAM storage engine is stored as 3 files, with the same file name as the table name and the extension: Frm,myd,myi.

• FRM file: The structure of the stored table.
• MyD file: Stores data.
• Myi file: Stores the storage index.

Before MySQL version 5.0, the MYISAM default supported table size is 4GB, and if you need to support a MyISAM table larger than 4GB, you need to specify the Max_Rows and Avg_row_length properties. Starting with MySQL5.0, MyISAM supports 256T of single-table data by default.

The buffer pool for MyISAM caches only the index files, not the data files. The cache of the data files is given to the operating system. This is different from most databases that use the LRU algorithm to cache data. Prior to MySQL5.1.23, the maximum cache could be set to 4GB, regardless of the 32-bit system or 64-bit system, and in later versions, the 64-bit system could support an index buffer greater than 4GB.

MEMORY

The Memory storage Engine (formerly known as the heap storage engine) stores the data in the table in RAM. The table structure is stored as a file on disk. The file name is the same as the table name, with the suffix named frm. The server needs to have enough memory to maintain the use of the table of the memory storage engine. If you don't want to use it, you can free up the content, or delete the tables you don't want.

The memory storage engine uses a hash index by default and can specify the index type as required.

Only table locks are supported and concurrency performance is poor. column types that do not support varchar, blob, and text.

Memory storage engines are often seldom used. Because of memory-based, the response is very fast. However, if the memory is abnormal, it will affect the integrity of the data. All memory data will be lost if the machine is restarted or shut down, or when the mysqld daemon crashes.

The size of the memory table is mainly determined by the two parameters, Max_Rows and max_heap_table_size, respectively:

• Max_Rows can be specified when the table is created;
• The size of the max_heap_table_size defaults to 16MB and can be expanded as needed.

If the data exceeds the set value, the MySQL database converts it to the MyISAM Storage engine table, which is stored on disk. This results in decreased access performance.

Consider using the memory table when the data is as follows:

• Temporary data: The target data is only temporary and must be available immediately in its life cycle.
• Relatively unrelated data: data stored in memory tables, if suddenly lost, does not have a substantial negative impact on application services and has no long-term impact on data integrity.

If you use MySQL4.1 and previous versions, the memory search is less effective than the MyISAM table, because the memory table supports only hash indexes, which requires searching using the entire key. However, the version after 4.1 supports both the hash index and the B-tree index. B-Tree cables can use some queries and wildcard queries, or you can use operators such as <, >, and >=.

NDB

The NDB storage engine is a cluster that stores the previous, similar to the Oracle-like RAC cluster.

NDB is characterized by a very fast primary key lookup and the ability to submit database performance linearly by adding NDB data storage nodes, which is a highly available, high performance cluster system.

A problem with the NDB storage engine is that its query connection operations are done at the database level, not the storage engine. This means that complex connection operations require significant network overhead, so the return scrubbing speed is slow.

Archive

The archive storage engine supports only insert and select operations, and indexes are supported starting from MySQL5.1.

The archive storage engine uses the zlib algorithm to store data and compress it for up to 1:10 compression ratios. The archive storage engine is ideal for storing archived data. Archive uses row locks to implement high-concurrency inserts, but does not support transactions.

Federated

The federated storage Engine does not store data, but simply points to a table on a remote MySQL database server.

Maria

The Maria storage Engine is a newly developed engine that replaces the original MyISAM. Supports cache data and index files, applies row lock design, provides MVCC functionality, supports transactional and non-transactional security options, and better processing performance for BLOB character types.

Other storage engines

MySQL also has a lot of other storage engines, including Merge,cvs,sphinx,infobright, which each have their own use cases.

Specifying the storage engine when creating a table

Create a InnoDB data table with the basic format:

CREATE Table Table name (

......

) ENGINE = INNODB;

Example: Create a Country table and specify the storage engine as InnoDB.

CREATE TABLE Country (    country_id SMALLINT UNSIGNED not NULL auto_increment,    country VARCHAR (.) Not NULL,    Last_update TIMESTAMP not NULL DEFAULT current_timestamp on update current_timestamp,    PRIMARY KEY (country_id)) Engin E = InnoDB DEFAULT charset=gbk;

Modify the table's storage engine

Modify the table's storage engine to InnoDB, the basic format:

ALERT table name ENGINE = INNODB;

Modifying a table's storage engine with alert table may result in data loss in the database, so you need to back up your data before you modify it.

In order to prevent unscrupulous site crawler crawling articles, hereby identified, reproduced please indicate the source of the article. LAPLACEDEMON/SJQ.

Http://www.cnblogs.com/shijiaqi1066/p/3857808.html