在web应用中,提倡sql简单,所以在我们公司的应用中看不到jon,子查询等语句的存在,所以间接oprder by 与 limit的使用占据大多数,其实很多技巧,别人都是总结过的,仔细分析,仔细学习别人的经验才是正道.而不可浮躁,凭经验主义.

    1:order by with limit

    2:make sure it uses index

    对于order by with limit来说,不执行扫描和排序是非常重要的,所以索引是非常重要的,index range scan执行的结果:一旦特定数量的行返回就结束.

    比如 select * from sites order by date_created desc limit 10,使用索引(date_created)将非常的快,

    我们来看下一个查询 select * from sites where category_id=5 order by date_created desc limit 10,在这个情况下date_created索引将工作,但可能并不是最有效的,因为category_id很大一部分将需要扫描找出10行,所以索引(category_id, date_created)是个很好的解决方案.

    让我们看一个更复杂的查询 select * from sites where category_id in (5,10,15) order by date_created desc limit 10,这个查询其实和上一个有很大不同,category_id因为有多个值,所以这个索引不能直接使用,这是btree索引的原因.

    select from people where gender=\'m\' and age between 18 and 28 and country_id=5 and city_id=345 order by last_online desc limit 10

    这个有很多的限制因素,可以通过一些通用的搜索解决方案去解决.但是假如我们在选择性比较好的列使用multiple indexes将是一个很好的性能解决方案,比如索引(gender,last_online),假定大部分的性别类型是固定的.这个索引将会比较有作用.

    我们另外需要仔细观察的事情,假如你没有通过索引完全覆盖where条件,这样很多记录将被扫描并进行排序(一般都在慢查询中发现),假如只有50行记录去执行并只提供10行将不会有多大问题,假如有5000行,你就需要重新考虑索引了.

    记录被扫描提供结果集是个动态的结果:同其他因素混合在一块是经常变化的,举个例子,假如仅仅使用索引last_online,假如我们寻找美国人,那么10行会很快扫描到,假如是一个比价小的国家,这样可能需要扫描很多次才能提供结果集.

    在上面的例子中,我们通过最后一个列进行索引,实际上order by的字段一般都可以索引,而leading列进行排序,

    注意,一些情况下列不能同时进行order by索引和过滤条件索引.

    select * from tbl where c=5 order by a,b limit 10语句,索引(a,b,c) 索引能够使用order by ,但是不能过滤c的结果,所以提倡使用索引(c,a,b)

    3:sort by column in leading table

    4:sort in one direction

    5:beware of large limit

    if you\'re dealing with LIMIT query with large offset efficiency will suffer . bug Using index to sort is efficient if you need first few rows

    6:Force index if needed

    Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity or just better estimates instead of which allows you to do the sort.

    7:Do not sort by expressions

    是不要用mysql内置函数和表达式去排序,而非过滤,对于我们文章库x_rank字段为什么不使用索引,是因为status是个range 扫描,即使加x_rank索引也是没有效果的

    select * from table where uid=\'\' and status in (1,3) and x_rank &64=64 order by date2 desc

    http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

建议继续学习：

1. MYSQL分页limit速度太慢优化方法    (阅读：4344)
2. 合理使用MySQL的Limit进行分页    (阅读：3015)
3. 一条SQL引发的对order by的思考    (阅读：2465)
4. MySQL中order by的实现 和 by rand() 和优化    (阅读：2546)