当前位置: 首页 > news >正文

慢SQL调优-索引详解

Mysql 慢SQL调优-索引详解

  • 前言
  • 一、慢查询日志设置
  • 二、explain查看执行计划
  • 三、索引失效
  • 四、索引操作
  • 五、profile 分析执行耗时


前言

最新的 Java 面试题,技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等,会持续更新。

如果对老铁有帮助,帮忙免费点个赞,谢谢你的发财手!

一、慢查询日志设置

1、开启慢查询日志,设置超过几秒为慢SQL语句,抓取慢SQL语句;
(阿里的ARMS监控平台直接就能查询到慢SQL)

show variables like "%query%"

< img1>

  • 1)参数log_queries_not_using_indexes :表示如果运行的SQL语句没有使用到索引,是否也被当作慢查询语句记录到慢查询日志中,OFF表示不记录,ON表示记录
  • 2)参数long_query_time :表示“多长时间的查询”被认定为“慢查询”,默认值为10秒,表示超过10秒的查询被认定为慢查询
  • 3)参数 slow_query_log :表示是否开启慢查询日志。开启:“> set global slow_query_log=on”关闭:“> set global slow_query_log=off ”
  • 4)参数slow_query_log_file:指定慢查询日志存储于哪个日志文件中,默认的慢查询日志文件名为“主机名-slow.log”,慢查询日志的位置为datadir参数所对应的目录位置。
    慢查询日志中给出了账号、主机、运行时间、锁定时间、返回行等信息,当日志文件越来越大,通过vi或cat命令不能很直观地查看日志,这时就可以使用MySQL内置的mysqldumpslow命令来查询:
mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log

查询返回记录集最多的5个慢查询SQL。

二、explain查看执行计划

2、通过explain查看SQL执行计划,重点关注type、key、rows、extra指标;创建索引并调整语句,再查看执行计划,对比调优结果。
< img2>

  • 1)id:反映的是表的读取顺序或查询中执行select语句的顺序。
    ① id相同,可以认为是一组,从上往下顺序执行;
    ② id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行。
  • 2)select_type:表示select的类型,主要用于区别普通查询、联合查询、子查询等复杂查询。
    SIMPLE:表示查询语句不包含子查询或union。
    PRIMARY:查询中若包含任何复杂的子部分,最外层查询标记为primary。
    SUBQUERY:select中的子查询语句
    DEPENDENT SUBQUERY:select或where列表中的子查询。
    DERIVED(衍生):在from列表中包含的子查询,MySQL 会将结果存放在一个临时表中。
    UNION:若第二个select出现在union后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为DERIVED。
  • 3)table:表名称或别名(显示这一行的数据是关于哪张表的)。
  • 4)type:表示MySQL在表中找到目标行的方式,又称“访问类型”;
    性能:system > const > eq_ref > ref > range > index > ALL
    const、system:表示通过索引一次就查询到了相关记录,一般为主键或唯一索引查询,system是const类型的特例,当查询的表只有一行的情况下使用。
    eq_ref:类似ref,区别在于使用的索引是唯一索引,对于每个索引键,表中只有一条记录与之匹配,常见于多表连接中,一般使用primary key或者unique key作为关联条件。
    ref:非唯一性索引扫描,返回索引过滤的数据,可为多条,常出现在关联查询中。
    range:使用索引进行范围扫描,一般就是在where语句中出现between、< 、>、in等。
    index::index与ALL的区别为index类型只遍历索引树。
    ALL:扫描全表数据行。
  • 5)possible_keys:表示查询时可能使用到的索引。
  • 6)key:查询时真正使用到的索引,如果没有选择索引,则显示是NULL。
  • 7)key_len:显示索引中使用的字节数,可以判断是否全部使用了组合索引。
  • 8)ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。
  • 9)rows:显示MySQL根据表统计信息,估算找到所需的记录要读取的行数,越小越好。
  • 10)Extra:该列包含MySQL解决查询的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
    Using where:表示查询需要通过where条件查询数据。
    Using temporary:使用临时表保存中间结果,常见的情况有使用distinct关键字,join语句中使用order by或group by无索引列、order by与group by字段不同、union子查询等。
    Using filesort:表示有order by操作而且无法利用索引完成的排序,出现using filesort一般是因为order by后的条件导致索引失效,最好进行优化。
    Using join buffer:表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值,应当注意,根据查询的具体情况可能需要添加索引来改进。
    Using index:表示使用了索引覆盖(select要查询的字段少于或等于创建的索引字段),不需要访问表。如果与Using where一起出现,则表示索引用于查询过滤,还需回表查询出所需数据。
    Using Index Condition:表示通过使用索引对存储引擎索引出的数据进行再过滤,减少回表查询的次数。

