发布于 由admin

MySQL高阶讲座之：`MySQL`的`CPU`飙升：从`Show Processlist`到火焰图的诊断路径。

各位观众老爷，大家好！今天咱们来聊聊MySQL的CPU飙升，这可是个让人头疼的问题，轻则网站响应慢如蜗牛，重则直接宕机。别慌，咱们今天就来抽丝剥茧，一步一步地找到罪魁祸首，然后咔嚓一声，解决它！

一、打招呼：CPU飙升，谁干的？

MySQL服务器CPU飙升，就好比家里突然来了熊孩子，把东西搞得乱七八糟，你得先搞清楚是谁干的，才能对症下药。

二、第一步：Show Processlist，揪出“嫌疑犯”

Show Processlist，绝对是你的第一个好帮手。它能显示当前MySQL服务器上所有正在运行的线程信息，包括线程的状态、执行的SQL语句等等。

SHOW FULL PROCESSLIST;

执行这条命令后，你会得到一个类似这样的表格：

Id User Host db Command Time State Info
42 root localhost test Query 0 starting SHOW FULL PROCESSLIST
43 root localhost test Sleep 2 NULL
44 user 192.168.1.100 mydb Query 120 Sending data SELECT * FROM orders WHERE order_date > ‘2023-01-01’ ORDER BY order_date DESC;
45 user 192.168.1.101 mydb Query 60 Waiting for table lock UPDATE products SET stock = stock – 1 WHERE id = 123;
46 user 192.168.1.102 mydb Query 300 Locked DELETE FROM logs WHERE create_time < ‘2022-01-01’;
47 root localhost NULL Sleep 1800 NULL

别被这一堆信息吓到，咱们重点关注这几个字段：

  • Id: 线程ID，用于后续KILL掉线程。
  • User: 执行SQL的用户。
  • Host: 用户从哪个主机连接的。
  • db: 当前线程使用的数据库。
  • Command: 线程正在执行的命令类型，例如Query、Sleep、Connect等。
  • Time: 线程处于当前状态的时间，单位是秒。这个非常重要，看看谁占用了最长时间。
  • State: 线程的当前状态，这个状态能告诉你线程在干什么。
  • Info: 线程正在执行的SQL语句。

通过Show Processlist，你可以初步判断哪些线程可能导致CPU飙升：

  • 长时间处于Sending data状态的线程: 这通常表示MySQL正在向客户端发送大量数据，可能是因为查询没有使用索引或者数据量太大。
  • 长时间处于Waiting for table lock或者Locked状态的线程: 这表示线程正在等待获取表锁，可能是因为有其他线程正在执行更新操作，导致锁冲突。
  • Time值非常大的线程: 这表示线程已经运行了很长时间，可能是因为SQL语句执行效率低下，或者出现了死锁。
  • 大量Sleep线程: 虽然单个Sleep线程不会占用太多CPU，但是如果存在大量的Sleep线程，也可能导致资源浪费，影响服务器性能。

三、第二步：定位问题SQL，使用慢查询日志

Show Processlist只能看到当前正在运行的线程，如果CPU飙升是之前执行的SQL语句导致的，那怎么办呢？这时候，慢查询日志就派上用场了。

慢查询日志可以记录执行时间超过指定阈值的SQL语句。

1. 开启慢查询日志:

首先，你需要确认慢查询日志是否开启。

SHOW VARIABLES LIKE 'slow_query_log';

如果slow_query_log的值是OFF，你需要开启它。

SET GLOBAL slow_query_log = 'ON';

2. 设置慢查询时间阈值:

你需要设置一个合理的慢查询时间阈值，例如1秒。

SHOW VARIABLES LIKE 'long_query_time';

如果long_query_time的值不合适，你需要修改它。

SET GLOBAL long_query_time = 1;

3. 指定慢查询日志文件:

你需要指定慢查询日志文件的路径。

SHOW VARIABLES LIKE 'slow_query_log_file';

如果slow_query_log_file的值不合适，你需要修改它。

SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

4. 分析慢查询日志:

开启慢查询日志后，MySQL会将执行时间超过阈值的SQL语句记录到日志文件中。你可以使用mysqldumpslow工具来分析慢查询日志。

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

这个命令会按照执行时间（-s t）排序，显示前10条（-t 10）慢查询语句。

mysqldumpslow工具可以帮助你统计慢查询语句的出现次数、总执行时间、平均执行时间等信息，从而快速定位问题SQL。

四、第三步：Explain，SQL性能分析神器

找到了“嫌疑SQL”，接下来就要用EXPLAIN命令来分析它的执行计划，看看哪里出了问题。

EXPLAIN命令可以模拟MySQL优化器执行SQL语句的过程，并显示SQL语句的执行计划，包括使用的索引、扫描的行数、连接类型等等。

EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01' ORDER BY order_date DESC;

执行EXPLAIN命令后，你会得到一个类似这样的表格：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL ALL order_date NULL NULL NULL 10000 10.00 Using where; Using filesort

咱们来解读一下这些字段：

  • id: SELECT 查询的序列号。
  • select_type: SELECT 查询的类型，例如SIMPLE、PRIMARY、SUBQUERY等。
  • table: 查询的表名。
  • partitions: 匹配的分区。
  • type: 访问类型，这是最重要的一个字段，它表示MySQL是如何查找表中的行的。常见的类型有：
    • system: 表中只有一行记录。
    • const: 通过索引一次就能找到。
    • eq_ref: 使用唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。
    • ref: 使用非唯一索引扫描，返回匹配某个单独值的所有行。
    • range: 只检索给定范围的行，使用索引来选择行。
    • index: 全索引扫描，只扫描索引树。
    • ALL: 全表扫描，这是最差的类型，意味着MySQL需要扫描整个表才能找到匹配的行。
  • possible_keys: 可能使用的索引。
  • key: 实际使用的索引。
  • key_len: 索引的长度。
  • ref: 哪个列或常量被用于查找索引列中的值。
  • rows: MySQL估计需要扫描的行数。
  • filtered: 按照表条件过滤的行百分比。
  • Extra: 包含一些额外的信息，例如：
    • Using index: 使用了覆盖索引，这意味着MySQL可以直接从索引中获取数据，而不需要回表查询。
    • Using where: 使用了WHERE子句来过滤数据。
    • Using temporary: MySQL需要使用临时表来存储结果集，这通常发生在ORDER BY和GROUP BY子句中。
    • Using filesort: MySQL需要对结果集进行排序，这通常发生在ORDER BY子句中。

