mysql语句优化，统计一段时间玩家回流用户数

悬赏园豆：50 [已解决问题] 解决于 2019-03-16 09:52

因某需求统计回流用户，目前已写出sql语句，但是效率很低，目前数据200多万，
回流用户：如2019-01-05号登陆过的玩家，在6号到7号之间没有登陆过，但是他们在8号又登陆过的用户。

问题补充：

sql语句如下：

 1 SELECT count(*) FROM player_login WHERE openid IN (
 2 　　SELECT openid FROM player_login WHERE openid NOT IN (
 3 　　　　SELECT openid FROM player_login
 4 　　　　WHERE
 5 　　　　　　date_time BETWEEN '2019-01-06 00:00:00'
 6 　　　　　　AND '2019-01-07 23:59:59'
 7 　　　　　　AND EXISTS (
 8 　　　　　　　　SELECT openid FROM player_login
 9 　　　　　　　　WHERE
10 　　　　　　　　　　date_time BETWEEN '2019-01-05 00:00:00'
11 　　　　　　　　　　AND '2019-01-05 23:59:59'
12 　　　　　　　　　　GROUP BY openid
13 　　　　　　)
14 　　　　)
15 　　　　AND date_time BETWEEN '2019-01-05 00:00:00'
16 　　　　AND '2019-01-05 23:59:59'
17 　　)
18 AND date_time BETWEEN '2019-01-08 00:00:00'
19 AND '2019-01-08 23:59:59'

水平有限只能写成这样，请问各位大神还能怎么优化？

花儿为何那样红 | 初学一级 | 园豆：81
提问于：2019-02-25 18:45

in 用join 表连接试一下。会快吗？

– Sopcce 5年前

< >

最佳答案

--要查询8日登录的数量。筛选是否含有5日登录且67没有登录

需要创建临时表，去除in 使用exists，在数据多的时候，in 会严重影响索引，所以一个使用

#创建一个小数据的临时表。使用链接查询，选择交集。

这段sql 主要是查询在5日登陆并且67没有登录的用户。把所有星替换掉啊。

CREATE TEMPORARY TABLE table_temp_567
select openid from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid ) as table_temp_5 where not exists(select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67 where table_temp_67.openid = table_temp_5.openid)
-----------------------------------
DROP TABLE table_temp_567;
select * from table_temp_567
---------------
可以不用临时表，但是查询太多也会印象查询速度的，一般最后拆分开。

select * from table_temp_567
left join (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid ) as table_temp_8 on table_temp_567.openid=table_temp_8.openid

这样子的速度，不清楚哈哈

测试流程数据

CREATE TEMPORARY TABLE table_temp_567
select openid from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid ) as table_temp_5 where not exists(select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67 where table_temp_67.openid = table_temp_5.openid)

select * from player_login

--查询67日登陆的测试6条
select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67

--查询5日登陆 7条
SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid

--查询5日登陆且67没有登录的 31.4
select openid from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid ) as table_temp_5 where not exists(select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67 where table_temp_67.openid = table_temp_5.openid)

--查询8日登录 8条
SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid

--创建临时表，需要对567、8日的，一567为主，毕竟567表示5日等且67日登录的。所以一567为主。关联查询。
select * from table_temp_567
left join (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid ) as table_temp_8 on table_temp_567.openid=table_temp_8.openid
--查询2条 31.4

测试数据，存在局限性，要不请提供sql 结构包括数据，数据请自动脱敏。

测试数据表结构和数据下面回复贴出来了

https://img2018.cnblogs.com/q/676728/201902/676728-20190228161325733-975401087.png

下载此文件，修改后缀改为SQL ,可以打开结构和数据脚本

收获园豆：30

家秋 | 菜鸟二级 |园豆：475 | 2019-02-27 14:35

这段sql查询的结果和table_temp_567表的结果一样不知道是怎么回事。

花儿为何那样红 | 园豆：81 (初学一级) | 2019-02-27 16:41

@花儿为何那样红: 567 是排除67七日登录用户的数据,5日等的用户，
select * from table_temp_567 这是查询567日数据的。

SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid
这是查询8日数据的。

我的测试结果是一样的。

一下附上我的测试数据

/*
Navicat Premium Data Transfer

Source Server : localhost-root-123456
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : mywebsite

Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001

Date: 28/02/2019 16:00:33
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for player_login
-- ----------------------------
DROP TABLE IF EXISTS player_login;
CREATE TABLE player_login (
id int(11) NOT NULL AUTO_INCREMENT,
openid int(11) NOT NULL,
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
date_time datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
age int(11) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- Records of player_login
-- ----------------------------
INSERT INTO player_login VALUES (1, 1, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (2, 1, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (3, 1, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (4, 1, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (5, 1, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (6, 1, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (7, 2, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (8, 2, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (9, 2, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (10, 2, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (11, 2, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (12, 2, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (13, 3, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (14, 3, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (15, 3, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (16, 3, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (17, 3, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (18, 4, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (19, 31, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (20, 31, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (21, 31, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (22, 31, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (23, 4, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (24, 4, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (25, 4, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (26, 4, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (27, 5, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (28, 5, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (29, 5, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (30, 5, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (31, 5, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (32, 6, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (33, 6, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (34, 6, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (35, 6, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (36, 6, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (37, 7, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (38, 7, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (39, 7, '1', '2019-02-09 13:40:43', 11);

SET FOREIGN_KEY_CHECKS = 1;

家秋 | 园豆：475 (菜鸟二级) | 2019-02-28 16:02

@Sopcce: 方便加个qq交流吧，我给你发表结构和数据。我的qq：919303698

花儿为何那样红 | 园豆：81 (初学一级) | 2019-02-28 16:19

其他回答(1)

SELECT count(*) FROM player_login WHERE openid IN (

count(*)，建议用count(1)替代；
WHERE openid IN 建议用exists替代in

另外，用explain语句，执行下该sql，根据执行计划，进行修复

收获园豆：20

Co~Co | 园豆：507 (小虾三级) | 2019-02-26 13:15

我尝试过用exists，但是查询出来的数据和in查询出来的数据相差很大，具体原因未知

支持(0) 反对(0) 花儿为何那样红 | 园豆：81 (初学一级) | 2019-02-26 16:35

@花儿为何那样红: 建议发下表结构再发几条数据过来，只看sql没法看啊……

支持(0) 反对(0) Co~Co | 园豆：507 (小虾三级) | 2019-02-26 17:17

方便的话，给个邮箱，我发给你，谢谢！

支持(0) 反对(0) 花儿为何那样红 | 园豆：81 (初学一级) | 2019-02-26 17:27

@花儿为何那样红: 1009866898@qq.com

支持(0) 反对(0) Co~Co | 园豆：507 (小虾三级) | 2019-02-26 17:37

@coco_xu: 已发你邮箱

支持(0) 反对(0) 花儿为何那样红 | 园豆：81 (初学一级) | 2019-02-26 17:47

@花儿为何那样红: 看不出可还要怎么优化了，不好意思哈

支持(0) 反对(0) Co~Co | 园豆：507 (小虾三级) | 2019-02-27 11:04

@coco_xu: 好吧！那也多谢了

支持(0) 反对(0) 花儿为何那样红 | 园豆：81 (初学一级) | 2019-02-27 11:08

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。