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

This article introduces the knowledge of "what is the engine of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

How would it feel if you were a race car driver and could change the engine immediately at the push of a button without having to drive the car to the garage? What MySQL databases do for developers is like a button switch engine; it lets you choose the database engine and gives you an easy way to switch it.

MySQL's own engine is certainly enough, but in some cases, other engines may be more suitable for the task than the one at hand. If you like, you can even use MySQL++ API to create your own database engine, just like piercing a cylinder to install your own carburetor. Now let's take a look at how you choose the engine and how to switch between the available engines.

Choose your engine.

The database engine you can use depends on how MySQL is compiled during installation. To add a new engine, you must recompile MySQL. The concept of compiling an application just to add a feature may seem strange to Windows developers, but in the UNIX world, it has become the standard. By default, MySQL supports three engines: ISAM, MyISAM, and HEAP. The other two types, InnoDB and Berkley (BDB), are also often available.

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 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 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 functions of 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 need to run the OPTIMIZE TABLE command frequently to recover the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMChk tool to repair database files and the MyISAMPack tool to recover wasted space.

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 providers and Internet platform providers (Internet Presence Provider,IPP) are only allowed to use the MyISAM format.

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. Let me repeat: don't forget to delete the form after you have finished using it.

InnoDB and Berkley DB

Both InnoDB and the Berkley DB (BDB) engine are direct products of the technology that makes MySQL flexible, which is MySQL++ API. Almost every challenge you face when using MySQL stems from the fact that the ISAM and MyISAM database engines do not support transactions or foreign keys. Although much slower than the ISAM and MyISAM engines, InnoDB and BDB include 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.

If you feel really skilled, you can also use MySQL++ to create your own database engine. This API provides you with the ability to manipulate fields, records, tables, databases, connections, secure accounts, and all the other myriad other features you need to set up a DBMS such as MySQL. An in-depth explanation of API is beyond the scope of this article, but it is important that you understand the existence of MySQL++ and the technology behind its interchangeable engines. It is estimated that the model of this plug-in database engine can even be used to create a native XML provider (XML provider) for MySQL. (any MySQL++ developer reading this article can take this as a requirement.)

Press the switch

The switch that makes all the flexibility possible is the MySQL extension provided to ANSI SQL-the TYPE parameter. MySQL allows you to specify the database engine at the table level, so they sometimes refer to table formats. The following sample code shows how to create tables that use the MyISAM, ISAM, and HEAP engines, respectively. Note that the code to create each table is the same, except for the final TYPE parameter, which is used to specify the data engine.

CREATE TABLE tblMyISAM (

Id INT NOT NULL AUTO_INCREMENT

PRIMARY KEY (id)

Value_a TINYINT

) TYPE=MyISAM

CREATE TABLE tblISAM (

Id INT NOT NULL AUTO_INCREMENT

PRIMARY KEY (id)

Value_a TINYINT

) TYPE=ISAM

CREATE TABLE tblHeap (

Id INT NOT NULL AUTO_INCREMENT

PRIMARY KEY (id)

Value_a TINYINT

) TYPE=Heap

You can also use the ALTER TABLE command to move the original table from one engine to another. The following code shows how to use ALTER TABLE to move the MyISAM table to the InnoDB engine:

ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB

MySQL takes three steps to achieve this. First, a copy of the table is created. Any changes to the input data are then queued and the copy is moved to another engine. Finally, any data changes that are queued are sent to the new table, and the original form is deleted.

--

ALTER TABLE shortcut

If you just want to update the table from ISAM to MyISAM, you can use the _ convert_table_format command instead of writing ALTER TABLE expressions.

--

You can use the SHOW TABLE command (another extension of the ANSI standard from MySQL) to determine which engine is managing a particular table. SHOW TABLE returns a result set with multiple columns that you can use to query for all types of information: the name of the database engine is in the Type field. The following sample code illustrates the use of SHOW TABLE:

SHOW TABLE STATUS FROM tblInnoDB

--

The replacement method of SHOW TABLE

You can use SHOW CREATE TABLE [TableName] to retrieve the information that SHOW TABLE can retrieve.

--

Finally, if you want to use an engine that is not compiled into MySQL or activated, it is useless, and MySQL will not prompt for this. It will only provide you with a table in the default format (MyISAM). In addition to using the default table format, there are ways to get MySQL to give an error, but for now, if you're not sure if a particular database engine is available, you need to use SHOW TABLE to check the table format.

More choice means better performance.

Given the extra complexity of recompiling and tracking engines for specific tables, why do you still want to use a non-default database engine? The answer is simple: adjust the database to meet your requirements.

To be sure, MyISAM is fast, but if your logical design requires transactions, you are free to use an engine that supports transactions. Furthermore, because MySQL allows you to apply the database engine at the table level, you can optimize performance only on tables that require transactions, while handing over tables that do not require transactions to the lighter MyISAM engine. For MySQL, flexibility is the key

This is the end of the content of "what is the engine of MySQL database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.