什么影响了mysql性能？

介绍服务器硬件、服务器系统、数据库存储引擎（插件式存储引擎）、数据库参数配置、数据库结构设计和sql语句对数据库的影响

影响性能的几个方面

服务器硬件

服务器系统

数据库存储引擎（插件式存储引擎）

MyISAM：不支持事务，表级锁

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

数据库参数配置

数据库结构设计和sql语句

服务器硬件

cpu资源和可用内存大小

mysql不支持多cpu对同一sql并发处理

磁盘的配置和选择

使用传统机器磁盘

特点：

存储空间大，读写慢、价格低、使用最多

传统机器硬盘读取数据的过程

移动磁头到磁盘表面上的正确位置

等待磁盘旋转，使得所需的数据在磁头之下

等待磁盘旋转过去，所有所需的数据都被磁头读出

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

什么是RAID(Redundant Array of Independent Disks，独立磁盘冗余阵列)

RAID是一种把多块独立的硬盘（物理硬盘）按不同的方式组合起来形成一个硬盘组（逻辑硬盘），从而提供比单个硬盘更高的存储性能和提供数据备份技术。组成磁盘阵列的不同方式成为RAID的不同级别（RAID Levels）。组成的磁盘组就像是一个硬盘，用户可以对它进行分区，格式化等，用户对磁盘阵列的操作与对单个硬盘的操作并无二致。

RAID 0 定义： RAID 0又称为Stripe或Striping，它代表了所有RAID级别中最高的存储性能。RAID 0提高存储性能的原理是把连续的数据分散到多个磁盘上存取，这样，系统有数据请求就可以被多个磁盘并行的执行，每个磁盘执行属于它自己的那部分数据请求。这种数据上的并行操作可以充分利用总线的带宽，显著提高磁盘整体存取性能;RAID 0 并不是真正的RAID结构，没有数据冗余，没有数据校验的磁盘陈列。实现RAID 0至少需要两块以上的硬盘，它将两块以上的硬盘合并成一块，数据连续地分割在每块盘上。 因为带宽加倍，所以读/写速度加倍， 但RAID 0在提高性能的同时，并没有提供数据保护功能，只要任何一块硬盘损坏就会丢失所有数据。因此RAID 0 不可应用于需要数据高可用性的关键领域。

RAID 1 定义：

两组以上的N个磁盘相互作镜像，在一些多线程操作系统中能有很好的读取速度，理论上读取速度等于硬盘数量的倍数，与RAID 0相同。另外写入速度有微小的降低。只要一个磁盘正常即可维持运作，可靠性最高。其原理为在主硬盘上存放数据的同时也在镜像硬盘上写一样的数据。当主硬盘（物理）损坏时，镜像硬盘则代替主硬盘的工作。因为有镜像硬盘做数据备份，所以RAID 1的数据安全性在所有的RAID级别上来说是最好的。但无论用多少磁盘做RAID 1，仅算一个磁盘的容量，是所有RAID中磁盘利用率最低的一个级别。

READ 5 定义

RAID Level 5是一种储存性能、数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping（硬盘分区）技术。RAID 5至少需要三块硬盘，RAID 5不是对存储的数据进行备份，而是把数据和相对应的奇偶校验信息存储到组成RAID5的各个磁盘上，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，可以利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。RAID 5可以理解为是RAID 0和RAID 1的折衷方案。RAID 5可以为系统提供数据安全保障，但保障程度要比镜像低而磁盘空间利用率要比镜像高。RAID 5具有和RAID 0相近似的数据读取速度，只是因为多了一个奇偶校验信息，写入数据的速度相对单独写入一块硬盘的速度略慢，若使用“回写缓存”可以让性能改善不少。同时由于多个数据对应一个奇偶校验信息，RAID 5的磁盘空间利用率要比RAID 1高，存储成本相对较便宜。

READ 10

