MYSQL进阶怎么学

36次阅读

没有评论

共计 8120 个字符，预计需要花费 21 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍 MYSQL 进阶怎么学，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

文章目录

1 影响性能的几个方面

1.1 硬件方面

1.2 服务器系统

1.3 数据库存储引擎的选择

1.4 数据库参数配置

1.5 数据库结构设计和 SQL 语句（重点）

2 硬件方面

2.2.1 使用传统机器硬盘

2.2.2 使用 RAID 增强传统机器硬盘的性能

2.2.3 使用固态存储 SSD 和 PCIe 卡

2.2.4 使用网络存储 NAS 和 SAN

2.2.2.2.1 RAID 0

2.2.2.2.2 RAID 1

2.2.2.2.3 RAID 5 —— 常见的 RAID 组别

2.2.2.2.4 RAID 10 —— 常用的 RAID 组别

2.2.2.1 什么是 RAID

2.2.2.2 RAID 级别

2.2.2.3 RAID 级别的选择

2.2.4.1 网络存储使用的场景

2.2.4.2 网络性能的限制

2.2.4.3 网络对性能的影响

2.1.1 如何选择 CPU

2.1.2 内存

2.1.2.1 常用 MySQL 存储引擎

2.1.2.2 提示

2.1.2.3 如何选择内存

2.1 CPU 资源和可用内存大小

2.2 磁盘的配置和选择

2.3 总结

3 操作系统对性能的影响

3.1 CentOS 系统参数优化

4 文件系统对性能的影响

5 MySQL 体系结构

1 影响性能的几个方面

1.1 硬件方面

通常个人计算机速度慢，我们都会说是因为电脑硬件的问题，通常是 CPU，内存，磁盘 IO 等因素，因此在服务器上也会出现这个问题。

1.2 服务器系统

一般个人电脑的操作系统都是 windows，不同版本的 windows 系统的性能都不相同，或者配置了某一些参数导致性能的不同。这对于服务器系统也是一样，参数的设置也会影响服务器性能。

1.3 数据库存储引擎的选择

MySQL 具有插件式存储引擎，可以根据不同的业务需求选择不同的存储引擎。
而不同的存储引擎也有不同的特点：

MyISAM：不支持事务，表级锁。

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

1.4 数据库参数配置

对于不同存储引擎，它的参数配置都不尽相同，有些参数对存储引擎的影响是微乎其微，但有些参数却对性能起着决定性作用。因此我们会根据所选的存储引擎和不同的业务需求，对参数的优化也是很重要的。

1.5 数据库结构设计和 SQL 语句（重点）

我们在进行数据库结构设计的时候应该考虑到今后我们要在数据库上执行怎样的 sql 语句，来对表结构进行查询和更新，只有这样才能设计出符合要求的表结构。
对于慢查询，是导致性能低下的罪魁祸首，而它就是由于我们对数据库表结构设计不合理而产生的。而对于这类 sql 来说，也是最难优化的，因为项目一旦上线，就很难对数据库表结构进行修改。

因此我们优化数据库性能的重点在于：

数据库表结构设计

SQL 语句的编写和优化

下面具体对每一个方面进行详细的说明。

2 硬件方面

2.1 CPU 资源和可用内存大小

2.1.1 如何选择 CPU

通常在选择 CPU 的时候，我们都希望 CPU 的频率和核心数量两者都尽量高，但由于成本或各种因素，往往只能迫使我们选择其中的一种。那我们应该怎样选择最优的方案？因此，在购买 CPU 时我们需要注意几点问题：

我们的应用时 CPU 密集型的吗？

如果我们的应用为 CPU 密集型的话，要加快 sql 的处理速度，显然我们需要更好的 CPU，而不是更多的 CPU。

对于当前的 MySQL 而言，还不支持 duoCPU 对同一 SQL 并发处理。

我们系统的并发量如何？

如果我们系统需要更多吞吐量，那么我们的 CPU 则是越多越好。假设我们有 40 个 CPU，那我们是不是可以同时处理 40 个 SQL 了呢。

