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