RAID 10是先镜射再分区数据，再将所有硬盘分为两组，视为是RAID 0的最低组合，然后将这两组各自视为RAID 1运作。当RAID 10有一个硬盘受损，其余硬盘会继续运作。RAID 01只要有一个硬盘受损，同组RAID 0的所有硬盘都会停止运作，只剩下其他组的硬盘运作，可靠性较低。如果以六个硬盘建RAID 01，镜射再用三个建RAID 0，那么坏一个硬盘便会有三个硬盘离线。

对比

使用固态存储SSD和PCIe卡

特点相对于机械磁盘

相比相比机械磁盘固态磁盘更容易损坏（每次写操作，都要对写入的单元进行擦除操作）

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

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

SSD

特点

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

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

PCI-E SSD

特点

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

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

固态存储的使用场景

### 使用网络存储NAS和SAN

定义

SAN（storage Area Network) 和 NAS（network-attached storage)是两种外部文件存储设备加载到服务器的方法。

区别

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

网络存储适用的场景

数据库备份

网络对性能的影响

磁盘性能的限制

延迟、吞吐量

网络性能的限制

延迟、带宽（512k, 10M)

网络带宽对性能的影响

网络质量对性能的影响（丢包，网络风暴）

建议

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

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

尽可能的进行网络隔离

服务器系统

操作系统对性能的影响

windows大小写不敏感，linux敏感

FreeBSD

Solaris

linux

CentOs系统参数优化

内核相关参数(/etc/sysctl.conf)

增加资源限制（/tec/security/linit.conf)-打开文件数的显示

磁盘调度策略（/sys/block/devname/queue/scheduler)

文件系统对性能的影响

windows

fat

ntfs

linux

挂载参数：/etc/fstab

ext3

ext4

xfs

数据库存储引擎

mysql体系结构

插件式存储引擎

mysql服务层，Select:如何从文件中获得我们所要查询的数据，这个具体的实现则是由下一层存储引擎实现的

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

mysql常用存储引擎之MyISAM

myISAM存储引擎表有MYD和MYI组成

MySQL5.5之前版本默认存储引擎（系统表，临时表：在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表）

特性

命令行：myisampack

check table

repair table myIsam

练习

并发性与锁级别：表级锁

表损坏修复

建立的表在：/usr/local/mysql/var/mysql_study/下(find / -name 'myIsam.frm';)

MyISAM 表支持的索引类型

MyISAM 表支持数据压缩

限制

版本

如存储大表则需要修改MAXROWS和AVGROW_LENGTH

版本 > mysql5.0时默认支持为256TB

使用场景

非事务型应用

只读类应用（压缩功能）

空间类应用（gps函数运算）

mysql常用存储引擎之Innodb

Mysql5.5 及以后版本默认存储的引擎

Innodb 使用表空间进行数据存储 innodbfileper_table on:独立表空间：tablename.ibd off:系统表空间：iddataX （innodb 数据字典信息，undo回滚）

系统表空间和独立表空间要如何选择

系统表空间无法简单的收缩文件大小

独立表空间可以通过optimize table 命令收缩系统文件

系统表空间会产生io瓶颈

独立表空间可以同时向多个文件刷新数据

建议

对innodb使用独立表空间

表转移的步骤（系统-> 独立）

使用mysqldump 导出所有数据库表数据

停止mysql服务，修改参数，并删除innodb相关文件

重启mysql服务，重建innodb系统表空间

重新导入数据

Innodb存储引擎的特性

innode是一种事务性存储引擎

完全支持事务的ACID特性

Redo Log(已经提交事务的log) 和 Undo Log (未提交事务的log)

innode支持行级锁

行级锁可以最大程度的支持并发

行级锁是有存储引擎层实现的。

innodb状态检查：show engine innodb status

什么是锁

锁的主要作用是管理共享资源的并发访问

锁用于实现事务的隔离性

锁的类型

共享锁（也称读锁）

独占锁（也称写锁）

锁的粒度

表级锁 （ )

行级锁

阻塞和死锁

什么是阻塞？

阻塞是由于资源不足引起的排队等待现象。

什么死锁？ 死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。

使用场景

innodb适用大多数OLTP应用

mysql常用存储引擎之CSV

文件存储特点

数据以文本方式存储在文件中

