MySQL 优化思路篇

MySQL 优化思路篇

  • 1、MySQL 查询的优化步骤
  • 2、查询系统性能参数
  • 3、慢查询日志定位问题
    • 3.1、开启慢查询日志参数
    • 3.2、查看慢查询数目
    • 3.3、慢查询日志的分析工具 mysqldumpslow
    • 3.4、关闭慢查询日志
    • 3.5、慢查询日志的删除与重建
  • 4、SHOW PROFILE :查看SQL执行成本

1、MySQL 查询的优化步骤

 

       数据库调优流程,主要分为两部分**观察(Show status)**和 行动(Action),分别用字母 S 和 A 表示,如下图:
在这里插入图片描述

  • 周期性的波动,可能是双十一,促销活动等,可以通过加缓存或更改缓存失效策略解决。

  • 开启慢查询,并设置long_query_time参数定义【慢】的阈值,帮助我们定位执行慢的SQL语句,收集慢查询语句,再通过分析工具对慢查询日志进行分析。

  • 对慢SQL使用 EXPLAIN 分析其执行计划,或者使用 SHOW PROFILE 查看慢SQL每一步骤的时间成本,以确认慢SQL是执行时间长还是等待时间长

    • 等待时间长,可以调优服务器参数,比如适当增加数据库的缓冲池。
    • 执行时间长,就考虑索引设计问题关联表数量过多数据库表结构设计问题等。
  • 以上均不能解决,再考虑是否数据库自身的 SQL 查询性能达到瓶颈。

    • 达到瓶颈,就考虑增加服务器(采用读写分离架构),或进行分库分表,比如垂直分库、垂直分表、水平分表等.
    • 如未达到瓶颈,则建议再重新检查以上各项。

在这里插入图片描述

2、查询系统性能参数

 

MySQL可使用 SHOW STATUS 语句查询 MySQL 数据库的性能参数执行频率等.

-- 执行语句
SHOW [GLOBAL | SESSION] STATUS LIKE '参数';
-- 查询 MySQL 服务器的连接次数,示例:
SHOW STATUS LIKE 'Connections';
-- 查询 MySQL 服务器的慢查询次数,示例:
SHOW STATUS LIKE 'Slow_queries';
参数说明
Connections连接MySQL服务器的次数。
UptimeMySQL 服务器的上线时间。
Slow_queries慢查询的次数。
Innodb_rows_readselect 查询返回的行数。
Innodb_rows_inserted执行 INSERT 操作插入的行数。
Innodb_rows_updated执行 UPDATE 操作更新的行数。
Innodb_rows_deleted执行 DELETE 操作删除的行数。
Com_select查询操作的次数。
Com_update更新操作的次数。
Com_insert插入操作的次数,批量插入只累加算一次。
Com_delete删除操作的次数。
last_query_cost统计查询成本,结果为查询所需要读取的页数量

3、慢查询日志定位问题

 
       MySQL 的慢查询日志,用来记录在MySQL中 响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的 SQL,会被记录到慢查询日志中。long_query_time默认值为 10,指运行 10秒以上(不含10秒)的语句被认定为慢SQL.

       MySQL 默认没有开启慢查询日志,有需求可手动开启。由于开启慢查询日志,会或多或少带来一定的性能影响,如果不是调优需求的话,一般不建议启动该参数。慢查询日志支持将日志记录写入文件。

 

3.1、开启慢查询日志参数

 

-- 查询是否开启了慢查询日志,ON 表示开启,OFF 表示未开启,默认为 OFF.
-- 查询结果中 slow_query_log_file 表示慢查询日志所在文件地址
SHOW VARIABLES LIKE '%slow_query_log%';
-- 查询慢查询 阈值,查询结果单位 为 秒,默认为 10秒。
SHOW VARIABLES LIKE '%long_query_time%';
-- 设置 慢查询阈值为1s。
-- 注意:使用该方式对当前session的long_query_time是失效的,对新连接的客户端有效。
SET GLOBAL long_query_time = 1;

在这里插入图片描述

可以通过修改配置文件 my.cnf 或 my.ini 的方式,永久设置。

[mysqld]
slow_query_log=ON # 开启慢查询日志
slow_query_log_file=/var/lib/mysql/my_slow.log # 设置慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询阈值为 3 秒
log_output=FILE

 

3.2、查看慢查询数目

 

