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