问题场景说明
数据库负载高，读写IO高整体一般的体现是cpu负载高，这里面具体分为两种场景，一种是业务并发升高，表现为会话中没有发现明显执行慢的sql。另一种场景是业务有明显的慢sql，查看会话列表可以明显发现长时间执行的sql。有一个简单的判断逻辑是，如果我们的数据库规格是16c，同时执行16个耗时为1s的sql，这时cpu的利用率是100%。也就是说我们再会话如果看到执行活跃的会话数量大于cpu的规则，这时候负载出现问题。
磁盘空间突增，突增一般指的是短时间内数据库磁盘利用率大量上升，比如100GB,500GB等，不过一般业务数据的大量写入（包括binlog）不会有这种数量级的体现，一般是数据库临时文件突增才会有突增情况。这里面主要设计一些长时间运行的sql，比如排序，join等，不过还有一些特殊情况是事务链表太长，同时使用blob，txt这种字段导致的表空间不断膨胀的情况，这种情况比较少见，我们这里不讨论。
锁问题，数据库锁是数据库保持数据一致性的一种机制。锁在数据库中是一种比较正常的情况，不是说出现锁就说明数据库有问题，一般来说，行锁，死锁这种不会影响数据库的使用。不过大量的行锁，死锁，mdl锁会对业务造成比较大的困扰，事务不能正常提交或者需要长时间等待。
问题发现
应急恢复
保留现场：
show processlist 保留数据库会话信息
show engine innodb status 保留innodb引擎信息
pstack $pid 保留数据库堆栈信息
iostat 保留主机磁盘利用率信息（如果能登录主机）
top -c 服务器 CPU、内存和 I/O 使用情况 （如果能登录主机）
恢复
kill对应的sql
扩容
重启数据库（一般不采用）
问题分析及定位
信息收集
确认服务器性能指标
使用 top 或 htop 命令检查服务器 CPU、内存和 I/O 使用情况。关注以下指标：
%Cpu(sy)：系统 CPU 使用率
%Cpu(us)：用户 CPU 使用率
%Cpu(id)：空闲 CPU 使用率
%Cpu(wa)：I/O 等待使用率
Mem：内存使用情况
Swap：交换分区使用情况
检查 MySQL 服务器状态
使用 SHOW GLOBAL STATUS; 命令查看 MySQL 服务器的整体运行情况，关注以下几个关键指标：
Queries：总查询次数
Threads_connected：当前连接线程数
Threads_running：当前活跃线程数
Connections：总连接数
Aborted_clients：异常中断的客户端连接数
Aborted_connects：尝试连接失败的次数
Open_tables：打开的表的数量
检查 MySQL 进程列表
使用 SHOW FULL PROCESSLIST; 或 SHOW PROCESSLIST; 命令查看当前正在执行的查询，关注以下指标：
Time：查询执行时间
State：查询状态
Info：查询语句
使用命令过滤空闲线程：pager grep -v Sleep
慢日志分析
分析慢查询日志
使用 pt-query-digest 工具分析慢查询日志，关注以下指标：
Query_time：查询执行时间
Lock_time：锁定时间
Rows_examined：扫描的行数
检查数据库表结构和索引
使用 EXPLAIN 命令分析慢查询的执行计划，关注以下指标：
type：访问类型（如 const、ref、range、index、ALL 等）
possible_keys：可能的索引
key：实际使用的索引
rows：预计扫描的行数
Extra：额外的查询信息
检查数据库参数配置
使用 SHOW GLOBAL VARIABLES; 命令查看数据库参数，关注以下几个关键参数：
innodb_buffer_pool_size：InnoDB 缓冲池大小
innodb_log_file_size：InnoDB 重做日志文件大小
innodb_flush_log_at_trx_commit：InnoDB 事务提交时的日志刷新策略
max_connections：最大并发连接数
query_cache_size：查询缓存大小
监控锁等待和死锁
使用 `SHOW ENGINE INNODB STATUS;` 命令查看 InnoDB 引擎状态，关注以下指标：
Mutex and RW-lock statistics：互斥锁和读写锁的统计信息
History list length：历史列表长度，反映系统事务的活跃程度
LATEST DETECTED DEADLOCK：最近检测到的死锁
TRANSACTIONS：当前事务的状态和信息
分析系统性能
使用 vmstat、iostat、dstat 等工具分析系统的 CPU、内存、I/O 和网络性能，关注以下指标：
vmstat：CPU 使用率、内存使用率、虚拟内存交换情况、磁盘 I/O 等
iostat：磁盘 I/O 操作、磁盘使用率、每秒传输数据量等
dstat：实时查看系统资源消耗状态，如 CPU、内存、I/O 和网络等
问题场景处理
cpu负载高
高负载基本上分为三种场景：
应用负载（QPS）高，特征：实例的QPS高，查询比较简单，执行效率高，优化余地小。表现：没有出现慢查询，或者慢查询不是主要原因，且QPS和CPU使用率曲线变化吻合。
慢查询导致查询成本高：特征：实例的QPS不高，查询执行效率低、执行时需要扫描大量表数据、优化余地大。表现：存在慢查询，QPS和CPU使用率曲线变化不吻合。
大量行锁冲突、行锁等待或后台任务导致实例CPU使用率过高，这种场景出现的概率比较低，这里不做展开。
登录服务器执行:
pager grep -v Sleep;
show processlist;
查看输出结果：
对于mysql而言，基本上数据库的规格是多少核就支持数据库实例同一时间能支持多少并发。比如，假如每个sql执行时间是1s，数据库规格是16c，那么同一时间就可以支持，16个这种sql运行，此时cpu已经100%。如果sql的执行时间是100ms，那么就能支持160个sql同时跑。这是个基本判断逻辑。
比如上面这个例子，同时有4个sql在执行，如果这个实例规格是是16核，此时的瞬间的cpu负载应该是25%，如果数据库规格是4c，那么瞬时的cpu负载是100%。
依赖上面的输出，我们正常是可以一眼看出属于哪种类型的高负载，比如结果中的sql都是大表的关联，全表扫描，基本上是第二种情况。如果结果中都是简单的查询，同时单独执行时间（可以在备库试试）很短，同时结合数据库的QPS明显升高，基本上认定是第一种场景。
针对第一种场景的优化方式：
联系业务限制业务并发
扩容实例规格
一般不是互联网场景很少遇到这种并发突然增加的场景，很多情况是业务程序逻辑出了问题，比如重试太对，写死循环等等，联系业务开发排查代码。
查看sql的执行计划，看看还有没有优化空间
针对第二种场景优化方式：
查找出对应慢sql，不过一般当时可能有很多sql输出，肉眼比较难判断具体是哪条sql引起的，可以使用pt-query-digest进行聚合分析，具体命令如下
pt-query-digest slow.log_20230315.log --since '2023-03-15 22:00:00' --until '2023-03-15 23:00:00'
找到对应sql之后一方面让业务对sql进行限流，一方面进行sql分析，看看sql的优化空间。
和业务商讨之后，可以kill对应的慢查询，恢复业务。
kill id;
对于批量的连接kill，可以使用如下脚本：
#!/bin/bash
# MySQL 登录信息
MYSQL_USER="itdba"
MYSQL_PASSWORD="xxxxxx"
MYSQL_HOST="ip"
MYSQL_PORT="33066"
获取满足条件的连接的 process ID
PROCESS_IDS=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT} -Ne "SELECT id FROM information_schema.processlist WHERE user'system user' AND command 'Sleep' AND time > 60;" | awk 'NR>1')
批量 kill 连接
for PROCESS_ID in ${PROCESS_IDS}
do
echo "Killing connection with process ID: ${PROCESS_ID}"
mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT} -e "KILL ${PROCESS_ID};"
done
读写IO高
场景和cpu负载高的处理方式一样，读写IO高最终还是导致CPU负载高。这里不做暂开。
磁盘突增
日志文件
binary logs
查看binlog，有条件登录主机可以在文件系统中查看
show binary logs；
清除到指定binllog
purge binarylog to 'xxx';
设置日志过期时间
expire_logs_days
relay logs
一般出现在从库延迟情况下，日志无法及时应用，导致日志堆积。
通过show slave status\G 查看从库延迟情况
通过show relay log events in 'xxxx' from xxx limti 10;查看具体卡在哪个sql
一般是没有主键的情况引起的。
数据文件
主要写入数据量太大，通过以下语句查看大表情况：
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)' ,CREATE_OPTIONS
from information_schema.tables order by data_length desc, index_length limit 10 desc;
tmp文件
一般是sql执行语句造成的，通过show processlist查看，重点注意一下几种语句：
大型 JOIN 操作
当 JOIN 操作涉及到大量数据时，MySQL 可能需要创建临时文件来存储中间结果。为了避免这种情况，可以优化查询，尽量减少 JOIN 操作中使用的数据量。
排序操作（ORDER BY）
对大量数据进行排序时，MySQL 可能会使用临时文件来存储排序结果。你可以考虑在查询时使用 LIMIT 子句来减少返回的数据量，或者尝试优化索引以提高排序性能。
分组操作（GROUP BY）
对大量数据进行分组时，MySQL 可能需要创建临时文件来存储分组结果。优化索引和查询语句可以提高 GROUP BY 操作的性能。
聚合函数（如 SUM、COUNT、AVG 等）
当使用聚合函数处理大量数据时，MySQL 可能需要在磁盘上创建临时文件。优化查询语句和索引以减少处理的数据量可以帮助提高性能。
子查询和派生表
使用子查询和派生表时，MySQL 可能需要创建临时文件来存储中间结果。考虑使用 JOIN 操作或其他方式重写查询以提高性能。
UNION 操作
对于 UNION 查询，MySQL 可能需要创建临时文件来存储每个子查询的结果。优化 UNION 查询中的每个子查询可以减少临时文件的使用。
为了避免或减少大型临时文件的生成，你可以尝试以下优化方法：
解决办法：和业务沟通之后kill对应的sql语句。
锁问题分析
行锁
确定当前锁定的事务和等待锁定的事务：
SELECT * FROM information_schema.innodb_trx;
这个查询会展示当前正在进行的事务的信息，包括事务ID、状态、开始时间等。如果某个事务被阻塞，您将能够从这里找到相关信息。
确定哪些行被锁定：
SELECT * FROM information_schema.innodb_locked_rows;
这个查询将返回被锁定的行信息，包括表名、索引名、锁类型等。这有助于找出具体哪些行导致了锁问题。
查看正在等待的锁请求：
SELECT * FROM information_schema.innodb_lock_waits;
这个查询会列出所有正在等待锁的请求，包括请求的事务ID、被锁定对象的表名、索引名等。这可以帮助您了解哪些请求正在等待锁定资源。
结合上述信息，找出锁竞争情况：
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
此查询可以帮助您找出哪个事务在等待锁，以及哪个事务持有锁。您可以通过比较等待和持有锁的事务来找出潜在的死锁或性能瓶颈。
根据需要，优化或修改查询，以减轻锁竞争。这可能包括改变隔离级别、修改索引或重构查询。
KILL TRANSACTION trx_id;
或
KILL QUERY connection_id;
MDL锁
查看当前的元数据锁情况：
SELECT * FROM performance_schema.metadata_locks;
查看锁的竞争关系
SELECT
PROCESSLIST_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
OBJECT_TYPE,
LOCK_TYPE,
LOCK_DURATION,
LOCK_STATUS,
LOCK_DATA,
PROCESSLIST_USER,
PROCESSLIST_HOST,
PROCESSLIST_DB,
PROCESSLIST_COMMAND,
PROCESSLIST_TIME,
PROCESSLIST_STATE,
PROCESSLIST_INFO
FROM
performance_schema.metadata_locks
INNER JOIN performance_schema.threads
ON performance_schema.metadata_locks.OWNER_THREAD_ID = performance_schema.threads.THREAD_ID
INNER JOIN performance_schema.processlist
ON performance_schema.threads.PROCESSLIST_ID = performance_schema.processlist.ID;
解决方式
KILL connection_id;
减少长时间运行的事务。
减少同时锁定多个对象的操作。
在适当的地方使用锁提示（如LOW_PRIORITY，HIGH_PRIORITY）。
尽量避免在线DDL操作，如ALTER TABLE。
死锁
死锁是一个比较常见的现场，理论上不算是一种异常，通常死锁也不会引起比较严重的性能问题。对于死锁的分析也相对比价负载一点，核心原因现在的死锁日志只会记录一个事务中的部分执行sql，不能记录事务的所有sql执行步骤。
show engine innodb status\G
ACTIVE 74 sec 表示事务活动时间，starting index read 为事务当前正在运行的状态，可能的事务状态有：fetching rows，updating，deleting，inserting, starting index read 等状态。
同时mysql而言，锁都是加载索引上的，通过上面的信息判断锁是加载主键上。
解析binlog
通过上面的信息，我们基本分析不了具体是什么原因产生了死锁，具体的事务信息我们通过解析binlog来获取
mysqlbinlog -vv binlog.xxxx
通过分析分析两个事务执行逻辑找出发生死锁的具体语句和逻辑。
问题解决
通常来说，数据库大部分的问题是sql引起的，比如cpu负载高，如果是业务并发导致的，需要业务限流，如果无法限流，可以提升数据库配置。如果是慢sql引起的，可以针对新的优化对应的sql。磁盘突增的问题我们主要是要找到对应的引起磁盘突增的sql或者文件，针对的做sql优化或者数据清理。死锁问题主要是找到死锁触发的逻辑和事务，针对这种事务的并发做修改，比如缩短事务的处理流程，大事务切分成小事务执行。
