The difference between MySQL's storage engine MyISAM and InnoDB, and what to do with it

The difference between MySQL's storage engine MyISAM and InnoDB
How do you usually choose these 2 types in a specific project?

------Solution--------------------
MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is an abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store 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 executing a lot of 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.

For INNODB types 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.

===============================================================

InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons, depending on the application. The following is a known difference between the two, for informational purposes only.

InnoDB
InnoDB provides MySQL with transaction security with transactional (commit), rollback (rollback), and crash-repair capabilities (crash recovery capabilities) (Transaction-safe (ACID compliant )) Type table. The InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) that is consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. There is no need to widen the lock (lock escalation) in the InnoDB table because the InnoDB column lock (Row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide a foreign key constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB.
InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.


MyISAM
MyISAM is the MySQL default storage engine.

Each of the MyISAM tables is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). The index file is an myi (myindex) extension.

Because MyISAM is relatively simple, it is better than innodb in efficiency. Using MyISAM for small applications is a good choice.

MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle

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

1.InnoDB does not support indexes of type Fulltext.
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.

Www.ixdba.net


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%"
------Solution--------------------
MyISAM is a non-transactional security type, and InnoDB is a transaction-safe type.

The granularity of MyISAM locks is table-level, while InnoDB supports row-level locking.

MyISAM supports full-text type indexing, while InnoDB does not support full-text indexing.

MyISAM is relatively simple, so in terms of efficiency is better than INNODB, small applications can consider the use of MyISAM.

MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle.

The InnoDB table is more secure than the MyISAM table and can be switched from non-transactional tables to transaction tables (ALTER TABLE tablename TYPE=INNODB) without ensuring that the data is not lost.

