数据库优化-2 什么影响了MySQL的性能
来源：互联网 发布：欧洲旅游 知乎 编辑：程序博客网 时间：2024/04/28 05:47
影响性能的几个方面
1、 服务器的硬件
2、 服务器系统
3、数据库的存储引擎；
- MyISAM：不支持事务，使用表级锁；
- InnoDB：事务级存储引擎，完美支持行锁，事务ACID特性；
4、数据库参数的配置；
5、数据库结构设计和SQL语句；
慢查询的产生：数据库表结构的设计；
CPU资源和可用内存大小
CPU资源
CPU的选择
我们的应用是否密集型的吗？？？ cpu在好不在多
我们的系统并发量大； cpu越多越好
wed类应用 ：核心数量比频率更重要
可用内存大小
内存越多越好
对性能的影响是有限的
并不能无限的增加性能
多余的内存可以增加操作系统等其他服务的性能
内存的选择
频率越高，速度越快
选择与主板支持的最大内存频率
网络
I/O资源
磁盘配置和选择
- 使用传统的机器硬盘
- 传统机器硬盘的读写过程
- 移动磁头到磁盘表面上的正确位置
- 等待磁盘旋转，使的所需的数据在磁头之下
- 传统机器硬盘的读写过程
- 移动磁头到磁盘表面上的正确位置
- 等待磁盘旋转，使的所需的数据在磁头之下
- 等待磁盘旋转过去，所有所需的数据都被磁头读出
- 如何选择传统机器
- 存储容量
- 传输速度
- 访问时间
- 主轴转速
- 物理尺存
- 传统机器硬盘的读写过程
使用RAID增加传统机器硬盘的性能
- 什么是RAID
- PAID是磁盘冗余队列的简称（Redundant Arrays of Independent Disks）简单的说就ＰＡＩＤ就是可以把多个容量较小的磁盘组成一个容量更大的磁盘并提供数据冗余来保证数据完整性的技术
- RAID 0 是最早出现的RAID的模式，也称之为数据带，是组建磁盘阵列中最简单的一种形式，只需要2块以上的硬盘即可，成本低，可以提高整个磁盘的性能和吞吐量，RAID 0没有提供冗余或错误修复能力，但是实现成本是最低的。
- RAID 又称为磁盘镜像原理是吧一个磁盘的数据镜像到另一个磁盘上，也就是说数据在写入一块磁盘的同时，会在另一块闲置的磁盘上生成镜像文件，在不影响性能的情况下最大限度的保证系统的可靠性和课修复性。
- RAID 5（最好使用在从服务器上） 又称为分布式奇偶校验磁盘阵列，通过分布式奇偶校验把数据分散到多个磁盘上，这样如果任意一个磁盘数据失败，都可以在奇偶校验中重建。但是如果两块磁盘失效，则整个卷的数据都是无法恢复
- RAID 10 又称分片的镜像 它是对磁盘先做RAID I之后对两组RAID 1的磁盘在做RAID 0，所有对读写都有良好的性能，相对于RAID 5 重建起来更简单，速度更快。
- 等级特点是否冗余盘数读写RAID 0便宜，快速危险否N快快RAID 1高效读，简单，安全是2快慢RAID 5安全，成本折中是N+1快取决于最慢的盘RAID 10贵，高速，安全是2N快快
使用固态存储SSD或PCIE卡
- 固态存储的特点
- 相比机械磁盘固态磁盘有更好地随机读写性能
- 相比机械磁盘固态磁盘能更好地支持并发
- 相比机械磁盘固态磁盘更容易损坏（缺点）
- 固态硬盘的特点
- 使用SATA接口 ，可以替换传统磁盘而不需要改变
- ASTA接口的SSD同样支持RAID技术
- 固态存储PCIe卡特点
- 无法使用SATA接口需要独特的驱动和配置
- 价格相对于SSD要贵但是性能比SSD更好
- 固态存储的使用场景（最好使用在从服务器上）
- 适用于存在大量随机I/O的场景
- 使用与解决单线程负载的I/O瓶颈
使用网络存储SAN和NAS
- SAN（Storage Area Network）和NAS（Netwoek-Attached Storage）是两种外部文件存储设备加载到服务器上的方法
- SAN设备是通过光纤连接到服务器，通过光纤接口进行访问，服务器可以将其当做硬盘使用。
- SAN可以承受大量的顺序读写、读写I/O、缓存、I/O合并但是随即读写慢，不如RAID访问本地磁盘的读写效率
- NAS设备使用网络连接，通过基于文件的协议如NFS或SMB来访问
- 网络存储适应的场景
- 数据库备份
- 数据库备份
- 网络性能的限制
- 延迟
- 吞吐量
- 网络对性能的影响
- 网络带宽
- 网络质量
- 建议
- 采用高性能和高带宽的网络接口设备和叫换机
- 对多个网卡进行绑定，增强可用性和带宽
- 尽可能的进行网络隔离
- 建议
服务器硬件对性能的影响
- CPU
- 64位的CPU一定要工作在64位的系统
- 对于并发比较高的场景CPU的数量比频率更重要
- 对于CPU密集型场景和复杂SQL则频率越高越好
- 内存
- 选择主板所能使用的最高频率的内存
- 内存的大小对性能很重要，所以尽可能的大
- I/O子系统
- PCIE->SSD ->Raid10> 磁盘->SAN
- PCIE->SSD ->Raid10> 磁盘->SAN
操作系统对性能的影响-MySQL适合的操作系统
- MyAQL 支持多种操作系统
- mysql合适的操作系统
- windows（存储形式：文件，大小写不敏感）
- linx（大小写敏感）
- FreeBSD（最新版本支持）
- Solaris
-
CentOS系统参数优化
- 内核相关参数（/etc/sysctl.confz中进行修改）
- net.core.somaxconn= "" #最大的监听队列长度
- net.core.netdev_max_backlog ="" #在每个网络接口，接收的速率与内核包处理速率快的时候，允许通过的最大网络包
- net .ipv4.tcp_max_syn_backlog=""
- net.ipv4.tcp_fin_timeout = ""
- net.ipv4.tcp_tw_reuse=""
- net.ipv4.tcp_tw_recycle=""
- net.core.wmem_default =""
- net.core.wmem_max=""
- net.core.rmen_default = ""
- net.core.rmem_max=""
- net.ipv4.tcp_keepalive_time =""
- net.ipv4.tcp_keepalive-intvl = ""
- net.ipv4.tcp_keepalive_probes = ""
- kernel.shmmax = ""(Linux内核中最重要的参数之一，用于定义单个文件共享内存段的最大值)
- 这个值应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池大小
- 这个值的大小对于64位linux系统，可取的最大值为物理内存值-1byte，建议值为大于物理内存的一半，一般取值大小Innodb缓冲池的大小即可，可以取物理内存-1byte
- vm.swappinss =0(这个参数当内存不足时会对性能产生比较明显的影响)
- linux 系统内存交换分区：在linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区，如果我们使用free-m在系统中查看可以看到类似于下面内容的，其中swap是磁盘交换分区
当操作系统因为没有足够内存时就会将一些虚拟内存写到磁盘交换分区中这样就会发生内存交换（内存交换对于数据库服务器是透明的）
在数据库服务器上 是否使用交换分区有一些争议：- 在MySQL服务器所在的linux系统上完全禁止交换分区
- 带来的风险
- 降低操作系统的性能
- 容易造成内存溢出，崩溃，被操作系统全部kill掉
- 总结：在MySQL服务器上保留交换区还是很必要的，但是要控制何时使用交换分区
- 带来的风险
- 在MySQL服务器所在的linux系统上完全禁止交换分区
- Vm.swsppiness = 0： 当这个值是0的时候就是告诉linux内核除非虚拟内存完全满了，否则不要使用交换分区
- 增加资源限制（/etc/security/limit.conf）这个文件实际上是linx PAM也就是插入式认证的配置文件。打开文件数的限制
- 用于控制打开文件的限制（加到limit.conf文件末尾就可以了）（注意：这个文件修改需要重启才能生效）
- soft nofile “”
- hard nofile “”
- * 表示对所有用户有效
- soft 指的是当前系统生效的设置（不能比hard高）
- hard 表明系统中所能设置的最大值
- nofile 表示所限制的资源是打开文件的最大数目
- “” 就是限制的数量
- 用于控制打开文件的限制（加到limit.conf文件末尾就可以了）（注意：这个文件修改需要重启才能生效）
- 磁盘调整策略（/sys/block/devname/queue/scheduler）
- 查看文件调度策略： cat /sys/block/devname/queue/scheduler
- 默认使用的是cfq策略
- noop（电梯式调度策略）NOOP实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。NOOP倾向于饿死读而利于写，因此NOOP对于闪存设备、RAM及嵌入式系统是最好的选择。
- deadline（截止时间调度策略） Dadline确保了在一个截止时间内服务请求，这个截止时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能读取而饿死的现象，Deadline对数据库类应用是最好的选择。
- anticipatory（预料I/O调度策略） 本质上与Deadline一样，但是在最后一次读操作后，要等待6ms（毫秒），才能继续进行对其他I/O请求进行调度。它会在每个6ms中插入新的I/O操作，而会将一些小写入流合并成一个大写入流，用写入延时换取更大的写入吞吐量。AS适合写入较多的环境，比如文件服务器，AS对数据库环境表现很差
- 更改磁盘调度
- 查看文件调度策略： cat /sys/block/devname/queue/scheduler
文件系统对性能的影响
- 文件系统依赖于服务器系统
- Windows
- FAT
- NTFS（服务器必须使用）
- linux
- EXT3
- EXT4
- XFS
都支持日志
- EXT3/4系统的挂载参数（/etc/fstab）
- data = writeback | ordered | journal
- writeback 只有原数据写入日志，原数据和数据写入不同步
- ordered 只会记录原数据，员数据和数据写入同步
- journal 原子日志
- writeback 只有原数据写入日志，原数据和数据写入不同步
- noatime , nodiratime 用于禁止记录文件访问时间和读取目录的时间
- data = writeback | ordered | journal
- Windows
MySQL体系结构（与众不同的是：插入式存储引擎）
什么是与存储引擎无关呢
MySQL的存储引擎
MySQL常用存储引擎之MyISAM
- MySQL5.5之前版本默认存储引擎
- 系统表 临时表 所使用的存储引擎
- 临时表：在排序、分组等操作中，当数量超过一定的大小之后，由查询优化器建立的临时表
- MyISAM存储引擎表有MYD和MYI（索引）组成 frm（表结构）
- 特性：
- 并发性与锁级别
- 表损坏修复
- check table 表名 检查表
- repair table 表名 恢复表
- 支持的索引类型
- 支持数据压缩（命令：myisampack）
- 限制
- 在MySQL 5.0之前MyISAM表默认大小是4G
- 如果想要存储大表要修改
- MAX_Rows
- AVG_ROW_LENGTH
- 在MySQL 5.0 之后默认的表大小是256TB
- 适用场景
- 非事务型应用
- 只读类应用
- 空间类应用
MySQL常用存储引擎之Innodb
- MySQL 5.5及之后的版本默认的存储引擎
- Innodb是事务型的存储引擎 也就是说Innodb是支持ＡＣＩＤ特性的
- Innodb使用表空间进行数据存储
- innodb_file_per_table
- ON : 独立表空间：tablename.ibd
- OFF：系统表空间ibdataX（X代表数字）
- innodb_file_per_table
- 系统表空间和独立表空间要如何选择呢？
- 比较
- 系统表空间无法简单的收缩文件大小
- 独立表空间可以通过optimize table命令来收缩系统文件
- 系统表空间会产生I/O瓶颈
- 独立表空间可以同时向多个文件刷新数据
- 建议
- 对innodb使用独立的表空间
- 对innodb使用独立的表空间
- 比较
- 把原来存在于系统表空间中的表转移到独立表空间中的方法
- 使用mysqldump导出所有数据库表数据
- 停止MySQL服务器，修改参数，并删除Innodb相关文件
- 重启MySQL服务，重建Innodb系统表空间
- 导入数据
Innodb存储引擎的特性
- 系统表空间和独立表空间如何选择
- innodb数据字典信息
- undo回滚段
- innodb存储引擎的特性
- innodb是一种事务性的存储引擎
- 完全支持事务的ACID特性
- Redo Log 和 Undo Log
- innodb支持行级锁
- 行级锁可以最大程度的支持并发
- 行级锁是由存储引擎层实现的
- 什么是锁
- 锁主要作用是管理共享资源的并发访问
- 锁用于实现事务的隔离性
- 锁的类型
- 共享锁（也称为读锁）
- 独占锁（也称为写锁）
- 锁的粒度
- 表级锁
- 行级锁
- 阻塞和死锁
- 什么是阻塞：因为不同锁之间的兼容性关系
- 什么是死锁：两个或两个以上的事务在执行过程中相互占用了对方等待的资源造成的异常
- innodb状态检查
- show engine innodb status
- show engine innodb status
- 适用场景
- innodb适合于大多数OLTP应用 （全文索引） （空间函数）
- innodb适合于大多数OLTP应用 （全文索引） （空间函数）
MySQL常用存储引擎之CSV
- 文件系统存储特点
- 数据以文本方式存储在文件中
- .CSV 文件存储表结构
- .CSM文件存储表的元数据 如：表状态和数据量
- .frm文件存储表结构信息
- 特点
- 以CSV格式进行数据存储
- 所有的列必须都是不能为空的列
- 不支持索引 （不适合大表，不适合在线处理）
- 可以对数据文件直接编辑 （保存文本文件内容）
- 适用场景
- 适合做为数据交换的中间表
- 适合做为数据交换的中间表
MySQL常用存储引擎之Archive
MySQL常用存储引擎之Memory
MySQL常用存储引擎之Federated
如何选择存储引擎
MySQL服务器参数介绍
内存配置相关参数
IO相关配置参数
安全相关配置参数
其它常用配置参数
数据库设计对性能的影响
总结
未完 待续
0 0
- 数据库优化-2 什么影响了MySQL的性能
- 打造扛得住的MySQL数据库架构-第2章 什么影响了MySQL性能
- 什么影响了mysql的性能？
- 什么影响了MySQL的性能(1)
- 什么影响了MySQL的性能(01)
- 什么影响了数据库性能
- 什么影响了mysql性能
- mysql - 什么影响了性能和解决方案
- MySQL优化（二）——什么影响了MySQL性能
- MySQL优化（一）——哪些因素影响了数据库性能
- 数据库参数对mysql性能的影响
- 什么决定了数据库性能的优劣？
- 扛得住mysql 之 影响数据库性能的因素
- MySQL 数据库性能优化
- 数据库性能优化(MySQL)
- 优化MySQL数据库性能
- MySQL数据库性能优化
- MySQL 数据库性能优化
- ./configure --prefix --with
- Oracle 索引组织表（IOT）
- android开发常用的颜色值
- 【hdu 2177】取(2堆)石子游戏
- Spring Ioc、依赖注入原理
- 数据库优化-2 什么影响了MySQL的性能
- 2017年我的学习以及工作计划
- show engine innodb status
- Linux输出流>到wndow处理^H
- 隐式跳转(Intent)与显示跳转的区别大比拼
- UIView 基础动画
- linux下生成高强度密码的四大神器
- Bootstrap 模态框避免点击背景处关闭
- Oracle 基础-(一)
