目录标题
- join时以大表为基表
join时以大表为基表
-
查不动
explain select u.id as useId, u.open_id as userOpenId, u.name as userName, u.phone as userPhone, s.id as shopId, s.shop_id as shopNum, s.shop_name as shopName from jml_mp_wechatuser u right join jml_shop s on u.id = shopkeeper_id where u.phone = '6D26B768750A90426AAA4CE04FA4CCBB'
-
查不动
EXPLAIN select u.id as useId, u.open_id as userOpenId, u.name as userName, u.phone as userPhone, s.id as shopId, s.shop_id as shopNum, s.shop_name as shopName from jml_shop s LEFT JOIN jml_mp_wechatuser u on u.id = s.shopkeeper_id where u.phone = '6D26B768750A90426AAA4CE04FA4CCBB'
-
5s
EXPLAIN select u.id as useId, u.open_id as userOpenId, u.name as userName, u.phone as userPhone, s.id as shopId, s.shop_id as shopNum, s.shop_name as shopName from jml_shop s LEFT JOIN jml_mp_wechatuser u on u.open_id = s.shopkeeper_openid where u.phone = '6D26B768750A90426AAA4CE04FA4CCBB'
-
3s
explain select u.id as useId, u.open_id as userOpenId, u.name as userName, u.phone as userPhone, s.id as shopId, s.shop_id as shopNum, s.shop_name as shopName from jml_mp_wechatuser u left join jml_shop s on u.id = shopkeeper_id where u.phone = '6D26B768750A90426AAA4CE04FA4CCBB'
-
4s
explain select u.id as useId, u.open_id as userOpenId, u.name as userName, u.phone as userPhone, s.id as shopId, s.shop_id as shopNum, s.shop_name as shopName from jml_mp_wechatuser u left join jml_shop s on u.open_id = s.shopkeeper_openid where u.phone = '6D26B768750A90426AAA4CE04FA4CCBB'
A left join B = B right join A； join的原理是以基准表为底，根据on的条件for循环去另一张表找到等值进而关联。所以显而易见，影响join效率的是等值的长度及for循环次数。以大表为基准可以有效减少for循环次数，选取主键关联可以更快的判断等值。
如何join？ on主键+大表为基准！
