MySQL 查询缓存的实际应用代码示例_MySQL

MySQL查询

以下的文章主要介绍的是MySQL 查询缓存的实际应用代码以及查看MySQL 查询缓存的大小 ，碎片整理，清除缓存以及监视MySQL 查询缓存性能的相关内容的描述，以下就是具体内容的描述，希望在你今后的学习中会有所帮助。

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> select @@query_cache_type;  </span></span>
</li>
<li><span>+--------------------+  </span></li>
<li class="alt"><span>| @@query_cache_type |  </span></li>
<li><span>+--------------------+  </span></li>
<li class="alt"><span>| ON |   </span></li>
<li><span>+--------------------+  </span></li>
<li class="alt">MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> set </span><span class="attribute"><font color="#ff0000">query_cache_type</font></span><span>=</span><span class="attribute-value"><font color="#0000ff">off</font></span><span>;  </span>
</li>
<li>MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> set </span><span class="attribute"><font color="#ff0000">query_cache_type</font></span><span>=</span><span class="attribute-value"><font color="#0000ff">on</font></span><span>;  </span>
</li>
<li class="alt">MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span>   </span>
</li>
<li>MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> select sql_cache id, title, body from article;  </span>
</li>
<li class="alt">MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> select sql_no_cache id, title, body from article;  </span>
</li>
<li>MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> show variables like 'have_query_cache';  </span>
</li>
<li class="alt"><span>+------------------+-------+  </span></li>
<li><span>| Variable_name | Value |  </span></li>
<li class="alt"><span>+------------------+-------+  </span></li>
<li><span>| have_query_cache | YES |   </span></li>
<li class="alt"><span>+------------------+-------+  </span></li>
<li><span>1 row in set (0.00 sec)  </span></li>
</ol>

 

查看MySQL 查询缓存的大小

 

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> select @@global.query_cache_size;  </span></span>
</li>
<li><span>+---------------------------+  </span></li>
<li class="alt"><span>| @@global.query_cache_size |  </span></li>
<li><span>+---------------------------+  </span></li>
<li class="alt"><span>| 16777216 |   </span></li>
<li><span>+---------------------------+  </span></li>
<li class="alt"><span>1 row in set (0.00 sec)  </span></li>
<li>MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> select @@query_cache_size;  </span>
</li>
<li class="alt"><span>+--------------------+  </span></li>
<li><span>| @@query_cache_size |  </span></li>
<li class="alt"><span>+--------------------+  </span></li>
<li><span>| 16777216 |   </span></li>
<li class="alt"><span>+--------------------+  </span></li>
<li><span>1 row in set (0.00 sec)   </span></li>
</ol>

 

查看最大缓存结果，如果结果集大于该数，不缓存。

 

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> select @@global.query_cache_limit;  </span></span>
</li>
<li><span>+----------------------------+  </span></li>
<li class="alt"><span>| @@global.query_cache_limit |  </span></li>
<li><span>+----------------------------+  </span></li>
<li class="alt"><span>| 1048576 |   </span></li>
<li><span>+----------------------------+  </span></li>
<li class="alt"><span>1 row in set (0.00 sec) </span></li>
</ol>

 

 

碎片整理

 

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> flush query cache  </span></span>
</li>
<li>
<span>-</span><span class="tag"><strong><font color="#006699">></font></strong></span><span> ;  </span>
</li>
<li class="alt"><span>Query OK, 0 rows affected (0.00 sec)  </span></li>
</ol>

 

清除缓存

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> reset query cache  </span></span>
</li>
<li>
<span>-</span><span class="tag"><strong><font color="#006699">></font></strong></span><span> ;  </span>
</li>
<li class="alt"><span>Query OK, 0 rows affected (0.00 sec) </span></li>
</ol>

 

监视MySQL 查询缓存性能：

 

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> flush tables;  </span></span>
</li>
<li><span>Query OK, 0 rows affected (0.04 sec)  </span></li>
<li class="alt">MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> show status like 'qcache%';  </span>
</li>
<li><span>+-------------------------+----------+  </span></li>
<li class="alt"><span>| Variable_name | Value |  </span></li>
<li><span>+-------------------------+----------+  </span></li>
<li class="alt"><span>| Qcache_free_blocks | 1 |   </span></li>
<li><span>| Qcache_free_memory | 16768408 |   </span></li>
<li class="alt"><span>| Qcache_hits | 6 |   </span></li>
<li><span>| Qcache_inserts | 36 |   </span></li>
<li class="alt"><span>| Qcache_lowmem_prunes | 0 |   </span></li>
<li><span>| Qcache_not_cached | 86 |   </span></li>
<li class="alt"><span>| Qcache_queries_in_cache | 0 |   </span></li>
<li><span>| Qcache_total_blocks | 1 |   </span></li>
<li class="alt"><span>+-------------------------+----------+  </span></li>
<li><span>8 rows in set (0.06 sec)  </span></li>
</ol>

 

看看当前缓存中有多少条信息：

 

<ol class="dp-xml">
<li class="alt">MySQL<span><span class="tag"><strong><font color="#006699">></font></strong></span><span> show status like 'qcache_q%';  </span></span>
</li>
<li><span>+-------------------------+-------+  </span></li>
<li class="alt"><span>| Variable_name | Value |  </span></li>
<li><span>+-------------------------+-------+  </span></li>
<li class="alt"><span>| Qcache_queries_in_cache | 0 |   </span></li>
<li><span>+-------------------------+-------+  </span></li>
<li class="alt"><span>1 row in set (0.00 sec)  </span></li>
<li>MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> select sql_cache id, title, body from article;  </span>
</li>
<li class="alt">MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> show status like 'qcache_q%';  </span>
</li>
<li><span>+-------------------------+-------+  </span></li>
<li class="alt"><span>| Variable_name | Value |  </span></li>
<li><span>+-------------------------+-------+  </span></li>
<li class="alt"><span>| Qcache_queries_in_cache | 1 |   </span></li>
<li><span>+-------------------------+-------+  </span></li>
<li class="alt"><span>1 row in set (0.00 sec)  </span></li>
<li>MySQL<span class="tag"><strong><font color="#006699">></font></strong></span><span> show status like 'qcache_f%';  </span>
</li>
<li class="alt"><span>+--------------------+----------+  </span></li>
<li><span>| Variable_name | Value |  </span></li>
<li class="alt"><span>+--------------------+----------+  </span></li>
<li><span>| Qcache_free_blocks | 1 |   </span></li>
<li class="alt"><span>| Qcache_free_memory | 16766728 |   </span></li>
<li><span>+--------------------+----------+  </span></li>
<li class="alt"><span>2 rows in set (0.00 sec)  </span></li>
</ol>

 

 

以上的相关内容就是对MySQL 查询缓存的介绍，望你能有所收获。