究竟是什么影响了MySQL的性能

小灰灰 2020-11-01 07:50 676阅读 0赞

**1.影响数据库性能的几个方面**

服务器硬件

服务器系统

数据库存储引擎的选择（MySQL的插件式存储引擎）

MyISAM：不支持事务，表级锁。

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

数据库参数配置

数据库结构设计和SQL语句的编写和优化

**2.CPU资源和可用内存大小（服务器硬件）**

对MySQL性能有影响的硬件资源：CPU资源和可用内存大小

（1）目前版本的MySQL不支持多CPU对同一SQL的并发处理。

（2）内存的大小直接影响了数据库的效率。

（3）**MyISAM把索引缓存到内存中，而数据通过操作系统来进行缓存。**

（4）**InnoDB会同时在内存中缓存数据和索引，从而提高数据库运行效率。**

（5）内存虽然是越多越好，但是对性能的影响是有限的，并不能通过增加内存来无限的增加性能。

（6）在读取数据时，先读取缓存，缓存没有再读取硬盘；在写入数据时，也可以将数据写入缓存，然后将多次写入变成一次写入，将数据一次性从缓存写入硬盘。

（7）选择内存时应该选择服务器主板支持的最大内存频率，频率越高速度越快。

**3.磁盘的配置和选择**

**无论如何，数据最终都要在磁盘上实现永久存储，所以IO子系统比内存更加重要。**

**常用的磁盘IO系统：**

**使用传统机器硬盘：**

特点：最常见，使用最多，价格性对低，存储空间较大，读写速度较慢。

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

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

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

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

*（1，2为访问时间。3为传输时间）*

**如何选择传统机器硬盘：**

存储容量

传输速度

访问时间

主轴转速

物理尺寸

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

概述：RAID是**磁盘冗余队列**的简称。简单来说RAID的作用就是可以把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。

**常用RAID级别：**

**RAID 0：**最早出现的RAID模式，也称之为数据条带。是组建磁盘阵列中最简单的一种形式，只需要2块以上的硬盘即可，成本低，可以提高整个磁盘的性能和吞吐量。**RAID 0没有提供冗余或错误修复能力**，但是实现成本是最低的。

**RAID 1：**又称为磁盘镜像，原理是把一个磁盘的数据镜像到另一个磁盘上，也就是说数据在写入一块磁盘的同时，会在另一块闲置的磁盘上生成镜像文件，在不影响性能情况下**最大限度的保证系统的可靠性和可修复性。**

**RAID 5：**又称之为分布式奇偶校验磁盘阵列。通过分布式奇偶检验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果两块磁盘失效，则整个卷的数据都无法恢复。

**RAID 10 ：**又称分片的镜像。它是对磁盘先做RAID 1之后对两种RAID 1的磁盘再做RAID 0，所以对读写都有良好的性能，相对于RAID 5 重建起来更简单，速度也更快。

**使用固态存储SSD和PCIE卡：**

特点：相比机械磁盘固态磁盘有更好的随机读写性能，能更好的支持并发，但是也更容易损坏。

使用场景：

适用于存在大量随机I/O的场景

适用于解决单线程负载的I/O瓶颈

使用网络存储NAS和SAN：

含义：NAS和SAN是两种外部文件存储设备加载到服务器上的方法。

区别：

SAN设备通过光纤连接到服务器，设备通过块接口访问，服务器可以将其当做硬盘使用。**顺序读写快，随机读写慢**。

NAS设备使用网络连接，通过基于文件的协议如NFS或SMB来访问。

网络存储使用的场景：

并不适合MySQL数据库存储数据文件。

适合数据库备份

4.MySQL体系结构

客户端

MySQL服务层：包括连接管理器、查询缓存、查询解析、查询优化器。比如select语句也是在MySQL服务层来实现的。

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

5.MySQL常用存储引擎之MyISAM

**1.MySQL5.5之前版本默认存储引擎**

**2.MyISAM存储引擎表由MYD（数据文件）和MYI（索引文件）组成**

**3.MyISAM的特性：**

并发性与所级别

表损坏修复

MyISAM表支持的索引类型（全文索引）

MyISAM表支持数据压缩

**4.MyISAM的限制：**

版本<MySQL5.0时默认表大小为4G

如存储大表则要修改MAX\_Rows和AVG\_ROW\_LENGTH

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

**5.适用场景：**

非事务型应用

只读类应用

空间类应用

6.MySQL常用存储引擎之Innodb

**Innodb使用表空间进行 数据存储**

参数：innodb\_file\_per\_table

参数为on，则表示独立表空间：tablename.ibd；

参数为OFF，则表示系统表空间：ibdataX.

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

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

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

系统表空间会产生IO瓶颈

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

**建议：**

对Innodb使用独立表空间

**把原来存在于系统表空间中的表转移到独立表空间中的方法：**

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

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

重启MySQL服务，重建Innodb系统表空间

重新导入数据

Innodb存储引擎的特性

