学习笔记：MySQL数据库优化思路的细节分析(性能方向)

不要听信你看到的关于优化的“绝对真理”，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。 本篇文章只是给大家提供一些优化方面的方向和思路，而具体业务场景的不同，使用的MySQL服务版本不同，都会使得优化方案的制定也不同。

首先开看优化可能带来的问题

• 优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统！
• 优化手段有很大的风险，一定要意识到和预见到！
• 任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
• 对于优化来说调优而带来的问题,控制在可接受的范围内才是有成果。
• 保持现状或出现更差的情况都是失败！

优化的需求

• 稳定性和业务可持续性,通常比性能更重要！
• 优化不可避免涉及到变更，变更就有风险！
• 优化使性能变好，维持和变差是等概率事件！
• 优化应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！

所以优化工作,是由业务需要驱使的！！！

优化一般由那些角色参与

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

1. 优化介绍及思路

1.1 优化的方向

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

• 安全 —> 数据安全性
• 性能 —> 数据的高性能访问

本篇文章主要是在性能优化方向进行介绍

1.2 优化的维度

从上图中可以看出，我们把数据库优化分为四个纬度：硬件，系统配置，数据库表结构，SQL及索引

硬件： CPU、内存、存储、网络设备等

系统配置： 服务器系统、数据库服务参数等

数据库表结构： 高可用、分库分表、读写分离、存储引擎、表设计等

Sql及索引： sql语句、索引使用等

• 从优化成本进行考虑：硬件>系统配置>数据库表结构>SQL及索引
• 从优化效果进行考虑：硬件<系统配置<数据库表结构<SQL及索引

1.3 优化的工具

检查问题常用工具

msyqladmin		#mysql客户端，可进行管理操作  
mysqlshow		#功能强大的查看shell命令  
show [SESSION | GLOBAL] variables	#查看数据库参数信息  
SHOW [SESSION | GLOBAL] STATUS		#查看数据库的状态信息  
SHOW ENGINE INNODB STATUS Innodb	#引擎的所有状态  
information_schema	#获取元数据的方法
SHOW PROCESSLIST	#查看当前所有连接session状态  
explain		#获取查询语句的执行计划
how index	#查看表的索引信息  
slow-log	#记录慢查询语句  
mysqldumpslow	#分析slowlog文件的 

不常用但好用的工具

zabbix		#监控主机、系统、数据库（部署zabbix监控平台）
mysqlslap	#分析慢日志
sysbench	#压力测试工具
workbench	#管理、备份、监控、分析、优化工具（比较费资源）
pt-query-digest	#分析慢日志
mysql profiling	#统计数据库整体状态工具
Performance Schema mysql	#性能状态统计的数据

1.4 数据库使用优化思路

​ 在多数时候，我们进行调优不需要进行这么全面、大范围的调优，一般情况下，我们进行数据库层面的优化就可以了，那我们该如何调优的呢？

应急调优的思路：

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

1. show processlist（查看链接session状态）
2. explain(分析查询计划)，show index from table（分析索引）
3. 通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题
4. show status like ‘%lock%’; # 查询锁状态
5. kill SESSION_ID; # 杀掉有问题的session

常规调优的思路：

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

1. 查看slowlog，分析slowlog，分析出查询慢的语句。
2. 按照一定优先级，进行一个一个的排查所有慢语句。
3. 分析top sql，进行explain调试，查看语句执行时间。
4. 调整索引或语句本身。

2. 查询优化

2.1 MySQL查询流程

​ 我们该如何进行sql优化呢， 首先我们需要知道，sql优化其实主要是解决查询的优化问题，所以我们先从数据库的查询开始入手，下面这幅图显示了查询的执行路径：

① 客户端将查询发送到服务器；

② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。

③ 服务器解析，预处理。

④ 查询优化器优化查询

⑤ 生成执行计划，执行引擎调用存储引擎API执行查询

⑥服务器将结果发送回客户端。

​

查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据，如果命中缓存直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

当某个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态，在Innodb中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。

语法解析和预处理器
MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。

查询优化器
语法书被校验合法后由优化器转成查询计划，一条语句可以有很多种执行方式，最后返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

查询执行引擎
在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。最常使用的也是比较最多的引擎是MyISAM引擎和InnoDB引擎。mysql5.5开始的默认存储引擎已经变更为innodb了。

2.2 查询优化

​ 前面的查询流程分析，我们大概了解了MySQL是如何执行的，其中涉及到的部分我们在后面会一一道来。现在我们先从查询优化部分开始。

​ sql是我们和数据库交流最重要的部分，所以我们在调优的时候，需要花费的大量时间就在sql调优上面。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

2.2.1 慢查询

• 慢查询日志开启

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数

log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5

5.7版本:

slow_query_log = on
slow-query-log-file = C:/ProgramData/MySQL/MySQL Server 5.7/slow/slow.log
long_query_time = 1

log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；

long_query_time=5中的5表示查询超过五秒才记录；

还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。

• 慢查询分析

​ 我们可以通过打开log文件查看得知哪些SQL执行效率低下 ，从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。

​ 如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

进入log的存放目录，运行：

[root@mysql_data]# mysqldumpslow slow-query.log
Reading mysql slow query log fromslow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s)Rows=1.0 (2), root[root]@mysql
select count(N) from t_user; 

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10/database/mysql/slow-query.log

这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

例如：

/path/mysqldumpslow -s r -t 10/database/mysql/slow-log

得到返回记录集最多的10个查询。

/path/mysqldumpslow -s t -t 10 -g “le