The difference between Engine=innodb and Engine=myisam in MySQL

1/isam

ISAM is a well-defined and time-tested form of data management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.

2/innodb

It provides transaction control capabilities that ensure that a set of commands are executed successfully, or that the results of all commands are rolled back when there is an error in any command, and it is important to imagine the transaction control capability in an electronic bank. Supports commit, rollback, and other transactional features. The latest version of MySQL is already planning to remove support for BDB and to develop InnoDB in full.

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 does not support indexes of type Fulltext.
The table is not saved in 2.InnoDB
The exact number of rows, that is, the execution of select COUNT (*) from
Table, InnoDB to scan through the entire table to calculate how many rows, but MyISAM simply read out the number of saved rows. Note that when the COUNT (*) statement contains
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.
5.LOAD TABLE from
The 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 "? a%"

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.

Mysql
Administrator The database is built, the table defaults to the InnoDB type.

Innodb,myisam
What is the difference between the two types: 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 advanced database functionality such as transaction support, foreign keys, and so on.

Binary data files of type MyISAM can be migrated in different operating systems. That is, it can be copied directly from the Windows system to a Linux system.

To modify the engine type of a table:

Alter
TABLE tablename ENGINE = MyISAM;

MyISAM:, which is based on the traditional ISAM type, ISAM is indexed
Sequential access method (indexed sequential access methods)
Abbreviation, which is the standard method for storing records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables.
MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys. If the rollback of a thing causes incomplete rollback, it does not have atomicity. If you perform a large number
The Select,myisam is a better choice.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.

The main reason for the speed of the InnoDB type that supports things is that the autocommit default setting is open and the program does not explicitly call begin
Start a transaction, causing each insert to automatically commit, seriously affecting the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if autocommit hits
can also be used), will greatly improve performance.

1. View the table information, including the type of engine used, character encoding, table structure, etc.

Use this command

Mysql>
The show create TABLE t1;--t1 is the table name

2.
You can execute the following command to toggle non-transactional tables to transactions (data is not lost), and the InnoDB table is more secure than the MyISAM table:
ALTER TABLE T1
TYPE=INNODB;--T1 is the table name

3.
InnoDB table cannot be used with Repair Table command and MYISAMCHK-R table_name
But you can use the check table
T1, and Mysqlcheck [OPTIONS] database [tables]

4.
The following parameters have been added to the command line starting the MySQL database to make the newly published MySQL data table default to use transactions (
Only the CREATE statement is affected. ）
--default-table-type=innodb

5.
Temporarily changing the default table type can be used:
Set Table_type=innodb;

MyISAM
Advantages: Fast, disk space consumption, a library or table disk usage can be checked by the operating system to the appropriate file (folder) size, or through the SQL statement show TABLE status can be found
Cons: No data integrity mechanism, that is, transactions and foreign keys are not supported

InnoDB
Advantages: Support transactions and foreign keys, the data integrity mechanism is complete; You can use Show table status to find the disk footprint of a library or table
Cons: Ultra-slow, disk-space-intensive, all libraries in one (usually) or several files, unable to understand the footprint of a library or table through the operating system

BDB
Advantages: Support transactions, not support foreign keys, because on the basis of transaction support, foreign keys can be in the database client (may be the end customer's server side, such as PHP) indirectly implemented, so data integrity is still guaranteed;
Cons: Slow, disk-occupied, unable to query a library or table space consumption through Show table status, the operating system can understand the library corresponding folder, or the size of the corresponding file, but because the BDB table always produces a log file, The actual disk occupancy should also include the log file, so the operating system to find a library or table size is always less than the actual space occupied.

The difference between Engine=innodb and Engine=myisam in MySQL