Heim >Datenbank >MySQL-Tutorial >MySQL的查询缓存机制基本学习教程_MySQL

MySQL的查询缓存机制基本学习教程_MySQL

WBOY
WBOYOriginal
2016-05-27 13:45:431202Durchsuche

MySQL缓存机制简单的说就是缓存sql文本及查询结果,如果运行相同的sql,服务器直接从缓存中取到结果,而不需要再去解析和执行sql。如果表更改 了,那么使用这个表的所有缓冲查询将不再有效,查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变,包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等,也包括那些映射到改变了的表的使用MERGE表的查询。显然,这对于频繁更新的表,查询缓存是不适合的,而对于一些不常改变数据且有 大量相同sql查询的表,查询缓存会节约很大的性能。

命中条件

缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key.在判断是否命中前,MySQL不会解析SQL,而是直接使用SQL去查询缓存,SQL任何字符上的不同,如空格,注释,都会导致缓存不命中.

如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的

 

工作流程

1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)

2. 如果找到了缓存,则直接返回缓存(性能提升)

3. 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等.

4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)


缓存失效

当某个表正在写入数据,则这个表的缓存(命中检查,缓存写入等)将会处于失效状态.在Innodb中,如果某个事务修改了表,则这个表的缓存在事务提交前都会处于失效状态,在这个事务提交前,这个表的相关查询都无法被缓存.

 

缓存的内存管理

缓存会在内存中开辟一块内存(query_cache_size)来维护缓存数据,其中有大概40K的空间是用来维护缓存的元数据的,例如空间内存,数据表和查询结果的映射,SQL和查询结果的映射等.

MySQL将这个大内存块分为小的内存块(query_cache_min_res_unit),每个小块中存储自身的类型,大小和查询结果数据,还有指向前后内存块的指针.

MySQL需要设置单个小存储块的大小,在SQL查询开始(还未得到结果)时就去申请一块空间,所以即使你的缓存数据没有达到这个大小,也需要用这个大小的数据块去存(这点跟Linux文件系统的Block一样).如果结果超出这个内存块的大小,则需要再去申请一个内存块.当查询完成发现申请的内存块有富余,则会将富余的空间释放掉,这就会造成内存碎片问题,见下图

2015112795908326.png (1061×575)

此处查询1和查询2之间的空白部分就是内存碎片,这部分空闲内存是有查询1查询完以后释放的,假设这个空间大小小于MySQL设定的内存块大小,则无法再被使用,造成碎片问题

在查询开始时申请分配内存Block需要锁住整个空闲内存区,所以分配内存块是非常消耗资源的.注意这里所说的分配内存是在MySQL初始化时就开辟的那块内存上分配的.

 

缓存的使用时机

衡量打开缓存是否对系统有性能提升是一个很难的话题

1. 通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache_hits) / 查询次数 (Com_select)

2. 通过缓存写入率, 写入率 = 缓存写入次数 (Qcache_inserts) / 查询次数 (Qcache_inserts)

3. 通过 命中-写入率 判断, 比率 = 命中次数 (Qcache_hits) / 写入次数 (Qcache_inserts), 高性能MySQL中称之为比较能反映性能提升的指数,一般来说达到3:1则算是查询缓存有效,而最好能够达到10:1

 

缓存配置参数

1. query_cache_type: 是否打开缓存

可选项

1) OFF: 关闭

2) ON: 总是打开

3) DEMAND: 只有明确写了SQL_CACHE的查询才会吸入缓存

 

2. query_cache_size: 缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)

 

3. query_cache_min_res_unit: 分配内存块时的最小单位大小

 

4. query_cache_limit: MySQL能够缓存的最大结果,如果超出,则增加 Qcache_not_cached的值,并删除查询结果

 

5. query_cache_wlock_invalidate: 如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回

 

GLOBAL STAUS 中 关于 缓存的参数解释:

Qcache_free_blocks: 缓存池中空闲块的个数

Qcache_free_memory: 缓存中空闲内存量

Qcache_hits: 缓存命中次数

Qcache_inserts: 缓存写入次数

Qcache_lowmen_prunes: 因内存不足删除缓存次数

Qcache_not_cached: 查询未被缓存次数,例如查询结果超出缓存块大小,查询中包含可变函数等

Qcache_queries_in_cache: 当前缓存中缓存的SQL数量

Qcache_total_blocks: 缓存总block数

 

减少碎片策略

1. 选择合适的block大小

2. 使用 FLUSH QUERY CACHE 命令整理碎片.这个命令在整理缓存期间,会导致其他连接无法使用查询缓存

PS: 清空缓存的命令式 RESET QUERY CACHE

 

查询缓存问题分析

20151127100053913.png (778×680)

InnoDB与查询缓存

Innodb会对每个表设置一个事务计数器,里面存储当前最大的事务ID.当一个事务提交时,InnoDB会使用MVCC中系统事务ID最大的事务ID跟新当前表的计数器.

只有比这个最大ID大的事务能使用查询缓存,其他比这个ID小的事务则不能使用查询缓存.

另外,在InnoDB中,所有有加锁操作的事务都不使用任何查询缓存

查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外,同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。

下面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查询缓存机制例子:

[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) 

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn