MySQL Execution Plan--NOT IN查询

在某系统中想使用NOT IN子查询进行数据过滤,SQL为:

SELECT * FROM TB001 AS T1
WHERE T1.update_time<DATE_ADD(NOW(),INTERVAL -90 DAY)
AND T1.BATCH_NO NOT  IN(SELECT BATCH_NO FROM TB002 AS T2 ) 
AND T1.OPT_STATUS=2 AND T1.BATCH_TYPE=10
LIMIT 1000

上面SQL执行时间未6.84秒,相关表数据量为:
表TB001:507716
表TB002:11266065

为验证NOT IN 子查询对查询的影响,移除NOT IN子查询后,SQL调整为:

SELECT * FROM TB001 AS T1
WHERE T1.update_time<DATE_ADD(NOW(),INTERVAL -90 DAY)
AND T1.OPT_STATUS=2 AND T1.BATCH_TYPE=10
LIMIT 1000

SQL执行时间未0.15秒

将上面NOT IN语句转换为程序伪代码:

## 设置limit 返回行数
int limit_row_count=1000
## 使用match_row_list存放满足的记录
match_row_list=[]
## 按照update_time上索引遍历满足update_time条件的记录
## 单次操作消耗约为6,一次按索引键读取+一次按主键读取
for row_item in TB001 where update_time<DATE_ADD(NOW(),INTERVAL -90 DAY):
    ## 按照其他条件过滤记录
    if row_item .OPT_STATUS=2 AND row_item .BATCH_TYPE=10:
        ## 按照子查询过滤记录
        ## 单次操作约为3或4,一次按索引键读取
        if not exists (SELECT BATCH_NO FROM TB002 where BATCH_NO=row_item.BATCH_NO )
            ## 将满足子查询的记录放到list中
            match_row_list.append(row_item)
            ## 满足limit行数后返回
            if match_row_list.length()==limit_row_count:
                retrun match_row_list

该SQL执行效率取决于3点:
1、满足update_time条件的记录总数(TN)
2、满足update_time条件的记录存满足NOT IN子查询的概率(PT)
3、查询需要返回的数据行数即LIMIT数量(LN)
4、对于NOT IN子查询内部,查询仅需要找到第一条满足条件的记录即可返回,子表TB002的数据量与查询时间没有明显关系


假设每遍历一条满足update_time条件的记录的操作消耗为10,查询消耗=10*Min((LN/PT),TN),:
1、最坏情况下,LN/PT的值远大于TN时或TN*PT的值小于LN时,查询需要遍历所有满足update_time条件的记录,即查询消耗最高为=10*TN
2、最佳情况下,当PT概率足够高无限接近于1时,查询遍历LN条数据即可跳出循环,查询最低消耗为=10*LN
3、普通场景下,需要返回的数量LN小于满足NOT IN条件的数量(TN*PT),查询消耗=10*LN/PT,查询消耗与PT成反比.


数据分布对查询性能影响:
在很多业务场景下,记录满足NOT IN子查询的概率并不是均匀的,以网站注册用户为例,并不是所有用户的购买商品概率都相同,最新注册用户购买商品的概率会远高于两年前注册用户。


扩展知识:
1、在按照索引查找记录(LIMIT 1或EXIST操作)时,查询效率与索引层级相关,受表数据量影响较小,相同表结构下,100万数据量索引层级可能为4,1000万数据量的索引层级也可能为4,此时访问100万数据量表的消耗和访问1000万数据量表的消耗相同和接近。
2、当SQL语句在数据库上执行时,查询优化器会按照统计信息来评估生成执行计划,MySQL内部会按照某些公式对SQL语句进行转换,如IN操作可能会被转换成EXIST操作,也可能依旧为IN操作,这也是结构化查询语言与编译语言的重要区别。

原文地址:https://www.cnblogs.com/gaogao67/p/10723472.html