mysql语句优化对比

①limit

limit offset,length中offset越大查询越慢；并不是用主键查询就快，例如下文中的id/uid

select * from wiz_chk_20160407 group by uid  limit 1,100;    (0.01s)

select * from wiz_chk_20160407 group by uid  limit 199000,100;    (9s)

select id from wiz_chk_20160407 group by uid  limit 189000,100;   (7.5s)

select uid from wiz_chk_20160407 group by uid  limit 17000,100;    (0.67s)

select * from  (select uid from wiz_checkfiles_greengallery_20160407 group by uid  limit 177000,100) as a left join wiz_checkfiles_greengallery_20160407 b on a.uid=b.uid group by b.uid;    (0.62s)

如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。

②mysql_num_rows 与 count(*)

结果：
fetch_num_rows 用时：35.272329092026（遍历数据库）

count(*) 用时：0.071956872940063（用于单纯统计数量）

③distinct

select  count(distinct(uid)) from wiz_checkfiles_greengallery_20160330;  (7s)

select count(1) from (select uid from wiz_checkfiles_greengallery_20160330 group by uid) as a;  (4s)

④count(*)  count(1)   count(主键)

count(1)   count(主键) 更好

count判断（）中的参数是否为空