Mysql中的分页写法

基本上分页都是通过两个sql来实现的，一个查询count（*），一个查询list，如下：

    mysql> select count(*) from test;

    +----------+

    | count(*) |

    +----------+

    | 2097152 |

    +----------+

    1 row in set (0.00 sec)

mysql> select * from test limit 10;

    +--------+------+

    | a | b |

    +--------+------+

    | 62599 | |

    | 158694 | |

    | 155279 | |

    | 233810 | |

    | 160137 | |

    | 221035 | |

    | 174371 | |

    | 242246 | |

    | 147503 | |

    | 257907 | |

    +--------+------+

    10 rows in set (0.00 sec)

    Mysql中引出了另外的分页实现方式，通过sql_calc_found_rows，found_rows()查询一次就可以实现，减少了数据库的调用次数。

    1.计算全部结果集合中有多少，这比不用LIMIT而再次运行问询要快，原因是结果集合不需要被送至客户端。

    mysql> select sql_calc_found_rows * from test limit 10;

    +--------+------+

    | a | b |

    +--------+------+

    | 62599 | |

    | 158694 | |

    | 155279 | |

    | 233810 | |

    | 160137 | |

    | 221035 | |

    | 174371 | |

    | 242246 | |

    | 147503 | |

    | 257907 | |

    +--------+------+

    10 rows in set (3.45 sec)

    2.直接取出count(*)总数

    mysql> select found_rows();

    +--------------+

    | found_rows() |

    +--------------+

    | 2097152 |

    +--------------+

    1 row in set (0.00 sec)

    sql_calc_found_rows 和 found_rows() 在当你希望限制返回的行数时很有用，你不需要再次根据count(*)取到结果集。最明显的例子就是Web 分页list分页脚本，使用found_rows() 你可以很容易确定剩下的结果需要多少其它的页。

    通过 found_rows()的有效行数是瞬时的，并且不用于越过SELECT sql_calc_found_rows语句后面的语句，你要是需要保留这个值。

    mysql> select sql_calc_found_rows * from ... ;

    mysql> set @rows = found_rows();

Mysql中翻页如何实现？

    通常分页比如论坛帖子，我们一般都会看好几页，比如这个帖子非常火，分页翻到了第100页，这种语句的实现方式：

    mysql> select * from test where a>10 limit 1000,10 ;

    +--------+------+

    | a | b |

    +--------+------+

    | 187673 | |

    | 187721 | |

    | 187760 | |

    | 187780 | |

    | 187816 | |

    | 187821 | |

    | 187827 | |

    | 187891 | |

    | 187893 | |

    | 187966 | |

    +--------+------+

    10 rows in set (0.01 sec)

    这么写的话通常需要扫描表的前1010行，大家都知道回表的代价是很大的，而随着翻页的增加，扫描的代价会越来越大，我们是否可以采取另外的实现方式。

    1.先根据索引去取出a，这个地方只能走索引

    mysql> select a from test where a>10 limit 1000,10 ;

    +--------+

    | a |

    +--------+

    | 187673 |

    | 187721 |

    | 187760 |

    | 187780 |

    | 187816 |

    | 187821 |

    | 187827 |

    | 187891 |

    | 187893 |

    | 187966 |

    +--------+

    10 rows in set (0.00 sec)

    2.根据取到的记录id再去回表

    mysql> select t1.* from test t1,(select a from test where a>10 limit 1000,10) t2 where t1.a=t2.a;

    这种实现方式只需要回表很少记录，因为取id是通过扫描来实现的，然后根据符合条件的记录数id来回表。

注:mysql中是不允许子查询中使用limit

    mysql> select * from test where a in(select a from test where a>10 limit 1000,10);

    ERROR 1235 (42000): This version of MySQL doesn\'t yet support \'LIMIT & IN/ALL/ANY/SOME subquery\'