mysql性能功能是什么意思_究竟是什么影响了MySQL的性能

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的限制：

版本

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