我的MYSQL抗造

一、服务器硬件

1、cpu(频率和数量)

（1）cpu频率

cpu密集型：主要对sql执行效率，目前mysql不支持多cpu对同一sql并发处理；

（2）cpu数量（web应用）

主要提高吞吐量和并发处理量；

例子：

 2、内存大小

内存越多越好，但根据实际情况增加

内存的选择：

建议：内存的主频和cpu的主频类似，选择主板支持的最大内存

注意：（1）组成购买升级

（2）每个通道的内存：相同品牌、颗粒、频率、电压、检验技术和型号；

（3）单条容量内存尽可能大；

3、磁盘的配置和选择

磁盘性能的限制： 延迟和吞吐量

（1）传统机器硬盘

考虑因素：存储容量；传输速度；访问时间；主轴转速；物理尺寸

（2）使用RAID增强传统机器硬盘的性能

考虑因素：raid级别：比如raid0(速度最快)，raid1(可靠性)，raid5(以读效率最好)，raid10(读写性能相对较好)

备注：raid卡最好选择有缓存功能的

（3）使用固态存储SSD和PCIe卡

考虑因素：随机读写性能相对更好；相对更好支持并发；对比更容易损坏；价格相对较高

SSD使用场景：

1)适合于存在大量随机I/O的场景；

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

（4）使用网络存储NAS和SAN

1)SAN--存储区域网络

通过光纤连接服务器，设备通过块接口访问，可其当做硬盘使用。

特点：大量顺序读写快，随机读写慢

2)NAS--网络附加存储

通过网络连接，基于文件的协议,如NFS或SMB来访问；

    网络存储适用的场景：

数据库备份；

4、网络对性能的影响: 延迟和带宽

（1）网络带宽对性能的影响

（2）网络质量对性能的影响

建议：采用高性能和高带宽的网络接口设备和交换机；

对多个网卡进行绑定，增强可用性和带宽；

尽可能的进行网络隔离；

5、服务器BIOS调整：

提升CPU效率参考设置：

（1）打开Perfirmance Per Watt Optimeized（DAPC）模式，发挥CPU最大性能，数据库通常需要高运算量

（2）打开CIE和C States等选项，目的也是为了提升CPU效率

（3） Memory Frequency（内存频率）选择Maximum Performance（最佳性能）

（4）内存设置菜单中，启动Node Interleaving，避免NUMA问题

6、阵列卡调整：

（1）购置阵列卡同时配备CACHE及BBU模块(机械盘)

（2）设置阵列写策略为WEB，甚至OFRCE WB （对数据安全要求高）（wb指raid卡的写策略：会写（write back））

（3）严禁使用WT策略，并且关闭阵列预读策略.

二、服务器系统

1、windows系统---mysql大小写问题

2、FreeBSD系统---选择最新的

3、Solaris系统

4、Linux系统----Redhat/Centos

Centos系统参数优化：

查看命令：sysctl -a

生效命令：sysctl -p

1、系统内核相关参数（/etc/sysctl.conf）

 //网络参数

net.core.somaxconn = 32768

#web应用中listen函数的backlog默认会给我们内核参数的net.core.somaxconn限制到128，而nginx定义的NGX_LISTEN_BACKLOG默认为511，所以有必要调整这个值。

net.core.netdev_max_backlog = 65535

#每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目。

net.ipv4.tcp_max_syn_backlog = 65535

#未收到客户端确认信息的连接请求的最大值

//控制tcp等待时间参数，加快tcp回收，实现高负载

net.ipv4.tcp_tw_reuse = 1

#表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；

net.ipv4.tcp_tw_recycle = 1

#表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭；

net.ipv4.tcp_fin_timeout = 10

#修改系統默认的 TIMEOUT 时间;

//以下四个参数表示tpc socket接受和发送缓冲区的默认值和最大值

net.core.wmem_default = 87380

net.core.rmem_max = 16777216           #最大socket读buffer,可参考的优化值:873200

net.core.rmem_default = 8388608

net.core.wmem_max = 16777216           #最大socket写buffer,可参考的优化值:873200

优化TCP接收/发送缓冲区

