当前位置: 首页 > news >正文

MySQL性能优化

一、概述

一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。

注意:优化有风险,涉足需谨慎!

优化可能带来的问题

  • 优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统!
  • 优化手段有很大的风险,一定要意识到和预见到!
  • 任何的技术可以解决一个问题,但必然存在带来一个问题的风险!
  • 对于优化来说调优而带来的问题,控制在可接受的范围内才是有成果。
  • 保持现状或出现更差的情况都是失败!

优化的需求

  • 稳定性和业务可持续性,通常比性能更重要!
  • 优化不可避免涉及到变更,变更就有风险!
  • 优化使性能变好,维持和变差是等概率事件!
  • 优化应该是各部门协同,共同参与的工作,任何单一部门都不能对数据库进行优化!

所以优化工作,是由业务需要驱使的!!!

优化由谁参与

在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。

优化的方向

在数据库优化上有两个主要方向:即安全与性能。

  • 安全 : 数据安全性
  • 性能 : 数据的高性能访问

优化的维度

9f2a26750579db1d4ef7d970ae867206

从上图中可以看出,我们把数据库优化分为四个纬度:硬件,系统配置,数据库表结构,SQL及索引

  1. 硬件:CPU、内存、存储、网络设备等
  2. 系统配置:服务器系统、数据库服务参数等
  3. 数据库表结构:高可用、分库分表、读写分离、存储引擎、表设计等
  4. SQL及索引:SQL语句、索引使用等

优化成本效果:

  • 从优化成本进行考虑:硬件 > 系统配置 > 数据库表结构 > SQL及索引
  • 从优化效果进行考虑:硬件 < 系统配置 < 数据库表结构 < SQL及索引

数据库使用优化思路

在多数时候,我们进行调优不需要进行这么全面、大范围的调优,一般情况下,我们进行数据库层面的优化就可以了,那我们该如何调优的呢?

应急调优的思路:

针对突然的业务办理卡顿,无法进行正常的业务处理!需要立马解决的场景!

  1. show processlist(查看连接session状态)
  2. explain(分析查询计划),show index from tableName(分析索引)
  3. show status like ‘%lock%’; 查询锁状态

常规调优的思路:

针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。

  1. 开启慢查询日志,运行一天
  2. 查看slowlog,分析slowlog,分析出查询慢的语句。
  3. 按照一定优先级,进行一个一个的排查所有慢语句。
  4. 分析top sql,进行explain调试,查看语句执行时间。
  5. 调整索引或语句本身。

二、优化实践

2.1 查询优化

2.1.1 MySQL查询流程

我们该如何进行sql优化呢,首先我们需要知道,sql优化其实主要是解决查询的优化问题,所以我们先从数据库的查询开始入手,下面这幅图显示了查询的执行路径:

① 客户端将查询发送到服务器;
② 服务器检查查询缓存,如果找到了,就从缓存中返回结果,否则进行下一步。
③ 服务器解析,预处理。
④ 查询优化器优化查询
⑤ 生成执行计划,执行引擎调用存储引擎API执行查询
⑥ 服务器将结果发送回客户端。

9a62048205bf8ccc41d50c8b601c39db

查询缓存

在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据,如果命中缓存直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

语法解析和预处理器

MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。

查询优化器

语法树被校验合法后由优化器转成查询计划,一条语句可以有很多种执行方式,最后返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

查询执行引擎

在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。最常使用的也是比较最多的引擎是MyISAM引擎和InnoDB引擎。mysql5.5开始的默认存储引擎已经变更为innodb了。

前面的查询流程分析,我们大概了解了MySQL是如何执行的。现在我们先从查询优化部分开始。

sql是我们和数据库交流最重要的部分,所以我们在调优的时候,需要花费的大量时间就在sql调优上面。常见的分析手段有慢查询日志,EXPLAIN分析查询,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

2.1.2 慢查询日志(重要)

默认情况下慢日志查询是禁用的。通过show variables like '%slow_query_log%'查看慢查询日志的开启情况

慢查询日志开启

如要开启慢查询日志,可以使用命令set global slow_query_log=1;。再次查看慢查询日志,可以发现已经开启。

但是当重启MySQL后,则又会关闭。如果需要长期开启的话,需要在配置文件/etc/my.cnf或my.ini中在[mysqld]一行下面加入三个配置参数

slow_query_log=ON
slow-query-log-file=/var/lib/mysql/slow-query.log
long_query_time=0

修改完成后,重启mysqlservice mysqld restart

慢查询分析

如果慢查询日志中记录内容很多,可以使用mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总,显示汇总后摘要结果。

进入log的存放目录,运行:

