MySQL优化（前台应用优化、查询缓存优化、内存优化）

灰太狼 2022-10-07 12:51 140阅读 0赞

# 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的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力，如下图所示：

![image-20210616221616155][]

#### 1.3.2 采用分布式数据库架构 ####

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

## 二、查询缓存优化 ##

### 2.1 概述 ###

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候（关键字大小写也必须相同），服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，查询缓存不适合修改比较频繁的表。

### 2.2 操作流程 ###

1.  客户端发送一条查询给服务器
2.  服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一阶段
3.  如果没有命中缓存，服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
4.  MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5.  将结果返回给客户端并且存放到查询缓存中，下一次执行此sql语句时，直接从缓存中获取数据

对应的图示如下：

![image-20210617221123049][]

### 2.3 查询缓存配置 ###

1.  查看当前的MySQL数据库是否支持查询缓存：
    
        SHOW VARIABLES LIKE 'have_query_cache';
    
    ![image-20210617221757773][]
2.  查看当前MySQL是否开启了查询缓存 ：
    
        SHOW VARIABLES LIKE 'query_cache_type';
    
    ![image-20210617221827299][]
3.  查看查询缓存的最大占用大小，单位是字节 ：
    
    如果太小，表示不能容纳过多缓存，需扩容
    
        SHOW VARIABLES LIKE 'query_cache_size';
    
    ![image-20210617222030988][]
4.  查看查询缓存的状态变量：
    
        SHOW STATUS LIKE 'Qcache%';
    
    ![image-20210617222100319][]
    
    各个变量的含义如下：
    
    <table> 
     <thead> 
      <tr> 
       <th>参数</th> 
       <th>含义</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>Qcache_free_blocks</td> 
       <td>查询缓存的可用内存块数</td> 
      </tr> 
      <tr> 
       <td>Qcache_free_memory</td> 
       <td>查询缓存的可用内存量</td> 
      </tr> 
      <tr> 
       <td>Qcache_hits</td> 
       <td>查询缓存命中数</td> 
      </tr> 
      <tr> 
       <td>Qcache_inserts</td> 
       <td>添加到查询缓存的查询数</td> 
      </tr> 
      <tr> 
       <td>Qcache_lowmen_prunes</td> 
       <td>由于内存不足而从查询缓存中删除的查询数</td> 
      </tr> 
      <tr> 
       <td>Qcache_not_cached</td> 
       <td>非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）</td> 
      </tr> 
      <tr> 
       <td>Qcache_queries_in_cache</td> 
       <td>查询缓存中注册的查询数</td> 
      </tr> 
      <tr> 
       <td>Qcache_total_blocks</td> 
       <td>查询缓存中的块总数</td> 
      </tr> 
     </tbody> 
    </table>

### 2.4 开启查询缓存 ###

MySQL的查询缓存默认是关闭的，需要手动配置参数 `query_cache_type` 来开启查询缓存。

query\_cache\_type 参数的可取值有三个 ：

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>OFF 或 0</td> 
   <td>查询缓存功能关闭</td> 
  </tr> 
  <tr> 
   <td>ON 或 1</td> 
   <td>查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则不予缓存，如果显式指定 SQL_NO_CACHE，不予缓存</td> 
  </tr> 
  <tr> 
   <td>DEMAND 或 2</td> 
   <td>查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存，其它均不予缓存</td> 
  </tr> 
 </tbody> 
</table>

在 `/usr/my.cnf` 配置中，增加以下配置 ：

![image-20210617222708767][]

配置完毕之后，重启服务既可生效 ；

![image-20210617133445282][]

### 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

[image-20210616221616155]: /images/20221005/51209d3cf5fd48b691f525c04121c0f6.png
[image-20210617221123049]: /images/20221005/a0b7c7d953404bdd89bd3cb46b8e3c40.png
[image-20210617221757773]: /images/20221005/a14c6a3b73f8466a876215cd5eb7b002.png
[image-20210617221827299]: /images/20221005/da07a490c8064cd3bf94cfce55a5d9a1.png
[image-20210617222030988]: /images/20221005/5e821aa832f246069e4f2d575fe77eff.png
[image-20210617222100319]: /images/20221005/1a470a106618491582affbb3a5dffbb6.png
[image-20210617222708767]: /images/20221005/3f90263468194431ba71eb1b8e95dc53.png
[image-20210617133445282]: /images/20221005/7e08ef14d2784ab4a93eb836f92eb58d.png