@wxf 2018-01-25T10:00:35.000000Z 字数 6794 阅读 991

什么影响了MySQL性能

【实战课】MySql性能管理及架构设计

MySql性能管理及架构设计

什么影响了MySQL性能

服务器硬件对性能的影响

服务器硬件对MySQL性能的影响可总结为如下几点：

CPU
1. 64位的CPU一定要工作在64位的操作系统下
2. 对于并发比较高的场景CPU的数量比频率重要
3. 对于CPU密集性场景和复杂SQL运算来说，CPU频率则越高越好
内存
1. 选择主板所能使用的最高频率的内存
2. 内存的大小对性能很重要，所以内存应尽可能的大。（足够大的内存可以将大量的随机I/O变成顺序I/O，并且对读写数据进行缓存，把多次写合并成一次写）
I/O子系统
对于I/O子系统的性能来说，使用固态存储的PCIe卡的随机I/O性能高于同样使用固态存储的SSD硬盘（这是由于接口性能的优势），但是SSD硬盘又可以直接使用在传统的磁盘接口上，所以使用简单。可是固态存储的成本相对于传统的磁盘较高。处于成本考虑可以使用由多个传统磁盘组成的Raid阵列，若使用Raid建议使用Raid10。而对于网络存储在数据库服务器上我们可以把它当做集中存放备份文件的一种方案进行使用。另外建议大家在使用网络存储作为高可用的方案时一定要谨慎，因为网络存储一旦出现设备问题很难及时得到解决，因而这并不是一个很好的高可用方案。

操作系统对性能的影响

系统参数优化（CentOS）

内核相关参数（/etc/sysctl.conf）
- 网络相关
  
  对于一个tcp链接来说服务器与客户端之间需要三次握手来建立网络链接，当三次握手成功后可以通过netstat命令查看端口状态由监听变为可链接，接着这条链路上就可以传输数据了。
  1. 网络监听队列
    net.core.somaxconn=65535 【设置每个端口最大的监听队列长度，一般设为2048即可】
    net.core.netdev_max_backlog=65535 【网络接口接收数据快于内核处理速度时，允许被发送到队列中的数据包的数量】
    net.ipv4.tcp_max_syn_backlog=65535 【还未获得对方链接的请求可保存到队列中的最大数目】
  2. tcp链接回收速度
    net.ipv4.tcp_fin_timeout=10 【用于控制tcp链接的等待状态的时间，可适当减少等待状态的时间，以加快tcp链接的回收速度】
    net.ipv4.tcp_tw_reuse=1
    net.ipv4.tcp_tw_recycle=1
  3. tcp链接缓冲区大小
    net.core.wmem_default=87380 【发送套接字缓冲区大小的默认值(以字节为单位)】
    net.core.wmem_max=16777216
    net.core.rmem_default=87380 【接收套接字缓冲区大小的默认值(以字节为单位)】
    net.core.rmem_max=16777216
    4.失效链接占用资源的数量
    net.ipv4.tcp_keepalive_time=120
    net.ipv4.tcp_keepalive_intvl=30
    net.ipv4.tcp_keepalive_probes=3
- 内存相关
  kernel.shmmax=4294967295 【定义单个共享内存段的最大值，该值应该足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小。说明：对于64位系统，可取值的最大值为物理内存值-1byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可。】
  vm.swappiness=0 【当内存不足时，该参数会对性能产生比较明显的影响。该参数是告诉Linux内核除非虚拟内存完全满了，否则不要使用交换分区。】

增加资源限制(/etc/security/limit.conf)
* soft nofile 65535
* hard nofile 65535
将上面两个参数加到limit.conf文件的末尾即可，需重启操作系统才能生效。
说明：

关键字	说明
*	表示对所有用户有效
soft	指的是当前系统生效的设置
hard	表明系统中所能设定的最大值
nofile	表示所限制的资源是打开文件的最大数目
65535	限制的数量

硬盘调度策略(/sys/block/设备名/queue/scheduler)
参考：磁盘IO调度算法

文件系统对性能的影响

Windows
- FAT:
- NTFS:(作为服务器时的选择)
Linux
- EXT3:
- EXT4:
- XFS:(性能略高)

MySql的体系结构

image_1bt9umjr2118j13f5bgp991t3g9.png-82.1kB

说明：
客户端(Java、C API、.Net以及JDBC等)，在客户端层面主要完成的是链接处理授权认证等功能。每个链接到MySql的客户端都会在进程中拥有一个线程。链接的查询只会在这个单独的线程中执行，也就是每个链接的查询只能用到一个CPU的核心。
MySql服务层，包括链接管理器、查询缓存、查询解析、查询优化器以及所有的内置函数等。
存储引擎层(innodb，myisam，XtraDB等)，了解MySql存储引擎对我们后面优化MySql性能有重要的影响。

MySql常用存储引擎

常用存储引擎-MyISAM

MySql5.5之前版本默认存储引擎

特性：

