68. MySQL数据库优化¶
1. 优化哲学¶
1.优化不总是对一个单纯的环境进行！还很可能是一个复杂的已投产的系统
2.优化手段本来就有很大的风险，只不过你没能力意识到和预见到
3.任何的技术可以解决一个问题，单必然存在带来另一个问题的风险
4.对于优化来说，解决问题而带来的问题控制在可接受的范围内才是有成果
5.保持现状或出现更差的情况都是失败
6.稳定性和业务可持续性，通常比性能更重要
7.优化不可避免涉及到变更，变更就有风险
8.优化使性能变好、维持和变差是等概率事件
9.优化不能只是数据库管理员担当风险，但会所有的人分享优化成果
10.所以优化工作是由业务需要驱使的
#谁参与优化？
1.数据库管理员
2.业务部门代表
3.应用程序架构师
4.应用程序设计人员
5.应用程序开发人员
6.硬件及系统管理员
7.存储管理员
2. 优化方向¶
#1.优化角度
-安全优化（业务持续性）
-性能优化（业务高效性）
#2.优化范围
1.存储、主机和操作系统
-主机架构稳定性
-I/O规划及配置
-swap
-os内核参数和网络问题
2.应用程序
-应用程序稳定性
-SQL语句性能
-串行访问资源
-性能欠佳会话管理
3.数据库优化
-内存
-数据库结构（物理&逻辑）
-实例配置
3. 优化工具定位问题¶
3.1 优化工具------定位问题¶
#操作系统
top
iostat
vmstat
nmon
dstat
#数据库优化
#基础优化命令工具
mysql
show global status
show engine innodb status
show processlist
show index
information schema
mysqldumpslow
explain
mysqladmin
mysqlslow
#深度优化命令工具（扩展）
mysqlslap
sysbench
mysql profiling
performance schema
tpcc
3.2 top命令¶
#主要看cpu和内存！！！
top - 15:39:23 up 19 days, 17:42, 1 user, load average: 0.21, 0.35, 0.43
Tasks: 184 total, 1 running, 183 sleeping, 0 stopped, 0 zombie
%Cpu(s): 9.1 us, 6.4 sy, 0.0 ni, 84.2 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 1882232 total, 82940 free, 1050604 used, 748688 buff/cache
KiB Swap: 1049596 total, 1049596 free, 0 used. 623632 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1519 kube 20 0 165812 100060 7600 S 3.0 5.3 116:40.31 kube-apiserver
2067 root 20 0 754600 47804 8672 S 2.0 2.5 398:38.37 kubelet
5305 root 10 -10 142844 18924 5816 S 1.7 1.0 395:44.64 AliYunDun
530 kube 20 0 296904 38296 7612 S 0.7 2.0 134:30.64 kube-controller
943 root 20 0 294016 29772 6724 S 0.7 1.6 152:29.08 kube-proxy
11913 root 20 0 113180 1584 1332 S 0.7 0.1 0:00.08 bash
951 etcd 20 0 10.5g 108112 3432 S 0.3 5.7 117:25.11 etcd
1012 zabbix 20 0 79004 1668 680 S 0.3 0.1 108:08.03 zabbix_agentd
1564 root 20 0 532944 57300 5376 S 0.3 3.0 74:38.15 BT-Panel
1616 root 20 0 804080 39992 6252 S 0.3 2.1 98:38.51 dockerd-current
1623 root 20 0 452176 14284 1420 S 0.3 0.8 13:14.82 docker-containe
13053 root 20 0 162020 2380 1616 R 0.3 0.1 0:00.03 top
1 root 20 0 43772 3460 1944 S 0.0 0.2 1:42.90 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 5:11.98 ksoftirqd/0
5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
#1.第一行
top - 15:39:23 up 19 days, 17:42, 1 user, load average: 0.21, 0.35, 0.43
#当前时间、系统运行时间、在线用户数、平均负载
#2.第二行
Tasks: 184 total, 1 running, 183 sleeping, 0 stopped, 0 zombie
#总进程数量，正在运行的进程数、睡眠进程数、僵尸进程数
#3.第三行：cpu相关
%Cpu(s): 9.1 us, 6.4 sy, 0.0 ni, 84.2 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
9.1 us #用户程序占用的cpu时间百分比
6.4 sy #系统调用占用的cpu百分比
0.0 ni
84.2 id #cpu空闲百分比
0.3 wa #cpu等待的时间百分比
#%us 是用户程序占用的cpu时间百分比，在cpu的能力范围内，越高越好
#%id 是cpu的空闲程度，只要在cpu工作能力之内，cpu越繁忙越好，最好不要低于10%。一般购买cpu会冗余30%-40%
#%wa 是cpu等待的时间百分比。越低越好。cpu等什么呢？等待应用程序运行、等待文件从磁盘加载到内存、等需要的I/O资源。比如数据从磁盘加载到内存慢、数据量大（大事务，一次事务更新1000w条数据）、内存小、等待锁等。
#%sy 资源调配相关，越低越好。如果很高，有可能是bug，也可能是中病毒了。
#4.第四行：内存相关
KiB Mem : 1882232 total, 82940 free, 1050604 used, 748688 buff/cache
#内存相关：总内存大小、空闲内存大小、使用内存大小、缓存大小
#total 内存总量
#used 已经使用的内存量
#free 没有被使用的内存量
#buff 内存缓冲区，修改类数据缓冲区域
#chache 内存缓存，读取类数据缓存区域
#可用内存=free + buff + cache
#5.第五行
KiB Swap: 1049596 total, 1049596 free, 0 used. 623632 avail Mem
#swap相关：总swap大小、空闲swap大小、
#centos7里，当内存剩余30%时，会优先使用swap。但是swap使用特别频繁的话，会降低io性能
#swap是为了防止oom的问题（out of memory 内存溢出）
#mysql要求，不要使用swap！！！因为mysql是对io要求很高的产品！！
#查看swap设置参数：cat /proc/sys/vm/swappiness
[root@k8s-master ~]# cat /proc/sys/vm/swappiness
0
#云服务器一般默认是0！！！
[root@k8s-master ~]# free -m
total used free shared buff/cache available
Mem: 1838 1034 69 3 734 600
Swap: 1024 0 1024
[root@k8s-master ~]#
[root@k8s-master ~]#
[root@k8s-master ~]# free -h
total used free shared buff/cache available
Mem: 1.8G 1.0G 69M 4.0M 734M 600M
Swap: 1.0G 0B 1.0G
#total = free + used + buff + cache
#used = RSS + anonymous
#page = buff + cache
#buff:数据缓冲区，负责修改类的操作缓冲
#cache：缓存，负责查询类的数据的缓存
3.3 iostat命令¶
#测试本机的io水平
#以MB为单位，每秒刷新一次
[root@k8s-master ~]# iostat -dm 1
Linux 3.10.0-957.21.3.el7.x86_64 (k8s-master) 05/17/2021 _x86_64_ (1 CPU)
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 16.25 0.01 0.17 18539 294353
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 6.00 0.00 0.01 0 0
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 63.00 0.00 1.75 0 1
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 9.09 0.00 0.04 0 0
^C
#以KB为单位，没秒刷新一次
[root@k8s-master ~]# iostat -dk 1
Linux 3.10.0-957.21.3.el7.x86_64 (k8s-master) 05/17/2021 _x86_64_ (1 CPU)
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
vda 16.25 11.20 177.76 18984711 301420349
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
vda 13.00 0.00 100.00 0 100
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
vda 6.06 0.00 12.12 0 12
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
vda 29.29 0.00 529.29 0 524
^C
[root@k8s-master ~]#
#2.dd命令
[root@k8s-master ~]# dd if=/dev/zero of=/data/bigfile bs=1M count=4096
#产生一个4096*1M=4G的文件/data/bigfile。 注意：/data目录必须事先存在！！！
#一般IO是结合CPU一起查看的，我们认为cpu us%和IO应该是成正比的！
#3.测试阿里云服务器io性能
#执行命令测试！
#dd
[root@k8s-master ~]# dd if=/dev/zero of=/data/bigfile bs=1M count=4096
4096+0 records in
4096+0 records out
4294967296 bytes (4.3 GB) copied, 30.8923 s, 139 MB/s
[root@k8s-master ~]# ll -h /data
total 4.1G
-rw-r--r-- 1 root root 4.0G May 17 16:21 bigfile
# top
top - 16:21:29 up 19 days, 18:25, 3 users, load average: 4.31, 1.35, 0.80
Tasks: 200 total, 2 running, 198 sleeping, 0 stopped, 0 zombie
%Cpu(s): 4.2 us, 20.1 sy, 0.0 ni, 0.0 id, 75.8 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 1882232 total, 79008 free, 1072776 used, 730448 buff/cache
KiB Swap: 1049596 total, 1049596 free, 0 used. 610896 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26538 root 20 0 109032 1668 548 D 9.6 0.1 0:02.37 dd
#iostat
vda 559.57 18.36 129.84 17 122
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 723.16 20.17 129.14 19 122
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 554.74 17.75 128.98 16 122
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 677.66 15.39 132.24 14 124
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 529.79 19.46 130.15 18 122
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 577.66 7.55 137.62 7 129
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 686.32 16.52 135.72 15 128
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 2210.42 83.34 46.11 80 44
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 1606.06 18.27 0.26 18 0
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
#4.场景1：cpu 的us很高，io很闲，read和write少，IOPS高
#原因：由于过度条带化
#5.场景2：cpu的wa很高，io read和write少 tps不高
#原因：io慢
#对于数据库来讲：
锁
随机IO过多（如全盘扫描，不走索引）
排序、分组、多表连接
4. 硬件层面优化¶
#1.cpu选择：根据数据库类型
cpu密集型：数据分析数据处理，OLAP，需要cpu高运算能力。如I系列，主频很高，核心少
io密集型：线上系统，OLTP主要是IO密集型的业务，高并发。如E系列（至强），主频相对低，核心数量多
#2.内存选择
建议2-3倍的cpu核心数量
#3.磁盘选择
SATA-III
SAS
Fc
SSD
pci-e ssd
Flash
#4.存储（根据业务特点选择，大部分业务都是80%以上是读）
r0：条带化，性能高
r1：镜像，安全
r5：校验+条带化，安全较高+性能较高（读），写性能较低（适合读多写少）
r10：安全+性能多很高，最少4块盘，但是浪费一半的空间（适合高io要求）
#5.IOPS：每秒磁盘最多能发生的io次数，是一个定值
#避免过度条带化，过度条带化会达到iops限制
5. 系统层面优化¶
5.1 swap调整¶
#1.临时
echo 0 > /proc/sys/vm/swappiness
#2.永久
vim /etc/sysctl.conf
#添加如下参数
vm.swappiness=0
5.2 io调度策略¶
#centos7默认是deadline
#centos6才需要调用，7不需要！！！
#1.查看调度策略
cat /sys/block/sda/queue/scheduler #阿里云服务器没有
#2.临时生效
echo deadline > /sys/block/sda/queue/scheduler
#3.永久生效
vim /boot/grub/grub.conf #阿里云服务器没有
6. 参数优化¶
6.1 max_connections¶
#1.简介
max_connection:mysql客户端的最大连接数。
#如果服务器的并发请求量比较大，可以调高这个值。
#当然这是建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销更多的内存，所以需要适当的调整该值。
#2.判断依据
show variables like 'max_connections';
show status like 'Max_used_connections';
#如果max_used_connections跟max——connections相同，那么就是max_connections设置过低或者超过服务器的负载上线了。低于10%则设置过大
#3.修改方式
vim /etc/my.cnf
Max_connections=1024
#4.补充
1.开启数据库时，我们可以临时设置一个比较大的测试值。
2.观察max_used_connections的变化
3.如果max_used_conncetions跟max_connections相同，那么就是max_connections设置过低或者超过服务器的负载的上限了，低于10%则设置过大
6.2 back_log¶
#1.简介
back_log：mysql能暂存的连接数量
#当主要mysql线程在一个很短时间内得到非常多的连接请求时，他就会起作用。
#如果mysql的连接数达到max_connections的时候，信赖的请求将会被存在堆栈中，等待某一个连接释放资源，该堆栈的数量及back_log
#如果等待连接的数量超过back_log，将不被授予连接资源
#back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以倍存在堆栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
#2.判断依据
show full processlist
#发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
#3.修改方式举例
vim /etc/my.cnf
back_log=1024
#注意：不能设置太大，因为是放在内存中。如果内存大的话，则可以设置的大一点
6.3 wait_timeout和interactive_timeout¶
#1.简介
#wait_timeout: 指的是mysql在关闭一个非交互的连接之前所需要等待的秒数
#interactive_timeout: 指的是mysql在关闭一个交互的连接之前所需要等待的的秒数
比如，我们在终端上进行mysql管理，使用的就是交互的连接。
这时候，如果没有操作的时间超过了interactive_time设置的时间，就会自动的断开。
默认的是28800，可调优为7200.
wait_timeout如果设置的太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
#2.设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist的时候，能看到很多的连接。一般希望wait_timeout尽可能的低
#3.修改方式举例
wati_timeout=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设置的比较小。
interactive_time要和应用开发人员沟通长连接的应用是否很多。
如果他需要长连接，那么这个值可以不需要调整。
另外，还可以使用类外的参数弥补。
#很多应用都是长连接，比如主从复制
6.4 key_buffer_size¶
#1.简介
#key_buffer_size：指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
1.此参数与myisam表的索引有关
2.临时表的创建有关（多表连接、子查询、union）
在有以上查询语句(如order by,join,subquery,union,distinct)出现的时候，需要创建临时表，用完之后就会被丢弃（计算类的操作，都需要使用临时表）
临时表有2种创建方式
内存中——key_buffer_size
磁盘上——ibdata1（5.6）ibtmp1（5.7）
#2.设置依据
show variables like "key_buffer_size";
默认值是8388608
show status like "key_read%";
key_read_requests:一共有多少个索引读请求
key_reads:直接从硬盘中读取的索引数
#注意：key_buffer_size只对myisam表起作用。即使不使用myisam表，但是内部的临时磁盘表也是myisam表，也要使用该值。
#可以使用检查状态值的参数created_tmp_disk_tables得知。
show status like "created_tmp%";
#略，碰到后查OneNote笔记！
#3.配置方法
key_buffer_size=64M
6.5 query_cache_size¶
#1.简介
查询缓存，简称QC。
使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写），将直接从缓冲区中读取结果。
例如：select * from t1 where id=10;
1.将此查询进行hash运算计算出一串hash值，称之为SQL_ID.
2.将存储引擎返回上来的表的内容+SQL_ID存储到查询缓存中
#2.使用方式
1.一条sql语句执行时，进行hash运算，得出sql_id，去找query cache
2.如果cache中有，则直接返回数据行，如果没有，就走原有的sql执行流程
一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。
#注意：2个sql语句，只要是相差那么是一个字符（列如果大小写不一样，多一个空格等），那么这2个sql将使用不同的一个cache。
#3.判断依据
show variables like "%query_cache";
show status like "%Qcache%";
Qcache_free_blocks:缓存中相邻内存块的个数。如果该值显示较大，则说明Query cache中的内存碎片较多。
flush query cache会对缓存中的碎片进行整理，从而得到一个空闲块。
#注：当一个表被更新之后，和它相关的cache blocks将会被free。但是这个block依然可能存在队列中，除非是在队列的尾部。
可以使用flush query cache语句来清空free blocks
Qcache_free_memory:Querycache中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的query cache内存大小是否足够，是需要增加还是过多了。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字约到，缓存效果越理想。
Qcache_inserts:表示多少次未命中然后插入，意思是信赖的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后，把结果insert到查询缓存中。这样的情况次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的。这很正常。
Qcache_lowmem_prunes:多少天query因为内存不足而被清除出querycache。
通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能够清楚的了解到我们系统中query cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有query被换出。
这个数字最好长时间来看，如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉你属于哪种情况）
Qcache_not_cached:不适合进行缓存的查询的数量，通常是由于这些查询不是select语句或者用了now（）之类的函数
6.6 max_connect_errors¶
#1.介绍
max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况。
当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令来清空此host的相关信息。
max_connect_errors的值与性能并无太大关系。
#2.设置
vim /etc/my.cnf
max_connect_errors=2000
6.7 sort_buffer_size¶
#1.介绍
每个需要进行排序的线程分配改大小的一个缓冲区。
增加这个值会加速
order by
group by
distinct
union
#2.配置依据
sort_buffer_size并不是越大越好。由于是connection级别的参数，过大的设置+高并发可能会耗尽系统内存资源。
#3.配置方法
vim /etc/my.cnf
sort_buffer_size=1M
6.8 max_allowed_packet¶
#1.简介
mysql根据配置文件会显示server接收的数据包的大小
#2.配置依据
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。必须设置1024的倍数
#3.配置方法
max_allowed_packet=32M
#服务端是限制写入操作。客户端影响的是读的操作。
#当看到报错时在设置也不迟
set global max_allowed_packet=32M
#备份时，可能某些文件太大备份不了，可以指定参数
mysqldump --max_allowed_packet=128M
6.9 join_buffer_size¶
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
尽量在sql语句方面进行优化，效果较为明显。
优化的方法：在on条件列加索引，至少应当是有mul索引。
6.10 thread_cache_size¶
#1.简介
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量。
当断开连接时，那么客户端的线程将被放到缓存中以影响下一个客户而不是销毁（前提是缓存数未达到上限）。
如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建。如果有很多新的线程，增加这个值可以改善系统性能。
#2.配置依据
通过比较connections和threads_created状态的变量，可以看到这个变量的作用。
设置规则如下：1GB内存配置为8，2G配置为16，3G配置为32，4G或更高的内存，可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达到上限）
show status like "threads_%";
threads_cached:代表当前此时此刻线程缓存中有多少个空闲线程。
threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
threads_created:代表从最近一次服务启动，已创建线程的数量。如果发现threads_created值过大的话，表名MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中的thread_cache_size值。
threads_running:代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数。有时候连接已建立，但是连接处于sleep状态
#3.配置方法
thread_cache_size=32
#整理
threads_created: 一般在结构设计阶段，会设置一个测试值，做压力测试。
结合zabbix监控，看一段时间内此状态的变化。
如果在一段时间内，threads_created趋于平稳，说明对应参数设定是ok
如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）
6.11 innodb_buffer_pool_size（重点！）¶
#1.简介
对于innodb表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于myisam表的作用。
#2.配置依据
innodb使用该参数指定大小的内存来缓冲数据和索引
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%，一般我们建议不要超过物理内存的70%
#3.配置方法
innodb_buffer_pool_size=2048M
#4.使用压力测试
QPS：query per seconds 每秒能做多少次select
TPS：transaction per seconds 每秒能做多少次事务
可以通过如下命令查看
show engine innodb status\G
6.12 innodb_flush_log_at_trx_commit（重点！双一标准之一）¶
#1.简介
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2.
0，表示当事务提交时，不做日志写入操作，而是每秒将log buffer中的数据写入日志文件并flush磁盘一次
1，表示在每秒钟或每次事务的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的acid
2，表示每次事务提交引起写入日志文件的动作，但每秒完成一次flush磁盘操作
#2.配置依据
实际测试发现，该值对于插入数据的速度影响非常大，设置为2时，插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，mysql手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设置为0或者2
#3.配置方法
innodb_flush_log_at_trx_commit=1
6.13 innodb_thread_concurrency¶
#1.简介
此参数用来设置innodb线程的并发数量，默认值为0表示不限制
#2.配置依据
官方文档建议：
如果一个工作负载中，并发用户线程的数量小于64，建议设置为0
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置为128，并通过不断降低这个参数，直到发现能够提供最佳性能的线程数。
#3.设置标准
1.当前系统cpu使用情况均不均匀
2.当前的连接数，有没有达到顶峰
show status like 'threas_%';
show processlist;
#4.配置方法
innodb_thread_concurrency=8
6.14 innodb_log_buffer_size¶
#1.介绍
此参数确定写日志文件所用内存大小，以MB为单位。
缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小
innodb_log_buffer_size=8M
#2.设置依据
1.大事务：存储过程调用call
2.多事务
show innodb status\G
6.15 innodb_log_file_size=50M¶
此参数确定数据日志文件的大小，以MB为单位，更大的设置可以提高性能
innodb_log_file_size=100M
6.16 innodb_log_file_in_group=3¶
为了提高性能，MySQL可以以循环方式将日志文件写到多个文件，推荐设置为3
#15和16通常一起使用，来提高性能
6.18 read_rnd_buffer_size=1M¶
mysql的随机读（查询操作）缓冲区大小。
当按任意顺序读取行时（如按照排序顺序），如果需要排序大量数据，可适当调高该值。但mysql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
#注：顺序读是指根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键虚招实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
6.19 bulk_insert_buffer_size=8M¶
批量插入数据缓存大小，可以有效提高插入效率，默认为8M
6.20 binary log （重点！双一标准之一）¶
log-bin=/data/mysql-bin
binlog_cache_size=2M //为每个session分配的内存，在事务的过程中用来存储二进制日志的缓存，提高记录bin-log的效率。
没有什么大事务，dml也不是很频繁的情况下，可以设置小一点。建议1M
如果事务大而且多，dml操作也频繁，则可以适当的调大点。建议2-4M
max_binlog_cache_size=8M //表示的是binlog能够使用的最大cache内存大小
max_binlog_size=512M //指定binlog日志文件的大小。如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或者小于4096字节。默认是1GB。
在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days=7 //定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认是0，表示没有自动删除。根据全备周期设置该参数。
log-bin/data/mysql-bin
binlog_format=row
sync_binlog=1 #双一标准之一
set sql_log_bin=0
show status like 'com_%';
6.21 双一标准¶
#1.双一标准：基于安全的控制
sync_binlog=1 #每次事务commit，都会将binlog写入磁盘
innodb_flush_log_at_trx_commit=1 #每次事务commit，都会将redo写入磁盘
#如果都是0，则是性能比较高
#2.最安全的模式
innodb_flush_log_at_trx_commit=1
innodb_flush_method=0_DIRECT
#3.最高性能模式
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fdatasync
#一般情况下，我们更偏向于安全
#4.双一标准+刷写策略
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=0_DIRECT
7. 参数优化配置文件¶
vim /etc/my.cnf
[mysqld]
#基础参数
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
#主从复制参数
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
#从库做为其他从库的主库时 log-slave-updates参数是必须要添加的，因为从库要作为其他从库的主库，必须添加该参数。该参数就是为了让从库从主库复制数据时可以写入到binlog日志，为什么要用这个参数写binlog日志呢，不是在配置文件中开启log-bin = /data/3307/mysql-bin选项就可以吗？
#答：从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。
#指定安全导出目录
secure-file-priv=/tmp
#优化参数如下，可以先查询一下默认值，再设置
relay_log_purge=0 #是否自动清空不再需要的中继日志。默认值为1(启用)。
#1.
max_connections=1024 #最大的客户端连接数
back_log=128 #暂存的客户端连接数。当大并发达到上一个参数设置的值时，其他的128个就会存在堆栈中，超过128个则不被授予连接资源
#2.
wait_timeout=60 #mysql关闭一个非交互的连接所需要等待的时间
interactive_timeout=7200 #mysql关闭一个交互的连接所需要等待的时间
#3.
query_cache_size=64M #查询缓存的大小。一条select语句执行后，再次执行则从缓存中读取，速度块
query_cache_type=1 #开启查询缓存？
query_cache_limit=50M
#4.
max_connect_errors=20 #客户端最大的错误连接次数。防止暴力破解密码。
#5.
sort_buffer_size=2M #排序查询缓存的大小
join_buffer_size=2M #多表查询缓存的大小
#6.
max_allowed_packet=32M #服务端接收的最大的数据包的大小
#7.
thread_cache_size=200 #服务器线程的缓存大小
#8.
key_buffer_size=16M #索引缓冲区的大小，只对myisam表（临时表）起作用
#9.
innodb_buffer_pool_size=1024M #innodb表的数据和索引的缓存大小。与上面的参数作用类似。建议不要超过物理内存的70%
#10.
innodb_flush_log_at_trx_commit=1 #控制将事务的log刷写到磁盘的策略，双1标准之一
innodb_log_buffer_size=32M #写日志文件的缓存大小
#11.
innodb_log_file_size=128M #数据日志文件的大小
innodb_log_files_in_group=3 #与上一个参数同时使用，将日志文件写入3个文件，提高性能。
#12.
binlog_cache_size=2M #二进制日志的缓存大小
max_binlog_cache_size=8M #二进制日志能够使用的最大的缓存大小
max_binlog_size=512M #二进制日志文件的最大大小
expire_logs_days=7 #二进制日志文件的过期清理时间
#13.
read_buffer_size=2M #读缓存大小
read_rnd_buffer_size=2M #随机读的缓存大小
bulk_insert_buffer_size=8M #批量插入数据的缓存大小，默认8M
#read_buffer_size：（数据文件存储顺序）是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率 数据文件顺序。
#read_rnd_buffer_size：是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。
#————————————————
#版权声明：本文为CSDN博主「WEI_WB」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
#原文链接：https://blog.csdn.net/wei_wenbo/article/details/50817692
[client]
socket=/tmp/mysql.sock
#不带中文注释的配置文件内容
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
secure-file-priv=/tmp
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M #该参数根据实际设置！
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
最后更新:
2022-02-20 11:21:58