MySQL优化聊两句

LXY3800

　　原文地址:http://www.cnblogs.com/verrion/p/mysql_optimised.html
　　MySQL优化聊两句
　　MySQL不多介绍，今天聊两句该如何优化以及从哪些方面入手，很多运维从业者一说起优化就不知所措，当运营过程中某个参数值到达一定阀值之后，就会出现各种问题，很多运维工程师这时不知所措，第一可能也从来没有处理过类似情况，另一方面业务又紧张，系统不正常，首要任务是解决问题，那没办法只能重启了，我们先不说重启是否可行，比如有些应用可以重启并且解决了问题，但如没有解决问题，或者爆发出新的问题怎么办，比如血崩情况。所以我们应该从问题本身出发，第一查看日志，然后再具体问题具体分析等。
　　上面说了故障处理的一方面解决办法，同样适合于优化处理之道，根据本人学习和经验，这里说一下我的优化思路：首先要有这点认同感，任何系统应用都一定能达到瓶颈的时候，那我们为什么不事先去想想瓶颈在哪里，而不是当问题出现了，才去临时抱佛脚。尤其对于运维来说，任何事情都要具有可把控性，预先想好大部分情况的预案和解决措施。
　　为什么要优化：
　　系统在现有的环境中达到处理能力的最大限制，可能多一个请求或者连接都能对业务造成影响
　　如何优化：
　　问题关键在于：硬件资源（CPU、内存、磁盘、网络）等已经负荷啦。这样我们不很明了了，找出是哪个部分使硬件资源超负荷了，然后采取相应的措辞去调整，优化是个过程，直至系统已经优化足够好了，还是没有解决问题，只有横向扩展加硬件资源啦。
　　根据上面所说的，我总结一下，mysql(其他的也一样)优化应该从以下几点出发，从体系架构层面一层一层的往上讲解：
　　1、底层硬件层：最基础的也是最重要的，下面几层的优化最终是解决这层瓶颈；
　　2、操作系统层：搭载怎样的操作系统、文件系统、网络参数等选型和调优；
　　3、集群架构层：业务发展架构也要随之发展；
　　4、数据应用层：mysql安装、配置文件参数设置等；
　　5、SQL调优层：SQL语句优化，好的sql语句成功案例能提高70%的性能；
　　6、行为模式层：安全、流程、制度等优化；
　　下面一一说道，由于本人水平有限，有错误之处，请批评指正
　　一：底层硬件层
　　1、硬件资源列表
　　硬件资源
　　采购或购买云服务器配置参考
　　CPU
　　64位，2-16颗粒，L2越大越好
　　内存
　　96/128G跑3/4个实例；32/64G跑1-2个实例为佳
　　硬盘
　　机械硬盘(SAS)，数量越多越好，转速越高越好
　　单盘能力对比：SAS(300IOPS)>SSD(35000IOPS)
　　性能：SSD>SAS>SATA
　　磁盘阵列
　　性能：RAID0>RAID10(推荐)>RAID5(少用)>RAID1
　　注意：主库选择raid10，从库可选raid0/raid5/raid10，从库配置等于或大于主库
　　网卡
　　至少千兆网卡及千兆万兆交换机
　　多块网卡bond设置
　　注意：数据库属于IO密集型服务，硬件尽量避免使用虚拟化。
　　Slave硬件资源要等于或大于Master的
　　2、硬件配置列表(云服务跳过这一步)
　　设置选项
　　具体操作
　　BIOS系统
　　CPU优化设置（以DELL系列服务器为例）：
　　1.打开Perfirmance Per Watt Optimeized（DAPC）模式，提高CPU运算能力
　　2.打开CIE和C States等选项，减少cpu调度算法时间，提升效率
　　内存优化设置
　　1. Memory Frequency（内存频率）选择Maximum Performance（最佳性能）
　　2.内存设置菜单中，启动Node Interleaving，避免NUMA问题
　　磁盘阵列卡
　　1.阵列卡要具有CACHE及BBU模块
　　2.设置写策略为write back(感兴趣可以了解Cache两种写策略：write-through与write-back )，并且关闭阵列预读策略
　　二：操作系统层
　　1、文件系统相关
　　优化层级
　　具体操作
　　操作系统
　　无疑选择x86_64架构，（RedHat>CentOS）基于6.8稳定版
　　数据规划
　　基于物理块层面分别存放操作系统文件、mysql应用和数据文件
　　交换分区
　　不要使用swap空间，酌情使用共享内存/dev/shm
　　软件磁盘阵列
　　不要使用
　　LVM逻辑卷
　　不要使用
　　注意：下面几点设置目的是为了提升I/O性能
　　I/O调度算法
　　使用deadline调度参数，建议 read_expire = 1/2 write_expire
　　echo 500 > /sys/block/sdc/queue/iosched/read_expire
　　echo 1000 > /sys/block/sdc/queue/iosched/write_expire
　　xfs文件系统
　　业务量不是很大可采用ext4，业务量很大推荐使用xfs：并且调整XFS文件系统日志和缓冲变量
　　数据目录挂载
　　挂载设置以下参数：
　　async:往硬盘写数据时先写入内存缓冲区,待硬盘空闲时再同步下来，大大提升效率，缺点就是如果服务器宕机或不正常，会损失缓冲区中未写入磁盘的数据，解决办法：利用主板电池或UPS不间断电源供电；
　　noatime:access文件时不更新inode的时间戳，高并发环境下，可以提高系统I/O性能，对select操作尤为重要；
　　nodiratime:不更新系统上的directory inode时间戳，可以提高系统I/O性能
　　nobarrier:不建议使用raid卡电池
　　2、网络参数相关
　　内核参数
　　具体操作
　　swappiness
　　1.cat /proc/sys/vm/swappiness 默认为60
　　2.echo “vm.swappiness=10” >> /etc/sysctl.conf(一般设置0-10)
　　3.sysctl -p
　　ratio
　　vm.dirty_background_ratio设置为5-10
　　vm.dirty_ratio设置为它的两倍左右，
　　目的：确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待 ，方法如上
　　TCP相关参数
　　注意：熟悉TCP11种状态转换原理机制
　　1、减少TIME_WAIT(基本所有应用都要配置)
　　net.ipv4.tcp_tw_recyle=1
　　net.ipv4.tcp_tw_reuse=1
　　2、减少FIN-WAIT-2状态时间
　　net.ipv4.tcp_fin_timeout=10
　　3、减少TCP KeepAlived连接侦测的时间
　　net.ipv4.tcp_keepalived_time=600
　　4、提高系统最大SYN半连接数（默认1024）排队长度
　　net.ipv4.tcp_max_syn_backlog = 16384
　　5、减少系统SYN连接重试次数(默认5)
　　net.ipv4.tcp_synack_retries = 1
　　6、抛弃在内核里建立的连接之前发送SYN包的数量
　　net.ipv4.tcp_sync_retries = 1
　　7、允许系统打开的端口范围
　　net.ipv4.ip_local_prot_range = 4500 65535
　　网络相关参数
　　注意：熟悉网络相关知识
　　1、调整socket套接字缓冲区
　　net.core.rmem_max=16777216 #最大socket读buffer
　　net.core.wmem_max=16777216 #最大socket写buffer
　　net.core.wmem_default = 8388608
　　net.core.rmem_default = 8388608
　　2、调整TCP接收/发送缓冲区
　　net.ipv4.tcp_rmem=4096 87380 16777216
　　net.ipv4.tcp_wmem=4096 65536 16777216
　　net.ipv4.tcp_mem = 94500000 915000000 927000000
　　3、调整网络设备接收队列
　　net.core.netdev_max_backlog=3000
　　其他优化
　　net.ipv4.tcp_max_orphans = 3276800
　　net.ipv4.tcp_max_tw_buckets = 360000
　　三：集群架构层
　　架构设计
　　注意事项
　　实例
　　根据服务器硬件资源和业务需求，一般跑2-4个
　　方案
　　Mysql架构多种多样，扩展性极强，这里不详细介绍
　　如：主从复制一主多从架构，采用mixed模式
　　业务量数据量大的优化架构：
　　1、读写分离：通过程序或者dbproxy，主写从读；
　　2、垂直分库，水平分表（一般建议单表不超过4000万）
　　数据扫描
　　周期性使用pt-table-checksum、pt-table-sync来检查并修复主从复制的数据差异
　　缓存机制
　　DB层前端添加cache层，比如memcached/redis，用来存储session、token、好友任务排行榜等某些业务
　　静态化
　　动态的数据库静态化：比如整个文件静态化，页面字段静态化
　　规避选项
　　比如：SQL有大量模糊查询业务，尽量避免使用mysql数据库
　　注意：数据库架构是一门很大的学问，水平有限只是简单聊两句
　　四：数据应用层
　　注意：下面参数值的优化主要针对innodb引擎，如果要使用MyISAM引擎，需要调整key_buffer_size值
　　参数项
　　设置参考
　　innodb_buffer_pool_size
　　物理内存的50-70%左右
　　inno_flush_log_at_trx_commit
　　不丢失数据设置为1，根据业务需求来
　　sync_binlog
　　不丢失数据设置为1，根据业务需求来
　　innodb_file_per_table
　　设置为1：使用独立表空间
　　innodb_data_file_path
　　ibdata1:1G:autoextend
　　innodb_log_file_size
　　256M，这个参数和下面那个参数搭配使用
　　innodb_log_files_in_group
　　2
　　innodb_log_file_size
　　不要设置过大，第一保证记录日志更快，第二保障增量恢复数据库时间更短
　　long_query_time
　　设置慢查询sql的时间
　　max_connection
　　最大连接数，根据业务场景选择
　　max_connection_error
　　最大链接错误数，官方建议10万以上
　　open_files_limit
　　10倍max_connection
　　innodb_open_files
　　同上
　　table_open_cache
　　同上
　　table_definition_cache
　　同上
　　tmp_table_szie
　　Session分配情况，根据业务来设置
　　max_heap_table_size
　　同上
　　sort_buffer_size
　　同上
　　join_buffer_size
　　同上
　　read_buffer_size
　　同上
　　read_rnd_buffer_size
　　同上
　　query cache
　　官方建议关闭，要设置不要超过512M
　　提示：更多内核参数有时间详细介绍，一切来源于官方文档，我只是千万运维的一枚搬运工而已。
　　五：SQL调优层
　　1、数据库表设计的一些想法，DBA必须参数开发
　　设计思路
　　具体操作
　　字符集
　　建议使用utf-8(中文影响)，官方默认用Latin1，因后者快
　　字符串
　　1.固定字符串使用定长char，尽量避免varchar
　　2.变长字符串varchar，不要用char（因UTF8忽略此影响）
　　上面两者如果能预测业务存储长度，长度能短则短
　　约束
　　字段属性添加not、null，并且表结构设计无关的自增列做主键
　　特殊类型
　　1.文本字段(备注，博文内容等)设置为enum类型
　　2.text/blob类型尽量不用，对select查询性能影响极大
　　随机I/O
　　严禁使用select *语句，查询特定需要的列即可
　　索引
　　1.多用复合索引(除特殊建独立索引)，尤其cardinality很小时候
　　如：该列唯一值总数少于255个，就不要建立独立索引
　　2.对于varchar类型，通常取其50%或更少长度创建前缀索引就能满足90%左右查询需求，没必要整个长度创建反而性能降低
　　2、语句优化
　　设计思路
　　具体操作
　　前期准备
　　项目开发阶段，DBA必须参与设计SQL，并查找慢sql
　　配置my.cnf
　　long_query_time = 2
　　log-slow-queries=/data/mysql/slow-log.log
　　log_queries_not_using_indexs = true
　　工具使用
　　日志慢sql分析工具mysqlsla或pt-query-digest，还有很多工具

