MySQL 性能定位
一、SQL性能分析
1.SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT 、 UPDATE 、 DELETE 、 SELECT 的访问频次：
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
Com_delete: 删除次数 Com_insert: 插入次数
Com_select: 查询次数 Com_update: 更新次数
我们可以在当前数据库再执行几次查询操作，然后再次查看执行频次，看看 Com_select 参数会不会变化。
通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了
那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 我们可以借助于慢查询日志。
2.慢查询日志
慢查询日志记录了所有执行时间超过指定参数（ long_query_time ，单位：秒，默认 10 秒）的所有SQL 语句的日志。
MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log 。
3.profile详情
show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling参数，能够看到当前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling ;
开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_user;
执行一系列的业务 SQL 的操作，然后通过如下指令查看指令的执行耗时：
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看指定SQL各个阶段的耗时情况 :
show profile for query 97;
4.explain分析
EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
来源：https://blog.51cto.com/u_15011668/7734846
二、正在执行的线程查看 show processlist; +----+------+--------------------+-----------+---------+------+-------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+--------------------+-----------+---------+------+-------------+------------------+ | 36 | root | 172.16.100.19:7954 | tpcc_test | Sleep | 456 | | NULL | | 37 | root | 172.16.100.19:7969 | tpcc_test | Sleep | 456 | | NULL | | 42 | root | localhost | NULL | Query | 0 | System lock | show processlist | | 43 | root | 10.0.102.204:49224 | employees | Sleep | 12 | | NULL | +----+------+--------------------+-----------+---------+------+-------------+------------------+ 4 rows in set (0.00 sec) ID：连接标识。这个值和INFORMATION_SCHEMA.PROCESSLIST表的ID列，以及PERFORMANCE_SCHEMA中的threads中的process_id值是相同的。 time： 线程已经在当前状态的时间。 kill线程 与MySQL服务器每个链接都在一个单独的线程中运行。可以使用如下语句杀死一个线程。 kill [connection| query] processlist_id connection: 与kill processlist_id相同；中断连接正在执行的任何语句之后，中断连接。 query： 中断连接正在执行的语句，但是保持本身的连接。 注意： 这里需要注意的就是如果出现大量的sleep进程的话，并且时间很长的话，这种都是链接的客户端在使用完链接没有close造成的。这里是需要调整的属性 wait_timeout 就是 sleep 连接最大存活时间，默认是 28800 s，换算成小时就是 8 小时，
相当于今天上班以来所有建立过而未关闭的连接都不会被清理。 这里说明一下，下面的时间单位是秒。 执行命令： show global variables like '%wait_timeout'; set global wait_timeout=250;
实用小sql(20221009)
-- 按照客户端IP分组，看哪个客户端的连接数最多 select client_ip, count(client_ip) as client_num from (select substring_index(host, ':', 1) as client_ip from `information_schema`.processlist) as connect_info group by client_ip order by client_num desc; -- 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程 select * from `information_schema`.processlist where Command != 'Sleep' order by Time desc; -- 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 select concat('kill ', id, ';') from `information_schema`.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
三、查看最大链接数 show variables like '%max_connection%'; 查看最大连接数 set global max_connections=1000; 重新设置最大连接数 mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 32 | | Threads_connected | 10 | | Threads_created | 50 | | Threads_rejected | 0 | | Threads_running | 1 | +-------------------+-------+ 5 rows in set (0.00 sec) Threads_connected ：这个数值指的是打开的连接数. Threads_running ：这个数值指的是激活的连接数，这个数值一般远低于connected数值. Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数
四、事务相关 # 查看正在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; # 查看等待锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
Lock wait timeout exceeded：后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常。
Dead Lock：两个事务互相等待对方释放相同资源的锁，从而造成的死循环，就会引发这个异常。
还有一个要注意的是innodb_lock_wait_timeout与lock_wait_timeout也是不一样的。
innodb_lock_wait_timeout：innodb的dml操作的行级锁的等待时间
lock_wait_timeout：数据结构ddl操作的锁的等待时间
那么如何查看innodb_lock_wait_timeout的具体值：
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'
ps. 注意global的修改对当前线程是不生效的，只有建立新的连接才生效
五、日志刷新导致IO过高 可能存在MySQL在日志在每次事务提交时，都会将其写入并刷新到磁盘，造成磁盘IO的高占用。如果所在磁盘是机械磁盘的话，可能io会更高。 通过在MySQL命令行运行以下命令： show variables like 'sync_binlog'; 可以看到：sync_binlog 的值为1。 该值意味着：启用在提交事务之前将二进制日志同步到磁盘。这是最安全的设置，但是会造成磁盘的较高占用。 show variables like 'innodb_flush_log_at_trx_commit'; 可以看到：innodb_flush_log_at_trx_commit 的值为1。 该值意味着：日志会在每次事务提交时写入并刷新到磁盘。
对于设置 0 和 2，不能 100% 保证每秒一次刷新。
注意：这种解决办法是在牺牲数据库安全的前提下，提高磁盘的性能！！！更改配置可能会带来更高的数据丢失风险！！！
六、临时表创建过多导致IO过高 mysql> show global status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 3 | | Created_tmp_files | 22 | | Created_tmp_tables | 8 | +-------------------------+-------+ ————————————————
多执行几次，如果发现tmp_files和tmp_disk_tables的值在增长，证明在大量的创建临时文件及磁盘临时表，则会引起磁盘IO过高。
常见的情况会导致频繁建立临时表
1. UNION查询;
2. insert into select ...from ...
3. ORDER BY和GROUP BY的子句不一样时;
4.数据表中包含blob/text列
其他更多创建临时表过多的情况可参考官方文档：
七、有频繁的全表扫描的sql导致IO过高 查看sql的全表扫描次数： show global status like '%Select_scan%'; 频繁的全表扫描也会引起数据库的io过高。
八、大事务写Binlog导致实例I/O高 现象 事务只有在提交时才会写Binlog文件，如果存在大事务，例如一条Delete语句删除大量的行，可能会产生几十GB的Binlog文件，Binlog文件刷新到磁盘时，会造成很高的I/O吞吐。 解决方案 建议尽量将事务拆分，避免大事务和降低刷新磁盘频率。
九、DDL语句导致实例I/O高
现象
DDL语句可能会重建表空间，期间会扫描全表数据、创建索引排序、刷新新表产生的脏页，这些都会导致大量的I/O吞吐。另外一种场景是删除大表造成的I/O抖动。
十、 MySQL活跃线程数高 活跃线程数或活跃连接数是衡量MySQL负载状态的关键指标，通常来说一个比较健康的实例活跃连接数应该低于10，高规格和高QPS的实例活跃连接数可能20、30，如果出现几百、上千的活跃连接数，说明出现了SQL堆积和响应变慢，严重时会导致实例停止响应，无法继续处理SQL请求。 mysql> show global status like 'Thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 31 | | Threads_connected | 239 | | Threads_created | 2914 | | Threads_running | 4 | +-------------------+-------+ 排查慢SQL堆积问题 首先通过show processlist;命令查看是否有慢SQL。如果有很多扫描行数太多的SQL，容易导致活跃连接数升高。 排查表缓存（Table Cache）问题 现象 Table Cache不足时，会导致大量SQL处于Opening table状态，在QPS过高或者表很多的场景容易出现。 解决方案 将参数table_open_cache（不需要重启实例）和table_open_cache_instances（需要重启实例）调大。 排查行锁冲突问题 现象 行锁冲突表现为Innodb_row_lock_waits和Innodb_row_lock_time监控项的指标升高。 解决方案 可以通过show engine innodb status;命令查看是否有大量会话处于Lock wait状态，如果有，说明行锁冲突比较严重，需要通过优化热点更新、降低事务大小、及时提交事务等方法避免行锁冲突。
https://blog.csdn.net/xiangzaixiansheng/article/details/127049454
