Mysql Usage

显示当前进程列表

1
2
3
SHOW PROCESSLIST;

SHOW full PROCESSLIST 

显示最大链接数

1
show variables like '%max_connections%';

当连接数不够用时，产生错误：Too many connections

System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding ‘EnableRetryOnFailure()’ to the ‘UseMySql’ call. —> MySqlConnector.MySqlException (0x80004005): Too many connections at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 910 at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 556 at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 422 at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 126 at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 126 at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Nullable1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 940 at MySqlConnector.MySqlConnection.OpenAsync(Nullable1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 445 at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlRelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)

修改my.ini文件，如下配置： [mysqld] max_connections=200

重新启动mysql服务生效。

调整失效连接清理时长缩短

1
2
wait_timeout=7200
interactive_timeout=7200

以上两个配置项配合使用。

示慢日志是否开启

1
SHOW VARIABLES LIKE 'slow_query_log%';

查询慢日志记录时间秒

1
SHOW VARIABLES LIKE 'long_query_time%';

修改慢日志记录方式为文件、表

1
set global log_output ='FILE,TABLE'

查询慢日志

1
SELECT * FROM mysql.slow_log

打开慢日志文件

当配置项 slow_query_log_file="iZ1vnl5ota5kq0Z-slow.log" 设置的是相当路径时，慢日志的文件会保存在所在数据库目录下，也就是配置项 datadir 指定的目录下。

windows下使用mysqldumpslow.pl命令分析慢日志

需要从官网 http://strawberryperl.com/ 下安装Perl

锁定时间最长的前10条 perl C:\tools\mysql\mysqldumpslow.pl -s l -t 10 C:\wk\iZ1vnl5ota5kq0Z-slow.log
得到返回记录集最多的10个SQL。 perl C:\tools\mysql\mysqldumpslow.pl -s r -t 10 C:\wk\iZ1vnl5ota5kq0Z-slow.log
得到访问次数最多的10个SQL perl C:\tools\mysql\mysqldumpslow.pl -s c -t 10 C:\wk\iZ1vnl5ota5kq0Z-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。 perl C:\tools\mysql\mysqldumpslow.pl -s t -t 10 -g “left join” C:\wk\iZ1vnl5ota5kq0Z-slow.log
另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现刷屏的情况。 perl C:\tools\mysql\mysqldumpslow.pl -s r -t 20 C:\wk\iZ1vnl5ota5kq0Z-slow.log | more

perl命令语法

 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default（排序方式）
                 al: average lock time（平均锁定时间）
                 ar: average rows sent（平均返回记录数）
                 at: average query time（平均查询时间）
                  c: count（访问计数）
                  l: lock time（锁定时间）
                  r: rows sent（返回记录）
                  t: query time（查询时间）
   -r           reverse the sort order (largest last instead of first)
   -t NUM       just show the top n queries（返回前面n条数据）
   -a           don't abstract all numbers to N and strings to 'S'
   -n NUM       abstract numbers with at least n digits within names
   -g PATTERN   grep: only consider stmts that include this string（正则匹配模式，大小写不敏感）
   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                default is '*', i.e. match all
   -i NAME      name of server instance (if using mysql.server startup script)
   -l           don't subtract lock time from total time

直接打开慢日志文件

# Time: 2024-11-06T14:56:10.559590Z
# User@Host: root[root] @ iZ1vnl5ota5kq20Z [10.18.39.188]  Id:    55
# Query_time: 29.007449  Lock_time: 0.000192 Rows_sent: 5  Rows_examined: 20636
SET timestamp=1730904970;
SELECT `a`.`Id`, `a`.`Amount`, `a`.`BillNo`, `a`.`CampaignAmount`, `a`.`CancelTime`, `a`.`CancelUserId`, `a`.`ChannelId`, `a`.`CreationTime`, `a`.`CreatorUserId`, `a`.`DC`, `a`.`DeleterUserId`, `a`.`DeletionTime`, `a`.`DeviceID`, `a`.`DiscountRate`, `a`.`DurationHour`, `a`.`DurationMin`, `a`.`EndTime`, `a`.`EstimateTime`, `a`.`IsCancel`, `a`.`IsDeleted`, `a`.`IsPaymentStatus`, `a`.`IsPending`, `a`.`IsStop`, `a`.`IsTermination`, `a`.`JobId`, `a`.`LastUpdateTime`, `a`.`ObjectId`, `a`.`ObjectType`, `a`.`OriginalAmount`, `a`.`PendingTime`, `a`.`Price`, `a`.`Qty`, `a`.`RealMin`, `a`.`Remark`, `a`.`TenantId`, `a`.`TerminationTime`, `a`.`TerminationUserId`
FROM `AppChargerBills` AS `a`
WHERE (((false OR NOT (`a`.`IsDeleted`)) AND (false OR (`a`.`TenantId` = 1))) AND (`a`.`DeviceID` = 75)) AND ((((`a`.`IsPaymentStatus` AND NOT (`a`.`IsCancel`)) AND NOT (`a`.`IsStop`)) AND `a`.`TerminationUserId` IS NULL) AND (timestamp('2024-11-06 22:55:41.551716') < `a`.`EstimateTime`));

