/ 2019-08-12
(1)简介:
查询缓存简称QC,使用查询缓冲,mysql将查询结果存放在缓冲区中,今后对于同样的select语句(区分大小写),将直接从缓冲区中读取结果。
SQL层:
select * from t1 where name=:NAME;
select * from t1 where name=:NAME;
1、查询完结果之后,会对SQL语句进行hash运算,得出hash值,我们把他称之为SQL_ID
2、会将存储引擎返回的结果+SQL_ID存储到缓存中。
存储方式:
例子:select * from t1 where id=10; 100次
1、将select * from t1 where id=10; 进行hash运算计算出一串hash值,我们把它称之为“SQL_ID"
2、将存储引擎返回上来的表的内容+SQLID存储到查询缓存中
使用方式:
1、一条SQL执行时,进行hash运算,得出SQLID,去找query cache
2、如果cache中有,则直接返回数据行,如果没有,就走原有的SQL执行流程
一个sql查询如果以select开头,那么mysql服务器将尝试对其使用查询缓存。
注:两个sql语句,只要想差哪怕是一个字符(列如大小写不一样;多一个空格等),那么这两个sql将使用不同的一个cache。
(2)判断依据
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2002 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
---------------------状态说明--------------------
Qcache_free_blocks:缓存中相邻内存块的个数。
如果该值显示较大,则说明Query Cache 中的内存碎片较多了,FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。
注:当一个表被更新之后,和它相关的cache
blocks将被free。但是这个block依然可能存在队列中,除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大,缓存效果越理想。
Qcache_inserts:表示多少次未命中然后插入,意思是新来的SQL请求在缓存中未找到,不得不执行查询处理,执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多,表示查询缓存应用到的比较少,效果也就不理想。当然系统刚启动后,查询缓存是空的,这很正常。
Qcache_lowmem_prunes:
多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的free_blocks和free_memory可以告诉您属于哪种情况)
Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache:当前Query Cache 中cache 的Query 数量;
Qcache_total_blocks:当前Query Cache 中的block 数量;。
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
90/ 10000 0 90
如果出现hits比例过低,其实就可以关闭查询缓存了。使用redis专门缓存数据库
Qcache_free_blocks 来判断碎片
Qcache_free_memory + Qcache_lowmem_prunes 来判断内存够不够
Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
(3)配置示例
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 | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
mysql>
-------------------配置说明-------------------------------
以上信息可以看出query_cache_type为off表示不缓存任何查询
各字段的解释:
query_cache_limit:超过此大小的查询将不缓存
query_cache_min_res_unit:缓存块的最小大小,query_cache_min_res_unit的配置是一柄”双刃剑”,默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费。
query_cache_size:查询缓存大小 (注:QC存储的最小单位是1024byte,所以如果你设定了一个不是1024的倍数的值,这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
query_cache_type:缓存类型,决定缓存什么样的查询,注意这个值不能随便设置,必须设置为数字,可选项目以及说明如下:
如果设置为0,那么可以说,你的缓存根本就没有用,相当于禁用了。
如果设置为1,将会缓存所有的结果,除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2,则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
修改/etc/my.cnf,配置完后的部分文件如下:
query_cache_size=128M
query_cache_type=1
max_connect_errors是一个mysql中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况,当超过指定次数,mysql服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件,在[mysqld]下面添加如下内容
max_connect_errors=2000
(1)简介:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速
ORDER BY
GROUP BY
distinct
union
(2)配置依据
Sort_Buffer_Size并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。
列如:500个连接将会消耗500*sort_buffer_size(2M)=1G内存
(3)配置方法
修改/etc/my.cnf文件,在[mysqld]下面添加如下:
sort_buffer_size=1M
(1)简介:
mysql根据配置文件会限制,server接受的数据包大小。
(2)配置依据:
有时候大的插入和更新会受max_allowed_packet参数限制,导致写入或者更新失败,更大值是1GB,必须设置1024的倍数
(3)配置方法:
max_allowed_packet=32M
select a.name,b.name from a join b on a.id=b.id where xxxx
用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。
尽量在SQL与方面进行优化,效果较为明显。
优化的方法:在on条件列加索引,至少应当是有MUL索引
(2)