目录

• 运维实战
• 实战

运维

1. show processlist 能查看当前数据库连接的用户，状态耗时等
   1.1 state中"Sending data" 状态通常表示MySQL正在执行查询并向客户端发送结果数据，但还未完成发送。该状态通常出现在查询返回大量数据时，因为MySQL必须将所有结果数据从内存中取出并逐行发送到客户端，这可能需要一些时间
   1.2 如果CPU跑高并且有很多查询处于 "Sending data" 状态，可能是因为查询返回了大量数据，并且MySQL正在进行大量的I/O操作和网络操作，这可能会导致MySQL实例的负载升高。此外，如果查询所用的索引不足以支持请求的数据量，则可能需要在MySQL服务器上执行一些表扫描操作，从而导致更多的CPU使用率
   1.3 为了解决这个问题，可以尝试使用更具有选择性的索引来优化查询，从而减少所需的数据量。同时，也可以尝试使用分页等技术来限制每次查询所返回的结果数量，减少 "Sending data" 状态的持续时间。如果查询返回的结果需要进行计算或聚合操作，可以尝试使用更高效的算法或引入缓存来减少计算量
   1.4 还可以通过监控系统性能并进行性能优化来改善CPU使用率。可以使用工具如top、sar、perf等来监控系统性能，并使用慢查询日志和MySQL性能日志来定位瓶颈和优化查询
   1.5 有遇到过mysql低版本的query_cache_size query_cache_type设置不合理导致很多sending date状态，这边优化这个参数解决问题
2. SHOW ENGINE INNODB STATUS 能查看innodb状态，innodb有各种锁，有时候死锁了也能自己解开因为配置了超时时间; [mysqld] innodb_deadlock_detect=ON要开就是了
   SHOW VARIABLES LIKE 'innodb_print_all_deadlocks'; 查看是否打开死锁检查,MySQL死锁日志分析入门之如何看懂阅读日志这篇文章有解释具体的日志读法
3. mysiam的话SHOW OPEN TABLES WHERE In_use > 0;大于0的表示被锁
4. 机器查看内存剩余free -g显示的free也0也别紧张，如果cache比较大可能是被页面、缓存等占用
5. mysql给某个ip全部权限， 5.7多版本

CREATE USER 'root'@'具体ip支持%' IDENTIFIED BY '你的密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'具体ip支持%' WITH GRANT OPTION;

select host,user from mysql.user;

实战

根据表A有国家字段country和省份province，每个国家的省份最多取五条

• 使用派生表 + 变量的形式 mysql5.7版本，如果mysql8.0可以直接使用ROW_NUMBER()
• case when的场景解释

1. 如果当前country和province与上一个记录相同，排名保持不变
2. 如果当前country与上一个记录相同但province不同，排名加1
3. 如果当前country与上一个记录不同，排名重置为1

• 如果你不能升级MySQL 8.0版本，并且必须使用用户定义变量，你可以考虑使用表锁或行锁来确保在计算排名时没有其他查询能够修改相关的数据。在MySQL 5.7中，使用用户定义变量（如@rank, @partval, @rankval）来实现排名或累积计算，确实可能会在并发环境中引起问题，因为这些变量的值在会话之间是共享的，而不是每个查询或会话私有的。当多个查询或会话同时修改这些变量时，它们的值可能会变得不可预测，导致结果错误

SELECT country,
       province,
       province_en,
       ranking
from (SELECT target.*,
             @rank := CASE
                          WHEN @partval = country AND @rankval = province THEN @rank
                          WHEN @partval = country AND (@rankval := province) IS NOT NULL THEN @rank + 1
                          WHEN (@partval := country) IS NOT NULL AND (@rankval := province) IS NOT NULL
                              THEN 1 END AS ranking
      FROM (SELECT province, country, province_en
            FROM A
            where province != 'test'
            GROUP BY province
            ORDER BY province desc) target,
           (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x
      ORDER BY country) as D
WHERE D.ranking <= 5
order by country desc

参考文章

• MySQL死锁日志分析入门之如何看懂阅读日志