mysql 优化学习二

---

影响数据库的性能的几个因素

• 服务器硬件
• 服务器操作系统
• 服务器操作引擎的选择
• 服务器参数配置
• 数据库表结构的设计和SQL语句

CPU资源和可用内存大小

计算密集型的应用CPU越可能影响整个系统的性能，成为系统的瓶颈。
热数据的大小远远超过内存大小时IO系统就可能成为系统的瓶颈。
网络往往发生在大量的数据被查询时，特别是当我们使用memcache这的缓存层时，如果缓存大量失效就会产生大量的网络传输。

如何选择CPU

1. 系统是否是CPU密集型的？

   CPU密集型我们就需要更好的CPU，而不是更多的CPU。MySQL暂时不支持多CPU对同一个SQL并发处理。

2. 我们的系统并发量如何？

   更多的CPU就能够同时处理更多的SQLMySQL一般被应用在Web类的应用系统中，而对于Web类应用并发量一般非常大，所以对于这类应用对于CPU来说核心数比频率更重要

3. 我们所使用的MySQL的版本

   MySQL老版本对多核CPU的支持并不好。MySQL5.0之前的版本限制比较严重。MySQL5.6和5.7有了很大的改善，我们可以放心的使用16核或32核

4. 选择32位还是64位的CPU？

   目前这个没有什么可争议的了，目前MySQL对64位也提供了完美的支持。不过需要注意不要在32位的服务器上使用64位MySQL版本

内存

SSD、Fusion-IO高速内存内存频率越高速度越快，所以我们要选择服务器主板支持的最大内存频率。

常用的MySQL存储引擎如何使用内存：

MyISAM --> 索引 --> 内存    |  --> 数据 --> OS(通过操作系统来缓存)InnoDB --> 索引 --> 内存    |  --> 数据 -----↑InnoDB的数据和索引都会缓存到内存来提高运行效率。数据库的写操作也可以使用内存进行缓存，把多次的写操作合并为一次。

• 内存肯定越多越好
• 对性能影响其实也有限
• 内存增加并不能无线的增加性能(数据库可以使用的内存是有限的)

磁盘

使用传统机械硬盘使用RAID增强传统机器硬盘的性能使用固态存储SSD和PCIe卡使用网络存储NAS和SAN

传统机械硬盘

机械硬盘的特性：

• 存储空间大
• 价格低
• 使用最多
• 最常见
• 读写速度较慢

机械硬盘读取步骤：

1. 移动磁头到磁盘表面上的正确位置
2. 等待磁盘旋转，使的所需的数据在磁头下
3. 等待磁盘旋转过去，所有所需的数据都被磁头读出

第一二步称为访问时间，又称寻址时间第三步称为传输速度

如何选择机械磁盘

1. 存储容量
2. 传输速度
3. 访问时间
4. 主轴转速
5. 物理尺寸

RAID技术

使用RAID增加传统机械硬盘的性能

RAID是磁盘冗余队列的简称(Redundant Arrays of Independent Disks)简单来说RAID的作用就是可以把多个容量比较小的磁盘组成一个容量比较大的磁盘，并提供数据冗余来保证数据完整性的技术

RAID级别：

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

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

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

4. RAID 10
   它是对磁盘先做RAID 1之后对两组RAID 1的磁盘再做RAID 0，所以对读写都有良好的性能，相对于RAID 5重建起来更简单，速度也更快。

固态存储(SSD、PCI-E SSD)

固态存储也叫做闪存。相比于机械磁盘，固态磁盘有更好的随机读写性能，能更好的支持并发，但是固态磁盘更容易损坏。

SSD特点：
1. 使用SATA接口，可以替换传统磁盘而不需要任何改变。
2. SATA接口的SSD同样支持RAID技术。

PCI-E SSD特点(不推荐使用RAID)：
1. PCI-E无法使用SATA接口，需要单独的驱动和配置
2. 价格相对于SSD要贵，但是性能比SSD更好

固态存储的使用场景：
1. 适用于存在大量随机I/O的场景
2. 用于解决单线程负载的I/O瓶颈

