MySQL高级—应用优化(参数优化)

一、MySQL查询缓存优化

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该参数的可取值有三个:

含义
OFF或0 查询缓存功能关闭
ON或1 查询缓存功能打开,SELECT的结果符合缓存条件即会缓存,否则,不予缓存,显式指定SQL NO_CACHE,不予缓存
DEMAND或2 查询缓存功能按需进行,显式指定SQL CACHE的SELECT语句才会缓存;其它均不予缓存_

在/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,可以根据需要进行动态设置。对于需要快速反馈的业务系统来说,可以将行锁的等待时间调小,以避免事务长时间挂起;对于后台运行的批量处理程序来说,可以将行锁的等待时间调大,以避免发生大的回滚操作。

留言区

还能输入500个字符