MySQL Query optimization-Admin (1) _ MySQL

When you perform administrator optimization, remember the following rules: MySQL optimizes MySQL queries.

When you perform administrator optimization, remember the following rules:

· Accessing data in the memory is faster than accessing data on the disk.

· Try to save data in the memory to reduce disk operations.

· Retaining the index information is more important than retaining the data record content.

We will discuss how to apply these rules later.

Increase the server cache size. The server has many parameters (system variables). you can change these parameters to affect server operations. Several parameters directly affect the query processing speed. The most important parameter you can change is the data table cache size and the cache size used by the storage engine to buffer index operation information. If you have available memory, allocate it to the server cache to allow information to be stored in the memory and reduce disk operations. This will have a good effect, because the access to information in the memory is much faster than reading information from the disk.

· When the server opens table files, it tries to keep these files open to reduce the number of file opening operations. To implement this function, it maintains the information of opened files in the table cache. The table_cache system variable controls the cache size. If the server accesses a large number of tables, the table cache will be filled up, and the server will shut down those tables that have not been used for a period of time to open the new table. You can access the table cache by checking the Opened_tables status indicator:

Show status like 'opened _ tables ';

Opened_tables displays the number of times a data table must be opened (because it has not been opened ). This value is also displayed as the Opens value in the output information of the mysqladmin status command. If the number is stable or slow, the setting may be correct. If this number grows fast, it means that the cache is too small. you must close the data table frequently to open other data tables. If you have file description information, increasing the table cache size will reduce the number of open operations on the data table.

· MyISAM storage engine uses key Buffering to maintain index information blocks for index-related operations. Its size is controlled by the key_buffer_size system variable. The larger the value, MySQL will keep more index information blocks in the memory at a time, which can be added to the memory (instead of reading new information blocks from the disk) the possibility of finding the key value. The default size of the key cache is 8 MB. If you have a lot of memory, this is a very conservative value, you can directly increase its size, the performance of index-based retrieval, index creation, and modification operations is greatly improved.

In MySQL 4.1 or later versions, you can create an additional key cache for the MyISAM data table and specify tables to use them. This helps increase the query processing speed on these data tables.

· InnoDB and BDB engines have their own caches used to buffer data and index values. Their size is controlled by the innodb_buffer_pool_size and bdb_cache_size variables. The InnoDB engine also maintains a log buffer. The innodb_log_buffer_size variable can control its size.

· Another dedicated cache is query cache. we will explain it in the "use query cache" section.

When you change these parameter values, follow these principles:

· Change only one parameter at a time. If you change multiple independent variables at a time, it is difficult to evaluate the effect of each change.

· Gradually increase the value of system variables. According to theory, the larger the number, the better the performance, but if you make a variable too large, it may cause a lack of system resources, leading to reverse effects and speed reduction.

· Do not conduct parameter adjustment experiments on the server that runs the business MySQL database. it is best to create an independent test server.

· To get a general idea of which parameter variables might fit your system, you can view the my-small.cnf, my-medium.cnf, my-large included in the MySQL release documentation. cnf and my-huge.cnf option files (on Unix systems, you can always find these files in the shared directories of the source distribution files that support file directories and binary publishing files. On Windows, they are located in the basic installation directory, and their extension may be. ini ). These files may let you know that it is best to change the parameters on the server to adapt to different levels of use, and provide some typical values for these parameters.

Other policies used to improve server operation performance include:

Disable unnecessary storage engines. The server does not allocate any memory for the banned engine, so we can use this. If MySQL is created from the source file, most storage engines can be excluded from the server during configuration. For those engines that are included in the server, using appropriate startup options can disable most of them at runtime.
Maintain the simplicity of table authorization. Although the server caches the authorization table content in the memory, if you have some data rows in the tables_priv or columns_priv table, the server must check the table level and column level permissions for each query statement. If these tables are empty, the server can optimize its own permission check process and skip these levels.

If you create MySQL from the source file, configure it to use a static class library instead of a shared class library. Using dynamic binary files of shared class libraries saves disk space, but static binary files are faster. However, if you use a UDF mechanism, some systems require dynamic links. In such systems, static binary files cannot work.

Use the MyISAM key to cache

When MySQL executes a statement that uses the index of the MyISAM data table, it uses the key cache to maintain the index value. This cache reduces disk I/O: If you find the key value required by a data table in the cache, you do not need to read it from the disk again. Unfortunately, this type of key cache is limited, and by default, it is shared by all MyISAM data tables. If the key value is not found in the key cache and the key cache is full, competition will result in: some values in the cache must be discarded to leave space for the new value. If you need the discarded values next time, you must read them from the disk again.

If you rely heavily on the MyISAM data table, it will be good to save its key in the memory, but the contention in the cache will lead to the opposite effect. Reading data from the same or different tables may cause contention. You can set the key cache to save all the indexes of a specific data table to avoid contention for the same data table. However, Keys of other data tables still need to compete for the cache space.