MySQL Engine related knowledge

The MySQL database engine depends on how MySQL was compiled when it was installed. To add a new engine, you must recompile MySQL. By default, MySQL supports three engines: ISAM, MyISAM, and heap. Two other types of InnoDB and Berkley (BDB) are also often available. If you are skilled, you can also use the mysql++ API to make an engine yourself. Several database engines are described below:

ISAM: ISAM is a well-defined and time-tested method of data table management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result,ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. the two main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.

MyISAM: MyISAM is the ISAM extended format of MySQL and the default database engine. In addition to providing a number of functions for index and field management not available in ISAM, MyISAM also uses a table-locking mechanism to optimize multiple concurrent read and write operations, at the expense of the need to run the Optimize Table command frequently to restore 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, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most web hosting providers and internet platform providers only allow the use of the MyISAM format. an important flaw in the MyISAM format is the inability to recover data after a table is corrupted.

Heap:The heap allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutting down, all the data will be lost. The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using the table.

InnoDB: The InnoDB database engine is a direct product of the technology that makes MySQL flexible, which is the mysql++ API. every challenge you face when using MySQL comes from the ISAM and MyISAM database engine does not support transactional processing (transaction process) or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for transactional and foreign keys, which are not available in the top two engines for two points. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.

MySQL is the official explanation for InnoDB:InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row level and also provides an Oracle-style, non-locking read in the SELECT statement, which adds multiuser deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.

InnoDB is designed for maximum performance when dealing with huge amounts of data, and its CPU efficiency can be unmatched by any other disk-based relational database engine.

The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.

The InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a default table for MySQL on Windows.

InnoDB is used to generate large database sites that require high performance. The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and there are other sites on the InnoDB that handle an average of 800 insertions/updates per second

Generally speaking, MyISAM is suitable for: (1) do a lot of count calculation, (2) Insert infrequently, query very frequently; (3) No transaction. InnoDB Suitable for: (1) High reliability requirements, or request transactions, (2) Table updates and queries are fairly frequent, and table locking opportunities are relatively large cases.

In general, MySQL will provide a variety of storage engines by default, which can be viewed through the following:

(1) See what your MySQL has to offer now what storage engine:mysql> show engines;

(2) Look at your MySQL current default storage engine:mysql> show variables like '%storage_engine% ';

(3) You want to see what engine the table uses (in the display results, the storage engine that the table is currently using):mysql> show create table name;

All performance tests are tested on computers with: Micrisoft window XP SP2, Intel (r) pentinum (r) M processor 1.6oGHz 1G memory.

Test method: Submit 10 consecutive query, table records total: 380,000, time unit s

Engine Type MyISAM InnoDB performance difference

Count 0.0008357 3.0163 3609

Query primary key 0.005708 0.1574 27.57

Querying for non-primary keys 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

Insertion 0.004188 0.3694 88.21

(1) After the index, for the MyISAM query can be accelerated: 4 206.09733 times times, the InnoDB query 510.72921 times times faster, while the MyISAM update slowed down to the original 1/2,innodb update speed slowed to the original 1/30. to see if the situation determines whether to index, such as the log table without querying, do not do any index.

(2) If your data volume is millions, and there is no transaction processing, then using MyISAM is the best choice for performance.

(3) The size of the InnoDB table is more large, with MyISAM can save a lot of hard disk space.

In this 38w table we tested, the table takes up space 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

Another 176W record table, where the table occupies space, is as follows:

            Engine type                  myisam                INNORDB
             Data                     56,166 kb           90,736 KB
             Index                     67,103 kb           88,848 KB
            occupy total space          123,269 kb         179,584 KB

Knowledge of the MySQL engine