我的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(读写性能相对较好)
等级 | 特点 | 是否冗余 | 盘数 | 读 | 写 |
RAID0 | 便宜、快速、危险 | 否 | N | 快 | 快 |
RAID1 | 高速读、简单、安全 | 有 | 2 | 快 | 慢 |
RAID5 | 安全、成本折中 | 有 | N+1 | 快 | 取决于最慢的盘 |
RAID10 | 贵、高速、安全 | 有 | 2N | 快 | 快 |
备注：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、维护优化：根据实际情况对索引、存储结构等进行优化
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网页名称：我的MYSQL抗造-创新互联
本文来源：http://azwzsj.com/article/gcdjj.html
