求教SQL语句优化

[yalan]

千万行的表，快到亿了，使用 in 字句要崩溃啊，求优化方法。

表结构简述如下：
id，username，regtime，loginip，logindatetime

想要检索出登录10次以上的用户，现在用的语句是：
select * from table where username in (select username from table group by username having count(*)>10)

如果加上条件，要检索出某天登录10次以上、来自某几个IP的用户，更慢：
select * from table where username in (select username from table group by username having count(*)>10) and date(logindatetime)='2019-12-12' and loginip in('1,1,1,1','2.2.2.2','3.3.3.3')

折腾了半天，快崩溃了。。。求优化

[liangtu]

你这张表应该是登录日志表，有几个问题需要确认 1.是否所有记录都在一张表内，没有按照时间分表 2.建表语句、索引语句都贴出来 3.查询登录10次以上的用户，多久查询一次，一般查询哪些天的？

[yalan]

登录表只有一张表，每个月自动生成一张表，比如201910 201911 201912。。。。 不定啥时候需要查询用户，查询哪些天也不定，所以有点头疼 表的结构就是我一楼的结构， id 主键 username key

[yalan]

补充：每个月的登录次数就要几千万行了 我想问在现有表的前提下如何优化查询呢？现在唯一能做的权限就是给表加个索引啥的，唉

[liangtu]

登录时间上加索引，查询时用 >= '2020-03-01 00:00:00' and <'2020-03-02 00:00:00'

[yalan]

引用 4 楼 liangtu 的回复:

登录时间上加索引，查询时用 >= '2020-03-01 00:00:00' and <'2020-03-02 00:00:00'

试了下基本没改善 我觉得效率低主要在 in 字句，尤其是加了检索条件后有两个 in 字句，效率更差啊

[csdn_castiel]

select username from table where logindatetime='2019-12-12' and loginip in('1,1,1,1','2.2.2.2','3.3.3.3') group by username having count(*)>10 联合索引(logindatetime,loginip,username) logindatetime='2019-12-12'此处不要用时间函数，要用也放右边 得到的username存入临时表tmp_table 最后 select * from tmp_table a join table b on a.username=b.username

[ACMAIN_CHM]

explain select * from table where username in (select username from table group by username having count(*)>10) and date(logindatetime)='2019-12-12' and loginip in('1,1,1,1','2.2.2.2','3.3.3.3') 结果贴出来分析一下。 还有相关表的 show index 也贴出来。

[stonecutter]

select username from table group by username having count(*)>10
此表肯定是全表扫描，要想快就是加大buffer_pool或者减少表的数据量（分表）。

[yalan]

引用 6 楼 csdn_castiel 的回复:

select username from table where logindatetime='2019-12-12' and loginip in('1,1,1,1','2.2.2.2','3.3.3.3') group by username having count(*)>10 联合索引(logindatetime,loginip,username) logindatetime='2019-12-12'此处不要用时间函数，要用也放右边 得到的username存入临时表tmp_table 最后 select * from tmp_table a join table b on a.username=b.username

感谢大家帮助，最后还是分表解决的