The difference between MySQL's InnoDB and MyISAM

The MyISAM engine was used by default before MySQL 5.5, and the InnoDB engine was used by default after 5.5.

The difference:

• Transactions and foreign keys
  InnoDB supports transactions and foreign keys, with security and integrity, suitable for a large number of insert and update operations.
  MyISAM does not support transactions and foreign keys, it provides high-speed storage and retrieval, suitable for a large number of select query operations.

• Locking mechanism
  InnoDB supports row-level locks to lock specified records.Locking is implemented based on the index.
  MyISAM supports table-level locks, locking the entire table.
  The two directly affect the efficiency of concurrency in terms of locking mechanism.

• Index structure
  InnoDB uses a clustered index (clustered index), the index and records are stored together -> both the index and the records are cached.
  MyISAM uses a clustered index (non-clustered index), the index and records are separate.

• Concurrency processing ability
  InnoDB read and write blocking is related to the isolation level. Multi-version concurrency control (MVCC) can be used to support high concurrency.
  MyISAM uses table locks, which will be exclusive, resulting in a low concurrency rate of write operations, and will not block read and write blocking between reads.

• Storage files
  The InnoDB table corresponds to two files, one ==.frmtable structure file and one .ibddata file.The standard can store up to 64TB.
  MyISAM table corresponds to three files, one .frm table structure file, one .MYD table data file, and one .MYI== index file.As of 5.0, tables can store up to 256TB.
• Applicable scenarios

  • InnoDB

    • Requires transaction support (has better transaction features)
    • Row-level locking is well-adapted to high concurrency
    • Scenarios with frequent data updates
    • Higher data consistency requirements
    • The memory of the hardware device is large, and the better cache capacity of InnoDB can be used to improve memory utilization and reduce disk IO

  • MyISAM

    • Transaction support is not required (not supported)
    • relatively low concurrency (locking mechanism problem ->table lock)
    • Data modification is relatively small, mainly read
    • Data consistency requirements are not high

  • Summary->How to choose between two engines?

    • Are transactions required?Required, InnoDB
    • Are there concurrent modifications? Yes, InnoDB
    • Are you pursuing higher query speed with less data modification?Yes, MyISAM

    InnoDB is recommended

Other chapters ->Jump

end...