MySQL Performance Optimization _ MySQL

MySQL Performance Optimization MySQL supports five different table types, which may be unusual. These five types are BDB, HEAP, ISAM, MERGE, and MyISAM. Among them, the BDB type belongs to one category separately, which is called "transaction-safe", and the other table types belong to the second category, which is called "non-transaction-safe ). The following describes the table types in detail.

Transaction Security type

▲Bdb

The full name of BDB is "BerkeleyDB", which is a table type with transaction capabilities in MySQL, developed by SleepycatSoftware. The BDB table type provides the functions that MySQL users can expect for a long time, that is, the transaction control capability. In any RDBMS, transaction control is an extremely important and valuable function. The transaction control capability enables us to ensure that all commands have been successfully executed or that the execution results of all commands are rolled back when any command has an error. As you can imagine, the transaction control capability is extremely important in e-bank applications.

Non-transaction security type

▲Heap

HEAP tables are the MySQL tables with the fastest data access speed, because these tables use hashed indexes stored in the memory. Note that if MySQL or server crashes, data in the HEAP table will be lost!

▲Isam

The ISAM table type is the default MySQL table type before MyISAM appears. Therefore, this table type is not recommended. we recommend that you use MyISAM tables instead.

▲Merge

MERGE is a new type of table that deserves attention and is provided in version 3.23.25. The MERGE table is actually composed of a group of identical MyISAM tables. The reason why we need to merge multiple identical tables into one is mainly for the sake of performance, because it can improve the search speed, improve the repair efficiency, and save disk space.

The current MERGE table type still belongs to the BETA version, but I believe the official version will soon appear.

▲Myisam

MyISAM table type is the default MySQL table type. MyISAM table types are based on ISAM, but many useful extensions are added. Here are some reasons for replacing the ISAM table type with the MyISAM table type:

MyISAM tables are smaller than ISAM tables, so they consume less resources.

MyISAM tables can be transplanted in binary format between different platforms.

MyISAM has many other advantages. Visit http://www.mysql.com/doc/ I /s/isam.html?about the description of the table.

The table type is specified when the table is created. In the following example, a HEAP table is created:

Mysql> CREATETABLEemail_addressesTYPE = HEAP (

-> Emailchar (55) NOTNULL,

-> Namechar (30) NOTNULL,

-> PRIMARYKEY (email ));

Creating a BDB table requires more configuration parameters. for details, refer to tables.

MySQL4.0 adds two new table types, Innobase and Gemeni. There is not much information available for the two types of tables.

There is too much to be learned about the MySQL table type. This article will not be complete or detailed. We recommend that you visit the MySQL documentation for more detailed information.

Correct compilation methods are important, but they are only part of improving MySQL server performance. Many MySQL server parameters will affect the performance of the server, and we can save these parameters to the configuration file so that these parameters will automatically take effect each time the MySQL server is started. This configuration file is my. cnf.

MySQL server provides my. several examples of the cnf file can be found under the/usr/local/mysql/share/mysql/directory named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, respectively. The scale description in the file name describes the applicable system types of the configuration file. For example, if the system memory that runs the MySQL server is small and MySQL is used only occasionally, the my-small.cnf configuration file is ideal, which tells mysqlddaemon to use the least amount of system resources. Otherwise, if the MySQL server is used to support a large-scale online store, the system has 2 GB of memory, then the most appropriate use of mysql-huge.cnf.