MySQL关联主键查询优化：索引创建建议及EXPLAIN无索引问题咨询

咱们一步步来拆解你的查询优化问题，帮你解决索引没被使用的困惑：

一、基础过滤条件的索引设计 #

首先说你提到的(Hotel.IsClosed, Hotel.Enabled)和(HotelRoom.Deleted, HotelRoom.Enabled)这两组条件，确实需要创建针对性的索引，但索引的顺序和包含的字段很关键：

1. Hotel表的索引 #

你的查询需要从Hotel表获取HotelId, Name, Enabled, IsClosed, AuxiliaryId，同时通过IsClosed=0和Enabled=1过滤，还要和HotelRoom做关联。

• 索引的最左列应该是过滤性最强的条件字段（一般来说IsClosed和Enabled都是布尔值，过滤性差不多，谁放前面都行），然后加上AuxiliaryId（适配后面的FIND_IN_SET条件），最后把需要查询的字段加进去做覆盖索引，避免回表：

  -- MySQL 8.0+支持INCLUDE，推荐用这个（不占用索引键空间）
  CREATE INDEX idx_hotel_closed_enabled_aux ON Hotel (IsClosed, Enabled, AuxiliaryId) INCLUDE (HotelId, Name);
  
  -- 旧版本MySQL不支持INCLUDE，就把需要的字段都加入索引
  CREATE INDEX idx_hotel_closed_enabled_aux ON Hotel (IsClosed, Enabled, AuxiliaryId, HotelId, Name);
  

• 不建议创建(HotelId, IsClosed, Enabled)这样的索引，因为你的WHERE条件是先过滤IsClosed和Enabled，而索引的最左前缀是HotelId，MySQL无法利用这个索引来过滤前面的条件，相当于索引起不到作用。

2. HotelRoom表的索引 #

HotelRoom表通过HotelId关联Hotel，同时过滤Deleted=0和Enabled=1，需要返回RoomId, Name：

• 索引的最左列必须是HotelId（因为关联查询时会先按HotelId匹配），然后是过滤条件Deleted, Enabled，最后加上需要查询的字段做覆盖索引：

  -- MySQL 8.0+版本
  CREATE INDEX idx_room_hotel_deleted_enabled ON HotelRoom (HotelId, Deleted, Enabled) INCLUDE (RoomId, Name);
  
  -- 旧版本MySQL
  CREATE INDEX idx_room_hotel_deleted_enabled ON HotelRoom (HotelId, Deleted, Enabled, RoomId, Name);
  

这样设计的话，关联Hotel时可以快速定位到对应的HotelRoom行，同时过滤条件也能利用索引，还不需要回表查询数据。

二、关于FIND_IN_SET条件的索引问题 #

你新增的IF(LENGTH(TRIM(sAuxiliaryIds)) > 0 AND sAuxiliaryIds IS NOT NULL, FIND_IN_SET(Hotel.AuxiliaryId, sAuxiliaryIds), 1=1)这部分，很遗憾无法利用普通B-tree索引：

• FIND_IN_SET是对逗号分隔的字符串做匹配，MySQL的B-tree索引只能处理前缀匹配、等值匹配、范围匹配这类操作，没法解析字符串里的逗号分隔值。
• 如果这个条件是性能瓶颈，我建议你做结构优化：
  1. 把AuxiliaryId改成关联表：比如创建Hotel_Auxiliary表，存储HotelId和对应的AuxiliaryId，然后用JOIN代替FIND_IN_SET，这样就能利用关联表的索引。
  2. 应用层拆分参数：如果sAuxiliaryIds是应用传入的参数（比如'1,2,3'），可以在应用层把字符串拆成数组，然后用IN条件代替FIND_IN_SET：

     AND (sAuxiliaryIds IS NULL OR LENGTH(TRIM(sAuxiliaryIds)) = 0 OR Hotel.AuxiliaryId IN (1, 2, 3))
     

     这样AuxiliaryId的索引就能被利用起来，比FIND_IN_SET高效得多。

三、为什么加了索引却没被使用？ #

你说执行EXPLAIN后发现没用到任何索引，可能是这些原因：

• 数据量太小：如果Hotel和HotelRoom表只有几百行，MySQL会觉得全表扫描比走索引更快（索引也有开销），这是正常现象。
• 索引选择性太差：比如Hotel.IsClosed=0的行占了表的90%以上，MySQL会判断走索引的收益不如全表扫，就会放弃索引。
• 统计信息过时：MySQL的优化器依赖表的统计信息来选择执行计划，如果统计信息太久没更新，可能会做出错误的选择。你可以执行以下命令更新统计信息：

  ANALYZE TABLE Hotel;
  ANALYZE TABLE HotelRoom;
  

• 索引顺序不对：如果你的索引最左列不是查询中过滤或关联的字段（比如之前提到的(HotelId, IsClosed, Enabled)），MySQL无法利用这个索引，得按我前面建议的顺序重建索引。
• MySQL版本或配置限制：比如某些旧版本的MySQL对复杂条件的索引选择逻辑不够完善，可以尝试升级到8.0+版本，或者先排查前面的原因再考虑调整配置。

最后一步：验证优化效果 #

创建好正确的索引，更新统计信息后，你可以用EXPLAIN ANALYZE（MySQL 8.0+支持）查看实际执行计划，看看索引是否被使用，以及查询的耗时有没有下降。如果还是有问题，可以把EXPLAIN的结果贴出来，进一步排查。

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