老男孩MySQL DBA培训之优化(三)老男孩教育

    /    2019-08-12

query_cache_size ***

(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 ***

max_connect_errors是一个mysql中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况,当超过指定次数,mysql服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件,在[mysqld]下面添加如下内容
max_connect_errors=2000

sort_buffer_size ***

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

max_allowed_packet *****

(1)简介:
mysql根据配置文件会限制,server接受的数据包大小。
(2)配置依据:
有时候大的插入和更新会受max_allowed_packet参数限制,导致写入或者更新失败,更大值是1GB,必须设置1024的倍数
(3)配置方法:
max_allowed_packet=32M

join_buffer_size ***

select a.name,b.name from a join b on a.id=b.id where xxxx
用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。
尽量在SQL与方面进行优化,效果较为明显。
优化的方法:在on条件列加索引,至少应当是有MUL索引



(2)

分享至