MySql 对性能产生影响的条件

影响数据库性能的因素

cpu 内存

应用类型：
- 计算密集型应用(CPU密集型)：需要更快的cpu(频率)
- 系统并发处理量大：需要更多的CPU
CPU位数：64位CPU使用32位服务器版本
- 32位操作系统：不能使用大量的内存，单独的进程不能寻址到4G以上的内存(MySQL单进程应用)
注：MySQL不支持多CPU对同一条sql同时处理,MySQL5.0和之前版本不支持CPU多核处理(4核以上没有)

网络/IO

内存IO效率>SSD/Fusion-IO

常用的MySQL存储引擎（插件式引擎）

MyISAM：索引放在内存里，数据放在操作系统(OS)
InnoDB：索引和数据都放在内存中

无限提高内存:

数据和内存一致的时候提高内存是对数据库性能提高没有用的,但是可以提高其它的服务效率

缓存对数据库的读操作和写操作都有好的影响

缓存足够高的时候可以提高读请求的命中率
缓存还可以延缓数据库写操作，把多次写操作整合成一次操作。
例：商城物品点击量计数器，放到缓存中（更新内存中的计数器）当缓存中大于100次的时候进行写操作，100次写操作变成一个操作。

选择内存

1.选择服务器频率达到服务器可以支持的最高频率。
2.并且每个通道的内存最好买完全相同的内存条。
3.单条的内存条尽可能的大。

磁盘的配置和选择（IO子系统的效率）

传统机器磁盘

存储空间大、价格低、读写慢、使用最多、最常见
读取过程
1.移动磁头到磁盘表面上的正确位置。
2.等待磁盘旋转，使得所需的数据在磁头之下。
3.等待磁盘旋转过去，所有所需的数据都被磁头读出。
1和2访问速度，3传输速度
磁盘选择
存储容量:大
传输速度：快
访问时间：快
主轴转速：尽量快(1w5转)
物理尺寸：小的速度快空间小、大的速度慢空间大

RAID技术增加传统磁盘的性能（磁盘冗余队列）

* 什么是RAID？*
可以把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。
RAID级别
RAID 0 磁盘阵列 比较适合一次性数据
RAID 1 磁盘镜像 磁盘利用率50% 更换需要进行磁盘镜像 比较适合数据安全的
RAID 5 分布式奇偶校验磁盘阵列 比较适合读位置
RAID 10 磁盘分片的镜像

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

RAID选择：
读写频繁 RAID1、0
从库 RAID0、RAID5
选择RAID卡，选择带有缓存的RAID控制器

固态存储

相比于机械磁盘固态磁盘有更好的随机读写性能
相比机械磁盘更好的支持并发
相比于机械磁盘固态磁盘更容易损坏
SSD
可以直接使用SATA接口，可以替换传统的磁盘不需要改变，但会收到接口的影响
可以使用RAID技术（RAID控制器和传统磁盘的RAID控制器不一样）
PCI-E SSD（闪存卡Fusion-IO）
无法使用SATA接口、需要单独的驱动和配置
价格比较贵，会使用服务器的内存和CPU资源（IO性能牺牲内存）
RAID配置成本高
固态存储的使用场景
使用大量随机IO场景
解决单线程负载I/O瓶颈
只有一块的存储设备，最好使用在从服务器上（放在主服务器不安全、并且从服务器单线程适合使用）

网络存储SAN和NAS

SAN
SAN设备通过光纤连接到服务器，设备通过块接口访问，服务器可以将其当做硬盘使用
可以进行大量的顺序读写、随机读写慢
NAS
NAS设备使用网络进行连接，通过基于文件的协议如NFS或SMB来访问
网络延迟
网络存储适合的使用场景
存储数据库备份文件

网络性能的限制

延时 吞吐量（带宽）

网络带宽对性能的影响

1000M的网络，50台服务器，每台2M，网络带宽满载

网络质量对性能的影响

网络质量不好就会容易丢包，丢包就会造成数据重新发送请求，容易形成网络风暴把整个网络带宽沾满。

建议

• 采用高性能和高宽带的网络接口设备和交换机（最好使用万兆）
• 对多个网卡进行绑定，增强可用性和带宽
• 尽可能进行网络隔离（内网/外网,企业/管理）

总结

cpu
* 64位的CPU一定要工作在64位的系统下
* 对于对于并发比较高的场景CPU的数量比频率更重要
* 对于CPU密集型场景和复杂SQL则频率越高越好
内存
* 选择主办所能使用的最高频率的内存
* 内存的大小对性能很重要，所以尽可能的大：足够的内存可以把大量的随机IO变成顺序IO
I/O子系统
* PCIe>SSD>RAID10>磁盘>SAN(SAN出现问题需要大量的时间不是一个很好的高可用方案)

