MyISAM and InnoDB summary of MySQL storage engine

Difference	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, external keys and other advanced database functions
select  UPDATE INSERT Delete	If performing a lot of Select,myisam is a better choice	1. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table 2.DELETE from table, InnoDB does not re-establish the table, but deletes a row of rows. 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
Actions on auto_increment	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). 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, if MySQL cannot determine the scope 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, and 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 as two.

The approximate differences between the two storage engines are shown in the following:

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 key, MyISAM not support

4) from MySQL5.5.5 onwards, InnoDB is the default engine

5) InnoDB does not support index of Fulltext type

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 rows saved. Note that when the COUNT (*) statement contains a where condition, MyISAM also needs to scan the entire table

7) for self-growing fields, InnoDB must contain only the index of the 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 will rebuild the table.

9) InnoDB supports row locks (or, in some cases, lock whole tables, such as 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.

MyISAM and InnoDB summary of MySQL storage engine