`MySQL`性能分析

`show profiles`

使用show profiles分析sql性能，详细用法请参考链接

`explain`用法

执行计划各个字段含义：

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下执行；id不同，值越大，越先执行）。
select_type：https://www.cnblogs.com/joimages/p/14521966.html，表示select的类型，常见的取值有SIMPLE（查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型，无论是单表查询还是联合查询这些查询的级别都是 simple。顾名思义，这些查询都被 MySQL 认为是比较简单的查询模式。）、PRIMARY（对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY）、UNION（包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的嘛。除了第一个是 PRIMARY，其他的都是 UNION）、UNION RESULT（如果 MySQL 中的 UNION 需要用到临时表进行去重的话，那么这个小查询的级别就是 UNION RESULT）、SUBQUERY（如果我们的子查询不能转换对应 semi-join的形式，而且这个查询不是相关子查询的话，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，这个时候该子查询的第一个 SELECT 的级别就是 SUBQUERY）
type：表示连接类型，性能有好到差的连接类型为NULL、system、const、eq_ref、ref、range、index（用了索引但是需要对整个索引遍历扫描）、all。
possible_key：表示可能应用在这个查询上的索引，一个或者多个。
key：表示查询中实际用到的索引，NULL为没有用到任何索引。
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用的长度，在不损失精确性的前提下，长度越短越好。
rows：MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占总读取行数的百分比，filtered的值越大越好。

创建测试数据库

sql

CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

use testdb;

