18查询一行数据慢

查询一行数据慢

select * from t where id=1；查询慢

锁

等待其他事务的锁

事务更新

sessionA	sessionB
start transaction with consistent snapshot;
	update t set c=c+1 where id=1;//执行100万次
select * from t where id=1;
select * from t where id=1 lock in sharemode;
事务A启动，事务B启动，事务B update t1 set i=i+1 where id=1;执行10w次；
事务B select * from t where id=1;//一致性读，等待很久
select * from t where id=1 lock in share mode;//当前读，快速返回
因为MVCC控制，第一个查询语句需执行10w次redolog-1 才返回

慢sql排查

1 排查慢sql日志

启用日志
SHOW VARIABLES LIKE 'slow_query_log';
set global slow_query_log='ON';
set global long_query_time = 3; 阈值 long_query_time 默认10s

2 分析工具

2.1 mysqldumpslow

perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log"

-s：采用 order 排序的方式，排序方式可以有以下几种。分别是 c（访问次数）、t（查询时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式
-t：返回前 N 条数据
-g：后面可以是正则表达式，对大小写不敏感

2.2 pt-query-digest分析日志

关注参数:查询次数，总执行时间，平均查询时间，扫描行数，锁时间，返回行数
针对查询时间长、锁时间长、扫描行数不符合预期的sql语句进一步分析

分析执行计划，分析优化器的行为

启动配置 performance_schema=on 会有10%左右性能损耗
连接状态排查（是否有死锁）
show processlist 查看链接的状态
若有死锁通过 select blocking_pid from sys.schema_table_lock_waits; 查找阻塞的PID
kill命令断开

慢的原因

• sql自身问题
  select字段过多使用文件排序、join导致性能问题、子查询性能差、groupby orderby使用临时表、索引未命中（前缀问题，函数，统计问题[force index来优化]）、大事务引发锁问题
• 外部因素
  锁、内存、redo log刷盘等、统计信息不准导致执行计划选择错误
  • 内存
    redo log buffer不足、binlog buffer不足、内存命中率

性能分析

SHOW PROFILE 测试环境使用，快速方便，MySQL 8.0弃用