Mysql query cache and varchar field type

MySQL server has an important feature since MySQL 4.0.1: Query Cache. In use, the query cache stores
SELECT
The query text corresponds to the result sent to the client. If the same query is received later, the server will retrieve the result from the query cache, instead of re-analyzing and executing the same query.

Note: No expired data is returned in the query cache. After the data is modified, any related entries in the query cache are cleared.

When some tables do not change frequently and you execute a large number of identical queries on them, the query cache is very useful. This is a typical situation where many WEB servers use a large amount of dynamic information.

The following is a cached performance data query. (These results are generated by executing the MySQL benchmark suite and the following on a Linux Alpha 2x500 MHz, 2 gb ram, and 64 MB query cache ):

* If all the queries you perform are simple (for example, selecting a row from a table), but they are still different, the query cannot be buffered. When the query cache is active, the overhead is 13%. This can be seen as the worst case. However, in actual situations, queries are much more complex than our simple examples, so the overhead is usually significantly lower.

* If you search for a row in only one record table, the search speed is 238% faster. This can be considered to be close to the minimum acceleration expected for a buffered query.

* If you want to disable the query cache, set query_cache_size to 0. If the query cache is disabled, there will be no significant overhead. (With the help of the configuration option -- without-query-cache, the query cache can be excluded from the external code)

6.9.1 how the query cache works

The query is compared before analysis.

SELECT * FROM tbl_name

And

Select * from tbl_name

The query cache is treated as a different query, so the query must be strictly consistent (byte to byte) to be considered the same. In addition, if a client uses a new connection protocol format or another character set different from other clients, a query is considered different.

Queries using different database versions, protocol versions, or different default strings are considered to be different queries and buffered separately.

The high-speed buffer does not apply to queries of the SELECT CALC_ROWS... and SELECT FOUND_ROWS ()... types, because the number of rows found is also stored in the buffer.

If the query result is returned from the query cache, the state variable Com_select will not be added, but Qcache_hits will increase. View section 6.9.4 query the cache status and maintenance.

If a TABLE changes (INSERT, UPDATE, DELETE, TRUNCATE, ALTER, or drop table | DATABASE), then all the buffered queries used by this TABLE (possibly through a MRG_MyISAM TABLE !) Will be invalidated and removed from the buffer.

The changes made by the InnoDB table's transactions will invalidate the data when a COMMIT is completed.

If a query includes the following functions, it cannot be buffered:

Function Functions

User-Defined Functions CONNECTION_ID FOUND_ROWS

GET_LOCK RELEASE_LOCK LOAD_FILE

MASTER_POS_WAIT NOW SYSDATE

CURRENT_TIMESTAMP CURDATE CURRENT_DATE

CURTIME CURRENT_TIME DATABASE

ENCRYPT (only one parameter is called) LAST_INSERT_ID RAND

UNIX_TIMESTAMP (called without parameters) USER BENCHMARK

　
If a query contains user variables, reference the MySQL System database, or IN one of the following formats, SELECT... in share mode, SELECT
... Into outfile..., SELECT... into dumpfile... or SELECT * FROM
AUTOINCREMENT_FIELD is null (the last insert ID-ODBC statement IS retrieved), and the query cannot be cached.

However, found rows () returns the correct value even if the previous query was read from the cache.

If a query does not use any table, or uses a temporary table, or you have a column permission on any related table, the query will not be cached.

Before a query is read from the query cache, MySQL checks that the user has the SELECT permission on all related databases and tables. If this is not the case, the cached results cannot be used.

6.9.2 query cache settings

The query cache adds several MySQL System variables to mysqld, which can be set in the configuration file or on the command line when mysqld is started.

