mysql性能分析--explain详解和profiling开销（资源分配）

explain

id：select标识符。select查询序列号，即sql语句执行的顺序。当有子查询时，id就会出现不一致，如子查询sql的id是1，外查询sql的id是2.
selecttype：select类型。
simple，标示简单的select，没有union和子查询
primary，最外面的select，在有子查询的语句中，最外面的select查询就是primary
subquery，子查询sql
union，在union语句的第二个或者说是后面那一个。
dependent union，union中的第二个或后面的select语句，取决于外面的查询
union result，union的结果。
EXPLAIN SELECT * FROM table_name t1 WHERE t1.primary_key =  (SELECT column_name FROM table_name t2 WHERE t2.primary_key = (SELECT 10 FROM table_name t3 LIMIT 1));
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | t1    | const | PRIMARY       | PRIMARY | 8       | const |    1 |             |
|  2 | SUBQUERY    | t2    | const | PRIMARY       | PRIMARY | 8       |       |    1 |             |
|  3 | SUBQUERY    | t3    | index | NULL          | PRIMARY | 8       | NULL  |  853 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
table：表名，显示别名，没有别名的显示表名本身。
type：连接类型。
system，表只有一行，这是const类型的特例，可以忽略不计。
const，方案最好最优化的一个类型。必须是用到了pk或unique且只匹配一条记录。const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。
EXPLAIN SELECT * FROM table_name WHERE pk BETWEEN 3 AND 3;--const
EXPLAIN SELECT * FROM table_name WHERE pk BETWEEN 3 AND 4;--range
eq_ref，对于每个来自于前面的表的行组合，从该表中读取一行。这可能是除const外的最好的链接类型。它用在一个索引的所有部分被连接使用并且索引是unique或pk。
如下，使用了t2的主键来关联t1，所以t2表使用了eq_ref。
EXPLAIN SELECT * FROM  t1 LEFT JOIN t2  ON t1.column_name = t2.pk;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                       | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL                      | 2644 |       |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | t1.column_name |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
ref，如果连接只使用健的最左边的前缀，或如果健不是unique或primary key（即连接不能基于关键字选择单个行的话），则使用ref
ref_or_null，该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge，该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素
unique_subquery
index_subquery
range，给定范围内的检索，使用一个索引来检查行。看下面两条语句
explain select * from uchome_space where uid in (1,2)
explain select * from uchome_space where groupid in (1,2)
uid有索引，groupid没有索引，结果是第一条语句的联接类型是range,第二个是ALL
index， 该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。
ALL，对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
possible_keys：提示使用哪个索引会在该表中找到行
key：mysql使用的索引。显示MySQL实际决定使用的健，如果没有索引被选择，健是NULL。
key_len：mysql使用的索引长度。如果健是NULL，长度就是NULL。
ref：ref列显示使用哪个列或常数与key一起从表中选择行
rows：显示mysql执行查询的行数，数值越大越不好，说明没有用好索引。（InnoDB中不是很准确）
extra：mysql解决查询的详细信息。（如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。）
distinct
not exists
rang checked for each record，没有找到合适的索引
using filesort
using index，值使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。说明使用了索引
using temporary，出现using temporary就说明语句需要优化了。
EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.id ORDER BY ads.id desc
id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                          
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  -------------------------------
    1  SIMPLE       city   ref     ads_id,city_id  city_id  4        const                   2838    100.00 Using temporary; Using filesort
    1  SIMPLE       ads     eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where   
这条语句会使用using temporary,而下面这条语句则不会
EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.id ORDER BYcity.ads_id desc
id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                      
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  ---------------------------
    1  SIMPLE       city   ref     ads_id,city_id  city_id  4        const                   2838    100.00 Using where; Using filesort
    1  SIMPLE       ads    eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where   
这是为什么呢？他俩之间只是一个order by不同，MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）以上两个查询语句，驱动表都是 city，如上面的执行计划所示！
对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）
因此，order by ads.id desc 时，就要先 using temporary 了！
驱动表的定义
wwh999 在 2006年总结说，当进行多表连接查询时， [驱动表] 的定义为：
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
2）未指定联接条件时，行数少的表为[驱动表]（Important!）。
永远用小结果集驱动大结果集
今天学到了一个很重要的一点：当不确定是用哪种类型的join时，让mysql优化器自动去判断，我们只需写select * from t1,t2 where t1.field = t2.field
using where
Using sort_union(...), Using union(...),Using intersect(...)

Using index for group-by，类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

转自：http://blog.csdn.net/zhuxineli/article/details/14455029

profiling执行开销

查看配置：

SELECT @@PROFILING;   

+-------------+
| @@PROFILING |
+-------------+
|           0 |
+-------------+

SHOW VARIABLES LIKE '%profiling%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |  是否支持profiling
| profiling              | OFF   |是否开启了profiling
| profiling_history_size | 15    |保留最近执行的记录数。0-100，默认15，0是禁用。
+------------------------+-------+

开启及使用：

1）SET PROFILING = 1;

2）执行sql，如select * from table_name where ...

3）SHOW PROFILES；

+----------+------------+-----------------------------------------+  
| query_id | duration   | query                                   |  
+----------+------------+-----------------------------------------+  
|        1 | 0.00705950 | show variables like '%profiling%'       |  
|        2 | 0.00127400 | select * from ...               |   
+----------+------------+-----------------------------------------+ 

4）SHOW PROFILE;显示最后一个执行记录的开销明细

5）SHOW PROFILE CPU,BLOCK IO;显示执行时间，和cpu、block io的相亲

6）SET PROFILING=0;