数据库跨表统计排序及效率优化

背景

项目中遇到了这样一个需求：显示出在某一时间范围内，按照推荐客户人数降序排列的用户列表。用户表与推荐客户表属于1:n的关系。针对此类需求，特作此总结。

情景复现

数据库搭建

假设数据库结构如下： 用户表：

1
2
3
4
5

CREATE TABLE users(
    `id`    INT(11) PRIMARY KEY AUTO_INCREMENT,
    `name`  CHAR(20) NOT NULL COMMENT '用户名',
    `mobile`    CHAR(15) NOT NULL COMMENT '电话'
)ENGINE=INNODB;

推荐客户表：

1
2
3
4
5
6
7

CREATE TABLE customer(
    `id`    INT(11) PRIMARY KEY AUTO_INCREMENT,
    `uid`   INT(11)  NOT NULL COMMENT '推荐人ID',
    `name`  CHAR(20) NOT NULL COMMENT '客户姓名',
    `mobile` CHAR(15) NOT NULL COMMENT '客户电话',
    `recommend_at`  DATETIME NOT NULL COMMENT '推荐时间'
)ENGINE=INNODB;

需要解决的问题

首先，排序需要分页显示，而分页需要有两个参数：数据总量、每页数据数量 每页数据量可以自行设置，但是数据总量又怎么得出呢？这里的数据总量，不能是所有的用户，因为不一定所有用户推荐过客户，这里需要统计推荐过客户的用户即可。

获取数据总量

SELECT COUNT(DISTINCT(uid)) FROM customer WHERE recommend_at > '开始时间' and recommend_at < '结束时间';

语句解析： 我们仅需要显示在规定时间内推荐过的用户，那么只需要在customer表里边，将uid去重后统计即可。

DESCRIBE SELECT COUNT(DISTINCT(uid)) FROM customer WHERE recommend_at > '开始时间' and recommend_at < '结束时间';

语句优化：使用 DESCRIBE 语句可以发现，此方法会扫描全表（type=ALL），效率不高，如果这个功能使用比较勤，或者对效率要求比较高的话，可以考虑给customer建立相关索引，如：INDEX(`uid, recommend_at`)

获取数据

这次的重头戏就是获取数据了，经过一番研究，发现有两种解决方案。 假设users表和customer表的内容如下： users:

id

name

mobile

1

王一

13012345671

2

王二

13012345672

3

王三

13012345673

customer表:

id

uid

name

mobile

contract_at

1

1

张一

15012345671

2018-04-09 01:31

2

1

张二

15012345672

2018-04-09 01:31

3

1

张三

15012345673

2018-04-09 01:31

4

2

张四

15012345674

2018-04-09 01:31

即：王一 推荐了 3个客户，王二 推荐了 1个用户， 王三没有推荐用户 最终结果预测：

uid

num

1

3

2

1

方案1 JOIN+GROUP（不推荐）

使用 JOIN 将users表和customer表串联起来，使用where子句筛选出符合条件的信息，最后使用GROUP BY 按照uid进行分组，然后逆序排列即可。 SELECT users.id, COUNT(*) AS num FROM users LEFT JOIN customer ON customer.uid = users.id AND recommend_at > '开始时间' AND recommend_at < '结束时间' WHERE customer.uid IS NOT NULL GROUP BY users.id ORDER BY num LIMIT 0, 10;

语句分析： 首先，使用 LEFT JOIN 将customer与users内联起来，直接内联结果如下表。用WHERE子句过滤掉没有推荐客户的users后，使用GROUP BY 进行分组，将统计数量 num 逆序排序，输出结果。

id(u)

name(u)

mobile(u)

id(c)

uid(c)

name(c)

mobile(c)

recommend_at(c)

1

王一

13012345671

1

1

张一

15012345671

2018-04-09 01:31

1

王一

13012345671

2

1

张二

15012345672

2018-04-09 01:31

1

王一

13012345671

3

1

张三

15012345673

2018-04-09 01:31

2

王二

13012345671

4

2

张四

15012345674

2018-04-09 01:31

3

王三

13012345671

NULL

NULL

NULL

NULL

NULL

语句优化：同理，如果不建立索引，那么使用 DESCRIBE 语句可以发现，type=ALL，可给customer增加INDEX(`uid, recommend_at`)。又由于用户表的id是主键id，所以不用考虑索引的问题了。

不推荐理由

通过DESCRIBE和JOIN的原理就可以看出来，这种方式是要跨两个表查询的，即先查询 users表中符合条件的数据，再按照关联查询出 customer中的数据，效率较低，最后再对统计数据中的 num建立临时表，倒序输出。

方案二 GROUP BY（推荐）

直接对customer先使用where筛选出符合条件的数据，再对其进行GROUP分组即可。至于我为什么没有一开始就想到这个方法，只是因为惯性思维，认为统计用户信息需要从用户表着手罢了。 SELECT uid,COUNT(*) AS num FROM customer WHERE recommend_at > '开始时间' AND recommend_at < '结束时间' GROUP BY uid ORDER BY num LIMIT 0, 10;

语句分析：查询customer表中符合时间段的数据，查询完毕后进行GROUP BY uid，就可以把uid对应时间的数据统计出来了。 语句优化：普通查询语句，仅需加INDEX(`recommend_at`, `uid`);即可。

两种方案差别

方案一，由于是基于users表，所以查询出结果时，可以直接附带上用户表中的树形。 方案二，只针对customer表进行统计，速度会比较快，但是只能获取users表和customer表的关联属性uid，如若获取用户其他信息，则只能再次查询数据库。

总结

主表(users),从表(customer)，跨表统计需要解决两个问题，一个是数据总量，一个是数据。 数据总量可以通过从表使用DISTINCT去重+COUNT(*)得出。 数据获取有两种方式，第一种方式：主表+JOIN+从表+GROUP，这样可以在获取数据的时候顺便获取主表的信息，效率相比较低；第二种方式：从表+GROUP，这种方式可以获取到从表与主表之间的关联ID和统计数据，效率较高。