MySQL数据库性能优化

前言

由于一些企业需要在本地部署系统(使用企业服务器部署系统,数据库也部署在同一台服务器上),本地部署的系统的服务器往往无法到达我们的云部署服务器,速度性能更差。尤其是在查询统计报表时,对于本地企业来说,上云几秒的速度需要几分钟多的时间,所以最近企业数据库的性能得到了优化。事实上,查询缓存优化和内存管理优化的速度可以显著提高。

[En]

Because some enterprises require local deployment of systems (using enterprise servers to deploy systems and databases are also deployed on the same server), the servers of locally deployed systems often fail to reach our cloud deployment servers, and the speed performance is even less. Especially when querying statistical reports, the speed of a few seconds on the cloud takes more than a few minutes for local enterprises, so the performance of the enterprise database has been optimized recently. In fact, the speed of query cache optimization and memory management optimization can be significantly improved.

一、应用优化

1. 使用连接池

对于访问数据库,建立连接的成本更高,因为我们经常创建和关闭连接,这更消耗资源。

[En]

For accessing the database, the cost of establishing a connection is more expensive, because we frequently create and close connections, which is more resource-consuming.

2. 避免对数据进行重复检索

可以一次从数据库中读取,不要多次读取,最好是一次读取。

[En]

Can be read from the database at one time, do not read multiple times, it is best to read at once.

3. 增加cache层

在应用中,我们可以在应用中增加 缓存 层来达到减轻数据库负担的目的。缓存层有很多种,也有很多实现方式,只要能达到降低数据库的负担又能满足应用需求就可以。因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储, 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存,或者使用redis数据库来缓存数据 。

4. 负载均衡

负载均衡是应用中非常常见的一种优化方法,其机制是利用一定的均衡算法将固定的负载分配给不同的服务器,从而降低单个服务器的负载,达到优化的效果。

[En]

Load balancing is a very common optimization method used in applications, and its mechanism is to use a certain balancing algorithm to distribute a fixed load to different servers, so as to reduce the load of a single server and achieve the effect of optimization.

实现负债均衡的方式:

1.利用MySQL复制分流查询
通过MySQL的主从复制,实现读写分离,使增删改操作走主节点,查询操作走从节点,从而可以降低单台服务器的读写压力。

MySQL数据库性能优化

二、查询缓存优化(MySQL8.0:不在支持查询缓存;官方解释 )

开启Mysql的查询缓存,当执行完全相同的SQL语句的时候,服务器就会直接从缓存中读取数据,当数据被修改,之前的缓存会失败,修改比较频繁的表不适合做查询缓存。

  1. 开启查询缓存后,MySQL查询流程

a. 客户端发送一条查询给服务器

b. 服务器先回检查查询缓存,如果命中了缓存,则立即返回储存在缓存中的结果。否则进入下一阶段;

c. 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划;

d. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询;

e. 将结果返回给客户端,同时将查询结果放入缓存

MySQL数据库性能优化
2.查询缓存配置****
  • 查看当前的MySQL是否支持查询缓存(YES/NO)
show variables like 'have_query_cache';
  • 查看当前MySQL是否开启了查询缓存(OFF/NO)
show variables like 'query_cache_type';
  • 查看查询缓存的占用大小
show VARIABLES like 'query_cache_size';
  • 查询缓存的状态变量
show status like 'Qcache%';

MySQL数据库性能优化

3. 开启查询缓存配置

Linux:在/etc/my.cnf配置中,Windows:在my.ini配置中,添加以下配置(配置完毕之后,重启服务器即可生效):

query-cache-type=1

4. select可以指定不适用缓存

sql_cache:如果查询结果是可缓存的,并且query_cache_type系统变量的值为ON或DEMAND,则缓存查询结果。

sql_no_cache:服务器不适用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果。

SELECT SQL_CACHE id,name FROM customer;
SELECT SQL_NO_CACHE id,name FROM customer;

5. 查询缓存失败的情况****

  1. SQL语句不一致的情况,要想命中查询缓存,查询的SQL语句必须一致。
  2. 当查询语句中有一些不确定方法时,则不会缓存。如:now(),current_date(),curdate(),curtome(),rand(),uuid(),user(),database()。
  3. 不使用任何表查询语句。如 select ‘A’
  4. 查询mysql,information_schema或performance_schema数据库中的表时,不会走查询缓存。
  5. 如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除

