MySQL-性能优化_影响MySQL性能的因素分析及解决方案

生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

---

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

---

影响性能的几个因素

• 硬件资源（CPU 、内存、磁盘等）

• 操作系统的区别

• MySQL的存储引擎的选择

  MyISAM： 不支持事务，表级锁
  InnoDB： 事务级存储引擎，完美支持行级锁，事务ACID特性。

  但 比不是说 MyISAM比InnoDB差，看使用场景

• 数据库参数设置

• 数据库结构设计和SQL语句 ,毫无疑问 这个是最最重要的因素 —> mysql 可以开启慢查询监控耗时长的SQL

---

CPU

更多的CPU or 更快的CPU ？

主要从以下几个方面考虑：

• （1）CPU密集型的应用？

分析下我们的应用的类型，是的话，要提高SQL的运行效率，那就需要更快的CPU。

还有一点需要注意 MySQL不支持多CPU对同一SQL的并发处理，也就是说一个SQL只能运行在一个CPU的核上。

---

• （2） 系统的并发量如何 ？

一个SQL只能跑在一个CPU上，如果有32个CPU呢 ，是不是同时可以跑32个SQL呢？ 就是我们前面说的QPS 。 一般web应用，CPU的核心数量比CPU的主频更重要。

---

• （3） MySQL的版本 ？

5.6、 5.7以上的版本，对多核CPU的支持更好。

---

• （4） 64位操作系统 OR 32位操作系统

32的操作系统，进程所能寻址的最大内存也就4G。

总结

• 对于并发较高的场景，CPU的数量比频率重要
• 对CPU密集型场景和复杂的SQL 频率越高越好

---

内存

MyISAM : 索引 缓存在内存 ，数据通过OS来缓存

InnoDB : 索引和数据都是通过内存来缓存

举个例子， 100G的数据量， 内存64G，这个时候增加内存对性能有提高。 如果内存是256G，还是慢，那就是其他问题了。

当然了，内存多了，对于数据的读和写都是好处的，都可以利用缓存来提高读写性能。

如何选择内存呢？ ----> 建议选择主板支持的最大内存频率， 跟CPU一个道理。

内存的型号，保持一致， 单个内存尽可能的大，都是比较好的选择。

配置的时候，要考虑数据的增长，比如100G的数据， 128G内存可能已经满足了要求，但数据可能增长较快，这个时候256的更佳。

总结

• 选择主板所能使用的最高频率的内存
• 内存尽可能的多

---

磁盘的配置和选择

主流的4种

• 使用传统的机械硬盘
• 使用RAID增强传统硬盘的性能
• 使用固态存储SSD和PCIe卡
• 使用网络存储NAS和SAN

传统的机械硬盘

传统的机械硬盘的读取数据的过程

1. 移动磁头到磁盘表面上正确的位置
2. 等待磁盘旋转，使所需的数据在磁头之下 ----> 访问时间
3. 等磁盘旋转过去，所需的数据被磁头读出 ------> 传输速度

考虑的话 1. 存储容量 2.传输速度 3 访问时间 4 主轴的转速（常见的7200转、1万5千转等等） 5. 物理尺寸

---

RAID

RAID ： 磁盘冗余队列 （Redundant Arrays of Independent Disks ）

RAID是一种将多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术 。

Linux-Raid0、Raid1、Raid5、Raid10初探

---

SSD 或者 PCIe卡

SSD

• 比普通的机械盘有更好的随机读写性能
• 支持更好的并发， I/O 性能好
• 缺点： 长时间密集的写，容易造成损坏

特点：

• 可以使用SATA接口，可以替换传统的磁盘而不需要任何的改动。
• SATA接口的SSD同样支持RAID
• SATA的SSD 3.0 接口如果放到了 2.0接口上，受2.0接口的性能影响，性能会下降

举个例子 SATA3.0接口 6Gbps , 放到SATA2.0接口上 最多只能用到2.0的 3Gbps

---

PCIe 卡（Fushion IO）

• 无法使用SATA接口，需要独特的驱动和配置
• 比SSD性能好，但价格也比SSD贵
• PCIe 也会吃服务器的内存
• 支持PCIe的Raid控制器比较少，而且成本相当高

---

固态存储使用的场景

• 大量随机I/O的场景
• 解决单线程负载的I/O 瓶颈

举个例子

主从节点的MYSQL， 有一个SSD， 给哪个节点用呢 ？

----> 从节点 上用。 主节点上写 ，是多线程， 从节点的复制，单线程，为了减少延迟，建议放到从服务器上。

