MySQL Execution Plan--IN查询计划

对于IN查询，MySQL会根据当前表数据结构(索引)和数据分布(统计信息和预估)生成多种执行计划，并根据执行成本挑选出“最优执行计划”。

假设有查询

SELECT *
FROM student
WHERE class_id IN (1,2,3,4);

有下面三种执行计划：

1、对表student做表扫描
2、循环IN列表中每个值，对表student上class_id列做索引查找
3、计算IN列表中最大值和最小值，对表student上class_id列做索引范围扫描

 

方式1：对表student做表扫描

对表做全表扫描，遍历student表的每行数据，找出每行匹配IN条件的记录并返回。查询效率与表数据量成正比。

伪代码：

def get_students_01():
    class_id_list=(1,2,3,4)
    matched_rows=[]
    for student_row in (table scan in table student):
        if student_row.class_id in class_id_list:
            matched_rows.append(student_row)
    return matched_rows

适用场景：

1、列class_id上无索引，导致只能全表扫描

2、满足IN条件的数据占整表数据比重较大时，如表中班级ID仅有(1,2,3,4,5),需要查询满足(1,2,3,4)的记录，表中大部分数据都满足该条件，如果使用列class_id做索引查找+PRIMARY KEY LOOKUP操作，PRIMARY KEY LOOKUP操作会产生大量随机IO，执行成本远超过全表扫描产生的顺序IO。

性能问题:

当列class_id上存在索引且满足IN条件的数据占整表数据比重较小时，全表扫描会访问大量“无用数据”，浪费IO和CPU资源，导致性能问题。如全表数据有1000W，满足IN条件的数据仅有10行，此时使用INDEX SEEK+KEY LOOPUP会效率更高。

 

方式2：循环IN列表中每个值，对表student上class_id列做索引查找

循环取出IN列表中每个值，并使用该值去表student中根据class_id做等值查询，然后做PRIMARY KEY LOOPUP,最后将每个IN列表值查询结果汇总后返回

伪代码：

def get_students_02():
    class_id_list=(1,2,3,4)
    matched_rows=[]
    for tmp_class_id in class_id_list:
        for tmp_student_id in (index seek in table student with index idx_class_id where class_id = tmp_class_id):
            student_row = (index seek in table student with primary key where student_id = tmp_student_id)
            if student_row is not null:
                matched_rows.append(student_row)
    return matched_rows

适用场景：

1、列class_id上有索引，且列class_id选择性较高，IN列表数据量较少

性能问题：

1、列class_id上有索引，但列class_id选择性较差，需要进行大量KEY LOOPUP操作，产生大量随机IO导致性能问题

2、列class_id上有索引，但IN列表包含值太多，需要进行多次循环，MySQL Server层和存储引擎层需要进行多次交互，引发性能问题。

 

方式3：计算IN列表中最大值和最小值，对表student上class_id列做索引范围扫描

获取IN列表中最大值和最小值，并使用这两值去表student中根据class_id做范围扫描(顺序IO),对扫描后的结果按照IN列表进行过滤，然后做PRIMARY KEY LOOPUP,最后将所有满足条件的数据汇总返回。

伪代码：

def get_students_02()
    class_id_list=(1,2,3,4)
    matched_rows=[]
    max_class_id=max(class_id_list)
    min_class_id=min(class_id_list)
    for tmp_student_id in (index seek in table student with index idx_class_id where class_id >=min_class_id and class_id<=max_class_id):
        student_row = (index seek in table student with primary key where student_id = tmp_student_id)
        if student_row is not null:
            if student_row.class_id in class_id_list:
                matched_rows.append(student_row)
    return matched_rows

方式3是对方式2的优化，通过一次范围扫描来替换循环索引查找。

适用场景：

1、列class_id上有索引，IN列表包含大量值，且值集中在特定范围，如class_id的值分布在0-99999范围，而IN列表的值集中在1000-2000范围，扫描该范围数据可获得所有满足条件的数据。

性能问题：

1、列class_id上有索引，IN列表包含大量值，且值分散在整表范围，如class_id的值分布在0-99999范围，而IN列表的值为(1000,5000,10000,90000),取值在1000-90000范围，需要扫描范围过大，其扫描结果中大量数据不满足IN条件，访问过多“无用数据”，造成性能问题。

 

扩展知识：

对于IN列表中的值进行预估时，受参数eq_range_index_dive_limit影响，超过阈值后，会导致预估准确率问题。

https://www.cnblogs.com/TeyGao/p/6585879.html