You are here
mysql explain 分析查询 分析解析过程 有大用 有大大用
下面的全要看下 搜索本站 mysql 栏目下的 "性能" 或 "explain"
http://www.cnblogs.com/xuanzhi201111/p/4175635.html
http://blog.csdn.net/zhuxineli/article/details/14455029
http://database.51cto.com/art/200912/168453.htm
http://blog.csdn.net/u012721013/article/details/54965844
http://database.51cto.com/art/201707/546296.htm
http://blog.jobbole.com/103058/
http://shipingzhong.cn/node-admin/11335
https://zhidao.baidu.com/question/1821638433481084988.html
https://www.cnblogs.com/xcxc/p/3629930.html
http://blog.csdn.net/gmgmyxx/article/details/54571828
http://www.manongjc.com/article/927.html
https://www.cnblogs.com/xcxc/p/3629930.html
https://www.cnblogs.com/amyStart/p/5965472.html
http://zhengyun-ustc.iteye.com/blog/1942797
https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html
https://www.2cto.com/database/201309/244415.html
http://blog.csdn.net/u013065023/article/details/54964275
https://segmentfault.com/q/1010000007986568?_ea=1513262
https://huoding.com/2013/06/04/261
https://www.cnblogs.com/shengdimaya/p/7123069.html
http://blog.csdn.net/jbfsdzpp/article/details/48414235
http://www.jb51.net/article/66999.htm
https://www.cnblogs.com/JohnABC/p/7150921.html
https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html
https://zhidao.baidu.com/question/1821638433481084988.html
http://blog.csdn.net/doris_crazy/article/details/51775446
https://www.cnblogs.com/BeginMan/p/3754322.html
http://blog.csdn.net/u013065023/article/details/54964275
在建有索引的字段上尽量不要使用函数操作 这一点要切记
例如：在一个Date类型的字段上使用year（）函数时，将会使索引不能发挥作用，下面两个语句后者要比前者快。
select * from order where year(orderDate)<2001;
select * from order where orderDate <"2001-01-01";
like “%query”不使用B-Tree索引，但like “query%”会使用B-Tree索引。 最好不好使用 like
②数据类型出现隐式转换的时候也不会使用索引。尤其当列类型是字符串时，一定记得在where条件中把字符串常量值用引号引起来，比如where last_name = ‘1’;
分类：
版权声明：本文为博主原创文章，未经博主允许不得转载。
写在前面
1、不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程
2、不要求每个人一定知道线上（现在或未来）哪张表数据量大，哪张表数据量小
3、但把mysql客户端（如SQLyog，如HeidiSQL）放在桌面上，时不时拿出来 explain 一把，这是一种美德！
在实例讲解之前，我们先回顾一下联表查询的基础知识
联表查询的基础知识
引子：为什么第一个查询using temporary，第二个查询不用临时表呢？
下面两个查询，它们只差了一个order by，效果却迥然不同。
第一个查询：
EXPLAIN extended
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
第二个查询：
EXPLAIN extended
SELECT ads.id FROM ads,city
WHERE
city.city_id =8005
AND ads.status = 'online'
AND city.ads_id=ads.id
ORDER BY city.ads_id desc
执行计划里没有了using temporary：
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
为什么？DBA告诉我们：
MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）
以上两个查询语句，驱动表都是 city，如上面的执行计划所示！
对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）
因此，order by ads.id desc 时，就要先 using temporary 了！
驱动表的定义
wwh999 在 2006年总结说，当进行多表连接查询时， [驱动表] 的定义为：
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
2）未指定联接条件时，行数少的表为[驱动表]（Important!）
忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断
既然“未指定联接条件时，行数少的表为[驱动表]”了，而且你也对自己写出的复杂的 Nested Loop Join 不太有把握（如下面的实例所示），就别指定谁 left/right join 谁了，请交给 MySQL优化器 运行时决定吧。
如果您对自己特别有信心，可以像火丁一样做优化。
小结果集驱动大结果集
de.cel 在2012年总结说，不管是你，还是 MySQL，优化的目标是尽可能减少JOIN中Nested Loop的循环次数。
以此保证：永远用小结果集驱动大结果集（Important!）！
实例讲解
Nested Loop Join慢查SQL语句
先了解一下 mb 表有 千万级记录，mbei 表要少得多。慢查实例如下：
explain
SELECT mb.id, ……
FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid
WHERE 1=1
ORDER BY mbei.apply_time DESC
limit 0,10
够复杂吧。Nested Loop Join 就是这样，以驱动表的结果集作为循环的基础数据，然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据，最后合并结果。
此时还有第三个表，则将前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此反复。
这条语句的执行计划如下：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE mb index userid userid 4 (NULL) 6060455 Using index; Using temporary; Using filesort
1 SIMPLE mbei eq_ref mb_id mb_id 4 mb.id 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 mb.uid 1 Using index
由于动用了“LEFT JOIN”，所以攻城狮已经指定了驱动表，虽然这张驱动表的结果集记录数达到百万级！
如何优化？
优化第一步：LEFT JOIN改为JOIN，干嘛要 left join 啊？直接 join！
explain
SELECT mb.id……
FROM mb JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid
WHERE 1=1
ORDER BY mbei.apply_time DESC
limit 0,10
立竿见影，驱动表立刻变为小表 mbei 了， Using temporary 消失了，影响行数少多了：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE mbei ALL mb_id (NULL) (NULL) (NULL) 13383 Using filesort
1 SIMPLE mb eq_ref PRIMARY,userid PRIMARY 4 mbei.mb_id 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 mb.uid 1 Using index
优化第一步之分支1：根据驱动表的字段排序，好吗？ 好的 ,需要对驱动表直接排序
left join不变。干嘛要根据非驱动表的字段排序呢？因为非驱动表排序太慢 我们前面说过“对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序！”的。
explain
SELECT mb.id……
FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid
WHERE 1=1
ORDER BY mb.id DESC
limit 0,10
也满足业务场景，做到了rows最小：
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE mb index userid PRIMARY 4 (NULL) 10 1 SIMPLE mbei eq_ref mb_id mb_id 4 mb.id 1 Using index 1 SIMPLE u eq_ref PRIMARY PRIMARY 4 mb.uid 1 Using index
优化第二步：去除所有JOIN，让MySQL自行决定！写这么多密密麻麻的 left join/inner join 很开心吗？
explain
SELECT mb.id……
FROM mb,mbei,u
WHERE
mb.id=mbei.mb_id
and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10
立竿见影，驱动表一样是小表 mbei：
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE mbei ALL mb_id (NULL) (NULL) (NULL) 13388 Using filesort 1 SIMPLE mb eq_ref PRIMARY,userid PRIMARY 4 mbei.mb_id 1 1 SIMPLE u eq_ref PRIMARY PRIMARY 4 mb.uid 1 Using index
最后的总结：
强调再强调：
1、不要过于相信你的运气！
2、不要相信你的开发环境里SQL的执行速度！
3、请拿起 explain 武器，如果你看到以下现象，请优化：
1）出现了Using temporary
2）rows过多，或者几乎是全表的记录数
3）key 是 (NULL)
4）possible_keys 出现过多（待选）索引
记住，explain 是一种美德！
来自 http://blog.csdn.net/u013065023/article/details/54964275
MySQL Explain详解
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，此时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。（QEP：sql生成一个执行计划query Execution plan）
mysql> explain select * from servers; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.03 sec)
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：
一、 id
我的理解是SQL执行的顺序的标识,SQL从大到小的执行
1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
二、select_type
示查询中每个select子句的类型
(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果)
(6) SUBQUERY(子查询中的第一个SELECT)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
三、table
显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)
mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | | | 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
四、type (对性能影响大)
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
五、possible_keys (对性能影响大)
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
六、Key (对性能影响大)
key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
七、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好
八、ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
九、rows (对性能影响大)
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
十、Extra (对性能影响大)
该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询(这个要注意)
Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。(这个要注意)
Impossible where：这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
在建有索引的字段上尽量不要使用函数操作
例如：在一个Date类型的字段上使用year（）函数时，将会使索引不能发挥作用，下面两个语句后者要比前者快。
select * from order where year(orderDate)<2001;
select * from order where orderDate <"2001-01-01";
总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
炫志：
你好
非常感谢你写的这么详尽的博文
我目前遇到了一个官方文档查询示例，不怎么理解
问过几个开发都没有得到比较好的解释
文档地址：http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/tutorial.html#example-maximum-column-group-row
其中“3.6.4. 拥有某个字段的组间最大值的行”这个示例是我所困惑的
按照explain返回的结果如下图：
结合本文所述，上述地址文档示例查询：
1 2 3 4 5 | SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
|
应该先执行子查询，但子查询受上层查询限制，上层查询会遍历表的所有行与子查询比较，我非常困惑这种比较是怎样进行的，这个查询是怎样保证分组MAX出来的price不会带出来非组内最大值的记录（若存在某分组最大值与另一分组内非最大值相同的情况）？
来自 http://www.cnblogs.com/xuanzhi201111/p/4175635.html
MYSQL explain详解
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
虽然这篇文章我写的很长，但看起来真的不会困啊，真的都是干货啊！！！！
先解析一条sql语句，看出现什么内容
EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address
FROM uchome_space AS s,uchome_spacefield AS f
WHERE 1
AND s.groupid=0
AND s.uid=f.uid
1. id
SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序，看下面这条sql
EXPLAIN SELECT *FROM (SELECT* FROMuchome_space LIMIT 10)AS s
它的执行结果为
可以看到这时的id变化了
2.select_type
select类型，它有以下几种值
2.1 simple 它表示简单的select,没有union和子查询
2.2 primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary,上图中就是这样
2.3 union union语句的第二个或者说是后面那一个.现执行一条语句，explain
select * from uchome_space limit 10 union select * from uchome_space limit 10,10
会有如下结果
第二条语句使用了union
2.4 dependent union UNION中的第二个或后面的SELECT语句，取决于外面的查询
2.5 union result UNION的结果,如上面所示
还有几个参数，这里就不说了，不重要
3 table
输出的行所用的表，这个参数显而易见，容易理解
4 type
连接类型。有多个参数，先从最佳类型到最差类型介绍 重要且困难
4.1 system
表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计
4.2 const
表最多有一个匹配行，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快
记住一定是用到primary key 或者unique，并且只检索出两条数据的 情况下才会是const,看下面这条语句
explain SELECT * FROM `asj_admin_log` limit 1,结果是
虽然只搜索一条数据，但是因为没有用到指定的索引，所以不会使用const.继续看下面这个
explain SELECT * FROM `asj_admin_log` where log_id = 111
log_id是主键，所以使用了const。所以说可以理解为const是最优化的
4.3 eq_ref
对于eq_ref的解释，mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。看下面的语句
explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid
得到的结果是下图所示。很明显，mysql使用eq_ref联接来处理uchome_space表。
目前的疑问：
4.3.1 为什么是只有uchome_space一个表用到了eq_ref,并且sql语句如果变成
explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid
结果还是一样，需要说明的是uid在这两个表中都是primary
4.4 ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。
看下面这条语句 explain select * from uchome_space where uchome_space.friendnum = 0，得到结果如下，这条语句能搜出1w条数据
4.5 ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
上面这五种情况都是很理想的索引使用情况
4.6 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
4.7 unique_subquery
4.8 index_subquery
4.9 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.以为是一定范围所以说像 between也可以这种联接,很明显
explain select * from uchome_space where friendnum = 17
这样的语句是不会使用range的，它会使用更好的联接类型就是上面介绍的ref
4.10 index 该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。
4.11 ALL 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。5 possible_keys 提示使用哪个索引会在该表中找到行，不太重要
6 keys MYSQL使用的索引，简单且重要
7 key_len MYSQL使用的索引长度
8 ref ref列显示使用哪个列或常数与key一起从表中选择行。
9 rows 显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
10 Extra 该列包含MySQL解决查询的详细信息。
10.1 Distinct MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。一直没见过这个值
10.2 Not exists
10.3 range checked for each record
没有找到合适的索引
10.4 using filesort
MYSQL手册是这么解释的“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。”目前不太明白
10.5 using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解，就是说明是否使用了索引
explain select * from ucspace_uchome where uid = 1的extra为using index（uid建有索引）
explain select count(*) from uchome_space where groupid=1 的extra为using where(groupid未建立索引)
10.6 using temporary
为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
出现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，如上面的执行计划所示！
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
2）未指定联接条件时，行数少的表为[驱动表]（Important!）。
今天学到了一个很重要的一点：当不确定是用哪种类型的join时，让mysql优化器自动去判断，我们只需写select * from t1,t2 where t1.field = t2.field
10.7 using whereWHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。（这个说明不是很理解，因为很多很多语句都会有where条件，而type为all或index只能说明检索的数据多，并不能说明错误，useing where不是很重要，但是很常见）
如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。
10.8 Using sort_union(...), Using union(...),Using intersect(...)这些函数说明如何为index_merge联接类型合并索引扫描
10.9 Using index for group-by
类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
实例讲解
通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。
2017年1.26的拓展 我是无所不能的coder的分界线
回头看看几年前写的这篇博客，真的也是很浅显，只是简单的介绍了explain后每个选项的概念，对于实例没有太多的讲解，而且最重要的是没有指出那种情况下的选项（结合实际情况）才是最优化的，ok,start again
很明显，在所有explain的结果中最重要的要数type/key/rows/extra这4个字段了,那接下来我着重在说一下这四个字段代表的意思及如何优化
现有两个表，一个项目表（project），一个留言表(t_message)，用户可以针对不同的项目进行流行操作。
现有一个最基本的联表操作，
EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id
结果是这样的
出现这种情况是最容易理解的了，因为这只是简单联表查询，没有加任何条件，在实际情况下是不会出现这种sql的。从上图的结果中可以看出mysql对t_message表进行了全表扫描，对project表使用了eq_ref,这符合了mysql对什么情况下会使用到eq_ref的定义，这是非常理想的一种连接类型。
下面我们讨论一个实际情况下会遇到的例子，我们联表取前100条数据，
EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id LIMIT 100
可以发现，除了影响的行数稍微多了一点（可以忽略，甚至可以理解为没有不同），其他所有的参数都是相同的，也就是说，这种情况下搜索全部数据和搜索100条数据的耗时是一样的，为什么会这样呢？不应该啊！！
这里需要着重说明的是：上面两条语句explain得到的结果是相同的，是因为他们的索引使用策略是相同的，即都没有很好的使用索引，（因为没有where条件和order by语句）但他们的最终耗时是不同的，很明显传输100条数据肯定要比传送1条数据慢。所以，最终耗时会在sending data（用show profile查看）上消耗的比例最大
那实际情况下，最有可能会遇到什么问题呢？
1 根据项目id作为搜索条件（即使用where条件）
2 根据时间或者id来排序（即使用order by条件）
3 根据以上两个
下面我们开始举栗子
《1》搜索最新的100条留言
《2》搜索出某个项目下最新的10条留言
《3》搜索出某个项目最近一个月每天有多少条留言
《4》搜索出最近一个月每天有多少条留言
《5》搜索某个用户今天留言数量
《6》搜索今天有多少条新增留言
下面我们开始吃栗子
《1》搜索最新的100条留言
EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id ORDER BY t.id DESC LIMIT 100 ;
EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id ORDER BY p.id DESC LIMIT 100
以下两条语句都能实现效果，但索引使用情况却完全不同。第一条语句要比第二条优化的多，
可以看到，第一条语句的type值为index,影响结果即只有100行，也就是说非常合适的使用了索引。正所谓，福无双至祸不单行，当你一个地方出问题的时候，难免其他地方也出问题，因为没有使用合理的索引-->导致全表扫描-->影响结果集太大-->从而导致使用了using temporary和using filesort(这个也很重要)。那这两条语句很明显只有order by条件的一点小小的不同.说实话，我不是很理解为什么会出现这种情况因为这两个条件分表是两个表的主键，都有主键索引，唯一合理的解释可能是因为这时候联表之后t_message是主表（因为他是留言表，一切以他为准），而order by排序当然应该是根据主表的主键拍排序才会使用到索引了，似乎有点牵强，但貌似这么理解没有大毛病
下面着重说一下using temporary和using filesort
using temporary 官方解释：”为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。“”很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过家里临时表来辅助处理
using filesort 官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”
我这里的理解是：对于order by的字段没有使用到字段，所以使用了using filesort. 这两个问题同时出现的可能性很大啊！！！
《2》搜索出某个项目下最新的10条留言
EXPLAIN SELECT * FROM t_message WHERE target_id = 770 ORDER BY id DESC LIMIT 10;
EXPLAIN SELECT * FROM t_message WHERE target_id = 770 ORDER BY publish_time DESC LIMIT 10
以上两条select语句的执行搜索结果是一样的，但explain分析结果不同，只是因为order by 条件的不同
下面是自己亲自测试的
1) 下面是 message 和 project 两表是 message的 外键 p_id 与 project 的 id 主键关联
message 表
project 表
2)
explain select * from project as p join message as m on p.id = m.p_id
3)不排序 下面 两表区别 就是 下表比上表 多一个 limit 500 好像 explain 后没区别
4) 由下表可知 使用 message 的 id 排序较快 第一行为驱动表 即 message (也就是 m) 不管message 行数是不是比 project 多
2017年架构师最重要的48个小时 | 8折倒计时
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了：
如：
explain select surname,first_name form a,b where a.id=b.id
EXPLAIN列的解释：
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
extra列返回的描述的意义
Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
system 表只有一行：system表。这是const连接类型的特殊情况
const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免