1 、order by + limit 组合的索引优化
select * from [table] order by [sort] limit [offset] ,[limit];
在字段sort上建立索引
2、 where + order by + limit 组合的索引
select * from [table] where [columnX] = [value] order by [sort] limit [offset] ,[limit];
联合索引 在字段column X和sort 上创建索引
3、where + in + order by + limit 组合索引优化
select * from [table] where [coulunmX] in ([value1] ,[value2]………) order by [sort] limit [offset] ,[limit];
=>
SELECT * FROM [table] WHERE [columnX]=[value1] ORDER BY [sort] LIMIT [offset],[limit]
UNION
SELECT * FROM [table] WHERE [columnX]=[value2] ORDER BY [sort] LIMIT [offset],[limit]
UNION
联合索引 在字段column X和sort 上创建索引
4、不要在where和 order by 上运用表达式(函数)
SELECT * FROM [table] ORDER BY YEAR(date) LIMIT 0,30;
5、WHERE + ORDER BY 多个栏位 + LIMIT
SELECT * FROM [table] WHERE uid = 1 ORDER x ,y LIMIT 0,10;
对于这个语句,大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。