Teach you to speed up the MySQL database
- 2020-05-10 23:00:00
- OfStack
MySQL's own engine is certainly adequate, but in some cases, other engines may be better suited to the task than the one at hand. If you want, you can even use MySQL++ API to create your own database engine, just like you would a carburetor through a cylinder. Now let's look at how you choose the engine and how you can 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 concept of compiling an application just to add one feature may seem strange to developers of Windows, but in the Unix world, it has become the standard. By default, MySQL supports three engines: ISAM, MyISAM, and HEAP. Two other types, InnoDB and Berkley(BDB), are also commonly used.
ISAM
ISAM is a well-defined, time-tested approach to data table management that was designed with the database being queried far more often than updated. As a result, ISAM can perform read operations quickly and without consuming large amounts 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, your data files cannot be recovered. If you are using ISAM for mission-critical applications, you must always back up all of your real-time data. With its replication feature, MySQL can support such backup applications.
MyISAM
MyISAM is MySQL's ISAM extension format and the default database engine. In addition to providing a number of indexing and field management features not available in ISAM, MyISAM also USES a table locking mechanism to optimize multiple concurrent read and write operations. The cost is that you often need to run the OPTIMIZE TABLE command to recover the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMChk tool for fixing database files and the MyISAMPack tool for restoring wasted space.
MyISAM emphasizes quick read operations, which is probably the main reason why MySQL is so popular with Web development: most of the data operations you do in Web development are read operations. Therefore, most web hosting 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. Staying in memory makes HEAP faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before the shutdown, all the data is lost. HEAP also doesn't waste a lot of space when rows are deleted. The HEAP table is useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table when you're done with it. Let me repeat: after you finish using the form, don't forget to delete it.
InnoDB and Berkley DB
The InnoDB and Berkley DB(BDB) database engines are both direct products of the technology that makes MySQL flexible, which is MySQL++ API. Almost every challenge you face with MySQL stems from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although much slower than the ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, both of which were not available in the first two engines. As mentioned earlier, if your design requires either one of these features or both, you will be forced to use one of the latter two engines.
You can also use MySQL++ to create your own database engine if you feel like you are really skilled. This API gives you the ability to manipulate fields, records, tables, databases, connections, secure accounts, and all the myriad other functions you need to set up DBMS like MySQL. Going deep into API is beyond the scope of this article, but it is important that you understand the existence of MySQL++ and the technology behind its exchangeable engine. It is estimated that this plug-in database engine model could even be used to create a local XML provider for MySQL (XML provider). (any MySQL++ developer reading this article can take this 1 point as a requirement.)
The switch that makes all this flexibility possible is the MySQL extension provided to ANSI SQL -- the TYPE parameter. MySQL lets you specify the database engine at this level of the table, so they sometimes refer to table formats as well. The following sample code shows how to create a table using the MyISAM, ISAM, and HEAP engines, respectively. Note that the code to create each table is the same, except for the TYPE parameter at the end, which specifies the data engine.
You can also use the ALTER TABLE command to move the old table from one engine to another. The following code shows how to use ALTER TABLE to move the MyISAM form to InnoDB's engine:
MySQL does this in three steps. First, a copy of the table is created. Then, any changes to the input data are queued, and the copy is moved to another engine. Finally, any queued data changes are sent to the new table, and the original table is deleted.
ALTER TABLE 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 an ALTER TABLE expression.
You can use the SHOW TABLE command (which is another extension of MySQL to the ANSI standard) to determine which engine is managing a particular table. SHOW TABLE returns a result set with multiple data columns that you can 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:
Substitution method for SHOW TABLE
You can use SHOW CREATE TABLE [TableName] to fetch the information that SHOW TABLE can fetch. Finally, if you want to use an engine that has not been compiled to MySQL and has not been activated, it is useless. MySQL will not prompt this point. It will only give you one table in the default format (MyISAM). In addition to using the default table format, there are ways to get an error from MySQL, but for now, if you're not sure if a particular database engine is available, you can use SHOW TABLE to check the table format.
More choice means better performance
Given the extra complexity of recompiling and tracing engines for specific tables, why would you want to use a non-default database engine? The answer is simple: tweak the database to suit your needs.
To be sure, MyISAM is fast, but if your logical design requires transaction processing, you are free to use an engine that supports transaction processing. As a further step, since MySQL allows you to apply the database engine at the table tier 1, you can optimize the performance of only tables that require transaction processing, leaving tables that do not require transaction processing to the more portable MyISAM engine. For MySQL, flexibility is key.
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 concept of compiling an application just to add one feature may seem strange to developers of Windows, but in the Unix world, it has become the standard. By default, MySQL supports three engines: ISAM, MyISAM, and HEAP. Two other types, InnoDB and Berkley(BDB), are also commonly used.
ISAM
ISAM is a well-defined, time-tested approach to data table management that was designed with the database being queried far more often than updated. As a result, ISAM can perform read operations quickly and without consuming large amounts 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, your data files cannot be recovered. If you are using ISAM for mission-critical applications, you must always back up all of your real-time data. With its replication feature, MySQL can support such backup applications.
MyISAM
MyISAM is MySQL's ISAM extension format and the default database engine. In addition to providing a number of indexing and field management features not available in ISAM, MyISAM also USES a table locking mechanism to optimize multiple concurrent read and write operations. The cost is that you often need to run the OPTIMIZE TABLE command to recover the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMChk tool for fixing database files and the MyISAMPack tool for restoring wasted space.
MyISAM emphasizes quick read operations, which is probably the main reason why MySQL is so popular with Web development: most of the data operations you do in Web development are read operations. Therefore, most web hosting 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. Staying in memory makes HEAP faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before the shutdown, all the data is lost. HEAP also doesn't waste a lot of space when rows are deleted. The HEAP table is useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table when you're done with it. Let me repeat: after you finish using the form, don't forget to delete it.
InnoDB and Berkley DB
The InnoDB and Berkley DB(BDB) database engines are both direct products of the technology that makes MySQL flexible, which is MySQL++ API. Almost every challenge you face with MySQL stems from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although much slower than the ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, both of which were not available in the first two engines. As mentioned earlier, if your design requires either one of these features or both, you will be forced to use one of the latter two engines.
You can also use MySQL++ to create your own database engine if you feel like you are really skilled. This API gives you the ability to manipulate fields, records, tables, databases, connections, secure accounts, and all the myriad other functions you need to set up DBMS like MySQL. Going deep into API is beyond the scope of this article, but it is important that you understand the existence of MySQL++ and the technology behind its exchangeable engine. It is estimated that this plug-in database engine model could even be used to create a local XML provider for MySQL (XML provider). (any MySQL++ developer reading this article can take this 1 point as a requirement.)
The switch that makes all this flexibility possible is the MySQL extension provided to ANSI SQL -- the TYPE parameter. MySQL lets you specify the database engine at this level of the table, so they sometimes refer to table formats as well. The following sample code shows how to create a table using the MyISAM, ISAM, and HEAP engines, respectively. Note that the code to create each table is the same, except for the TYPE parameter at the end, which specifies 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 old table from one engine to another. The following code shows how to use ALTER TABLE to move the MyISAM form to InnoDB's engine:
ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB MySQL does this in three steps. First, a copy of the table is created. Then, any changes to the input data are queued, and the copy is moved to another engine. Finally, any queued data changes are sent to the new table, and the original table is deleted.
ALTER TABLE 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 an ALTER TABLE expression.
You can use the SHOW TABLE command (which is another extension of MySQL to the ANSI standard) to determine which engine is managing a particular table. SHOW TABLE returns a result set with multiple data columns that you can 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 Substitution method for SHOW TABLE
You can use SHOW CREATE TABLE [TableName] to fetch the information that SHOW TABLE can fetch. Finally, if you want to use an engine that has not been compiled to MySQL and has not been activated, it is useless. MySQL will not prompt this point. It will only give you one table in the default format (MyISAM). In addition to using the default table format, there are ways to get an error from MySQL, but for now, if you're not sure if a particular database engine is available, you can use SHOW TABLE to check the table format.
More choice means better performance
Given the extra complexity of recompiling and tracing engines for specific tables, why would you want to use a non-default database engine? The answer is simple: tweak the database to suit your needs.
To be sure, MyISAM is fast, but if your logical design requires transaction processing, you are free to use an engine that supports transaction processing. As a further step, since MySQL allows you to apply the database engine at the table tier 1, you can optimize the performance of only tables that require transaction processing, leaving tables that do not require transaction processing to the more portable MyISAM engine. For MySQL, flexibility is key.