# Increase Linux autotuning TCP buffer limits

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

// 以下三个参数用于减少失效链接所占用的tcp系统资源，加快资源回收效率

查看命令（sysctl -a|grep tcp_keepalive）

net.ipv4.tcp_keepalive_time = 600

#表示当keepalive起用的时候，TCP发送keepalive消息的频度；减少TCP KeepAlived连接侦测的时间，使系统可以处理更多的连接。缺省是2小时，改为10分钟。

net.ipv4.tcp_keepalive_intvl = 30

#当探测没有确认时，重新发送探测的频度。缺省是75秒。

net.ipv4.tcp_keepalive_probes = 3

#认定连接失效之前，发送多少个TCP的keepalive探测包。缺省值是9。这个值乘以tcp_keepalive_intvl之后决定了，一个连接发送了keepalive之后可以有多少时间没有回应

net.ipv4.tcp_syncookies = 1

#表示开启SYN Cookies。当出现SYN等待队列溢出时，启用cookies来处理，可防范少量SYN***，默认为0，表示关闭；

net,ipv4.tcp_synack_retries = 1

#减少系统SYN连接重试次数(默认5)

net.ipv4.tcp_sync_retries = 1

#在内核放弃建立的连接之前发送SYN包的数量

net.ipv4.ip_local_prot_range = 4500 65535

#允许系统打开的端口范围

net.ipv4.tcp_max_tw_buckets = 4096

# 系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息

net.ipv4.tcp_max_syn_backlog = 4096

# 进入SYN队列最大长度，加大队列长度可容纳更多的等待连接（默认1024）

//内存参数

#设置最大内存共享段大小bytes

kernel.shmmax = 68719476736

#重要参数之一，用于定义单个共享内存段的最大值。

注意：

1）建议设置的足够大，以便一个共享内存段容纳整个的Innodb缓存池的大小；

2）可取最大为物理内存-1byte,建议值大于物理内存的一半，一般取值大于innodb缓冲池的大小即可。

kernel.shmall = 4294967296

#系统一次可以使用的共享内存大小，以页为单位；Linux 共享内存页大小为4KB,shmall=shmmax/4;

vm.swappiness=0

#内存交换分区；当物理内存使用到100%时使用内存交换分区；

备注：

如果禁用内存交换分区会降低操作系统的性能；容易造成内存溢出，崩溃，会别系统kill掉。

在MySQL的服务器上最好设置vm.swappiness=1或0

//文件缓存

vm.dirty_background_ratio = 10

vm.dirty_background_bytes = 0

vm.dirty_ratio = 20

vm.dirty_bytes = 0

vm.dirty_writeback_centisecs = 500

vm.dirty_expire_centisecs = 3000

vm.dirty_background_ratio 是内存可以填充“脏数据”的百分比。这些“脏数据”在稍后是会写入磁盘的，pdflush/flush/kdmflush这些后台进程会稍后清理脏数据。

vm.dirty_ratio 是绝对的脏数据限制，内存里的脏数据百分比不能超过这个值。如果脏数据超过这个数量，新的IO请求将会被阻挡，直到脏数据被写进磁盘。这是造成IO卡顿的重要原因，但这也是保证内存中不会存在过量脏数据的保护机制。

vm.dirty_expire_centisecs 指定脏数据能存活的时间。在这里它的值是30秒。当 pdflush/flush/kdmflush 进行起来时，它会检查是否有数据超过这个时限，如果有则会把它异步地写到磁盘中。毕竟数据在内存里待太久也会有丢失风险。

vm.dirty_writeback_centisecs 指定多长时间 pdflush/flush/kdmflush 这些进程会起来一次。

备注：

  将vm.dirty_background_ratio设置为5-10；

将vm.dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待

查看内存中有多少脏数据：

  cat /proc/vmstat | egrep "dirty|writeback"

备注：根据实际线上调整相关参数，更多可以参考官方

2、文件系统层优化

（1）调整磁盘Cache mode

启用WCE=1(Write Cache Enable),RCD=0(Read Cache Disable)模式

命令：sdparm -s WCE=1,RCD=0 -S /dev/sdb

