一、概述
一般的应用系统,读写比例在10:1
左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。
注意:优化有风险,涉足需谨慎!
优化可能带来的问题
- 优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统!
- 优化手段有很大的风险,一定要意识到和预见到!
- 任何的技术可以解决一个问题,但必然存在带来一个问题的风险!
- 对于优化来说调优而带来的问题,控制在可接受的范围内才是有成果。
- 保持现状或出现更差的情况都是失败!
优化的需求
- 稳定性和业务可持续性,通常比性能更重要!
- 优化不可避免涉及到变更,变更就有风险!
- 优化使性能变好,维持和变差是等概率事件!
- 优化应该是各部门协同,共同参与的工作,任何单一部门都不能对数据库进行优化!
所以优化工作,是由业务需要驱使的!!!
优化由谁参与
在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。
优化的方向
在数据库优化上有两个主要方向:即安全与性能。
- 安全 : 数据安全性
- 性能 : 数据的高性能访问
优化的维度
从上图中可以看出,我们把数据库优化分为四个纬度:硬件,系统配置,数据库表结构,SQL
及索引
- 硬件:CPU、内存、存储、网络设备等
- 系统配置:服务器系统、数据库服务参数等
- 数据库表结构:高可用、分库分表、读写分离、存储引擎、表设计等
- SQL及索引:SQL语句、索引使用等
优化成本效果:
- 从优化成本进行考虑:硬件 > 系统配置 > 数据库表结构 > SQL及索引
- 从优化效果进行考虑:硬件 < 系统配置 < 数据库表结构 < SQL及索引
数据库使用优化思路
在多数时候,我们进行调优不需要进行这么全面、大范围的调优,一般情况下,我们进行数据库层面的优化就可以了,那我们该如何调优的呢?
应急调优的思路:
针对突然的业务办理卡顿,无法进行正常的业务处理!需要立马解决的场景!
- show processlist(查看连接session状态)
- explain(分析查询计划),show index from tableName(分析索引)
- show status like ‘%lock%’; 查询锁状态
常规调优的思路:
针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。
- 开启慢查询日志,运行一天
- 查看
slowlog
,分析slowlog
,分析出查询慢的语句。 - 按照一定优先级,进行一个一个的排查所有慢语句。
- 分析
top sql
,进行explain
调试,查看语句执行时间。 - 调整索引或语句本身。
二、优化实践
2.1 查询优化
2.1.1 MySQL查询流程
我们该如何进行sql
优化呢,首先我们需要知道,sql
优化其实主要是解决查询的优化问题,所以我们先从数据库的查询开始入手,下面这幅图显示了查询的执行路径:
① 客户端将查询发送到服务器;
② 服务器检查查询缓存,如果找到了,就从缓存中返回结果,否则进行下一步。
③ 服务器解析,预处理。
④ 查询优化器优化查询
⑤ 生成执行计划,执行引擎调用存储引擎API
执行查询
⑥ 服务器将结果发送回客户端。
查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么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
修改完成后,重启mysql
:service 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
支持的存储引擎结果如下:
mysql
支持存储引擎有好几种,咱们这里主要讨论一下常用的Innodb
,MyISAM
存储引擎。
3.1 存储引擎介绍
3.1.1 InnoDB存储引擎
特点:
InnoDB
存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。相比较MyISAM
存储引擎,InnoDB
写的处理效率差一点并且会占用更多的磁盘空间保留数据和索引。- 提供了对数据库事务
ACID
(原子性Atomicity
、一致性Consistency
、隔离性Isolation
、持久性Durability
)的支持,实现了SQL
标准的四种隔离级别。 - 设计目标就是处理大容量的数据库系统,
MySQL
运行时InnoDB
会在内存中建立缓冲池,用于缓冲数据和索引。 - 执行“select count(*) from table”语句时需要扫描全表,因为使用
innodb
引擎的表不会保存表的具体行数,所以需要扫描整个表才能计算多少行。 InnoDB
引擎是行锁,粒度更小,所以写操作不会锁定全表,在并发较高时,使用InnoDB
会提升效率。即存在大量UPDATE
/INSERT
操作时,效率较高。
使用场景:
- 经常
UPDATE
/INSERT
的表,使用处理多并发的写请求 - 支持事务,只能选出
InnoDB
。 - 可以从灾难中恢复(日志+事务回滚)
- 外键约束、列属性
AUTO_INCREMENT
支持
3.1.2 MyISAM存储引擎
特点:
-
MyISAM
不支持事务,不支持外键,SELECT
/INSERT
为主的应用可以使用该引擎。 -
每个
MyISAM
在存储成3
个文件,扩展名分别是:- frm:存储表定义(表结构等信息)
- MYD(MYData),存储数据
- MYI(MYIndex),存储索引
-
不同
MyISAM
表的索引文件和数据文件可以放置到不同的路径下。 -
MyISAM
类型的表提供修复的工具,可以用CHECK TABLE
语句来检查MyISAM
表健康,并用REPAIR TABLE
语句修复一个损坏的MyISAM
表。 -
在
MySQL5.6
以前,只有MyISAM
支持Full-text
全文索引
使用场景:
- 经常
SELECT
的表,插入不频繁,查询非常频繁。 - 不支持事务。
- 做很多
count
的计算。
3.1.3 MyISAM和Innodb区别
InnoDB
和MyISAM
是许多人在使用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
值会使索引以及索引的统计信息变得很复杂。 - 对于只包含特定类型的字段,可以使用
enum
、set
等数据类型。 - 数值型字段的比较比字符串的比较效率高得多,字段类型尽量使用最小、最简单的数据类型。例如
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
,然后对ID
取10
的余数,将用户均匀的分配进这0-9
这10
个表中。查找的时候也按照这种规则,又快又方便。
有些表业务关联比较强,那么可以使用按时间划分的。例如:每天的数据量很大,需要每天新建一张表。这种业务类型就是需要高速插入,但是对于查询的效率不太关心。表越大,插入数据所需要索引维护的时间也就越长。
4.3 读写分离
大型网站会有大量的并发访问,如果还是传统的数据存储方案,只是靠一台服务器处理,如此多的数据库连接、读写操作,数据库必然会崩溃,数据丢失的话,后果更是不堪设想。这时候,我们需要考虑如何降低单台服务器的使用压力,提升整个数据库服务的承载能力。
我们发现一般情况对数据库而言都是“读多写少”,也就说对数据库读取数据的压力比较大,这样分析可以采用数据库集群的方案。其中一个是主库,负责写入数据,我们称为写库;其它都是从库,负责读取数据,我们称为读库。这样可以缓解一台服务器的访问压力。
MySql
自带主从复制功能,我们可以使用主从复制的主库作为写库,从库和主库进行数据同步,那么可以使用多个从库作为读库,已完成读写分离的效果。
4.4 数据库集群
如果访问量非常大,虽然使用读写分离能够缓解压力,但是一旦写操作一台服务器都不能承受了,这个时候我们就需要考虑使用多台服务器实现写操作。
例如:可以使用MyCat
搭建MySql
集群,对ID
求3
的余数,这样可以把数据分别存放到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
时就要分配并且总是存在的全局缓存,可以在MySQL
的my.conf
或者my.ini
文件的[mysqld]
组中配置。查询缓存属于全局缓存。
目前有:
- key_buffer_size(默认值:402653184,即384M)、
- innodb_buffer_pool_size(默认值:134217728即:128M)、
- innodb_additional_mem_pool_size(默认值:8388608即:8M)、
- innodb_log_buffer_size(默认值:8388608即:8M)、
- query_cache_size(默认值:33554432即:32M)
-
key_buffer_size
用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),对MyISAM
表性能影响最大的一个参数。如果你使它太大,系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。对于内存在
4GB
左右的服务器该参数可设置为256M
或384M
. -
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 Buffer
。InnoDB
在写事务日志的时候,为了提高性能,也是先将信息写入Innodb Log Buffer
中,当满足innodb_flush_log_trx_commit
参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件(或者同步到磁盘)中。可以通过innodb_log_buffer_size
参数设置其可以使用的最大内存空间。InnoDB
将日志写入日志磁盘文件前的缓冲大小。理想值为1M
至8M
。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit
)。因此,如果有大的事务处理,设置大的日志缓冲可以减少磁盘I/O
。这个参数实际上还和另外的flush
参数相关。一般来说不建议超过32MB
。
6.3 局部缓存
除了全局缓冲,MySql
还会为每个连接发放连接缓冲。每个连接到MySQL
服务器的线程都需要有自己的缓冲。大概需要立刻分配256K
,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配大约read_buffer_size
,sort_buffer_size
,read_rnd_buffer_size
,tmp_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
是MySql
的heap
(堆积)表缓冲大小。所有联合在一个DML
指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP
)表。具有大的记录长度的临时表(所有列的长度的和)或包含BLOB
列的表存储在硬盘上。如果某个内部
heap
(堆积)表大小超过tmp_table_size
,MySQL
可以根据需要自动将内存中的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
,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能。通过比较
Connections
和Threads_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,因为在数据库服务器中不需要缓存文件。 - 使用
noatime
和nodirtime
挂载文件系统,因为不需要对数据库文件修改时间。 - 使用
XFS
文件系统,一种比ext3
更快、更小的文件系统。 - 调整
XFS
文件系统日志和缓冲变量–为了最高性能标准。 - 使用
64
位的操作系统,这会支持更大的内存。 - 删除服务器上未使用的安装包和守护进程,节省系统的资源占用。
- 把使用
MySQL
的host
和你的MySQL host
放到一个hosts
文件中。
https://gitcode.csdn.net/65eec91f1a836825ed79d747.html
https://cloud.tencent.com/developer/article/2307355