[root@mysql_data]# mysqldumpslow slow-query.log
Reading mysql slow query log fromslow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s)Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;

mysqldumpslow命令

mysqldumpslow -s c -t 10 slow-query.log

这会输出记录次数最多的10条SQL语句,其中:

  • -s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙
  • -t, 是top n的意思,即为返回前面多少条的数据;

例如:

mysqldumpslow -s r -t 10 slow-query.log:得到返回记录集最多的10个查询。
mysqldumpslow -s t -t 10 -g “leftjoin” slow-query.log:得到按照时间排序的前10条里面含有左连接的查询语句。

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句,对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后,由于日志记录操作,在一定程度上会占用CPU资源影响mysql的性能,但是可以阶段性开启来定位性能瓶颈。

2.1.3 执行计划Explain

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的。我们可以用执行计划来分析查询语句或者表结构的性能瓶颈

具体参考:MySQL执行计划

2.2 索引优化

具体参考:MySQL索引结构及原理

2.3 高级查询

具体参考:索引优化全攻略:提升排序、GROUP BY与分页性能

三、存储优化

MySQL中索引是在存储引擎层实现的,这里我们会讲解存储引擎。

执行查询引擎的命令show engines,可以看到MySQL支持的存储引擎结果如下:

dca6f214b13daa4d9ab23958565cb4a5

mysql支持存储引擎有好几种,咱们这里主要讨论一下常用的InnodbMyISAM存储引擎。

3.1 存储引擎介绍

3.1.1 InnoDB存储引擎

特点:

  1. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。相比较MyISAM存储引擎,InnoDB写的处理效率差一点并且会占用更多的磁盘空间保留数据和索引。
  2. 提供了对数据库事务ACID(原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability)的支持,实现了SQL标准的四种隔离级别。
  3. 设计目标就是处理大容量的数据库系统,MySQL运行时InnoDB会在内存中建立缓冲池,用于缓冲数据和索引。
  4. 执行“select count(*) from table”语句时需要扫描全表,因为使用innodb引擎的表不会保存表的具体行数,所以需要扫描整个表才能计算多少行。
  5. InnoDB引擎是行锁,粒度更小,所以写操作不会锁定全表,在并发较高时,使用InnoDB会提升效率。即存在大量UPDATE/INSERT操作时,效率较高。

使用场景:

  1. 经常UPDATE/INSERT的表,使用处理多并发的写请求
  2. 支持事务,只能选出InnoDB
  3. 可以从灾难中恢复(日志+事务回滚)
  4. 外键约束、列属性AUTO_INCREMENT支持

3.1.2 MyISAM存储引擎

特点:

  1. MyISAM不支持事务,不支持外键,SELECT/INSERT为主的应用可以使用该引擎。

  2. 每个MyISAM在存储成3个文件,扩展名分别是:

    1. frm:存储表定义(表结构等信息)
    2. MYD(MYData),存储数据
    3. MYI(MYIndex),存储索引
  3. 不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。

  4. MyISAM类型的表提供修复的工具,可以用CHECK TABLE语句来检查MyISAM表健康,并用REPAIR TABLE语句修复一个损坏的MyISAM表。

  5. MySQL5.6以前,只有MyISAM支持Full-text全文索引

使用场景:

  1. 经常SELECT的表,插入不频繁,查询非常频繁。
  2. 不支持事务。
  3. 做很多count的计算。

3.1.3 MyISAM和Innodb区别

InnoDBMyISAM是许多人在使用MySQL时最常用的两个存储引擎,这两个存储引擎各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理,而InnoDB类型支持。MyISAM类型强调的是性能,其执行速度比InnoDB类型更快,而InnoDB提供事务支持已经外部键等高级数据库功能。

具体实现的差别:

  • MyISAM是非事务安全型的,而InnoDB是事务安全型的。
  • MyISAM锁的粒度是表级,而InnoDB支持行级锁定。
  • MyISAM不支持外键,而InnoDB支持外键
  • MyISAM相对简单,所以在效率上要优于InnoDB,小型应用可以考虑使用MyISAM
  • InnoDB表比MyISAM表更安全。

3.2 存储优化

3.2.1 禁用索引

对于使用索引的表,插入记录时,MySQL会对插入的记录建立索引。如果插入大量数据,建立索引会降低插入数据速度。为了解决这个问题,可以在批量插入数据之前禁用索引,数据插入完成后再开启索引。

禁用索引的语句:

ALTER TABLE table_name DISABLE KEYS

开启索引语句:

ALTER TABLE table_name ENABLE KEYS

MyISAM对于空表批量插入数据,则不需要进行操作,因为MyISAM引擎的表是在导入数据后才建立索引。

