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

灰太狼 2022-10-07 12:51 222阅读 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 字段 ， 查询如下：

  1. select id , name from tb_book;

之后，在业务逻辑中又需要获取书籍状态信息， 查询如下：

  1. select id , status from tb_book;

这样需要向数据库提交两次请求，数据库就要做两次查询操作，完全可以用一条SQL语句得到想要的结果：

  1. 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数据库是否支持查询缓存：

    1. SHOW VARIABLES LIKE 'have_query_cache';

    image-20210617221757773

  2. 查看当前MySQL是否开启了查询缓存 ：

    1. SHOW VARIABLES LIKE 'query_cache_type';

    image-20210617221827299

  3. 查看查询缓存的最大占用大小，单位是字节 ：

    如果太小，表示不能容纳过多缓存，需扩容

    1. SHOW VARIABLES LIKE 'query_cache_size';

    image-20210617222030988

  4. 查看查询缓存的状态变量：

    1. SHOW STATUS LIKE 'Qcache%';

    image-20210617222100319

    各个变量的含义如下：










































    参数 含义
    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 配置中，增加以下配置 ：

image-20210617222708767

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

image-20210617133445282

2.5 查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项 ：

  • SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果 。
  • SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

  1. #两个参数添加在SELECT关键字之后，列名之前
  2. SELECT SQL_CACHE id, name FROM customer;
  3. SELECT SQL_NO_CACHE id, name FROM customer;

2.6 查询缓存失效的情况

1） SQL 语句不一致的情况，不会使用缓存（比如下面的两条sql语句s的大小写） ，要想命中查询缓存，查询的SQL语句必须一致。

  1. select count(*) from tb_item;Select count(*) from tb_item;#sql语句不一致，查询缓存失效

2） 当查询语句中有一些不确定的值时，不会使用缓存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 等。

  1. select * from tb_item where updatetime < now() limit 1;

3） 不使用任何表查询的语句。

  1. select 'A';

4） 查询 mysql， information_schema 或 performance_schema 系统数据库中的表时，不会使用查询缓存。

  1. 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 中做如下配置：

  1. 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 中配置：

  1. innodb_buffer_pool_size=512M

innodb_log_buffer_size

决定了InnoDB重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

在 /usr/my.cnf 中配置：

  1. innodb_log_buffer_size=10M

发表评论

表情：
评论列表 （有 0 条评论，222人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql数据库查询优化-缓存

    查询执行的基础 一.基础 当希望mysql能够以更高的性能运行查询时，最好的办法就是弄清楚mysql是如何优化和执行查询的，基本可以从以下3点开始了解: 1.客户端

    怼烎@怼烎@/ 0 赞/ 384 阅读