[sql]分页查询

来源：互联网发布：小米双卡网络切换编辑：程序博客网时间：2024/06/14 02:07

程序员代码的编写能力主要体现在思维的严谨上。有些看起来很简单的东西，里面包含很多很细的点，你能想到吗？今天我就简单说一下一个例子，让大家学习到新知识的同时，也养成一种思维的习惯。

有一张收藏表，里面存储的是用户和图书ID。数据量为1亿。现在要求分页获取所有用户ID（不重复），写下你的sql语句。

表结构大致如下：

MySQL
1
2
3
4
5
6
7
8
CREATE TABLE 收藏表(
     `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'primary key',
     `uid` bigint(20) unsigned NOT NULL default 0 COMMENT 'uid',<br>　　  `status` tinyint(3) unsigned NOT NULL default 0 COMMENT 'status',
     `book_id` bigint(20) unsigned NOT NULL default 0 COMMENT 'book Id',
     `create_time` int(11) unsigned not null default 0 COMMENT 'create time',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uid_book_id` (`uid`, `book_id`),<br>　　　 KEY `uid_status` (`uid`, `status`)
)ENGINED=Innodb Auto_increment=1 default charset=gbk COMMENT '用户收藏信息';

三种设计

最容易想到的第一种分页语句是（这也是我们最容易想到的语句）：

MySQL
1
2
selectdistinctuidfrom收藏表order byuiddesclimit0,10;
selectdistinctuidfrom收藏表order byuiddesclimit11,10;

再高级点语句，第二种（$last_min_uid表示上一次读到的最后一个uid）：

MySQL
1
2
select distinct uid from 收藏表 order by uid desc limit 10;
select distinct uid from 收藏表 where uid < $last_min_uid order by uid desc limit 10;

最高级的方式

MySQL
1
2
selectuidfrom收藏表groupbyuidorder byuiddesclimit10;
selectuidfrom收藏表groupbyuidhavinguid<$last_min_uidorder byuiddesclimit10;

分析

以上三种方式都可以实现分页获取到用户ID列表，那么区别是什么？我现在就把每一种跟大家分析下。

第一种在业务场景中，会出现丢数据的情况。——这是比较严重的情况，不予采纳。

具体的业务场景是这样的：当你读取第5页的时候，前四页的用户id列表中，假如有一页的用户ID从库中删除掉，那么你这时读到的第5页(limit 51, 10)，就是原来的第6页，你会把1页的用户ID丢失掉。

第二种的第二条语句，通过explain分析，实际并没有命中唯一索引，而只是命中了一般索引，数据查询范围在7百万级别，故explain建议我们使用group by。——这个查询会有严重的性能问题。

MySQL
1
2
3
4
5
+----+--------------+---------------+-------+-------------------
| id   | select_type | table   | type  | possible_keys  | key  | key_len | ref  | rows | Extra |
+----+--------------+---------------+-------+-------------------
| 1    | SIMPLE     | ubook_room | range | uid_book_id | uid_status  | 4  | NULL | 7066423  | Using where; Using index for group-by; Using temporary; Using filesort | 
+----+--------------+---------------+-------+-------------------

第三种explain分析，数据查询范围在12万级别（跟第二种相差一个数量级），查询性能高。

MySQL
1
2
3
4
5
+----+--------------+---------------+-------+-------------------
|id  |select_type|table  |type  |possible_keys  |key  |key_len|ref  |rows|Extra|
+----+---------------+------------+-------+-----------------+-----------------+---------+---
|1    |SIMPLE  |收藏表  |index|NULL  |uid_book_id|12  |NULL  |121719|Usingindex|
+----+---------------+------------+-------+-----------------+-----------------+---------+---

0 0