（2）采用Linux I/O scheduler算法deadline（参考Linux栏目详细配置）

磁盘调度策略：

# cat /sys/block/sda/queue/scheduler

noop(No Operation) --- 适合闪存设备、RAM及嵌入式系统

   cfq(Completely Fair Scheduler ) ---完全公平调度器

   Deadline ---适合数据库类应用

（3）deadline调度参数

对于Centos Linux建议 read_expire = 1/2 write_expire

命令如下：

      echo 500 > /sys/block/sdb/queue/iosched/read_expire

      echo 1000 > /sys/block/sdb/queue/iosched/write_expire

  （4）文件系统，建议xfs(centos7默认)

Windows：FAT和NTFS

Linux：EXT3、EXT4和XFS

（5）mount挂载文件系统(如果是EXT3和EXT4注意如下选项)

data=writeback ，ordered，journal

选项：async,noatime,nodiratime,nobarrier等

noatime：访问文件时不更新inode的时间戳，高并发环境下，推线显示应用该选项，可以提高系统I/O性能。

async：写入时数据会先写到内存缓冲区,只到硬盘有空档才会写入磁盘,这样可以提升写入效率！风险为若服务器宕机或不正常，会损失缓冲区中未写入磁盘的数据 解决办法：服务器主板电池或加UPS不间断电源。

nodiratime：不更新系统上的directory inode时间戳，高并发环境，推荐显示该应用，可以提高系统I/O性能。

nobarrier：不使用raid卡上电池

例子：/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

（6）资源限制（/etc/security/limits.conf ）---打开文件数的限制。

查看命令：ulimit -a（根据实际情况调整）

备注：limits.conf是Linux PAM(插入式认证模块)

* soft nofile 65535

* hard nofile 65535

* soft noproc 65535

* hard noproc 65535

#上述两个参数控制打开文件数的限制

参数说明：

*    ----表示对所有用户有效

soft----指的是当前系统生效的设置

hard---是代表系统中所能设定的最大值

nofile--指所限制的资源是打开文件的最大数目

noproc--是代表系统中所能设定最大进程数

注意：重启系统才生效

3、关闭seliunx（安全机制详细参考其他文章）

# vim /etc/selinux/config

SELINUX=disabled

4、关闭numa（建议关闭bios）

可以从BIOS，操作系统，启动进程时临时关闭.

三、数据库体系结构

客户端----链接管理器（mysql服务层）----存储引擎

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

1、存储引擎之MyISAM

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

特性：

1）并发性与锁级别----修改加锁级表；读的加共享锁；读写交互不好，单读效率高

2）表损坏修复----容易造成数据丢失

#查看表是否损坏：

mysql > check table tablename；

#修复表：

mysql > repair table tablename；

3）MYISAM表支持的索引类型

4）MYISAM表支持数据压缩---压缩后的表只允许读，不能写

命令：myisampack

例子：myisampack -b tablename.MYI

限制:根据版本不同存储大小不同

场景：

1）非事务型应用

2）只读类应用

3）空间类应用

2、存储引擎之innodb

2.1、innodb使用表空间进行 数据存储

通过innodb_file_per_table状态存储

mysql > show variables like 'innodb_file_per_table';

ON：独立表空间:tablename.ibd

OFF:系统表空间：ibdataX

对比：

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

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

2）系统表空间会产生IO瓶颈；

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

表转移步骤：(系统空间转独立表空间)

1）使用MySQLdump导出所有数据库表数据；

2）停止mysql服务，修改参数，并删除innodb相关文件；

3）重启mysql服务，重建innodb系统表空间；

4）重新导入数据；

两个重要关键字：innodb数据字典信息和Undo回滚段

2.2、两个特殊日志(Redi Log和Undo Log)

1)Redo log文件包含一组log files，通常是物理日志，记录的是数据页的物理修改，其会被循环使用。

 innodb_log_file_size 和 innodb_log_files_in_group

生成两个文件：ib_logfile0和ib_logfile1

2）Undo Log(随机读取)---用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录；

2.3、innodb检查状态

mysql> show engine innodb status\G;

2.4、场景

1）使用于大多数OLTP应用

3、其他存储引擎