* Query_cache_limit does not cache results greater than this value. (1 MB by default)

　
* The query_cache_min_res_unit variable was introduced from 4.1. Query Result (data sent to the client)
The results are stored in the query Cache during retrieval. Therefore, data is not processed in a large segment. The query cache allocates blocks as needed to process this data. Therefore, when a block is filled, a new block is divided
Configuration. Memory Allocation is very expensive. The query cache uses the smallest size query_cache_min_res_unit
Allocate blocks. When a query is completed, the final result block is trimmed to the size of the actual data so that unused memory is released.

The default value of o query_cache_min_res_unit is 4 KB, which is sufficient in most cases.

　
O if you have many queries that return a smaller result, the default block size may cause memory fragmentation.
(Displayed as a large number of idle blocks (Qcache_free_blocks). This will cause the query cache to have to be out of memory.
(Qcache_lowmem_prunes) and delete the query from the cache ). In this case, you should reduce query_cache_min_res_unit.

O if your primary query returns a large result set (view Qcache_total_blocks and Qcache_queries_in_cache), you can increase the performance by adding query_cache_min_res_unit. However, be careful not to set it too large.

* Query_cache_size The amount of memory allocated to store the old query results (specified in bytes ). If it is set to 0, the query buffer is disabled (the default value is 0 ).

* Query_cache_type can be set to (only numbers are allowed)

Option description

0 (OFF, no cache or re-obtain results)

1 (ON, cache all results, except SELECT SQL _NO_CACHE... query)

2 (DEMAND, only cache SELECT SQL _CACHE... query)

In a thread (connection), the query cache behavior can be changed. Syntax:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

Option description

0 or OFF do not cache or get results again

1 or ON Cache all results except SELECT SQL _NO_CACHE... Query

2 or DEMAND only caches SELECT SQL _CACHE... Query

6.9.3 query cache options in SELECT

Two possible query cache-related parameters can be specified in a SELECT query:

Option description

SQL _CACHE: If QUERY_CACHE_TYPE is DEMAND, this query can be cached. If QUERY_CACHE_TYPE is ON, this is the default value. If QUERY_CACHE_TYPE is OFF, it does not do anything.

SQL _NO_CACHE prevents this query from being cached and cannot be stored in the cache.

6.9.4 query the cache status and Maintenance

Using the flush query cache command, you can sort out the query cache to make better use of its memory. This command does not remove any queries from the cache. Flush tables clears the query cache.

The mission of reset query cache is to remove all QUERY results from the query cache.

You can check whether the query cache is introduced in your MySQL:

Mysql> show variables like 'have _ query_cache ';

+ ------------------ + ------- +

| Variable_name | Value |

+ ------------------ + ------- +

| Have_query_cache | YES |

+ ------------------ + ------- +

1 row in set (0.00 sec)

In show status, you can monitor the query cache performance:

Variable meaning

Qcache_queries_in_cache number of queries registered in the cache

Qcache_inserts number of queries added to the cache

Qcache_hits cache sample count

Qcache_lowmem_prunes number of queries deleted from the cache due to lack of memory

Qcache_not_cached does not have the number of queries cached (cannot be cached, or because of QUERY_CACHE_TYPE)

Qcache_free_memory

Qcache_free_blocks query the number of idle memory blocks in the cache

Qcache_total_blocks

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

The query cache uses longer blocks. Therefore, Qcache_total_blocks and Qcache_free_blocks may display the queried cache fragments. After flush query cache, there is only one separate (large) idle block.

Note: Each query requires at least two blocks (one for storing query text and the other for storing query results ). Similarly, each table used by a query requires a block. However, if two or more queries use the same table, you only need to allocate one block.

　
You can use the status variable Qcache_lowmem_prunes.
To coordinate the query cache size. It counts the queries that are removed from the cache. The query is removed to release the memory and cache the new queries. The query cache uses a least recently
Used (LRU) policy to determine which query is removed from the cache.

If mysql defines the varchar (64) type and the character set used on the mysql server is utf8, the table also uses UFT8 and can only store 64 characters (both Chinese and English ), however, if the character set is changed to latin1, only 32 Chinese characters and 64 English characters can be added!