三、内存管理及优化

1.内存优化原则****

a. 将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够的内存。

b. MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做IO缓存。

c. 排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽。
2. MyISAM内存优化

myisam存储引擎使用key_buffer缓存索引块,加速myisam索引的读写速度,等对于myisam表的数据块,mysql没有特别的缓存机制,完全依赖于操作系统的IO缓存。

  1. key_buffer_size决定MyISAM索引块缓存区的大小,直接影响到MyISAM表的存取效率。修改其内存大小,可以在my.ini/cnf中做如下配置:key_buffer_size=512
  2. read_buffer_size:如果需要经常顺序扫描myisam表,可以通过增大read_buffer_size值来改善性能。但需要注意的是read_buffer_size是每个session独占的,如果默认值设置太大,就会造成内存浪费。
  3. read_rnd_buffer_size:对于需要做排序的myisam表的查询,如带有order by子句的sql,适当增加read_rnd_buffer_size的值,可以改善此类sql性能。但需要注意的是read_rnd_buffer_size是每个session独占的,如果默认值设置太大,就会造成内存浪费。

3. InnoDB内存优化

innodb用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。

  1. innodb_buffer_pool_size:该变量决定了innodb存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size的值越大,缓存命中率越高,访问InnoDB表需要的磁盘I/O就越少,性能也能越高。
  2. innodb_log_buffer_size:决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

四、并发参数调整

从实现上来说,MySQL Server 是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在Mysql中,控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。

  1. max_connections:采用max_connections 控制允许连接到MySQL数据库的最大数量,默认值是 151。如果状态变量connection_errors_max_connections 不为零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这是可以考虑增大max_connections 的值。
-- 64G内存最大连接数,并发连接数
max_connections=100000

-- 8G内存配置
max_connections=5000
  1. back_log:back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源后,该堆栈中的请求去连接,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源,将会报错。5.6.6 版本之前默认值为 50 , 之后的版本默认为 50 +(max_connections / 5), 但最大不超过900。
-- 64GB
back_log=4096
-- 8GB
back_log=500
  1. table_open_cache:该参数用来控制所有SQL语句执行线程可打开表缓存的数量, 而在执行SQL语句时,每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 :
-- 64GB
table_open_cache=16384
-- 8GB
table_open_cache=2048
  1. thread_cache_size:为了加快连接数据库的速度,MySQL 会缓存一定数量的客户服务线程以备重用(类似线程池,避免重复打开关闭线程资源),通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量
-- 64GB
thread_cache_size=64
-- 8GB
table_open_cache=64
  1. innodb_lock_wait_timeout:该参数是用来设置InnoDB 事务等待行锁的时间,默认值是50ms , 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说,可以将行锁的等待时间调小,以避免事务长时间挂起; 对于后台运行的批量处理程序来说,可以将行锁的等待时间调大, 以避免发生大的回滚操作。
-- 64GB
innodb_lock_wait_timeout=30s
-- 8GB
innodb_lock_wait_timeout=30s

