mysql如何explan优化sql_mysql explain 及 profile，优化SQL

在mysql查询性能分析中最常用的就是explain了，profile查看一些具体的性能也是不错的

1. profile

我们可以先使用

SELECT @@profiling;

来查看是否已经启用profile，如果profilng值为0，可以通过

SET profiling = 1;

来启用。启用profiling之后，我们执行一条查询语句，比如：

select count(*) from roi_summary;

然后show profiles查看如下：

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

| Query_ID | Duration | Query |

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

| 1 | 0.00021500 | select @@profiling |

| 2 | 0.05522700 | select count(*) from roi_summary |

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

2 rows in set (0.00 sec)

其中ID为5的语句是刚执行的查询语句，这时候我们执行show profile for query 2来查看这条语句的执行过程如下;

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

| Status | Duration |

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

| starting | 0.000021 |

| checking query cache for query | 0.000045 |

| checking permissions | 0.000007 |

| Opening tables | 0.000011 |

| System lock | 0.000004 |

| Table lock | 0.000040 |

| init | 0.000012 |

| optimizing | 0.000005 |

| statistics | 0.000010 |

| preparing | 0.000010 |

| executing | 0.000005 |

| Sending data | 0.055021 |

| end | 0.000007 |

| end | 0.000004 |

| query end | 0.000003 |

| storing result in query cache | 0.000004 |

| freeing items | 0.000008 |

| closing tables | 0.000005 |

| logging slow query | 0.000002 |

| cleaning up | 0.000003 |

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

20 rows in set (0.00 sec)

可以看出此条查询语句的执行过程及执行时间，总的时间约为0.05s。

这时候我们再执行一次

select count(*) from roi_summary;

show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.00021500 | select @@profiling |

| 2 | 0.05522700 | select count(*) from roi_summary |

| 3 | 0.00006000 | select count(*) from roi_summary |

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

然后执行show profile for query 3来查看本条语句的执行过程

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

| Status | Duration |

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

| starting | 0.000016 |

| checking query cache for query | 0.000007 |

| checking privileges on cached | 0.000004 |

| checking permissions | 0.000005 |

| sending cached result to clien | 0.000022 |

| logging slow query | 0.000003 |

| cleaning up | 0.000003 |

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

可以看出此次第二次查询因为前一次的查询生成了cache，所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取，结果查询时间比第一次快了N倍。