MYSQL性能分析之show profiles

最新推荐文章于 2024-05-11 10:55:06 发布

kwinH

最新推荐文章于 2024-05-11 10:55:06 发布

阅读量712

点赞数

分类专栏： mysql 文章标签： mysql 性能

本文链接：https://blog.youkuaiyun.com/kwinH/article/details/73527629

版权

mysql 专栏收录该内容

20 篇文章

订阅专栏

打开profile分析语句

show variables like ‘%profiling%’;
#on开off关开启
set profiling=on;

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF    |
+---------------+-------+

mysql> set profiling=on;
Query OK, 0 rows affected (0.00 sec)


mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00073300 | SELECT DATABASE()               |
|        2 | 0.00734900 | select * from dict limit 1      |
+----------+------------+---------------------------------+
2 rows in set (0.00 sec)


mysql> show profile for query 2;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000052 |
| Opening tables     | 0.000009 |
| System lock        | 0.000003 |
| Table lock         | 0.000006 |
| init               | 0.000016 |
... 省略...
| freeing items      | 0.000029 |
| logging slow query | 0.000002 |
| cleaning up        | 0.000019 |
+--------------------+----------+
15 rows in set (0.00 sec)

值得注意的mysql进程状态

converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘
create tmp table 创建临时表(如group时储存中间结果)
Copying to tmp table on disk 把内存临时表复制到磁盘
locked 被其他查询锁住
logging slow query 记录慢查询

什么情况下产生临时表?

MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎;

如果group by 的列没有索引,必产生内部临时表
例如：explain select goods_id,cat_id from goods group by cat_id \G
如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表
例如：ORDERY BY price GROUP BY name;
distinct 与order by 一起使用可能会产生临时表
例如：explain select distinct cat_id from goods order by goods_id \G
union合并查询时会用到临时表
某些视图会用到临时表,如使用temptable方式建立,或使用union或聚合查询的视图
在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
distinct 和 order by 一起使用时
例如：ORDERY BY DISTINCT(price)
开启了 SQL_SMALL_RESULT 选项(SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可)

什么情况下临时表写到磁盘上?

在处理请求的某些场景中,服务器创建内部临时表. 即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上.
用户不能直接控制服务器内部用内存还是磁盘存储临时表

取出的列含有text/blob类型时 —内存表储存不了text/blob类型
在group by 或distinct的列中存在>512字节的string列
select 中含有>512字节的string列,同时又使用了union或union all语句

想确定查询是否需要临时表,可以用EXPLAIN查询计划,并查看Extra列,看是否有Using temporary.
如果使用SQL_SMALL_RESULT,MySQL会使用内存临时表,除非查询中有一些必须要把临时表建立在磁盘上.
如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表. 内存中临时表的最大值为tmp_table_size和max_heap_size中较小值.
这和create table时显示指定的内存表不一样:这些表只受max_heap_table_size系统参数影响.

当服务器创建内部临时表(无论在内存还是在磁盘),create_tmp_tables变量都会增加.
如果创建了在磁盘上内部临时表(无论是初始创建还是由in-memory转化),
create_tmp_disk_tables 变量都会增加.