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

对数据库性能影响的因素:

• 1.服务器硬件(cpu快慢, 磁盘IO快慢,内存多少)
• 2.服务器系统
• 3.数据库存储引擎的选择: (MyISAM : 不支持事务, 表级锁.; InnoDB: 事务级存储引擎, 完美支持行级锁, 事务ACID特性)
• 4.数据库参数配置
• 5.数据库结构设计和SQL语句

服务器硬件

CPU资源和可用内存大小

一，影响数据库：
1，计算密集型的应用：
cpu，可用内存大小
当热数据量远远超过内存大小时，“网络及io资源”也会成为数据库的瓶颈

二，服务器硬件对mysql性能影响
1，选择cpu
密集型的应用：
选择频率高的cpu，可以加快sql处理速度，目前版本mysql只能支持单一cpu执行s q l
高并发的应用（qps高）：
选择核数多的cpu，可以同时处理多个sql
mysql版本：
mysql5.6才对多核cpu有更好的性能
cpu位数：
最好选择64位cpu在64位的操作系统上使用

2，内存
对数据库的读写性能都会有提升
读：
写：比如浏览量写入到内存100才执行一次插入数据库中

磁盘的配置和选择

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

硬盘读取的过程有对快，磁盘的速度就有多快。

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

一，磁盘的配置和选择
1，传统磁盘：
价格低，空间大，读写慢
RAID的定义:
RAID是磁盘冗余队列的简称。 RAID的作用就是可以把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术；增加传统机器硬盘的性能。

使用固态存储SSD或PCIe卡

固态存储SSD或PCIE卡
1，读写快，支持并发，容易损坏
2，固态存储使用的场景：
热数据超出内存容量，可以存在大量随机I/O的场景
可以解决单线程负载的I/O瓶颈

使用网络存储SAN和NAS

1，定义
SAN通过光线连接到服务器，服务器可以将其作为硬盘使用，随机读写慢
NAS设备使用网络连接，通过基于文件的协议如NFS或SMB来访问
2，作用：
网络存储大多用于“数据库备份”
3，网络性能的限制
延迟，带宽
4，网路的质量
经常丢包
建议：
采用高性能和高带宽的网络接口设备和交换机
对多个网卡进行绑定，增强可用性和带宽
尽可能的进行网络隔离

服务器硬件对性能的影响总结

cpu
1、64位的cpu一定要在64系统下
2、高并发，核数重要
3、密集型、复杂sql，频率重要
内存
1、主板最大支持的内存
2、越大越好
io子系统
pcie->ssd->reaid10(普通磁盘)->网络存储（备份数据库）

操作系统对性能的影响

mysql在windows操作系统不区分大小写，在linux操作系统上区分大小写。

文件系统对性能的影响

