内容来自 https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html
——联表查询的基础知识——
引子:为什么第一个查询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
------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- ---------------------------
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
1)指定了联接条件时, 满足查询条件的记录行数少的表为[驱动表];
2) 未指定联接条件时,行数少的表为[驱动表] (Important!)。
永远用小结果集驱动大结果集 (Important!)!
——实例讲解——
explainSELECT mb.id, ……FROMmb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uidWHERE 1=1ORDER BY mbei.apply_time DESClimit 0,10
------ ----------- ------ ------ -------------- -------------- ------- ------------------- ------- --------------------------------------------
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
explainSELECT mb.id……
FROM mb JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uidWHERE 1=1ORDER BY mbei.apply_time DESClimit 0,10
------ ----------- ------ ------ -------------- ------- ------- ---------------------------- ------ --------------
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:根据驱动表的字段排序,好吗?
explainSELECT mb.id……
FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uidWHERE 1=1ORDER BY mb.id DESClimit 0,10
------ ----------- ------ ------ -------------- -------------- ------- ------------------- ------ -----------
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自行决定!
explainFROM mb,mbei,uSELECT mb.id……
WHERE
mb.id=mbei.mb_id
and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10
------ ----------- ------ ------ -------------- ------- ------- ---------------------------- ------ --------------
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
最后的总结:
不要过于相信你的运气!不要相信你的开发环境里SQL的执行速度!请拿起 explain 武器,如果你看到以下现象,请优化:
- 出现了Using temporary;
- rows过多,或者几乎是全表的记录数;
- key 是 (NULL);
- possible_keys 出现过多(待选)索引。
记住,explain 是一种美德!
![http://ww3.sinaimg.cn/bmiddle/97f224aagw1e8fffdvbtkg20b404qu0x.gif](http://ww3.sinaimg.cn/bmiddle/97f224aagw1e8fffdvbtkg20b404qu0x.gif)