衡量数据库处理能力的指标：QPS，指的是同时处理 SQL 的数量。但这个指标是在 1s 中处理 SQL 的数量，但上一点阐述的同时处理是在纳秒级的维度上。

MySQL 通常是使用在 web 应用上的，往往并发量比较大，这时 CPU 数量比 CPU 频率更为重要。

我们所使用的 MySQL 的版本

在 5.0 版本之前，MySQL 对多核的 CPU 支持是并不好，对系统的限制是很严重的，在现在 5.6，5.7 版本上，对多核 CPU 的支持已经有了很大的改善。因此推荐使用最新版的 MySQL 版本，以达到更好的性能。

选择 32 位还是 64 位的 CPU？

目前服务器的 CPU 默认都是 64 位架构的，但是要注意，要检查好系统是否在 64 位上装了 32 位的服务器版本，这会严重影响服务器性能。

2.1.2 内存

内存的大小直接影响数据库的性能。目前内存的效率要远远高于磁盘。因此把数据缓存到内存中，可以大大提高服务器性能。

2.1.2.1 常用 MySQL 存储引擎

有两种常用的存储引擎：MyISAM 和 InnoDB。
MyISAM：
索引存储在内存中，数据保存在硬盘中。

InnoDB：
索引和数据都保存在内存中，从而提高数据库的运行效率。

2.1.2.2 提示

虽然内存的数量是越多越好，但是对系统的性能影响是有限的。
假如我们数据库的数据有 100G，那么内存选择在 128G 左右就可以达到最大的性能了，这时如果所有的数据都是热数据，那么都会缓存在内存当中，没有必要上 256G 的内存，但是选择更大的内存，对于操作系统等其他服务的性能也会有相应的提高，并且在短期内不用考虑升级内存的问题。

对于内存缓存的写操作时，可以进行延缓写入，减少数据库的压力。
内存在读操作上已经有了很好的支持，在写操作上也可以在内存上完成，我们最后都需要将数据写入到磁盘中，虽然不能避免写入磁盘的操作，但是我们可以对写入操作进行延缓，将多次写入合并成一次写入，减轻数据库的压力。数据库提供了类似的功能，可以在缓存池中将多次的写操作合并成一次，最终写入磁盘中。

2.1.2.3 如何选择内存

尽量使用主板能够支持最大频率的内存

组成购买升级，每个通道的内存尽量相同品牌、颗粒、频率、电压、校验技术和型号。

根据数据库大小选择内存。

2.2 磁盘的配置和选择

虽然内存对数据库性能起到很大的作用，但是我们不能忽略 IO 子系统对性能的影响。目前我们常用的磁盘选择有以下 4 种：

2.2.1 使用传统机器硬盘

特点：存储空间大，价格低，使用最多，最常见，读、写较慢

如何选择传统机器硬盘？

存储容量

传输速度

访问时间

主轴转速

物理尺寸

2.2.2 使用 RAID 增强传统机器硬盘的性能

2.2.2.1 什么是 RAID

RAID 是磁盘冗余队列的简称（Redundant Arrays of Independent Disks），简单来说 RAID 的作用就是把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。

2.2.2.2 RAID 级别

2.2.2.2.1 RAID 0

RAID 0 是最早出现的 RAID 模式，也称之为数据条带。是组件磁盘阵列中最简单的一种形式，只需要 2 块以上的硬盘即可，成本低，可以提高整个磁盘的性能和吞吐量。RAID 0 没有提供冗余或错误修复能力，但是实现成本是最低的。但在考虑到数据恢复和可靠性因素，RAID 0 成为了成本最高的配置，因为 RAID 0 中没有冗余，并且数据在损坏的概率在当个磁盘中的还要高。因为数据在任意一个磁盘中损坏都会造成数据的丢失。比如由 3 块磁盘组成的 RAID 0，其损坏的几率是单个硬盘的 3 倍。
因此 RAID 0 适用于不会单一丢失数据的情况，比如：可以随时可以从其他数据库克隆的备库或者某些只需一次性使用的数据库。

简单来说，RAID 0 就是将硬盘串联在一起，形成更大的磁盘，比如：

