共计 6473 个字符，预计需要花费 17 分钟才能阅读完成。
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 数据库层面
查看问题常用工具
mysql
msyqladmin 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 processlist
2、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 /O
Memory：swpd 显示被替换到磁盘的数据块的数量。未被应用的数据块，用户缓冲数据块，用于操作系统的数据块的数量
Swap：操作系统每秒从磁盘上替换到内存和从内存替换到磁盘的数据块的数量。s1 和 s0 最好是 0
Io：每秒从设施中读入 b1 的写入到设施 b0 的数据块的数量。反映了磁盘 I /O
System：显示了每秒产生中断的数量 (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-engine
innodb_buffer_pool_size # 没有固定大小，50% 测试值，看看状况再微调。然而尽量设置不要超过物理内存 70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1 是最平安的，0 是性能最高，2 折中
binlog_sync
Innodb_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_bin
max_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 干货，也会分享收费的学习材料课程和面试宝典
回复：【计算机】【设计模式】【面试】有惊喜哦
