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操作，这也是结构化查询语言与编译语言的重要区别。