07 MySQL的应用层调整，查询缓存设置，内存管理设置，并发参数的设置常识

目录

• 1 MySQL应用层面优化策略
  • 1-1 策略1：建立数据库连接池
  • 1-2 策略2：减少对MySQL的直接访问
    • 1.2.1 避免对数据的重复访问
    • 1.2.2 增加cache层
      • 具体的策略：
  • 1-3 负载均衡
    • 1-3-1 利用MySQL的复制来完成查询操作（降低单台服务器压力）
    • 1.3.2 采用分布式数据库架构
• 2 MySQL中的查询缓存优化（MySQL的服务层，8废弃了）
  • 2-1 概述
    • 缓存何时生效？
    • 缓存何时失效？
    • 不会进行缓存的情况：
  • 2-2 MySQL中缓存相关的命令
• 2-3 Mysql内存管理及优化
  • • 2-3-1 总的优化原则
    • 2-3-2 MyISAM的内存优化
    • 2-3-3 InnoDB 内存优化
• 2-4 MySQL的并发参数调整
• 参考资料

知识点复习：整个MySQL Server由以下组成，可以大致分为四个层次：连接层，服务层，引擎层，存储层

1 MySQL应用层面优化策略

1-1 策略1：建立数据库连接池

1-2 策略2：减少对MySQL的直接访问

1.2.1 避免对数据的重复访问

基本思想:一次访问就能获得的数据，不要分2次访问。

1.2.2 增加cache层

基本思想：在应用中，我们可以在应用中增加缓存层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。

具体的策略：

• 部分数据从数据库中抽取出来放到应用端以文本方式存储
• 使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，
• 使用redis数据库来缓存数据

1-3 负载均衡

基本思想：利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果

1-3-1 利用MySQL的复制来完成查询操作（降低单台服务器压力）

上图中：

• 主节点(Master)：负责各个客户端增删改操作
  • 主节点的数据修改后需要与从节点进行同步（synchronize）
• 多个从节点(Master): 负责客户端的读操作。

上图中这种架构适合数据库的查询操作远多于增删改操作。

1.3.2 采用分布式数据库架构

分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数
据，可以实现在多台服务器之间的负载均衡，提高访问效率。

2 MySQL中的查询缓存优化（MySQL的服务层，8废弃了）

2-1 概述

缓存何时生效？

当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果。

缓存何时失效？

• 数据被修改，之前的缓存会失效，因此修改比较频繁的表不适合做查询缓存

不会进行缓存的情况：

• 当查询语句中有一些不确定的时，则不会缓存。
  • now() , current_date() , curdate() , curtime() , rand() ,
    uuid() , user() , database()
• 不使用任何表查询语句
• 查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存
• 在存储的函数，触发器或事件的主体内执行的查询

缓存在实际中如何被使用的？

客户端发送一条查询给服务器；
step1: 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
step2: 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
step3: MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
step4: 将结果返回给客户端。

2-2 MySQL中缓存相关的命令

SHOW VARIABLES LIKE 'have_query_cache';  --查看数据库是否支持查询缓存，MySQL8废弃了这个功能
SHOW VARIABLES LIKE 'query_cache_type';  --查看是否开启缓存
SHOW VARIABLES LIKE 'query_cache_size';  --查看查询缓存的占用大小
SHOW STATUS LIKE 'Qcache%';              --查询缓存的状态变量

--开启缓存
在 /usr/my.cnf 配置中，增加以下配置 
query_cache_type=1

状态变量的含义

2-3 Mysql内存管理及优化

2-3-1 总的优化原则

• 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存
• MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。
• 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理
  分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽

2-3-2 MyISAM的内存优化

对于MyISAM,索引与数据的缓存是分开的：

• 索引：使用 key_buffer 缓存索引块，加速myisam索引的读写速度
• 数据：数据库没有提供数据缓存，数据缓存依赖于IO缓存

参数	说明	备注
key_buffer_size	key_buffer_size决定MyISAM索引块缓存区的大小
read_buffer_size	适用经常顺序扫描myisam表情况	每个session独占的默认值设置太大，内存浪费
read_rnd_buffer_size	适用需要做排序的myisam表的查询	每个session独占的默认值设置太大，内存浪费

2-3-3 InnoDB 内存优化

对于InnoDB, 设计了专门的IO缓存池用于缓存索引块与数据块：

参数值	说明	备注
innodb_buffer_pool_size	innodb 存储引擎表数据和索引数据的最大缓存区大小	资源足够，越大越好
innodb_log_buffer_size	innodb重做日志缓存的大小	对于可能产生大量更新记录的事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的磁盘操作

2-4 MySQL的并发参数调整

参数	说明	备注
max_connections	允许连接到MySQL数据库的最大数量	最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷，CPU的处理速度，期望的响应时间等
back_log	MySQL监听TCP端口时设置的积压请求栈大小	需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值
table_open_cache	控制所有SQL语句执行线程可打开表缓存的数量	执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定
thread_cache_size	缓存数量的客户服务线程	参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量
innodb_lock_wait_timeout	设置InnoDB 事务等待行锁的时间	对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说，可以将行锁的等待时间调大， 以避免发生大的回滚操作。

参考资料

01 数据库课程
02 MySQL手册

---

20210310