并发性与锁级别：MyISAM使用的是表级锁，其对读写的并发性支持的不够好，如果是只读操作性能还可以接受。
表损坏修复：MyISAM引擎支持由于任意关闭而造成的数据表损坏时的修复操作(不同于事务恢复)，所以可能会造成数据丢失。命令：check table tablename(检查表状态)；repair table tablename(修复数据表)
MyISAM表支持的索引类型：其支持全文索引，还支持对text等类型的字段建立前500字符的前缀索引。
MyISAM表支持数据压缩：即当数据导入到表中后不再进行修改操作时可以对MyISAM表进行压缩。命令：myisampack -b 表名.MYI。

使用场景：

非事务型应用
只读类应用
空间类应用（如：GPS数据、空间函数）

常用存储引擎-Innodb

MySql5.5之后版本默认存储引擎，Innodb是事务型存储引擎，它是支持事务的ACID特性的。Innodb使用表空间进行数据存储。

Innodb有表空间的概念，表中的数据是存储在表空间的。而参数innodb_file_per_table决定了使用什么样的表空间，若值为NO则表示数据会存储到独立表空间(tablename.ibd);若值为OFF则表示数据会存储到系统共享表空间(ibdataX)。

查看innodb_file_per_table值的方式：show variables like 'innodb_file_per_table';
设置innodb_file_per_table值的方式：set global innodb_file_per_table = off;

那么问题来了，如何选择系统表空间和独立表空间呢？
1.比较：

系统表空间无法简单的收缩文件大小，
独立表空间可以通过optimize table命令收缩系统文件
系统表空间会产生I/O瓶颈
独立表空间可以同时向读个文件刷新数据

2.建议：使用独立表空间

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

使用mysqldump导出所有数据库表数据
停止MySql服务，修改参数，并删除Innodb相关文件
重启MySql服务，重建Innodb系统表空间
重新导入数据
补充：把数据迁移到独立表空间后，系统表空间中还有什么信息呢？
Innodb 数据字典信息（存放与数据库对象相关信息，如表、列、索引、外键等）
Undo 回滚段（在5.7版本后支持从系统表空间中移出，但默认依然存在系统表空间中）

Innodb的特性

Innodb存储引擎的特性

Innodb是一种事务性存储引擎，完全支持事务的ACID[1]特性。为了能够实现原子性、一致性和持久性Innodb使用了两种日志类型：Redo Log（从做日志）和 Undo Log （回滚日志）
- Redo Log主要用于实现事务的持久性，其有两部分组成：
  - ① 内存中的Redo Log缓冲区，由innodb_log_buffer_size参数决定,查看方式：show variables like 'innodb_log_buffer_size'。
  - ② Redo Log文件，也就是文件系统中以ib_logfile开头的文件，该文件数量由innodb_log_files_in_group参数决定。
- Undo Log主要作用是帮助未提交事务进行回滚和多版本并发控制。而Redo Log中存放的主要是已提交的事务，因此当我们对Innodb中的表进行修改时不仅会产生Redo Log而且还会产生Undo Log。
Innodb支持行级锁，行级锁可以最大限度的支持并发（写操作时锁定的资源更少），行级锁是由存储引擎层实现的。
- 什么是锁？ ① 锁的主要作用是管理共享资源的并发访问。 ② 锁实现了事务的隔离性。
- 锁的类型
  - 共享锁（也称读锁）
  - 独占锁（也称写锁）
  -：写锁读锁
  
  写锁不兼容不兼容
  
  读锁不兼容兼容
- 锁的粒度
  - 表级锁
  - 行级锁
Innodb状态检查
命令：show engine innodb status

-：	写锁	读锁
写锁	不兼容	不兼容
读锁	不兼容	兼容

常用存储引擎-CSV

CSV存储引擎是基于CSV格式文件存储数据的。

文件系统存储特点：数据以文本方式存储在文件中（Innodb则是二进制）
.csv文件存储表内容
.csm文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
CSV存储引擎的特点：
以CSV格式进行数据存储（逗号隔开，引号）
所有的列必须都是不能为NULL的
不支持索引（不适合大表，不适合在线处理）
可以对数据文件直接编辑（保存文本文件内容）
应用场景：
适合做为数据交换的中间表。能够在服务器运行的时候，拷贝和拷出文件，可以将电子表格存储为CSV文件再拷贝到MySQL数据目录下，就能够在数据库中打开和使用。同样，如果将数据写入到CSV文件数据表中，其它web程序也可以迅速读取到数据。

常用存储引擎-Archive

文件系统存储特点：
- 以zlib对表数据进行压缩，磁盘I/O更少
- 数据存储在ARZ为后缀的文件中
Archive存储引擎的特点：
- 只支持insert和select操作
- 值允许在自增ID列上加索引
应用场景：
日志和数据采集类应用

常用存储引擎-Memory

也称为HEAP存储引擎，所以数据保存在内存中，具有易失性。

Memory存储引擎的特点：
- 支持HASH索引（适合等值查找）和BTree索引（适合范围查找）
- 所有字段都为固定长度
- 不支持BLOG和TEXT字段类型
- 支持表级锁
- 最大大小由max_heap_table_size参数决定（其对已经创建的数据表无效）
适用场景：
- 用于查找或是映射表，例如邮编和地区的对应表
- 用于保存数据分析中产生的中间表
- 用于缓存周期性聚合数据的结果表