总结一下针对explain命令生成执行计划:

  • 首先关注查询类型type列,如果出现all关键字,代表全表扫描,没有用到任何index;
  • 再看key列,如果key列是NULL,代表没有使用索引;
  • 然后看rows列,该列数值越大意味着需要扫描的行数越多,相应耗时越长;
  • 最后看Extra列,要避免出现Using filesort或Using temporary这样的字眼,这是很影响性能的。
    对于没有走索引的查询,通过添加适当的索引,注意需对照原表上的索引,看看有没有需要合并成联合索引,避免构建过多的索引,会占用空间和影响插入/更新的效率。

三、索引失效

下面列出常见的一些索引失效的场景:

    1. 索引列上加函数:在查询的索引列上使用内置函数都会让索引失效。
    1. 对索引列运算:与使用函数相似,都是会使得索引列值发生变化,从而无法使用索引。
    1. 联合索引最左匹配原则:不满足最左匹配原则,索引不生效。
    1. 隐式类型转换:select * from t_user where tel = 123; tel字段是varchar类型,但数值是int类型,自动类型转换会使得索引失效。
    1. 范围查询阻断后续字段不能走索引:范围查询包括 >=、<=、>、<、in、between。
    1. 负向查询和is NULL判断可能导致索引失效:负向查询包括 NOT、<>、!>、!<、!= 等。
    1. 使用like模糊查询,前后都加了%,”%李%”不会走索引, 而使用like “李%”会走索引。
    1. asc和desc混用:select * from t order by a asc, b desc。
    1. or:如果是单例索引,or会使用索引;如果是组合索引,or不会使用索引。

四、索引操作

  • 1、添加PRIMARY KEY(主键索引)
ALTER TABLE 表名 ADD PRIMARY KEY ( 字段 )
  • 2、添加UNIQUE(唯一索引)
ALTER TABLE 表名 ADD UNIQUE (字段)
  • 3、添加INDEX(普通索引)
ALTER TABLE 表名 ADD INDEX 索引名 ( 字段 )
  • 4、添加联合索引
ALTER TABLE 表名 ADD INDEX 索引名 ( 字段1, 字段2, ...)
  • 5、删除索引:
ALTER TABLE 表名 DROP INDEX 索引名;

五、profile 分析执行耗时

  • 1、查询profile开启状态
    show variables like ‘%profil%’
    在这里插入图片描述

    1)have_profiling:确定是否支持 profile;
    2)profiling:是否开启profiling;
    3)profiling_history_size:定义MySQL服务器最近接收到的SQL条数。

  • 2、开启profiling
    set profiling=ON
    set profiling_history_size=30

  • 3、查看最近运行的SQL
    – 查询最近30条SQL

show profiles;

在这里插入图片描述

(查看数据库版本:show variables like ‘%version%’;)

相关文章:

慢SQL调优-索引详解

Mysql 慢SQL调优-索引详解 前言一、慢查询日志设置二、explain查看执行计划三、索引失效四、索引操作五、profile 分析执行耗时 前言 最新的 Java 面试题&#xff0c;技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linu…...

知乎语音下载(mediadown)