网络存储SAN和NAS

SAN(Storage Area Network)和NAS(Network-Attached Storage)是两种外部文件存储设备加载到服务器上的方法。

SAN设备通过光纤连接到服务器，设备通过块接口访问，服务器可以使其当做硬盘使用
SAN可以承载大量的顺序读写，随机读写慢

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

网络存储的使用场景：
1. 不适用于MySQL存储
2. 适用于数据库备份

对性能的影响：
1. 磁盘的性能限制在延迟、吞吐量
2. 网络的性能限制在延迟、带宽

操作系统对性能的影响

centos系统参数优化
内核相关参数：修改/etc/sysctl.conf

端口的最大的监听队列长度net.core.somaxconn=65535 在每个网络接口接受数据包的速率比内核处理机接受数据包快的时候，允许未发送到队列中的数据包的最大数据net.core.netdev_max_backlog=65535还未获得对方链接的请求可保存在队列中的最大数目，超过这个数目的数据包可能会被抛弃net.ipv4.tcp_max_syn_backlog=65535tcp链接处理的等待状态的时间(加快TCP链接的回收)net.ipv4.tcp_fin_timeout=10net.ipv4.tcp_tw_reuse=1net.ipv4.tcp_tw_recycle=1tcp链接接受和发送缓冲区大小的最大值和默认值net.core.wmen_default=87380net.core.wmen_max=16777216net.core.rmen_default=87380net.core.rmen_max=16777216用于较少失效链接占用的tcp系统资源，加快资源回收效率net.ipv4.tcp_keepalive_time=120net.ipv4.tcp_keepalive_intvl=30net.ipv4.tcp_keepalive_probes=3用于定义单个共享内存段的最大值，(1.这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个Innodb缓冲池的大小。2.这个值的大小对于64位linux系统，可取的最大值为物理内存值-1byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可，可以取物理内存-byte)kernel.shmmax = 4294967295当内存不足时会对性能产生较明显的影响(Linux内存交换区)vm.swappines = 0

资源限制：修改/etc/security/limits.conf

打开文件数限制* soft nofile 65535* hard nofile 65535* 表示对所有用户有效soft 指的是当前系统生效设置hard 表明系统中所能设定的最大值nofile 表示所限制的资源是打开文件的数目65535 就是限制的数量把可打开的文件数量增加到65535个可以保证可以打开足够多的文件句柄这个文件的修改需要重启系统才能生效

磁盘调度策略：修改/sys/block/${devname}/queue/scheduler

cat /sys/block/xvda/queue/schedulernoop [deadline] cfq

文件系统对性能的影响

windows包括FAT和NTFS

linux包括EXT3、EXT4、XFS

EXT3和EXT4系统的挂在参数：/etc/fstab
data = writeback | ordered | journal

MySQL体系结构

存储引擎是和表相关的，同一个库中不同的表可以使用不同的存储引擎。
MySQL如何存储和获取数据是由存储引擎决定的。

MySQL存储引擎之MyISAM

MyISAM是MySQL5.58之前版本默认的存储引擎。
MyISAM是mysql的系统表、临时表使用的存储引擎(这里所说的临时表不是通过CREATE TEMPORARY TABLE语句创建的表，而是在排序、分组等操作中，当数量超过一定大小后，有查询优化器创建的磁盘临时表)。

MyISAM存储引擎有MYD(数据文件)和MYI(索引表)组成。

注意：mysql还有一个frm的文件，这个文件和存储引擎无关，这个文件用于记录表的结构。

MyISAM特性：
1. 并发性和锁级别
MyISAM使用的是表级锁，所以对表中的数据进行修改时，需要对整个表加互斥锁，对数据进行读取时，需要加共享锁。MyISAM对与读写混合的并发性并不好，如果只读的操作还是可以接受
2. 损害修复
MySQL支持由于任意意外关闭而损坏的MyISAM表进行检查和修复，但并不是事务恢复，因为MyISAM不是事务性的存储引擎，所以它也不会记录用于事务恢复的相关日志，对MyISAM的表进行恢复，可能会造成数据丢失。
使用check table tablename来对表进行检查
使用repair table tablename来对标进行恢复。
mysql提供了myisamchk命令进行修复MyISAM表，但必须在mysql服务停止的情况下。
3. MyISAM支持的索引(索引保存在内存中)
MyISAM支持全文索引，支持对text、blob等前500字符的前缀索引
MyISAM支持数据压缩(适用于归档的数据)
mysql提供myisampack命令进行数据压缩，它是对数据的单行数据压缩，所以在读取的时候不必对整个表进行解压，但是压缩的表只能进行读操作，不能进行写操作。