-- 查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%slow_queries%';
-- 慢查询的认定与两个因素有关,除慢查询阈值外,还有一个 扫描过的最少记录数(min_examined_row_limit )
-- 如果查询扫描的记录数,超过该变量指,且超过慢查询阈值,即认定为慢SQL.
-- min_examined_row_limit 默认值为 0
-- 可通过 修改配置文件 my.cnf 或 my.ini 修改 min_examined_row_limit值,
-- 也可通过 set 语句修改  min_examined_row_limit

 

3.3、慢查询日志的分析工具 mysqldumpslow

 

-- mysqldumpslow 工具由 MySQL提供。
-- 可以查看 mysqldumpslow 帮助说明
mysqldumpslow -- help
-- 使用 mysqldumpslow 查看 慢日志文件示例
-- 显示5条,不显示查询参数
-- mysqldumpslow -s t  -t 5 [慢日志文件全路径地址] 
mysqldumpslow -s t  -t 5 /var/lib/mysql/atguigu05-slow.log
-- 显示5条,显示查询参数
-- mysqldumpslow -a -s t  -t 5 [慢日志文件全路径地址] 
mysqldumpslow -a -s t  -t 5 /var/lib/mysql/atguigu05-slow.log

在这里插入图片描述
 

3.4、关闭慢查询日志

 

  • 永久性方式

    -- 修改配置文件 my.cnf 或 my.ini ,把[mysqld]组下的 slow_query_log 值设为 OFF. 
    -- 修改配置文件后,保存,再重启MySQL服务,即可生效。
    [mysqld]
    slow_query_log=OFF
    -- 或者注释掉 slow_query_log,保存,再重启MySQL服务,即可生效。
    [mysqld]
    # slow_query_log=OFF
    
  • 临时性方式

    -- 使用SET 语句设置
    SET GLOBAL slow_query_log=OFF;
    

 

3.5、慢查询日志的删除与重建

 

-- 找到慢查询日志地址
SHOW VARIABLES LIKE '%slow_query_log_file%';
-- 使用 rm -rf [慢查询日志文件全路径地址] 删除
-- mysqladmin flush-logs slow 重新生成慢查询日志文件
mysqladmin -uroot -p flush-logs slow
-- 慢查询日志的删除重建,都是使用 mysqladmin flush-logs slow 来执行的,
-- 一旦执行该命令,慢查询日志都只存在新的日志文件中,如果需要旧的查询日志,就需要事先备份。

4、SHOW PROFILE :查看SQL执行成本

 

       show profile 是MySQL 提供用来分析当前会话中的SQL做了什么、执行的资源消耗情况的工具,用于SQL调优优化。默认情况下处于关闭状态,并保存最近15次的运行结果。

-- 查看 show profile 启动状态,ON 开启,OFF 关闭。
SHOW VARIABLES LIKE 'profiling';
-- 在会话级别开启 
SET profiling = 'ON';
-- 查询最近 15次查询的 query_id
SHOW profiles;
-- 查看最近一次查询的开销
SHOW profile;
-- 查询 query_id 为2 的执行成本
SHOW profile for query 2;
-- 添加 cpu、memory 列参数
SHOW  profile cpu,memory;

在这里插入图片描述

status列 结果说明:

  1. starting:查询开始执行的时间点。
  2. checking permissions:检查执行查询所需的权限的时间点。
  3. Opening tables:打开查询中涉及的表的时间点。
  4. init:初始化查询执行的时间点。
  5. System lock:获取系统锁的时间点。
  6. optimizing:查询优化器优化查询的时间点。
  7. statistics:收集统计信息的时间点。
  8. preparing:准备执行查询的时间点。
  9. executing:执行查询的时间点。
  10. Sending data:发送查询结果的时间点。
  11. end:查询执行结束的时间点。
  12. query end:查询结束的时间点,包括发送结果到客户端的时间。
  13. closing tables:关闭查询中涉及的表的时间点。
  14. freeing items:释放查询执行过程中使用的资源的时间点。
  15. cleaning up:清理查询执行过程中的临时数据和状态的时间点。

 
show profile 常用查询参数:

参数说明
ALL显示所有的开销信息。
BLOCK IO显示块 IO 开销。
CONTEXT SWITCHES上下文切换开销。
CPU显示 CPU 开销信息。
IPC显示发送和接收开销。
MEMORY显示内存开销信息。
PAGE FAULTS显示页面错误开销信息。
SOURCE显示和 source_function、source_file、source_line 相关的开销信息。
SWAPS显示交换次数开销信息。

 
日常开发需注意的结论:

  • ① converting HEAP to MyISAM:查询结果太大,内存不够,数据往磁盘上搬了。
  • ② Creating tmp table:创建临时表。先拷贝数据到临时表,用完再删除临时表。
  • Copying to tmp table on disk:把内存中临时表复制到磁盘上,警惕!
  • locked.

如果再 show profile 诊断结果中出现了以上四种结果中的一条,则SQL语句需要优化。

SHOW PROFILE 命令相关信息,也可以 通过查 information_schema 中的 profiling 数据表中查到。

 
EXPLAIN 相关的内容,请看系列文章四: 《MySQL 的索引分类和设计原则》
 
 
系列文章:

一: 《搞懂 MySql 的架构和执行流程》

二: 《从InnoDB索引的数据结构,去理解索引》

三: 《从 Hash索引、二叉树、B-Tree 与 B+Tree 对比看索引结构选择》

四: 《MySQL 的索引分类和设计原则》

五: 《MySQL 优化思路篇》
 
 
 
 
 
.

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/179517.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

IntelliJ IDEA Services工具栏运行不显示端口问题解决

问题 如Spring Boot服务启动时,端口不显示。 解决 1、 清理所有缓存 2、 关闭IntelliJ IDEA后,到C:\Users\(你自己的用户名)\AppData\Local\Temp路径把所有文件都删除,因为时一个缓存,不影响其他软件…

智慧灯杆网关智能化选择(网关助力城市完整项目方案)

在当代城市发展中,智慧照明作为一项重要的技术创新,正逐渐改变着我们的城市生活。作为城市智慧照明的核心设备,智慧灯杆网关SG600凭借出色的性能和创新的解决方案,成为了引领城市智慧照明的完美选择。本文将详细介绍SG600的特点和…

Java实现Hive UDF详细步骤 (Hive 3.x版本,IDEA开发)