知乎语音下载(mediadown) 一、介绍 知乎语音下载,能够帮助你下载知乎知学堂课程中的语音和视频。它不能帮你越过会员权限,下载你没权限访问的语音和视频。 二、下载地址 本站下载:知乎语音下载(mediadown) 百度网盘下载:知乎语音下载(mediadown) 三、安装教程 …...

2023 最新 IntelliJ IDEA 2023.3 详细配置步骤演示:新入职如何快速配置 IntelliJ IDEA?

博主猫头虎的技术世界 &#x1f31f; 欢迎来到猫头虎的博客 — 探索技术的无限可能&#xff01; 专栏链接&#xff1a; &#x1f517; 精选专栏&#xff1a; 《面试题大全》 — 面试准备的宝典&#xff01;《IDEA开发秘籍》 — 提升你的IDEA技能&#xff01;《100天精通鸿蒙》 …...

Linux 下安装 Git

Linux 下安装 Git 1 参考2 安装2.1 通过 yum方式安装&#xff08;不推荐&#xff09;2.2 通过源码编译安装&#xff08;推荐&#xff09; 3 配置SSH 1 参考 Linux 下安装 Git 2 安装 2.1 通过 yum方式安装&#xff08;不推荐&#xff09; 在Linux上安装git仅需一行命令即可…...

Manomotion 实现AR手势互动-解决手势无效的问题

之前就玩过 Manomotion &#xff0c;现在有新需求&#xff0c;重新接入发现不能用了&#xff0c;不管什么办法&#xff0c;都识别不了手势&#xff0c;我记得当初是直接调用就可以的。 经过研究发现&#xff0c;新版本SDK改了写法。下边就写一下新版本的调用&#xff0c;并且实…...

第十五届蓝桥杯第三期模拟赛(Java)

1. 【问题描述】 请问 2023 有多少个约数&#xff1f;即有多少个正整数&#xff0c;使得 2023 是这个正整数的整数倍。 【答案提交】 这是一道结果填空的题&#xff0c;你只需要算出结果后提交即可。本题的结果为一个整数&#xff0c;在提交答案时只填写这个整数&#xff0c;…...

vscode中eslint插件不生效问题

case: 最近使用webpack打包js资源中使用到了VS Code中的eslint插件辅助eslint plugin对代码进行校验&#xff0c;在.eslintrc.js文件中以及webpack.config.js配置好后&#xff0c; 在控制台运行npx webpack可以读取到eslint plugin的检测结果 一、eslint插件读取项目中.eslint…...

Go程序是如何编译并运行起来的(图文详解)

Go程序是如何编译的 从hello RdrB1te开始 package main import "fmt" func main() { fmt.Println("hello RdrB1te") }不实际编译它&#xff0c;只输出它的编译过程&#xff1a; go build -n简单的编译过程分析&#xff1a; 上面的过程确认了两个…...

程序员如何选择职业赛道

程序员的职业赛道就像是一座迷宫&#xff0c;有前端的美丽花园&#xff0c;后端的黑暗洞穴&#xff0c;还有数据科学的神秘密室。你准备好探索这个充满挑战和机遇的迷宫了吗&#xff1f;快来了解如何选择职业赛道吧&#xff01; 方向一&#xff1a;自我评估与兴趣探索 选择适合…...

GOWIN软件使用

1、管脚复用 根据自己需求把复用管脚勾选上&#xff0c;管脚当普通管脚使用 JTAG设置成普通管脚&#xff0c;下载程序时候JTAGEN管脚需要上拉高电平&#xff08;可以在下载器线上上拉个电阻&#xff0c;下载后把下载线拔走&#xff0c;否则JTAG管脚无法使用&#xff0c;管脚充…...

Ajax (1)

什么是Ajax&#xff1a; 浏览器与服务器进行数据通讯的技术&#xff0c;动态数据交互 axios库地址&#xff1a; <script src"https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script> 如何使用呢&#xff1f; 我们现有个感性的认识 <scr…...