create table if not exists course(
  id bigint primary key auto_increment,
  name varchar(64) not null
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;

create table if not exists student(
  id bigint primary key auto_increment,
  name varchar(64) not null,
  `no` varchar(64) not null
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;

create table if not exists student_course(
  id bigint primary key auto_increment,
  studentid bigint not null,
  courseid bigint not null,
  constraint fk_studentCourse_studentId foreign key (studentid) references student(id),
  constraint fk_studentCourse_courseid foreign key (courseid) references course(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;

insert into course values
(1,'Java'),
(2,'PHP'),
(3,'MySQL'),
(4,'Hadoop');

insert into student values
(1,'黛倚丝','2000100101'),
(2,'谢逊','2000100102'),
(3,'昕天正','2000100103'),
(4,'韦一笑','2000100104');

insert into student_course values
(1,1,1),
(2,1,2),
(3,1,3),
(4,2,2),
(5,2,3),
(6,3,4);

测试explain

shell

# 执行计划id相同，执行顺序从上到下执行
# id都等于1,先执行student，再执行student_course，在执行course
mysql> explain select s.*,c.* from student s,student_course sc,course c where s.id=sc.studentid and c.id=sc.courseid\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
   partitions: NULL
         type: ALL
possible_keys: fk_studentCourse_studentId,fk_studentCourse_courseid
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: testdb.sc.courseid
         rows: 1
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

# 执行计划id不同，值越大，越先执行
# 先执行course子查询，在执行student查询，最后执行stuent_course查询
mysql> explain select s.* from student s where s.id in(select sc.studentid from student_course sc where sc.courseid=(select c.id from course c where c.name='MySQL'))\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <subquery2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: s
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: <subquery2>.studentid
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: MATERIALIZED
        table: sc
   partitions: NULL
         type: ref
possible_keys: fk_studentCourse_studentId,fk_studentCourse_courseid
          key: fk_studentCourse_courseid
      key_len: 8
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 3
  select_type: SUBQUERY
        table: c
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
4 rows in set, 1 warning (0.00 sec)

导致`SQL`慢有哪些因素呢？

todo 做实验证明并解决问题

SQL查询慢的原因可能涉及多个方面，以下是一些常见的原因：

1. 索引问题

没有索引或未使用索引：这是查询慢最常见的问题之一。如果查询的列上没有索引，数据库将不得不进行全表扫描，这会显著增加查询时间。
索引失效：即使存在索引，如果查询条件或查询方式导致索引无法被有效利用（如使用了函数或计算），索引也会失效，从而影响查询性能。

2. 硬件资源限制

I/O吞吐量小：磁盘I/O性能瓶颈会限制数据的读取速度，从而影响查询性能。
内存不足：如果数据库服务器的内存不足以缓存足够的数据和索引，将导致频繁的磁盘I/O操作，从而降低查询速度。
网络速度慢：如果数据库服务器与应用服务器之间的网络连接速度较慢，数据传输将受到影响，导致查询响应变慢。

3. 查询语句问题

查询语句未优化：复杂的查询语句、不恰当的连接条件、不必要的子查询等都可能降低查询性能。
查询出的数据量过大：如果查询返回的数据量非常大，将增加数据传输和处理的时间。

4. 并发和锁竞争

锁或死锁：在高并发的环境中，多个查询或事务可能相互等待对方释放锁资源，从而导致锁竞争或死锁，进而影响查询性能。
读写竞争资源：如sp_lock、sp_who等活动用户查看工具显示的信息可能表明存在读写竞争资源的情况。

5. 数据库设计和配置问题

数据库设计不合理：如数据冗余、数据类型选择不当等都可能影响查询性能。
配置不当：如数据库服务器的自动增长设置不当、缓存大小配置不合理等都可能影响数据库的性能。

6. 其他因素

没有创建计算列：这可能导致查询不优化，因为数据库无法利用计算列上的索引。
使用了游标：游标在处理大量数据时可能会非常慢，因为它需要逐行处理数据。

解决方案

针对上述原因，可以采取以下一些解决方案来优化SQL查询性能：

优化索引：确保查询的列上有适当的索引，并避免索引失效的情况。
优化查询语句：简化查询语句、使用连接代替子查询、限制返回的数据量等。
提升硬件资源：增加内存、使用更快的磁盘（如SSD）等。
调整数据库配置：如设置合理的自动增长策略、调整缓存大小等。
优化数据库设计：避免数据冗余、选择合适的数据类型等。
使用数据库性能分析工具：如Profiler、EXPLAIN等来分析查询语句的执行计划和性能瓶颈。

综上所述，SQL查询慢的原因多种多样，需要结合具体的查询语句、数据库设计、硬件配置和并发情况等因素进行综合分析和优化。

`CPU`使用率高如何排查和解决

todo 完善
todo 怎么通过events_statements_summary_by_digest知道sql是否io慢、内存不足呢？

排查方法如下：

查看系统相关负载，是否io慢等
使用show full processlist命令查看正在执行的SQL，找出Time列时间（秒数）比较长的SQL
查看performance_schema.events_statements_summary_by_digest分析哪些SQL比较繁忙
分析慢日志后，使用explain命令分析慢SQL

模拟实验：

运行 demo-mybatis-plus-assistant 项目，协助模拟分析MySQL cpu使用率高情况

bash

# 编译项目
mvn package

# 启动数据库服务
docker compose up -d

# 初始化测试数据
# 运行junit测试DatumPreparationTests初始化数据

# 运行junit测试ApplicationTests.test测试模拟cpu使用率高

使用show full processlist命令查看正在执行的SQL，观察Time比较长的SQL
bash
```
show full processlist\G;
```

查看performance_schema.events_statements_summary_by_digest表分析找到对应有可以问题的SQL

bash

select * from performance_schema.events_statements_summary_by_digest\G;

# 使用explain命令分析sql是否存在问题

从慢日志找出慢SQL后，使用explain命令分析慢SQL

todo 解决cpu使用率高：

备份

https://stackoverflow.com/questions/45584065/cannot-find-oltp-test-on-sysbench

# MySQL性能测试
# 准备测试数据
sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 \
  --mysql-host=192.168.1.193 --mysql-port=50000 --mysql-db=demo_db --range_size=100 \
  --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare

# 运行测试
sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 \
  --mysql-host=192.168.1.193 --mysql-port=50000 --mysql-db=demo_db --range_size=100 \
  --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run
  
# 清理测试数据 
sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 \
  --mysql-host=192.168.1.193 --mysql-port=50000 --mysql-db=demo_db --range_size=100 \
  --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua cleanup

MySQL性能分析 ​

show profiles ​

explain用法 ​

导致SQL慢有哪些因素呢？ ​

1. 索引问题 ​

2. 硬件资源限制 ​

3. 查询语句问题 ​

4. 并发和锁竞争 ​

5. 数据库设计和配置问题 ​

6. 其他因素 ​

解决方案 ​

CPU使用率高如何排查和解决 ​