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