mysql 瓶颈分析的数_MySql的语句分析和性能瓶颈分析：explain ，profile

最新推荐文章于 2025-03-21 17:34:35 发布

快手镜相

最新推荐文章于 2025-03-21 17:34:35 发布

阅读量243

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 瓶颈分析的数

本文链接：https://blog.youkuaiyun.com/weixin_29792607/article/details/113270636

本文介绍MySQL中使用EXPLAIN分析查询执行计划的方法，包括SELECT和UPDATE语句的性能瓶颈定位，并通过展示具体实例来说明如何利用MySQL Profile进行更深入的性能分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql的执行计划内容，使用explain来做分析

explain select * from mail_sms_weather limit 10;

+----+-------------+------------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------------+------------+------+---------------+------+---------+------+-------+----------+-------+

mysql> explain update mail_sms_weather set zonecode='010' where mobile ='13001076987';

+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

上面分别是select和update的explain语句分析

字段含义(主要的)：

select type：SIMPLE表示除去子查询和union之外的查询

type：：INDEX表示全索引扫描，ALL表示全表扫描，INDEX比ALL快一些

rows：搜索的行数

fileterd：对符合条件(where或者连接)的记录数的百分比做的悲观估算

MySql Profile 定位性能瓶颈

注意profile的内容来自于information_schema.profiling，使用如下

mysql> show profile;

Empty set, 1 warning (0.00 sec)

mysql> set profile=1;

ERROR 1193 (HY000): Unknown system variable 'profile'

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;

Empty set, 1 warning (0.00 sec)

mysql> select * from mail_sms_weather limit 1;

+-------------+----------+--------+

| mobile | zonecode | status |

+-------------+----------+--------+

| 13001002004 | 010 | 1 |

+-------------+----------+--------+

1 row in set (0.00 sec)

mysql> show profiles;

+----------+------------+----------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+----------------------------------------+

| 1 | 0.00063725 | select * from mail_sms_weather limit 1 |

+----------+------------+----------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> show profile cpu for query 1;

+--------------------------------+----------+----------+------------+

+--------------------------------+----------+----------+------------+

| starting | 0.000115 | 0.000000 | 0.000000 |

| Executing hook on transaction | 0.000019 | 0.000000 | 0.000000 |

| starting | 0.000023 | 0.000000 | 0.000000 |

| checking permissions | 0.000020 | 0.000000 | 0.000000 |

| Opening tables | 0.000067 | 0.000000 | 0.000000 |

| init | 0.000017 | 0.000000 | 0.000000 |

| System lock | 0.000024 | 0.000000 | 0.000000 |

| optimizing | 0.000014 | 0.000000 | 0.000000 |

| statistics | 0.000039 | 0.000000 | 0.000000 |

| preparing | 0.000037 | 0.000000 | 0.000000 |

| executing | 0.000082 | 0.000000 | 0.000000 |

| end | 0.000013 | 0.000000 | 0.000000 |

| query end | 0.000012 | 0.000000 | 0.000000 |

| waiting for handler commit | 0.000022 | 0.000000 | 0.000000 |

| closing tables | 0.000043 | 0.000000 | 0.000000 |

| freeing items | 0.000070 | 0.000000 | 0.000000 |

| cleaning up | 0.000024 | 0.000000 | 0.000000 |

+--------------------------------+----------+----------+------------+

show profile cpu for query 1; 这个可以查看cpu的性能瓶颈

当业务量上来后，优化语句查询非常重要，可以有效的节省服务器资源，但是也只是在必要的时候去优化，很多思想是在写语句的时候就注意到了