Rumah >pangkalan data >tutorial mysql >大数据学习的MYSQL进阶
免费学习推荐:mysql视频教程
文章目录
1 影响性能的几个方面
1.1 硬件方面
通常个人计算机速度慢,我们都会说是因为电脑硬件的问题,通常是CPU,内存,磁盘IO等因素,因此在服务器上也会出现这个问题。
1.2 服务器系统
一般个人电脑的操作系统都是windows,不同版本的windows系统的性能都不相同,或者配置了某一些参数导致性能的不同。这对于服务器系统也是一样,参数的设置也会影响服务器性能。
1.3 数据库存储引擎的选择
MySQL具有插件式存储引擎,可以根据不同的业务需求选择不同的存储引擎。
而不同的存储引擎也有不同的特点:
1.4 数据库参数配置
对于不同存储引擎,它的参数配置都不尽相同,有些参数对存储引擎的影响是微乎其微,但有些参数却对性能起着决定性作用。因此我们会根据所选的存储引擎和不同的业务需求,对参数的优化也是很重要的。
1.5 数据库结构设计和SQL语句(重点)
我们在进行数据库结构设计的时候应该考虑到今后我们要在数据库上执行怎样的sql语句,来对表结构进行查询和更新,只有这样才能设计出符合要求的表结构。
对于慢查询,是导致性能低下的罪魁祸首,而它就是由于我们对数据库表结构设计不合理而产生的。而对于这类sql来说,也是最难优化的,因为项目一旦上线,就很难对数据库表结构进行修改。
因此我们优化数据库性能的重点在于:
数据库表结构设计
SQL语句的编写和优化
下面具体对每一个方面进行详细的说明。
2 硬件方面
2.1 CPU资源和可用内存大小
2.1.1 如何选择CPU
通常在选择CPU的时候,我们都希望CPU的频率和核心数量两者都尽量高,但由于成本或各种因素,往往只能迫使我们选择其中的一种。那我们应该怎样选择最优的方案?因此,在购买CPU时我们需要注意几点问题:
2.1.2 内存
内存的大小直接影响数据库的性能。目前内存的效率要远远高于磁盘。因此把数据缓存到内存中,可以大大提高服务器性能。
2.1.2.1 常用MySQL存储引擎
有两种常用的存储引擎:MyISAM和InnoDB。
MyISAM:
索引存储在内存中,数据保存在硬盘中。
InnoDB:
索引和数据都保存在内存中,从而提高数据库的运行效率。
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倍的性能。
RAID 1 又称磁盘镜像,原理是把一个磁盘的数据镜像到另一个磁盘上,也就是说数据在写入一块磁盘的同时,会在另一块限制的磁盘上生成镜像文件,在不影响性能情况下最大限度的保证系统的可靠性和可修复性。
它与RAID 0不同的地方在,中间的地方画上了一个等于号。两个磁盘的数据都是一样的,具备良好的冗余能力,但成本会相应的提高,当出现磁盘故障的情况下也可以正常运行,但需要即使更换故障的磁盘,否则系统也会崩溃。
在更换新的磁盘后,数据的同步需要消耗很多时间,虽然不会对数据的访问造成影响,但系统的性能是会有所下降的。
RAID 1在很多情况下,可以提供很好的读性能,并且在不同磁盘间冗余数据,因此数据冗余性很好。RAID 1在读上比RAID 0 要好,因此比较适合在存放日志或类似的工作。
RAID 5 又称之为分布式奇偶校验磁盘阵列。通过分布式奇偶校验块把数据分散到多个磁盘上,这样如果任何一个盘数据失效,都可以从奇偶校验块中重建。但是如果两块磁盘失效,则整个卷的数据都无法恢复。
可见,每个磁盘中分别有Dp,Cp,Bp,Ap,如果其中一块磁盘出现问题,则可以通过其他三块磁盘的数据和奇偶校验值重新计算出磁盘的数据。
对于RAID 0和RAID 1而言,这是最经济的冗余配置,因为整个阵列配置只需要1块磁盘的容量就可以了。
在RAID 5上写速度较慢,因为每次写都要在磁盘之间进行2次读和2次写,以计算存储校验位的数值,但是,随机读和顺序读都很快,因为在读取的时候不需要计算奇偶校验位,因此RAID 5 更适合以读为主的数据库业务。
RAID 5发生的最大问题是在磁盘失效的时候,因为数据需要重新分配到其他磁盘上,这将会严重影响磁盘的性能,所以使用RAID 5最好使用在重读的情况下。
RAID 10又称分片的镜像。它是对磁盘先做RAID 1之后对两组RAID 1的磁盘再做RAID 0,所以对读写都有良好的性能,相对于RAID 5重建起来更简单,速度也更快。
在RAID 10上,如果损坏了一个硬盘,那么对性能会造成严重的影响,因为在读写过程中,本来可以在两块相邻的磁盘中同时读取,如果损坏了一块,那么只能从单台磁盘进行读取,因此最坏的情况下,我们的性能会降低50%。
等级 | 特点 | 是否冗余 | 盘数 | 读 | 写 |
---|---|---|---|---|---|
RAID 0 | 便宜,快速,危险 | 否 | N | 快 | 快 |
RAID 1 | 高速读,简单,安全 | 有 | 2 | 快 | 慢 |
RAID 5 | 安全,成本折中 | 有 | N+1 | 快 | 取决于最慢的盘 |
RAID 10 | 贵,高速,安全 | 有 | 2N | 快 | 快 |
2.2.3 使用固态存储SSD和PCIe卡
固态存储又称为闪存。
特点:
SSD特点:
固态存储PCIe卡特点:
固态存储的使用场景
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:
内存:
I/O子系统:
3 操作系统对性能的影响
MySQL适合的操作系统:Windows,FreeBSD,Solaris,Linux
3.1 CentOS系统参数优化
内核相关参数(/etc/sysctl.conf)
net.core.somaxconn = 65535
net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
、net.ipv4.tcp_tw_recycle = 1
net.core.wmem_default = 87380
、net.core.wmem_max = 16777216
、net.core.r0mem_default = 87380
、net.core.rmem_max = 16777216
net.ipv4.tcp_keepalive_time = 120
、net.ipv4.tcp_keepalive_intvl = 30
、net.ipv4.tcp_keepalive_probes = 3
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
vm.swappiness = 0
free -m
在系统中查看可以看到类似下面的内容,其中swap就是交换分区。当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换区中这样就会发生内存交换。增加资源限制(/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教程(视频)
Atas ialah kandungan terperinci 大数据学习的MYSQL进阶. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!