Original: https://www.cnblogs.com/yl0604/p/16266493.html
Author: JJJenny
Title: MySQL数据库性能优化

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505385/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • @Mapper报错,java.lang.NoClassDefFoundError: org/apache/ibatis/annotations/Mapper

    已解决,可以直接看末尾 @Mapper报错,如图: 查了好多资料,如:修改依赖 把1.2改成1.3后还是未能解决。 换成@MapperScan 之后项目启动报错,如图 找到解决方法…

    数据库 2023年6月11日
    0154
  • springboot~HandlerFunction和RouterFunction

    HandlerFunction和RouterFunction RouterFunction为我们应用程序添加一个新的路由,这个路由需要绑定一个HandlerFunction,做为它…

    数据库 2023年6月6日
    0117
  • 网页包抓取工具Fiddler工具简单设置

    当下载好fiddler软件后首先通过以下简单设置,或者有时候fiddler抓取不了浏览器资源了。可以通过以下设置。 设置完成后重启软件。打开网络看看有没有抓取到包。 Origina…

    数据库 2023年6月6日
    0166
  • ShardingSphere 异构迁移最佳实践:将3.5亿量级的顾客系统 RTO 减少60倍

    Apache ShardingSphere 助力当当 3.5 亿用户量级顾客系统重构,由 PHP+SQL Server 技术栈无缝转型为 Java+ShardingSphere+M…

    数据库 2023年6月16日
    0172
  • 多商户商城系统功能拆解28讲-平台端营销-消费奖励

    多商户商城系统,也称为B2B2C(BBC)平台电商模式多商家商城系统。可以快速帮助企业搭建类似拼多多/京东/天猫/淘宝的综合商城。 多商户商城系统支持商家入驻加盟,同时满足平台自营…

    数据库 2023年6月14日
    0135
  • 号称能将STW干掉1ms以内的Java垃圾收集器ZGC到底是个什么东西?

    ZGC介绍 ZGC(The Z Garbage Collector)是JDK 11中推出的一款追求极致低延迟的实验性质的垃圾收集器,它曾经设计目标包括: 停顿时间不超过10ms; …

    数据库 2023年6月16日
    0191
  • 博客园美化-随季节变化实现不同的飘落效果

    最近在研究博客园的美化效果,看到有一个樱花飘落的效果,忽然突发奇想,如果能根据当前日期所处的季节实现不同的飘落效果岂不是更酷。😂 最近在研究博客园的美化效果,看到有一个樱花飘落的效…

    数据库 2023年6月6日
    0167
  • 14 在 Java 中,如何跳出当前的多重嵌套循环

    在最外层添加一个标记如A,然后用breakA,即可跳出多重循环 关键字break 使用范围:switch-case,循环结构中 break在循环结构中的作用:结束 当前循环 bre…

    数据库 2023年6月6日
    0133
  • DRF使用缓存

    在做项目的时候,有时很多数据都是经常被访问的而且数据是基本不变的,比如省市区的数据是经常被用户查询使用的,而且数据基本不变化,所以我们可以将省市区数据进行缓存处理,减少数据库的查询…

    数据库 2023年6月14日
    0105
  • ASP.NET Core Docker部署

    前言 在前面文章中,介绍了 ASP.NET Core在 macOS,Linux 上基于Nginx和Jexus的发布和部署,本篇文章主要是如何在Docker容器中运行ASP.NET …

    数据库 2023年6月11日
    0174
  • go的调度

    操作系统根据资源访问权限的不同,体系架构可以分为用户空间和内核空间;内核空间主要操作访问CPU资源,IO资源,内存资源等硬件资源,为应用程序提供最基本的基础资源;用户空间是上层应用…

    数据库 2023年6月9日
    0142
  • Dubbo源码(八)-负载均衡

    前言 本文基于Dubbo2.6.x版本,中文注释版源码已上传github:xiaoguyu/dubbo 负载均衡,英文名称为Load Balance,其含义就是指将负载(工作任务)…

    数据库 2023年6月11日
    0138
  • xtrabackup2版本和xtrabackup8版本对比

    导语在使用xtrabackup8版本对mysql8版本进行备份恢复搭建从库的时候,继续使用xtrabackup2版本的方式,从xtrabackup_binlog_info 文件中找…

    数据库 2023年6月16日
    0171
  • 什么是真正的HTAP?(二)挑战篇

    上一篇文章中,我们从技术和商业角度分析了 HTAP 系统缘起的背景,本篇文章中,我们将从 HTAP 定义及其相关核心技术等方面来讨论:构建一个 HTAP 所面临的核心问题和挑战有哪…

    数据库 2023年5月24日
    0104
  • B树-查找

    B树系列文章 1. B树-介绍 2. B树-查找 3. B树-插入 4. B树-删除 查找 假设有一棵3阶B树,如下图所示。 下面说明在该B树中查找 52的过程 首先,从根结点出发…

    数据库 2023年6月14日
    0189
  • 你的 SQL 还在回表查询吗?快给它安排覆盖索引

    什么是回表查询 小伙伴们可以先看这篇文章了解下什么是聚集索引和辅助索引:Are You OK?主键、聚集索引、辅助索引,简单回顾下,聚集索引的叶子节点包含完整的行数据,而非聚集索引…

    数据库 2023年5月24日
    0128
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球