Windows ：fat ntfs
linux： ext3 ext4 xfs(都有日志功能，对数据安全性很重要，xfs性能更高）
ext3/4挂载参数 /etc/fstab
data=writeback|ordered|journal 代表不同的日志策略
writeback 只有元数据写入到日志，元数据写入和数据写入不同步，innodb有自己的事务日志，最好的选择
ordered 只记录元数据，提供一直性保证，写元数据前，先写数据，使保持一致
journal 在数据写入前会先记录到日志中
noatime 禁止记录访问文件时间
nodiratime 禁止记录访问目录时间

MySQL体系结构

客户端->mysql服务层->存储引擎层
mysql服务层负责:
1 对客户端的连接管理
2 查询缓存
3 查询解析
4 查询优化
存储引擎层负责
如何从磁盘中获取数据,以及使用什么样的组织形式将数据存储到磁盘

MySQL存储引擎

MyISAM

MyISAM的文件
1 xxx.frm 表结构数据
2 xxx.MYD 真是的表数据
3 xxx.MYI 表的索引数据
MyISAM的特性
一 并发性和锁级别
1表级锁
2 写操作对整个表加独占锁,读操作对整个表加共享锁
二 表损坏修复
1 check table tablename
2 repair table tablename
3 这里的修复不是指事物,mysiam表不支持事物
三 索引
1 支持全文索引
2 支持text blog前500个字符建立前缀索引
3 支持数据压缩(mysiampack),压缩后的表只能读,不能进行写操作
四 支持数据压缩
命令：myisampack

限制
1 mysql5.0之前的版本默认表的大小为4G,如果要存储大表,需要修改max_rows和avg_row_length
2 mysql5.0之后默认表的最大大小是256t
适合场景
非事务型应用
只读类应用
空间类应用 Mongodb 可以代替。

Innodb

Innodb存储引擎
MySQL5.5版本之后默认的存储引擎
Innodb使用表空间进行数据存储的。
show variables like 'innodb_file_per_table' 来查看存储空间位置。
ON表示独立空间 OFF表示共享空间

表转移的步骤
把原来存在于系统表空间中的表转移到独立表空间中的方法
步骤：
1.使用mysqldump导出所有数据库表数据
2.停止MySQL服务，修改参数，并删除Innodb相关文件
3.重启MySQL服务，重建Innodb系统表空间
4.重新导入数据

CSV

csv存储引擎：
1.以文本格式存储在文件中
2.csv文件存储表内容
3.frm寸表结构
4.csm文件存储表的元数据（主要是表状态和数据行数等信息）
特点：
1.以csv格式存储数据
2.所有列不能为NULL
3.不支持索引（所以不适合大表，不适合在线处理）
4.可以对数据文件直接编辑

create table mycsv(id int not null, c1 varchar(10) not null, c2 char(10) not null) engine=csv;
insert into mycsv values(1, 'aaa', 'aa'),(2, 'bbb', 'bb');
create index idx_id on mycsv(id);

报：1069错误，不支持索引
适用场景：
作为数据交换的中间表。比如excel-->csv-->mysql

Archive

Archive存储引擎
特点：
1.以zlib对表数据进行压缩，磁盘IO更少
2.只支持insert和select操作。支持行级锁和专用缓冲区，支持高并发操作
3.只允许在自增id列加索引

create table myarchive(id int auto_increment not null, c1 varchar(10), c2 char(10), key(id)) engine=archive;
insert into myarchive(c1,c2) values('aa','bb'),('cc','dd');

适用场景：
日志和数据采集类应用

Memory

memory存储引擎
也称HEAP存储引擎，所有数据保存内存中。重启后数据丢失，表结构不丢失。
功能特点：
1.支持HASH（等值查找）和BTree索引（范围查找）。
2.所有字段都为固定长度 varchar(10)->char(10)。
3.不支持BLOB和TEXT等大字段。
4.表级锁（吞吐性能一般）。
5.表最大大小由max_heap_table_size参数决定

临时表分为系统使用的临时表和create temporary table建立临时表，其中系统使用的临时表又分为超过限制使用MyISAM临时表，未超过限制使用Memory表。

Federated

Federated存储引擎
特点：
1.提供访问远程mysql服务器上表的方法。
2.本地不存数据，数据全部存远程服务器
3.本地需要保存表结构和远程服务器的连接信息

适用场景：
1.偶尔手工查询和统计分析。
grant select,update,insert,delete on remote.remote_fed to fred_link@'127.0.0.1' indentified by '123123';

rename table remote_fed to local_fed;

show create table local_fed\G

如何选择存储引擎

如何选择存储引擎：
1.首选innodb
2.事务支持
3.备份，要支持在线热备（innodb）。mysqldump不是在线热备，需要加锁
4.崩溃恢复：概率小，恢复快
5.存储引擎的特性：聚集索引，地理空间搜索
6.不要混合多种引擎，比如回滚可能会带来问题，比如热备

MySQL服务器参数

获取配置信息路径

• 命令行参数
  mysqld_safe --datadir=/data/sql_data
• 配置文件
  mysqld --help --verbose | grep -A 1 'Default options'
  /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

配置参数的作用域

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

内存配置相关参数

• 确定可以使用的内存上限
• 确定每个连接可以使用的内存
  sort_buffer_size: 排序缓存区大小，当需要排序的时候，MySQL会立即分配该参数指定内存大小的全部内存，配置需谨慎
  join_buffer_size: 连接缓冲区大小，如果一个查询中关联了多张表，就会为每个关联分配一个连接缓存，所以每个查询可能会有多个连接缓存
  read_buffer_size: 对myisam表进行全表扫面时分配读缓存区大小，MySQL只有在有查询需要的时候才会分配该缓存，该参数值大小一定要是4K的倍数
  read_rnd_buffer_size: 索引缓冲区大小，MySQL只有在查询需要时才会分配该缓冲区的内存，并且只会分配需要的大小，而不是参数的大小

以上参数都是为每个线程分配的，如果有100个连接，可能会分配100倍以上内存大小的和，如果以上参数设置过大很可能会造成MySQL内存浪费和溢出，而使MySQL崩溃

• 如何为缓存池分配内存
  Innodb_buffer_pool_size: 该参数定义了Innodb缓存池的大小，对Innodb引擎的性能影响十分重要，Innodb的性能严重依赖于缓存池，因此要保证给它分配足够的内存。如果系统中只使用了Innodb表，那么该参数可以大致使用以下方式进行计算：总内存 - （每个线程所需要的内存 * 连接数）- 系统保留内存
  key_buffer_size: 该参数配置的缓存池主要用户myisam存储引擎。myisam只会缓存索引，数据只会依赖于操作系统的缓存。可以通过以下查询来知道myisam表索引所占用的空间大小：select sum(index_length) from information_schema.tables where engine='myisam';
  需要注意的是，如果系统中使用的全都是Innodb表，也还是要为该参数分配一定的空间，这是因为MySQL的系统表目前还在使用myisam存储引擎。

IO相关配置参数

• Innodb IO相关配置
  Innodb是一种事务型存储引擎，为了减少提交事务时产生的IO开销，Innodb采用了预写日志的方式，也就是在事务提交的时候会先写入日志中，而不是都把修改刷新到数据文件中，这样做是为了提高IO的性能，因为事务提交和修改使数据和索引文件通常都会映射到表空间的随机位置，所以将数据刷新变更到数据文件就会产生大量的随机IO。而记录日志所需要的是顺序IO，所以相比于刷新脏数据到数据文件系统来说，记录事务日志的方法要快得多。一旦事务日志安全写入到磁盘中，事务就算被持久化了，因为即使变更还未写入到数据文件，如果这时候发生了服务器的宕机，这样的情况还是可以使用事务日志来恢复已经提交的事务。
  Innodb_log_file_size: 控制了单个事务日志的大小
  Innodb_log_file_in_group: 控制事务文件的个数
  事务日志的总大小的 = Innodb_log_file_size * Innodb_log_file_in_group
  事务日志是循环使用的，写满一个后才会使用下一个
  一般配置单个日志文件大一点，能记录至少1个小时的数据库事务日志信息。
  事务日志先写到事务日志缓冲区，然后再刷新到磁盘中。
  这个缓冲区是由Innodb_log_buffer_size参数来控制的，不用设置太大，因为至少1秒会刷新一次磁盘，所以缓冲区保存个几秒钟的事务就可以。一般32MB~128MB。
  刷新事务日志的频繁程度会对性能影响更大，这个频繁度由下面这个参数决定：
  Innodb_flush_log_at_trx_commit：
  0：每秒进行一次log写入cache，并flush log到磁盘（mysql进程崩溃时会丢失1秒的数据）
  1[默认]：每次事务提交，执行log写入cache，并flush log到磁盘（不会丢失任何数据，安全性高，性能差）
  2[建议]：每次事务提交，执行log写入cache，每秒执行一次flush log到磁盘。（mysql进程崩溃时，不会丢失任何事务。只有在服务器宕机时，才会丢失至少1秒的事务）
  这里cache是操作系统的内存区。
• Innodb其他IO配置
  Innodb_flush_method=O_RIRECT，innodb数据刷新方式，决定了innodb数据文件和日志文件如何跟文件系统进行交互。o_direct通知操作系统不要缓存数据，也不要预读，避免innodb和操作系统的双重缓存。
  Innodb_file_per_table=1，控制innodb如何使用表空间，设置为1时，innodb为给每张表建立一个单独的表空间，否则所有表都会存在系统表空间中。强烈建议设置这个参数。
  Innodb_doublewrite=1，控制innodb的双写缓存，避免页没有写完整导致的数据损坏。innodb默认页大小是16k，当系统崩溃，或程序bug导致一个磁盘写操作不能完整的完成，会造成16K数据不完整写到数据文件，并导致文件损坏。所以建议设置这个参数，虽然设置后会对性能有些影响，但影响不大。
• Myisam IO相关配置
  delay_key_write:
  OFF 每次写操作后刷新键缓冲中的脏块到磁盘（最安全，性能最差）
  ON 只对在建表时指定了delay_key_write选项的表使用延时刷新
  ALL 对所有myisam表都使用延迟写入（如果启用延迟写入，在服务器崩溃并且缓存中有的块没有写入到磁盘文件，这时就会造成mysiam表索引文件损坏，这时候需要对myisam表进行修复）

安全配置参数

expie_logs_days: 指定自动清理binlog的天数，至少应该设置两次全备所间隔的天数。即便每天都全备，也至少要把binglog保存7天。
max_allowed_packet，控制mysql可以接收的最大包的大小，也会影响一个用户定义变量的最大容量。默认值一般太小，需要调整大些（比如32M）。主从同步时，主从配置最好一致。
skip_name_resolve，禁用dns查找，建议启动这个选项来禁止dns。
sysdate_is_now，确保sysdate()返回确定性日期
read_only，禁止非super权限的用户写权限，配置到从库中，只接受从主库中传输过来的数据变更。另外，就是别给普通用户授予super权限。
skip_slave_start，禁用slave自动恢复。禁止mysql在崩溃后自动启用复制。
sql_mode，设置mysql所使用的的sql模式。
常见的sql_mode选项：
strict_trans_tables 数据不能插入到事务引擎中会中断当前操作，对非事务引擎不会有任何影响
on_engine_subtitution 保证在create table 指定的存储引擎是可用的如果不可用创建表也不可能成功
no_zero_date 在严格sql模式下不能吧像0000-00-00的日期写入表中
no_zero_in_date不接受部分0000-00-00这样日期到数据库
only_full_group_by 要求在group by 聚合函数的列 要求全部列出来 否侧sql语句不能执行

其他常用配置参数

sync_binlog 控制mysql 如何向磁盘刷新binlog
默认0 mysql并不会主动刷新有操作系统自己决定刷新
大于0 两次刷新binlog间隔时间 1代表每次有事务提交就刷新磁盘
建议主从复制中的主db设为1
tmp_table_size max_heap_table_size 一起使用
控制内存表临时表大小
max_connections 控制允许的最大连接数 一般 2000 更大

数据库设计对性能的影响

1、不宜过多的列，影响服务端的解析性能。但是列太少会引起关联表，表的关联查询性能更差。
2、关联表不宜过多，10个以内。（最多支持61个表关联）
3、在OLTP环境中使用不恰当的分区。分区键的选择很重要，选的不好则查询时会跨分区查询。分区表最好在OLAP环境或日志类表使用。
4、不建议使用外键约束，过多外键检查影响数据库行能。但建议在关联键上建立相关索引。

性能优化顺序

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