MySQL Database Engines Overview
The MySQL database engine depends on how MySQL was compiled at installation time. To add a new engine, you must recompile MySQL. By default, MySQL supports three engines: ISAM, MyISAM, and HEAP. Two other types, InnoDB and BerkeleyDB (BDB), are also often available. If you have advanced skills, you can even create your own engine using the MySQL++ API. Here is an introduction to several database engines:
ISAM: ISAM is a well-defined and time-tested method for managing data tables, designed with the consideration that databases are queried far more often than they are updated. Therefore, ISAM performs read operations very quickly and does not consume large amounts of memory and storage resources. The two main drawbacks of ISAM are that it does not support transactions and is not fault-tolerant: if your hard drive crashes, the data files cannot be recovered. If you are using ISAM in mission-critical applications, you must frequently back up all your real-time data. Through its replication features, MySQL can support such backup applications.
MyISAM: MyISAM is the MySQL ISAM extension format and the default database engine. Besides providing a host of functions for index and field management not found in ISAM, MyISAM uses a table-locking mechanism to optimize multiple concurrent read and write operations, at the cost of needing to run the OPTIMIZE TABLE command frequently to reclaim space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool for repairing database files and the MyISAMPACK tool for reclaiming wasted space. MYISAM emphasizes fast read operations, which is probably the main reason why MySQL has been heavily favored in web development: the vast majority of data operations you perform in web development are read operations. Therefore, most virtual host providers and Internet platform providers only allow the MYISAM format. A significant defect of the MyISAM format is that it cannot recover data after table corruption.
HEAP: HEAP allows temporary tables that reside only in memory. Residing in memory makes HEAP faster than both ISAM and MyISAM, but the data it manages is volatile, and if not saved before shutdown, all data will be lost. HEAP does not waste much space when data rows are deleted. HEAP tables are very useful when you need to use SELECT expressions to select and manipulate data. Remember to drop the tables after you are finished using them.
InnoDB: The InnoDB database engine is a direct product of the technology that makes MySQL flexible, that is, the MYSQL++ API. When using MYSQL, almost every challenge you face stems from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and foreign keys, both features the previous two engines lack. As mentioned earlier, if your design requires one or both of these features, you will be forced to use one of the latter two engines.
MySQL’s official explanation of InnoDB is: InnoDB provides MySQL with a transaction-safe (ACID-compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-style consistent non-locking read in SELECT statements, which increases multi-user deployment and performance. There is no need to escalate locks in InnoDB because row-level locking in InnoDB fits into very small space. InnoDB also supports FOREIGN KEY enforcement. In SQL queries, you can freely mix InnoDB type tables with other MySQL table types, even within the same query.
InnoDB is designed for maximum performance when handling huge data volumes; its CPU efficiency may be unmatched by any other disk-based relational database engine.
The InnoDB storage engine is completely integrated with the MySQL server. InnoDB maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which can contain several files (or raw disk partitions). This is different from MyISAM tables, where each table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where file size is limited to 2GB.
InnoDB is included in MySQL binary distributions by default. The Windows Essentials installer makes InnoDB the default table type for MySQL on Windows.
InnoDB is used to power many large-scale database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores over 1TB of data on InnoDB, and other sites handle an average load of 800 inserts/updates per second on InnoDB.
Generally speaking, MyISAM is suitable for: (1) doing many count calculations; (2) infrequent inserts but very frequent queries; (3) no transactions. InnoDB is suitable for: (1) relatively high reliability requirements or transactions being required; (2) scenarios where table updates and queries are both quite frequent, and the chance of table locking is relatively high.
Typically, MySQL provides multiple storage engines by default, which you can view using the following:
(1) See what storage engines your MySQL currently provides: mysql> show engines;
(2) See the current default storage engine for your MySQL: mysql> show variables like '%storage_engine%';
(3) To see which engine a particular table uses (in the results, the parameter after engine indicates the current storage engine used by that table): mysql> show create table table_name;
All performance tests were conducted on a computer with: Microsoft Windows XP SP2, Intel(R) Pentium(R) M processor 1.60GHz, 1G RAM.
Test Method: Submitted 10 queries consecutively. Total table records: 380,000. Time unit: seconds (s).
Engine Type MyISAM InnoDB Performance Difference
count 0.0008357 3.0163 3609
Query PK 0.005708 0.1574 27.57
Query non-PK 24.01 80.37 3.348
Update PK 0.008124 0.8183 100.7
Update non-PK 0.004141 0.02625 6.338
Insert 0.004188 0.3694 88.21
(1) After adding an index, for MyISAM queries can speed up by: 4,206.09733 times, and for InnoDB queries by 510.72921 times. Meanwhile, MyISAM update speed slowed to 1/2 of the original, while InnoDB update speed slowed to 1/30 of the original. You need to decide whether to add indexes based on the situation; for example, a log table that is not queried should not have any indexes.
(2) If your data volume is in the millions and there is no transaction processing, using MyISAM is the best choice for performance.
(3) InnoDB tables are much larger in size; using MyISAM can save a significant amount of hard disk space.
In our tested table of 380,000 records, the space usage is as follows:
Engine Type MyISAM InnoDB
Data 53,924 KB 58,976 KB
Index 13,640 KB 21,072 KB
Total Space Occupied 67,564 KB 80,048 KB
For another table with 1.76 million records, the space usage is as follows:
Engine Type MyISAM InnoDB
Data 56,166 KB 90,736 KB
Index 67,103 KB 88,848 KB
Total Space Occupied 123,269 KB 179,584 KB