并且在并发的过程中，可以达到相当于单个硬盘 3 倍的性能。

2.2.2.2.2 RAID 1

RAID 1 又称磁盘镜像，原理是把一个磁盘的数据镜像到另一个磁盘上，也就是说数据在写入一块磁盘的同时，会在另一块限制的磁盘上生成镜像文件，在不影响性能情况下最大限度的保证系统的可靠性和可修复性。

它与 RAID 0 不同的地方在，中间的地方画上了一个等于号。两个磁盘的数据都是一样的，具备良好的冗余能力，但成本会相应的提高，当出现磁盘故障的情况下也可以正常运行，但需要即使更换故障的磁盘，否则系统也会崩溃。
在更换新的磁盘后，数据的同步需要消耗很多时间，虽然不会对数据的访问造成影响，但系统的性能是会有所下降的。
RAID 1 在很多情况下，可以提供很好的读性能，并且在不同磁盘间冗余数据，因此数据冗余性很好。RAID 1 在读上比 RAID 0 要好，因此比较适合在存放日志或类似的工作。

2.2.2.2.3 RAID 5 —— 常见的 RAID 组别

RAID 5 又称之为分布式奇偶校验磁盘阵列。通过分布式奇偶校验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果两块磁盘失效，则整个卷的数据都无法恢复。

可见，每个磁盘中分别有 Dp，Cp，Bp，Ap，如果其中一块磁盘出现问题，则可以通过其他三块磁盘的数据和奇偶校验值重新计算出磁盘的数据。
对于 RAID 0 和 RAID 1 而言，这是最经济的冗余配置，因为整个阵列配置只需要 1 块磁盘的容量就可以了。
在 RAID 5 上写速度较慢，因为每次写都要在磁盘之间进行 2 次读和 2 次写，以计算存储校验位的数值，但是，随机读和顺序读都很快，因为在读取的时候不需要计算奇偶校验位，因此 RAID 5 更适合以读为主的数据库业务。
RAID 5 发生的最大问题是在磁盘失效的时候，因为数据需要重新分配到其他磁盘上，这将会严重影响磁盘的性能，所以使用 RAID 5 最好使用在重读的情况下。

2.2.2.2.4 RAID 10 —— 常用的 RAID 组别

RAID 10 又称分片的镜像。它是对磁盘先做 RAID 1 之后对两组 RAID 1 的磁盘再做 RAID 0，所以对读写都有良好的性能，相对于 RAID 5 重建起来更简单，速度也更快。

在 RAID 10 上，如果损坏了一个硬盘，那么对性能会造成严重的影响，因为在读写过程中，本来可以在两块相邻的磁盘中同时读取，如果损坏了一块，那么只能从单台磁盘进行读取，因此最坏的情况下，我们的性能会降低 50%。

2.2.2.3 RAID 级别的选择等级特点是否冗余盘数读写 RAID 0 便宜，快速，危险否 N 快快 RAID 1 高速读，简单，安全有 2 快慢 RAID 5 安全，成本折中有 N + 1 快取决于最慢的盘 RAID 10 贵，高速，安全有 2N 快快

2.2.3 使用固态存储 SSD 和 PCIe 卡

固态存储又称为闪存。
特点：

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

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

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

SSD 特点：

使用 SATA 接口，可以替换传统磁盘而不需任何改变

SATA 接口的 SSD 同样支持 RAID 技术

固态存储 PCIe 卡特点：

无法使用 SATA 接口，需要独特的驱动和配置

价格相对于 SSD 更贵，但是性能比 SSD 更好

固态存储的使用场景

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

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

2.2.4 使用网络存储 NAS 和 SAN

SAN(Strorage Area Network) 和 NAS(Network-Attached Storage) 是两种外部文件存储设备加载到服务器上的方法。

SAN：
SAN 设备通过光纤连接到服务器，设备通过块接口访问，服务器可以将其当作硬盘使用。

SAN 的特点：

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

2.2.4.1 网络存储使用的场景

适合使用在数据库备份。

2.2.4.2 网络性能的限制