通过EXPLAIN命令，你可以发现SQL语句的性能瓶颈：

  • type是ALL: 意味着全表扫描，需要优化索引。
  • Extra包含Using temporary或Using filesort: 意味着需要优化ORDER BY或GROUP BY子句。
  • rows过大: 意味着需要扫描的行数太多，需要优化WHERE子句或索引。

五、第四步：对症下药，SQL优化技巧

找到了问题SQL，也分析了执行计划，接下来就是对症下药，优化SQL语句了。

1. 索引优化:

索引是提高查询效率的关键。

  • 创建合适的索引: 根据查询条件创建合适的索引。
  • 避免在WHERE子句中使用函数或表达式: 这会导致索引失效。
  • 使用覆盖索引: 尽量让查询直接从索引中获取数据，避免回表查询。
  • 定期维护索引: 定期重建或优化索引，可以提高查询效率。

2. SQL语句优化:

  • 避免使用SELECT *: 只选择需要的列，可以减少数据传输量。
  • 使用JOIN代替子查询: 在某些情况下，使用JOIN可以提高查询效率。
  • 优化WHERE子句: 尽量使用索引列进行查询，避免使用OR和NOT等操作符。
  • 分页查询优化: 对于分页查询，可以使用LIMIT和OFFSET来实现，但是当OFFSET过大时，效率会很低，可以考虑使用书签方式或延迟关联来优化。
  • 批量操作: 尽量使用批量插入、更新或删除操作，可以减少网络开销。

3. 数据库结构优化:

  • 选择合适的数据类型: 选择合适的数据类型可以减少存储空间和提高查询效率。
  • 拆分大表: 将大表拆分成多个小表，可以提高查询效率。
  • 使用分区表: 将表按照一定的规则分成多个分区，可以提高查询效率。

六、第五步：火焰图，性能分析的终极武器

如果上面的方法还不够，或者你想更深入地了解CPU飙升的原因，那么火焰图就是你的终极武器了。

火焰图是一种可视化性能分析工具，它可以展示CPU的调用栈，让你清楚地看到CPU的时间都花在了哪些函数上。

1. 安装 perf 工具:

首先，你需要安装perf工具，这是Linux系统自带的性能分析工具。

sudo apt-get install linux-perf

2. 采集数据:

使用perf工具采集CPU数据。

sudo perf record -F 99 -p $(pidof mysqld) -g -o perf.data
  • -F 99: 设置采样频率为99Hz，即每秒采样99次。
  • -p $(pidof mysqld): 指定要分析的进程ID，这里使用pidof mysqld来获取MySQL的进程ID。
  • -g: 记录调用栈信息。
  • -o perf.data: 将数据保存到perf.data文件中。

3. 生成火焰图:

使用FlameGraph工具将perf.data文件转换成火焰图。

首先，你需要下载FlameGraph工具。

git clone https://github.com/brendangregg/FlameGraph.git

然后，执行以下命令生成火焰图。

sudo perf script -i perf.data | ./FlameGraph/stackcollapse-perf.pl | ./FlameGraph/flamegraph.pl > flamegraph.svg

打开flamegraph.svg文件，你就可以看到火焰图了。

解读火焰图:

火焰图的横轴表示采样数，纵轴表示调用栈的深度。每个矩形表示一个函数，矩形的宽度表示该函数占用的CPU时间比例。

从火焰图上，你可以看到哪些函数占用了最多的CPU时间，从而找到性能瓶颈。

七、实战案例：一个慢查询引发的CPU飙升

咱们来模拟一个实际的案例，看看如何使用这些工具来解决CPU飙升问题。

假设我们的MySQL服务器CPU突然飙升，通过Show Processlist，我们发现有一个SQL语句执行时间很长：

SELECT * FROM users WHERE name LIKE '%张三%';

这个SQL语句的Time值非常大，而且State是Sending data。

接下来，我们使用EXPLAIN命令来分析这个SQL语句：

EXPLAIN SELECT * FROM users WHERE name LIKE '%张三%';

结果显示，type是ALL，possible_keys是name，key是NULL，Extra是Using where。

这意味着MySQL正在全表扫描users表，并且没有使用索引。

原因很明显，LIKE '%张三%'会导致索引失效，因为MySQL无法确定张三在字符串中的位置。

解决办法是：

  1. 避免使用LIKE '%张三%': 如果必须使用模糊查询，可以考虑使用全文索引或者第三方搜索引擎。
  2. 优化索引: 如果需要经常使用LIKE '张三%'进行查询，可以创建一个前缀索引。

八、总结：CPU飙升，不再是难题

今天咱们学习了MySQL CPU飙升的诊断路径，从Show Processlist到慢查询日志，再到EXPLAIN命令和火焰图，一步一步地找到了问题所在，并给出了相应的优化建议。

记住，解决CPU飙升问题需要耐心和技巧，希望今天的分享能帮助你在遇到类似问题时不再束手无策，能够快速定位问题并解决它。 祝大家工作顺利，bug退散！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注