1.1 前言
在进行MySQL的优化之前必须要理解的就是MySQL的查问过程，很多的查问优化工作实际上就是遵循一些准则让MySQL的优化器可能依照料想的正当形式运行而已。更多对于MySQL查问相干参照：http://www.cnblogs.com/clsn/p... 系列文章。
图 - MySQL查问过程
1.2 优化的哲学
优化有危险，涉足需谨慎
1.2.1 优化可能带来的问题
优化不总是对一个单纯的环境进行，还很可能是一个简单的已投产的零碎。优化伎俩原本就有很大的危险，只不过你没能力意识到和预见到！
任何的技术能够解决一个问题，但必然存在带来一个问题的危险！
对于优化来说解决问题而带来的问题,管制在可承受的范畴内才是有成绩。
放弃现状或呈现更差的状况都是失败！
1.2.2 优化的需要
稳定性和业务可持续性,通常比性能更重要！优化不可避免波及到变更，变更就有危险！
优化使性能变好，维持和变差是等概率事件！
切记优化,应该是各部门协同，独特参加的工作，任何繁多部门都不能对数据库进行优化！
所以优化工作,是由业务须要驱使的！！！
1.2.3 优化由谁参加
在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、利用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相干人员独特参加。
1.3 优化思路
1.3.1 优化什么
在数据库优化上有两个次要方面：即平安与性能。平安 ---> 数据可持续性
性能 ---> 数据的高性能拜访
1.3.2 优化的范畴有哪些
存储、主机和操作系统方面:
主机架构稳定性I/O布局及配置
Swap替换分区
OS内核参数和网络问题
应用程序方面:
应用程序稳定性SQL语句性能
串行拜访资源
性能欠佳会话治理
这个利用适不适宜用MySQL
数据库优化方面:
内存数据库构造(物理&逻辑)
实例配置
阐明：不论是在，设计零碎，定位问题还是优化，都能够依照这个程序执行。
1.3.3 优化维度
数据库优化维度有四个:
硬件、系统配置、数据库表构造、SQL及索引
优化抉择
优化老本:硬件>系统配置>数据库表构造>SQL及索引优化成果:硬件<系统配置<数据库表构造<SQL及索引
1.4 优化工具有啥？
1.4.1 数据库层面
查看问题常用工具
mysqlmsyqladmin mysql客户端，可进行治理操作mysqlshow 功能强大的查看shell命令show [SESSION | GLOBAL] variables 查看数据库参数信息SHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息information_schema 获取元数据的办法SHOW ENGINE INNODB STATUS Innodb引擎的所有状态SHOW PROCESSLIST 查看以后所有连贯session状态explain 获取查问语句的执行打算show index 查看表的索引信息slow-log 记录慢查问语句mysqldumpslow 剖析slowlog文件的不罕用但好用的工具
zabbix 监控主机、零碎、数据库（部署zabbix监控平台）pt-query-digest 剖析慢日志mysqlslap 剖析慢日志sysbench 压力测试工具mysql profiling 统计数据库整体状态工具 Performance Schema mysql性能状态统计的数据workbench 治理、备份、监控、剖析、优化工具（比拟费资源）1.4.2 数据库层面问题解决思路
个别应急调优的思路：
针对忽然的业务办理卡顿，无奈进行失常的业务解决！须要立马解决的场景！
1、show processlist2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 后果集>30;
show index from table;
3、通过执行打算判断，索引问题（有没有、合不合理）或者语句自身问题
4、show status like '%lock%'; # 查问锁状态
kill SESSION_ID; # 杀掉有问题的session
惯例调优思路：
针对业务周期性的卡顿，例如在每天10-11点业务特地慢，然而还可能应用，过了这段时间就好了。
1、查看slowlog，剖析slowlog，剖析出查问慢的语句。2、依照肯定优先级，进行一个一个的排查所有慢语句。
3、剖析top sql，进行explain调试，查看语句执行工夫。
4、调整索引或语句自身。
1.4.3 零碎层面
cpu方面
vmstat、sar top、htop、nmon、mpstat内存
free 、ps -aux 、IO设施（磁盘、网络）
iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、vmstat 命令阐明：
Procs：r显示有多少过程正在期待CPU工夫。b显示处于不可中断的休眠的过程数量。在期待I/OMemory：swpd显示被替换到磁盘的数据块的数量。未被应用的数据块，用户缓冲数据块，用于操作系统的数据块的数量Swap：操作系统每秒从磁盘上替换到内存和从内存替换到磁盘的数据块的数量。s1和s0最好是0Io：每秒从设施中读入b1的写入到设施b0的数据块的数量。反映了磁盘I/OSystem：显示了每秒产生中断的数量(in)和上下文替换(cs)的数量Cpu：显示用于运行用户代码，零碎代码，闲暇，期待I/O的CPU工夫iostat命令阐明
实例命令： iostat -dk 1 5 iostat -d -k -x 5 （查看设施使用率（%util）和响应工夫（await））tps：该设施每秒的传输次数。“一次传输”意思是“一次I/O申请”。多个逻辑申请可能会被合并为“一次I/O申请”。iops ：硬件出厂的时候，厂家定义的一个每秒最大的IO次数"一次传输"申请的大小是未知的。kB_read/s：每秒从设施（drive expressed）读取的数据量；KB_wrtn/s：每秒向设施（drive expressed）写入的数据量；kB_read：读取的总数据量；kB_wrtn：写入的总数量数据量；这些单位都为Kilobytes。1.4.4 零碎层面问题解决办法
你认为到底负载高好，还是低好呢？
在理论的生产中，个别认为 cpu只有不超过90%都没什么问题 。
当然不排除上面这些非凡状况：
问题一：cpu负载高，IO负载低
内存不够磁盘性能差
SQL问题 ------>去数据库层，进一步排查sql问题
IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位工夫内tps过高）
tps过高: 大量的小数据IO、大量的全表扫描
问题二：IO负载高，cpu负载低
大量小的IO 写操作：autocommit ，产生大量小IO
IO/PS,磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。
大量大的IO 写操作
SQL问题的几率比较大
问题三：IO和cpu负载都很高
硬件不够了或sql存在问题
1.5 根底优化
1.5.1 优化思路
*定位问题点吮吸*
硬件 --> 零碎 --> 利用 --> 数据库 --> 架构（高可用、读写拆散、分库分表）
*解决方向*
明确优化指标、性能和平安的折中、防患未然
1.5.2 硬件优化
主机方面：
依据数据库类型，主机CPU抉择、内存容量抉择、磁盘抉择均衡内存和磁盘资源
随机的I/O和程序的I/O
主机 RAID卡的BBU(Battery Backup Unit)敞开
cpu的抉择：
cpu的两个关键因素：核数、主频依据不同的业务类型进行抉择：
cpu密集型：计算比拟多，OLTP 主频很高的cpu、核数还要多
IO密集型：查问比拟，OLAP 核数要多，主频不肯定高的
内存的抉择：
OLAP类型数据库，须要更多内存，和数据获取量级无关。OLTP类型数据个别内存是cpu外围数量的2倍到4倍，没有最佳实际。
存储方面：
依据存储数据品种的不同，抉择不同的存储设备配置正当的RAID级别(raid5、raid10、热备盘)
对与操作系统来讲，不须要太非凡的抉择，最好做好冗余（raid1）（ssd、sas 、sata）
raid*卡：主机raid**卡抉择：*
实现操作系统磁盘的冗余（raid1）
均衡内存和磁盘资源
随机的I/O和程序的I/O
主机 RAID卡的BBU(Battery Backup Unit)要敞开。
网络设备方面：
应用流量反对更高的网络设备（交换机、路由器、网线、网卡、HBA卡）
留神：以上这些布局应该在初始设计零碎时就应该思考好。
1.5.3 服务器硬件优化
1、物理状态灯：
2、自带治理设施：近程控制卡（FENCE设施：ipmi ilo idarc），开关机、硬件监控。
3、第三方的监控软件、设施（snmp、agent）对物理设施进行监控
4、存储设备：自带的监控平台。EMC2（hp收买了）， 日立（hds），IBM低端OEM hds，高端存储是本人技术，华为存储
1.5.4 系统优化
Cpu：
根本不须要调整，在硬件抉择方面下功夫即可。
内存：
根本不须要调整，在硬件抉择方面下功夫即可。
SWAP：
MySQL尽量避免应用swap。阿里云的服务器中默认swap为0
IO ：
raid、no lvm、 ext4或xfs、ssd、IO调度策略
Swap调整(不应用swap分区)
/proc/sys/vm/swappiness的内容改成0（长期），/etc/sysctl.conf上增加vm.swappiness=0（永恒）这个参数决定了Linux是偏向于应用swap，还是偏向于开释文件系统cache。在内存缓和的状况下，数值越低越偏向于开释文件系统cache。
当然，这个参数只能缩小应用swap的概率，并不能防止Linux应用swap。
批改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式。
这种状况下，InnoDB的buffer pool会间接绕过文件系统cache来拜访磁盘，然而redo log依旧会应用文件系统cache。
值得注意的是，Redo log是覆写模式的，即便应用了文件系统的cache，也不会占用太多
IO调度策略
#echo deadline>/sys/block/sda/queue/scheduler 长期批改为deadline永恒批改
vi /boot/grub/grub.conf更改到如下内容:kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet1.5.5 零碎参数调整
Linux零碎内核参数优化
vim /etc/sysctl.conf net.ipv4.ip_local_port_range = 1024 65535 # 用户端口范畴 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.tcp_fin_timeout = 30 fs.file-max=65535 # 零碎最大文件句柄，管制的是能关上文件最大数量 用户限度参数（mysql能够不设置以下配置）
vim /etc/security/limits.conf * soft nproc 65535 * hard nproc 65535 * soft nofile 65535 * hard nofile 655351.5.6 利用优化
业务利用和数据库利用独立,
防火墙：iptables、selinux等其余无用服务(敞开)：
chkconfig --level 23456 acpid off chkconfig --level 23456 anacron off chkconfig --level 23456 autofs off chkconfig --level 23456 avahi-daemon off chkconfig --level 23456 bluetooth off chkconfig --level 23456 cups off chkconfig --level 23456 firstboot off chkconfig --level 23456 haldaemon off chkconfig --level 23456 hplip off chkconfig --level 23456 ip6tables off chkconfig --level 23456 iptables off chkconfig --level 23456 isdn off chkconfig --level 23456 pcscd off chkconfig --level 23456 sendmail off chkconfig --level 23456 yum-updatesd off装置图形界面的服务器不要启动图形界面 runlevel 3
另外，思考未来咱们的业务是否真的须要MySQL，还是应用其余品种的数据库。用数据库的最高境界就是不必数据库。
1.6 数据库优化
SQL优化方向：
执行打算、索引、SQL改写
架构优化方向：
高可用架构、高性能架构、分库分表
1.6.1 数据库参数优化
调整：
实例整体（高级优化，扩大）：
thread_concurrency # 并发线程数量个数 sort_buffer_size # 排序缓存 read_buffer_size # 程序读取缓存 read_rnd_buffer_size # 随机读取缓存 key_buffer_size # 索引缓存 thread_cache_size # (1G—>8, 2G—>16, 3G—>32, >3G—>64)连贯层（根底优化）
设置正当的连贯客户和连贯形式
max_connections # 最大连接数，看交易笔数设置 max_connect_errors # 最大谬误连接数，能大则大 connect_timeout # 连贯超时 max_user_connections # 最大用户连接数 skip-name-resolve # 跳过域名解析 wait_timeout # 期待超时 back_log # 能够在堆栈中的连贯数量SQL层（根底优化）
query_cache_size： 查问缓存 >>> OLAP类型数据库,须要重点加大此内存缓存， 然而个别不会超过GB 对于常常被批改的数据，缓存会立马生效。 咱们能够实用内存数据库（redis、memecache），代替他的性能。1.6.2 存储引擎层（innodb根底优化参数）
default-storage-engineinnodb_buffer_pool_size # 没有固定大小，50%测试值，看看状况再微调。然而尽量设置不要超过物理内存70%innodb_file_per_table=(1,0)innodb_flush_log_at_trx_commit=(0,1,2) # 1是最平安的，0是性能最高，2折中binlog_syncInnodb_flush_method=(O_DIRECT, fdatasync)innodb_log_buffer_size # 100M以下innodb_log_file_size # 100M 以下innodb_log_files_in_group # 5个成员以下,个别2-3个够用（iblogfile0-N）innodb_max_dirty_pages_pct # 达到百分之75的时候刷写 内存脏页到磁盘。log_binmax_binlog_cache_size # 能够不设置max_binlog_size # 能够不设置innodb_additional_mem_pool_size #小于2G内存的机器，推荐值是20M。32G内存以上100M起源：http://dwz.date/d7Wz
1.7 参考文献
[1] https://www.cnblogs.com/zishe...[2] https://www.jianshu.com/p/d76...
欢送关注公众号 【码农开花】一起学习成长
我会始终分享Java干货，也会分享收费的学习材料课程和面试宝典
回复：【计算机】【设计模式】【面试】有惊喜哦