网络性能的限制主要是延迟和带宽。

2.2.4.3 网络对性能的影响

网络带宽对性能的影响

网络质量对性能的影响
建议：

采用高性能和高带宽的网络接口设备和交换机

对多个网卡进行绑定，增强可用性和带宽

尽可能的进行网络隔离

2.3 总结

CPU：

64 位的 CPU 一定要工作在 64 位的系统下

对于并发比较高的场景 CPU 的数量比频率重要

对于 CPu 密集型场景和复杂 SQL 则频率越高越好

内存：

选择主板所能使用的最高频率的内存

内存的大小对性能很重要，所以尽可能的大

I/ O 子系统：

PCIe – SSD – RAID10 – 磁盘 – SAN

3 操作系统对性能的影响

MySQL 适合的操作系统：Windows，FreeBSD，Solaris，Linux

3.1 CentOS 系统参数优化

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

net.core.somaxconn = 65535
对于处于一个监听状态的端口，都有一个自己的监听队列，这个参数决定了每个端口的监听队列的最大长度。这个参数的默认值可能会比较小，对于很大的服务器来说是不够的，一般会修改成 2048 或更大的值。

net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
其中 backlog 这个参数决定了在每个网络接口接收数据包的速率比内核处理机处理快的时候，允许被发送到队列中的数据包的最大的数目，而另一个参数了是决定了这些还未获得对方连接的这种请求可保存在队中的最大数目。对于超过这个值大小的连接可能会被抛弃，所以要同时调大一些。

net.ipv4.tcp_fin_timeout = 10
这个参数是用于控制 tcp 连接处理的等待状态的超时时间。对于连接比较频繁的系统，通常由大量的连接数处于等待状态，这个参数的设置就是减少连接超时的时间，加快 tcp 的回收速度。同样有对 tcp 连接有影响的参数有以下两个：
net.ipv4.tcp_tw_reuse = 1、net.ipv4.tcp_tw_recycle = 1
这三个参数都是主要加快 tcp 的回收，在高负载的系统下，如果 tcp 连接被占满的话，就会出现连接数据库 500 的错误，因此这三个参数的作用是很大的。

net.core.wmem_default = 87380、net.core.wmem_max = 16777216、net.core.r0mem_default = 87380、net.core.rmem_max = 16777216
以上 4 个参数决定了 tcp 连接接收和发送缓冲区大小的默认值和最大值。对于数据库来说，应该把这几个参数的值调整的稍微大一些。

net.ipv4.tcp_keepalive_time = 120、net.ipv4.tcp_keepalive_intvl = 30、net.ipv4.tcp_keepalive_probes = 3
以上三个参数用于减少失效连接所占用的 tcp 系统资源的数量，加快资源回收的效率，net.ipv4.tcp_keepalive_time 是表示 tcp 发送 tcp_keepalive 探测消息的时间的间隔，单位为秒，用于确认 tcp 连接是否有效。net.ipv4.tcp_keepalive_intvl 用于当探测这个 tcp 连接没有反应后，重新发送探测消息的时间间隔，单位为秒，net.ipv4.tcp_keepalive_probes 表示在认定 tcp 连接失效之前，需要发送多少个 tcp_keepalive 探测消息。这三个参数的默认值对于一个平常系统来说稍微有点大了，所以这里分别对它们改为了小了一些。

kernel.shmmax = 4294967295
这个参数是 Linux 内核参数中最重要的参数之一，用于定义单个共享内存段的最大值。
注意：

这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的 Innodb 缓冲池的大小。

这个值的大小对于 64 为 Linux 系统，可取的最大值为物理内存值 – 1 byte，建议值为大于物理内存段的一半，一般取直大于 Innodb 缓冲池的大小即可，可以取物理内存 – 1 byte。

vm.swappiness = 0
这个参数当内存不足时会对性能产生比较明显的影响。这个参数就是告诉 Linux 系统内核除非虚拟内存完全满了，否则不要使用交换区。
Linux 系统内存交换分区：
在 Linux 系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区。如果我们使用 free - m 在系统中查看可以看到类似下面的内容，其中 swap 就是交换分区。当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换区中这样就会发生内存交换。
在 MySQL 服务所在的 Linux 系统上完全禁用交换分区，会带来以下两点风险：

