mysql5.6版本之前没有加入index condition pushdown,所以索引逻辑还是这样的:
即便对于复合索引,从第一列开始先确定第一列索引范围,如果范围带=号,则对于=号情况,确定第二列索引范围加入索引结果集里,每列的处理方式都是一样的。
确定完索引范围后,则回表查询数据,再用剩下的where条件进行过滤判断。
mysql5.6后加入了ICP,对于确定完了索引范围后,会用剩下的where条件对索引范围再进行一次过滤,然后再回表,再用剩下的where条件进行过滤判断。(减少回表记录数量)。
The optimizer attempts to use additional key parts to determine the interval as long as the comparison operator is =, <=>, or IS NULL. If the operator is >, <, >=, <=, !=, <>, BETWEEN, or LIKE, the optimizer uses it but considers no more key parts.For the following expression, the optimizer uses = from the first comparison. It also uses >= from the second comparison but consid