为何含NOT IN的SQL全表扫描？求查询优化方案

问题详情 #

编写的SQL查询使用了NOT IN操作符（包含子查询），已创建复合索引，但执行时仍触发全表扫描。执行计划显示access_type为ALL，查询成本376074.60，单次扫描行数达1663413，怀疑是NOT IN导致该问题，希望获得优化方案以减少行扫描量和查询成本。

原查询语句 #

SELECT col1 
FROM table1 
WHERE col2 BETWEEN '2023-01-15 00:00:00' AND '2023-01-15 07:31:09' 
AND col5 NOT IN('lit@yopmail.com','sneh@yopmail.com','snehah@yopmial.com','vishm@yopmail.com','aneha@yopmail.com','sneha10@yopmail.com','mukesh.sh@qa.team','ssw@yopmail.com','vish@yopmail.com','neuro@yopmail.com','delete@yopmail.com','sms@yopmail.com','krk@yopmail.com','samarora916@gmail.com','testing221@yopmail.com','om@yopmail.com','muskes@yopmail.com','putruuui@yopmail.com','rajuu@yopmail.com','priyan123@yopmail.com','prep28@yopmail.com','kewq@yopmail.com','ER.KUNALKHATRubbbhbh@GMAIL.COM') 
AND col4 IN (value1,value2) 
AND col3 > 2 
AND col1 IN (val1,val2,val3,val4,val5) 
AND orderID NOT IN (
    SELECT col6 
    FROM table1 
    WHERE col1 = value61 
    AND col4 IN (value1,value2) 
    AND col3 > 0 
    AND col2 BETWEEN '2023-01-15 00:00:00' AND '2023-01-15 07:31:09'
    );

执行计划信息 #

{
  "query_cost": "376074.60",
  "table_name": "table1",
  "access_type": "ALL",
  "possible_keys": [
    "col5",
    "col1_col7_col3"
  ],
  "rows_examined_per_scan": 1663413,
  "rows_produced_per_join": 9300,
  "filtered": "0.56",
  "cost_info": {
    "read_cost": "374214.59",
    "eval_cost": "1860.01",
    "prefix_cost": "376074.60",
    "data_read_per_join": "99M"
  },
  "table": {
    "table_name": "table1",
    "access_type": "ref",
    "possible_keys": [
      "col6",
      "col1_col7_col3"
    ],
    "key": "col1_col6_col3",
    "used_key_parts": [
      "col1"
    ],
    "key_length": "4",
    "ref": [
      "const"
    ],
    "rows_examined_per_scan": 63,
    "rows_produced_per_join": 0,
    "filtered": "0.74"
  }
}

优化方案 #

1. 替换NOT IN为NOT EXISTS #

NOT IN在处理子查询时，优化器执行效率通常低于NOT EXISTS，且若子查询返回NULL值会导致整个条件失效。将主查询中的orderID NOT IN (...)替换为NOT EXISTS：

AND NOT EXISTS (
    SELECT 1 
    FROM table1 t2
    WHERE t2.col6 = table1.orderID
    AND t2.col1 = value61 
    AND t2.col4 IN (value1,value2) 
    AND t2.col3 > 0 
    AND t2.col2 BETWEEN '2023-01-15 00:00:00' AND '2023-01-15 07:31:09'
)

2. 调整复合索引结构 #

索引字段顺序遵循等值/IN条件在前，范围条件在后的原则，让优化器能更好利用索引：

• 为主查询创建覆盖过滤条件的复合索引：

  CREATE INDEX idx_table1_col1_col4_col3_col2 ON table1(col1, col4, col3, col2);
  

• 为子查询优化现有索引，补充col4和col2字段实现索引覆盖：

  CREATE INDEX idx_table1_col1_col4_col6_col3_col2 ON table1(col1, col4, col6, col3, col2);
  

3. 优化col5的NOT IN条件 #

对于多值NOT IN，改用NOT EXISTS结合临时表的方式提升处理效率：

AND NOT EXISTS (
    SELECT 1 
    FROM (
        VALUES 
            ('lit@yopmail.com'),('sneh@yopmail.com'),('snehah@yopmial.com'),
            ('vishm@yopmail.com'),('aneha@yopmail.com'),('sneha10@yopmail.com'),
            ('mukesh.sh@qa.team'),('ssw@yopmail.com'),('vish@yopmail.com'),
            ('neuro@yopmail.com'),('delete@yopmail.com'),('sms@yopmail.com'),
            ('krk@yopmail.com'),('samarora916@gmail.com'),('testing221@yopmail.com'),
            ('om@yopmail.com'),('muskes@yopmail.com'),('putruuui@yopmail.com'),
            ('rajuu@yopmail.com'),('priyan123@yopmail.com'),('prep28@yopmail.com'),
            ('kewq@yopmail.com'),('ER.KUNALKHATRubbbhbh@GMAIL.COM')
    ) AS block_list(email)
    WHERE block_list.email = table1.col5
)

4. 更新表统计信息 #

过时的统计信息可能导致优化器选错执行计划，执行以下命令更新：

ANALYZE TABLE table1;

5. 临时方案：强制使用索引 #

若优化器仍未选择正确索引，可在主查询中强制指定索引（仅作为临时方案，优先依赖优化器自动选择）：

SELECT col1 
FROM table1 FORCE INDEX(idx_table1_col1_col4_col3_col2)
WHERE ... -- 原WHERE条件

内容的提问来源于stack exchange，提问作者Banshika Kumari