程序员代码的编写能力主要体现在思维的严谨上。有些看起来很简单的东西,里面包含很多很细的点,你能想到吗?今天我就简单说一下一个例子,让大家学习到新知识的同时,也养成一种思维的习惯。
有一张收藏表,里面存储的是用户和图书ID。数据量为1亿。现在要求分页获取所有用户ID(不重复),写下你的sql语句。
表结构大致如下:
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
'用户收藏信息';
|
三种设计
最容易想到的第一种分页语句是(这也是我们最容易想到的语句):
1
2
|
select
distinct
uid
from
收藏表
order by
uid
desc
limit
0,
10;
select
distinct
uid
from
收藏表
order by
uid
desc
limit
11,
10;
|
再高级点语句,第二种($last_min_uid表示上一次读到的最后一个uid):
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;
|
最高级的方式
1
2
|
select
uid
from
收藏表
group
by
uid
order by
uid
desc
limit
10;
select
uid
from
收藏表
group
by
uid
having
uid
<
$last_min_uid
order by
uid
desc
limit
10;
|
分析
以上三种方式都可以实现分页获取到用户ID列表,那么区别是什么?我现在就把每一种跟大家分析下。
第一种在业务场景中,会出现丢数据的情况。——这是比较严重的情况,不予采纳。
具体的业务场景是这样的:当你读取第5页的时候,前四页的用户id列表中,假如有一页的用户ID从库中删除掉,那么你这时读到的第5页(limit 51, 10),就是原来的第6页,你会把1页的用户ID丢失掉。
第二种的第二条语句,通过explain分析,实际并没有命中唯一索引,而只是命中了一般索引,数据查询范围在7百万级别,故explain建议我们使用group by。——这个查询会有严重的性能问题。
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万级别(跟第二种相差一个数量级),查询性能高。
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
|
Using
index
|
+----+---------------+------------+-------+-----------------+-----------------+---------+---
|