Mysql调优攻略大全(建议收藏)

0.前言

建议与我的八股专栏的jvm八股配合使用。觉得本贴有用的收藏点个赞评论下送朵花什么的吧。下面我会让你看看什么是传说中的mysql调优(八股面试吹b版)。

打个小广告

我的架构设计专栏:https://www.nowcoder.com/creation/manager/columnDetail/0ybvLm

我的八股专栏:https://www.nowcoder.com/creation/manager/columnDetail/j8ZZk0

内有详细苍穹外卖话术哦!

为什么我要开个八股专栏?

一方面有感于现在面试找工作对八股的要求之高,一方面有感于市面上的一些八股资料又乱又复杂难懂,所以我决定自己结合各种市面上优秀的博客、gpt,牛客上的面经和我自己的理解,整合出一份八股资料,我的目标是:让八股文成为真正简单易懂的八股,知识点丰富且浅显易懂,不追求过分的深入,但一定重点层次分明(如果想深入了解知识点的话还是建议自己认真看看javaguide和小林coding,但其实我就是看了他们的全部博客取精华精炼部分融入我的专栏。。。),一句话就是我的八股专栏主打一个面试速成,一点超纲的知识点都不会有,因为我自己也用这份笔记准备秋招。。。

1.怎么定位慢查询?

2种方法·:

  • 运维监控工具Slywalking

我们当时做压测的时候有的接口非常的慢,接口的响应时间超过了2秒以上,因为我们当时的系统部署了运维的监控系统Skywalking ,在展示的报表中可以看到是哪一个接口比较慢,并且可以分析这个接口哪部分比较慢,这里可以看到SQL的具体的执行时间,所以可以定位是哪个sql出了问题

  • mysql慢查询日志

2.怎么分析慢sql?

通过EXPLAIN分析SQL执行计划

  • ALL:表示全表扫描,需要遍历全表来找到对应的行。
  • possible_keys:可能使用到的索引。
  • key:实际使用到的索引。
  • key_len:当前使用的索引的长度。
  • ref:关联id等信息。
  • rows:查找到记录所扫描的行数。
  • filtered:查找到所需记录占总扫描记录数的比例。
  • Extra:额外的信息。

通过Show Profile分析SQL执行性能

上述通过 EXPLAIN 分析执行计划,仅仅是停留在分析SQL的外部的执行情况,如果我们想要深入到MySQL内核中,从执行线程的状态和时间来分析的话,这个时候我们就可以选择Profile。

Profile除了可以分析执行线程的状态和时间,还支持进一步选择ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES等类型来查询SQL语句在不同系统资源上所消耗的时间。以下是相关命令的注释:

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type参数:
| ALL:显示所有开销信息
| BLOCK IO:阻塞的输入输出次数
| CONTEXT SWITCHES:上下文切换相关开销信息
| CPU:显示CPU的相关开销信息 
| IPC:接收和发送消息的相关开销信息
| MEMORY :显示内存相关的开销,目前无用
| PAGE FAULTS :显示页面错误相关开销信息
| SOURCE :列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS:显示swap交换次数的相关开销信息

Show Profiles只显示最近发给服务器的SQL语句,默认情况下是记录最近已执行的15条记录,我们可以重新设置profiling_history_size增大该存储记录,最大值为100。

获取到Query_ID之后,我们再通过Show Profile for Query ID语句,就能够查看到对应Query_ID的SQL语句在执行过程中线程的每个状态所消耗的时间了:

通过以上分析可知:SELECT COUNT(*) FROM order; SQL语句在Sending data状态所消耗的时间最长,这是因为在该状态下,MySQL线程开始读取数据并返回到客户端,此时有大量磁盘I/O操作。

3.怎么用explain分析sql执行计划

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划

1.什么是执行计划

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后,具体的执行方式。

通过 EXPLAIN 的结果,可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

2.执行计划常用字段

MySQL 为我们提供了 EXPLAIN 命令,来获取执行计划的相关信息。

需要注意的是,EXPLAIN 语句并不会真的去执行相关的语句,而是通过查询优化器对语句进行分析,找出最优的查询方案,并显示对应的信息。

执行计划有12个字段组成,常用的有:

1.type表示查询表连接类型**,查看索引执行情况的一个重要指标。以下性能从好到坏依次:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system:这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。
  • const:通过一次索引就能找到数据,一般用于主键或唯一索引作为条件,这类扫描效率极高,,速度非常快。基于主键或唯一索引唯一值查询,最多返回一条结果,比eq_ref略好。
  • eq_ref:常用于主键或唯一索引扫描,一般指使用主键的关联查询
  • ref : 常用于非主键和唯一索引扫描。
  • ref_or_null:这种连接类型类似于ref,区别在于MySQL会额外搜索包含NULL值的行
  • index_merge:使用了索引合并优化方法,查询使用了两个以上的索引。
  • unique_subquery:类似于eq_ref,条件用了in子查询
  • index_subquery:区别于unique_subquery,用于非唯一索引,可以返回重复值。
  • r

剩余60%内容,订阅专栏后可继续查看/也可单篇购买

Java抽象带蓝子的笔记专栏 文章被收录于专栏

我的笔记专栏,内有自己整理的八股知识笔记和算法刷题笔记,我会不断通过他人和自己的面经来更新和完善自己的八股笔记。专栏每增加一篇文章费用就会上涨一点,如果你喜欢的话建议你尽早订阅。内有超详细苍穹外卖话术!后续还会更新其他项目和我的实习经历的话术!敬请期待!

全部评论
我是觉得篮子哥牛逼的👀
点赞 回复 分享
发布于 08-29 02:01 江苏
skywalking吧
点赞 回复 分享
发布于 10-16 14:10 湖北
篮子哥可以整理整理春秋招笔试高频知识点和题目大全。狠狠洽一波
点赞 回复 分享
发布于 10-17 23:12 江苏

相关推荐

11-02 12:46
已编辑
东南大学 C++
点赞 评论 收藏
分享
12 91 评论
分享
牛客网
牛客企业服务