mysql手册11_查询缓存优化和内存管理优化、并发参数调整

柔光的暖阳◎ 2022-11-29 05:47 184阅读 0赞

**mysql手册11\_查询缓存优化和内存管理优化、并发参数调整**

--------------------

**当开启查询缓存，执行了相同sql查询操作时，服务器会直接从查询缓存中读取结果，当数据被修改后，缓存失效**

**修改频繁的表不适合设置查询缓存。**

**查询缓存参数的配置：**

**注：Mysql8.0已经不再支持查询缓存功能**

查看当前数据库是否支持查询缓存：
    show variables like "have_query_cache";
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | have_query_cache | NO    |
    +------------------+-------+
    
    查看查询缓存开关是否开启：
    show variables like "query_cache_type";
    
    查看查询缓存的占用大小（单位：字节）：
    show variables like "query_cache_size";
    
    查看查询缓存的状态信息：
    show status like "Qcache%";
    
    开启查询缓存：
    在linux系统下，修改/usr/my.cnf 配置文件，添加：query_cache_type=1,保存，重启mysql。
    
    语句指定是否走缓存:
    缓存：select sql_cache id,name from user;
    不缓存：select sql_no_cache id,name from user;

**查询缓存失效场景：**

1. sql语句不一致(大小写不一致也算)
    2. 语句中有now()，rand()，uuid()等不确定信息
    3. 查询mysql系统数据库时不使用缓存
    4. 在存储过程、函数、触发器内执行的查询不使用缓存
    5. 表发生修改操作后，缓存失效

--------------------

**内存优化原则**

1. 尽可能分配给mysql更多的内存
    2. MyISAM存储引擎的数据文件依赖于操作系统自身的IO缓存，因此如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存
    3. 排序区、连接区等缓存是分配给每个会话session的，如果设置太大，既浪费资源，在并发连接数较多时会导致物理内存耗尽

--------------------

### **MyISAM存储引擎的内存优化** ###

**key\_buffer\_size**  
MyISAM 存储引擎使用 key\_buffer 缓存索引块，加速索引的读写速度，对于数据块，MyISAM 完全依赖操作系统的IO缓存

查看key\_buffer 的默认大小

show variables like 'key_buffer_size';
    +-----------------+---------+
    | Variable_name   | Value   |
    +-----------------+---------+
    | key_buffer_size | 8388608 |
    +-----------------+---------+

在 /usr/my.cnf 文件中配置 key\_buffer 的大小（建议将物理内存的1/4分配给key\_buffer\_size）：

key_buffer_size=512M

**read\_buffer\_size**  
若需要经常顺序扫描 MyISAM 表，可通过增大**read\_buffer\_size**来改善性能  
注：**read\_buffer\_size** 为session独占，值过大易造成内存浪费

**read\_rnd\_buffer\_size**  
若需要经常对 MyISAM 表进行排序操作  
注：**read\_rnd\_buffer\_size** 为session独占，值过大易造成内存浪费

--------------------

### **InnoDB存储引擎的内存优化** ###

**InnoDB存储引擎的索引块和数据块都是使用的IO缓存池**

**innodb\_buffer\_pool\_size**  
该值决定了InnoDB表数据和索引数据的最大缓存区大小，值越大，缓存命中率越高，降低IO操作，提高性能

innodb_buffer_pool_size=512M

**innodb\_log\_buffer\_size**  
该值决定了日志缓存大小，增加该缓存大小可减少日志写入磁盘的IO操作，提高性能

innodb_log_buffer_size=10M

--------------------