什么影响了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 表支持数据压缩

限制

版本

如存储大表则需要修改MAXROWSAVGROW_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语句

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

系统选择及优化

硬件升级

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180706G0E94F00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券