MySQL 优化实施方案
1.1 前言
在进行MySQL的优化之前必须要了解的就是MySQL的查询过程，很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。更多关于MySQL查询相关参照：http://www.cnblogs.com/clsn/p/8038964.html#_label6 系列文章。
1.2 优化的哲学
1.2.1 优化可能带来的问题
优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。
优化手段本来就有很大的风险，只不过你没能力意识到和预见到！
任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败！
1.2.2 优化的需求
稳定性和业务可持续性,通常比性能更重要！
优化不可避免涉及到变更，变更就有风险！
优化使性能变好，维持和变差是等概率事件！
切记优化,应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！
所以优化工作,是由业务需要驱使的！！！
1.2.3 优化由谁参与
在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。
1.3 优化思路
1.3.1 优化什么
在数据库优化上有两个主要方面：即安全与性能。
安全 -–> 数据可持续性
性能 -–> 数据的高性能访问
1.3.2 优化的范围有哪些
存储、主机和操作系统方面:
<p class="a">
I/O规划及配置
</p>
<p class="a">
Swap交换分区
</p>
<p class="a">
OS内核参数和网络问题
</p>
应用程序方面:
<p class="a">
SQL语句性能
</p>
<p class="a">
串行访问资源
</p>
<p class="a">
性能欠佳会话管理
</p>
<p class="a">
这个应用适不适合用MySQL
</p>
数据库优化方面:
<p class="a">
数据库结构(物理&逻辑)
</p>
<p class="a">
实例配置
</p>
说明：不管是在，设计系统，定位问题还是优化，都可以按照这个顺序执行。
1.3.3 优化维度
数据库优化维度有四个:
硬件、系统配置、数据库表结构、SQL及索引
优化选择
<p class="a">
优化效果:硬件<系统配置<数据库表结构<SQL及索引
</p>
1.4 优化工具有啥？
1.4.1 数据库层面
检查问题常用工具
不常用但好用的工具
关于zabbix参考：http://www.cnblogs.com/clsn/p/7885990.html
1.4.2 数据库层面问题解决思路
一般应急调优的思路：
针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！
<p class="a">
2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
</p>
<p class="a">
select id,name from stu where id=2-1 函数 结果集>30;
</p>
<p class="a">
show index from table;
</p>
<p class="a">
3、通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题
</p>
<p class="a">
4、show status like '%lock%'; # 查询锁状态
</p>
<p class="a">
kill SESSION_ID; # 杀掉有问题的session
</p>
常规调优思路：
针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。
<p class="a">
2、按照一定优先级，进行一个一个的排查所有慢语句。
</p>
<p class="a">
3、分析top sql，进行explain调试，查看语句执行时间。
</p>
<p class="a">
4、调整索引或语句本身。
</p>
1.4.3 系统层面
cpu方面
内存
IO设备（磁盘、网络）
vmstat 命令说明：
iostat****命令说明
1.4.4 系统层面问题解决办法
你认为到底负载高好，还是低好呢？
在实际的生产中，一般认为 cpu只要不超过**90%**都没什么问题 。
当然不排除下面这些特殊情况：
问题一：cpu负载高，IO负载低
<p class="a">
磁盘性能差
</p>
<p class="a">
SQL问题 ------>去数据库层，进一步排查sql问题
</p>
<p class="a">
IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）
</p>
<p class="a">
tps过高: 大量的小数据IO、大量的全表扫描
</p>
问题二：IO负载高，cpu负载低
_大量小的IO _写操作：
autocommit ，产生大量小IO
IO/PS,磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。
_大量大的IO _写操作
SQL问题的几率比较大
问题三：IO和cpu负载都很高
硬件不够了或sql存在问题
1.5 基础优化
1.5.1 优化思路
定位问题点吮吸
硬件 –> 系统 –> 应用 –> 数据库 –> 架构（高可用、读写分离、分库分表）
处理方向
明确优化目标、性能和安全的折中、防患未然
1.5.2 硬件优化
主机方面：
<p class="a">
平衡内存和磁盘资源
</p>
<p class="a">
随机的I/O和顺序的I/O
</p>
<p class="a">
主机 RAID卡的BBU(Battery Backup Unit)关闭
</p>
cpu的选择：
<p class="a">
根据不同的业务类型进行选择：
</p>
<p class="a">
cpu密集型：计算比较多，OLTP 主频很高的cpu、核数还要多
</p>
<p class="a">
IO密集型：查询比较，OLAP 核数要多，主频不一定高的
</p>
内存的选择：
<p class="a">
OLTP类型数据一般内存是cpu核心数量的2倍到4倍，没有最佳实践。
</p>
存储方面：
<p class="a">
配置合理的RAID级别(raid5、raid10、热备盘)
</p>
<p class="a">
对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas 、sata）
</p>
<p class="a">
<span style="text-decoration: underline;"><strong><em>raid</em></strong><strong><em>卡：主机raid</em></strong><strong><em>卡选择：</em></strong></span>
</p>
<p class="a">
实现操作系统磁盘的冗余（raid1）
</p>
<p class="a">
平衡内存和磁盘资源
</p>
<p class="a">
随机的I/O和顺序的I/O
</p>
<p class="a">
主机 RAID卡的BBU(Battery Backup Unit)要关闭。
</p>
网络设备方面：
注意：以上这些规划应该在初始设计系统时就应该考虑好。
1.5.3 服务器硬件优化
1、物理状态灯：
2、自带管理设备：远程控制卡（FENCE设备：ipmi ilo idarc），开关机、硬件监控。
3、第三方的监控软件、设备（snmp、agent）对物理设施进行监控
4、存储设备：自带的监控平台。EMC2（hp收购了）， 日立（hds），IBM低端OEM hds，高端存储是自己技术，华为存储
1.5.4 系统优化
Cpu：
内存：
SWAP：
<p class="a">
阿里云的服务器中默认swap为0
</p>
IO ：
Swap****调整(不使用swap__分区)
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
修改MySQL的配置参数innodb_flush_method，开启O_DIRECT****模式。
这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。
值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多
IO****调度策略
永久修改
1.5.5 系统参数调整
Linux系统内核参数优化
用户限制参数（mysql可以不设置以下配置）
1.5.6 应用优化
业务应用和数据库应用独立,
防火墙：iptables、selinux等其他无用服务(关闭)：
安装图形界面的服务器不要启动图形界面 runlevel 3
另外，思考将来我们的业务是否真的需要MySQL，还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。
1.6 数据库优化
SQL优化方向：
架构优化方向：
1.6.1 数据库参数优化
调整：
实例整体（高级优化，扩展）：
连接层（基础优化）
设置合理的连接客户和连接方式
SQL层（基础优化）
1.6.2 存储引擎层（innodb基础优化参数）
1.7 参考文献
<p class="a">
[2] <a href="https://www.jianshu.com/p/d7665192aaaf">https://www.jianshu.com/p/d7665192aaaf</a>
</p>
<li>
<a href="#13">1.3 优化思路</a><ul>
<li>
<a href="#131">1.3.1 优化什么</a>
</li>
<li>
<a href="#132">1.3.2 优化的范围有哪些</a>
</li>
<li>
<a href="#133">1.3.3 优化维度</a>
</li>
</ul>
</li>
<li>
<a href="#14">1.4 优化工具有啥？</a><ul>
<li>
<a href="#141">1.4.1 数据库层面</a>
</li>
<li>
<a href="#142">1.4.2 数据库层面问题解决思路</a>
</li>
<li>
<a href="#143">1.4.3 系统层面</a>
</li>
<li>
<a href="#144">1.4.4 系统层面问题解决办法</a>
</li>
</ul>
</li>
<li>
<a href="#15">1.5 基础优化</a><ul>
<li>
<a href="#151">1.5.1 优化思路</a>
</li>
<li>
<a href="#152">1.5.2 硬件优化</a>
</li>
<li>
<a href="#153">1.5.3 服务器硬件优化</a>
</li>
<li>
<a href="#154">1.5.4 系统优化</a>
</li>
<li>
<a href="#155">1.5.5 系统参数调整</a>
</li>
<li>
<a href="#156">1.5.6 应用优化</a>
</li>
</ul>
</li>
<li>
<a href="#16">1.6 数据库优化</a><ul>
<li>
<a href="#161">1.6.1 数据库参数优化</a>
</li>
<li>
<a href="#162_innodb">1.6.2 存储引擎层（innodb基础优化参数）</a>
</li>
</ul>
</li>
<li>
<a href="#17">1.7 参考文献</a>
</li>
- 原文作者：惨绿少年
- 原文链接：https://clsn.io/clsn/lx287.html
- 版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。