The MySQL database engine depends on how MySQL is compiled during installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines: ISAM, MYISAM, and HEAP. The other two types, INNODB and BERKLEY (BDB), can also be used. If you are skilled, you can also use the MySQL++API to build your own engine. The following describes several database engines:
ISAM ISAM is a well-defined and time tested data table management method. When it was designed, it took into account that the number of queries on the database was far greater than the number of updates. Therefore, ISAM performs read operations quickly and does not consume a lot of memory and storage resources 。 The two main disadvantages of ISAM are that it does not support transaction processing and cannot tolerate faults : If your hard disk crashes, your data files cannot be recovered. If you are using ISAM in mission critical applications, you must always back up all your real-time data. Through its replication feature, MYSQL can support such backup applications.
MyISAM : MyISAM is the ISAM extension format of MySQL and the default database engine. In addition to providing a large number of functions for index and field management that are not available in ISAM, MyISAM also uses a table locking mechanism to optimize multiple concurrent read and write operations. The cost is that you often need to run the OPTIMIZE TABLE command to recover the space wasted by the updated mechanism 。 MyISAM also has some useful extensions, such as the MyISAMCHK tool for repairing database files and the MyISAMPACK tool for recovering wasted space. MYISAM emphasizes fast read operations This may be the main reason why MySQL is so popular in Web development: a large number of data operations you perform in Web development are read operations. So, Most virtual host providers and Internet platform providers only allow MYISAM format 。 An important defect of MyISAM format is that the data cannot be recovered after the table is damaged 。
HEAP ： HEAP allows temporary tables that reside only in memory. Residing in memory makes HEAP faster than ISAM and MYISAM, but the data it manages is unstable, and if it is not saved before shutdown, all data will be lost 。 When data rows are deleted, HEAP will not waste a lot of space. HEAP tables are very useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table after using it 。
InnoDB : InnoDB database engine is a direct product of MySQL flexibility technology, which is called MYSQL++API. When using MYSQL, almost every challenge you face stems from the fact that ISAM and MyISAM database engines do not support transaction processing and foreign keys. Although it is much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and foreign keys, which are not available in the first two engines 。 As mentioned earlier, if your design requires one or both of these features, you will be forced to use one of the last two engines.
MySQL officially explains InnoDB as follows ： InnoDB provides MySQL with a transaction security (ACID compatible) storage engine with the ability to commit, rollback, and crash recovery 。 InnoDB locks at the row level and also provides a consistent Oracle style non locked read in the SELECT statement. These features increase multi-user deployment and performance. There is no need to expand locking in InnoDB, because row level locking is suitable for very small space in InnoDB. InnoDB also supports FOREIGN KEY enforcement. In SQL queries, you can freely mix InnoDB type tables with other MySQL table types, even in the same query.
InnoDB is designed for maximum performance when dealing with huge amounts of data 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 for caching data and indexes in main memory 。 InnoDB stores its tables&indexes in one tablespace, which can contain several files (or raw disk partitions). This is different from the MyISAM table. For example, each table in the MyISAM table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB.
InnoDB is included in MySQL binary distribution by default 。 Windows Essentials installer makes InnoDB the default table for MySQL on Windows.
InnoDB is used to generate data on many large database sites that need high performance 。 The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites process 800 inserts/updates per second on InnoDB on average
In general, MyISAM is suitable for ： (1) Do many count calculations; (2) Insert infrequently and query frequently; (3) There are no transactions. InnoDB is suitable for ： (1) High reliability requirements or transaction requirements; (2) Table updates and queries are frequent, and the chances of table locking are large.
Generally, MySQL will provide multiple types by default Storage Engine , can be viewed through the following:
(1) See what storage your MySQL now provides engine : mysql> show engines;
(2) Look at you MySQL Current default storage engine: mysql> show variables like '%storage_engine%';
(3) You need to see what engine is used for a table (after the parameter engine in the display result indicates the current storage engine used for the table): mysql> Show create table name;
All performance tests are conducted on a Microsoft window xp sp2, Intel (R) Pentium (R) M processor 1.6oGHz 1G memory computer.
Test method: 10 queries are submitted consecutively, the total number of table records is 380000, and the time unit is s
Engine type MyISAM InnoDB performance difference
count 0.0008357 3.0163 3609
Query PK 0.005708 0.1574 27.57
Query non primary key 24.01 80.37 3.348
Update primary key 0.008124 0.8183 100.7
Update non primary key 0.004141 0.02625 6.338
Insert 0.004188 0.3694 88.21
(1) After the index is added, the MyISAM query speed can be increased by 4 206.09733 times, and the InnoDB query speed can be increased by 510.72921 times. At the same time, the MyISAM update speed can be reduced by 1/2 of the original speed, and the InnoDB update speed can be reduced by 1/30 of the original speed. Decide whether to add an index according to the situation. For example, do not do any index on the log table that is not queried 。
(2) If you have millions of data and no transaction processing, MyISAM is the best choice for performance.
(3) The size of InnoDB table is larger, and MyISAM can save a lot of hard disk space.
In the 38w table we tested, the space occupied by the table is as follows:
Engine type MyISAM InnoDB
Data 53924 KB 58976 KB
Index 13640 KB 21072 KB
Total space occupied 67564 KB 80048 KB
The space occupied by another table with 176W records is as follows:
Engine type MyIsam InnorDB
Data 56166 KB 90736 KB
Index 67103 KB 88848 KB
Total space occupied 123269 KB 179584 KB