MyISAM限制：

1. MySQL版本

MySQL存储引擎之Innodb

MySQL5.58之后版本默认存储引擎
Innodb是事务性的存储引擎，也就是支持事务的ACID特性。Innodb使用表空间进行数据存储。
innodb_file_per_table来决定存储在什么样的存储空间中。
如果innodb_file_per_table=ON 独立表空间: tablename.ibd
如果innodb_file_per_table=OFF 系统表空间: ibdataX(X代表数字)

show variables like 'innodb_file_per_table%';

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

比较：

• 系统表空间无法简单的收缩文件大小
• 独立表空间可以通过optimize table命令收缩系统文件
• 系统表空间会产生IO瓶颈(只有一个文件，多个表操作是顺序进行的)
• 独立表空间可以同时向多个文件刷新数据

mysql5.6之前innodb默认使用系统表空间mysql5.6及其之后innodb默认使用独立表空间

系统表空间会存储Innodb数据字典信息(如表、列、索引、wai)、Undo回滚段和innodb临时表
frm是服务器曾的数据字典，对所有的存储引擎都一样，与存储引擎无关
innodb内部的数据字典是存储引擎内部产生的，并可以保证事务的安全性，innodb并没有使用mysql数据库上的类型，而是自己封装了类型的定义，frm是普通的二进制文件，而innodb数据字典是使用btree进行数据管理的。

Innodb存储引擎的特性

• innodb是事务性的存储引擎
• 完全支持事务的ACID特性
• Redo Log和Undo Log
• innodb支持行级锁、在存储引擎层实现的

Innodb状态检查

show engine innodb status;

Innodb存储引擎使用场景

Innodb适用于大多数的OLTP(联机事务处理过程)应用
MySQL5.7开始支持全文索引和空间函数

Redo Log 主要用于实现事务的持久性，有两部分组成：内存中的重做日志缓冲区(innodb_log_buffer_size)和重做日志文件(ib_logfile0)Undo Log 主要帮助未提交事务进行回滚

什么是锁？

• 锁的主要作用是管理共享资源的并发访问
• 锁用于实现事务的隔离性

锁的类型：

• 共享锁(也称读锁)
• 独占锁(也称写锁)

锁的粒度：

• 行级锁(只在存储引擎中实现)
• 表级锁

lock table tablename write; # 表级独占锁unlock table; # 解锁

阻塞：阻塞是因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放，他所占用的资源就形成了阻塞。阻塞是确保事务的可以并发且正常的运行。死锁：死锁是指两个或两个以上的事务在执行过程中，相互占用了对方所等待的资源。死锁数据库系统都可以自动发现，在多个死锁的事务中，选择占用最少的事务进行回滚操作，这样其他事务就可以执行了，死锁一般系统可以自动处理。

MySQL存储引擎之CSV

文件存储特点：

• 数据以文本方式存储在文件中
• 使用csv存储表数据
• csm文件存储表的元数据如表状态和数据量
• frm MySQL服务器层存储元数据(表结构信息)，存在于所有的存储引擎中

CSV存储引擎特点：

• 以CSV格式进行数据存储
• 所有的列必须都是不能为NULL的
• 不支持索引
• 可以对数据文件直接编辑

CSV存储引擎适用场景：

• 适合作为数据交换的中间表

MySQL存储引擎之Archive

文件存储特点：

• 以zlib对表数据进行压缩，磁盘IO更少
• 数据存储在ARZ为后缀的文件中

Archive存储引擎特点：

