MySQL从架构师角度看调优
调优金字塔
- 从图上可以看出，越往上走，难度越来越高，收益却是越来越小的。
硬件层面的调优
- 最容易想到的：SSD盘比用机械硬盘更好
- 使用哪种文件系统：XFS
- 操作系统的磁盘调度算法：deadline，对机械硬盘和SSD都比较合适。
- 裸设备运行：跳过操作系统。
MySQL层面的调优
MySQL客户端/服务器通信
- MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
- 产生的问题：没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。
- 服务器返回客户端一般由多个数据包组成。客户端一般是接收到所有的数据包开始进行下一步。
当statement设置以下属性时，采用的是流数据接收方式，每次只从服务器接收部分数据，直到所有数据处理完毕，不会发生JVM OOM。
setResultSetType(ResultSet.TYPE_FORWARD_ONLY);
setFetchSize(Integer.MIN_VALUE);
生命周期中的查询优化处理
- 查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。
- 包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误（例如语法错误）都可能终止查询。
- 优化器很复杂很智能，尽量能自己优化的sql就不要让优化器去帮你优化！同时不要去干扰优化器的工作，大部分情况下，我们都不如这个优化器厉害。
- 优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。
- 静态优化可以直接对解析树进行分析，并完成优化。
- 动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。
查询执行引擎
- 在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。
- 相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL 只是简单地根据执行计划给出的指令逐步执行。
返回结果给客户端
- 即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。
如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。 - 如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。
- MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
- 好处1：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。
- 好处2：MySQL客户端第一时间获得返回的结果。
状态查询：sql在正在干什么
- 慢查询日志在查询结束以后才记录，在应用反映执行效率出现问题的时候查询未必执行完成。
- 有时候问题的产生不一定是语句的执行，有可能是其他原因导致的。慢查询日志并不能定位问题。
- 查询方式：show processlist显示哪些线程正在运行
- 具体官网连接：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
- statistics：服务器正在计算统计信息以研究一个查询执行计划。如果线程长时间处于此状态，则服务器可能是磁盘绑定执行其他工作。
- Creating tmp table：该线程正在内存或磁盘上创建临时表。
- Sending data：线程正在读取和处理 SELECT 语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态。
线程查询：这个sql到底哪里慢
-
检查当前MySQL是否支持profile
select @@have_profiling;
-
开启profiling
select @@profiling;
set profiling=1; -
执行一个SQL查询
select count(*) from test; -
查看SQL的Query ID
show profiles;
-
查看执行过程中线程的每个状态消耗的时间
show profile for query 1;
- 在获取到最消耗时间的线程状态后，MySQL 支持进一步选择all、cpu、block io、contextswitch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过高的时间:
show profile all for query 1;
当前执行主要是cpu耗时比较高！
架构调优
- 把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做。
- 考虑写的并发量有多大，是否需要采用分布式。
- 读的压力是否很大，是否需要读写分离。
- 反范式设计。
- 分库，分表，分区。
- DBA参与到业务中，进行表结构设计、审核、跟踪！
- 适当使用缓存。
- 避免过早优化！
