SQL优化之index merge

SELECT
	*
FROM
	FUND_LOAN_REPAY_PLAN
WHERE
	1 = 1
AND REPAY_STATUS = '002005002'
AND NOTIFY_THIRD_FLAG = 0
AND UPDATE_TIME >= '2019-07-01 00:00:01.313'
AND UPDATE_TIME <= '2019-07-02 20:00:01.313'
AND FUND_CODE = 'XYXJ'
LIMIT 0,
 1000

查看执行计划 

mysql> explain SELECT
    -> *
    -> FROM
    -> FUND_LOAN_REPAY_PLAN
    -> WHERE
    -> 1 = 1
    -> AND REPAY_STATUS = '002005002'
    -> AND NOTIFY_THIRD_FLAG = 0
    -> AND UPDATE_TIME >= '2019-07-01 00:00:01.313'
    -> AND UPDATE_TIME <= '2019-07-02 20:00:01.313'
    -> AND FUND_CODE = 'XYXJ'G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: FUND_LOAN_REPAY_PLAN
   partitions: NULL
         type: index_merge
possible_keys: IDX_REPAY_STATUS,IDX_NOTIFY_THIRD_FLAG,FUND_LOAN_REPAY_PLAN_UPDATE_TIME_index,IDX_FUND_CODE
          key: IDX_FUND_CODE,IDX_NOTIFY_THIRD_FLAG,IDX_REPAY_STATUS
      key_len: 63,2,27
          ref: NULL
         rows: 100374
     filtered: 14.67
        Extra: Using intersect(IDX_FUND_CODE,IDX_NOTIFY_THIRD_FLAG,IDX_REPAY_STATUS); Using where
1 row in set, 1 warning (0.00 sec)

可以看到执行计划中 type为index_merge，Extra额外信息中  Using intersect(IDX_FUND_CODE,IDX_NOTIFY_THIRD_FLAG,IDX_REPAY_STATUS);

关于index merge的官方文档：https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html#index-merge-intersection

博客：https://www.cnblogs.com/digdeep/archive/2015/11/18/4975977.html