• 　　根据报告结果自己写脚本在某个固定时间抓取当天慢sql
  
• 　　计划任务某个固定时间点把上面结果发邮件至相关人事分析
  

　　索引检测工具
　　1.定期使用pt-duplicate-key-checker检查并删除重复的索引
　　2.定期使用pt-index-usage检查并删除使用频率很低的索引
　　3.使用pt-online-schema-change来实现大表的online ddl需求
　　SQL设计
　　1.搜索业务，比如like查询，不建议使用MySQL
　　2.不要使用count(*)，可能锁表
　　3.多表连接查询，关联字段使用索引而且尽量一致
　　4.多表连接查询，结果集小的表作为驱动表
　　5. where子句尽量使用union代替子查询
　　SQL拆分
　　大的复杂的sql语句拆分为各个功能性小sql
　　比如：join连表查询，子查询，单表超过4000万条记录等
　　瓶颈问题
　　慢sql解决方法：

• 　　show processlist:找出特定SQL占用的资源影响性能
  
• 　　使用explain或者set profile打印报表
  
• 　　具体问题具体分析，有时研发参与
  

　　六：行为模式层
　　标准规范
　　具体操作
　　启动程序
　　文件权限700，属主和用户组都为mysql
　　超级用户
　　MySQL超级用户root设置复杂的密码，比如：我生成环境干掉root用户，创建其他管理用户并且名字不是大众类型
　　登录行为

• 　　命令行不要暴露密码
  
• 　　备份脚本设置密码保护比如加密(转换成MD5值)
  

　　运维思想
　　运维有种约定：有种配置叫默认，导致很多运维人员不去关注该参数的作用和来源

• 　　删除默认存在的test库
  
• 　　删除无用的用户，只保留root@127.0.0.1和root@localhost
  

　　责任问题
　　禁止一个用户管理所有的DB，建立起一一服务关系
　　权限问题

• 　　禁止开发人员得到生成环境程序连接的密码并且限制连接
  
• 　　禁止开发程序过程中使用不带子句的DDL语句
  
• 　　Phpmyadmin管理工具安全问题
  

　　环境问题

• 　　服务器禁止设置外网IP
  
• 　　定期清理MySQL操作记录文件(.mysql_history)
  
• 　　防SQL注入***：比如nginx+lua编写WAF控制等
  

　　上线行为

• 　　必须制定上线变更流程制度（开发—测试—运维）
  
• 　　测试流程：开发环境—测试环境—线上执行—验证