Quick Guide to Implementing the MySQL database engine

The following article describes how to implement the MySQL database engine quickly. If you are a racing driver, you just need to press the button to change the MySQL database engine immediately. How do you feel? What MySQL (the best combination with PHP) databases do for developers is like changing the engine by clicking a button;

It allows you to select the database engine and give you a simple way to switch it.

The built-in MySQL database engine of MySQL (the best combination with PHP) must be enough, but in some cases, other engines may be more suitable for completing tasks than they are used at hand. If you want to, you can even use MySQL (the best combination with PHP) + + API to create your own database engine, just like hitting the cylinder and installing your own carburetor. Now let's take a look at how you choose an engine and how to switch between available engines.

Select your engine

The database engine you can use depends on how MySQL (the best combination with PHP) is compiled during installation. To add a new MySQL database engine, you must re-compile MySQL (the best combination with PHP ). The concept of compiling an application just to add a feature may be strange for Windows developers, but it has become a standard in the UNIX world.

By default, MySQL (the best combination with PHP) supports three engines: ISAM, MyISAM, and HEAP. The other two types are InnoDB and BerkleyBDB.

ISAM

ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance:

If your hard disk crashes, the data file cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data and use its copy feature to MySQL (the best combination with PHP) supports such backup applications.

MyISAM

MyISAM is the ISAM extension format of MySQL (the best combination with PHP) and the default MySQL database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations. The cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism.

MyISAM also has some useful extensions, such as the MyISAMChk tool used to fix database files and the MyISAMPack tool used to restore wasted space.

MyISAM emphasizes fast read operations, which may be the main reason why MySQL (the best combination with PHP) is so favored by Web development: in Web development, a large amount of data operations are read operations. Therefore, most VM providers and Internet platform providers Internet Presence Provider, IPP) only allow the use of MyISAM format.

HEAP

HEAP allows only temporary tables in memory. HEAP is faster than ISAM and MyISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space.

HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up. Let me repeat it again: do not forget to delete the table after you have used up the table.

InnoDB and Berkley DB

InnoDB and Berkley DBBDB) database engines are direct products that make MySQL (the best combination with PHP) flexible technology. This technology is the best combination of MySQL (and PHP) ++ API. When using MySQL (the best combination with PHP), almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys.

Although it is much slower than ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.

If you feel that you are indeed highly skilled, you can also use MySQL (the best combination with PHP) ++ to create your own MySQL database engine. This API provides you with the functions of operation fields, records, tables, databases, connections, and security accounts, as well as the creation of best combinations such as MySQL (and PHP) in this way, DBMS requires numerous other functions.

An in-depth explanation of the API is beyond the scope of this article, but you need to understand the existence of MySQL (the best combination with PHP) ++ and the technology behind the interchangeable engine, this is important. It is estimated that this plug-in database engine model can even be used to create local xml for MySQL (the best combination with PHP) (standardization is getting closer and closer) provider ). Any MySQL (the best combination with PHP) ++ developer who reads this article can regard this as a requirement .)

Press Switch

The toggle that makes all flexibility possible is the extension-TYPE parameter provided to MySQL (the best combination with PHP) of ansi SQL. MySQL (the best combination with PHP) allows you to specify the database engine at the table layer, so they sometimes refer to table formats. The following sample code shows how to create tables using the MyISAM, ISAM, and HEAP engines.

Note that the code for creating 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,
  
  
Prima (the most complete virtual host Management System) ry key (id ),
  
  
Value_a TINYINT
  
  
) TYPE = MyISAM
  
  
Create table tblISAM (
  
  
Id int not null AUTO_INCREMENT,
  
  
Prima (the most complete virtual host Management System) ry key (id ),
  
  
Value_a TINYINT
  
  
) TYPE = ISAM
  
  
Create table tblHeap (
  
  
Id int not null AUTO_INCREMENT,
  
  
Prima (the most complete virtual host Management System) ry 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 MySQL database engine. The following code shows how to use alter table to move a MyISAM TABLE to the InnoDB engine:

 
 

  
  
ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB  
 
 

MySQL (the best combination with PHP) achieves this goal in three steps. First, a copy of the table is created. Then, any changes to input data are queued, and the copy is moved to another engine. Finally, any changes made to data in the queue are sent to a new table, and the original table is deleted.

Alter table shortcuts

To update a TABLE from ISAM to MyISAM, you can use the _ convert_table_format command of MySQL (the best combination with PHP) without writing the alter table expression.

You can use the show table command, which is another extension of the ANSI standard in MySQL (the best combination with PHP) to determine which engine is managing specific tables. Show table returns a result set with multiple data columns. You can use this result set to query all types of information: the database engine name is in the Type field. The following sample code illustrates how to use show table:

 
 

  
  
SHOW TABLE STATUS FROM tblInnoDB  
 
 

Replace SHOW TABLE

You can use show create table [TableName] to retrieve the information that can be retrieved by show table.

Finally, if you want to use an engine that is not compiled into MySQL (the best combination with PHP) and is not activated, it is useless. MySQL (the best combination with PHP) this is not prompted. However, it will only provide you with a table in the default format of MyISAM. In addition to using the default table format, MySQL (the best combination with PHP) can also be used to give an error message, but for now, if you are not sure whether a specific database engine is available, you need to use show table to check the TABLE format.

More options mean better performance

Engines used for specific tables need to be re-compiled and tracked. Considering this extra complexity, why do you still want to use non-default MySQL database engines? The answer is simple: you need to adjust the database to meet your requirements.

Certainly, MyISAM is indeed fast, but if your logic design requires transaction processing, you can freely use the engine that supports transaction processing. Further, because MySQL (the best combination with PHP) allows you to apply the database engine at the table layer, you can only optimize the performance of tables that require transaction processing, instead, the tables that do not need to be processed are handed over to the MyISAM engine, which is lighter. For MySQL (the best combination with PHP), flexibility is the key.