Innodb是一种事务型存储引擎

完全支持事务的ACID特性

日志类型：Redo Log 和 Undo Log

Redo Log 实现事务的持久性 存储的是已经提交的事务（提交失败或者回滚），顺序写入。

Undo Log 实现的是未提交的事务，随机读写。

Innodb支持行级锁

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

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

**什么是锁**

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

锁用于实现事务的隔离性

**锁的类型**

共享锁（读锁）

独占锁（写锁）

**锁的粒度**

表级锁

行级锁

**阻塞和死锁**

什么是阻塞：事务中的兼容性关系，比如一个事务的锁需要等待另一个事务的锁的释放。

什么是死锁：两个或者两个以上的事务，在执行过程中，相互占用了对方等待的资源。数据库系统会自动发现死锁，可以由系统自动处理。处理方式是：将死锁中占用资源最少的事务回滚，能使其他事务进行下去。

**Innodb状态检查**

提供了一个独特的性能监控工具：show engine innodb status。如果想使用要在两次间隔时间至少30秒。

**使用场景**

MySQL5.7版本之后开始支持**全文索引**和**空间函数**。

7.MySQL常用存储引擎之CSV

**文件系统存储特点：**

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

.CSV文件存储表内容

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

.frm文件存储表结构信息

**特点：**

以CSV格式进行数据存储

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

不支持索引，不适合大表，不适合在线处理

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

**使用场景：**

适合作为数据交换的中间表。可以将电子表格（excel）存储为CSV文件，存储到MySQL数据目录下。

8.MySQL常用存储引擎之Archive

**文件系统存储特点：**

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

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

**Archive存储引擎的特点：**

只支持insert和select操作

只允许在自增ID列上加索引

**使用场景：**

日志和数据采集类应用

9.MySQL常用存储引擎只Memory

**文件系统存储特点：**

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

**功能特点：**

支持HASH索引和BTree索引

HASH索引适合等值查找

BTree索引适合范围查找

所有字段都为固定长度 varchar（10）= char（10）

不支持BLOG和TEXT等大字段

Memory存储引擎使用表级锁

最大大小由max\_heap\_table\_size参数决定

**容易混淆的概念：**

Memory存储引擎表：

临时表：

系统使用临时表：

超过限制使用Myisam临时表

未超限制使用Memory表

create temporary table 建立的临时表：

**使用场景：**

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

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

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

10.MySQL常用存储引擎之Federated

**特点：**

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

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

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

**如何使用：**

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

mysql://user\_name\[:password\]@host\_name\[:port\_num\]/db\_name/tbl\_name

user\_name,password:本地连接到远程的用户名和密码

host\_name:远程MySQL服务器的IP

port\_num:远程MySQL服务器的端口号

db\_name:我们所要在本地去映射的服务器的名字

tbl\_name:映射的表的名字

**使用场景：**

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

11.如何选择正确的存储引擎

**参考条件：**

事务

备份

崩溃恢复

存储引擎的特有特性

12.MySQL服务器参数

**MySQL获取配置信息路径：**

命令行参数：mysqld\_safe -- datadir=/data/sql\_data

配置文件

**MySQL配置参数的作用域**

全局参数

set global 参数名=参数值；

set @@global.参数名 ：=参数值；

会话参数

set【session】参数名 = 参数值；

set@@session.参数名：= 参数值；

**内存配置相关参数：**

确定可以使用的内存的上限

确定需要为操作系统保留多少内存

如何为缓存池分配内存

Innodb\_buffer\_pool\_size

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

key\_buffer\_size（MyISAM）

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

sort\_buffer\_size

join\_buffer\_size

read\_buffer\_size

read\_rnd\_buffer\_size

**安全相关配置参数：**

expire\_logs\_days 指定自动清理binlog的天数

max\_allowed\_packet 控制MySQL可以接受的包的大小

skip\_name\_resolve 禁用DNS查找

sysdate\_is\_now 确保sysdate()返回确定性日期

read\_only禁止非super权限的用户写权限（保证主从复制的一致性）

skip\_slave\_start 禁用slave自动恢复

sql\_mode 设置MySQL所使用的SQL模式

strict\_trans\_tables

no\_engine\_subtitution

no\_zero\_date

no\_zero\_in\_date

only\_full\_group\_by

究竟是什么影响了MySQL的性能

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关是什么影响了MySQL索引B+树的高度？

相关 mysql 表空间收缩_MySQL性能及架构设计：什么影响了数据库查询速度与MySQL性能...

相关什么影响了MySQL性能

相关 mysql innodb行级锁的理解，究竟是什么解决了幻读

相关 2. 什么影响了MySQL性能(part1.硬件原因)

相关 ORM究竟是什么？

相关究竟什么是token？？

相关影响mysql性能的因素

相关 MySQL性能管理及架构设计（一）：什么影响了数据库查询速度、什么影响了MySQL性能（收藏）

相关究竟是什么影响了MySQL的性能