这里写目录标题 前言1. 新建项目2.配置maven依赖3.编写代码4.打jar包5.上传服务器6.代码中引用 前言 老版本编写UDF时,需要继承 org.apache.hadoop.hive.ql.exec.UDF类,然后直接实现evaluate()方法即可。 由于公司hive版本比较高(3.x&#x…

【有源码】基于Python的篮球人才管理系统Springboot的篮球竞赛管理系统(源码、调试、lw、开题报告、ppt)

💕💕作者:计算机源码社 💕💕个人简介:本人七年开发经验,擅长Java、Python、PHP、.NET、微信小程序、爬虫、大数据等,大家有这一块的问题可以一起交流! 💕&…

Cookie+Session

目录 Cookie和Session 实现模拟登录 1.编写登录页面 2.编写LoginServlet处理登录请求 3.编写indexServlet显示登录页面 Cookie和Session Cookie:浏览器提供的持久化储存数据的机制。把信息保存到客户端,后续访问服务器的时候带着cookie数据进行访问。 服务器通…

2023NOIP A层联测23-涂鸦

有一面由 n m n\times m nm 个格子组成的墙,每个格子要么是黑色,要么是白色。你每次将会进行这样的操作:等概率随机选择一个位置 ( x , y ) (x,y) (x,y),和一个颜色 c c c(黑色或者白色)( 1…

Redo Log(重做日志)的刷盘策略

1. 概述 Redo Log(重做日志)是 InnoDB 存储引擎中的一种关键组件,用于保障数据库事务的持久性和崩溃恢复。InnoDB 将事务所做的更改先记录到重做日志,之后再将其应用到磁盘上的数据页。 刷盘策略(Flush Policy&#x…

如何记录每天的工作日程?电脑手机通用的日程管理软件

在工作时间有限,但工作任务愈加繁多的现在职场中,要求每一个职场人士做好高效日程管理。通过高效管理日程,我们可以更好地组织和安排任务,合理分配时间和优先级,这有助于我们更专注地进行工作,减少时间的浪…

MCU HardFault_Handler调试方法

一.获取内核寄存器的值 1.在MDK的DEBUG模式下,当程序出现跑飞后,确定卡死在HardFault_Handler中断处 2. 通过Register窗口读取LR寄存器的值来确定当前系统使用堆栈是MSP还是PSP LR寄存器值堆栈寄存器0xFFFFFFF9MSP寄存器0xFFFFFFFDPSP寄存器 如下图所…

【JavaEE】cookie和session

cookie和session cookie什么是 cookieServlet 中使用 cookie相应的API Servlet 中使用 session 相应的 API代码示例: 实现用户登陆Cookie 和 Session 的区别总结 cookie 什么是 cookie cookie的数据从哪里来? 服务器返回给浏览器的 cookie的数据长什么样? cookie 中是键值对…

HR模块开发(1):简单的开发流程和注意事项

HR模块开发 一、模块概述 人力资源管理解决方案关注3个领域:每位雇员都发展和维护着‘公司内’和‘公司外’的种种‘关系’。运用科技,强化这些关系,可以提高忠诚度和生产力,公司整体得到商业价值。 员工关系管理员工职业生命周期管理员工事务处理管理HR模块的基本知识和构…

[Unity][VR]透视开发系列4-解决只看得到Passthrough但看不到Unity对象的问题

【视频资源】 视频讲解地址请关注我的B站。 专栏后期会有一些不公开的高阶实战内容或是更细节的指导内容。 B站地址: https://www.bilibili.com/video/BV1Zg4y1w7fZ/ 我还有一些免费和收费课程在网易云课堂(大徐VR课堂): https://study.163.com/provider/480000002282025/…

算法通关村第四关-黄金挑战基础计算器问题

大家好我是苏麟 , 今天带来栈的比较难的问题 . 计算器问题 基础计算器 LeetCode 224 描述 : 给你一个字符串表达式 s ,请你实现一个基本计算器来计算并返回它的值。 s 由数字、、-、(、)、和 组成s 表示一个有效的表达式 不能用作一元运算(例如, …

2014年亚太杯APMCM数学建模大赛A题无人机创造安全环境求解全过程文档及程序

2014年亚太杯APMCM数学建模大赛 A题 无人机创造安全环境 原题再现 20 国集团,又称 G20,是一个国际经济合作论坛。2016 年第 11 届 20 国集团峰会将在中国召开,这是继 APEC 后中国将举办的另一个大型峰会。此类大型峰会,举办城市…

【计算机网络】浏览器的通信能力

1. 用户代理 浏览器可以代替用户完成http请求,代替用户解析响应结果,所以我们称之为用户代理 user agent。 浏览器两大核心能力: 自动发送请求的能力自动解析响应的能力 1.1 自动发送请求的能力 用户在地址栏输入了一个url地址&#xff0…

[双指针] (四) LeetCode 18.四数之和

[双指针] (四) LeetCode 18.四数之和 文章目录 [双指针] (四) LeetCode 18.四数之和题目解析解题思路代码实现总结 18. 四数之和 题目解析 (1) 从一个数组中找一个目标值target (2) target nums[a] nums[b] nums[c] nums[d] 解题思路 和上一道题三数之和一样, 我们把四…

Android笔记(十一):Compose中使用ViewModel

通过ViewModel组件用于保存视图中需要的数据。ViewModel主要目的是将与用户界面相关的数据模型和应用程序的逻辑与负责实际显示和管理用户界面以及与操作系统交互的代码分离开来,为UI界面管理数据。常见的管理方式主要有:LiveData和StateFlow两种形式来实…

Redis常见风险分析

击穿 概念:在Redis获取某一key时, 由于key不存在, 而必须向DB发起一次请求的行为, 称为“Redis击穿”。 引发击穿的原因: 第一次访问恶意访问不存在的keyKey过期 合理的规避方案: 服务器启动时, 提前写入规范key的命名, 通过中间件拦截对…

BUUCTF FLAG 1

BUUCTF:https://buuoj.cn/challenges 题目描述: 注意:请将 hctf 替换为 flag 提交,格式 flag{} 密文: 下载附件,得到一张.png图片。 解题思路: 1、因为附件是一张图片,先放到StegSolve中&…

CentOS 7使用RPM包安装MySQL5.7

目标 本文目标是简单介绍如何在CentOS 7上使用RPM包安装MySQL 5.7,然后描述如何调整存储路径datadir。 环境准备 操作系统 —— CentOS 7MySQL版本 —— MySQL 5.7.44 获取MySQL-rpm包 官网下载地址:https://dev.mysql.com/downloads/mysql/5.7.htm…