PS现在orders表中存在idx_user_id(user_id)一个索引1-问题当前我们之前加的只有user_id的索引然后我们换一个sql来进行查询EXPLAINSELECT*FROMordersWHEREuser_id1ANDstatus1;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_ididx_user_id9const910Using where分析虽然查询条件里有user_id和status但目前只用上了user_id的单列索引type ref说明还是走了索引查找比全表扫描好很多key idx_user_id实际用的索引是你之前建的user_id索引rows 9预估扫描9行比之前的十万行已经非常小Extra Using where表示虽然通过索引定位到了user_id 1的行但还需要再用WHERE status 1来过滤2-优化key idx_user_id表示只用到了user_id的索引没有用到status如果查询条件里有多个字段但只用到其中一个索引就说明可能需要联合索引Extra Using where说明虽然通过索引定位到了user_id1的行但还要额外用WHERE status1来过滤索引没有覆盖到status导致多余的过滤rows 9, filtered 10数据库预估扫描 9 行但只有 10% 符合条件这意味着索引帮你缩小到某个用户的订单但在这些订单里还要再过滤status效率不算最优所以我们需要加入user_id,status联合索引提高效率# 新增索引CREATEINDEXidx_user_statusONorders(user_id,status);# 再次运行EXPLAINSELECT*FROMordersWHEREuser_id1ANDstatus1;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_id,idx_user_statusidx_user_status14const,const2100分析possible_keys idx_user_id, idx_user_status数据库识别到两个索引都可能用但最终选择了联合索引idx_user_statuskey idx_user_status实际使用的是你刚建的(user_id, status)联合索引这说明优化成功key_len 14索引长度更长因为联合索引包含了两个字段ref const,const表示两个条件user_id1和status1都直接用常量去匹配索引rows 2预估只需要扫描 2 行比之前的 9 行更少说明索引定位更精准filtered 100表示这2行全部符合条件不需要额外过滤Extra没有Using where说明条件已经完全由索引覆盖不需要再做额外的行过滤3-扩展PS现在orders表中存在idx_user_id(user_id)以及idx_user_status(user_id, status)两个索引3-1.只查询索引字段之前我们的都是使用的Select *查询的所有现在我们只查询索引字段EXPLAINSELECTuser_id,statusFROMordersWHEREuser_id1ANDstatus1;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_id,idx_user_statusidx_user_status14const,const2100Using index分析key idx_user_status数据库选择了你新建的联合索引(user_id, status)而不是单列索引ref const,const两个条件都直接用常量匹配索引说明索引完全覆盖了查询条件rows 2预估只需要扫描 2 行比之前的 9 行更少定位非常精准filtered 100表示这 2 行全部符合条件不需要额外过滤Extra Using index它意味着查询只依赖索引不需要回表去取数据(关键)。因为你只查询了索引里的字段user_id和status而这两个字段已经包含在联合索引里所以数据库直接从索引返回结果3-2.分析其实相比之前的select *少了一个回表查询所有字段的操作之前的select *的流程联合索引 idx_user_status ↓ 找到满足条件的索引记录 ↓ 根据主键回表 ↓ 读取整行数据只查询user_id, status这两个字段时的流程联合索引 idx_user_status ↓ 找到满足条件记录 ↓ 直接返回3-3.覆盖索引的概念覆盖索引查询的所有字段都在索引中例如当前索引idx_user_status(user_id,status)SqlSELECTuser_id,status字段全部包含在内
【MySQL-索引调优】03：联合索引及覆盖索引概念
PS现在orders表中存在idx_user_id(user_id)一个索引1-问题当前我们之前加的只有user_id的索引然后我们换一个sql来进行查询EXPLAINSELECT*FROMordersWHEREuser_id1ANDstatus1;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_ididx_user_id9const910Using where分析虽然查询条件里有user_id和status但目前只用上了user_id的单列索引type ref说明还是走了索引查找比全表扫描好很多key idx_user_id实际用的索引是你之前建的user_id索引rows 9预估扫描9行比之前的十万行已经非常小Extra Using where表示虽然通过索引定位到了user_id 1的行但还需要再用WHERE status 1来过滤2-优化key idx_user_id表示只用到了user_id的索引没有用到status如果查询条件里有多个字段但只用到其中一个索引就说明可能需要联合索引Extra Using where说明虽然通过索引定位到了user_id1的行但还要额外用WHERE status1来过滤索引没有覆盖到status导致多余的过滤rows 9, filtered 10数据库预估扫描 9 行但只有 10% 符合条件这意味着索引帮你缩小到某个用户的订单但在这些订单里还要再过滤status效率不算最优所以我们需要加入user_id,status联合索引提高效率# 新增索引CREATEINDEXidx_user_statusONorders(user_id,status);# 再次运行EXPLAINSELECT*FROMordersWHEREuser_id1ANDstatus1;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_id,idx_user_statusidx_user_status14const,const2100分析possible_keys idx_user_id, idx_user_status数据库识别到两个索引都可能用但最终选择了联合索引idx_user_statuskey idx_user_status实际使用的是你刚建的(user_id, status)联合索引这说明优化成功key_len 14索引长度更长因为联合索引包含了两个字段ref const,const表示两个条件user_id1和status1都直接用常量去匹配索引rows 2预估只需要扫描 2 行比之前的 9 行更少说明索引定位更精准filtered 100表示这2行全部符合条件不需要额外过滤Extra没有Using where说明条件已经完全由索引覆盖不需要再做额外的行过滤3-扩展PS现在orders表中存在idx_user_id(user_id)以及idx_user_status(user_id, status)两个索引3-1.只查询索引字段之前我们的都是使用的Select *查询的所有现在我们只查询索引字段EXPLAINSELECTuser_id,statusFROMordersWHEREuser_id1ANDstatus1;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_id,idx_user_statusidx_user_status14const,const2100Using index分析key idx_user_status数据库选择了你新建的联合索引(user_id, status)而不是单列索引ref const,const两个条件都直接用常量匹配索引说明索引完全覆盖了查询条件rows 2预估只需要扫描 2 行比之前的 9 行更少定位非常精准filtered 100表示这 2 行全部符合条件不需要额外过滤Extra Using index它意味着查询只依赖索引不需要回表去取数据(关键)。因为你只查询了索引里的字段user_id和status而这两个字段已经包含在联合索引里所以数据库直接从索引返回结果3-2.分析其实相比之前的select *少了一个回表查询所有字段的操作之前的select *的流程联合索引 idx_user_status ↓ 找到满足条件的索引记录 ↓ 根据主键回表 ↓ 读取整行数据只查询user_id, status这两个字段时的流程联合索引 idx_user_status ↓ 找到满足条件记录 ↓ 直接返回3-3.覆盖索引的概念覆盖索引查询的所有字段都在索引中例如当前索引idx_user_status(user_id,status)SqlSELECTuser_id,status字段全部包含在内
