In-depth parsing of MySQL query Cache Mechanism

Source: Internet
Author: User

The MySQL query cache mechanism is one of the important mechanisms in the MySQL database. The following provides an in-depth analysis of the MySQL query cache mechanism for your reference.

The MySQL cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly obtains the results from the cache without parsing and executing the SQL statement. If the table is changed, all buffered queries using this table will no longer be valid, and the items related to the query cache value will be cleared. Changes refer to any data or structure changes in the TABLE, including INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database, this also includes queries for tables mapped to changed tables that use MERGE tables. Obviously, the query cache is not suitable for frequently updated tables. For tables that do not often change data and have a large number of identical SQL queries, the query cache will save a lot of performance.

The query must be exactly the same (in bytes) 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.

The following SQL query cache is considered different:

 
 
  1. SELECT * FROM tbl_name  
  2. Select * from tbl_name  

Query cache Parameters

 
 
  1. Mysql> show variables like '% query_cache % ';
  2. + ------------------------------ + --------- +
  3. | Variable_name | Value |
  4. + ------------------------------ + --------- +
  5. | Have_query_cache | YES | -- query whether the cache is available
  6. | Query_cache_limit | 1048576 | -- maximum value of the specific query result that can be cached
  7. | Query_cache_min_res_unit | 4096 |
  8. | Query_cache_size | 599040 | -- query the cache size.
  9. | Query_cache_type | ON | -- blocks or supports query Cache
  10. | Query_cache_wlock_invalidate | OFF |
  11. + ------------------------------ + --------- +

The following is an example of a simple MySQL query cache mechanism:

 
 
  1. [Mysql @ csdba1850 ~] $ Mysql-u root-p
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with; or \ g.
  4. Your MySQL connection id is 3
  5. Server version: 5.0.45-community MySQL Community Edition (GPL)
  6.  
  7. Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
  8.  
  9. Mysql> set global query_cache_size = 600000; -- sets the cache memory.
  10. Query OK, 0 rows affected (0.00 sec)
  11.  
  12. Mysql> set session query_cache_type = ON; -- enable query Cache
  13. Query OK, 0 rows affected (0.00 sec)
  14.  
  15. Mysql> use test
  16. Reading table information for completion of table and column names
  17. You can turn off this feature to get a quicker startup with-
  18.  
  19. Database changed
  20. Mysql> show tables;
  21. + ---------------- +
  22. | Tables_in_test |
  23. + ---------------- +
  24. | Animals |
  25. | Person |
  26. + ---------------- +
  27. 5 rows in set (0.00 sec)
  28.  
  29. Mysql> select count (*) from animals;
  30. + ---------- +
  31. | Count (*) |
  32. + ---------- +
  33. | 6 |
  34. + ---------- +
  35. 1 row in set (0.00 sec)
  36.  
  37. -- Qcache_hits indicates the cumulative number of times that the SQL query hits in the cache, which is the cumulative value.
  38. Mysql> show status like 'qcache _ hits ';
  39. + --------------- + ------- +
  40. | Variable_name | Value |
  41. + --------------- + ------- +
  42. | Qcache_hits | 0 | -- 0 times
  43. + --------------- + ------- +
  44. 8 rows in set (0.00 sec)
  45.  
  46. Mysql> select count (*) from animals;
  47. + ---------- +
  48. | Count (*) |
  49. + ---------- +
  50. | 6 |
  51. + ---------- +
  52. 1 row in set (0.00 sec)
  53.  
  54. Mysql> show status like 'qcache % ';
  55. + --------------- + ------- +
  56. | Variable_name | Value |
  57. + --------------- + ------- +
  58. | Qcache_hits | 1 | -- indicates that the SQL statement is directly obtained in the cache and does not need to be parsed.
  59. + --------------- + ------- +
  60. 8 rows in set (0.00 sec)
  61.  
  62. Mysql> select count (*) from animals;
  63. + ---------- +
  64. | Count (*) |
  65. + ---------- +
  66. | 6 |
  67. + ---------- +
  68. 1 row in set (0.00 sec)
  69.  
  70. Mysql> select count (*) from animals;
  71. + ---------- +
  72. | Count (*) |
  73. + ---------- +
  74. | 6 |
  75. + ---------- +
  76. 1 row in set (0.00 sec)
  77.  
  78. Mysql> show status like 'qcache _ hits ';
  79. + --------------- + ------- +
  80. | Variable_name | Value |
  81. + --------------- + ------- +
  82. | Qcache_hits | 3 | -- the preceding SQL statement is obtained directly from the cache.
  83. + --------------- + ------- +
  84. 1 row in set (0.00 sec)
  85.  
  86. Mysql> insert into animals select 9, 'testsds '; -- after data is inserted, all SQL caches related to the table will be cleared.
  87. Query OK, 1 row affected (0.00 sec)
  88. Records: 1 Duplicates: 0 Warnings: 0
  89.  
  90. Mysql> select count (*) from animals;
  91. + ---------- +
  92. | Count (*) |
  93. + ---------- +
  94. | 7 |
  95. + ---------- +
  96. 1 row in set (0.00 sec)
  97.  
  98. Mysql> show status like 'qcache _ hits ';
  99. + --------------- + ------- +
  100. | Variable_name | Value |
  101. + --------------- + ------- +
  102. | Qcache_hits | 3 | -- or equal to 3, indicating that the previous SQL statement was not directly obtained from the cache.
  103. + --------------- + ------- +
  104. 1 row in set (0.00 sec)
  105.  
  106. Mysql> select count (*) from animals;
  107. + ---------- +
  108. | Count (*) |
  109. + ---------- +
  110. | 7 |
  111. + ---------- +
  112. 1 row in set (0.00 sec)
  113.  
  114. Mysql> show status like 'qcache _ hits ';
  115. + --------------- + ------- +
  116. | Variable_name | Value |
  117. + --------------- + ------- +
  118. | Qcache_hits | 4 |
  119. + --------------- + ------- +
  120. 1 row in set (0.00 sec)

Implementation of MySQL non-repeated Query

Five common MySQL command lines

Two methods to fix mysql tables

How to insert an array into a mysql table in php

Usage of MySQL conditional query statements

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.