<返回更多

MySQL 优化实施方案

2019-09-03    
加入收藏
作者 ：惨绿少年
来源：http://clsn.io/clsn/lx287.html

 

一、前言

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

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

 

MySQL 优化实施方案

 

 

图 - MySQL查询过程

二、优化的哲学

注意：优化有风险，涉足需谨慎！

1. 优化可能带来的问题

2. 优化的需求

3. 优化由谁参与

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

三、优化思路

1. 优化什么

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

2. 优化的范围有哪些

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

应用程序方面:

数据库优化方面:

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

3. 优化维度

数据库优化维度有四个：

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

MySQL 优化实施方案

 

 

优化选择：

四、优化工具有啥

1. 数据库层面

检查问题常用工具：

MySQL 优化实施方案

 

 

不常用但好用的工具：

MySQL 优化实施方案

 

 

2. 数据库层面问题解决思路

一般应急调优的思路：

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

常规调优思路：

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

3. 系统层面

cpu方面：

vmstat、sar top、htop、nmon、mpstat

内存：

free 、ps -aux 、

IO 设备(磁盘、网络)：

IOStat 、 ss 、 netstat 、 iptraf、iftop、lsof、

vmstat 命令说明：

iostat 命令说明

实例命令： iostat -dk 1 5

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

4. 系统层面问题解决办法

你认为到底负载高好，还是低好呢？

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

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

问题一：cpu 负载高，IO 负载低

问题二：IO 负载高，cpu 负载低

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

硬件不够了或 SQL 存在问题

五、基础优化

1. 优化思路

定位问题点:

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

处理方向：

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

2. 硬件优化

主机方面：

cpu 的选择：

内存的选择：

存储方面：

raid 卡：主机 raid 卡选择：

网络设备方面：

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

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

3. 服务器硬件优化

4. 系统优化

Cpu：

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

内存：

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

SWAP：

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

IO ：

 

MySQL 优化实施方案

 

 

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

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

IO 调度策略：

MySQL 优化实施方案

 

 

5. 系统参数调整

Linux 系统内核参数优化：

MySQL 优化实施方案

 

 

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

MySQL 优化实施方案

 

 

6. 应用优化

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

MySQL 优化实施方案

 

 

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

六、数据库优化

SQL 优化方向：

执行计划、索引、SQL 改写

架构优化方向：

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

1. 数据库参数优化

调整：

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

MySQL 优化实施方案

 

 

连接层(基础优化)

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

MySQL 优化实施方案

 

 

SQL 层(基础优化)

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

 

MySQL 优化实施方案

 

 

 

随着系统用户量的不断增加，MySQL 索引的重要性不言而喻，对于后端工程师，只有在了解索引及其优化的规则，并应用于实际工作中后，才能不断的提升系统性能，开发出高性能、高并发和高可用的系统。

声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号