手写分页sql_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'