mysql - 这个 sql 是否命中了 MySql 复合索引？

Question

我有一张表:student_homework，其复合索引之一是 uk_sid_lsnid_version(student_id, lesson_id, curriculum_version, type) :

student_homework    0   uk_sid_lsnid_version    1   student_id  A   100             BTREE       
student_homework    0   uk_sid_lsnid_version    2   lesson_id   A   100             BTREE       
student_homework    0   uk_sid_lsnid_version    3   curriculum_version  A   100             BTREE       
student_homework    0   uk_sid_lsnid_version    4   type    A   100             BTREE   

现在我有一个 Sql: select * from student_homework where student_id=100 and type=1 和 explain 结果如下:

1   SIMPLE  student_homework        ref uk_sid_lsnid_version,idx_student_id_update_time uk_sid_lsnid_version    4   const   20  10.0    Using index condition

执行计划是 uk_sid_lsnid_version 。
我的问题是查询条件 type 在这里是如何工作的？数据库引擎是否会为其扫描所有(缩小的)记录？在我的理解中，树层次结构是:

              student_id 
               /       \
           lesson_id     lesson_id
            /                      \  
     curriculum_version          curriculum_version
       /            \
      type         type

对于查询条件(student_id，type)， student_id 匹配树索引的根。然而， type 与索引 lesson_id 不匹配，数据库引擎会将 type 应用于 的所有记录 ，这些记录已被 student_id 过滤。

我的理解是否正确？如果带有 student_id 的子集记录很大，则查询成本仍然很高。

查询条件没有区别 student_id = 100 and type =0 and type=0 and student_id = 100

为了充分利用复合索引，添加一个新的复合索引(student_id, type)会不会更好？

Answer 1

是的，您的理解是正确的，mysql 将使用 uk_sid_lsnid_version 索引仅匹配 student_id ，而对 type 的过滤将在与 student_id 匹配的减少的行集上完成。
提示在解释结果的 extra 列中:Using index condition

Using index condition (JSON property: using_index_condition)

Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary. See Section 8.2.1.6, “Index Condition Pushdown Optimization”.

Section 8.2.1.6, “Index Condition Pushdown Optimization 将这种技术的步骤描述为:

1. Get the next row's index tuple (but not the full table row).
2. Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is notsatisfied, proceed to the index tuple for the next row.
3. If the condition is satisfied, use the index tuple to locate and read the full table row.
4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

是否在student_id上再增加一个复合索引会更好，type是我们无法客观回答的问题，需要测试一下。
如果使用当前索引的查询速度很好，那么您可能不需要新索引。您还需要权衡有多少其他查询会使用该索引——仅仅为一个查询创建索引没有多大意义。您还需要权衡 type 字段的选择性。具有有限值列表的类型字段通常不够有选择性。 Mysql 可能决定使用索引条件下推，因为 student_id，类型 index 不是覆盖索引，无论如何 mysql 都必须获取完整行。