SQL索引是否会导致搜索查询耗时变长？背后原因及示例有哪些？

索引的本质是通过额外的存储空间、写入时的索引维护开销，来降低查询时的检索成本。当索引检索的综合开销高于全表扫描的开销时，就会出现用索引比不用索引更慢的情况。

• 低区分度字段的单列索引
  当索引字段的取值非常少，符合查询条件的行数占总表比例超过15%左右时，走索引需要先遍历索引树拿到主键、再回表查询完整数据的随机IO开销，会远高于全表扫描的顺序IO开销。
  示例：100万行的用户表，为仅存3种取值的gender字段建普通索引，执行SELECT * FROM user WHERE gender = '男'时，走索引的耗时通常是全表扫描的2~3倍。
• 小表上的非主键索引
  行数不足1000行的小表，全表扫描的开销本身极低，如果额外建立多个索引，不仅写入时要同步维护所有索引拉高写入耗时，查询时走索引的寻址+回表开销也会高于直接遍历全表的开销。
• 存在隐式类型转换的索引
  如果查询时的写法导致索引字段被函数包裹或者发生隐式类型转换，会直接导致索引失效，数据库不仅要走全表扫描，还要额外承担类型转换/函数计算的开销，耗时比普通全表扫描更高。
  示例：字符串类型的user_id字段建有索引，执行SELECT * FROM user WHERE user_id = 12345时，数据库会对所有user_id字段做字符串转数字的转换，索引完全失效。
• 不符合最左匹配原则的联合索引
  联合索引遵循最左匹配规则，当查询条件完全不包含联合索引的最左字段时，索引无法被命中，数据库在查询优化阶段还会多一步索引适配判断的开销，整体耗时略高于直接全表扫描。

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