sql优化 not in join exists 取差集

最新推荐文章于 2023-02-08 23:03:19 发布

跟着爸爸走

最新推荐文章于 2023-02-08 23:03:19 发布

阅读量367

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/heqiang1995/article/details/115698401

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

白名单功能几千万数据

最终方案

base_apt 和 base_guard 添加字段whiteip 默认为0 白名单为1 只要sip或者dip在白名单数据就标为1

添加字段时需要多久
全量update 更新白名单要多久需要增大innodb_buffer_pool

添加白名单和删除白名单全量update需要多久
定时任务多久跑一趟去分析新数据 id为游标
修改所有查询sql

1.优化sql

2.数据库添加字段接入或者定时分析就识别

EXISTS就是对一条数据返回true false

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)

exists

SELECT COUNT(1) FROM base_apt WHERE happen_time BETWEEN '2021-01-29 00:00:01.0' AND '2021-03-01 00:00:00.0' AND s_ip NOT IN ('10.128.2.227','10.1.48.1','10.68.5.2','10.62.36.3','10.62.36.4','10.62.36.14','10.1.122.229','10.62.5.72','10.62.5.169','10.62.5.180','10.62.34.2','10.63.3.252','10.1.96.61','10.1.130.100','10.1.208.9','10.1.165.16','10.1.165.23','10.1.53.16','10.1.204.201','10.62.11.130','10.62.11.26','10.1.193.8','10.1.77.46','10.1.77.252','10.1.159.66','192.1.72.39','10.101.254.254','10.1.162.103','172.20.120.22','10.103.7.244') AND d_ip NOT IN ('10.128.2.227','10.1.48.1','10.68.5.2','10.62.36.3','10.62.36.4','10.62.36.14','10.1.122.229','10.62.5.72','10.62.5.169','10.62.5.180','10.62.34.2','10.63.3.252','10.1.96.61','10.1.130.100','10.1.208.9','10.1.165.16','10.1.165.23','10.1.53.16','10.1.204.201','10.62.11.130','10.62.11.26','10.1.193.8','10.1.77.46','10.1.77.252','10.1.159.66','192.1.72.39','10.101.254.254','10.1.162.103','172.20.120.22','10.103.7.244')

总共 1272011
存在 1272011
不存在 0

白名单1244907

exists （返回结果集，为真）
not exists (不返回结果集，为真）

SELECT COUNT(*) from
(

SELECT id FROM base_apt as a WHERE happen_time BETWEEN '2021-01-29 00:00:01.0' AND '2021-03-01 00:00:00.0'
and not EXISTS
(SELECT id FROM base_apt as b WHERE happen_time BETWEEN '2021-01-29 00:00:01.0' AND '2021-03-01 00:00:00.0' AND (s_ip IN ('10.128.2.227','10.1.48.1','10.68.5.2','10.62.36.3','10.62.36.4','10.62.36.14','10.1.122.229','10.62.5.72','10.62.5.169','10.62.5.180','10.62.34.2','10.63.3.252','10.1.96.61','10.1.130.100','10.1.208.9','10.1.165.16','10.1.165.23','10.1.53.16','10.1.204.201','10.62.11.130','10.62.11.26','10.1.193.8','10.1.77.46','10.1.77.252','10.1.159.66','192.1.72.39','10.101.254.254','10.1.162.103','172.20.120.22','10.103.7.244') or d_ip IN ('10.128.2.227','10.1.48.1','10.68.5.2','10.62.36.3','10.62.36.4','10.62.36.14','10.1.122.229','10.62.5.72','10.62.5.169','10.62.5.180','10.62.34.2','10.63.3.252','10.1.96.61','10.1.130.100','10.1.208.9','10.1.165.16','10.1.165.23','10.1.53.16','10.1.204.201','10.62.11.130','10.62.11.26','10.1.193.8','10.1.77.46','10.1.77.252','10.1.159.66','192.1.72.39','10.101.254.254','10.1.162.103','172.20.120.22','10.103.7.244'))

and a.id = b.id)

)
as c

left join 小表靠大表

SELECT count(*) from
(
(SELECT id FROM base_apt WHERE happen_time BETWEEN '2021-01-29 00:00:01.0' AND '2021-03-01 00:00:00.0' ) as t1

left join

(SELECT id FROM base_apt WHERE happen_time BETWEEN '2021-01-29 00:00:01.0' AND '2021-03-01 00:00:00.0' AND (s_ip IN ('10.128.2.227','10.1.48.1','10.68.5.2','10.62.36.3','10.62.36.4','10.62.36.14','10.1.122.229','10.62.5.72','10.62.5.169','10.62.5.180','10.62.34.2','10.63.3.252','10.1.96.61','10.1.130.100','10.1.208.9','10.1.165.16','10.1.165.23','10.1.53.16','10.1.204.201','10.62.11.130','10.62.11.26','10.1.193.8','10.1.77.46','10.1.77.252','10.1.159.66','192.1.72.39','10.101.254.254','10.1.162.103','172.20.120.22','10.103.7.244') or d_ip IN ('10.128.2.227','10.1.48.1','10.68.5.2','10.62.36.3','10.62.36.4','10.62.36.14','10.1.122.229','10.62.5.72','10.62.5.169','10.62.5.180','10.62.34.2','10.63.3.252','10.1.96.61','10.1.130.100','10.1.208.9','10.1.165.16','10.1.165.23','10.1.53.16','10.1.204.201','10.62.11.130','10.62.11.26','10.1.193.8','10.1.77.46','10.1.77.252','10.1.159.66','192.1.72.39','10.101.254.254','10.1.162.103','172.20.120.22','10.103.7.244'))) as t2

on t1.id = t2.id
) where t2.id IS NULL