Optimization measure for the administrator of the MySQL Query optimization lecture _ MySQL

MySQL Query optimization lecture administrator's optimization measures the optimization measures described in the previous section are not executed by privileged MySQL users. The system administrator of the MySQL server or computer can perform additional optimization measures. For example, some server parameters are attached to the query processing process and can be adjusted, and some hardware configuration factors have a direct impact on the query processing speed. In many cases, these optimization measures improve the performance of the entire server, so it can benefit all MySQL users.

Generally, when you perform administrator optimization, remember the following rules:

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

? Store data in the memory as much as possible to reduce disk operations.

? Retaining the information in the index is more important than retaining the content of the data record.

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 a table file, 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.

? The 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.

? The 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, which is explained in the "use query cache" section.

When you change these parameter values, follow these principles:

? Only one parameter is changed 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 system variable value. 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.cnf, and my-huge.cnf option files included in the MySQL release documentation (on Unix systems, you can find these files in the directory that supports source distribution files and the shared directory of binary distribution 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.