Mysql实例MySQL的查询缓存机制基本学习教程

MYSQL应用
SELECT * FROM tbl_name 
Select * from tbl_name

MYSQL应用
mysql> SHOW VARIABLES LIKE '%query_cache%'; 

MYSQL应用
+------------------------------+---------+ 
| Variable_name        | Value  | 
+------------------------------+---------+ 
| have_query_cache       | YES   | --查询缓存是否可用 
| query_cache_limit      | 1048576 | --可缓存具体查询结果的最大值 
| query_cache_min_res_unit   | 4096  |  
| query_cache_size       | 599040 | --查询缓存的大小 
| query_cache_type       | ON   | --阻止或是支持查询缓存 
| query_cache_wlock_invalidate | OFF   |  
+------------------------------+---------+ 

MYSQL应用
[mysql@csdba1850 ~]$ mysql -u root -p 

MYSQL应用
Enter password:  
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 3 
Server version: 5.0.45-community MySQL Community Edition (GPL) 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 

MYSQL应用
mysql> set global query_cache_size = 600000; --设置缓存内存 

MYSQL应用
Query OK, 0 rows affected (0.00 sec) 

MYSQL应用
mysql> set session query_cache_type = ON; --开启查询缓存 

MYSQL应用
Query OK, 0 rows affected (0.00 sec) 

MYSQL应用
 
mysql> use test 

MYSQL应用
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Database changed 

MYSQL应用
mysql> show tables; 

MYSQL应用
+----------------+ 
| Tables_in_test | 
+----------------+ 
| animals    |  
| person     |  
+----------------+ 
5 rows in set (0.00 sec) 
 
mysql> select count(*) from animals; 
+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 
 
--Qcache_hits表示sql查询在缓存中命中的累计次数,是累加值. 

MYSQL应用
mysql> SHOW STATUS LIKE 'Qcache_hits'; 

MYSQL应用
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 0   | --0次 
+---------------+-------+ 
8 rows in set (0.00 sec) 
 
mysql> select count(*) from animals; 
+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 

MYSQL应用
mysql> SHOW STATUS LIKE 'Qcache%'; 

MYSQL应用
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 1   | --表示sql在缓存中直接得到结果,不需要再去解析 
+---------------+-------+ 
8 rows in set (0.00 sec) 

MYSQL应用
mysql> select count(*) from animals; 

MYSQL应用
+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 

MYSQL应用
mysql> select count(*) from animals; 

MYSQL应用
+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 

MYSQL应用
mysql> SHOW STATUS LIKE 'Qcache_hits'; 

MYSQL应用
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 3   |  --上面的sql也是是从缓存中直接取到结果 
+---------------+-------+ 
1 row in set (0.00 sec) 

MYSQL应用
 
mysql> insert into animals select 9,'testsds' ; --插入数据后,跟这个表所有相关的sql缓存就会被清空掉 

MYSQL应用
Query OK, 1 row affected (0.00 sec) 
Records: 1 Duplicates: 0 Warnings: 0 

MYSQL应用
 
mysql> select count(*) from animals; 

MYSQL应用
+----------+ 
| count(*) | 
+----------+ 
|    7 |  
+----------+ 
1 row in set (0.00 sec) 

MYSQL应用
 
mysql> SHOW STATUS LIKE 'Qcache_hits'; 

MYSQL应用
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 3  | --还是等于3,说明上一条sql是没有直接从缓存中直接得到的 
+---------------+-------+ 
1 row in set (0.00 sec) 

MYSQL应用
mysql> select count(*) from animals; 

MYSQL应用
+----------+ 
| count(*) | 
+----------+ 
|    7 |  
+----------+ 
1 row in set (0.00 sec) 

MYSQL应用
mysql> SHOW STATUS LIKE 'Qcache_hits';  

MYSQL应用
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 4   |  
+---------------+-------+ 
1 row in set (0.00 sec)