type
status
date
slug
summary
tags
category
icon
password

1、基本运行情况

查看正在执行的 SQL 线程信息

查看 InnoDB 存储引擎信息

用于显示 InnoDB 存储引擎的状态信息,包括信号量(CPU自旋、OSWAITARRAYINFO)、死锁情况、外键冲突、事务状态、I/O操作和缓冲池等,这些都是数据库性能诊断的关键指标。通过对这些信息的理解,可以帮助优化数据库性能,解决死锁和资源争用问题。

查看内存配置参数

这些变量分别表示InnoDB缓冲池大小、键缓冲区大小和查询缓存大小。如果这些值设置得过高,会导致MySQL占用大量内存。
  • innodb_buffer_pool_size :这个参数控制了InnoDB存储引擎使用的内存量。根据系统可用内存和数据库的读写负载情况,适当调整这个值可以减少内存占用。建议将其设置为系统总内存的50%-80%。
  • key_buffer_size:这个参数控制了键缓冲区的大小,主要用于MyISAM存储引擎。如果您的数据库主要使用InnoDB存储引擎,可以将这个值设置为较小的值或完全关闭(设置为0)。
  • query_cache_size :查询缓存用于存储SELECT语句和对应的结果。如果您的数据库经常更新数据,查询缓存可能会占用大量内存。在这种情况下,可以关闭查询缓存以减少内存占用。通过将query_cache_size设置为0来关闭查询缓存。

查看字符集

查看数据库/表大小

查看表最后更新时间

2、事务和锁

确认表是否在被使用

查看当前运行的所有事务

INNODB_TRX表中主要记录了当前正在执行的事务信息,包括只读事务。首先来看字段和字段所表示的含义:
字段名称
字段含义
TRX_ID
InnoDB存储引擎内部的唯一事务ID
TRX_WEIGHT
事务权重(与事务修改的行数和锁定的行数有关),当两个事务执行发生死锁时,InnoDB会选择权重较低的事务进行回滚
TRX_STATE
当前的事务执行状态,包括RUNNING, LOCK WAIT, ROLLING BACK, 以及COMMITTING,LOCK WAIT表示当前事务正等待某个锁的释放
TRX_STARTED
事务开始时间,格式如2000-01-01 14:01:08
TRX_REQUESTED_LOCK_ID
当前事务所等待的锁ID,该字段只有在状态为LOCK WAIT才有值,否则为NULL。可与INNODB_LOCKS通过LOCK_ID字段进行关联查询,获取更为详细的锁信息。
TRX_WAIT_STARTED
当前事务等待锁的起始时间,在状态为LOCK WAIT时才有值,否则为NULL。
TRX_QUERY
当前事务正在执行的SQL语句(不是事务所有的执行语句)
TRX_OPERATION_STATE
事务的当前操作状态,包括PREPARING, UPDATING, DELETING, COMMITTING以及NULL,该字段在绝大部分情况下均为NULL,捕捉某一事务的瞬间执行状态还是比较困难的(除非是大事务)
TRX_TABLES_IN_USE
正在执行的SQL语句所操作的表数量,是一个动态变化值,通常很难观测
TRX_TABLES_LOCKED
当前事务在各个表中添加行锁的表数量
TRX_LOCK_STRUCTS
当前事务持有的锁数量
TRX_LOCK_MEMORY_BYTES
当前事务中锁结构的内存总占用
TRX_ROWS_LOCKED
当前事务锁住的近似数据总行数
TRX_ROWS_MODIFIED
当前事务插入、修改的总行数
TRX_CONCURRENCY_TICKETS
表示当前事务在换出之前所能做的工作之和
TRX_ISOLATION_LEVEL
当前事务隔离级别,包括READ UNCIMMITTED、READ COMMITTED、READ REPEATABLE以及SERIALIZABLE
TRX_UNIQUE_CHECKS
当前事务是否开启唯一性检查
TRX_FOREIGN_KEY_CHECKS
当前事务是否开启外键检查
TRX_LAST_FOREIGN_KEY_ERROR
当前事务执行时最后发生的外键错误
TRX_ADAPTIVE_HASH_LATCHED
当前事务是否锁定了自适应哈希索引
在这20多个字段中,较为重要的包括事务ID,事务执行状态,事务等待锁的起始时间,事务锁定的近似总行数。

