MySQL优化(前台应用优化、查询缓存优化、内存优化)
MySQL优化
文章目录
- MySQL优化
- 一、前台应用优化
- 1.1 使用连接池
- 1.2 减少对MySQL的访问
- 1.2.1 避免对数据进行重复检索
- 1.2.2 增加cache层
- 1.3 负载均衡
- 1.3.1 利用MySQL主从复制
- 1.3.2 采用分布式数据库架构
- 二、查询缓存优化
- 2.1 概述
- 2.2 操作流程
- 2.3 查询缓存配置
- 2.4 开启查询缓存
- 2.5 查询缓存SELECT选项
- 2.6 查询缓存失效的情况
- 三、内存优化
- 3.1 内存优化原则
- 3.2 MyISAM内存优化
- key_buffer_size
- read_buffer_size
- read_rnd_buffer_size
- 3.3 InnoDB内存优化
- innodb_buffer_pool_size
- innodb_log_buffer_size
一、前台应用优化
在实际生产环境中,由于数据库本身的性能局限,就必须要对前台的应用进行一些优化,来降低数据库的访问压力。
1.1 使用连接池
对于访问数据库来说,建立连接的代价是比较昂贵的,因为频繁的创建、关闭连接,是比较耗费资源的,所以有必要建立数据库连接池如DBUtils、C3P0等,以提高访问的性能。
1.2 减少对MySQL的访问
1.2.1 避免对数据进行重复检索
能够一次连接就获取到结果的,就不使用两次连接。
比如 ,需要获取书籍的 id 和 name 字段 , 查询如下:
select id , name from tb_book;
之后,在业务逻辑中又需要获取书籍状态信息, 查询如下:
select id , status from tb_book;
这样需要向数据库提交两次请求,数据库就要做两次查询操作,完全可以用一条SQL语句得到想要的结果:
select id, name , status from tb_book;
1.2.2 增加cache层
使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存,或者使用redis数据库来缓存数据,下次访问的时候直接从缓存中获取数据,而不用再去数据库中获取数据。
1.3 负载均衡
利用某种均衡算法,将固定的负载量分布到不同的服务器上, 以此来降低单台服务器的负载,达到优化的效果。
1.3.1 利用MySQL主从复制
通过MySQL的主从复制,实现读写分离,使增删改操作走主节点,查询操作走从节点,从而可以降低单台服务器的读写压力,如下图所示:
1.3.2 采用分布式数据库架构
分布式数据库架构适合大数据量、负载高的情况,它有良好的拓展性和高可用性。通过在多台服务器之间分布数据,可以实现在多台服务器之间的负载均衡,提高访问效率。
二、查询缓存优化
2.1 概述
开启Mysql的查询缓存,当执行完全相同的SQL语句的时候(关键字大小写也必须相同),服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,查询缓存不适合修改比较频繁的表。
2.2 操作流程
- 客户端发送一条查询给服务器
- 服务器先会检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果,否则进入下一阶段
- 如果没有命中缓存,服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划
- MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询
- 将结果返回给客户端并且存放到查询缓存中,下一次执行此sql语句时,直接从缓存中获取数据
对应的图示如下:
2.3 查询缓存配置
查看当前的MySQL数据库是否支持查询缓存:
SHOW VARIABLES LIKE 'have_query_cache';
查看当前MySQL是否开启了查询缓存 :
SHOW VARIABLES LIKE 'query_cache_type';
查看查询缓存的最大占用大小,单位是字节 :
如果太小,表示不能容纳过多缓存,需扩容
SHOW VARIABLES LIKE 'query_cache_size';
查看查询缓存的状态变量:
SHOW STATUS LIKE 'Qcache%';
各个变量的含义如下:
参数 含义 Qcache_free_blocks 查询缓存的可用内存块数 Qcache_free_memory 查询缓存的可用内存量 Qcache_hits 查询缓存命中数 Qcache_inserts 添加到查询缓存的查询数 Qcache_lowmen_prunes 由于内存不足而从查询缓存中删除的查询数 Qcache_not_cached 非缓存查询的数量(由于 query_cache_type 设置而无法缓存或未缓存) Qcache_queries_in_cache 查询缓存中注册的查询数 Qcache_total_blocks 查询缓存中的块总数
2.4 开启查询缓存
MySQL的查询缓存默认是关闭的,需要手动配置参数 query_cache_type
来开启查询缓存。
query_cache_type 参数的可取值有三个 :
值 | 含义 |
---|---|
OFF 或 0 | 查询缓存功能关闭 |
ON 或 1 | 查询缓存功能打开,SELECT的结果符合缓存条件即会缓存,否则不予缓存,如果显式指定 SQL_NO_CACHE,不予缓存 |
DEMAND 或 2 | 查询缓存功能按需进行,显式指定 SQL_CACHE 的SELECT语句才会缓存,其它均不予缓存 |
在 /usr/my.cnf
配置中,增加以下配置 :
配置完毕之后,重启服务既可生效 ;
2.5 查询缓存SELECT选项
可以在SELECT语句中指定两个与查询缓存相关的选项 :
- SQL_CACHE : 如果查询结果是可缓存的,并且 query_cache_type 系统变量的值为ON或 DEMAND ,则缓存查询结果 。
- SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果。
例子:
#两个参数添加在SELECT关键字之后,列名之前
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
2.6 查询缓存失效的情况
1) SQL 语句不一致的情况,不会使用缓存(比如下面的两条sql语句s的大小写) ,要想命中查询缓存,查询的SQL语句必须一致。
select count(*) from tb_item;Select count(*) from tb_item;#sql语句不一致,查询缓存失效
2) 当查询语句中有一些不确定的值时,不会使用缓存。如 : now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 等。
select * from tb_item where updatetime < now() limit 1;
3) 不使用任何表查询的语句。
select 'A';
4) 查询 mysql, information_schema 或 performance_schema 系统数据库中的表时,不会使用查询缓存。
select * from information_schema.engines;
5) 在存储过程,函数,触发器或事件的主体内执行的查询不会使用查询缓存。
6)如果表更改,则使用该表的所有高速缓存都将变为无效并从高速缓存中删除。如对表使用 INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE,或 DROP DATABASE 。
三、内存优化
3.1 内存优化原则
1) 将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够内存。
2) MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果使用MyISAM存储引擎,就要预留更多的内存给操作系统做IO缓存。
3) 排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽。
3.2 MyISAM内存优化
对于索引块,MyISAM存储引擎使用 key_buffer
缓存索引块,加速MyISAM索引的读写速度。
对于数据块,MyISAM没有特别的缓存机制,完全依赖于操作系统的IO缓存。
key_buffer_size
key_buffer_size决定MyISAM索引块缓存区的大小,直接影响到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.3 InnoDB内存优化
InnoDB用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。
innodb_buffer_pool_size
该变量决定了 InnoDB 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size 的值越大,缓存命中率越高,访问InnoDB表需要的磁盘 I/O 就越少,性能也就越高。
在 /usr/my.cnf
中配置:
innodb_buffer_pool_size=512M
innodb_log_buffer_size
决定了InnoDB重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。
在 /usr/my.cnf
中配置:
innodb_log_buffer_size=10M
还没有评论,来说两句吧...