mysql 相关问题

1. 分析mysql的性能：EXPLAIN MYSQL;

2. 用mysql做权重的排序

权重规则：距离与人气各占50%

例：

SELECT *,((M.rank1+N.rank2)*0.5) AS weight
FROM ( 
( SELECT shop_id,distance,@rank1:=@rank1+1 AS rank1 FROM ( 
(SELECT shop_id,@rank1:=0,12756274*ASIN(SQRT(POWER(SIN(('纬度'-lat)*0.008726646),2) + COS(36.793*0.0174533)*COS(lat*0.0174533)*POWER(SIN(('经度'-lng)*0.008726646),2))) AS distance 
FROM `tableName` WHERE 1=1 ) AS A ) ORDER BY distance ASC 
) AS M 

LEFT JOIN 

( SELECT shop_id,@rank2:=@rank2+1 AS rank2,number FROM ( 
(SELECT shop_id,@rank2:=0,number FROM `tableName` WHERE 1=1 ) AS B ) ORDER BY number DESC 
) AS N 

ON M.shop_id=N.shop_id 

LEFT JOIN `tableName` AS F ON M.shop_id=F.shop_id 

) 
WHERE 1=1 GROUP BY M.shop_id ORDER BY weight ASC,M.shop_id DESC 

注：用@key:=value来定义、赋值变量