MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

MySQL : 如何监控和处理慢查询与长事务 ?


什么是慢查询、长事务 ?

慢查询 是指一条 SQL 的执行时间太长。比如在一个有100w条数据的表中,查询一条数据时未命中索引,从而通过全表扫描查询数据,这个查询会耗时很长。这就是一个 Long SQL 。类似,更新数据、删除数据也可能出现慢查询 。

长事务,是指事务开启后长时间未结束(commit或者rollback都没执行)。出现长事务的原因有很多,例如:

  • 事务中有一个慢查询;
  • 就是忘记结束事务了;
  • 一个长事务中锁住了一些资源,导致其他需要该资源的事务也变成长事务。

很多情况下,慢查询和长事务,会对业务产生很大的负面影响:

  • 业务响应太慢。
  • 连接数暴涨。

如何发现慢查询 ?

方法1:

使用 show processlist 查看MySQL 线程情况,若有 Command 为 Query ,Time 很大的线程在列表中,那么这个线程就是在执行一个慢查询。Info 字段能看出来对应的 SQL 。

select * from information_schema.processlist 效果和 show processlist 相同。

示例:

mysql> show processlist \G
***************************[ 1. row ]***************************
Id      | 2
User    | root
Host    | localhost:52376
db      | <null>
Command | Query
Time    | 0
State   | init
Info    | show processlist

show processlist 官方文档 

方法2:

开启慢查询日志,实时解析日志内容。

略。

如何处理不可接受的慢查询?

如果慢查询在意料之中,可接受,那么不用处理。否则应该:

  • 干掉此次的慢查询。使用 kill <id> ,id 是 show processlist 返回的 Id 。
  • 优化SQL、索引等,防止再次出现慢查询。

如何发现长事务?

根据 MySQL运维中长事务和锁等待排查 ,可以用下面的 SQL :

select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G

查询结果中,idle_time 过大的事务就是长事务。输出结果中, trx_mysql_thread_id  是对应的线程标识。

如何干掉长事务?

方案1

INFORMATION_SCHEMA.INNODB_TRX 得到的长事务信息中,有对应的线程标识, kill 掉即可。

方案2

设置较小的 wait_timeout 可以干掉部分长事务。 对于业务代码,使用的是非交互模式的连接,可以使用 wait_timeout 设置超时时间。相当于,若某个连接在 wait_timeout 这个时间范围内是空闲的,MySQL 服务器会自动关闭连接。 注意,数据库连接池参数要根据情况调整下。


( 本文完 )

文章目录