请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册

QQ登录

只需要一步,快速开始

搜索
开启左侧

大牛是怎样思考设计SQL优化方案的?

马上注册,分享更多源码,享用更多功能,让你轻松玩转云大陆。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
在进行MySQL的优化之前,必需要理解的就是MySQL的查询过程,很多查询优化工作实际上就是遵循一些原则,让MySQL的优化器能够按照料想的合理方式运行而已。
v20HMaOcL69JFT42.jpg

图-MySQL查询过程
一、优化的哲学
注:优化有风险,涉足需谨慎
1、优化可能带来的问题?
·优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统;
·优化手段原本就有很大的风险,只不过你没能力意识到和预见到;
·任何的技术可以处理一个问题,但必然存在带来一个问题的风险;
·对于优化来说处理问题而带来的问题,控制在可接受的范围内才是有成果;
·保持现状或出现更差的情况都是失败!
2.优化的需求?
· 稳固性和业务可持续性,通常比性能更重要;
· 优化不可避免涉及到变更,变更就有风险;
· 优化使性能变好,维持和变差是等概率事件;
· 切记优化,应该是各部门协同,共同参与的工作,任何单一部门都不能对数据库进行优化!
· 所以优化工作,是由业务需要驱使的!
3.优化由谁参与?
在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。
二、优化思路
1.优化什么?
在数据库优化上有两个主要方面:即安全与性能。
· 安全->数据可持续性;
· 性能->数据的高性能访问。
2.优化的范围有哪些?
存储、主机和操作系统方面:
· 主机架构稳固性;
· I/O规划及配置;
· Swap交换分区;
· OS内核参数和网络问题。
应用程序方面:
· 应用程序稳固性;
· SQL语句性能;
· 串行访问资源;
· 性能欠佳会话管理;
· 这个应用适不适合用MySQL。
数据库优化方面:
· 内存;
· 数据库结构(物理&逻辑);
· 实例配置。
说明:不管是设计系统、定位问题还是优化,都可以按照这个顺序执行。
3优化维度?
数据库优化维度有四个:
硬件、系统配置、数据库表结构、SQL及索引。
R4nonJoIB621o1go.jpg

优化选择:
· 优化成本:硬件>系统配置>数据库表结构>SQL及索引。
· 优化效果:硬件数据库-->架构(高可用、读写分离、分库分表)。
处理方向:明确优化目标、性能和安全的折中、防患未然。
2.硬件优化?
主机方面:

wvNuG1vXvyg8Xg0t.jpg

CPU的选择:
V6uukkt2wy1TQnut.jpg

内存的选择:


存储方面:
BfRh5hmMFammLmzF.jpg

网络设备方面:


注意:以上这些规划应该在初始设计系统时就应该考虑好。
3.服务器硬件优化?

UadZv1C639H8lA6L.jpg

4.系统优化?
Cpu:


内存:


SWAP:


IO :


Swap调整(不使用swap分区)


这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。
当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。
修改MySQL的配置参数innodb_flush_ method,开启O_DIRECT模式:
这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。
值得注意的是,Redo log是覆写模式的,即便使用了文件系统的cache,也不会占用太多。
IO调度策略:


永久修改


5.系统参数调整?

Linux系统内核参数优化:
ezG7T8x3r7Ex3X32.jpg

客户限制参数(MySQL可以不设置以下配置):
mRA1OJ9131cvVO9o.jpg

6.应用优化?
业务应用和数据库应用独立;
防火墙:iptables、selinux等其他无用服务(关闭):
w77KBnwKNbSOBYbc.jpg

安装图形界面的服务器不要启动图形界面runlevel 3。
另外,思考将来我们的业务能否真的需要MySQL,还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。
五、数据库优化
SQL优化方向:执行计划、索引、SQL改写。
架构优化方向:高可用架构、高性能架构、分库分表。
1.数据库参数优化?
调整
实例整体(高级优化,扩展):
XvX694x4x9k1915a.jpg

连接层(基础优化)
设置合理的连接客户和连接方式:
ZT621ll12Ia2eboI.jpg

SQL层(基础优化)


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

Y9R6zBkSXcS6EdKx.jpg

小礼物走一走,来简书关注我
欢迎工作一到五年的Java工程师朋友们加入Java填坑之路:860113481
群内提供免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

  • 0 关注
  • 0 粉丝
  • 1 帖子
广告招商