查看当前出现的锁

  • 在 MySQL 5.5 以上、5.7.14以下的版本中,用户可以通过 information_schema 下的 INNODB_TRXINNODB_LOCKS 以及INNODB_LOCK_WAITS这三张表简单地监控并分析可能存在的锁问题。
  • 在 MySQL 8.0 版本中,则需要使用performance_schema下的data_locks以及data_lock_waits获取相关的锁以及锁等待信息。
  • MySQL版本在 5.7.14 到 8.0 之间的用户,只能通过其它手段间接的获取上述信息。
以 MySQL 8.0 版本的 performance_schema.data_locks 为例
字段名称
字段含义
ENGINE
申请或持有锁的存储引擎类型
ENGINE_LOCK_ID
存储引擎内部的锁ID,该值会发生动态变化,外部系统不应该依赖该值
ENGINE_TRANSACTION_ID
持有锁的事务ID,与INNODB_TRX中的TRX_ID对应
THREAD_ID
持有锁的线程ID
EVENT_ID
事件ID,该字段将于下方进行详细描述
OBJECT_SCHEMA
锁所在的schema(database)
OBJECT_NAME
锁所在的表名称
PARTITION_NAME
锁所在分片名称
SUBPARTITION_NAME
锁所在的子分片名称
INDEX_NAME
被添加锁的索引名称
OBJECT_INSTANCE_BEGIN
锁的内存空间起始地址
LOCK_TYPE
锁类型,包含TABLE和RECORD
LOCK_MODE
锁的模式,包括S,X,IS,IX,AUTO_INC以及UNKNOWN
LOCK_STATUS
锁的状态,InnoDB引擎中包括GRANTED(已添加)和WAITING(等待中)
LOCK_DATA
锁覆盖的范围,该字段将于下方详细描述

查看由于锁占用导致等待的表

data_lock_waits相比于INNODB_TRX以及data_locks而言则要更复杂一些,该表实际上是一个ManyToMany的关系表,记录了data_locks中锁之间的等待以及依赖关系,同时也记录了锁所对应的事务/会话信息。
字段名称
字段含义
ENGINE
存储引擎类型
REQUESTING_ENGINE_LOCK_ID
存储引擎内锁ID,对应于data_locks表中的ENGINE_LOCK_ID
REQUESTING_ENGINE_TRANSACTION_ID
存储引擎内事务ID
REQUESTING_THREAD_ID
线程ID
REQUESTING_EVENT_ID
事件ID
REQUESTING_OBJECT_INSTANCE_BEGIN
锁的内存空间起始地址
BLOCKING_ENGINE_LOCK_ID
等待释放的锁ID
BLOCKING_ENGINE_TRANSACTION_ID
等待结束的事务ID
BLOCKING_THREAD_ID
等待结束的线程ID
BLOCKING_EVENT_ID
等待结束的事件ID
BLOCKING_OBJECT_INSTANCE_BEGIN
等待结束的锁的内存空间起始地址
notion image

查看事务等待锁的超时时间

当一个事务的等待时间超过该值后,就对这个事务进行回滚,于是锁就释放了,另一个事务就可以继续执行了。在 InnoDB 中,参数 innodb_lock_wait_timeout 是用来设置超时时间的,默认值时 50 秒。

开启主动死锁检测

主动死锁检测在发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑,默认就开启。

3、Mysql日志

查看慢日志参数

  • slow_query_log:慢日志开关,默认为OFF关闭
  • slow_query_log_file:慢日志保存文件;
  • long_query_time:慢日志判断阀值,执行时长超过此值的sql都将记录入慢日志文件;
  • slow_launch_time:此值不是慢日志的判断阀值,注意区分,好像是废弃的;

启用慢日志

方法一:全局变量设置,只在当前会话生效。
方法二:配置文件设置,然后重启Mysql服务
修改配置文件 my.cnf,在 [mysqld] 的下方加入

查看慢日志记录

执行超过时长阀值(默认10s)的sql语句
此时查看 slow_query_log_file 文件,多出如下记录:
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow。使用 mysqldumpslow --help 来查看用法
常见用法
使用Arthas查看和修改Spring容器对象Mysql系列:select for update是怎么加锁的
mcbilla
mcbilla
一个普通的干饭人🍚
Announcement
type
status
date
slug
summary
tags
category
icon
password
🎉欢迎来到飙戈的博客🎉
-- 感谢您的支持 ---
👏欢迎学习交流👏