MySql cache query principle and cache monitoring and index monitoring introduction

Query Cache

1. Query Cache Operation Principle

Before executing a query statement, MySQL compares the query statement with the statements in the query cache, and compares them byte by byte. Only those that are completely consistent are considered identical. As follows, these two statements are considered different queries.

SELECT * FROM tb1_name

Select * from tb1_name

1) Queries for different databases, different protocol versions, or different character sets are treated as different queries and cached separately.

2) The following two types of queries are not cached

a. Prepared statements

b. Subquery of nested query

3) Before fetching query results from the query cache, MySQL checks whether the user has query permissions for all databases and tables involved in the query. If not, the cached query results are not used.

4) If a query result is returned from the cache query, the server increments the Qcache_hits status variable instead of Com_select

5) If the table changes, all cached queries that use that table become invalid and are removed from the cache. Tables may be altered by several types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

Reference Links:

http://dev.mysql.com/doc/refman/4.1/en/query-cache-operation.html

2. Check whether cache query is enabled

SHOW VARIABLES LIKE 'have_query_cache';

MySql cache query principle and cache monitoring and index monitoring

3. Remove all query caches from the query cache

RESET QUERY CACHE;

4. Query cache performance monitoring

SHOW STATUS LIKE 'Qcache%'

MySql cache query principle and cache monitoring and index monitoring

Output description:

Qcache_free_blocks: free memory blocks in the query cache

Qcache_free_memory: The amount of free memory in the query cache

Qcache_hits: number of query cache hits

Qcache_inserts: The number of queries added to the query cache (not reads that were not cached, but reads that were invalidated)

Qcache_lowmen_prunes: The number of queries that were removed from the query cache because of low memory.

Qcache_not_chached: The number of uncached queries (not cached because of the query_cache_type setting)

Qcache_queries_in_cache: The number of queries registered in the cache query

Qcache_total_blocks: The total number of memory blocks in the query cache

Total SELECT queries:

Com_select+Qcache_hits+ Number of queries with errors found by parser

Among them, Com_select represents the number of cache misses, and Qcache_hits represents the number of cache hits.

Com_select calculation formula:

Qcache_inserts+Qcache_not_cached+number of permissions check errors (queries with errors found during the column-privileges check)

Index Monitoring

SHOW STATUS LIKE 'handler_read%';

MySql cache query principle and cache monitoring and index monitoring

Output description:

Handler_read_first

The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed

The number of times the first entry in an index is read. If this value is high, it means that the server is doing a lot of full index scans, such as SELECT col1 FROM foo, assuming that col1 is indexed.

Handler_read_key

The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

The number of requests to read a row based on a key. If this value is high, it is a good indication that the table is indexed appropriately for the requests being executed.

Handler_read_next

The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.

The number of requests to read the next row, according to key order. If you are querying an index column with a set of constraints or performing an index scan, this value will increase.

Handler_read_prev

The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC

The number of times to request to read the previous row, in order of keys. This read method is mainly used to optimize ORDER BY ... DESC

Handler_read_rnd

The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.

The number of requests to read a row at a fixed position. A high value indicates that many queries are being executed that require sorting of the result sets. You may be executing many queries that require full table scans, or joins that do not use the appropriate keys.

Handler_read_rnd_next

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

The number of requests to read the next row in the data file. A high value indicates that many full table scans are being performed. This usually indicates that the table is not using appropriate indexes or that the query request does not take advantage of existing indexes.

Reference Links:

http://dev.mysql.com/doc/refman/5.7/en/dynindex-statvar.html#statvar-index-H

Reference Links:

http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html

http://dev.mysql.com/doc/refman/4.1/en/query-cache-status-and-maintenance.html

This is the end of this article about the principles of MySql cache query and the introduction to cache monitoring and index monitoring. For more relevant MySql cache query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!