Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the MySQL engine has, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The MySQL data library engine depends on how MySQL is compiled at installation time. 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), are also often available. If you are highly skilled, you can also use MySQL++ API to make your own engine. Here are several database engines:

ISAM:ISAM is a well-defined and time-tested data table management method. When it is designed, it takes into account that the number of times the database is queried is much greater than the number of updates. As a result, ISAM performs read operations very quickly and does not consume a lot of memory and storage resources. The two main drawbacks of ISAM are that it does not

Support for transactions and no fault tolerance: if your hard drive crashes, the data files cannot be recovered. If you are using ISAM in mission-critical applications, you must back up all your real-time data frequently, and MYSQL can support such backup applications through its replication feature.

MyISAM:MyISAM is MySQL's ISAM extension format and default database engine. In addition to providing a large number of indexing and field management features that are not available in ISAM, MyISAM also uses a table locking mechanism to optimize multiple concurrent read and write operations at the cost of running OPTIMIZE TABLE commands frequently to recover the 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. MYISAM emphasizes fast read operations, which is probably the main reason why MySQL is so popular with WEB developers: a lot of the data operations you do in WEB development are read operations. Therefore, most web hosting providers and INTERNET platform providers are only allowed to use the MYISAM format. An important drawback of the MyISAM format is that it cannot recover data after the table is corrupted.

HEAP:HEAP allows temporary tables that reside only in memory. Staying in memory makes HEAP faster than both ISAM and MYISAM, but the data it manages is unstable, and if it is not saved before shutting down, all data will be lost. HEAP does not waste a lot of space when the data row is deleted. HEAP tables are useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table after you have finished using it.

The InnoDB:InnoDB database engine is a direct product of the technology that makes MySQL flexible, which is MYSQL++ API. Almost every challenge you face when using MYSQL stems from the fact that ISAM and the MyISAM database engine do not support transaction processing (transaction

Process) also does not support foreign keys. Although much slower than the ISAM and MyISAM engines, InnoDB includes support for transactions and foreign keys that the first two engines do not have. 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 defines InnoDB as follows: InnoDB provides MySQL with a transaction-secure (ACID-compatible) storage engine with commit, rollback, and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-style consistent non-lock read in SELECT statements, which increases multi-user deployment and performance. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small spaces. InnoDB also supports FOREIGN

KEY mandatory. In SQL queries, you are free to mix InnoDB tables with other MySQL table types, even in the same query.

InnoDB is designed for maximum performance when dealing with large amounts of data, and its CPU efficiency may not be matched 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 tables & indexes in one tablespace, which can contain several files (or raw disk partitions). This is different from the MyISAM table, such as where 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 by default in the MySQL binary distribution. Windows Essentials installer makes InnoDB the default table for MySQL on Windows.

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

Generally speaking, MyISAM is suitable for: (1) doing a lot of count calculations; (2) inserting infrequently and querying very frequently; (3) no transactions. InnoDB is suitable for: (1) high reliability requirements, or transactions; (2) table updates and queries are quite frequent, and the chances of table locking are high.

In general, MySQL provides a variety of storage engines by default, which can be viewed as follows:

(1) see what storage engine your MySQL now provides: mysql > show

Engines

(2) check out your MySQL's current default storage engine: mysql > show variables like'% storage_engine%'

(3) you need to see what engine is used for a table (the storage engine currently used for the table is indicated by the parameter engine in the display result): mysql > show create table table name

All performance tests were performed on computers with Micrisoft window xp sp2, Intel (R) Pentinum (R) M processor 1.6oGHz 1G memory.

Test method: submit 10 query successively, total number of table records: 380000, time unit s

Engine type MyISAM InnoDB performance difference

Count 0.0008357 3.0163 3609

Query key 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 adding the index, the query for MyISAM can be accelerated by 4 206.09733 times, and the query for InnoDB can be accelerated by 510.72921 times. At the same time, the update speed of MyISAM can be slowed down to the original speed of 1x2jue InnoDB, which slows down to the original speed of 1x30. It depends on the situation to decide whether to add indexes, such as log tables that are not queried, and do not do any indexes.

(2) if you have millions of data and do not have any transactions, then using MyISAM is the best choice.

(3) the size of the InnoDB table is larger, and you can save a lot of hard disk space with MyISAM.

In the 38w table we tested, the table occupies space as follows:

Engine type MyISAM InnoDB

Data 53924 KB 58976 KB

Index 13640 KB 21072 KB

Occupies total space 67564 KB 80048 KB

Another table with 1.76 million records takes up space as follows:

Engine type MyIsam InnorDB

Data 56166 KB 90736 KB

Index 67103 KB 88848 KB

Occupies total space 123269 KB 179584 KB

Thank you for reading this article carefully. I hope the article "what are the MySQL engines" shared by the editor will be helpful to you? at the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.