Common storage engines and index types in MySQL

Storage Engine

1. Definition

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table).

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.

2. Types and features of the storage engine

Engine name	Advantages	Defects	Application Scenarios
MyISAM	Independent of the operating system, which means that it can be easily ported from a Windows Server to a Linux server	Transaction/row-level lock/FOREIGN KEY constraint not supported	Ideal for managing mail or Web server log data
InnoDB	Robust transactional storage engine, support for transaction/row-level lock/FOREIGN KEY constraints automatic disaster recovery/auto_increment		Transaction support is required and has a high frequency of concurrent reads
MEMORY	To get the fastest response time, the logical storage medium used is the system memory	When the mysqld daemon crashes, all memory data is lost; You cannot use a variable length data type such as BLOB and text	Temp table
MERGE	is a variant of the MyISAM type. Merging tables is the merging of several identical MyISAM tables into a single virtual table		Often applied to logs and data warehouses
ARCHIVE	Archive meaning, support index, have a good compression mechanism	Only insert and query features are supported	Often used as a warehouse

For details, please refer to:

http://blog.csdn.net/codepen/article/details/46678725

Http://www.cnblogs.com/lina1006/archive/2011/04/29/2032894.html#commentform

Index

nomal:

There's nothing to say.

Unique:

Column values cannot be duplicated, and a primary key is a special unique index

Full Text:

For full-text indexing, available only on fields of char, varchar, and text in the MYISAM/INNODB engine table

Create:

CREATE TABLE article (

ID INT auto_increment not NULL PRIMARY KEY,

Title VARCHAR (200),

Body TEXT,

Fulltext (title, body)

) Type=myisam;

Use:

--Single row, word

SELECT * from ' student ' WHERE MATCH (' name ') against (' Cong ')

--Multi-column, multi-word

SELECT * from ' student ' WHERE MATCH (' name ', ' address ') against (' Cong Guangdong ')

--use in BOOLEAN mode to avoid the threshold limit of 50%.

SELECT * from ' student ' WHERE MATCH (' name ') against (' Cong ' in BOOLEAN MODE)

(see:http://blog.csdn.net/u011734144/article/details/52817766)

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

B-tree Index:

The B-tree index is the most frequently used index type in a MySQL database, and all storage engines except the Archive storage engine support B-tree indexes. Most of the physical files indexed by B-tree are stored in the structure of the Balance tree, which means that all the data needed is stored in the leaf node of the tree, and the shortest path to any leaf node is exactly the same length.

Hash Index:

Convert the indexed field to Hashcode, and sort the hashcode. Only memory engines are supported.

(See: http://blog.sina.com.cn/s/blog_6fd335bb0100v1lm.html)

Common storage engines and index types in MySQL