Python基础语法:基本数据类型(数字类型和布尔类型)

在我们的日常生活中&#xff0c;经常会用到数字&#xff0c;所以数字&#xff08;numbers&#xff09;是 pytthon 中的一个基本数据类型。在 python 里面啊&#xff0c;numbers 这种数据类型是一个大类&#xff0c;在 numbers 这个大类下面有整型、浮点型&#xff08;小数&…...

springboot 下载 Excel 文件的 Controller 层案例

环境 pom.xml 中 springboot版本&#xff1a; <parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.15</version></parent>Excel 文件依赖&#xff1a; &l…...

RabbitMQ队列

RabbitMQ队列 1、死信的概念 ​ 先从概念解释上搞清楚这个定义&#xff0c;死信&#xff0c;顾名思义就是无法被消费的消息&#xff0c;字面意思可以这样理解&#xff0c;一般来说,producer将消息投递到broker或者直接到queue里了&#xff0c;consumer 从 queue取出消息进行消…...

Day12:信息打点-Web应用源码泄漏开源闭源指纹识别GITSVNDS备份

目录 开源-CMS指纹识别源码获取方式 闭源-习惯&配置&特性等获取方式 闭源-托管资产平台资源搜索监控 思维导图 章节点 Web&#xff1a;语言/CMS/中间件/数据库/系统/WAF等 系统&#xff1a;操作系统/端口服务/网络环境/防火墙等 应用&#xff1a;APP对象/API接口/微…...

使用正确的技术和项目管理工具来定义项目范围

根据 PMI 的统计&#xff0c;34% 的项目会出现范围蔓延&#xff1a;悄悄增加超出范围的活动&#xff0c;威胁到利润空间、项目成功率和客户满意度。 预防和控制范围蔓延的方法之一&#xff0c;是首先明确界定项目范围。 项目范围是项目规划、资源调度和变更管理的重要步骤。然…...

【C++】类型转换和IO流