操作系统对性能的影响

windows linux FreeBSD Solaris Linux
MySQL的schema的存储方式在文件系统中是一个目录，Windows下表名大小写不敏感但是linux下表名大小写敏感（配置mysql强制进行数据库名字小写）
FreeBSD老版本对MySQL支持不是很好，最新版本支持
Solaris 稳定性著称，提供了大量的工具，现在可以运行在X86系统
Linux 桌面版和服务器专门设计的版本（centOS比较流行）

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 //控制tcp处于等待超时时间
net.ipv4.tcp_tw_resue
net.ipv4.tcp_tw_recycle
net.core.wmem_default=87380
net.core.wmem_max=87380
net.core.rmem_default
net.core.rmem_max
net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3
内存相关的参数（/etc/sysctl.conf）
kernel.shmmax=4294967295//应该设置的足够大以便于能在一个共享内存断下容纳整个INNODB缓冲池大小即可。如果创建多个小的共享内存段会影响系统
vm.swappiness=0 //内存不足的时候设置为0除非内存使用满了否则不使用交换分区
系统交换分区swap，当操作系统中因为没有足够的内存就会将一些虚拟内存写入的内存交换区内，
完全禁用交换分区，容易造成内存溢出
增加资源限制（/etc/security/limit.conf）
打开文件数的限制
* soft nofile 65535
* hard nofile 65535
* 表示对所有用户有效
soft 指的是当前系统生效的设置
hard 表明系统中所设定的最大值
nofile 表示所限制的资源是打开文件的最大数目
65535 就是限制的数量
数据库中一个表需要两到三个文件
磁盘调度策略（ /sys/block/sda/queue/scheduler）
cat /sys/block/sda/queue/scheduler (查看调度策略)
cfq 默认的调度策略 会插入不必要的请求导致访问时间比较长
noop 电梯式调度策略 饿死读利于写
deadline截止时间调度策略
anticipatory（预料I/O调度策略）
修改磁盘调度策略 echo deadline > /sys/block/sda/queue/scheduler

文件系统对性能的影响

ext3/4系统的挂载参数（/etc/fstab）
data=writeback|ordered|journal
noattime,nodiratime 减少不必要的写操作

MySQL本身对性能的影响

插件式存储引擎

MySQL体系结构
1.客户端：连接处理
2.MySQL服务层：连接管理器=>查询解析=>查询缓存=>查询优化器
3.存储引擎层：存储引擎针对表可以在一个数据库中使用多种不同的存储引擎

MySQL常用的存储引擎

engine 指定对应的存储引擎
MyISAM
系统表
临时表：在排序分组等操作中，当数量超过一定的大小之后，有查询优化器建立的临时表（不是sql建的）
MyISAM的存储引擎表有MYD和MYI开头组成文件
MYD:存储数据信息
MYI:存储索引信息
frm:用于记录表结构信息
特性：
并发性与表级锁：对于读写同时操作支持不好
表损坏修复：check table 表名 检查表状态
repair table 表名 修复表 容易造成数据丢失
表支持的索引类型
支持表数据压缩 命令myisampack 压缩的表只能进行读操作 不能进行写操作
限制：
MySQL5.0时默认表的大小为4G，如需存储大表则需要修改MAX—Rows和AVG——ROW——lENGTH
5.0之后默认支持256TB
适用场景：
* 非事务性应用 数据仓库报表
* 只读类应用 可以进行表压缩
* 空间类应用 JSP数据，空间函数
InnoDB
5.5之后innodb成为默认存储引擎
适合处理大量的小事务
使用表空间进行数据存储
innodb_file_per_table
ON:独立表空间：tablename.ibd
OFF:系统共享空间表：ibdataX x代表数字

OFF：系统表空间无法简单的收缩文件大小 造成大量的空间浪费 重启mysql服务 顺序写入 造成IO阻塞
ON：使用独立表空间可以通过optimize table命令收缩系统文件 重建表 每个表有自己的空间
系统表空间向独立表空间迁移
1.使用mysqldymp到处所有的数据库表数据
2.停止MySQL服务，修改参数，并删除Innodb相关文件
3.重启mysql服务重建innodb系统表空间
4.重新导入数据

大表

大表会影响DDL(数据定义)操作影响
大表的处理:
1. 分库分表
分库主键的选择
分库后跨分区数据的查询和统计
2. 历史数据归档
如何进行归档
数据归档的时间点选择

大事务

运行时间长、操作数据比较多
1.锁定太多的数据，造成大量的阻塞和超时
2.回滚的时间长
3.执行时间长，容易造成主从延迟
解决方案进行分批次操作