史上最全的MySQL高性能优化实战总结!

  1. 云栖社区>
  2. 博客>
  3. 正文

史上最全的MySQL高性能优化实战总结!

java填坑路 发布时间:2018-09-17 17:08:00 浏览194 评论0

摘要: 1.1 前言 MySQL对于很多Linux从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程,很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

img_6520f41f7e246d593e47a1ab114ba4eb.jpe

1.1 前言

MySQL对于很多Linux从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程,很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

今天给大家体验MySQL的优化实战,助你高薪之路顺畅。

img_198b0ee617f8f2f4a1ca69e89e6d85da.jpe

图 - MySQL查询过程

1.2 优化的哲学

优化有风险,涉足需谨慎

1.2.1 优化可能带来的问题


img_1d144a54ed91956a8bed33e2957a1cc0.png

1.2.2 优化的需求


img_2ca326a33375db1bcb2f4eccb2a0123f.png

1.2.3 优化由谁参与

在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。

1.3 优化思路

1.3.1 优化什么

在数据库优化上有两个主要方面:即安全与性能。


img_25deafd5287fe27fd3654a9e2dc8e877.png

1.3.2 优化的范围有哪些

存储、主机和操作系统方面:


img_9a3e663b7358b0635acadcf23f2e4a52.png

应用程序方面:


img_c956729f54b6e5edcdd1cb4ccb0c3b71.png

数据库优化方面:


img_2e826c2fd6da6b1647ae4cc5ed46b7fd.png

说明:不管是在,设计系统,定位问题还是优化,都可以按照这个顺序执行。

1.3.3 优化维度

数据库优化维度有四个:

硬件、系统配置、数据库表结构、SQL及索引

img_b2aa370a0beada5cc68e6bb18c8f952e.jpe

优化选择


img_40e104427f6a94c7830c9d540a4d7be2.png

1.4 优化工具有啥?

1.4.1 数据库层面

检查问题常用工具


img_045661a6bd21d8732295bac4db9bf590.png

不常用但好用的工具


img_2c6bee8719868090406449145cdcebde.png

1.4.2 数据库层面问题解决思路

一般应急调优的思路:

针对突然的业务办理卡顿,无法进行正常的业务处理!需要立马解决的场景!


img_43c7f54a51841fa32475978fdb26e499.png

常规调优思路:

针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。


img_6771a28f0d579d14a4f32afb213e6f47.png

1.4.3 系统层面

cpu方面

vmstat、sar top、htop、nmon、mpstat

内存

free、ps -aux 、

IO设备(磁盘、网络)

iostat、 ss 、 netstat 、 iptraf、iftop、lsof、

vmstat 命令说明:


img_e069b20ab7df599497b53d78f0c948dc.png

iostat命令说明

实例命令: iostat -dk 1 5

iostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await))


img_05904e79919e96b40a3df4dd468c7feb.png

1.4.4 系统层面问题解决办法

你认为到底负载高好,还是低好呢?

在实际的生产中,一般认为 cpu只要不超过90%都没什么问题 。

当然不排除下面这些特殊情况:

问题一:cpu负载高,IO负载低


img_88bfd927dc27246d8f5131738dee21fa.png

问题二:IO负载高,cpu负载低


img_4967346f11d3e130a5f05d132f6c3f3d.png

问题三:IO和cpu负载都很高

硬件不够了或sql存在问题

1.5 基础优化

1.5.1 优化思路

定位问题点:

硬件 --> 系统 --> 应用 --> 数据库 --> 架构(高可用、读写分离、分库分表)

处理方向

明确优化目标、性能和安全的折中、防患未然

1.5.2 硬件优化

主机方面:


img_007b7d1805c7295043ad5083f2978280.png

cpu的选择:


img_141087b572c3743b6e23506ce77b6b62.png

内存的选择:


img_f1e69ac8336f3abbab0a0286892ffa12.png

存储方面:


img_b26655d7156552f4e390c080289e1e66.png

raid卡:主机raid卡选择:

img_9aaf3b1d5d9bea92d89db2fd24a7b54b.png

网络设备方面:

使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA卡)

注意:以上这些规划应该在初始设计系统时就应该考虑好。

1.5.3 服务器硬件优化


img_49841e12b096c794739cecb10ec74b61.png

1.5.4 系统优化

Cpu:

基本不需要调整,在硬件选择方面下功夫即可。

内存:

基本不需要调整,在硬件选择方面下功夫即可。

SWAP:

MySQL尽量避免使用swap。阿里云的服务器中默认swap为0

IO :


img_e056614c2e983597e275acbf671ebdb3.png


img_df7d7018998d800be40afbeae4a6e0a8.png

这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。

修改MySQL的配置参数innodb_flush_method,开启O_DIRECT模式。这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。值得注意的是,Redo log是覆写模式的,即使使用了文件系统的cache,也不会占用太多

IO调度策略


img_f6bb7c8ce229cebfe32e6030cdaa7b18.png

1.5.5 系统参数调整

Linux系统内核参数优化


img_c170c6186709ee584b8ff83b9d77d1f8.png

用户限制参数(mysql可以不设置以下配置)


img_09620505f9237266466742d6954cc495.png

1.5.6 应用优化

业务应用和数据库应用独立,防火墙:iptables、selinux等其他无用服务(关闭):


img_acc049a0bc26ac96b02ea9e4f8f7bac3.png

安装图形界面的服务器不要启动图形界面 runlevel 3,另外,思考将来我们的业务是否真的需要MySQL,还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。

1.6 数据库优化

SQL优化方向:

执行计划、索引、SQL改写

架构优化方向:

高可用架构、高性能架构、分库分表

1.6.1 数据库参数优化

调整:

实例整体(高级优化,扩展)


img_d326728b0e0f7911c61fda0cad7d9667.png

连接层(基础优化)

设置合理的连接客户和连接方式


img_79ce1b9a6f5273df16c5cb0826c73cb5.png

SQL层(基础优化)

query_cache_size: 查询缓存

OLAP类型数据库,需要重点加大此内存缓存.

但是一般不会超过GB.

为什么某些人会一直比你优秀,是因为他本身就很优秀还一直在持续努力变得更优秀,而你是不是还在满足于现状内心在窃喜!

欢迎工作一到五年的Java工程师朋友们加入Java架构开发:744677563

群内提供免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!

对于经常被修改的数据,缓存会立马失效。

我们可以实用内存数据库(redis、memecache),替代他的功能。

1.6.2 存储引擎层(innodb基础优化参数)


img_918bddf86e728205d8c1e585e4b9d7ff.png

欢迎工作一到五年的Java工程师朋友们加入Java架构开发:860113481

群内提供免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!

【云栖快讯】云栖专辑 | 阿里开发者们的20个感悟,一通百通  详情请点击

网友评论