The difference between MySQL storage engine innodb/myisam/merge/bdb

MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.

InnoDB: For transactional applications, with many features, including acid transaction support.

BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

Federated: The ability to link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

Archive: Provides the perfect solution for storing and retrieving large amounts of historically, archived, or security audit information that is rarely referenced.

Cluster/ndb:mysql's clustered database engine, especially suitable for applications with high performance lookup requirements, also requires the highest uptime and availability.

Other: The other storage engines include CSV (referencing a comma-delimited file used as a database table), blackhole (for temporary suppression of application input to the database), and the example engine, which can help with the quick creation of a custom plug-in storage engine.

the difference between Mysql:myisam and InnoDB



InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

Myiasm is a new version of the Iasm table, with the following extensions:
Portability at the binary level.
NULL column index.
There is less fragmentation than the ISAM table for variable-length rows.
Support for large files.
Better index compression.
A better key? Statistical distribution.
Better and faster auto_increment processing.

The following are some of the details and the specific implementation differences:

1.InnoDB indexes of type Fulltext are not supported (currently only MYISAM tables are supported and can only be used on fields of CHAR, VARCHAR, TEXT type)
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like '%wfc% '

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL.

The difference between MySQL storage engine innodb/myisam/merge/bdb