性能优化

# 性能剖析

# SHOW PROFILE

默认关闭，使用时在会话级别开启 set profiling = 1; 然后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询状态变更的相关数据。通过show profiles;查看记录的数据：

+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00024300 | select * from buyer |
+----------+------------+---------------------+

1
2
3
4
5

show profile for query 1;查看查询的每个步骤和花费的时间：

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000050 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000016 |
| init                 | 0.000020 |
| System lock          | 0.000007 |
| optimizing           | 0.000003 |
| statistics           | 0.000011 |
| preparing            | 0.000010 |
| executing            | 0.000003 |
| Sending data         | 0.000054 |
| end                  | 0.000004 |
| query end            | 0.000005 |
| closing tables       | 0.000005 |
| freeing items        | 0.000039 |
| cleaning up          | 0.000011 |
+----------------------+----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

也可以直接查information_schema中对应的表，按需格式化输出

set @query_id =1;
select state,sum(duration) as total_r,
  round( 100*sum(duration)/ (select sum(duration) from information_schema.profiling where query_id = @query_id  ),2 ) as pct_r,
  count(*) as calls,
  sum(duration)/count(*) as 'r/call'
from information_schema.profiling
where query_id=@query_id
group by state
order by total_r desc;

1
2
3
4
5
6
7
8
9

# 查看执行中的SQL

show processlist;
--或者
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE db='your_database_name';

1
2
3

终止耗时的SQL

kill query [id];

编辑

上次更新: 2024/04/30, 08:49:24

← 索引 MySQL的锁→