Differences between MySQL Database Engine MyISAM and InnoDB

MySQL Databases have multiple storage engines: MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE, etc, the most common ones are MyISAM and InnoDB. The following describes the differences between MyISAM and InnoDB mysql database storage engines.

The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities. It is suitable for applications with frequent queries such as data warehouses. In MyISAM, a table is actually saved as three files, the. frm storage table definition,. MYD storage data, and. MYI storage index. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default.

Other non-transactional storage engines (such as MyISAM) on the MySQL server follow different data integrity examples, which are called "Atomic operations ". According to the transaction term, MyISAM tables always work efficiently in AUTOCOMMIT = 1 mode. Atomic operations generally provide comparable integrity and better performance. Compared with the fastest transaction table after optimization and adjustment, it is 3 ~ faster ~ 5 times. Because the MySQL server supports two examples, you can decide whether to use atomic operations to better serve your applications or use transaction features. This option can be selected by table.
 
InnoDB is an engine that supports transactions. MySQL is provided with a transaction security (ACID-compatible) storage engine capable of committing, rollback, and crash recovery. Therefore, data is stored in one or more data files and supports Oracle-like locking mechanisms. It is widely used in OLTP applications. If the InnoDB configuration option is not specified, MySQL will create an automatic extended data file named ibdata1 and two log files named ib_logfile0 and ib_logfile1 under the MySQL data directory.

InnoDB locks row-level and also provides an Oracle-style non-locked read in the SELECT statement. These features increase the deployment and performance of multiple users. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.

InnoDB is designed for maximum performance when processing massive data volumes. Its CPU efficiency may be unmatched by any other disk-based relational database engine. The InnoDB Storage engine is fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the main memory.

InnoDB stores its tables and indexes in a tablespace, which can contain several files. InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB. InnoDB is also included in all MySQL 5.1 binary distributions by default.

Articles you may be interested in

• Rational use of MySQL database indexes to make the database run efficiently
• Mysql database cache function analysis, debugging, and performance summary
• Steps for synchronizing and backing up mysql Databases in windows
• Case sensitivity of mysql Databases
• How to forget the MySQL Database Password of PHPnow
• Summarize the causes and solutions for the slow MySQL Database Server
• Navicat for mysql remote connection to the mySql database prompt 10061,1045 error Solution
• Php mysql database operations