3.2.2 禁用唯一性检查

唯一性校验会降低插入记录的速度,可以在插入记录之前禁用唯一性检查,插入数据完成后再开启。(保证插入的数据没有重复的)

  • 禁用唯一性检查的语句:SET UNIQUE_CHECKS = 0;
  • 开启唯一性检查的语句:SET UNIQUE_CHECKS = 1;

3.2.3 禁用外键检查

插入数据之前执行禁止对外键的检查,数据插入完成后再恢复,可以提供插入速度。

  • 禁用:SET foreign_key_checks = 0;
  • 开启:SET foreign_key_checks = 1;

3.2.4 禁止自动提交

插入数据之前执行禁止事务的自动提交,数据插入完成后再恢复,可以提高插入速度。

  • 禁用:SET autocommit = 0;
  • 开启:SET autocommit = 1;

四、数据库结构优化

4.1 优化表结构

  • 尽量将表字段定义为NOT NULL约束,这时由于在MySQL中含有空值的列很难进行查询优化,NULL值会使索引以及索引的统计信息变得很复杂。
  • 对于只包含特定类型的字段,可以使用enumset等数据类型。
  • 数值型字段的比较比字符串的比较效率高得多,字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
  • 尽量使用TINYINT(4)SMALLINT(6)MEDIUM_INT(8)作为整数类型而非INT,如果非负则加上UNSIGNED
  • VARCHAR的长度只分配真正需要的空间
  • 尽量使用TIMESTAMP而非DATETIME,但TIMESTAMP只能表示1970-2038年,比DATETIME表示的范围小得多,而且TIMESTAMP的值因时区不同而不同。
  • 单表不要有太多字段,建议在20以内
  • 合理的加入冗余字段可以提高查询速度。

4.2 表拆分

4.2.1 垂直拆分

垂直拆分按照字段进行拆分,其实就是把组成一行的多个列分开放到不同的表中,这些表具有不同的结构,拆分后的表具有更少的列。例如:用户表中的一些字段可能经常访问,可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务,也可以保证两表的数据一致。缺点也很明显,由于拆分出来的两张表存在一对一的关系,需要使用冗余字段,而且需要join操作。但是我们可以在使用的时候可以分别取两次,这样的来说既可以避免join操作,又可以提高效率。

4.2.2 水平拆分

水平拆分按照进行拆分,常见的就是分库分表。以用户表为例,可以取用户ID,然后对ID10的余数,将用户均匀的分配进这0-910个表中。查找的时候也按照这种规则,又快又方便。

有些表业务关联比较强,那么可以使用按时间划分的。例如:每天的数据量很大,需要每天新建一张表。这种业务类型就是需要高速插入,但是对于查询的效率不太关心。表越大,插入数据所需要索引维护的时间也就越长。

4.3 读写分离

大型网站会有大量的并发访问,如果还是传统的数据存储方案,只是靠一台服务器处理,如此多的数据库连接、读写操作,数据库必然会崩溃,数据丢失的话,后果更是不堪设想。这时候,我们需要考虑如何降低单台服务器的使用压力,提升整个数据库服务的承载能力。

我们发现一般情况对数据库而言都是“读多写少”,也就说对数据库读取数据的压力比较大,这样分析可以采用数据库集群的方案。其中一个是主库,负责写入数据,我们称为写库;其它都是从库,负责读取数据,我们称为读库。这样可以缓解一台服务器的访问压力。

MySql自带主从复制功能,我们可以使用主从复制的主库作为写库,从库和主库进行数据同步,那么可以使用多个从库作为读库,已完成读写分离的效果。

4.4 数据库集群

如果访问量非常大,虽然使用读写分离能够缓解压力,但是一旦写操作一台服务器都不能承受了,这个时候我们就需要考虑使用多台服务器实现写操作。

例如:可以使用MyCat搭建MySql集群,对ID3的余数,这样可以把数据分别存放到3台不同的服务器上,由MyCat负责维护集群节点的使用。

五、硬件优化

服务器硬件的性能瓶颈,直接决定MySQL数据库的运行速度和效率。可以从以下几个方面考虑:

5.1 内存

足够大的内存,是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多,可以增加系统的缓冲区容量,使数据在内存停留的时间更长,以减少磁盘的IO。服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存。

5.2 磁盘

MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量在100万PV以上的系统,由于磁盘I/O的制约,MySQL的性能会非常低下,考虑以下几种解决方案:

  • 使用SSD或者PCIe SSD设备,至少获得数百倍甚至万倍的IOPS提升;
  • 购置阵列卡,可明显提升IOPS
  • 尽可能选用RAID-10,而非RAID-5
  • 使用机械盘的话,尽可能选择高转速的,例如选用15000RPM,而不是7200RPM的盘

