MySQL Advanced three

One, MySQL cache:

1. The cache includes:

(1), cache query plan to avoid two lexical parsing, optimization, execution.

(2), the cached MySQL query statement returns the full result, when hit, MySQL will understand the return results, eliminating the parsing, optimization and execution steps.

2, how to hit the cache?

MySQL saves the results in the cache: The SELECT statement itself is hashed, the result of the calculation as a key, query results as value

3. What kind of statements will not be cached?

When there are some indeterminate data in a query statement, it is not cached: such as now (), Current_time ();

In general, if your query contains user-defined functions, stored functions, user variables, temporary tables,

The system tables or permission tables in the MySQL library are not normally cached.

4. What is the extra cost of caching?

Each query has to be checked for hits first.

The query results are cached first.

5. Cache-Related variables:

Mysql>show Global variables like ' query_cache% ';

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

| variable_name | Value |

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

| Query_cache_limit | 1048576 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 16777216 |

| Query_cache_type | On |

| Query_cache_wlock_invalidate | OFF |

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

Query_cache_type:

Whether to turn on the cache function, its value has three kinds of on| Off| DEMAND, where DEMAND if the cache needs to be displayed in the SELECT statement Sql_cache

Query_cache_size:

The total amount of cache space, in bytes, must be an integer multiple of 1024. If the size is changed, the current cache is emptied.

Query_cache_min_res_unit: The smallest memory block that stores the cache.

(query_cahce_size-qcache_free_memory)/qcache_queries_in_cache i.e. (total cache space-free cache space)/cache count

Query_cache_limit:

The maximum value of a single cached object, which is not cached.

You can manually use Sql_no_cache to artificially avoid attempts to cache statements that return query results that exceed the qualified value of this parameter.

Query_cache_wlock_invalidate: If a table is locked by another user connection, the result is still returned from the cache, and off is returned.

6. If the cache hit rate is determined:

(1), mysql>show Global status like ' qcache% ';

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

| variable_name | Value |

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

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 16757128 |

| Qcache_hits | 3 |

| Qcache_inserts | 2 |

| Qcache_lowmem_prunes | 0 |

| qcache_not_cached | 29 |

| Qcache_queries_in_cache | 2 |

| Qcache_total_blocks | 6 |

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

Qcache_hits: Number of hit caches.

Qcache_inserts: The number of times the cache was written.

Qcache_free_memory: Free cache space.

Qcache_total_blocks: Number of blocks that have been requested.

Qcache_free_blocks: Number of free blocks.

Qcache_queries_in_cache: Number of caches

Qcache_not_cached: Number Not Cached

Qcache_lowmem_prunes: The number of times the memory has been repaired (freeing the old cache) because the total cache space is too small.

(2), Hit rate indicator:

(2.1), frequency hit ratio: qcache_hits/(com_select+qcache_hits)

Mysql>show Global status where variable_name= ' qcache_hits ' or variable_name= ' com_select ';

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

| variable_name | Value |

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

| Com_select | 35 |

| Qcache_hits | 3 |

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

(2.2), another reference indicator for hit rate is: The ratio of hit and write, that is, the value of Qcache_hits/qcache_inserts,

This value, if greater than 3:1, indicates that the cache is also valid and can reach 10:1, which is ideal.

7. Defragmentation:

FLUSH Query_cache

8. Empty the cache:

RESET Query_cache

MySQL Advanced three