降低操作系统的性能

容易造成内存溢出，崩溃，或都被操作系统 Kill 掉

增加资源限制（/etc/security/limit.conf）
limit.conf 这个文件实际上时 Linx PAM 也就是插入式认证模块的配置文件。
其中比较重要的参数配置就是打开文件数的限制。

结论：把可打开的文件数量增加到了 65535 个以保证可以打开足够多的文件句柄。
注意：这个文件的修改需要重启服务器后生效。

磁盘调度策略（/sys/block/devname/queue/scheduler）
可以使用命令 cat /sys/block/sda/queue/scheduler 查看当前磁盘所使用的调度策略。下面的 noop anticipatory deadline [cfq] 为系统默认的 cfq 调度策略。
在 MySQL 数据库服务下，cfq 并不合适，是由于在 MySQL 工作过程中，cfq 会在队列中插入一些不必要的请求，导致很差的响应时间。
除了 cfq 调度策略，还有以下几种策略：
noop（电梯式调度策略）：

deadline（截止时间调度策略）：

anticipatory（预料 I / O 调度策略）：

我们可以输入以下命令来改变磁盘的调度策略：
echo schedulerName /sys/block/sda/queue/scheduler
如：echo deadline /sys/block/sda/queue/scheduler

4 文件系统对性能的影响

推荐使用 XFS 文件系统，在 EXT3 和 EXT4 下需要配置以下参数：

EXT3/ 4 系统的挂载参数（/etc/fstab）：

data=writeback | ordered | journal
这个参数一共有三个可选择的值，writeback 表示只有元数据写入到日志，元数据写入和数据写入并不是同步的。这是一种最快的配置，因为 InnoDB 原本有自己的事务日志，所以通常是 InnoDB 最好的选择。ordered 只会记录元数据，但提供了一些一致性的保证，在写元数据之前，会先写数据，使它们保持一致，这个选项比 writeback 稍微慢一点，但出现崩溃会更加安全。journal 提供了原子日志的行为，在数据写入到最终的日志之前，将记录到日志中。这个选项对于 InnoDB 显然是没有必要的，也是三种中最慢的一种。

noatime、nodiratime
这两个选项用于记录文件的访问时间和读取目录的时间。设置了这两个参数可以减少一些写的操作。系统在读取文件和目录时也不必写操作来记录以上两个时间。
下面是文件 /dev/sda1/ext4 中的一些配置：
noatime,nodiratime,data=writeback 1 1

5 MySQL 体系结构

体系结构在最上层的叫做客户端，这一层代表了可以通过 mysql 连接协议连接到 mysql 的客户端，比如说 PHP，JAVA，C API，.Net 以及 ODBC，JDBC 等，从这里可以看出，这一层并不是 mysql 体系结构所特有。大多数 CS 架构的服务都是采用了这一种体系结构。这一层主要是完成了连接处理，授权认证和安全等一些功能。每个连接到 mysql 的客户端都在服务器的进程中拥有一个线程，这个连接的查询只会在这个线程中进行执行，也就是我们前面说到的，每个连接的查询只用到一个 CPU 的核心。
那么这个体系的第二层，大多数的 mysql 核心服务都在这一层中，如下图所示。

我们常用的 DDL 或者 DML 语句都是在这一层上定义的。但是我们只要记住一点就可以了，所有跨存储引擎的功能都是在这一层中实现的，因为这一层也被称之为服务层。
我们的结构体系的第三层是存储引擎层，mysql 是一款非常优秀的开源数据库，其中定义了一系列了存储引擎的接口，只要符合存储引擎的要求，我们就可以对 mysql 开发出一款完全符合自己需要的存储引擎，比如我们常用的 InnoDB，目前 mysql 支持的存储引擎有很多，如下图所示：

注意：存储引擎是针对于表的而不是针对于库的（一个库中的不同表可以使用不同的存储引擎）