一、MySQL查询缓存优化

在MySQL8.0之前我们的数据库是存在缓存这样的情况的，因为存在缓存，sql怎么执行都是很快，当然第⼀次其实不快但是我没注意到，以⾄于上线后因为缓存经常失效，导致rt（Response time）时高时低。

后来就发现了是缓存的问题，我们在执⾏SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。

我说一下为什么缓存会失效，而且是经常失效。

如果我们当前的MySQL版本支持缓存并且我们也开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，大家也看到我们下方的结构图了，每个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。

大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。下面我们就来了解一下再8.0版本下如何优化SQL查询缓存。

1、概述

开启MySQL的查询缓存,当执行完全相同的SQL语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存。

2、操作流程

一条SQL语句的执行流程（下面的流程是在MySQL开启了查询缓存下的情景）：

（1）MySQL客户端和MySQL服务器建立连接，客户端发来一条SQL语句；

（2）MySQL服务器收到SQL语句之后，首先去查询缓存中寻找有没有目标数据，如果命中就直接返回，否则把SQL语句叫给SQL解析器解析；

（3）SQL解析器把SQL语句按照MySQL的语法规范解析成一个语法树，然后交给预处理器进一步检查最终生成一颗解析树。

（4）查询优化器拿到解析树后生成执行计划化。通常一条查询语句可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

（5）完成语法解析和查询优化之后，查询执行引擎会将根据执行计划到存储引擎中获取数据。获取到数据后返回给客户端，并在查询缓存中缓存一份同样的数据。

3、查询缓存配置

（1）查看当前的MySQL数据库是否支持查询缓存:

SHOW VARIABLES LIKE 'have_query_cache';

（2）查看当前MySQL是否开启了查询缓存:

SHOW VARIABLES LIKE 'query_cache_type';

（3）查看查询缓存的大小

SHOW VARIABLES LIKE 'query_cache_size';

（4）查询缓存的状态变量

show status like 'Qcache%';

4、开启查询缓存

MvSOL的查询缓存默认是关闭的,需要手动配置参数aquerv cache tvpe，来开启查询缓存、auerv cache tvpe该参数的可取值有三个:

在/usr/my.cnf配置中,增加以下配置:

开启mysq1的查询缓存
query_cache_type= 1

配置完毕之后,重启服务既可生效;

然后就可以在命令行执行SQL语句进行验证, 执行条比较耗时的SQL语句 ,然后再多执行几次,查看后面几次的执行时间;获取通过查看查询缓存的缓存命中数,来判定是否走查询缓存。

5、查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项:

SQL CACHE:如果查询结果是可缓存的,并且query_cache_type 系统变量的值为ON或DEMAND ,则缓存查询结果。 SQL NO_CACHE:服务器不使用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果。 例子:

SELECT SQL_ CACHE id, name FROM customer ;
SELECT SQL_ NO_ CACHE id, name FROM customer ;

6、查询缓存失效的情况

（1） SQL语句不一 致的情况，要想命中查询缓存,查询的SQL语句必须-致。

SQL1 : select count(*) from tb_item;
SQL2 : Select count(*) from tb_item

（2）当查询语句中有一些不确定的时 , 则不会缓存。如: now) , current date(), curdate() , curtime(), rand() , uuid() , user() , database()。

SQL1 : select * from tb_item where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select databaseO;

（3）不使用任何表查询语句。

select 1+2;
select 'Hello';

（4）查询mysql，information_schema或performance_schema数据库中的表时,不会走查询缓存。

select * from information_schema.engines;

（5）在存储的函数,触发器或事件的主体内执行的查询。

（6）如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句,如被改变INSERT，UPDATE ，DELETE ，TRUNCATE TABLE, ALTER TABLE, DROP TABLE ,或DROP DATABASE

二、Mysql内存管理及优化

1、内存优化原则

（1）将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够内存。

（2）MyISAM存储弓|擎的数据文件读取依赖于操作系统自身的I0缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做1I0缓存。

（3）排序区、连接区等缓存是分配给每个数据库会话( session )专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽。

2、 MyISAM内存优化

MyISAM存储引擎使用key_buffer缓存索引块,加速MyISAM索弓|的读写速度。对于MyISAM表的数据块, mysql没有特别的缓存机制,完全依赖于操作系统的I0缓存。

key. buffer. size

key_buffer_size决定MylSAM索引块缓存区的大小,直接影响到MyISAM表的存取效率。可以在MySQL参 数文件中设置key_ buffer_size的值,对于一般MyISAM数据库 ,建议至少将1/4可用内存分配给key_buffer_size。

在/usr/my.cnf中做如下配置:

key_buffer_size=512M

read_buffer_size

如果需要经常顺序扫描myisam表,可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大,就会造成内存浪费。

read_rnd_buffer_size

对于需要做排序的myisam表的查询,如带有order by子句的sql,适当增加read_rnd_buffer_size的值,可以改善此类的sql性能。但需要注意的是read_rnd_buffer_size 每个session独占的,如果默认值设置太大,就会造成内存浪费。

3、InnoDB内存优化

innodb用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。

innodb_buffer_ pool_size

该变量决定了innodb存储引擎表数据和索弓|数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size 的值越大,缓存命中率越高,访问InnoDB表需要的磁盘I/O就越少,性能也就高。 1 innodb_ buffer_ pool_size=512M

innodb_buffer_poo1_size=512M

innodb. _log _buffer. size

决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事務,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

innodb_1og_buffer_size=10M

三、Mysql并发参数调整

从实现上来说, MySQL Server是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在Mysql中,控制并发连接和线程的主要参数包括max connections、back log、thread. cache size、table _open cahce。

1、 max_connections

采用max_connections控制允许连接到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors. max connections不为零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这是可以考虑增大max_connections的值。

Mysql最大可支持的连接数,取决于很多因素,包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度,期望的响应时间等。在Linux 平台下,性能好的服务器,支持500-1000个连接不是难事,需要根据服务器性能进行评估设定。

2、 back_log

back_log参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时 ,新来的请求将会被存在堆栈中,以等待某一连接释放资源 ,该堆栈的数量即back_log ,如果等待连接的数量超过back_log ,将不被授予连接资源,将会报错。5.6.6版本之前默认值为50，之后的版本默认为50+ ( max_connections/5)，但最大不超过900。

如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back. log的值。

3、 table_open_cache

该参数用来控制所有SQL语句执行线程可打开表缓存的数量,而在执行SQL语句时,每-一个SQL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定:

max_connections x N

4、 thread_cache_size

为了加快连接数据库的速度, MySQL会缓存一定数量的客户服务线程以备重用 ,通过参数thread_cache_size 可控制MySQL缓存客户服务线程的数量。

5、 innodb_lock_wait_timeout

该参数是用来设置InnoDB事务等待行锁的时间,默认值是50ms，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小,以避免事务长时间挂起;对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。