MySql服务器参数

MySql获取配置信息路径
- 命令行参数
  mysqld_safe --datadir=/data/sql_data
- 配置文件(长期使用的配置信息)
  MySql读取配置文件顺序的查看方法：mysqld --help --verbose | grep -A 1 'Default options'
  结果：/etc/my.cnf /etc/mysql/my.cnf /home/mysql/my.cnf ~/.my.cnf
- MySql配置参数的作用域
全局参数
set global 参数名 = 参数值；
set @@global.参数名:=参数值；
以上命令需在MySql客户端执行
会话参数
set [session] 参数名 = 参数值；
set @@session.参数名:=参数值；

内存配置相关参数

确定可以使用的内存的上限。如32位操作系统对一个进程使用的内存是有限制的，又因为MySql是单进程运行的，所以在32位系统中MySql只能使用3G以内的内存。

确定MySql的每个链接使用的内存，禁止给每个链接分配的内存过大。MySql对于一些缓存会单独分配给每个链接的，主要有以下几个：

参数名	名称及作用	注意事项
sort_buffer_size	排序缓存区，该参数定义了MySql每个线程使用的排序缓存区的大小。	MySql并不是在链接初始化时为每个缓冲区分配内存，而是在有排序操作时才会为每个缓冲区分配内存，此时会分配该参数指定大小的全部内存。
join_buffer_size	链接缓存区，该参数定义了MySql每个线程使用的链接缓存区的大小。	如果一个查询关联了多张表，那么就会为每个关联分配一个链接缓存区。所以每个查询可能会有多个链接缓存区。
read_buffer_size	读缓存区，该参数定义了当对MyISAM表进行全表扫描时所分配的读缓存区大小	MySql只会在有查询需要时才会对该缓存分配内存。同样分配该参数指定大小的全部内存。注意：该参数的大小一定要是4K的倍数。
read_rnd_buffer_size	索引缓存区	MySql只会在有查询需要时才会对该缓存分配内存。同样分配该参数指定大小的全部内存。

确定需要为操作系统保留多少内存，建议使用专门的服务器运行MySql。
如何为缓存池分配内存
- innodb_buffer_pool_size,该参数定义了innodb所使用的缓存池的大小，其对数据库性能具有非常大的影响，innodb引擎不仅要缓存索引还要缓存数据等。计算方式：总内存-（每个线程所需要的内存*最大连接数）-系统保留内存。
- key_buffer_size,该参数配置的缓存池主要用于MyISAM引擎。MyISAM引擎只会缓存索引而数据则是依赖操作系统缓存的。查看方式：select sum(index_length) from information_schema.tables where engine = 'myisam';

I/O相关配置参数

这部分参数决定了MySql如何同步缓存区的数据到磁盘上。下面主要介绍一下Innodb引擎的I/O相关参数。

Innodb I/O相关参数

innodb_log_file_size和innodb_log_files_in_group这个两个参数决定了Innodb事务日志（Redo Log）的大小和数量。Innodb是一种事务型的存储引擎，为了减少提交事务时产生的开销Innodb使用了预写日志的方式，也就是每次提交事务时会先将数据写入到事务日志中，而不是每次都把修改后的数据写入到数据文件中，从而提高I/O性能。

innodb_log_file_size,单个事务日志的大小。
innodb_log_files_in_group,事务日志文件的个数。
事务日志总大小=innodb_log_file_size * innodb_log_files_in_group。

注意：事务日志是循环使用的，写满一个文件后才会继续写下一个。
一般来说事务日志的总大小可以记录服务器一个小时左右的事务信息即可。
事务日志并不是每次提交都写到事务日志文件中的，而是先写入到事务日志的缓冲区中。
innodb_log_buffer_size,日志缓冲区大小。一般32M-128M即可。
innodb_flush_log_at_trx_commit,事务日志刷新频次。0：每秒钟进行一次log写入cache，并flush log到磁盘；1[默认]：在每次事务提交执行log写入cache，并flush log到磁盘；2[建议]：每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘。
其他参数及建议
- innodb_flush_method=O_DIRECT，innodb日志文件和数据文件如何与文件系统进行交互。
- innodb_file_per_table=1，innodb如何使用表空间，设置此参数后将为每个表建立单独的表空间。
- innodb_doublewrite=1，innodb是否使用双写缓存。

安全相关配置参数

expire_logs_days,指定自动清理binlog的天数
max_allowed_packet,控制MySql可以接收的包的大小，可设置为32M。如果使用了主从备份结构，则要将该参数设为一致。
skip_name_resolve,禁用DNS查找
sysdate_is_now,确保sysdate()返回确定性日期
read_only,禁止非super权限用户的写权限
skip_slave_start,禁用Slave自动恢复
sql_mode,设置MySql所使用的SQL模式

其他相关配置参数

sync_binlog 控制MySql如何向磁盘刷新binog
tmp_table_size和max_heep_table_size,控制内存临时表大小，这两个值要保持一致，并且不要太大。
max_connections,控制允许的最大连接数

[1] ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability） ↩

什么影响了MySQL性能