蓝盟IT小贴士，来喽！
SQL语句执行缓慢的理由是面试中经常听到的，对服务器端开发来说也是必须关注的问题。
在生产环境中，SQL运行缓慢是一个重大事件。 那么，如何定位慢速SQL、慢速原因以及如何防患于未然。 接下来，带着这些问题，开始这次旅行吧！
-思维导图-
写入操作
作为后端开发，日常操作数据库最常见的是写入操作和读出操作。 读一下操作吧。 在这个分类中，主要看一下写入操作时SQL为什么会变慢。
刷脏页
脏页的定义是，在存储器数据页和磁盘数据页不一致的情况下，将该存储器数据页称为脏页。
那么，为什么出现脏页，刷脏页的话SQL会变慢呢？ 那么，让我们来看看作业时的流程。
在一个写入操作的SQL中，写入日志、内存和同步磁盘都在运行中。
- Mysql架构图-
其中，日志文件是存储物理日志的存储引擎层中的重做日志。 写入时，存储引擎(在此介绍Innodb  )将记录写入重做日志并更新缓存。 这样就完成了更新操作。 后续的操作存储引擎根据需要将操作记录同步到磁盘.
看到这里可能有疑问，重做日志不是日志文件吗？ 日志文件保存在磁盘上。 那不是写得很慢吗？
实际上，写入重做log的过程是依次写入磁盘，磁盘的顺序写入缩短了查找等时间，比随机写入快得多(类似于Kafka存储原理)，因此写入重做log非常快
那么，让我们回到第一个问题。 为什么脏页出现，脏页为什么减慢了SQL？ 想想看。 重做日志的大小是恒定的，是循环写入。 在高并发情况下，重做日志很快就会满，但如果数据无法与磁盘同步，则会生成脏页，阻止后续的写入操作。 SQL的执行自然会变慢。
上锁。
写操作时SQL慢的另一种情况可能是锁定的，这很容易理解。 比如，你和别人一起租房子，只有一个厕所，你们俩都想同时去，但对方比你丢得早。 所以，这个时候你不能进去直到对方出来。
支持Mysql。 如果SQL中更改的行正好被锁定，则后续操作将无法等待锁定解除。但是另一个极端情况是室友占用了厕所，这个时候该怎么办？ 无论如何也不能穿裤子吧。 我很惭愧。 对Mysql的支持是发生死锁或等待锁的情况。 这种情况该怎么办？
Mysql提供了查看当前锁定情况的方法。
通过在命令行上执行图中的语句，可以查看当前正在执行的事务的情况。 这里介绍了一些查询结果的重要参数。
如果当前事务等待时间过长或发生死锁，可以使用kill线程ID解除当前锁定。
其中的线程ID表示表中的trx_mysql_thread_id参数。
读出动作
写完之后，阅读操作可能比较熟悉。 SQL慢则读取操作慢的问题在工作中经常是相关的。
慢查询
在解释读取操作变慢的原因之前，让我们先看看慢速SQL的定位。 Mysql称为行程查询日志，用于记录超过指定时间的SQL语句。 默认情况下处于关闭状态，可以手动设置打开和放置低速查询日志。
具体安排如下。
要显示当前低速查询日志的打开状态，请：
打开低速查询日志(临时):
注意：这里只暂时打开了行程查询日志，mysql重新启动后无效。 可以设置为在my.cnf上永久启用。
存在理由
我知道如何显示慢的SQL。 接下来，让我们看看读取操作时为什么会生成慢查询。
(1)索引错误
SQL查询慢的一个原因可能是索引未命中。 关于使用索引加快查询的理由和使用时的注意事项，因为在互联网上已经很多了，所以这里不做说明。
(2)脏页的问题
另一个是我们之前提到的刷脏页的情况，与写入操作不同，是在阅读的时候刷脏页。
有点无知吗，别着急，听我生动的路。
为了避免每次读写数据时访问磁盘增加IO开销，Innodb存储引擎通过将相应的数据页和索引页加载到内存缓冲池中来提高读写速度。 然后，根据最新的最小使用策略保留缓冲池中的缓存数据。
那么，如果读入的数据页不在内存中，则需要向缓冲池申请数据页，但缓冲池的数据页是恒定的，如果数据页达到上限，则需要从内存中废弃最早未使用的数据页但是，废弃脏页时，需要将脏页刷到磁盘上再利用。
看，又回到了刷脏页的时候。 阅读操作慢了也能理解吗？防患于未然
知道理由，我们怎么能避免或缓和这种状况？
首先，让我们看看索引小姐的情况。
我不知道有没有使用Mysql的explain的习惯，总之每次都使用它来调查当前SQL命中索引的情况。 避免带来未知的危险。
下面简要介绍了如何通过在要运行的SQL之前添加explain来分析当前的SQL执行计划。
执行后的结果对应的字段概要如下图所示。
这里，我们必须集中于以下字段：
1、type
演示MySQL如何在表中查找所需的行。 其中常用的类型是ALL、index、range、ref、eq_ref、const、system、NULL这样的类型从左到右，性能逐渐变好。
ALL:Mysql在所有表中循环查找匹配的行。
index  :与all不同，index类型只遍历索引树。
range  :只检索指定范围的行，使用一个索引选择行。
ref表示上述表中的连接匹配条件，用于搜索索引列值的列或常数
eq  _ ref  :类似于ref，但使用的索引是否唯一。 对于每个索引关键字值，表中只有一条记录匹配。 简单来说，在多个表连接中使用primary  key或unique  key作为相关条件。
const，system:MySQL优化部分查询并将其转换为常数时，使用这些类型进行访问。 将主键放在where列表中后，Mysql可以将查询转换为常数。 system是const类型的例外，如果查询中只有一行表，则使用system。
NULL:Mysql在优化过程中分解语句，无需访问表和索引即可执行。 例如，从索引列中选择最小值可以在单独的索引搜索中完成。
2、possible_keys
查询时可能使用的索引(但不一定使用，如果没有索引，则显示为NULL  )。
3、key
实际使用的索引。
4、rows
估计找到相应记录所需的行数。
5、额外人员
常见的有：
使用索引：表示正在使用封面索引，不需要返回到表。
Using  where  :无需读取表中的所有信息，只需索引即可获取所需的数据。 这表示当表中的所有请求列都是同一索引的一部分时发生，mysql服务器在存储引擎中查找行，然后进行过滤。using  temporary  :表示MySQL必须使用临时表来存储结果集。 排序和分组查询，一般group  by，order  by；
usingfilesort:query包含order  by操作，无法使用索引完成的排序操作称为“文件排序”。
如果要刷脏页，请控制脏页的比例，不要总是接近75%。 它还控制重做日志的写入速度，通过设置InnoDB_io_capacity参数告诉innodb磁盘能力。
总结一下
写入操作
重做日志满时打印脏页，写入也结束，SQL的执行自然变慢。
如果要更改的数据行或表被锁定，则必须等待锁定释放，SQL的执行也会变慢。
读出动作
读取操作之所以慢，是因为索引未命中，整个表都被扫描，可以使用explain方法分析SQL语句。
另一个原因是读取操作时读取的数据页不在内存中，因此为了申请新的数据页，需要废弃脏页，执行变慢。

文/上海蓝盟   IT外包专家

• 上一篇: 蓝盟IT外包，元宇宙：四梁八柱是什么
  下一篇: 蓝盟IT外包，高性能MySQL主从架构的复制原理和配置细节

• 分享到：