（1）CSV存储引擎---以文本方式存储在文件中

特点：

1）以CSV格式进行数据存储；

2）所有列必须都是不能为UNLL的；

3）不支持索引（不适合大表，不适合在线处理）；

4）可以对数据文件直接编辑；

场景：适合最为数据交换的中间表

（2）Archive存储引擎

特点：

1）只支持insert和select操作；

2）只允许在自增id列上加索引；

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

（3）Memory存储引擎

特点：

1）支持HASH索引(等值查找)和BTree索引(范围查找)；

2）所有字段都为固定长度；

3）不支持BLOG和TEXT等大字段；

4）Memory存储引擎使用表级锁；

5）最大大小由max_heap_table_size参数决定(已存无效,如需则重建)；

场景：

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

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

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

（4）Federated存储引擎

特点：

1）提供了访问远程mysql服务器上表的方法；

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

3）本地需要保存表结构和远程服务器的链接信息；

场景：偶尔的统计分析及手工查询中使用

4、如何选择存储引擎

参考条件：事物(innodb)、备份(innodb)、崩溃恢复(MYISAM)、存储引擎的特性

四、数据库参数文件my.cnf 配置

1、mysql基础

（1）mysql配置路径

1）命令行参数（版本不用有所差异）

mysqld_dafe --datadir=/data/sql_data

2）配置文件

查看mysql读取文件顺序命令：

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

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

（2）mysql作用域

（1）全局参数

set global 参数名=参数值；

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

（2）会话参数

set [session] 参数名=参数值；

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

2、内存配置参数

（1）每个连接(线程)使用的内存

sort_buffer_size-----排序缓冲大小

join_buferr_size-----连接缓冲大小

read_buferr_size---读查询操作所能使用的缓冲区大小，4k倍数

read_rnd_buferr_size-随机读的时所使用的索引缓冲区大小

（2）缓存池配置

Innodb_buferr_pool_size-----innodb缓存池

key_buffer_size ----MYISAM缓存池

3、I/O配置参数

（1）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 ----事务日志的大小

innodb_flush_log_at_trx_commit ---刷新日志模式，选项[0-2]建议2

其他的

innodb_flush_method=O_DIRECT---innodb刷新方法

innodb_file_per_table=1 ----打开独立表空间

innodb_doublewrite=1  --开启两次写，保证数据可靠性

   innodb_data_file_path ---mysql的ibdata1建议设置1G，防止高并发受影响

     设置：innodb_data_file_path = ibdata1:1G:autoextend

（2）MyISAM I/O配置

delay_key_write

OFF:每次写操作后刷新键缓冲中的脏块到磁盘(最安全，性能差)；

ON:只对在建表时指定了该选项参数的表使用延迟刷新；

ALL:对所有MYISAM表都使用延迟键写入；（索引易损坏）

4、安全配置

expire_logs_days ---指定自动清理binlog的天数

max_allowed_packet --控制mysql可以接收的包大小

skip_name_resolve----禁止DNS查找

sysdate_is_now---确保sysdate()返回确定性日期

read_only---禁止非super权限的用户写权限(主从里在从库启动，保证数据一致性)

skip_slave_start---禁用Slave自动恢复(主从里在从库启动，阻止mysql重启后自动复制数据)

sql_mode---设置MYSQL所使用的SQL模式（不要轻易改动）

5、其他常用配置参数

sync_binlog ----控制MYSQL如何向磁盘刷新binlog，建议设置为1

tmp_table_size和max_heap_table_size---控制内存临时表大小

max_connections----控制允许的最大连接数

五、数据库结构设计和sql语句

1、需要分析：全面了解产品设计的存储需求；

存储需求

数据处理需求

数据的安全性和完整性

2、逻辑设计：设计数据的逻辑存储结构

数据实体之间的逻辑关系，解决数据冗余

和数据维护异常

3、物理设计：根据所使用的数据库特点进行表结构设计

关系数据库：oracle，SQLServer，Mysql,postgresSQL

非关系数据库：mongodb，Redis，Hadoop

存储引擎：innodb，myisan

4、维护优化：根据实际情况对索引、存储结构等进行优化