select * from (SELECT * FROM 表 where fenlei in(1,2) and ispc=1 order by id desc) T WHERE T.fenlei in(1,2) and T.month>0 group by T.fenlei order by T.month desc LIMIT 0,20
创建了fenlei和ispc的单独索引和联合索引,但是还是扫描全表了。
select * from (SELECT * FROM 表 where fenlei in(1,2) and ispc=1 order by id desc) T WHERE T.fenlei in(1,2) and T.month>0 group by T.fenlei order by T.month desc LIMIT 0,20
创建了fenlei和ispc的单独索引和联合索引,但是还是扫描全表了。
下面用几个例子对比查询条件的不同对性能影响.
create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);
优: select * from test where a=10 and b>50
差: select * from test where a50
优: select * from test where order by a
差: select * from test where order by b
差: select * from test where order by c
优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c
优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c
索引原则
1.索引越少越好
原因:主要在修改数据时,第个索引都要进行更新,降低写速度。
2.最窄的字段放在键的左边
3.避免file sort排序,临时表和表扫描.