大数据学习的MYSQL进阶

免费学习推荐：mysql视频教程

文章目录

• 1 影响性能的几个方面
• • 1.1 硬件方面
  • 1.2 服务器系统
  • 1.3 数据库存储引擎的选择
  • 1.4 数据库参数配置
  • 1.5 数据库结构设计和SQL语句（重点）
• 2 硬件方面
• • 2.1 CPU资源和可用内存大小
  • • 2.1.1 如何选择CPU
    • 2.1.2 内存
    • • 2.1.2.1 常用MySQL存储引擎
      • 2.1.2.2 提示
      • 2.1.2.3 如何选择内存
  • 2.2 磁盘的配置和选择
  • • 2.2.1 使用传统机器硬盘
    • 2.2.2 使用RAID增强传统机器硬盘的性能
    • • 2.2.2.1 什么是RAID
      • 2.2.2.2 RAID级别
      • • 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.3 RAID级别的选择
    • 2.2.3 使用固态存储SSD和PCIe卡
    • 2.2.4 使用网络存储NAS和SAN
    • • 2.2.4.1 网络存储使用的场景
      • 2.2.4.2 网络性能的限制
      • 2.2.4.3 网络对性能的影响
  • 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 提示

1. 虽然内存的数量是越多越好，但是对系统的性能影响是有限的。
   假如我们数据库的数据有100G，那么内存选择在128G左右就可以达到最大的性能了，这时如果所有的数据都是热数据，那么都会缓存在内存当中，没有必要上256G的内存，但是选择更大的内存，对于操作系统等其他服务的性能也会有相应的提高，并且在短期内不用考虑升级内存的问题。
2. 对于内存缓存的写操作时，可以进行延缓写入，减少数据库的压力。
   内存在读操作上已经有了很好的支持，在写操作上也可以在内存上完成，我们最后都需要将数据写入到磁盘中，虽然不能避免写入磁盘的操作，但是我们可以对写入操作进行延缓，将多次写入合并成一次写入，减轻数据库的压力。数据库提供了类似的功能，可以在缓存池中将多次的写操作合并成一次，最终写入磁盘中。

2.1.2.3 如何选择内存

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

2. 组成购买升级，每个通道的内存尽量相同品牌、颗粒、频率、电压、校验技术和型号。
3. 根据数据库大小选择内存。

2.2 磁盘的配置和选择

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

2.2.1 使用传统机器硬盘

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

• 如何选择传统机器硬盘？

1. 存储容量
2. 传输速度
3. 访问时间
4. 主轴转速
5. 物理尺寸

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）

1. net.core.somaxconn = 65535
   对于处于一个监听状态的端口，都有一个自己的监听队列，这个参数决定了每个端口的监听队列的最大长度。这个参数的默认值可能会比较小，对于很大的服务器来说是不够的，一般会修改成2048或更大的值。
2. net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
   其中backlog这个参数决定了在每个网络接口接收数据包的速率比内核处理机处理快的时候，允许被发送到队列中的数据包的最大的数目，而另一个参数了是决定了这些还未获得对方连接的这种请求可保存在队中的最大数目。对于超过这个值大小的连接可能会被抛弃，所以要同时调大一些。
3. net.ipv4.tcp_fin_timeout = 10
   这个参数是用于控制tcp连接处理的等待状态的超时时间。对于连接比较频繁的系统，通常由大量的连接数处于等待状态，这个参数的设置就是减少连接超时的时间，加快tcp的回收速度。同样有对tcp连接有影响的参数有以下两个：
   net.ipv4.tcp_tw_reuse = 1、net.ipv4.tcp_tw_recycle = 1
   这三个参数都是主要加快tcp的回收，在高负载的系统下，如果tcp连接被占满的话，就会出现连接数据库500的错误，因此这三个参数的作用是很大的。
4. net.core.wmem_default = 87380、net.core.wmem_max = 16777216、net.core.r0mem_default = 87380、net.core.rmem_max = 16777216
   以上4个参数决定了tcp连接接收和发送缓冲区大小的默认值和最大值。对于数据库来说，应该把这几个参数的值调整的稍微大一些。
5. 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探测消息。这三个参数的默认值对于一个平常系统来说稍微有点大了，所以这里分别对它们改为了小了一些。
6. kernel.shmmax = 4294967295
   这个参数是Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值。
   注意：
   • 这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小。
   • 这个值的大小对于64为Linux系统，可取的最大值为物理内存值 - 1 byte，建议值为大于物理内存段的一半，一般取直大于Innodb缓冲池的大小即可，可以取物理内存 - 1 byte。
7. 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支持的存储引擎有很多，如下图所示：

注意：存储引擎是针对于表的而不是针对于库的（一个库中的不同表可以使用不同的存储引擎）
下面我们选一些比较常用的存储引擎进行简单的说明，mysql所使用的存储引擎会对数据库的性能产生直接的影响，还希望各位能仔细的了解存储引擎的一些特点，完了之后才使用存储引擎。

更多相关免费学习推荐：mysql教程(视频)

以上是大数据学习的MYSQL进阶的详细内容。更多信息请关注PHP中文网其他相关文章！