• 只支持insert和select操作
• 支持行级锁，和专用的缓冲区，支持高速的插入
• 只允许在自增ID列上加索引

Archive存储引擎适用场景：

• 日志和数据采集类应用

MySQL存储引擎之Memory

文件存储特点：

• 也称为HEAP存储引擎，所有数据保存在内存中
• 表结构在服务器重启后还会有

Memory存储引擎特点：

• 支持HASH索引和Btree索引(默认Hash索引，等值查询很快，范围查询无法使用Hash索引)
• 所有字段都为固定的长度varchar(10) = char(10)
• 不支持BLOG和TEXT等大字段
• Memory存储引擎使用表级锁
• 最大大小有max_heap_table_size参数决定

Memory存储引擎适用场景：

• 用于查找或者是映射表，例如邮编和地区的对应表
• 用于保存数据分析中产生的中间表
• 用于缓存周期性聚合数据的结果表

Memory数据易丢失，所以要求数据可再生

MySQL存储引擎之Federated

Federated存储引擎特点：

• 提供了远程访问MySQL服务器上表的方法
• 本地不存储数据，数据全部放到远程服务器上
• 本地需要保存表结构和远程服务器的连接信息

默认禁止，启用需要在启动时增加federated参数mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Federated存储引擎适用场景：

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

MySQL存储引擎的选择

参考条件：

1. 事务(需要事务优先选择Innodb)
2. 备份(只有Innodb有免费的热备方案)
3. 崩溃回复(MyISAM崩溃后数据损坏的可能性比innodb大)
4. 存储引擎的特有特性
5. 尽量不要混合使用存储引擎

MySQL服务器参数介绍

MySQL获取配置信息路径

• 命令行参数
  mysqld_safe –datadir=/data/sql_data

• 配置文件
  查询不同操作系统中mysql配置文件的顺序

  mysqld --help --verbose |grep -A 1 'Default options'

MySQL配置参数的作用域

• 全局参数
  设置全局参数
  set global 参数名=参数值;
  set @@global.参数名:=参数值;
• 回话参数
  设置回话参数
  set [session] 参数名=参数值;
  set @@session.参数名 := 参数值;

MySQL内存配置相关参数

• 确定可以使用的内存的上限
• 确定MySQL的每个连接使用的内存
  sort_buffer_size join_buffer_size
  read_buffer_size read_rnd_buffer_size
• 确定需要为操作系统保留多少内存
• 如何为缓存池分配内存
  Innodb_buffer_pool_size【总内存-(每个线程所需要的内存*连接数)-系统保留内存】
  key_buffer_size【用于MyISAM】

MySQL IO配置相关参数

Innodb IO相关配置

• Innodb_log_file_size
• Innodb_Log_files_in_group
• 事务日志总大小=Innodb_log_file_size*Innodb_Log_files_in_group
• Innodb_log_buffer_size
• Innodb_flush_log_at_trx_commit
  0:每秒进行一次log写入到cache，并flush log到磁盘
  1:默认的，在每次事务提交执行log写入cache，并flush log到磁盘
  2:建议，每次事务提交，执行logs数据写入到cache，每秒执行一次flush log到磁盘
• Innodb_flush_method=O_DIRECT
• Innodb_file_per_table=1
• Innodb_doublewrite=1

MyISAM IO相关配置

• delay_key_write
  OFF:每次写操作后刷新键缓冲中的脏块到磁盘
  ON:只对在建表时指定了delay_key_write选项的表使用延迟刷新
  ALL:对所有MyISAM表都使用延迟键写入

MySQL 安全相关参数

• 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常用配置

• sync_binlog 控制MySQL如何向磁盘刷新binlog
• tmp_table_size和max_heap_table_size控制内存临时表大小
• max_connections控制允许的最大连接数

数据库设计对性能的影响

• 过分的反范式化为表建立太多的列
• 过分的范式化造成太多的表关联
• 在OLTP环境中使用不恰当的分区表
• 使用外键保证数据的完整性

总结

MySQL优化的顺序
1. 数据库结构设计和SQL语句
2. 数据库存储引擎的选择和参数配置
3. 系统选择和优化
4. 硬件升级