mysql多表关联字符集不同导致全表扫描的优化案例

select m.tenant_id, m.task_no, m.travel_bill_no, m.supplier_no, m.cost_attribution_name, m.cost_attribution_id, m.corporation_id, m.corporation_name, bill.supplier_order_no, m.supplier_original_order_no, m.order_no, m.original_order_no, m.business_type, m.order_trade_type, m.entry_bill_time_type, m.entry_bill_time, m.bill_amount, m.booker_name, m.booker_phone, m.traveler_org_code, m.traveler_org_name, m.trip_code, m.trip_type_code, m.traveler_user_num, m.traveler_name, m.traveler_phone, m.vip_tag, m.cost_attribution_type, m.cost_unit_id, m.cost_unit_name, m.external_info, m.cost_attribution_type, m.book_time, s.hotel_city_name, s.hotel_name, s.room_type_name, s.reserve_num, s.check_in_date, s.check_out_date, s.reserve_days, s.room_count, m.pay_flow_no, m.pay_channel,s.room_amount_rate, s.assess_room_amount,s.assess_service_fee, m.pay_type, m.booker_user_id, m.traveler_user_id, m.trip_plan_code, m.trip_apply_code,m.trip_plan_has_finished, m.cost_unit_recording_mode, s.invoice_mode,hb.extra_charge, m.cost_attribution_project_name,m.cost_attribution_project_id,m.change_reason,m.trip_reason_type, if_start_approval, violation_reason, cost_detail, topic_code, item_code from t_travel_bill bill left join t_travel_source_bill m on bill.travel_bill_no = m.travel_bill_no left join t_travel_source_hotel_bill_detail s on s.tenant_id = m.tenant_id and s.travel_bill_no = m.travel_bill_no left join t_travel_hotel_bill_detail hb on bill.travel_bill_no = hb.travel_bill_no where bill.tenant_id = '63804648' and bill.supplier_no = 'C-TRIP' and bill.business_type = '0003' and bill.supplier_order_no in ( '24771550278' , '24111769045' , '24144577225' /*... omitted 1116 items ...*/)\GALTER TABLE t_travel_source_bill ADD INDEX idx_travel_bill_no(travel_bill_no);ALTER TABLE t_travel_hotel_bill_detail ADD INDEX idx_travel_bill_no(travel_bill_no);alter table t_travel_source_hotel_bill_detail add index idx_travel_bill_no(travel_bill_no);s表的关联字段tenant_id和travel_bill_no都有索引，但是都没用到。tenant_id列的选择性不高，不走这列的索引可以理解，但是为什么travel_bill_no这列的索引也不走呢？进一步排查原因是s表的字符集时utf8mb4，而其他表的字符集是uft8，所以需要将s表的字符集改为utf8，优化后执行计划如下从2.46s优化到0.5s