5.3 CPU

CPU仅仅只能决定运算速度,即使是运算速度都还取决于与内存之间的总线带宽以及内存本身的速度。但是一般情况下,我们都需要选择计算速度较快的CPU

关闭节能模式。操作系统和CPU硬件配合,系统不繁忙的时候,为了节约电能和降低温度,它会将CPU降频。这对环保人士和抵制地球变暖来说是一个福音,但是对MySQL来说,可能是一个灾难。为了保证MySQL能够充分利用CPU的资源,建议设置CPU为最大性能模式。

5.4 网络

应该尽可能选择网络延时低,吞吐量高的设备。

  • 网络延时:不同的网络设备其延时会有差异,延时自然是越小越好。
  • 吞吐量:对于数据库集群来说,各个节点之间的网络吞吐量可能直接决定集群的处理能力。

六、缓存优化

6.1 查询缓存

query_cache_size:作用于整个MySQL,主要用来缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。查询缓存从MySQL 5.7.20开始已被弃用,并在MySQL 8.0中被删除。

当我们打开了Query Cache功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求,MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache中直接查找是否已经缓存。如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。

当然,Query Cache也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失。

如果缓存命中率非常高的话,有测试表明在极端情况下可以提高效率238%,而在糟糕时,QC会降低系统13%的处理能力。

通过以下命令查看缓存相关变量

show variables like '%query_cache%';
  • have_query_cache:表示此版本mysql是否支持缓存
  • query_cache_limit:缓存最大值
  • query_cache_size:缓存大小
  • query_cache_type:off表示不缓存,on表示缓存所有结果。

6.2 全局缓存

数据库属于IO密集型的应用程序,其主职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是IO,尽可能将磁盘IO转化为内存IO,也就是使用缓存

启动MySQL时就要分配并且总是存在的全局缓存,可以在MySQLmy.conf或者my.ini文件的[mysqld]组中配置。查询缓存属于全局缓存。

目前有:

  1. key_buffer_size(默认值:402653184,即384M)、
  2. innodb_buffer_pool_size(默认值:134217728即:128M)、
  3. innodb_additional_mem_pool_size(默认值:8388608即:8M)、
  4. innodb_log_buffer_size(默认值:8388608即:8M)、
  5. query_cache_size(默认值:33554432即:32M)

  • key_buffer_size
    用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),对MyISAM表性能影响最大的一个参数。如果你使它太大,系统将开始换页并且真的变慢了。

    严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M384M.

  • innodb_buffer_pool_size
    主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。InnoDB占用的内存,除innodb_buffer_pool_size用于存储页面缓存数据外,另外正常情况下还有大约8%的开销,主要用在每个缓存页帧的描述、adaptive hash等数据结构,如果不是安全关闭,启动时还要恢复的话,还要另开大约12%的内存用于恢复,两者相加就有差不多21%的开销。

  • innodb_additional_mem_pool_size
    设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,所以当我们一个MySQL Instance中的数据库对象非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

  • innodb_log_buffer_size
    这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog BufferInnoDB在写事务日志的时候,为了提高性能,也是先将信息写入Innodb Log Buffer中,当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件(或者同步到磁盘)中。可以通过innodb_log_buffer_size参数设置其可以使用的最大内存空间。

    InnoDB将日志写入日志磁盘文件前的缓冲大小。理想值为1M8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。因此,如果有大的事务处理,设置大的日志缓冲可以减少磁盘I/O。这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

6.3 局部缓存

除了全局缓冲,MySql还会为每个连接发放连接缓冲。每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配大约read_buffer_sizesort_buffer_sizeread_rnd_buffer_sizetmp_table_size大小的内存空间。不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。

  • read_buffer_size
    MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能.

  • sort_buffer_size
    MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小

  • read_rnd_buffer_size
    MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

  • tmp_table_size
    MySqlheap(堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表(所有列的长度的和)或包含BLOB列的表存储在硬盘上。

    如果某个内部heap(堆积)表大小超过tmp_table_sizeMySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySql同时将增加heap表的大小,可达到提高联接查询速度的效果。

  • record_buffer
    record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。

6.4 其它缓存

  • table_cache
    TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE),table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。

    不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

  • thread_cache_size
    服务器线程缓存,默认的thread_cache_size=8,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,

    增加这个值可以改善系统性能。通过比较ConnectionsThreads_created状态的变量,可以看到这个变量的作用。

七、MySQL服务器优化

7.1 MySQL参数

