MySQL性能管理及架构设计(学习笔记)

慕课网视频: https://coding.imooc.com/class/49.html#Anchor
大神的学习笔记:https://segmentfault.com/a/1190000013672421
下面是我的学习笔记:
影响数据库性能的因素:

服务器硬件选型:
CPU:频率和数量(如果钱包够厚,两者兼顾),
cpu密集型目前mysql不支持多cpu对同一sql并发处理(选频率)
并发量:数量优先
内存:
大小:InnoDB引擎会在内存中缓存索引和数据,所以内存适量大点(硬盘中的数据全部缓存到内存中后,增加内存无法增加性能)
频率:选择cpu所支持的频率最高的内存,多个内存最好统一型号
磁盘:
传统磁盘:便宜,速度慢 ,容量大(也可以使用RAID将多个磁盘组成一组容量更大的磁盘)
SSD:使用SATA接口,也可以使用RAID技术;
PCI-E:性能好,价格贵,需要占用一部分系统资源(内存和CPU)
结论:固态存储适用于大量随机IO的场景.解决单线程负载IO瓶颈.
网络存储:SAN(光纤连接,可以当硬盘使用)和NAS(通过网络连接,有一定的网络延迟)
网络存储的顺序IO性能好,但是随机IO;常用于数据库备份
网络:带宽和质量对性能都有一定影响.
硬件对性能的影响—总结:



服务器系统对性能的影响:
CentOS系统参数优化:
内核相关的参数(/etc/sysctl.conf)
net.core.somaxconn=65535 //最大监听队列的长度
net.core.netdev_max_backlog=65535 //允许被发送到队列中的数目
net.ipv4.tcp_max_syn_backlog=65535 //未获得连接保存再内存中的最大连接数目

net.ipv4.tcp_fin_timeout=10  //用于控制tcp连接处理的等待状态的时间,对于大量连接的需要加快tcp连接回收的速度
net.ipv4.tcp_tw_reuse=1  //和net.ipv4.tcp_fin_timeout 配合使用
net.ipv4.tcp_recycle=1  //和net.ipv4.tcp_fin_timeout 配合使用

//下面四个决定TCP连接缓冲区默认值和最大值
net.core.wmem_default = 87380
net.core.wmem_max = 16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216

//下面3个参数,控制失效连接占用TCP系统资源的数量,加快资源回收效率(对于一些场景来说,默认参数可能大了)
net.ipv4.tcp_keepalive_time =120 //发送tcp探测消息的时间间隔 单位S
net.ipv4.tcp_keepalive_intvl=30 //探测消息未获得响应时,重发消息的时间间隔
net.ipv4.tcp_keepalive_probes=3 //认定连接失效之前发送多少次探测消息

kernel.shmmax =4294967295 //用于定义对单个共享内存段的最大值,应当足够大,以便容下整个innodb缓冲池(建议值大于物理内存的一半,一般大于innodb缓冲池大小即可)
vm.swappiness =0 跟使用交换区有关(是否禁用有一定争议,完全禁用,容易降低操作系统的性能,容易造成缓存溢出,建议保留一部分)vm.swappiness =0 就是告诉系统除非虚拟内存满了,否则不需要使用交换分区

增加资源限制(/etc/security/limit.conf)

磁盘调度策略(/sys/block/devname/queue/scheduler)
cfq noop deadline anticipatory (预调IO策略)
文件系统对性能的影响
windows FAT 和 NTFS
linux 建议使用XFS

MySQL选择存储引擎(一般选择默认就行了)
MySQL5.5及之后版本默认的存储引擎：InnoDB。

MySQL 服务器参数
获取参数:
命令行 mysqld_safe --datadir=/data/sql_data
配置文件 获取文件路径 mysqld --help --verbose | grep -A 1 ‘Default options’
可以找到.my.cnf文件
MySql配置参数
全局参数:
set global 参数名 =参数值
等价于 set @@global.参数名:=参数值
会话参数:
set [session]参数名= 参数值
等价于 set @@session.参数名:=参数值
内存配置相关参数:
确定可以使用内存的上限,大约可以确定每个连接使用的内存
sort_buffer_size:每个排序缓冲区的内存大小
join_buffer_size:每个连接缓存区的内存大小
read_buffer_size:每个连接读缓冲区的大小
read_rnd_buffer_size :索引缓存区的大小
不能分配的太大,如果有很多连接的时候,就很容易内存溢出了
确定为操作系统保留多少内存,如何为缓存池分配内存
Innodb_buffer_pool_size :缓冲池的大小 缓存索引和数据等
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 并刷新log到磁盘
1(默认):每次事务提交执行log写入cache 并刷新到磁盘
2(建议):每次事务提交后执行log数据写入cache,每秒刷新一次log到磁盘
Innodab_flush_method=O_DIRECT(LINUX系统建议)
Innodb_file_per_table=1 如何利用表空间(1,为每个表建立表空间,否则会将表存入系统表空间中)
Innodb_doublewrite=1 是否使用双写缓存 避免没有写完造成的数据损坏 (增加安全性)
安全相关的配置参数:
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模式 控制检查sql语句的宽松程度
其他常用配置:
sync_binlog 控制mysql如何向磁盘中刷新binlog 默认 0 不会主动刷 建议1
tmp_table_size和max_heap_table_size 控制临时表的大小
max_connections 允许最大连接数

对数据库影响最大的应该时数据库的结构设计和sql优化,当结构设计和sql语句没法优化的时候才考虑这些优化

数据库设计对性能的影响
	1\为表建立太多的列
	2\造成太多的表关联(mysql关联表最大为61个)
	3\ 在OLTP环境中使用不恰当的分区表
	4\使用外键保证数据的完整性(效率太低)