.csv 文件存储表的内容

.CSM文件存储表的元数据如表状态和数据量

.frm文件存储表结构信息

CSV引擎的特点

以CSV格式进行数据存储

所有列必须都是不能为NULL的

不支持索引

可以对数据文件直接编辑，保存文本文件内容

适用场景

适合做为数据交换的中间表

mysql常用存储引擎之Arichive

文件系统存储特点

以zlib对表数据进行压缩，磁盘i/0更少

数据存储在ARZ为后缀的文件中

Archive存储引擎的特点

只支持insert和select操作

只循序在自增id列上加索引

适用场景

日志和数据采集应用

mysql常用存储引擎之Memory

文件系统存储特点

也称HEAP存储引擎，所以数据保存在内存中

功能特点

支持HASH（等值查找）索引和BTree（范围查找）索引

所有字段都为固定长度 varchar(10)=char(10)

不支持blog和text等大字段

memory存储引擎使用表级锁

最大大小由maxheaptable_size参数决定

容易混淆的概念

memory存储引擎表

临时表

超过限制使用myisam临时表

未超过限制使用memory表

系统使用的临时表

create temporary table 建立的临时表

使用场景

用于查找或者映射表，例如邮编和地区的对应表

用于保存数据分析中产生的中间表

用于缓存周期性聚合数据的结果表

注意：Memory数据容易丢失，所以要求数据可再生。

mysql常用存储引擎之Federated

特点

提供了访问远程Mysql服务器上表的方法

本地不存储数据，数据全部放到远程服务器上

本地需要保存表结构和远程服务器的连接信息

如何使用

默认禁止，启用需要在启动时增加federated参数

使用场景

偶尔的统计分析及手工查询

如何选择存储引擎

参考条件

事务

备份

崩溃恢复

存储引擎的特有特性

不要混合使用存储引擎

数据库参数配置

mysql服务器参数

命令行参数

mysqldsafe --datadir=/data/sqldata

配置文件

/etc/my.cnf

/etc/mysql/my.cnf

/home/mysql/my.cnf

~/.my.cnf

mysql配置参数的作用域

全局参数

set global 参数名=参数值;

set @@global.参数名 := 参数值

会话参数

set [session] 参数名=参数值

内存配置相关参数

确定可以使用的内存上限

确定mysql的每个连接使用的内存

下面的配置都是针对线程

sortbuffersize:排序缓存区的大小

joinbuffersize:连接缓存的大小

readbuffersize: 全表查询读的缓冲区大小（4k倍数）

readrndbuffer_size:索引

需要为系统保留多少内存

如何为缓存池分配内存

总内存-（线程连接所需要的内存 * 连接数 ） - 系统保留内存

keybuffersize:针对MyISAM

Innodebufferpool_size

io相关配置参数

innodb i/0 相关配置

先写事务日志，后刷新数据到io

innodblogfile_size

innodblogfilesingroup

事务日志总大小 = innodblogfilesingroup * innodblogfile_size

innodblogbuffer_size

innodbflushlogattrx_commit

安全相关配置参数

expirelogsdays 指定自动清理binglog的天数

maxallowedpacket: 控制mysql可以接受的包的大小

skipnameresolve 禁用dns查找

sysdateisnow 确保sysdate()返回确定性日期

read_only 进制非super权限的用户写权限

skipslavestart禁用slave自动复制

sqlmode 设置mysql所使用的sql模式

stricttranstables

noenginesubtitution

nozerodate

nozeroindate

onlyfullgroup_by：聚合函数中的列必须列出

其他常用配置参数

sync_binlog 控制msyql如何向磁盘刷新binglog

tmptablesize

maxheaptable_size: 控制内存临时表

max_conections 控制允许的最大连接数

数据库结构设计和sql语句

数据库设计对性能的影响

过分的反范式化为表建立太多的列

过分的范式化造成太多的表关联

OLTP环境中使用不恰当的分区表

使用外键保证数据的完整性

总结

性能优化顺序

数据库结构设计和sql语句

数据库存储引擎的选择和参数配置

系统选择及优化

硬件升级