MySQL Optimization-application optimization: MySQL query High-Speed Buffer

MySQL query High-Speed Buffer

The query cache stores the SELECT query text and the corresponding results sent to the client. If the same query is received subsequently, the server returns the query result from the query cache, instead of parsing and executing the query. If there is a table that does not change frequently and the server receives a large number of identical queries for the table, the query cache is very useful in such an application environment.

This is a typical case for many Web servers. It generates a large number of dynamic pages based on the database content. The query cache does not return old data. After the table is changed, items related to the query cache value are cleared. If many mysqld servers update the same MyISAM table, the query cache does not work in this case.

Query cache is not applicable to statements written by the server. If you are using the statements written by the server, consider that these statements will not apply the query cache.

When the server starts, you must disable the query cache and set query_cache_size to 0. After the query cache code is disabled, there is no significant speed improvement. When compiling MySQL, you can use the -- without-query-cache option in configure to completely remove the query cache capability from the server.

Query how high-speed buffering works

The query is compared before parsing, so the two queries below are considered different by the query cache: SELECT * FROM tbl_name Select * from tbl_name query must be identical (same by byte) to be considered the same.

In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

Before extracting a query from the query cache, MySQL checks the user's SELECT permission on all relevant databases and tables. If you do not have the permission, do not use the cache results. If a query result is returned from the query cache, the server adds the value of the Qcache_hits status variable to one, instead of the Com_select variable.

If a table is changed, all buffered queries using that table will no longer be valid and will be removed from the buffer. This includes queries for tables mapped to changed tables that use MERGE tables. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database. After the COMMIT is executed, the modified transaction InnoDB table is no longer valid. When an InnoDB table is used, the query cache also works in the transaction. The version number of the table is used to check whether its content is still the current one.

View-generated queries are cached. SELECT SQL _CALC_FOUND_ROWS... and SELECT FOUND_ROWS () type queries use the query cache. The FOUND_ROWS () returns the correct value even if the number of rows created is saved in the buffer. The previous query is extracted from the cache.

If a query contains any of the following functions, it will not be cached: BENCHMARK () CONNECTION_ID () CURDATE () CURRENT_DATE () CURRENT_TIME () CURRENT_TIMESTAMP () CURTIME () DATABASE () ENCRYPT () FOUND_ROWS () GET_LOCK () LAST_INSERT_ID () LOAD_FILE () MASTER_POS_WAIT () NOW () RAND () RELEASE_LOCK () SYSDATE () with a parameter () UNIX_TIMESTAMP () USER () without Parameters ()

Under the following conditions, the query will not be cached: Reference UDFs ). Reference custom variables. Reference tables in the mysql System database.

Any of the following methods: SELECT... in share mode select... for update select... into outfile... SELECT... into dumpfile... SELECT * FROM... WHERE autoincrement_col is null the last method cannot be cached because it IS used as the ODBC workspace to obtain the recently inserted ID value.

Query high-speed buffer configurations

Use the system VARIABLES of the have_query_cache server to indicate whether the cache is available: mysql> show variables like 'have _ query_cache '; + ---------------- + ------- + | Variable_name | Value | have_query_cache | YES |
+ ------------------ + ------- + Even if the query cache is disabled, this value is always YES when the standard MySQL binary is used.

Several other system variables control the query cache operation. When mysqld is started, these variables can be set in the option file or command line. All query cache system variables start with query_cache.

To set the query cache size, set the query_cache_size system variable. If it is set to 0, the query cache is disabled. The default cache size is 0, that is, the query cache is disabled. When the query_cache_size variable is set to a non-zero value, remember that the query cache requires at least 40 kb to allocate its data structure. (The specific size depends on the system structure) mysql> set global query_cache_size = 1000000; Query OK, 0 rows affected, 1 warning (0.00 sec)

If the query cache size is set to greater than 0, the query_cache_type variable affects the working mode. This variable can be set to the following values: 0 or OFF, which will block the cache or query the cache result. 1 or ON. The cache will be allowed, except for the query statements starting with SELECT SQL _NO_CACHE. 2 or DEMAND. Only cache is enabled for query statements starting with SELECT SQL _CACHE.

Query high-speed buffer status and Maintenance

You can use the flush query cache statement to clear query cache fragments to improve memory usage. This statement does not remove any queries from the cache. The reset query cache statement removes all queries from the query cache. The flush tables statement also performs the same job.

To monitor query cache performance, use show status to View cache STATUS variables: