MYSQL_09从架构师角度全局理解Mysql性能优化
2023-04-16 09:52:31 17 举报AI智能生成
Mysql性能优化
作者其他创作
大纲/内容
查询性能优化
慢查询日志
mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志
默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能
配置
show VARIABLES like 'slow_query_log'
set GLOBAL slow_query_log=1
开启
show VARIABLES like '%long_query_time%'
long_query_time参数就是这个阈值。默认值为10，代表10秒
show VARIABLES like '%log_queries_not_using_indexes%'
对于没有运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件
show VARIABLES like 'log_output'
可以指定输出的位置，通过参数log_output来控制
可以输出到[TABLE][FILE][FILE,TABLE]
set global log_output='FILE,TABLE'
缺省是输出到文件，不过一般不推荐输出到表
分析
数据组成
“Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
“User@Host: root[root] @ localhost [] Id: 3”：用户名 、用户的IP信息、线程ID号
“Query_time: 0.000495”：执行花费的时长【单位：秒】
“Lock_time: 0.000170”：执行获得锁的时长
“Rows_sent”：获得的结果行数
“Rows_examined”：扫描的数据行数
“SET timestamp”：这SQL执行的具体时间
辅助工具
mysqldumpslow
汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出
mysqldumpslow -s r -t 10 slow-mysql.log
-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
-t NUM just show the top n queries：仅显示前n条查询
-g PATTERN grep: only consider stmts that include this string：通过grep来筛选语句
-s order (c,t,l,r,at,al,ar)
c:总次数
t:总时间
l:锁的时间
r:获得的结果行数
at,al,ar :指t,l,r平均数 【例如：at = 总时间/总次数】
优化SQL查询方法论
查询性能低下最基本的原因是访问的数据太多
确认应用程序是否在检索大量超过需要的数据
确认MySQL服务器层是否在分析大量超过需要的数据行
业务层
查询不需要的记录
例如取出100条记录，但是只是在页面上显示前面10条
总是取出全部列
取出全部列，会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗
重复查询相同的数据
不断地重复执行相同的查询，然后每次都返回完全相同的数据
当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出
执行层
响应时间、扫描的行数、返回的行数
响应时间
服务时间
数据库处理这个查询真正花了多长时间
排队时间
服务器因为等待某些资源而没有真正执行查询的时间—-可能是等I/O操作完成，也可能是等待行锁
重构SQL查询的方法论
切分查询
将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果
一般来说，将每个SQL所处理的记录控制在5000到10000是个比较好的权衡值
分解关联查询
可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联
让缓存的效率更高
执行单个查询可以减少锁的竞争
以减少冗余记录的查询
执行全流程考虑性能优化
查询执行的流程
客户端发送一条查询给服务器
服务器先检查查询缓存
如果命中了缓存，则立刻返回存储在缓存中的结果
否则进入下一阶段（当然从MySQL8.0开始，这个部分就没有了）
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
将结果返回给客户端
MySQL查询的生命周期
从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端
MySQL客户端/服务器通信
MySQL客户端和服务器之间的通信协议是“半双工”的
要么是由服务器向客户端发送数据
要么是由客户端向服务器发送数据
这两个动作不能同时发生
限制
没法进行流量控制
一旦一端开始发生消息，另一端要接收完整个消息才能响应它。这就像来回抛球的游戏
客户端用一个单独的数据包将查询传给服务器
max_allowed_packet
服务器响应给用户的数据通常很多，由多个数据包组成
多数连接MySQL 的库函数都可以获得全部结果集并缓存到内存里
如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集
对于Java程序来说，很有可能发生OOM，所以MySQL的JDBC里提供了setFetchSize() 之类的功能
当statement设置以下属性时，采用的是流数据接收方式
每次只从服务器接收部份数据，直到所有数据处理完毕，不会发生JVM OOM
setResultSetType(ResultSet.TYPE_FORWARD_ONLY)
setFetchSize(Integer.MIN_VALUE)
生命周期中的查询优化处理
0 条评论
下一页
