The MySQL engine

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
The MySQL database engine depends on the MySQL at the time of installation is to be compiled. To add a new engine, it must be re compiled MYSQL. By default, MYSQL supports three engine: ISAM, MYISAM and HEAP. The other two types of INNODB and BERKLEY (BDB), often can use. If the technology is excellent, can also use the MySQL++ API make an engine. Here are several database engine:

    ISAM: ISAM is a data table management method of a clearly defined and stand the test of time, the number of which in the design when taking into account the number of database query is far greater than the update. Therefore, the ISAM executes a read operation is fast, and do not take up a lot of memory and storage resources. Two major disadvantage of ISAM is that, it does not support transaction processing, also can't fault tolerance: if your hard drive crash, then the data file cannot be recovered. If you are using ISAM in mission critical applications, it must often backup all your real-time data, through its copy characteristics, MYSQL can support the backup applications.

    MyISAM: MyISAM is a MySQL extension of ISAM format and the default database engine. In addition to providing a functional index and field management are not in ISAM, MyISAM also use a form of locking mechanism, to optimize multiple concurrent read and write operation, the price is you need to often run the OPTIMIZE TABLE command, to restore the update mechanism of the wasted space. MyISAM also has some useful extensions, for example used to repair the database file MyISAMCHK tools and used to recover the waste of space MyISAMPACK tools. MYISAM highlighted the rapid read operation, which may be why MySQL is the main reason of WEB development is so popular: a large amount of data that you are in the development of WEB is a read operation. So, most of the virtual host and INTERNET platform providers are only allowed to use MYISAM format. The MyISAM format is an important defect is impossible to recover the data in the table after damage.

    HEAP: HEAP allows only temporary tables reside in memory. Reside in memory let HEAP be faster than ISAM and MYISAM, but the management of data is not stable, and if not saved before shutdown, all data will be lost. When deleted in the data row, HEAP also won't waste a lot of space. The HEAP table to use SELECT expressions to selection and manipulation of data is very useful to you. Remember, just delete the form in finished form after.

    InnoDB: The InnoDB database engine is the direct product of creating the flexibility of MySQL technology, this technology is the MYSQL++ API. When using MYSQL, every challenge you faced almost are derived from the ISAM and the MyISAM database engine does not support transaction processing (transaction process) also does not support foreign key. Although much slower than the ISAM and MyISAM engine, but the InnoDB include the transaction processing and foreign key support, both of which are not the first two engine. As mentioned before, if your design requires one or both of these properties, then you will be forced to use one of the two engines.

    MySQL official explained to InnoDB: InnoDB provided MySQL with transaction security submit, rollback and crash recovery ability (ACID compatible) storage engine. InnoDB lock at the row level and also in the SELECT statement provides a Oracle style consistent non locking read, these features increase the multi user deployment and performance. Without expanding the lock needs in InnoDB, because the lock for very small space in InnoDB row level. InnoDB also supports the FOREIGN KEY forced. In a SQL query, you can freely use the InnoDB type table with other MySQL table type mixed together, and also can be mixed in the same query.

    InnoDB is designed for maximum performance when processing huge amount of data, the CPU efficiency could be any other relational database engine based on disk that cannot match.

    InnoDB storage engine is completely with the MySQL server consolidation, InnoDB storage engines in main memory cache data and index while maintaining its own buffer pool. InnoDB stores its table and index on a table space, table space may contain several file (or the original disk partition). The MyISAM table, such as in the MyISAM table in each table is in separate files. The InnoDB table can be of any size, even if the file size is limited to 2GB operating system.

    The default InnoDB contained in the MySQL binary distribution. Windows Essentials installer make InnoDB Windows the default MySQL table.

    InnoDB is used to generate the need for high performance large database site. The famous Internet news site running on InnoDB. Mytrix, Inc. store more than 1TB of data in InnoDB, there are some other site in InnoDB treatment on average 800 times per second insert / update

    Generally speaking, MyISAM is suitable for: (1) to do the calculation a lot of count; (2) insert not frequently, the query is very frequent; (3) no transaction. InnoDB is suitable for: (1) reliability requirements are relatively high, or the transaction; (2) table update and query are quite frequent, and table locking a greater chance of.

    Under normal circumstances, MySQL will default to provide multiple storage engines, can use the following view:

    (1)Look at your MySQL now offer what the storage engine: MySQL> show engines;

    (2)See your storage engine MySQL the current default: MySQL> show variables like '%storage_engine%';

    (3)Do you want to see a table with what engine (displayed in the results in parameter engine on the back of said storage engine the current use): mysql> show create table table name;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Brent at November 28, 2013 - 6:53 PM