MySQL Bugs: #81250: sql plan is not right according to optimizer

Bug #81250	sql plan is not right according to optimizer_trace
Submitted:	29 Apr 2016 19:57	Modified:	16 Nov 2016 15:59
Reporter:	ashe sun (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	mysql 5.7.9, 5.7.12	OS:	Linux (centos6.5)
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer

Description:
###################################################################################
this is the table structure
mysql> show create table repay_flow\G
*************************** 1. row ***************************
       Table: repay_flow
Create Table: CREATE TABLE `repay_flow` (
  `id` bigint(64) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(20) NOT NULL DEFAULT ' ',
  `link_uuid` varchar(20) NOT NULL DEFAULT ' ' COMMENT '链条ID',
  `laundry_uuid` varchar(20) NOT NULL DEFAULT ' ' COMMENT '流水ID',
  `from_user` varchar(20) NOT NULL DEFAULT ' ' COMMENT '还款方',
  `to_user` varchar(20) NOT NULL DEFAULT ' ' COMMENT '接收方',
  `total_amount` varchar(20) NOT NULL DEFAULT '0' COMMENT '还款总金额＝金额＋逾期利息',
  `amount` varchar(20) NOT NULL DEFAULT '0' COMMENT '金额',
  `principal` varchar(20) NOT NULL DEFAULT '0' COMMENT '本金',
  `interest` varchar(20) NOT NULL DEFAULT '0' COMMENT '利息',
  `overdue_interest` varchar(20) NOT NULL DEFAULT '0' COMMENT '逾期利息',
  `rate` varchar(20) NOT NULL DEFAULT '0' COMMENT '利率',
  `overdue_day` smallint(6) DEFAULT NULL,
  `parent_repayed_amount` varchar(20) NOT NULL DEFAULT '0' COMMENT '上级以还金额',
  `parent_repay_amount` varchar(20) NOT NULL DEFAULT '0' COMMENT '上级应还金额',
  `sequence` tinyint(4) NOT NULL DEFAULT '1' COMMENT '序列',
  `pay_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '支付状态',
  `product_code` varchar(255) NOT NULL DEFAULT ' ' COMMENT '产品CODE',
  `bid_uuid` varchar(30) DEFAULT NULL,
  `tbid_uuid` varchar(30) DEFAULT NULL,
  `interest_day` smallint(6) DEFAULT NULL,
  `repay_amount` varchar(20) NOT NULL DEFAULT '0' COMMENT '还款金额',
  `link_count` tinyint(4) NOT NULL DEFAULT '1' COMMENT '链条数量',
  `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '起始时间',
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '结束时间',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `parent_repay_user` varchar(20) NOT NULL DEFAULT '0' COMMENT '上级还款用户',
  `delay_interest` varchar(20) NOT NULL DEFAULT '0' COMMENT '延期利息',
  `delay_day` tinyint(4) NOT NULL DEFAULT '0' COMMENT '延期天数',
  `overdue_rate` varchar(20) NOT NULL DEFAULT '0' COMMENT '逾期利率',
  `parent_uuid` varchar(35) NOT NULL DEFAULT '0' COMMENT '上级节点',
  `product_create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '产品发布时间',
  `call_version` varchar(15) DEFAULT ' ' COMMENT '还款版本号',
  `law_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '诉讼状态 (0:无  1:有)',
  `law_time` timestamp NOT NULL DEFAULT '2015-01-01 00:00:00' COMMENT '用户发起诉讼的时间',
  `original_principal` varchar(25) DEFAULT '0' COMMENT '原始本金',
  `type` tinyint(4) DEFAULT '0' COMMENT '类型',
  `gurantee_uuid` varchar(36) DEFAULT '' COMMENT '担保人uuid',
  `original_interest` varchar(36) DEFAULT '0' COMMENT '原始利息',
  `source_bid_uuid` varchar(32) DEFAULT '' COMMENT '原产品uuid',
  PRIMARY KEY (`id`),
  KEY `index_to_user` (`to_user`),
  KEY `index_link_uuid` (`link_uuid`),
  KEY `index_uuid` (`uuid`),
  KEY `index_update_time` (`update_time`),
  KEY `index_parentUuid` (`parent_uuid`),
  KEY `index_endTime` (`end_time`),
  KEY `index_tbid_uuid` (`tbid_uuid`),
  KEY `index_bid_uuid` (`bid_uuid`),
  KEY `index_source_bid_uuid` (`source_bid_uuid`),
  KEY `index_from_user_to_user` (`from_user`,`to_user`)
) ENGINE=InnoDB AUTO_INCREMENT=10664705 DEFAULT CHARSET=utf8 COMMENT='还款流水'
1 row in set (0.00 sec)

####################################################################################

this is the sql executing 
mysql> explain select          id,uuid,link_uuid,laundry_uuid,from_user,to_user,amount,principal,interest,sequence,pay_status,product_code,         start_time,end_time,bid_uuid,tbid_uuid,interest_day,repay_amount,link_count,create_time,update_time,         parent_repay_amount,parent_repayed_amount,total_amount,overdue_interest,overdue_day,rate,parent_repay_user,         delay_day,delay_interest,overdue_rate,parent_uuid,product_create_time,law_status,law_time, call_version,         original_principal, original_interest, gurantee_uuid, type, source_bid_uuid          from repay_flow  where from_user='499200489766955660' and overdue_day=3 and pay_status=4       and id>0  order by uuid desc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: repay_flow
   partitions: NULL
         type: index
possible_keys: PRIMARY,index_from_user_to_user
          key: index_uuid
      key_len: 62
          ref: NULL
         rows: 2650
     filtered: 0.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

###################################################################################
and it is not suitably here，so see the trace to get the reason for this plan :

              "resulting_clause": "`repay_flow`.`uuid` desc"
            }
          },
          {
            "added_back_ref_condition": "((`repay_flow`.`from_user` <=> '499200489766955660') and ((`repay_flow`.`pay_status` = 4) and (`repay_flow`.`overdue_day` = 3) and (`repay_flow`.`id` > 0)))"
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",         # here ,mysql reconsider the order by clause.
              "index_order_summary": {
                "table": "`repay_flow`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "index_from_user_to_user",
                "plan_changed": false     # and it does not change the plan ,that menns the optimizer will user the index(index_from_user_to_user)!
              }
            }
          },
          {
            "refine_plan": [
              {
                "table": "`repay_flow`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

this may be a bug of mysql5.7.9  or some other version 5.7.*

How to repeat:
987552848@qq.com

sunchuan@actionsky.com

mysql5.7.9 
sql plan is mot according with  optimizer_trace

important

Hello Ashe sun!

Thank you for the report.
Could you please provide subset of data to reproduce this issue at our end? You may want mark it as private after uploading.

Thanks,
Umesh

Thank you for the details.
Observed this with 5.7.12 build as well.

Thanks for your reply,thank you. 

I wish you a happy work.

Posted by developer:
 
Noted in 5.7.17, 8.0.1 changelogs.

For a query with ORDER BY and LIMIT, an optimizer trace did not
record the optimizer's switch to a different index.