[转]MySql查询缓存机制
本文转自：http://flatfish2000.iteye.com/blog/506525
缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表的使用MERGE表的查询。显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有大量相同sql查询的表，查询缓存会节约很大的性能。
查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。
下面sql查询缓存认为是不同的：
SELECT * FROM tbl_name
Select * from tbl_name
查询缓存相关参数
mysql> SHOW VARIABLES LIKE ’%query_cache%’;
+------------------------------+---------+
| 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@csdba1850 ~]$ mysql -u root -p
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> set global query_cache_size = 600000; --设置缓存内存
Query OK, 0 rows affected (0.00 sec)
mysql> set session query_cache_type = ON; --开启查询缓存
Query OK, 0 rows affected (0.00 sec)
mysql> use test
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> show tables;
+----------------+
| 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> SHOW STATUS LIKE ’Qcache_hits’;
+---------------+-------+
| 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> SHOW STATUS LIKE ’Qcache%’;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 1 | --表示sql在缓存中直接得到结果，不需要再去解析
+---------------+-------+
8 rows in set (0.00 sec)
mysql> select count(*) from animals;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from animals;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE ’Qcache_hits’;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 3 | --上面的sql也是是从缓存中直接取到结果
+---------------+-------+
1 row in set (0.00 sec)
mysql> insert into animals select 9,’testsds’ ; --插入数据后，跟这个表所有相关的sql缓存就会被清空掉
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select count(*) from animals;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE ’Qcache_hits’;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 3 | --还是等于3，说明上一条sql是没有直接从缓存中直接得到的
+---------------+-------+
1 row in set (0.00 sec)
mysql> select count(*) from animals;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE ’Qcache_hits’;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 4 |
+---------------+-------+
1 row in set (0.00 sec)
从 MySQL 4.0.1 开始，MySQL server 有一个重要的特征：Query Cache。 当在使用中，查询缓存会存储一个 SELECT 查询的文本与被传送到客户端的相应结果。如果之后接收到一个同样的查询，服务器将从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。
注意：查询缓存绝不返回过期数据。当数据被修改后，在查询缓存中的任何相关词条均被转储清除。
在某些表并不经常更改，而你又对它执行大量的相同查询时，查询缓存将是非常有用的。对于许多 WEB 服务器使用大量的动态信息，这是一个很典型的情况。
下面是查询缓存的一个性能数据。(这些结果的产生，是通过在一个 a Linux Alpha 2 x 500 MHz、2GB RAM 和 64MB 查询缓存上执行 MySQL 基准套件和到的)：
如果你执行的所有查询均是简单的(比如从表中一行一行的选取)；但是仍然是不同的，所以该查询不能被缓冲，查询缓存处于活动时，开销为 13%。这可以被看作是最差的情况。然而，在实际情况下，查询是比我们的简单示例要复杂得多的，所以开销通常显著得低。
在只有一行记录表中搜索一行后，搜索将快 238% 。这可以被认为是接近于对一个被缓冲的查询所期望的最小的加速。
如果你希望禁用查询缓存，设置 query_cache_size=0。禁用了查询缓存，将没有明显的开销。(在配置选项 --without-query-cache 的帮助下，查询缓存可以被排除在外码之外)
6.9.1 查询缓存如何运作
查询在分析之前先被比较，因而
SELECT * FROM tbl_name
和
Select * from tbl_name
对于查询缓存被当作是不同的查询，因而查询需要严格的一致(字节对字节的)，才会被认为是同样的。 另外，如果一个客户端使用一个新的连接协议格式或不同于其它客户端的另一个字符集，一个查询将被视为不同的。
使用不同数据库的，使用不同协议版本的，或使用不同的缺省字符串的查询将被认为是不同的查询，并将分别的缓冲。
高速缓冲不对 SELECT CALC_ROWS ... 和 SELECT FOUND_ROWS() ... 类型的查询起作用，因为找到的行的数目也是被存储在缓冲里的。
如果查询结果被从查询缓存中返回，那么状态变量 Com_select 将不会被增加，但是 Qcache_hits 却会增加。查看章节 6.9.4 查询缓存的状态和维护。
如果一个表发生的改变 (INSERT, UPDATE, DELETE, TRUNCATE, ALTER 或 DROP TABLE|DATABASE)，那么所有这张表使用的缓冲的查询(可能通过一个 MRG_MyISAM 表！)将被得失效，并从缓冲中移除。
InnoDB 表的事务所做的更改将在一个 COMMIT 被完成时，使数据失效。
如果一个查询包括下面的函数，它将不能被缓冲： 函数 函数 函数
User-Defined Functions CONNECTION_ID FOUND_ROWS
GET_LOCK RELEASE_LOCK LOAD_FILE
MASTER_POS_WAIT NOW SYSDATE
CURRENT_TIMESTAMP CURDATE CURRENT_DATE
CURTIME CURRENT_TIME DATABASE
ENCRYPT (只有一个参数调用) LAST_INSERT_ID RAND
UNIX_TIMESTAMP (无参数调用) USER BENCHMARK
如果一个查询包含用户变量，引用 MySQL 系统数据库，或下列之一的格式，SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... 或 SELECT * FROM AUTOINCREMENT_FIELD IS NULL (检索最后一个插入 ID - ODBC 语句)，该查询亦不可以被缓存。
然而，FOUND ROWS() 将返回正确的值，即使先前的查询是从缓存中读取的。
万一一个查询不使用任何表，或使用临时表，或用户对任何相关表有一个列权限，那么查询将不会被缓存。
在一个查询从查询缓存中读取前，MySQL 将检查用户对所有相关的数据库和表有 SELECT 权限。如果不是这种情况，缓存的结果将不能被使用。
6.9.2 查询缓存设置
查询缓存为了 mysqld 添加了几个 MySQL 系统变量，它可以在配置文件中被设置，或在启动 mysqld 时的命令行上设置。
query_cache_limit 不缓存大于这个值的结果。(缺省为 1M)
query_cache_min_res_unit 这个变量从 4.1 被引进。 查询的结果 (已被传送到客户端的数据) 在结果检索期间被存储到查询缓存中。因而，数据不会以一个大块地处理。查询缓存在需要时分配块用于处理这个数据，所以当一个块被填充后，一个新的块被分配。甚为内存分配操作是昂贵的，查询缓存以最小的尺寸 query_cache_min_res_unit 分配块。当一个查询执行完成，最后的结果块被修整到实际数据的尺寸大小，以便未使用的内存被释放。
query_cache_min_res_unit 的缺省值为 4 KB，在大多数据情况下已够用了。
如果你有许多查询返回一个较小的结果，缺省的块尺寸可能会引起内存碎片 (显示为一个很大数量的空闲块(Qcache_free_blocks)，这将引起查询缓存不得不因缺乏内存(Qcache_lowmem_prunes)而从缓存中删除查询)。在这种情况下，你应该减少 query_cache_min_res_unit。
如果你的主要查询返回的是大的结果集(查看 Qcache_total_blocks 和 Qcache_queries_in_cache)，你可以通过增加 query_cache_min_res_unit 来增加性能。然而，要小心不要将它设得太大。
query_cache_size 为了存储老的查询结果而分配的内存数量 (以字节指定) 。如果设置它为 0 ，查询缓冲将被禁止(缺省值为 0 )。
query_cache_type 这个可以被设置为 (只能是数字) 选项 含义
0 (OFF, 不缓存或重新得到结果)
1 (ON, 缓存所有的结果，除了 SELECT SQL_NO_CACHE ... 查询)
2 (DEMAND, 仅缓存 SELECT SQL_CACHE ... 查询)
在一个线程(连接)内，查询缓存的行为可以被改变。句法如下所示：
QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2
选项 含义
0 or OFF 不缓存或重新得到结果
1 or ON 缓存所有的结果，除了 SELECT SQL_NO_CACHE ... 查询
2 or DEMAND 仅缓存 SELECT SQL_CACHE ... 查询
6.9.3 在 SELECT 中的查询缓存选项
有两个可能的查询缓存相关的参数可以在一个 SELECT 查询中被指定：
选项 含义
SQL_CACHE 如果 QUERY_CACHE_TYPE 为 DEMAND，允许该查询被缓存。如果 QUERY_CACHE_TYPE 为 ON，这是缺省的。如果 QUERY_CACHE_TYPE 为 OFF，它不做任何事
SQL_NO_CACHE 使这个查询不被缓存，不允许这个查询被存储到高速缓存中
6.9.4 查询缓存的状态和维护
使用 FLUSH QUERY CACHE 命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。
RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。
你可以检查查询缓存在你的 MySQL 是否被引进：
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
在 SHOW STATUS 中，你可以监视查询缓存的性能：
变量 含义
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目
Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.
查询缓存使用变长的块，因而 Qcache_total_blocks 和 Qcache_free_blocks 可能显示查询缓存的碎片。在 FLUSH QUERY CACHE 之后，只有剩余一个单独的(大的)空闲块。
注意：每个查询最小需要两个块(一个用于存储查询文本，另一个或多个用于存储查询结果)。同样的，每个被一个查询使用的表需要一个块，但是，如果有两个或更多的查询使用同一张表，仅仅只需要分配一个块就行了。
你可以使用状态变量 Qcache_lowmem_prunes 来谐调查询缓存尺寸。它计数被从缓存中移除的查询，该查询的移除是为了释放内存，以缓存新建的查询。查询缓存使用一个 least recently used (LRU) 策略来判断从缓存中移除哪个查询。
posted on 2012-12-05 10:47 freeliver54 阅读(3856) 评论(0) 编辑 收藏 举报