---

网络存储SAN （光纤传输）和 NAS (带宽传输)

SAN： Storage Area Network

NAS： Network Attached Storage

是两种外部文件存储设备加载到服务器上的方法。

SAN设备通过光纤连接服务器，设备通过块接口访问，服务器可以将其当做硬盘使用。 可以承受大量的顺序读写，但随即读有的时候不如RAID

NAS 使用 宽带来传输，有延迟。

使用场景的话: 数据备份等 。

总结

• PCIe > SSD > Raid10 > 本地磁盘 > NAS

---

网络

网络带宽 —> 低延时 、吞吐量达

网络质量—> 少抖动

建议

• 采用高性能和高带宽的网络接口设备和交换机
• 对多个网卡进行绑定，增强可用性和带宽
• 尽可能的进行网络隔离

---

操作系统

windows 和 linux , 大小写的区别，可以通过修改MySQL的配置来调整。

以CentoOS为例来了解下系统的参数优化

内核相关参数 /etc/sysctl.conf

如需增加 可以直接追加到该文件的末尾

net.core.somaxconn=65535  # Linux kernel参数，表示socket监听的backlog(监听队列)上限
net.core.netdev_max_bakclog=65535 # 允许发送到队列中的数据包数目
net.ipv4.tcp_max_syn_backlog=65535 # #表示SYN队列长度，默认1024，改成65535，可以容纳更多等待连接的网络连接数。

net.ipv4.tcp_fin_timeout = 10 #表示如果套接字由本端要求关闭，这个参数决定了它保持在FIN-WAIT-2状态的时间
net.ipv4.tcp_tw_reuse = 1 #表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；
net.ipv4.tcp_tw_recycle = 1 #表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。

TCP/IP及内核参数优化调优

net.core.wmen_default = 87380 # 表示内核套接字发送缓存区默认的大小。
net.core.wmen_max = 16777216 # 表示内核套接字发送缓存区最大大小。
net.core.rmem_default = 87380 # 默认的TCP数据接收缓冲
net.core.rmem_max=16777216 # 最大的TCP数据接收缓冲

#单位是秒   可适当调整 
net.ipv4.tcp_keepalive_time = 120 # 有效时长
net.ipv4.tcp_keepalive_intvl = 30  # 探测失败时，重发的间隔
net.ipv4.tcp_keepalive_probes = 3  # 最多发送多少次探测消息

kernel.shmmax = 4294967295  # (4G)单个共享内存段的最大值，应该设置的足够大，以便能在一个共享段下容纳整个Innodb缓冲池的大小 。 可以取物理内存-1 byte 

vm.swappiness = 0 #  除非虚拟内存满了，否则不会使用交换分区

其实就是swap交换分区的设置， 当操作系统没有足够的内存时，就会将一些虚拟内存写到磁盘的交换区中，这样就会发生内存交换。

然后执行sysctl -p命令，使上述修改生效

---

资源限制的配置文件 /etc/security/limits.conf

# 文件句柄数量 

* soft nofile 65535
* hard nofile 65535

*  对所有用户生效
soft 当前系统生效的设置
hard 系统所能设置的最大值
nofile 表示所限制的资源是打开文件的最大数目
65535 限制的数目

这俩参数，重启系统才生效。

---

磁盘调度策略

I/O 调度算法再各个进程竞争磁盘I/O的时候担当了裁判的角色，以求得尽可能最好的整体I/O性能。

在linux下面列出4种调度算法

• CFQ (Completely Fair Queuing 完全公平的排队)(elevator=cfq) 默认策略
• Deadline (elevator=deadline)： 试图把每次请求的延迟降至最低
• NOOP (elevator=noop):FIFO队列形式处理
• Anticipatory (elevator=as):对读操作优化服务时间，在提供一个I/O的时候进行短时间等待，使进程能够提交到另外的I/O。

#查看当前IO

[root@artisan ~]#  cat /sys/block/sd*/queue/scheduler
noop [deadline] cfq 
[root@artisan ~]# 

---

文件系统

windows -----> FAT 和 NTFS

WinServer 只有 NTFS 这种可以选择

Linux —> EXT3 EXT4 XFS

EXT3 EXT4系统的挂载参数 /etc/fstab

# data的可配置选项

data=writeback | ordered |journal 


noatime 
nodiratime 



/dev/sda1/ext4 noatime,nodiratime,data-writeback 1 1

---

搞定MySQL