09月22日, 2014 121次
Order_recipient_extend_tab表有6000万个数据，慢查询的查询字段有start_date、station_id、status。符合指标设计初衷但实际失败的指标有：
Union索引字段1字段2字段3idx_date_station_driverstart _ date station _ iddriver _ id
要了解Mysql如何执行where条件查询，我们可以更快更清楚地看到索引失败的原因。这个慢速查询中匹配度较高的索引是idx_date_station_driver。在这个慢速查询中分析where条件查询的执行过程。 Mysql条件的提取规则可以分为三类：Index Key,Index Filter和Table Filter. 00-1010 indexkey用于确定索引树中此sql查询的范围。一个范围包括起点和终点，Index First Key用于定位索引查询的起始范围，Index Last Key用于定位索引查询的终止范围. 索引第一个键 提取规则：从索引的第一个字段，检查该字段是否存在于where条件中。如果存在且条件为=，=，则将对应条件添加到Index First Key，继续读取索引的下一个字段；如果存在，且条件为，则将对应条件添加到索引第一键，然后终止提取索引第一键；如果它不存在，索引第一键的提取也将终止。 索引最后一个键 与Index First Key相反，提取规则：从索引的第一个字段开始，检查它是否存在于where条件中；如果存在且条件为=，=，则向Index Last Key添加对应的条件，继续提取索引的下一个字段；如果存在且条件为，则将条件添加到索引最后一个键，然后终止提取；如果不存在，索引最后一个键的提取也将终止。 根据Index Key的提取规则，本次慢速查询提取的Index Last Key为：start_date 1628442000 ，Index Last Key为：start_date 1631120399 。 “索引第一键”仅用于定位索引的起始范围。使用索引第一键条件，从索引B树的根节点开始，使用二分搜索法方法快速索引到正确的叶节点位置。在Where查询过程中，Index First Key只做了一个判断。 Index Last Key用于定位索引的结束范围，因此需要判断在开始范围之后读取的每个索引记录是否已经超出了Index Last Key的范围，如果是，则当前查询结束。
Where条件查询执行过程
4 Index Filter
在Index Key确定的索引范围中，并不是所有的索引记录都满足查询条件。比如Index Last Key和Index Last Key范围中，不是所有索引记录都满足 station_id = 1809 。这个时候就需要用到Index Filter了。
Index Filter，又名索引下推，用于过滤索引查询范围中不满足查询条件的记录。对于索引范围中的每一条记录，均需要与Index Filter进行对比，若不满足Index Filter则直接丢弃，继续读取索引下一条记录。
Index Filter的提取规则：从索引的第一个字段开始，检查其在where条件中是否存在，若存在且条件仅为 =，则跳过第一字段继续检查索引下一字段，下一索引列采取相同的提取规则（解释：条件为=的字段已经在Index Key中过滤掉了）；若存在且条件为 =、 、 、 = 其中的几种，则跳过当前索引字段，将其余where条件中索引相关字段全部加入到Index Filter之中。
按照Index Filter的提取规则，在此次慢查询中提取出来的Index Filter为：station_id= 1809 。在Index Key确定的索引查询范围中，遍历索引记录时都需要比较 station_id= 1809 ，不满足该条件则直接丢失，继续读取索引下一条记录。
Table FilterTable Filter用于过滤掉索引无法过滤的数据。在二级索引中通过主键回表查询到整行记录后，判断该记录是否符合Table Filter条件，不符合则丢失，继续判断下一条记录。
提取规则很简单：所有不属于索引字段的查询条件，均归为Table Filter之中。按照Table Filter的提取规则，在此次查询中Table Filter为：status=‘2’。
总结和补充Index Key用于确定索引扫描的范围；Index Filter用于在索引中进行过滤；Table Filter需要回表后在Mysql服务器进行过滤。
Index Key和Index Filter发生在InnoDB存储层，Table Filter发生在Mysql Server层。
在 MySQL5.6 之前，并不区分Index Filter与Table Filter，统统将Index First Key与Index Last Key范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进行过滤。
在MySQL 5.6及之后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的存储引擎层进行过滤，减少了回表与返回MySQL Server层的记录交互开销，提高了SQL的执行效率。
分析索引失效原因首先是count()，此时通配符 * 经优化并不会拓展所有列，实际上会忽略所有的列直接统计行数。所以只想收集行数最好使用count()。
接下来分析where语句。假设此慢查询会使用了二级索引idx_date_station_driver，按照上面where条件查询的执行过程，该慢查询的Index First Key为start_date 1628442000 ，Index Last Key为： start_date 1631120399 ，Index Filter为：station_id= 1809 ，Table Filter为：status=‘2’。
提取Index First Key后在索引B+树上定位索引起始范围就是索引匹配的过程，在索引B+树上使用二分搜索方法快速定位符合查询条件的起始叶子节点。通过上文Where条件查询执行过程，我们知道该慢查询的where条件（start_date 1628442000 and start_date 1631120399 and status= 2 and station_id= 1809 ），只匹配了索引idx_date_station_driver(start_date, station_id, driver_id)的第一个字段，即只匹配了idx_date_station_driver(start_date)，station_id= 1809‘精确查询并没有作用到匹配索引上，而是在Index Filter即索引下推过程中发挥了作用。实际上这里是因为范围查询使联合索引停止匹配。
范围查询导致联合索引停止匹配为什么范围查询会使联合索引停止匹配？这里涉及到最左前缀匹配原理。假设建立一个联合索引 index(a, b)，会先对a进行排序，在a相等的情况下对b进行排序，如下图所示。在该索引树上，a是全局有序的，而b则处于全局无序、局部有序状态。从全局来看，b的值为1、2、1、4、1、2，只有 b=2 查询条件无法直接使用该索引；从局部来看，当a的值确定时，b则是有序状态，a=2 b=4可以使用该索引。所以范围查询使联合索引停止匹配的根本原因是，索引树上非首字段的有序状态依赖前一个字段相等情况，而范围查询破坏了下一个索引字段局部有序状态，导致索引停止匹配。
范围查询使联合索引停止匹配，并不能在索引匹配的时候就过滤掉 station_id不等于 1809 的数据，导致Mysql在索引上的扫描范围Index First Key和Index Last Key完全由start_timestamp_of_date时间决定。start_timestamp_of_date范围查询可以过滤73%数据量，而station_id= 1809 精确查询能过滤掉99%的数据量。