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

How to choose the MySQL database engine? for this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

How to choose the MySQL database engine

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 this be 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 OPTIMIZETABLE command frequently to restore the space wasted by the updated 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 web hosting providers and Internet platform providers (InternetPresenceProvider,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 BerkleyDB

Both InnoDB and the BerkleyDB (BDrolexB) database 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 (XMLprovider) for MySQL. (any MySQL++ developer who reads this article can take this as a requirement.)

Press the switch

The switch that makes all the flexibility possible is the MySQL extension TYPE parameter provided to ANSISQL. MySQL allows you to specify the database engine at the table level, so they sometimes refer to tableformats. 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.

CREATETABLEtblMyISAM (

IdINTNOTNULLAUTO_INCREMENT

PRIMARYKEY (id)

Value_aTINYINT

) TYPE=MyISAM

CREATETABLEtblISAM (

IdINTNOTNULLAUTO_INCREMENT

PRIMARYKEY (id)

Value_aTINYINT

) TYPE=ISAM

CREATETABLEtblHeap (

IdINTNOTNULLAUTO_INCREMENT

PRIMARYKEY (id)

Value_aTINYINT

) TYPE=Heap

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

How to choose the MySQL database engine

ALTERTABLEtblMyISAMCHANGETYPE=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.

The first step, ALTERTABLE shortcut

If you just want to update the table from ISAM to MyISAM, you can use the mysql_convert_table_format command instead of writing ALTERTABLE expressions.

The second step

You can use the SHOWTABLE command (another extension of the ANSI standard from MySQL) to determine which engine is managing a particular table. SHOWTABLE 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 SHOWTABLE:

SHOWTABLESTATUSFROMtblInnoDB

The third step, the replacement method of SHOWTABLE

You can use SHOWCREATETABLE [TableName] to retrieve the information that SHOWTABLE 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 SHOWTABLE 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 key.

The answer to the question about how to choose the MySQL database engine is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.