MySQL's own engine must be enough, but in some cases other engines might be better suited to the task than the one at hand. If you want, you can even use the mysql++ API to create your own database engine, like a cylinder with your own carburetor. Now let's see how you can choose the engine and how to switch between the available engines.
choose your engine.
The database engine you can use depends on how MySQL was compiled at the time of installation. To add a new engine, you must recompile MySQL. The idea of compiling an application just to add a feature may seem strange to Windows developers, but in the Unix world this has become a standard. By default, MySQL supports three engines: ISAM, MyISAM, and heap. The other two types of InnoDB and Berkley (BDB) are also often available.
ISAM
ISAM is a well-defined and time-tested method of data table management, designed to take into account that the number of times a database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two major drawbacks of ISAM are that it does not support transactions or fault tolerance: If your hard drive crashes, the data files cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and MySQL can support such a backup application by replicating the features.
MyISAM
MyISAM is the ISAM extended format and default database engine for MySQL. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The price is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism. MyISAM also has some useful extensions, such as MYISAMCHK tools for repairing database files and Myisampack tools to restore wasted space.
MyISAM highlights the fast read operation, which may be the main reason why MySQL is so favored by Web development: The large amount of data operations you do in Web development are read operations. As a result, most virtual host providers and internet Platform providers (Internet Presence Provider,ipp) only allow the use of MyISAM format.
HEAP
Heap allows temporary tables that reside only in memory. resides in memory to make heap faster than ISAM and MyISAM, but the data it manages is unstable, and if not saved before shutdown, all data will be lost. When data rows are deleted, heap does not waste a lot of space. Heap tables are useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using it. Let me repeat: After you finish using the form, don't forget to delete the form.
InnoDB and Berkley DB
The InnoDB and Berkley DB (BDB) database engines are direct products that create MySQL-flexible technologies, the mysql++ API. When using MySQL, almost every challenge you face comes from ISAM and MyISAM the database engine does not support transaction processing or foreign key support. Although it is much slower than the ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, which are not in the top two engines. 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 you are really skilled, you can use mysql++ to create your own database engine. This API provides you with the ability to manipulate fields, records, tables, databases, connections, security accounts, and all the myriad other features needed to build a DBMS such as MySQL. Delving into the API is beyond the scope of this article, but it is important to understand the mysql++ and the technology behind the interchangeable engine. Estimating the plug-in database engine's model can even be used to create a local XML provider (XML provider) for MySQL. (Any mysql++ developer who reads this article can see this as a requirement.) )
The switch that makes all the flexibility possible is the MySQL extended--type parameter provided to ANSI SQL. MySQL allows you to specify the database engine at this level of the table, so they sometimes refer to table formats. The following example code shows how to create a table that uses the MyISAM, ISAM, and heap engines separately. Note that the code to create each table is the same, except for the last type parameter, which specifies the data engine.
Copy Code code as follows:
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 existing form from one engine to another. The following code shows how to use ALTER TABLE to move the MyISAM table to the InnoDB engine:
Copy Code code as follows:
ALTER TABLE Tblmyisam Change Type=innodb
MySQL uses three steps to achieve this. First, a copy of this table is created. Any changes to the input data are then queued, and the replica is moved to another engine. Finally, any changes to the queued data are sent to the new table, and the original table is deleted.
ALTER Table Shortcuts
If you just want to update the table from ISAM to MyISAM, you can use the Mysql_convert_table_format command without having to write ALTER TABLE expressions.
You can use the Show Table command (which is another extension of MySQL's ANSI standard) to determine which engine is managing a particular table. Show Table returns a result set with multiple data 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 example code illustrates the use of Show table:
Copy Code code as follows:
Show TABLE STATUS from Tblinnodb
How to replace Show Table
You can use show CREATE table [TableName] to retrieve information that the show table can retrieve. Finally, if you want to use an engine that has not been compiled into MySQL and is not activated, it is no use, and MySQL will not prompt this. And it will only give you a table with a default format (MyISAM). In addition to using the default table format, there is a way to get MySQL to give you the wrong hints, but for now, if you are not sure whether a particular database engine is available, you should use Show table to check the table format.
More choices means better performance
The engine for a particular table needs to be recompiled and tracked, so why do you want to use a Non-default database engine, given the extra complexity? The answer is simple: adjust the database to meet your requirements.
To be sure, MyISAM is really fast, but if your logic design requires transactions, you are free to use the engine that supports transaction processing. Further, since MySQL allows you to apply the database engine at the table level, you can optimize performance only for tables that require transactions, and give tables that do not require transactions to a lighter MyISAM engine. For MySQL, flexibility is the key.