通过优化MySQL的参数可以提高资源利用率,从而达到提高MySQL服务器性能的目的。MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中,常用的参数如下:

  • back_log
    MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中(每个连接256kb,占用:125M)。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。

  • wait_timeout
    MySQL连接闲置,超过一定时间后将会被强行关闭。MySQL默认的wait-timeout值为8个小时。

    设置这个值是非常有意义的,比如你的网站有大量的MySQL链接请求(每个MySQL连接都是要内存资源开销的),由于你的程序的原因有大量的连接请求空闲啥事也不干,白白占用内存资源,或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态(可用showprocesslist),如果经常发现MYSQL中有大量的Sleep进程,则需要修改wait-timeout值了。

  • max_connections
    是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。

    MySQL服务器允许的最大连接数16384

  • max_user_connections
    是指每个数据库用户的最大连接针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

  • thread_concurrency
    的值的正确与否,对mysql的性能影响很大,在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值,会导致mysql不能充分利用多cpu(或多核),出现同一时刻只能一个cpu(或核)在工作的情况thread_concurrency应设为CPU核数的2倍。

  • skip-name-resolve
    禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

  • default-storage-engine
    default-storage-engine=InnoDB(设置InnoDB类型,另外还可以设置MyISAM类型)设置创建数据库及表默认存储类型

7.2 Linux系统优化

一般情况,我们都会使用Linux来进行MySQL的安装和部署,Linux系统在使用的时候,也需要进行相关的配置,以提高MySQL的使用性能,这里列举以下几点:

  • 避免使用Swap交换分区,因为交换时是从硬盘读取的,速度很慢。
  • 将操作系统和数据分区分开,不仅仅是逻辑上,还包括物理上,因为操作系统的读写会影响数据库的性能。
  • MySQL临时空间和复制日志与数据放到不同的分区,数据库后台从磁盘进行读写时会影响数据库的性能。
  • 避免使用软件磁盘阵列,使用硬件磁盘阵列。
  • Linux中设置swappiness的值为0,因为在数据库服务器中不需要缓存文件。
  • 使用noatimenodirtime挂载文件系统,因为不需要对数据库文件修改时间。
  • 使用XFS文件系统,一种比ext3更快、更小的文件系统。
  • 调整XFS文件系统日志和缓冲变量–为了最高性能标准。
  • 使用64位的操作系统,这会支持更大的内存。
  • 删除服务器上未使用的安装包和守护进程,节省系统的资源占用。
  • 把使用MySQLhost和你的MySQL host放到一个hosts文件中。

https://gitcode.csdn.net/65eec91f1a836825ed79d747.html

https://cloud.tencent.com/developer/article/2307355

http://www.hskmm.com/?act=detail&tid=18308

相关文章:

  • 关于“悬荡悟空”决策机制的简要技术说明
  • 最小二乘问题详解1:线性最小二乘
  • 9月26日
  • 工程监理行业多模态视觉​​​​​​​大模型系统,打造工地行业全场景的监理智能生态
  • 完整教程:【鸿蒙心迹】摸蓝图,打地基
  • 正则表达式
  • LuatOS Air780EPM 实现 HTTP 通信:从原理到代码实践
  • 搜维尔科技:Senseglove Nova 2触觉手套:虚拟训练、VR/AR模拟和研究中的触觉反馈
  • 深入解析:盟接之桥EDI软件:中国制造全球化进程中的连接挑战与路径探索
  • 【STM32H7】基于CubeMX从零开始搭建的HAL库工程模板(包含串口重定向和DSP库)
  • 在Windows架构中安装Miniforge及python环境变量配置
  • 搜维尔科技:Force Dimension Omega力反馈设备遥操作工业机器人
  • 3. Ollama 安装,流式输出,多模态,思考模型 - Rainbow
  • C++程序练习(部分未完全完成)
  • C#性能优化基础:垃圾回收机制
  • 实验报告1
  • 2025.9.26——1蓝
  • 根号
  • 【A】杂题选将
  • 有一个[1,5]的等概率随机函数fx(),在不改变fx()函数的情况下,利用fx()函数做出一个[1,7]的等概率随机函数。
  • WSL2 磁盘清理
  • 质因数分解
  • 关于OneBot的QQ机器人探索2
  • putty
  • 深入解析:PHP 8.0+ 高级特性深度探索:架构设计与性能优化
  • 客服系统源码二次开发
  • 喜讯!狮桥集团成为天津市行政执法监督企业联系点,共筑法治营商新环境!
  • redis实现分布式锁2
  • 题解:P7334 [JRKSJ R1] 吊打
  • 当不小心误触了一个事件该如何删除呢