MySQL's storage engine

MySQL supports several storage engines as processors of different table types, and the MySQL storage engine includes the engine that handles transaction security tables and the engine that handles non-transactional security tables:

• MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations, it is the default storage engine, unless you configure MySQL to use a different engine by default
• · The memory storage Engine provides an "in-store" table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, the memory and merge storage engines handle non-transactional tables, both of which are included by default in MySQL.
  
  Note: The memory storage engine is formally identified as the heap engine.
  
  · The InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in the Mysql-max binary distribution released for the operating system that supports it. InnoDB is also included by default in all MySQL 5.1 binary distributions, and you can configure MySQL to allow or disallow any engine as you prefer.
  
  · The example storage engine is a "stub" engine that does nothing. You can use this engine to create a table, but no data is stored in it or retrieved from it. The purpose of this engine is to serve as an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Similarly, its main interest is to developers.
  
  · NDB cluster is a storage engine that is used by MySQL cluster to implement tables that are partitioned into multiple computers. It is available in the Mysql-max 5.1 binary distribution. This storage engine is currently supported only by Linux, Solaris, and Mac OS X. In a future MySQL distribution, we want to add support for this engine from other platforms, including Windows.
  
  · The archive storage engine is used to easily overwrite a large amount of data that is stored in a non-indexed manner.
  
  · The CSV storage engine stores data in a comma-delimited format in a text file.
  
  · The Blackhole storage engine accepts but does not store data, and the retrieval always returns an empty set.
  
  · The federated storage Engine has data in the remote database. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In future distributions, we want to have it connect to another data source using a different drive or client connection method.

  More commonly used are MyISAM and INNOBD.

  	MyISAM	InnoDB
  The difference in composition:	Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. The. frm file stores the table definition. The data file has an extension of. MYD (MYData). The extension of the index file is. MYI (Myindex).	A disk-based resource is a InnoDB tablespace data file and its log file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB
  Transaction-handling aspects:	Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support	InnoDB provides transaction support transactions, and advanced database functions such as external keys (foreign key)
  SELECT   update,insert ,delete  operation	If performing a large number of select,myisam is a better choice for	1. If your data performs a large number of insert  or update, For performance reasons, when you should use the InnoDB table   2.DELETE   from table, InnoDB does not reestablish the table, but deletes one row at a time. The   3.LOAD   table from master operation has no effect on InnoDB, the workaround is to change the InnoDB table to MyISAM table first, import the data and then change to the InnoDB table. However, for tables that use additional InnoDB attributes (such as foreign keys), the    is not available;
  operation   for auto_increment;	one auto_ per table The internal processing of the Incremen column.   MyISAM  automatically updates this column for  INSERT  and  UPDATE  operations. This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur). The   auto_increment value can be used by ALTER TABLE or MYISAMCH to reset    for fields of type auto_increment, The InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields    better and faster auto_increment processing	If you specify a auto_increment column for a table, the InnoDB table handle in the data dictionary contains a counter called the autogrow counter, which is used to assign a new value to the column.    autogrow counters are stored only in main memory, not on disk    about the calculator's algorithm implementation, refer to   auto_ increment  column in  InnoDB  how to work
  The exact number of rows in the table	Select COUNT (*) from Table,myisam as long as you simply read the number of rows saved, note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same	The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows
  Lock	Table lock	Provides row lock (locking on row level), providing no lock read consistent with Oracle type (non-locking read in Selects), 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 "% aaa% "

MySQL's storage engine