MySQL Storage Engine-MyISAM and InnoDB

MySQL has two storage engines: InnoDB and MyISAM, and the following table is a simple comparison of the two engines

	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
Transactional aspects :	MyISAM types of tables emphasize performance and perform several more than InnoDB types, but do not provide transactional support	innodb provide transaction support transactions, external keys and other advanced database features
select  UPDATE INSERT Delete	If you do a lot of select,myisam it's better to choose	1. If your data performs a large number of INSERT or UPDATE , you should use the InnoDB table for performance reasons 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 first change the InnoDB table to MyISAM table, import the data and then change to the InnoDB table, However, tables that use additional InnoDB attributes, such as foreign keys, are not available for
to auto_ INCREMENT Operation	one auto_ per table The internal processing of the Incremen column. MyISAM for INSERT and UPDATE The action automatically updates this column . 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). Auto_increment values can be reset by ALTER TABLE or MYISAMCH For fields 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 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.   The autogrow counter is stored only in main memory, not on disk For an algorithmic implementation of this calculator, 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 storage engine MyISAM and InnoDB How to choose
MySQL has a variety of storage engines, each of which has its own advantages and disadvantages, which can be chosen with merit: MyISAM, InnoDB, MERGE, Memory (HEAP), BDB ( BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole.
Although the storage engine in MySQL is not just MyISAM and InnoDB two, it is commonly used in two. The approximate difference between the
two storage engines is shown in:
1) InnoDB support transactions, MyISAM not supported, this is very important. Transactions are an advanced way of handling, such as in some column additions and deletions, as long as the error can be rolled back to restore, and MyISAM will not be.
2) MyISAM suitable for query and insert-based applications, InnoDB suitable for frequent modification and involving high security applications
3) InnoDB support foreign keys, MyISAM not supported
4) MySQL5.5.5 is the default engine after InnoDB
5) InnoDB does not support fulltext type of index
6) InnoDB does not save the number of rows in the table, such as SELECT COUNT (*) from table, InnoDB needs to scan through the entire table to calculate how many rows, But MyISAM simply reads out the number of saved rows. Note that when the COUNT (*) statement contains a where condition, MyISAM also needs to scan the entire table,
7) for the self-growing field, the InnoDB must contain only the index of that field, but you can establish a federated index with other fields in the MyISAM table
8) When emptying the entire table, InnoDB is a one-line deletion, which is very inefficient. MyISAM rebuilds the table
9) InnoDB supports row locks (or, in some cases, lock an entire table, such as the Update table set a=1 where user like '%lee% '

About MyISAM and InnoDB Select Use:
MyISAM and InnoDB are the two storage engines provided by the MySQL database. The pros and cons of both are quite the same. InnoDB supports advanced features of relational databases, such as transactional and row-level locks, MyISAM not supported. MyISAM has a better performance and consumes less storage space. So, what kind of storage engine to choose, depending on the application:
1) If your application must use transactions, there is no doubt that you have to choose the InnoDB engine. Note, however, that InnoDB's row-level locks are conditional. The full table is still locked when the where condition does not use the primary key. Delete statements such as delete from MyTable.
2) If your application has high query performance requirements, you will need to use MyISAM. MyISAM indexes and data are separate, and their indexes are compressed to make better use of memory. So its query performance is significantly better than InnoDB. A compressed index can also save some disk space. MyISAM has full-text indexing capabilities, which can greatly optimize the efficiency of like queries.

Some people say that MyISAM can only be used in small applications, but this is only a prejudice.
If the amount of data is large, this needs to be addressed through an upgrade architecture, such as a split-table library, rather than relying solely on the storage engine.

Now generally are selected InnoDB, mainly MyISAM full table lock, read and write serial problem, concurrency efficiency lock table, low efficiency MyISAM for read-write intensive applications is generally not to choose.

About the default storage engine for MySQL databases:
MyISAM and InnoDB are two of MySQL's storage engines.
If it is the default installation, it should be INNODB, you can find the Default-storage-engine=innodb in the my.cnf file;
Of course you can specify the appropriate storage engine when you build the table.
The information can be seen through show create TABLE XX.

Comparison of InnoDB and MyISAM in MySQL
1) MyISAM:
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. frm file stores the table definition. The data file has an extension of. MYD (MYData).
MyISAM tables can be compressed, and they support full-text search. Transactions are not supported, and foreign keys are not supported. If the rollback of a thing causes incomplete rollback, it does not have atomicity. Table locks are performed when updata, and the concurrency is relatively small. If executing a lot of select,myisam is a better choice.
MyISAM indexes and data are separate, and the indexes are compressed, and memory usage increases a lot. Can load more indexes, and InnoDB is the index and data is tightly bound, no compression will cause innodb than MyISAM volume is large
The MyISAM cache is indexed in memory, not data. The InnoDB cache in memory is the data, relatively speaking, the larger the server memory, the greater the advantages of InnoDB play.

Advantages: Query data is relatively fast, suitable for a large number of select, can be full-text index.
Cons: Transactions not supported, foreign keys not supported, concurrency is small, not suitable for a large number of update

2) InnoDB: (parameter description: MySQL storage engine InnoDB important parameter description)
This type is transaction-safe: It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are very fast. Features richer than BDB, so it is recommended to use a transaction-safe storage engine. The table makes row locks at update time, and the concurrency is relatively large. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.
Pros: Support transactions, support foreign keys, concurrency is large, suitable for a large number of update
Cons: Query data is relatively fast, not suitable for a large number of select
For INNODB types of tables that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if the autocommit is open), which will greatly improve performance.

The basic differences are:
MyISAM types do 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.

MySQL Storage Engine-MyISAM and InnoDB