查询 '产品的名字和公司的名字' 从产品表左连接公司表，再左连接用户表，product->company->user
① 有where条件:用户名是‘sdf’
mysql> explain select product.name pname,company.name cname
from product
left join company using(cid)
left join user using(cid)
where user.name = 'sdf' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: ref
possible_keys: cid,ux
key: ux
key_len: 19
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: product
type: ref
possible_keys: cid
key: cid
key_len: 5
ref: info.user.cid
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: company
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: info.product.cid
rows: 1
Extra:
②无where条件
mysql> explain select product.name pname,company.name cname
from product
left join company using(cid)
left join user using(cid) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: product
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: company
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: info.product.cid
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: user
type: ref
possible_keys: cid
key: cid
key_len: 5
ref: info.product.cid
rows: 1
Extra: Using index
3 rows in set (0.00 sec)
结论1：当有where条件时用到了三个表的索引（有的用到了主键索引）
结论2：先 where 再 join ,先处理有where的表，再按join顺序处理表
结论3：join 的顺序在第二个实例中先是对product表全表扫描
