MySQL数据库架构--什么影响了MySQL性能-上

影响性能的几个方面

1. 服务器硬件的影响
包括：CPU，内存，磁盘IO等

2.服务器的操作系统的影响

3.数据库存储引擎的选择（插件式存储引擎）

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

4.数据库服务器的配置参数（影响最为关键）

不同的配置参数对性能的影响不同。

5.数据库结构设计和SQL语句的影响
数据库的优化重点在于数据库表结构设计和SQL语句的编写和优化

服务器硬件对性能的影响和优化

CPU资源和可用内存大小

当我们要使用的热数据大小远远超过内存大小的时候I/O系统可能就会成为我们系统的瓶颈。

因此CPU和可用内存大小，网络和磁盘I/O是影响性能的服务器硬件。

如何选择CPU？
CPU的频率和核心数的选择：

一些进程绝大多数时间在计算上，称为计算密集型（CPU密集型）computer-bound。
有一些进程则在input和output上花费了大多时间，称为I/O密集型，I/O-bound。比如搜索引擎蜘蛛大多时间是在等待相应这种就属于I/O密集型。

当应用是CPU密集型的时候，为了加快SQL语句的运行速度，应该选择更好的CPU而不是更多的。

衡量数据库处理能力的指标：QPS（同时处理SQL的数量是秒级别的）
当对并发量有要求的时候，则是增加核数。

64位架构CPU上使用32位的服务器版本时（MySQL是单线程的）

如何选择内存？（内存的I/O远大于其他的）
把数据缓存到内存中进行读取，可以大大提升数据库的性能

在常用的MySQL存储引擎中:

MyISAM：
会将索引缓存到内存
将数据通过操作系统进行缓存

InnoDB：
会同时在内存中缓存数据和索引，从而提高数据库的效率

我们需要明白一点，内存对性能的影响是有限的，并不能无限的增加性能。
数据库可以利用的内存是有限的，一般当缓存的数据和磁盘的数据一样时，即所有的数据被缓存到内存后，再增加内存的大小是没有意义的。

缓存不仅对读进行方便对写也是很方便。我们可以将多次写操作放到缓存中，然后一次写入到磁盘中。

内存的主频和服务器的主频一致，频率越高读取速度越快

磁盘的配置和选择

数据最终还是要保存到磁盘上，因此磁盘I/O还是影响数据库性能主要因素之一。

常用磁盘I/O：

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

传统机器磁盘：最常见，使用最多，价格低，存储空间大，读写较慢（读写速度却决于工作机制）

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

移动磁头到磁盘表面上的正确位置
等待磁盘旋转，使所需的数据在磁头之下
等待磁盘旋转过去，所有所需的数据都被磁头读出

1，2步称为访问时间，3步称为传输速度

磁盘执行这些步骤有多快，也就决定了磁盘的读写速度。

如何选择传统机器磁盘：

磁盘容量
传输速度
访问时间快的
主轴转速快的
物理尺寸（越小的时间越短）

使用RAID增加传统机器硬盘的性能:

什么是RAID：
RAID是磁盘冗余队列的简称（Redundant Arrays of Independent Disks）简单的说RAID的作用就是将多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。（增加传输速率）
RAID1（日志）的读写比RAID0（临时的）要快，RAID5的写较慢（要写校验位）读较快（使用在从服务器）。

使用固态存储SSD或PCIe卡

相比机械磁盘固态磁盘有更好的随机读写性能

相比机械磁盘固态磁盘能更好的支持并发

相比机械磁盘固态磁盘更容易损坏

使用场景：

适用于存在大量随机I/O的场景

使用于解决单线程负载的I/O瓶颈

使用网络存储SAN和NAS
SAN（Storage Area Network）和NAS（Network-Attached Storage）是两种外部文件存储设备加载到服务器上的方法。

关于SAN：
SAN设备通过光纤连接到服务器，设备通过光纤接口访问，服务器可以将其当作硬盘使用。
SAN能够支持大量顺序读写，这些读写I/O能够缓存和I/O合并。然而随机读写慢，不如本地RAID磁盘。

关于NAS：
NAS设备使用网络连接，通过基于文件的协议如NFS或SMB来访问。（受限于网络）

适用场景：数据库备份。

网络对性能的影响

网络性能的限制：质量带宽

建议：

采用高性能和高带宽的网络接口设备和交换机
多网卡进行绑定，增强可用性和带宽
尽可能的进行网络隔离（内外网隔离，企业网和管理的网络隔离）

服务器硬件对性能的影响的总结：

CPU

64位的CPU一定要工作在64位的系统下
对于并发比较高的场景CPU的数量比频率重要
对于CPU密集型场景和复杂SQL则频率越高越好

内存

选择主板所能使用的最高频率的内存
内存的大小对性能很重要，所以尽可能的大（足够的内存可以把大量的随机I/O变成顺序I/O，并对读写数据进行缓存，把多次写变成一次写）