目录 C语言中的类型转换 C eplicit && volatitle eplicit volatile C强制类型转换 static_cast(相关类型) reinterpret_cast(不相关类型&#xff09; const_cast&#xff08;去掉const属性&#xff09; dynamic_cast RTTI&#xff08;了解&#xff09; IO流 …...

leetCode刷题 5.最长回文子串

目录 1. 思路 2. 解题方法 3. 复杂度 4. Code 题目&#xff1a; 给你一个字符串 s&#xff0c;找到 s 中最长的回文子串。 如果字符串的反序与原始字符串相同&#xff0c;则该字符串称为回文字符串。 示例 1&#xff1a; 输入&#xff1a;s "babad" 输出&#x…...

计算机组成原理面试题

计算机组成原理是计算机科学的基础课程之一&#xff0c;涉及计算机系统的基本结构和工作原理。以下是一些可能出现在面试中的计算机组成原理相关题目&#xff1a; 1. **什么是冯诺依曼体系结构&#xff1f;** - 冯诺依曼体系结构是一种计算机组织架构&#xff0c;它将程序指…...

「Mybatis深入三」:高级查询-模糊查询

一、需求 根据username 模糊查询user 表 二、代码演示 1、方式1 数据库环境 CREATE DATABASE mybatis_db; USE mybatis_db; CREATE TABLE user (id INT(11) NOT NULL AUTO_INCREMENT,username VARCHAR(32) NOT NULL COMMENT 用户名称,birthday DATETIME DEFAULT NULL COMMEN…...

Vim 调用外部命令学习笔记

Vim 外部命令集成完全指南 文章目录 Vim 外部命令集成完全指南核心概念理解命令语法解析语法对比 常用外部命令详解文本排序与去重文本筛选与搜索高级 grep 搜索技巧文本替换与编辑字符处理高级文本处理编程语言处理其他实用命令 范围操作示例指定行范围处理复合命令示例 实用技…...

微软PowerBI考试 PL300-选择 Power BI 模型框架【附练习数据】

微软PowerBI考试 PL300-选择 Power BI 模型框架 20 多年来&#xff0c;Microsoft 持续对企业商业智能 (BI) 进行大量投资。 Azure Analysis Services (AAS) 和 SQL Server Analysis Services (SSAS) 基于无数企业使用的成熟的 BI 数据建模技术。 同样的技术也是 Power BI 数据…...

2025年能源电力系统与流体力学国际会议 (EPSFD 2025)

2025年能源电力系统与流体力学国际会议&#xff08;EPSFD 2025&#xff09;将于本年度在美丽的杭州盛大召开。作为全球能源、电力系统以及流体力学领域的顶级盛会&#xff0c;EPSFD 2025旨在为来自世界各地的科学家、工程师和研究人员提供一个展示最新研究成果、分享实践经验及…...

python爬虫:Newspaper3k 的详细使用(好用的新闻网站文章抓取和解析的Python库)

更多内容请见: 爬虫和逆向教程-专栏介绍和目录 文章目录 一、Newspaper3k 概述1.1 Newspaper3k 介绍1.2 主要功能1.3 典型应用场景1.4 安装二、基本用法2.2 提取单篇文章的内容2.2 处理多篇文档三、高级选项3.1 自定义配置3.2 分析文章情感四、实战案例4.1 构建新闻摘要聚合器…...

爬虫基础学习day2

# 爬虫设计领域 工商&#xff1a;企查查、天眼查短视频&#xff1a;抖音、快手、西瓜 ---> 飞瓜电商&#xff1a;京东、淘宝、聚美优品、亚马逊 ---> 分析店铺经营决策标题、排名航空&#xff1a;抓取所有航空公司价格 ---> 去哪儿自媒体&#xff1a;采集自媒体数据进…...

【HTTP三个基础问题】

面试官您好&#xff01;HTTP是超文本传输协议&#xff0c;是互联网上客户端和服务器之间传输超文本数据&#xff08;比如文字、图片、音频、视频等&#xff09;的核心协议&#xff0c;当前互联网应用最广泛的版本是HTTP1.1&#xff0c;它基于经典的C/S模型&#xff0c;也就是客…...

学习STC51单片机32(芯片为STC89C52RCRC)OLED显示屏2

每日一言 今天的每一份坚持&#xff0c;都是在为未来积攒底气。 案例&#xff1a;OLED显示一个A 这边观察到一个点&#xff0c;怎么雪花了就是都是乱七八糟的占满了屏幕。。 解释 &#xff1a; 如果代码里信号切换太快&#xff08;比如 SDA 刚变&#xff0c;SCL 立刻变&#…...

C# 求圆面积的程序(Program to find area of a circle)

给定半径r&#xff0c;求圆的面积。圆的面积应精确到小数点后5位。 例子&#xff1a; 输入&#xff1a;r 5 输出&#xff1a;78.53982 解释&#xff1a;由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982&#xff0c;因为我们只保留小数点后 5 位数字。 输…...

使用LangGraph和LangSmith构建多智能体人工智能系统

现在&#xff0c;通过组合几个较小的子智能体来创建一个强大的人工智能智能体正成为一种趋势。但这也带来了一些挑战&#xff0c;比如减少幻觉、管理对话流程、在测试期间留意智能体的工作方式、允许人工介入以及评估其性能。你需要进行大量的反复试验。 在这篇博客〔原作者&a…...

Kafka主题运维全指南:从基础配置到故障处理

#作者&#xff1a;张桐瑞 文章目录 主题日常管理1. 修改主题分区。2. 修改主题级别参数。3. 变更副本数。4. 修改主题限速。5.主题分区迁移。6. 常见主题错误处理常见错误1&#xff1a;主题删除失败。常见错误2&#xff1a;__consumer_offsets占用太多的磁盘。 主题日常管理 …...