MYSQL性能分析之show profiles

梦里梦外; 2022-06-12 23:37 193阅读 0赞

# 打开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进程状态 ##

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

#### 什么情况下产生临时表? ####

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

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

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

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

1.  取出的列含有text/blob类型时 —内存表储存不了text/blob类型
2.  在group by 或distinct的列中存在>512字节的string列
3.  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 变量都会增加.

## 数据库优化 ##

1、建表: 表结构的拆分,如核心字段都用int,char,enum等定长结构  
非核心字段,或用到text,超长的varchar,拆出来单放一张表.

2、建索引: 合理的索引可以减少内部临时表(索引优化策略里详解)

3、写语句: 不合理的语句将导致大量数据传输以及内部临时表的使用.

#### 临时表相关配置 ####

tmp\_table\_size：指定系统创建的内存临时表最大大小;max\_heap\_table\_size: 指定用户创建的内存表的最大大小;注意：最终的系统创建的内存临时表大小是取上述两个配置值的最小值。

#### 表的设计原则 ####

使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有：

1、创建索引：在ORDER BY或者GROUP BY的列上创建索引;

2、分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。

#### SQL优化 ####

如果表的设计已经确定，修改比较困难，那么也可以通过优化SQL语句来减少临时表的大小，以提升SQL执行效率。

常见的优化SQL语句方法如下：

1、拆分SQL语句

临时表主要是用于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的SQL，以降低排序或分组时临时表的大小，提升排序和分组的效率，我们的案例就是采用这种方法。

2、优化业务，去掉排序分组等操作

有时候业务其实并不需要排序或分组，仅仅是为了好看或者阅读方便而进行了排序，例如数据导出、数据查询等操作，这种情况下去掉排序和分组对业务也没有多大影响。

如何判断使用了临时表？

使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表。  
（关于explain使用[http://blog.csdn.net/kwinh/article/details/55504806][http_blog.csdn.net_kwinh_article_details_55504806]）

[http_blog.csdn.net_kwinh_article_details_55504806]: http://blog.csdn.net/kwinh/article/details/55504806