各段含义：

# Time：这是查询执行的时间戳。
# User@Host：执行查询的用户是root，主机是localhost。
# Query_time：查询执行花费了29.007449秒。
# Lock_time：查询在锁定上花费了0.000192秒。
Rows_sent：查询发送了5行数据给客户端。
Rows_examined：查询检查了20636行数据，这可能是性能问题的一个指标，特别是如果检查的行数远大于发送的行数。
use dbname：表明这个查询是在dbname数据库上执行的。
SET timestamp：这是查询执行时的UNIX时间戳。
SELECT：这是实际执行的SQL语句。

explain 分析

使用explain可以模拟优化器执行SQL查询语句，从而知道MySQL怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈

id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type ：查询类型或者是其他操作类型
table ：正在访问哪个表
partitions ：匹配的分区
type ：访问的类型
possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
key ：实际使用到的索引，如果为NULL，则没有使用索引
key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered ：查询的表行占表的百分比
Extra ：包含不适合在其它列中显示但十分重要的额外信息

type字段

从左往右，越靠左边的越优秀

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

NULL：该表没有使用索引，全表扫描，效率最低。
system：该表只有一行，全表扫描，效率最低。
const：该表只有一行，全表扫描，效率最低。
eq_ref：使用等值查询，且该表只有一行，全表扫描，效率最低。
ref：使用非等值查询，且该表只有一行，全表扫描，效率最低。
ref_or_null：使用非等值查询，且该表只有一行，全表扫描，效率最低。
index_merge：使用索引合并，效率较高。
range：使用范围查询，效率较高。
index：使用索引，效率较高。
ALL：全表扫描，效率最低。

参考：MySQL explain 应用详解(吐血整理🤩)

查看正在进行中的事务

SELECT * FROM information_schema.INNODB_TRX

Field	Type	Null	Default	Remark
trx_id	varchar(18)	NO		#事务ID
trx_state	varchar(13)	NO		#事务状态：
trx_started	datetime	NO	0000-00-00 00:00:00	#事务开始时间；
trx_requested_lock_id	varchar(81)	YES	NULL	#innodb_locks.lock_id
trx_wait_started	datetime	YES	NULL	#事务开始等待的时间
trx_weight	bigint(21) unsigned	NO	0	#
trx_mysql_thread_id	bigint(21) unsigned	NO	0	#事务线程ID
trx_query	varchar(1024)	YES	NULL	#具体SQL语句
trx_operation_state	varchar(64)	YES	NULL	#事务当前操作状态
trx_tables_in_use	bigint(21) unsigned	NO	0	#事务中有多少个表被使用
trx_tables_locked	bigint(21) unsigned	NO	0	#事务拥有多少个锁
trx_lock_structs	bigint(21) unsigned	NO	0	#
trx_lock_memory_bytes	bigint(21) unsigned	NO	0	#事务锁住的内存大小（B）
trx_rows_locked	bigint(21) unsigned	NO	0	#事务锁住的行数
trx_rows_modified	bigint(21) unsigned	NO	0	#事务更改的行数
trx_concurrency_tickets	bigint(21) unsigned	NO	0	#事务并发票数
trx_isolation_level	varchar(16)	NO		#事务隔离级别
trx_unique_checks	int(1)	NO	0	#是否唯一性检查
trx_foreign_key_checks	int(1)	NO	0	#是否外键检查
trx_last_foreign_key_error	varchar(256)	YES	NULL	#最后的外键错误
trx_adaptive_hash_latched	int(1)	NO	0	#
trx_adaptive_hash_timeout	bigint(21) unsigned	NO	0	#

查看正在锁的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查询是否锁表

1
SHOW OPEN TABLES where In_use > 0;

查看最近死锁的日志

在发生死锁时，这几种方式都可以查询到和当前死锁相关的信息。

1
show engine innodb status

生成未提交事务 kill 命令

1
2
3
SELECT GROUP_CONCAT(CONCAT('kill ',id) SEPARATOR '; ') AS cmd FROM information_schema.processlist 
WHERE USER='root' 
AND command='Sleep' and db = 'xxx'

显示当前进程列表#

显示最大链接数#

调整失效连接清理时长缩短#

示慢日志是否开启#

查询慢日志记录时间 秒#

修改慢日志记录方式为文件、表#

查询慢日志#

打开慢日志文件#

windows下使用mysqldumpslow.pl命令分析慢日志#

perl命令语法#

直接打开慢日志文件#

explain 分析#

查看正在进行中的事务#

查看正在锁的事务#

查看等待锁的事务#

查询是否锁表#

查看最近死锁的日志#

生成未提交事务 kill 命令#