I/O子系统

PCIe->SSD->Raid10->磁盘->SAN（随机读写性能依次减少）

操作系统对性能的影响

Windows ：数据库中对大小写不敏感，而Linux则是敏感的，配置参数强制为小写

下面我们以Centos系统为例：

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

net.core.somaxconn = 65535     # 端口的最大监听数量
net.core.netdev_max_backlog = 65535 # 进入包的最大设备队列
net.ipv4.tcp_max_syn_backlog = 65535 # 进入SYN包的最大请求队列.默认1024

net.ipv4.tcp_fin_timeout = 10 # 表示如果套接字由本端要求关闭，这个参数决定了它保持在FIN-WAIT-2状态的时间。
net.ipv4.tcp_tw_reuse = 1 # 该文件表示是否允许重新应用处于TIME-WAIT状态的socket用于新的TCP连接。
net.ipv4.tcp_tw_recycle = 1 # 表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。
以上三个加速tcp连接的回收 防止连接被占满

net.core.wmem_default = 87380 
net.core.wmem_max = 16777216
net.core.rmem_default = 87380 
net.core.rmem_default = 16777216 
以上四个参数设定了TCP接受和发送缓冲区的默认值和最大值

net.ipv4.tcp_keepalive_time =120 
net.ipv4.tcp_keepalive_intvl = 30 
net.ipv4.tcp_keepalive_probes = 3
以上三个参数用于减少失效连接占据tcp的资源，加快资源回收

有关内存的几个参数：

1	kernel.shmmax = 4294967295

Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值。
注意：
这个参数应该设置足够大，以便能够在一个共享内存段下容纳整个的Innodb缓冲池的大小。
这个值的大小对于64位Linux系统，可取的最大值为物理内存值 -1byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可。

1	vm.swappiness = 0

这个参数决定了当内存不足的时候会对性能产生比较明显的影响
该参数设置为0表示除非虚拟内存完全满了，否则不会使用交换分区。

增加资源限制（/etc/security/limit.conf）
这个文件实际上是Linux PAM也就是插入式认证模块的配置文件。
可以配置打开文件数的设置

1 2	* soft nofile 65535 * hard nofile 65535

以上两个参数用于打开文件数的限制，加到limit.conf文件的末尾即可

* 表示对所有用户有效
soft 指的是当前系统生效的设置（对于同一资源，soft的值不能比hard高）
hard 表明系统中所能设定的最大值
nofile 表示所限制的资源是打开文件的最大数目
65535 就是限制的数量

注意：这个文件的修改需要重启系统

磁盘调度策略（/sys/block/devname/queue/scheduler)

1 2	cat /sys/block/devname/queue/scheduler noop anticipatory deadline [cfq]

noop（电梯式调度策略）
NOOP实现了一个FIFO队列，它像电梯的工作方式一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一个介质。NOOP倾向于饿死读而利于写，因此NOOP对于闪存设备，RAM以及嵌入式是最好的选择。

deadline（介质时间调度策略）
Deadline确保了在一个截至时间内服务请求，这个截至时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象。Deadline对数据库类应用是最好的选择。

anticipatory（预料I/O调度策略）
本质上与Deadline一样，但在最后一次读操作后，要等待6ms，才能继续进行对其他I/O请求进行调度。它会在每个6ms中插入新的I/O操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。AS适合于写入较多的环境，比如文件服务器，AS对数据库环境表现很差。

通过下面方法修改磁盘策略：

1	echo deadline > /sys/block/devname/queue/scheduler

文件系统对性能的影响

windows 有FAT NTFS

Llinux 有EXT3 EXT4 XFS

EXT3/4 系统的挂载参数（/etc/fstab）中进行设置，

data参数，data=wtiteback | ordered | journal 这三个参数代表不同的日志策略，

其中 writeback 意味着只有原数据写入日志，原数据写入和数据写入并不是同步的，这是最快的一种配置，因为Innodb有自己的事务日志，所以选择Innodb是最好的选择

ordered选项只会记录原数据，但提供了一些一致性的保证，在写原数据之前的会先写数据，使他们保持一致，这个选项比writeback 慢一些，但是如果出现崩溃呢更加安全

journal提供了原子日志的一种行为，在数据写入到最终位置之前，将记录到日志中，这个选项对Innodb来说是没有必要的，在这三个选项中最慢的一个

noatime，nodiratime，用于禁止记录文件的访问时间和读取目录的时间，禁用了这两个时间的选项后，可以减少一些，写的操作。系统在读取文件和目录时候，不必写操作来记录以上两个时间

下面是一个完整的 fstab文件的配置

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

在这里可以看到使用了ext4这个文件系统，并且启用了 notime nodiratime这个选项，这是一种常用的文件配置方式