复杂SQL的子查询性能问题

复杂SQL的子查询中的多表关联，执行计划中表连接顺序不一样，导致sql执行效率降低。

案例1：

问题背景：测试环境同一个SQL执行比生产的快，测试环境的数据库是上周覆盖过的。数据量大致相同，表结构相同。

生产IP：10.18.xxx  mariadb 10.1.18

测试IP：10.16.xxx mariadb 10.1.11

数据库是： midea_gls

具体SQL如下所示：

select * from ( select goy.is_replacement_order, goy.ycon_no,go.booking_req_no,go.export_org_name,go.salesman_name,go.shipping_personnel_name,gtyr.warehouse_name,gsn.delivery_order_number,go.status,gtyr.ycon_status from go_order_ycon goy left join go_order go on go.order_no = goy.order_no left join go_task_ycon_refrence gtyr on gtyr.ycon_no = goy.ycon_no and gtyr.order_no =goy.order_no left join go_shipment_notice gsn on gsn.vir_container_number = gtyr.ycon_no where (goy.ycon_no ='VCH201808280034' or goy.lcl_vir_container_number = 'VCH201808280034' or goy.ycon_no in (select lcl_vir_container_number from go_order_ycon where ycon_no ='VCH201808280034') or goy.lcl_vir_container_number in (select lcl_vir_container_number from go_order_ycon where ycon_no ='VCH201808280034')) ) TEMP where TEMP.ycon_status <>'Y_110' AND TEMP.status = 'O_10'\G

关联查询的四个表如下所示，表结构和索引在两套数据库上一致。

go_order_ycon

CREATE TABLE `go_order_ycon` (

`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`id` varchar(50) NOT NULL COMMENT 'x',

`so_no` varchar(50) DEFAULT NULL COMMENT 'x-1',

`order_no` varchar(64) DEFAULT NULL COMMENT 'x',

`booking_req_detail_id` int(11) DEFAULT NULL COMMENT 'x',

`line_no` int(11) DEFAULT NULL COMMENT 'x',

`booking_req_id` int(11) DEFAULT NULL COMMENT 'x',

`booking_req_no` varchar(64) DEFAULT NULL COMMENT 'x',

`preloading_detail_id` int(11) DEFAULT NULL COMMENT 'x',

`ycon_no` varchar(64) DEFAULT NULL COMMENT 'x',

`container_type_id` varchar(32) DEFAULT NULL COMMENT 'x',

`container_type` varchar(32) DEFAULT NULL COMMENT 'x',

`expected_loading_date` datetime DEFAULT NULL COMMENT 'x',

`loading_type_code` varchar(64) DEFAULT NULL COMMENT 'x',

`container_box` varchar(32) DEFAULT NULL COMMENT 'x',

`container_size` varchar(32) DEFAULT NULL COMMENT 'x',

`marks` varchar(300) DEFAULT NULL COMMENT 'x',

`goods_description` varchar(3000) DEFAULT NULL COMMENT 'x',

`currency` varchar(32) DEFAULT NULL COMMENT 'x',

`weight` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`volume` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`box_qty` int(11) DEFAULT '0' COMMENT 'x',

`pi_no` varchar(64) DEFAULT NULL COMMENT 'x',

`pi_amount` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`pi_qty` int(11) DEFAULT NULL COMMENT 'x',

`lcl_number` varchar(32) DEFAULT NULL COMMENT 'x',

`customer_po_no` varchar(2000) DEFAULT NULL COMMENT 'x',

`is_dept_lcl` varchar(8) DEFAULT NULL COMMENT 'x',

`unit` varchar(32) DEFAULT NULL COMMENT 'x-1',

`ycon_type` varchar(64) DEFAULT NULL COMMENT 'x-1',

`ycon_amount` int(11) DEFAULT '0' COMMENT 'x-1',

`customer_type` varchar(32) DEFAULT NULL COMMENT 'x-1',

`product_code` varchar(32) DEFAULT NULL COMMENT 'x-1',

`factory_model` varchar(32) DEFAULT NULL COMMENT 'x-1',

`gross_weight` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`tare_weight` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`total_gross_weight` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`packing` varchar(8) DEFAULT NULL COMMENT 'x:箱/套-1',

`number_expected` int(11) DEFAULT NULL COMMENT 'x-1',

`vgm_type` varchar(32) DEFAULT NULL COMMENT 'x',

`vgm_unit` varchar(128) DEFAULT NULL COMMENT 'x',

`vgm_responsible` varchar(128) DEFAULT NULL COMMENT 'x',

`authorizer` varchar(32) DEFAULT NULL COMMENT 'x',

`remarks` varchar(128) DEFAULT NULL COMMENT 'x',

`remarks1` varchar(128) DEFAULT NULL COMMENT 'x',

`remarks2` varchar(128) DEFAULT NULL COMMENT 'x',

`rec_status` tinyint(1) DEFAULT '0' COMMENT 'x',

`create_time` datetime DEFAULT NULL COMMENT 'x',

`creator` varchar(36) DEFAULT NULL COMMENT 'x',

`modify_time` datetime DEFAULT NULL COMMENT 'x',

`MODIFIER` varchar(36) DEFAULT NULL COMMENT 'x',

`company_code` varchar(32) DEFAULT NULL COMMENT 'x',

`tenant_code` varchar(32) DEFAULT NULL COMMENT 'x',

`recVer` int(11) DEFAULT NULL COMMENT 'x',

`customer_po_no_detail` varchar(1000) DEFAULT NULL COMMENT 'x',

`mo_no_detail` varchar(3000) DEFAULT NULL COMMENT 'x',

`lcl_preloading_detail_id` int(11) DEFAULT NULL COMMENT 'x',

`lcl_vir_container_number` varchar(64) DEFAULT NULL COMMENT 'x',

`is_replacement_order` varchar(2) DEFAULT NULL COMMENT 'x',

`lcl_booking_req_id` int(11) DEFAULT NULL COMMENT 'x',

`lcl_booking_req_no` varchar(64) DEFAULT NULL COMMENT 'x',

`lcl_booking_order_id` int(11) DEFAULT NULL COMMENT 'x',

`lcl_booking_order_no` varchar(64) DEFAULT NULL COMMENT 'x',

`order_con_count_id` varchar(32) DEFAULT NULL COMMENT 'x',

`description` varchar(255) DEFAULT NULL COMMENT 'x',

`need_exam_flag` varchar(64) DEFAULT NULL COMMENT 'x',

`monitor_install_flag` varchar(64) DEFAULT NULL COMMENT 'x',

`need_exam_install_flag` varchar(64) DEFAULT NULL COMMENT 'x',

`is_dangerous_goods` varchar(2) DEFAULT NULL COMMENT 'x',

PRIMARY KEY (`_id`),

UNIQUE KEY `id` (`id`),

KEY `idx_gooy_order_no` (`order_no`),

KEY `idx_booking_req_detail_id` (`booking_req_detail_id`),

KEY `idx_lcl_vir_container_number` (`lcl_vir_container_number`),

KEY `idx_ycon_no` (`ycon_no`)

) ENGINE=InnoDB AUTO_INCREMENT=1449412 DEFAULT CHARSET=utf8 COMMENT='预排柜'；

go_order

CREATE TABLE `go_order` (

`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'x',

`id` varchar(50) NOT NULL COMMENT 'x',

`an_no` varchar(64) DEFAULT NULL COMMENT 'x',

`order_no` varchar(64) NOT NULL COMMENT 'x',

`order_type_code` varchar(64) DEFAULT NULL COMMENT 'x',

`customer_tips` varchar(8) DEFAULT NULL COMMENT 'x（0内部/x）',

`booking_req_id` int(11) DEFAULT NULL COMMENT 'x',

`booking_req_no` varchar(64) DEFAULT NULL COMMENT 'x',

`status` varchar(16) DEFAULT NULL COMMENT 'x',

`iflow_creator` varchar(255) DEFAULT NULL COMMENT 'x',

`export_org_id` int(11) DEFAULT NULL COMMENT 'x',

`export_org_name` varchar(32) DEFAULT NULL COMMENT 'x-1',

`export_org_area` varchar(16) DEFAULT NULL COMMENT 'x（1：华东，2：华南）',

`transit_org_id` int(11) DEFAULT NULL COMMENT 'x',

`transit_org_name` varchar(32) DEFAULT NULL COMMENT 'x-1',

`shp_notice_id` int(11) DEFAULT NULL COMMENT 'x',

`shp_notice_number` varchar(32) DEFAULT NULL COMMENT 'x',

`customer_id` int(11) DEFAULT NULL COMMENT 'x',

`customer_code` varchar(32) DEFAULT NULL COMMENT 'x',

`customer_name` varchar(128) DEFAULT NULL COMMENT 'x-1',

`customer_abbre` varchar(255) DEFAULT NULL COMMENT 'x',

`finance_warehouse_code` varchar(32) DEFAULT NULL COMMENT 'x',

`finance_warehouse_name` varchar(32) DEFAULT NULL COMMENT 'x',

`customer_delivery_address_id` int(11) DEFAULT NULL COMMENT 'x',

`customer_delivery_address` varchar(240) DEFAULT NULL COMMENT 'x',

`bill_data_id` int(11) DEFAULT NULL COMMENT 'x',

`departure_port_id` int(11) DEFAULT NULL COMMENT 'x/x',

`departure_port_code` varchar(32) DEFAULT NULL COMMENT 'x',

`destination_port_id` int(11) DEFAULT NULL COMMENT 'x/x',

`destination_port_code` varchar(32) DEFAULT NULL COMMENT 'x',

`destination` varchar(512) DEFAULT NULL COMMENT 'x',

`sea_route` varchar(128) DEFAULT NULL COMMENT 'x',

`sw_bw_type` varchar(16) DEFAULT NULL COMMENT 'x',

`expected_loading_date` datetime DEFAULT NULL COMMENT 'x',

`lc_lastest_ship_date` datetime DEFAULT NULL COMMENT 'x',

`payment_term_id` int(11) DEFAULT NULL COMMENT 'x',

`payment_term` varchar(64) DEFAULT NULL COMMENT 'x',

`picked_up_flag` varchar(11) DEFAULT NULL COMMENT 'x',

`picked_up_phone_number` varchar(32) DEFAULT NULL COMMENT 'x',

`picked_up_plate_number` varchar(32) DEFAULT NULL COMMENT 'x',

`region` varchar(64) DEFAULT NULL COMMENT 'x',

`salesman_name` varchar(64) DEFAULT NULL COMMENT 'x',

`salesman_id` int(11) DEFAULT NULL COMMENT 'x',

`salesman_tel` varchar(64) DEFAULT NULL COMMENT 'x',

`annto_salesman_code` varchar(32) DEFAULT NULL COMMENT 'x',

`annto_salesman_name` varchar(32) DEFAULT NULL COMMENT 'x',

`unit_id` int(11) DEFAULT NULL COMMENT 'x',

`sales_unit_id` int(11) DEFAULT NULL COMMENT 'x',

`shipping_personnel_id` int(11) DEFAULT NULL COMMENT 'x',

`shipping_personnel_code` varchar(64) DEFAULT NULL COMMENT 'x',

`shipping_personnel_name` varchar(64) DEFAULT NULL COMMENT 'x',

`doc_personnel_id` int(11) DEFAULT NULL COMMENT 'x',

`customs_port_code` varchar(32) DEFAULT NULL COMMENT 'x',

`customs_port_name` varchar(32) DEFAULT NULL COMMENT 'x',

`transport_type_code` varchar(33) DEFAULT NULL COMMENT 'x',

`transport_company_id` int(11) DEFAULT NULL COMMENT 'x',

`transport_company_code` varchar(32) DEFAULT NULL COMMENT 'x',

`transport_company_name` varchar(256) DEFAULT NULL COMMENT 'x',

`transport_company_contact_name` varchar(32) DEFAULT NULL COMMENT 'x',

`transport_company_contact_id` int(11) DEFAULT NULL COMMENT 'x',

`shipping_company_contract_no` varchar(64) DEFAULT NULL COMMENT 'x',

`monitor_install_flag` varchar(8) DEFAULT NULL COMMENT 'x',

`need_exam_flag` varchar(8) DEFAULT NULL COMMENT 'x',

`need_exam_install_flag` varchar(8) DEFAULT NULL COMMENT 'x',

`exam_date` datetime DEFAULT NULL COMMENT 'x',

`charged_weight` decimal(20,2) DEFAULT NULL COMMENT 'x',

`weight` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`volume` decimal(20,4) DEFAULT '0.0000' COMMENT 'x',

`qty` int(11) DEFAULT '0' COMMENT 'x',

`notify_party2` varchar(1024) DEFAULT NULL COMMENT 'x',

`is_partial_delivery` varchar(8) DEFAULT NULL COMMENT 'x',

`additional_notes` varchar(1000) DEFAULT NULL COMMENT 'x',

`loading_type_code` varchar(32) DEFAULT NULL COMMENT 'x（普通装柜、下线装柜、预装柜）',

`loading_form_code` varchar(32) DEFAULT NULL COMMENT 'x',

`receiver` varchar(256) DEFAULT NULL COMMENT 'x',

`receiver_address` varchar(512) DEFAULT NULL COMMENT 'x',

`annto_customer_code` varchar(32) DEFAULT NULL COMMENT 'x',

`annto_customer_name` varchar(64) DEFAULT NULL COMMENT 'x',

`systemsingle_date` datetime DEFAULT NULL COMMENT 'x（x）',

`customer_contactor` varchar(32) DEFAULT NULL COMMENT 'x',

`customer_contactor_tel` varchar(16) DEFAULT NULL COMMENT 'x',

`service_type` varchar(8) DEFAULT NULL COMMENT 'x',

`business_date` datetime DEFAULT NULL COMMENT 'x',

`is_transfer_flag` varchar(8) DEFAULT NULL COMMENT 'x,

`big_ship_company` varchar(32) DEFAULT NULL COMMENT 'x',

`booking_company` varchar(32) DEFAULT NULL COMMENT 'x(x)',

`expect_begin_date` datetime DEFAULT NULL COMMENT 'x',

`expect_end_date` datetime DEFAULT NULL COMMENT 'x',

`cargo_port_id` int(11) DEFAULT NULL COMMENT 'x',

`cargo_port_code` varchar(32) DEFAULT NULL COMMENT 'x',

`transfer_port_id` int(11) DEFAULT NULL COMMENT 'x',

`transfer_port_code` varchar(16) DEFAULT NULL COMMENT 'x',

`entrust_matters` varchar(64) DEFAULT NULL COMMENT 'x(x)',

`customer_prepay` varchar(32) DEFAULT NULL COMMENT 'x',

`shipment_prepay` varchar(32) DEFAULT NULL COMMENT '船运公司预/到付',

`order_time` datetime DEFAULT NULL COMMENT 'x',

`route_time` varchar(32) DEFAULT NULL COMMENT 'x',

`disbursement` varchar(16) DEFAULT NULL COMMENT 'x',

`refrigerant_motor_oil` varchar(16) DEFAULT NULL COMMENT 'x',

`application_pure_spell_box` varchar(128) DEFAULT NULL COMMENT 'x',

`transport_provision` varchar(32) DEFAULT NULL COMMENT 'x',

`port_destination` varchar(64) DEFAULT NULL COMMENT 'x',

`cargo_port_name` varchar(64) DEFAULT NULL COMMENT 'x-1',

`status_name` varchar(32) DEFAULT NULL COMMENT 'x-1',

`shp_notice_name` varchar(32) DEFAULT NULL COMMENT 'x-1',

`order_type_name` varchar(64) DEFAULT NULL COMMENT 'x-1',

`destination_port_name` varchar(256) DEFAULT NULL COMMENT 'x',

`departure_port_name` varchar(256) DEFAULT NULL COMMENT 'x',

`applicant` varchar(32) DEFAULT NULL COMMENT 'x-1',

`cust_delegate_no` varchar(32) DEFAULT NULL COMMENT 'x',

`d_region` varchar(64) DEFAULT NULL COMMENT 'x-1',

`consignor` varchar(128) DEFAULT NULL COMMENT 'x',

`consignor_address` varchar(300) DEFAULT NULL COMMENT 'x',

`notify_party` varchar(1024) DEFAULT NULL COMMENT 'x',

`plan_shipment_quantity` varchar(50) DEFAULT NULL COMMENT 'x',

`ocean_freight_price` decimal(10,4) DEFAULT '0.0000' COMMENT 'x(PI)',

`currency` varchar(8) DEFAULT NULL COMMENT 'x',

`receive_date` datetime DEFAULT NULL COMMENT 'x',

`work_require` varchar(32) DEFAULT NULL COMMENT 'x',

`hang_up_status` varchar(32) DEFAULT NULL COMMENT 'x',

`hang_up_reason` varchar(64) DEFAULT NULL COMMENT 'x',

`hang_up_opt_time` datetime DEFAULT NULL COMMENT 'x',

`remarks` varchar(2000) DEFAULT NULL COMMENT 'x',

`remark1` text COMMENT 'x',

`remark2` varchar(128) DEFAULT NULL COMMENT 'x',

`rec_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'x',

`create_time` datetime DEFAULT NULL COMMENT 'x',

`creator` varchar(36) DEFAULT NULL COMMENT 'x',

`modify_time` datetime DEFAULT NULL COMMENT 'x',

`MODIFIER` varchar(36) DEFAULT NULL COMMENT 'x',

`company_code` varchar(32) DEFAULT NULL COMMENT 'x',

`tenant_code` varchar(32) DEFAULT NULL COMMENT 'x',

`recVer` int(11) DEFAULT NULL COMMENT 'x',

`send_express_status` varchar(8) DEFAULT NULL COMMENT 'x',

`express_pic_url` varchar(512) DEFAULT NULL COMMENT 'x',

`salesman_code` varchar(64) DEFAULT NULL COMMENT 'x',

`doc_personnel_code` varchar(64) DEFAULT NULL COMMENT 'x',

`mip_id` varchar(32) DEFAULT NULL COMMENT 'x',

`mip_status` varchar(32) DEFAULT NULL COMMENT 'x',

`quotation_id` varchar(50) DEFAULT NULL COMMENT 'x',

`charge_id` varchar(50) DEFAULT NULL COMMENT 'x',

`express_cost` decimal(10,2) DEFAULT NULL COMMENT 'x',

`insurance` varchar(8) DEFAULT NULL COMMENT 'x(Y/N)',

`good_value` decimal(10,2) DEFAULT NULL COMMENT 'x',

`customs_clearance` varchar(8) DEFAULT NULL COMMENT 'x(Y/N)',

`delivery_order_number` varchar(64) DEFAULT NULL COMMENT 'x',

`delivery_reason` varchar(128) DEFAULT NULL COMMENT 'x/x',

`trunk_flag` varchar(16) DEFAULT NULL COMMENT 'x',

`dgm_flag` varchar(16) DEFAULT NULL COMMENT 'x',

`branch_collect_flag` varchar(16) DEFAULT NULL COMMENT 'x',

`dep_assume` varchar(16) DEFAULT NULL COMMENT 'x',

`messrs` varchar(16) DEFAULT NULL COMMENT 'x',

`to_pay_flag` varchar(16) DEFAULT NULL COMMENT 'x',

`consignee_company_name` varchar(128) DEFAULT NULL COMMENT 'x',

`consignee_company_code` varchar(32) DEFAULT NULL COMMENT 'x',

`consignee_person_name` varchar(128) DEFAULT NULL COMMENT 'x',

`consignee_person_code` varchar(32) DEFAULT NULL COMMENT 'x',

`consignee_phone_number` varchar(32) DEFAULT NULL COMMENT 'x',

`consignee_address_line1` varchar(256) DEFAULT NULL COMMENT 'x',

`shipper_company_name` varchar(128) DEFAULT NULL COMMENT 'x（x）',

`shipper_company_code` varchar(32) DEFAULT NULL COMMENT 'x（x）',

`shipper_person_name` varchar(128) DEFAULT NULL COMMENT 'x',

`shipper_person_code` varchar(32) DEFAULT NULL COMMENT 'x',

`shipper_phone_number` varchar(32) DEFAULT NULL COMMENT 'x',

`shipper_address_line1` varchar(256) DEFAULT NULL COMMENT 'x',

`notifier_company_name` varchar(128) DEFAULT NULL COMMENT 'x',

`notifier_company_code` varchar(32) DEFAULT NULL COMMENT 'x',

`notifier_person_name` varchar(128) DEFAULT NULL COMMENT 'x',

`notifier_person_code` varchar(32) DEFAULT NULL COMMENT 'x',

`notifier_phone_number` varchar(32) DEFAULT NULL COMMENT 'x',

`notifier_address_line1` varchar(256) DEFAULT NULL COMMENT 'x',

`notifier_specialmsg` varchar(128) DEFAULT NULL COMMENT 'x',

`consignee_specialmsg` varchar(128) DEFAULT NULL COMMENT 'x',

`position_requirements` varchar(128) DEFAULT NULL COMMENT 'x',

`board_requirements` varchar(128) DEFAULT NULL COMMENT 'x',

`make_requirements` varchar(128) DEFAULT NULL COMMENT 'x',

`parcel_number` int(11) DEFAULT NULL COMMENT 'x',

`is_canel` varchar(32) DEFAULT NULL COMMENT 'x',

`big_ship_company_name` varchar(128) DEFAULT NULL COMMENT 'x',

`destination_name` varchar(128) DEFAULT NULL COMMENT 'x',

`doc_personnel_name` varchar(128) DEFAULT NULL COMMENT 'x',

`booking_company_name` varchar(128) DEFAULT NULL COMMENT 'x',

`transfer_port_name` varchar(128) DEFAULT NULL COMMENT 'x',

`destination_code` varchar(255) DEFAULT NULL COMMENT 'x',

`invoice_title` varchar(128) DEFAULT NULL COMMENT 'x',

`ship_name` varchar(32) DEFAULT NULL COMMENT 'x',

`ship_count` varchar(32) DEFAULT NULL COMMENT 'x',

`closing_date` datetime DEFAULT NULL COMMENT 'x',

`customer_contactor_mail` varchar(64) DEFAULT NULL COMMENT 'x',

`service_provision_id` varchar(32) DEFAULT NULL COMMENT 'x',

`service_provision` varchar(32) DEFAULT NULL COMMENT 'x',

`haulage` varchar(100) DEFAULT NULL COMMENT 'x',

`haulage_remarks` varchar(64) DEFAULT NULL COMMENT 'x',

`fee_delivery_place` varchar(32) DEFAULT NULL COMMENT 'x',

`goods_ready_date` datetime DEFAULT NULL COMMENT 'x',

`goods_type_name` varchar(32) DEFAULT NULL COMMENT 'x',

`customs_code` varchar(32) DEFAULT NULL COMMENT 'x',

`customs_name` varchar(300) DEFAULT NULL COMMENT 'x',

`mark` varchar(300) DEFAULT NULL COMMENT 'x',

`clean_demand` varchar(256) DEFAULT NULL COMMENT 'x',

`description` varchar(300) DEFAULT NULL COMMENT 'x',

`goods_type` varchar(32) DEFAULT NULL COMMENT 'x',

PRIMARY KEY (`_id`),

UNIQUE KEY `id` (`id`),

KEY `idx_booking_req_no` (`booking_req_no`),

KEY `idx_order_no` (`order_no`),

KEY `booking_req_id` (`booking_req_id`),

KEY `idx_shipping_personnel_code` (`shipping_personnel_code`),

KEY `idx_export_org_id` (`export_org_id`),

KEY `idx_salesman_name` (`salesman_name`),

KEY `idx_annto_customer_code` (`annto_customer_code`),

KEY `idx_order_type_code` (`order_type_code`),

KEY `status` (`status`),

KEY `index_shp_notice_id` (`shp_notice_id`)

) ENGINE=InnoDB AUTO_INCREMENT=592133 DEFAULT CHARSET=utf8 COMMENT='x'；

go_task_ycon_refrence

CREATE TABLE `go_task_ycon_refrence` (

`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`id` varchar(50) NOT NULL COMMENT 'x',

`order_no` varchar(64) DEFAULT NULL COMMENT 'x',

`task_no` varchar(64) DEFAULT NULL COMMENT 'x',

`sub_task_no` varchar(64) DEFAULT NULL COMMENT 'x',

`vamp_no` varchar(64) DEFAULT NULL COMMENT 'x',

`booking_no` varchar(32) DEFAULT NULL COMMENT 'x',

`booking_req_detail_id` int(11) DEFAULT NULL COMMENT 'x',

`ycon_no` varchar(64) DEFAULT NULL COMMENT 'x',

`ycon_id` varchar(40) DEFAULT NULL COMMENT 'x',

`container_type` varchar(32) DEFAULT NULL COMMENT 'x',

`ycon_status` varchar(8) DEFAULT NULL COMMENT 'x',

`is_vir_cancel` varchar(8) DEFAULT NULL COMMENT 'x',

`vir_cancel_status` varchar(8) DEFAULT NULL COMMENT 'x',

`ycon_status_name` varchar(32) DEFAULT NULL COMMENT 'x-1',

`so_no` varchar(256) DEFAULT NULL COMMENT 'x',

`so_serial_number` varchar(256) DEFAULT NULL COMMENT 'x',

`ships_name` varchar(256) DEFAULT NULL COMMENT 'x',

`ships_count` varchar(32) DEFAULT NULL COMMENT 'x',

`sea_route` varchar(32) DEFAULT NULL COMMENT 'x',

`cabinet_no` varchar(32) DEFAULT NULL COMMENT 'x',

`seal_number` varchar(64) DEFAULT NULL COMMENT 'x',

`seals_no` varchar(32) DEFAULT NULL COMMENT 'x',

`counter_weight` decimal(10,4) DEFAULT '0.0000' COMMENT 'x',

`cabinet_weight` decimal(15,4) DEFAULT NULL COMMENT 'x',

`con_date` datetime DEFAULT NULL COMMENT 'x',

`out_con_date` datetime DEFAULT NULL COMMENT 'x',

`pur_con_date` datetime DEFAULT NULL COMMENT 'x（x）',

`ycon_goods_id` int(11) DEFAULT NULL COMMENT 'x',

`so_number` varchar(256) DEFAULT NULL COMMENT 'x',

`actual_container_number` varchar(32) DEFAULT NULL COMMENT 'x',

`promised_cabin` varchar(8) DEFAULT NULL COMMENT 'x(0：x)',

`accrued_truck_fee` decimal(11,0) DEFAULT NULL COMMENT 'x',

`group_no` varchar(32) DEFAULT NULL COMMENT 'x',

`is_both_load` varchar(8) DEFAULT NULL COMMENT 'x',

`platform_id` int(11) DEFAULT NULL COMMENT 'x',

`is_completed` varchar(8) DEFAULT NULL COMMENT 'x',

`superwised_warehouse_name` varchar(64) DEFAULT NULL COMMENT 'x',

`delivery_bill_no` int(11) DEFAULT NULL COMMENT 'x',

`warehouse_cabinet_status` varchar(8) DEFAULT NULL COMMENT 'x',

`port_loading_status` varchar(8) DEFAULT NULL COMMENT 'x',

`depot_time` datetime DEFAULT NULL COMMENT 'x',

`depot_time_slot` varchar(8) DEFAULT NULL COMMENT 'x',

`barge_vessel_code` varchar(32) DEFAULT NULL COMMENT 'x',

`truck_company` varchar(32) DEFAULT NULL COMMENT 'x',

`motorcade_no` varchar(32) DEFAULT NULL COMMENT 'x',

`motorcade_name` varchar(32) DEFAULT NULL COMMENT 'x',

`plate_number` varchar(32) DEFAULT NULL COMMENT 'x',

`truck_driver_name` varchar(32) DEFAULT NULL COMMENT 'x',

`truck_driver_tel` varchar(32) DEFAULT NULL COMMENT 'x',

`shipment_notification_no` varchar(50) DEFAULT NULL COMMENT 'x',

`plan_loading_date` datetime DEFAULT NULL COMMENT 'x',

`down_ycon_date` datetime DEFAULT NULL COMMENT 'x',

`warehouse_code` varchar(32) DEFAULT NULL COMMENT 'x',

`warehouse_name` varchar(32) DEFAULT NULL COMMENT 'x',

`inventory_organization` varchar(32) DEFAULT NULL COMMENT 'x',

`finance_warehouse_code` varchar(32) DEFAULT NULL COMMENT 'x',

`finance_warehouse_name` varchar(32) DEFAULT NULL COMMENT 'x',

`warehouse_organization` varchar(32) DEFAULT NULL COMMENT 'x',

`wharf_code` varchar(32) DEFAULT NULL COMMENT 'x',

`wharf_name` varchar(64) DEFAULT NULL COMMENT 'x',

`warehouse_remarks` varchar(240) DEFAULT NULL COMMENT 'x',

`load_remarks` varchar(128) DEFAULT NULL COMMENT 'x',

`cabinet_remark` varchar(64) DEFAULT NULL COMMENT 'x',

`shipment_remarks` varchar(128) DEFAULT NULL COMMENT 'x',

`barge_vessel_name` varchar(128) DEFAULT NULL COMMENT 'x',

`barge_remark` varchar(256) DEFAULT NULL COMMENT 'x',

`barge_live_date` datetime DEFAULT NULL COMMENT 'x',

`entry_time` datetime DEFAULT NULL COMMENT 'x',

`exit_time` datetime DEFAULT NULL COMMENT 'x',

`closing_date` datetime DEFAULT NULL COMMENT 'x',

`cargo_time` datetime DEFAULT NULL COMMENT 'cargo_time',

`engage_date` datetime DEFAULT NULL COMMENT 'x',

`schedule_period` varchar(16) DEFAULT NULL COMMENT '安排时段',

`is_ship_seal` varchar(8) DEFAULT NULL COMMENT 'x',

`not_seal_reason` varchar(128) DEFAULT NULL COMMENT 'x',

`lsw_update_date` datetime DEFAULT NULL COMMENT 'x',

`lsp_update_date` datetime DEFAULT NULL COMMENT 'x',

`feedback_state` varchar(8) DEFAULT NULL COMMENT 'x',

`is_booking_return` varchar(8) DEFAULT NULL COMMENT 'x',

`remarks1` varchar(128) DEFAULT NULL COMMENT '备注1',

`remarks2` varchar(128) DEFAULT NULL COMMENT '备注2',

`rec_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'x',

`create_time` datetime DEFAULT NULL COMMENT 'x',

`creator` varchar(36) DEFAULT NULL COMMENT 'x',

`modify_time` datetime DEFAULT NULL COMMENT 'x',

`modifier` varchar(36) DEFAULT NULL COMMENT 'x',

`company_code` varchar(32) DEFAULT NULL COMMENT 'x',

`annto_customer_name` varchar(64) DEFAULT NULL COMMENT 'x',

`annto_customer_code` varchar(32) DEFAULT NULL COMMENT 'x',

`tenant_code` varchar(32) DEFAULT NULL COMMENT 'x',

`recVer` int(11) DEFAULT NULL COMMENT 'x',

`ship_change_times` int(11) DEFAULT NULL COMMENT 'x',

`dispatch_time` datetime DEFAULT NULL COMMENT 'x',

`lock_rule` int(11) DEFAULT NULL COMMENT 'x',

`lock_dis_time` datetime DEFAULT NULL COMMENT 'x',

`loading_begin_time` datetime DEFAULT NULL COMMENT 'x',

`loading_end_time` datetime DEFAULT NULL COMMENT 'x',

`dc_whouse_time` datetime DEFAULT NULL COMMENT 'x',

`lc_whouse_time` datetime DEFAULT NULL COMMENT 'x',

`container_box` varchar(32) DEFAULT NULL COMMENT 'x',

`container_size` varchar(32) DEFAULT NULL COMMENT 'x',

`spell_str` varchar(16) DEFAULT 'C' COMMENT 'x',

`is_real_lcl` varchar(32) DEFAULT '0' COMMENT 'x',

`no_calculation` varchar(8) DEFAULT '0' COMMENT 'x,x',

`is_spell_cancel` varchar(32) DEFAULT NULL COMMENT 'x（N：主动）',

`cb_flag` varchar(32) DEFAULT NULL COMMENT 'x',

`edi_flag` varchar(32) DEFAULT '10' COMMENT 'x',

`is_change_ycon` varchar(128) DEFAULT NULL COMMENT 'x',

`last_update_date` datetime DEFAULT NULL COMMENT 'x',

`cabinet_flag` varchar(32) DEFAULT NULL COMMENT 'x',

`truck_driver_no` varchar(32) DEFAULT NULL COMMENT 'x',

`sticky_status` varchar(32) DEFAULT NULL COMMENT 'x',

`currency` varchar(32) DEFAULT NULL COMMENT 'x',

`mo_no` varchar(132) DEFAULT NULL COMMENT 'x',

`po_no` varchar(132) DEFAULT NULL COMMENT 'x',

`con_error_status` varchar(32) DEFAULT NULL COMMENT 'x',

`con_error_remarks` varchar(32) DEFAULT NULL COMMENT 'x',

`is_double_drag` varchar(32) DEFAULT NULL COMMENT 'x',

`is_notice` varchar(32) DEFAULT NULL COMMENT 'x',

`update_cabinet_date` datetime DEFAULT NULL COMMENT 'x',

`frame_no` varchar(128) DEFAULT NULL COMMENT 'x',

`lock_time` datetime DEFAULT NULL COMMENT 'x',

`is_lock` varchar(32) DEFAULT NULL COMMENT 'x',

`booking_order_detail_id` varchar(128) DEFAULT NULL COMMENT 'x',

`booking_feedback_id` varchar(128) DEFAULT NULL COMMENT 'x',

`booking_order_id` varchar(128) DEFAULT NULL COMMENT 'x',

`booking_feedback_detail_id` varchar(128) DEFAULT NULL COMMENT 'x',

`dispatch_online` varchar(8) DEFAULT '0' COMMENT 'x',

`truck_increament_no` int(8) DEFAULT '0' COMMENT 'x',

`si_no` varchar(64) DEFAULT NULL COMMENT 'x',

`clearance_tansit` varchar(128) DEFAULT NULL COMMENT 'x',

`customs_code` varchar(128) DEFAULT NULL COMMENT 'x',

`destination_code` varchar(128) DEFAULT NULL COMMENT 'x',

`cabinet_cancel_code` varchar(32) DEFAULT NULL COMMENT 'x',

`cabinet_cancel_msg` varchar(128) DEFAULT NULL COMMENT 'x',

`destination_name` varchar(128) DEFAULT NULL COMMENT 'x',

`task_con_count_id` varchar(32) DEFAULT NULL COMMENT 'x',

`is_barge_ordered` varchar(255) DEFAULT NULL COMMENT 'x',

`is_dislock` varchar(255) DEFAULT NULL COMMENT 'x',

`dislock_time` datetime DEFAULT NULL COMMENT 'x',

`monitor_install_flag` varchar(8) DEFAULT NULL COMMENT 'x',

`copy_to_precontainer_flag` varchar(8) DEFAULT 'N' COMMENT 'x',

`vgm_apply` varchar(32) DEFAULT '0' COMMENT 'x（0-未申报，1-已申报）',

`vgm_number` varchar(32) DEFAULT '0' COMMENT 'x',

`gscl_id` varchar(64) DEFAULT NULL COMMENT 'x',

`is_push_loms` varchar(2) DEFAULT 'N' COMMENT 'x, Y/N',

`change_cabinet_flag` varchar(8) NOT NULL DEFAULT '10' COMMENT 'x 10-未同步, 20-已同步',

`line_no` int(11) DEFAULT NULL COMMENT 'x',

`auto_warehose_setting` tinyint(1) unsigned DEFAULT NULL COMMENT 'x, 1-自动 0-手动',

`effective_plan` varchar(2) DEFAULT NULL COMMENT 'x, Y/N',

`release_voucher_time` datetime DEFAULT NULL COMMENT 'x',

`loading_warehouse_code` varchar(32) DEFAULT NULL COMMENT 'x',

PRIMARY KEY (`_id`),

UNIQUE KEY `id` (`id`),

KEY `idx_gotr_task_no` (`task_no`),

KEY `idx_warehouse_code` (`warehouse_code`),

KEY `idx_vamp_no` (`vamp_no`),

KEY `idx_booking_no` (`booking_no`),

KEY `idx_booking_req_detail_id` (`booking_req_detail_id`),

KEY `idx_ycon_id` (`ycon_id`),

KEY `idx_sub_task_no` (`sub_task_no`),

KEY `idx_so_no` (`so_no`(255)),

KEY `idx_create_time` (`create_time`),

KEY `idx_ycon_no` (`ycon_no`),

KEY `idx_wharf_code` (`wharf_code`),

KEY `idx_cabinet_no` (`cabinet_no`),

KEY `idx_closing_date` (`closing_date`),

KEY `idx_ycon_status` (`ycon_status`),

KEY `idx_annto_customer_code` (`annto_customer_code`),

KEY `idx_order_no` (`order_no`),

KEY `idx_motorcade_no` (`motorcade_no`),

KEY `index_cb_flag` (`cb_flag`)

) ENGINE=InnoDB AUTO_INCREMENT=1458277 DEFAULT CHARSET=utf8 COMMENT='x\r\n'；

go_shipment_notice

CREATE TABLE `go_shipment_notice` (

`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'x',

`id` varchar(50) NOT NULL COMMENT 'x',

`order_no` varchar(64) DEFAULT NULL COMMENT 'x',

`delivery_order_id` int(32) DEFAULT NULL COMMENT 'x',

`delivery_order_number` varchar(32) DEFAULT NULL COMMENT 'x',

`refrence_id` int(11) DEFAULT NULL COMMENT 'x',

`booking_req_detail_id` int(32) DEFAULT NULL COMMENT 'x',

`vir_container_number` varchar(32) DEFAULT NULL COMMENT 'x',

`is_add_item` varchar(1) DEFAULT NULL COMMENT 'x',

`customer_id` int(32) DEFAULT NULL COMMENT 'x',

`customer_delivery_address_id` int(32) DEFAULT NULL COMMENT 'x',

`customer_delivery_address` varchar(240) DEFAULT NULL COMMENT 'x',

`transit_org_id` int(32) DEFAULT NULL COMMENT 'x',

`export_org_id` int(32) DEFAULT NULL COMMENT 'x',

`salesman_id` int(32) DEFAULT NULL COMMENT 'x',

`sales_unit_id` int(32) DEFAULT NULL COMMENT 'x',

`customs_port_code` varchar(32) DEFAULT NULL COMMENT 'x',

`deli_without_collect_number` varchar(32) DEFAULT NULL COMMENT 'x',

`deli_without_collect_reason` varchar(240) DEFAULT NULL COMMENT 'x',

`is_inspection` char(1) DEFAULT NULL COMMENT 'x',

`vacuum_cleaner_invoice_num` varchar(30) DEFAULT NULL COMMENT 'x',

`is_sales_by_sets` char(1) DEFAULT NULL COMMENT 'x',

`is_add_premium_oa` char(1) DEFAULT NULL COMMENT 'x',

`inspect_flag` char(1) DEFAULT NULL COMMENT 'x',

`is_customs_declare` char(1) DEFAULT NULL COMMENT 'x',

`picked_up_flag` char(1) DEFAULT NULL COMMENT 'x',

`is_vat_invoice` char(1) DEFAULT NULL COMMENT 'x',

`credit_insurance_quota_flag` char(1) DEFAULT NULL COMMENT 'x',

`so_status` varchar(32) DEFAULT NULL COMMENT 'x',

`loading_status_code` varchar(32) DEFAULT NULL COMMENT 'x',

`transport_type_code` varchar(32) DEFAULT NULL COMMENT 'x',

`shp_type_code` varchar(32) DEFAULT NULL COMMENT 'x',

`loading_type_code` varchar(32) DEFAULT NULL COMMENT 'x',

`expected_loading_date` datetime DEFAULT NULL COMMENT 'x',

`actual_shp_date` datetime DEFAULT NULL COMMENT 'x',

`marks` varchar(240) DEFAULT NULL COMMENT 'x',

`customer_order_number` varchar(32) DEFAULT NULL COMMENT 'x',

`offset_marked` char(1) DEFAULT NULL COMMENT 'x',

`return_flag` char(1) DEFAULT NULL COMMENT 'x',

`return_reason` varchar(2000) DEFAULT NULL COMMENT 'x',

`virtual_return` char(1) DEFAULT NULL COMMENT 'x',

`return_comeback` char(1) DEFAULT NULL COMMENT 'x',

`is_deposit_return` char(1) DEFAULT NULL COMMENT 'x',

`old_delivery_order_number` varchar(32) DEFAULT NULL COMMENT 'x',

`is_dept_lcl` char(1) DEFAULT NULL COMMENT 'x',

`conversion_ratio` int(32) DEFAULT NULL COMMENT 'x',

`print_lock` char(1) DEFAULT NULL COMMENT 'x',

`print_times` int(32) DEFAULT NULL COMMENT 'x',

`printed_by_id` int(32) DEFAULT NULL COMMENT 'x',

`print_date` datetime DEFAULT NULL COMMENT 'x',

`currency` varchar(32) DEFAULT NULL COMMENT 'x',

`exchange_rate_type` varchar(32) DEFAULT NULL COMMENT 'x',

`usd_exchange_rate` decimal(10,4) DEFAULT NULL COMMENT 'x',

`exchange_rate` decimal(10,4) DEFAULT NULL COMMENT 'x',

`exchange_rate_date` datetime DEFAULT NULL COMMENT 'x',

`payment_term_id` int(32) DEFAULT NULL COMMENT 'x',

`price_clause_name` varchar(32) DEFAULT NULL COMMENT 'x',

`payment_type_id` int(32) DEFAULT NULL COMMENT 'x',

`to_owe_reason` varchar(240) DEFAULT NULL COMMENT 'x',

`loading_req_remarks` varchar(2000) DEFAULT NULL COMMENT 'x',

`factory_seal_no` varchar(240) DEFAULT NULL COMMENT 'x',

`preloading_detail_id` int(32) DEFAULT NULL COMMENT 'x',

`shp_notice_id` int(32) DEFAULT NULL COMMENT 'x',

`trans_date` datetime DEFAULT NULL COMMENT 'x',

`submit_date` datetime DEFAULT NULL COMMENT 'x',

`container_red_reason` varchar(2000) DEFAULT NULL COMMENT 'x',

`offset_remarks` varchar(2000) DEFAULT NULL COMMENT 'x',

`deliver_status_code` varchar(32) DEFAULT NULL COMMENT 'x',

`consumer_code` varchar(32) DEFAULT NULL COMMENT 'x',

`container_type_id` int(32) DEFAULT NULL COMMENT 'x',

`confirm_import_erp_status` varchar(32) DEFAULT NULL COMMENT 'x',

`confirm_failure_reason` varchar(240) DEFAULT NULL COMMENT 'x',

`erp_inv_process_result` varchar(100) DEFAULT NULL COMMENT 'x',

`erp_inv_failure_reason` varchar(2000) DEFAULT NULL COMMENT 'x',

`exception_quota_flag` char(1) DEFAULT NULL COMMENT 'x',

`erp_order_header_id` int(32) DEFAULT NULL COMMENT 'x',

`exception_remarks` varchar(2000) DEFAULT NULL COMMENT 'x',

`lc_id` int(32) DEFAULT NULL COMMENT 'x',

`overseas_transit_status` varchar(32) DEFAULT NULL COMMENT 'x',

`been_returned_flag` char(1) DEFAULT NULL COMMENT 'x',

`release_flag` char(1) DEFAULT NULL COMMENT 'x',

`plan_release_flag` char(1) DEFAULT NULL COMMENT 'x',

`related_order_number` varchar(64) DEFAULT NULL COMMENT 'x',

`related_logist_number` varchar(100) DEFAULT NULL COMMENT 'x',

`confirm_gl_date` datetime DEFAULT NULL COMMENT 'x',

`reservation1` varchar(100) DEFAULT NULL COMMENT '预留字段1',

`reservation2` varchar(100) DEFAULT NULL COMMENT '预留字段2',

`reservation3` datetime DEFAULT NULL COMMENT 'x',

`rec_status` tinyint(1) DEFAULT '0' COMMENT '状x',

`create_time` datetime DEFAULT NULL COMMENT 'x',

`creator` varchar(36) DEFAULT NULL COMMENT 'x',

`modify_time` datetime DEFAULT NULL COMMENT 'x',

`modifier` varchar(36) DEFAULT NULL COMMENT 'x',

`recVer` int(11) NOT NULL COMMENT 'x',

`is_replacement_order` varchar(2) DEFAULT NULL COMMENT 'x',

`unit_id` int(11) DEFAULT NULL COMMENT 'x',

`owe_status_name` varchar(16) DEFAULT NULL COMMENT 'x',

`exam_status` varchar(16) DEFAULT NULL COMMENT 'x, x',

`declaration_no` varchar(16) DEFAULT NULL COMMENT 'x',

`declaration_status` tinyint(1) DEFAULT NULL COMMENT 'x',

`declaration_remark` varchar(256) DEFAULT NULL COMMENT 'x',

PRIMARY KEY (`_id`),

UNIQUE KEY `id` (`id`),

KEY `idx_booking_req_detail_id` (`booking_req_detail_id`),

KEY `vir_container_number` (`vir_container_number`),

KEY `idx_delivery_order_id` (`delivery_order_id`),

KEY `idx_delivery_order_number` (`delivery_order_number`),

KEY `idx_reservation3` (`reservation3`) USING BTREE,

KEY `index_consumer_code` (`consumer_code`),

KEY `declaration_no` (`declaration_no`)

) ENGINE=InnoDB AUTO_INCREMENT=1313502 DEFAULT CHARSET=utf8 COMMENT='x'

表信息数据收集对比，两个环境的表全部都手动analyze 一遍，收集的数据相对是比较准确的。

生产环境：

dba@10.18.x:3306(基础数据) : midea_gls 01:45:48>select * from mysql.innodb_table_stats where database_name='midea_gls' and table_name in ('go_order_ycon','go_order','go_task_ycon_refrence','go_shipment_notice');

+---------------+-----------------------+---------------------+---------+----------------------+--------------------------+

| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |

+---------------+-----------------------+---------------------+---------+----------------------+--------------------------+

| midea_gls | go_order | 2020-03-30 13:45:16 | 542383 | 37503 | 12927 |

| midea_gls | go_order_ycon | 2020-03-30 13:45:28 | 1359347 | 28288 | 16085 |

| midea_gls | go_shipment_notice | 2020-03-30 13:45:35 | 1162486 | 33600 | 19409 |

| midea_gls | go_task_ycon_refrence | 2020-03-30 13:45:44 | 1231889 | 72192 | 56973 |

+---------------+-----------------------+---------------------+---------+----------------------+--------------------------+

                                                                                |

| optimizer_switch                    | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off |

| optimizer_use_condition_selectivity  | 1

测试环境：

dba@10.16.x:3306(海外物流uat1 db(主)) : midea_gls 01:43:49>select * from mysql.innodb_table_stats where database_name='midea_gls' and table_name in ('go_order_ycon','go_order','go_task_ycon_refrence','go_shipment_notice');

+---------------+-----------------------+---------------------+--------+----------------------+--------------------------+

| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |

+---------------+-----------------------+---------------------+--------+----------------------+--------------------------+

| midea_gls | go_order | 2020-03-30 13:41:22 | 372596 | 19392 | 6322 |

| midea_gls | go_order_ycon | 2020-03-30 13:43:49 | 936112 | 18876 | 8189 |

| midea_gls | go_shipment_notice | 2020-03-30 13:39:06 | 786093 | 19520 | 10466 |

| midea_gls | go_task_ycon_refrence | 2020-03-30 13:38:41 | 751950 | 36736 | 24294 |

+---------------+-----------------------+---------------------+--------+----------------------+--------------------------+

| optimizer_switch                    | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on |

| optimizer_use_condition_selectivity  | 1 

执行计划：

生产环境

+------+--------------+---------------+------+------------------------------------------------------------+----------------------+---------+------------------------+--------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+--------------+---------------+------+------------------------------------------------------------+----------------------+---------+------------------------+--------+-----------------------+

| 1 | PRIMARY | go | ref | order_no,idx_order_no,idx_status | idx_status | 51 | const | 182221 | Using index condition |

| 1 | PRIMARY | gtyr | ref | idx_ycon_no,idx_ycon_status,idx_order_no | idx_order_no | 195 | midea_gls.go.order_no | 1 | Using where |

| 1 | PRIMARY | goy | ref | idx_gooy_order_no,idx_lcl_vir_container_number,idx_ycon_no | idx_gooy_order_no | 195 | midea_gls.go.order_no | 1 | Using where |

| 1 | PRIMARY | gsn | ref | vir_container_number | vir_container_number | 99 | midea_gls.gtyr.ycon_no | 1 | Using where |

| 4 | MATERIALIZED | go_order_ycon | ref | idx_lcl_vir_container_number,idx_ycon_no | idx_ycon_no | 195 | const | 2 | Using index condition |

| 3 | MATERIALIZED | go_order_ycon | ref | idx_lcl_vir_container_number,idx_ycon_no | idx_ycon_no | 195 | const | 2 | Using index condition |

+------+--------------+---------------+------+------------------------------------------------------------+----------------------+---------+------------------------+--------+-----------------------+

6 rows in set (0.00 sec)

测试环境：

+------+--------------+---------------+--------+------------------------------------------------------------+----------------------+---------+------------------------+--------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+--------------+---------------+--------+------------------------------------------------------------+----------------------+---------+------------------------+--------+-----------------------+

| 1 | PRIMARY | goy | ALL | idx_gooy_order_no,idx_lcl_vir_container_number,idx_ycon_no | NULL | NULL | NULL | 965086 | Using where |

| 1 | PRIMARY | go | eq_ref | order_no,idx_order_no,idx_status | order_no | 194 | midea_gls.goy.order_no | 1 | Using where |

| 1 | PRIMARY | gtyr | ref | idx_ycon_no,idx_ycon_status,idx_order_no | idx_ycon_no | 195 | midea_gls.goy.ycon_no | 1 | Using where |

| 1 | PRIMARY | gsn | ref | vir_container_number | vir_container_number | 99 | midea_gls.goy.ycon_no | 1 | Using where |

| 4 | MATERIALIZED | go_order_ycon | ref | idx_lcl_vir_container_number,idx_ycon_no | idx_ycon_no | 195 | const | 2 | Using index condition |

| 3 | MATERIALIZED | go_order_ycon | ref | idx_lcl_vir_container_number,idx_ycon_no | idx_ycon_no | 195 | const | 2 | Using index condition |

+------+--------------+---------------+--------+------------------------------------------------------------+----------------------+---------+------------------------+--------+-----------------------+

6 rows in set (0.11 sec)

疑问： 为何相同的表结构，大致相同的数据量和数据分布。SQL执行计划不一样，性能差别太大。

问题处理：

首先发对比两个库的数据库版本信息、sql_mode 设置、optimizer_switch 配置信息、表结构、表字段字符集、表字段校验集信息、表索引信息、表数据数量。发现以上配置都是一致的。

这令人比较难以理解，mysql 到底是如何对复杂SQL进行优化执行的。这个需要后面查阅资料进行测试研究。

临时处理方案是，查看优化器给出的sql执行计划，然后指定多表关联的顺序（让执行计划和测试环境一致）。

查看优化器改写后的SQL信息：

explain extended select * from ( select goy.is_replacement_order, goy.ycon_no,go.booking_req_no,go.export_org_name,go.salesman_name,go.shipping_personnel_name,gtyr.warehouse_name,gsn.delivery_order_number,go.status,gtyr.ycon_status from go_order_ycon goy left join go_order go on go.order_no = goy.order_no left join go_task_ycon_refrence gtyr on gtyr.ycon_no = goy.ycon_no and gtyr.order_no =goy.order_no left join go_shipment_notice gsn on gsn.vir_container_number = gtyr.ycon_no where (goy.ycon_no ='VCH201808280034' or goy.lcl_vir_container_number = 'VCH201808280034' or goy.ycon_no in (select lcl_vir_container_number from go_order_ycon where ycon_no ='VCH201808280034') or goy.lcl_vir_container_number in (select lcl_vir_container_number from go_order_ycon where ycon_no ='VCH201808280034')) ) TEMP where TEMP.ycon_status <>'Y_110' AND TEMP.status = 'O_10'\G

优化器做的SQL改写如下：

| Note | 1003 | select `midea_gls`.`goy`.`is_replacement_order` AS `is_replacement_order`,`midea_gls`.`goy`.`ycon_no` AS `ycon_no`,`midea_gls`.`go`.`booking_req_no` AS `booking_req_no`,`midea_gls`.`go`.`export_org_name` AS `export_org_name`,`midea_gls`.`go`.`salesman_name` AS `salesman_name`,`midea_gls`.`go`.`shipping_personnel_name` AS `shipping_personnel_name`,`midea_gls`.`gtyr`.`warehouse_name` AS `warehouse_name`,`midea_gls`.`gsn`.`delivery_order_number` AS `delivery_order_number`,`midea_gls`.`go`.`status` AS `status`,`midea_gls`.`gtyr`.`ycon_status` AS `ycon_status` from `midea_gls`.`go_order_ycon` `goy` join `midea_gls`.`go_order` `go` join `midea_gls`.`go_task_ycon_refrence` `gtyr` left join `midea_gls`.`go_shipment_notice` `gsn` on(((`midea_gls`.`gsn`.`vir_container_number` = `midea_gls`.`gtyr`.`ycon_no`) and (`midea_gls`.`gtyr`.`ycon_no` is not null))) where ((`midea_gls`.`goy`.`ycon_no` = `midea_gls`.`gtyr`.`ycon_no`) and (`midea_gls`.`gtyr`.`order_no` = `midea_gls`.`go`.`order_no`) and (`midea_gls`.`goy`.`order_no` = `midea_gls`.`go`.`order_no`) and (`midea_gls`.`gtyr`.`ycon_status` <> 'Y_110') and (`midea_gls`.`go`.`status` = 'O_10') and ((`midea_gls`.`gtyr`.`ycon_no` = 'VCH201808280034') or (`midea_gls`.`goy`.`lcl_vir_container_number` = 'VCH201808280034') or <in_optimizer>(`midea_gls`.`goy`.`ycon_no`,`midea_gls`.`goy`.`ycon_no` in ( <materialize> (select `midea_gls`.`go_order_ycon`.`lcl_vir_container_number` from `midea_gls`.`go_order_ycon` where (`midea_gls`.`go_order_ycon`.`ycon_no` = 'VCH201808280034') ), <primary_index_lookup>(`midea_gls`.`goy`.`ycon_no` in <temporary table> on distinct_key where ((`midea_gls`.`goy`.`ycon_no` = `<subquery3>`.`lcl_vir_container_number`))))) or <in_optimizer>(`midea_gls`.`goy`.`lcl_vir_container_number`,`midea_gls`.`goy`.`lcl_vir_container_number` in ( <materialize> (select `midea_gls`.`go_order_ycon`.`lcl_vir_container_number` from `midea_gls`.`go_order_ycon` where (`midea_gls`.`go_order_ycon`.`ycon_no` = 'VCH201808280034') ), <primary_index_lookup>(`midea_gls`.`goy`.`lcl_vir_container_number` in <temporary table> on distinct_key where ((`midea_gls`.`goy`.`lcl_vir_container_number` = `<subquery4>`.`lcl_vir_container_number`))))))) |

手动优化改写后的SQL：

desc select * from ( select goy.is_replacement_order, goy.ycon_no,go.booking_req_no,go.export_org_name,go.salesman_name,go.shipping_personnel_name,gtyr.warehouse_name,gsn.delivery_order_number,go.status,gtyr.ycon_status from go_order_ycon goy straight_join go_order go on go.order_no = goy.order_no straight_join go_task_ycon_refrence gtyr on gtyr.ycon_no = goy.ycon_no and gtyr.order_no =goy.order_no left join go_shipment_notice gsn on gsn.vir_container_number = gtyr.ycon_no where (goy.ycon_no ='VCH201808280034' or goy.lcl_vir_container_number = 'VCH201808280034' or goy.ycon_no in (select lcl_vir_container_number from go_order_ycon where ycon_no ='VCH201808280034') or goy.lcl_vir_container_number in (select lcl_vir_container_number from go_order_ycon where ycon_no ='VCH201808280034')) ) TEMP where TEMP.ycon_status <>'Y_110' AND TEMP.status = 'O_10'\G

改写后的SQL 执行计划和测试环境的一致。

=======

案例2：

限于蝙蝠，这个案例的表结构就不详细说明了。

复杂SQL的子查询中的多表关联，执行计划中表连接顺序不一样，导致sql执行效率降低。

还有一个案例是两个环境的数据库版本不一致，分别是mariadb 10.1.18, 10.4.6.

表结构、表数据量全部相同。optimize_switch 配置一致。所有相关表analyze 之后，子查询中因为表关联顺序不同，导致执行效率相差非常大。

ESCM 项目

问题sql:

SELECT

t.*, bs.STOCK_NAME,

smi.ITEM_CODE,

smi.ITEM_NAME,

smi.MEMO ITEM_DESC,

vc.COMPANY_NAME,

ip.SUB_INV_POSITION_CODE,

ip.SUB_INV_POSITION_NAME,

pa.PRO_AREA_NAME,

vca.COMPANY_NAME VENDOR_NAME,

ta.TRANSPORT_ADDRESS,

bu.BUSINESS_NAME,

su.DESCRIPTION PUR_NAME,

vcb.COMPANY_SHORT_NAME SALE_INTENTION_NAME,

mc.CATEGORY_NAME STEEL_SPECIES_NAME,

cp.SITE_CODE VENDOR_PLACE_NAME,

vcc.COMPANY_NAME DUTY_UNIT_NAME,

(

SELECT

dr.REQUIRE_ORG_ID

FROM

escm_base_supply_demand_rel dr,

TSP_SYS_ORGANIZATIONS so,

tsp_bda_vb_companys vc,

escm_sto_putout_order_detail od,

escm_sto_putout_order_header oh

WHERE

oh.CUSTOMER_ID = vc.COMPANY_ID AND vc.COMPANY_CODE = dr.SUPPLIER_CUSTOMERS_NUMBER AND oh.ORGANIZATION_ID = so.ORGANIZATION_ID AND so.ERP_ORGANIZATION_ID = dr.SUPPLIER_ORG_ID AND t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID AND od.STO_PUTOUT_ORDER_HEADER_ID = oh.STO_PUTOUT_ORDER_HEADER_ID

) REQUIRE_ORG_ID,

(

SELECT DISTINCT sh.DELIVERY_ORDER_NO

FROM

escm_sal_shipping_order_detail d,

escm_sal_delivery_order_detail sd,

escm_sal_delivery_order sh,

escm_sto_putout_order_detail od

WHERE

od.APPLY_DETAIL_ID = d.SAL_SHIPPING_ORDER_DETAIL_ID AND sd.SAL_DELIVERY_ORDER_ID = sh.SAL_DELIVERY_ORDER_ID AND d.DELIVERY_ORDER_NO = sh.DELIVERY_ORDER_NO AND d.DELIVERY_ROW_NO = sd.ROW_NO AND t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID

) SAL_DELIVERY_ORDER_NO,

(

SELECT

sh.CUS_DELIVERY_ORG_ID

FROM

escm_sal_so_header sh,

escm_sal_shipping_order so,

escm_sto_putout_order_detail od,

escm_sto_putout_order_header oh

WHERE

oh.PUTOUT_APPLY_ORDER_ID = so.SAL_SHIPPING_ORDER_ID AND sh.SAL_SO_HEADER_ID = so.SAL_SO_HEADER_ID AND t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID AND od.STO_PUTOUT_ORDER_HEADER_ID = oh.STO_PUTOUT_ORDER_HEADER_ID

) CUS_DELIVERY_ORG_ID,

(

SELECT

so.ERP_ORGANIZATION_ID

FROM

escm_sto_putout_order_detail od,

escm_sto_putout_order_header oh,

tsp_sys_organizations so

WHERE

t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID AND od.STO_PUTOUT_ORDER_HEADER_ID = oh.STO_PUTOUT_ORDER_HEADER_ID AND oh.ORGANIZATION_ID = so.ORGANIZATION_ID

) PUTOUT_ERP_ORGANIZATION_ID,

(

SELECT

bs.ERP_STOCK_ID

FROM

escm_sto_putout_order_detail od,

escm_sto_putout_order_header oh,

TSP_BDA_STOCK bs

WHERE

t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID AND od.STO_PUTOUT_ORDER_HEADER_ID = oh.STO_PUTOUT_ORDER_HEADER_ID AND oh.STOCK_ID = bs.STOCK_ID

) PUTOUT_ERP_STOCK_ID,

(

SELECT

sh.ASSOCIATED_NO

FROM

escm_sto_putout_order_detail od STRAIGHT_JOIN escm_sal_so_header sh ON od.SO_NO = sh.SO_NO

WHERE

t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID

) PUTOUT_ASSOCIATED_NO,

(

SELECT

sd.ASSOCIATED_ROW_NO

FROM

escm_sal_so_detail sd,

escm_sal_so_header sh,

escm_sto_putout_order_detail od

WHERE

t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID AND od.SO_NO = sh.SO_NO AND od.SO_LINE_NO = sd.ROW_NO AND sd.SAL_SO_HEADER_ID = sh.SAL_SO_HEADER_ID AND sd.DEL_STATUS = 'N'

) PUTOUT_ASSOCIATED_ROW_NO,

pod.RELA_TRANSACTION_NO PUTOUT_RELA_TRANSACTION_NO,

pod.RELA_DIS_LINE_ID PUTOUT_RELA_DIS_LINE_ID,

(

SELECT

sso.CUSTOM_SUB_INV_CODE

FROM

escm_sto_putout_order_header poh,

escm_sal_shipping_order sso,

escm_sto_putout_order_detail od

WHERE

od.STO_PUTOUT_ORDER_HEADER_ID = poh.STO_PUTOUT_ORDER_HEADER_ID AND poh.PUTOUT_APPLY_ORDER_ID = sso.SAL_SHIPPING_ORDER_ID AND t.STO_PUTOUT_ORDER_DETAIL_ID = od.STO_PUTOUT_ORDER_DETAIL_ID

) PUTOUT_ERP_RECEIVE_SUBINV_CODE,

(

SELECT DISTINCT

epcm.CUSTOMER_UNIT

FROM

escm_prod_customer_material epcm

WHERE

t.CUSTOMER_ITEM_CODE = epcm.CUSTOMER_MATERIAL_CODE AND epcm.BIZ_STATUS = 'VALID' AND epcm.DEL_STATUS = 'N'

) CUSTOMER_UNIT,

(

SELECT

fcr.BATCH_COST

FROM

escm_fina_cost_regist fcr,

escm_sto_putin_order_header oh

WHERE

t.DEL_STATUS = 'N' AND fcr.DEL_STATUS = 'N' AND t.STO_PUTIN_ORDER_HEADER_ID = oh.STO_PUTIN_ORDER_HEADER_ID AND fcr.STOCK_ID = oh.STOCK_ID AND fcr.ITEM_ID = t.ITEM_ID AND fcr.SELF_CODE = t.SELF_CODE AND fcr.BATCH_NO = t.BATCH_NUM AND fcr.BATCH_COST IS NOT NULL

) FINA_BATCH_COST,

smi2.ITEM_CODE PARENT_NO_ITEM_CODE,

smi2.ITEM_NAME PARENT_NO_ITEM_NAME,

rd.TRANSACTION_ID ERP_TRANSACTION_ID,

sso.SAL_SO_HEADER_ID,

sso.SAL_CONTRACT_NO,

sdo.CUSTOM_SUB_INV_CODE,

sdo.CUSTOM_SUB_INV_NAME,

(

SELECT

ssd.SAL_SO_DETAIL_ID

FROM

escm_sal_so_header sso,

escm_sal_so_detail ssd

WHERE

sso.DEL_STATUS = 'N' AND ssd.DEL_STATUS = 'N' AND sso.SAL_SO_HEADER_ID = ssd.SAL_SO_HEADER_ID AND sso.SO_NO = t.SO_NO AND ssd.ROW_NO = t.SO_LINE_NO

) SAL_SO_DETAIL_ID,

(

SELECT

sss.SAL_SO_SHIPPING_ID

FROM

escm_sal_so_header sso,

escm_sal_so_detail ssd,

escm_sal_so_shipping sss

WHERE

sso.DEL_STATUS = 'N' AND ssd.DEL_STATUS = 'N' AND sss.DEL_STATUS = 'N' AND sso.SAL_SO_HEADER_ID = ssd.SAL_SO_HEADER_ID AND sso.SO_NO = t.SO_NO AND ssd.ROW_NO = t.SO_LINE_NO AND ssd.SAL_SO_DETAIL_ID = sss.SAL_SO_DETAIL_ID AND sss.MASTER_STATUS = 'Y'

) SAL_SO_SHIPPING_ID,

(

SELECT

ssd.SAL_PRICE

FROM

escm_sal_so_header sso,

escm_sal_so_detail ssd

WHERE

sso.DEL_STATUS = 'N' AND ssd.DEL_STATUS = 'N' AND sso.SAL_SO_HEADER_ID = ssd.SAL_SO_HEADER_ID AND sso.SO_NO = t.SO_NO AND ssd.ROW_NO = t.SO_LINE_NO

) SAL_PRICE

FROM

escm_sto_putin_order_detail t

LEFT JOIN TSP_BDA_STOCK bs ON t.STOCK_ID = bs.STOCK_ID

LEFT JOIN TSP_SYS_MATERIAL_ITEMS smi ON t.ITEM_ID = smi.ITEM_ID

LEFT JOIN TSP_SYS_MATERIAL_ITEMS smi2 ON t.PARENT_NO_ITEM_ID = smi2.ITEM_ID

LEFT JOIN TSP_BDA_VB_COMPANYS vc ON t.COMPANY_ID = vc.COMPANY_ID

LEFT JOIN escm_sto_sub_inv_position ip ON t.STO_SUB_INV_POSITION_ID = ip.STO_SUB_INV_POSITION_ID

LEFT JOIN ESCM_BASE_PRIMARY_PRO_AREA pa ON t.PUR_PRIMARY_PRO_AREA_ID = pa.PUR_PRIMARY_PRO_AREA_ID

LEFT JOIN TSP_BDA_VB_COMPANYS vca ON t.VENDOR_ID = vca.COMPANY_ID

LEFT JOIN escm_dis_transport_address ta ON t.DIS_TRANSPORT_ADDRESS_ID = ta.DIS_TRANSPORT_ADDRESS_ID

LEFT JOIN TSP_SYS_BUSINESS_UNIT bu ON t.BUSINESS_UNIT_ID = bu.BUSINESS_UNIT_ID

LEFT JOIN TSP_SYS_USERS su ON t.PUR_ID = su.USER_ID

LEFT JOIN TSP_BDA_VB_COMPANYS vcb ON t.SALE_INTENTION = vcb.COMPANY_ID

LEFT JOIN escm_sto_putin_order_header h ON t.STO_PUTIN_ORDER_HEADER_ID = h.STO_PUTIN_ORDER_HEADER_ID

LEFT JOIN TSP_MD_CATEGORIES mc ON t.STEEL_SPECIES = mc.CATEGORY_ID

LEFT JOIN tsp_bda_vb_companys_place cp ON t.VENDOR_PLACE_ID = cp.COMPANY_PLACE_ID

LEFT JOIN TSP_BDA_VB_COMPANYS vcc ON t.DUTY_UNIT = vcc.COMPANY_ID

LEFT JOIN escm_sto_putout_order_detail pod ON t.STO_PUTOUT_ORDER_DETAIL_ID = pod.STO_PUTOUT_ORDER_DETAIL_ID

LEFT JOIN escm_pur_order_receive_detail rd ON t.RECEIVE_LINE_NUM_ID = rd.PUR_ORDER_RECEIVE_DETAIL_ID

LEFT JOIN escm_sal_so_header sso ON t.SO_NO = sso.SO_NO

LEFT JOIN escm_sal_delivery_order sdo ON h.PUTIN_APPLY_ORDER_ID = sdo.SAL_DELIVERY_ORDER_ID

WHERE

t.DEL_STATUS = 'N' AND t.STO_PUTIN_ORDER_HEADER_ID = 185783398274432

ORDER BY t.DETAIL_NUMBER

LIMIT 0,20000;

mariadb10.1.18 的执行计划：


id select_type table type possible_keys key key_len ref rows filtered Extra

14 DEPENDENT SUBQUERY sso ref PRIMARY,index_header_id,index_so_no,escm_sal_so_header_inx index_so_no 153 cdc_base.t.SO_NO 1 100 Using index condition; Using where

14 DEPENDENT SUBQUERY ssd ref index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sso.SAL_SO_HEADER_ID 3 100 Using index condition; Using where

13 DEPENDENT SUBQUERY sso ref PRIMARY,index_header_id,index_so_no,escm_sal_so_header_inx index_so_no 153 cdc_base.t.SO_NO 1 100 Using index condition; Using where

13 DEPENDENT SUBQUERY ssd ref PRIMARY,index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sso.SAL_SO_HEADER_ID 3 100 Using where

13 DEPENDENT SUBQUERY sss ref SAL_SO_DETAIL_ID SAL_SO_DETAIL_ID 9 cdc_base.ssd.SAL_SO_DETAIL_ID 1 100 Using index condition; Using where

12 DEPENDENT SUBQUERY sso ref PRIMARY,index_header_id,index_so_no,escm_sal_so_header_inx index_so_no 153 cdc_base.t.SO_NO 1 100 Using index condition; Using where

12 DEPENDENT SUBQUERY ssd ref index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sso.SAL_SO_HEADER_ID 3 100 Using index condition; Using where

11 DEPENDENT SUBQUERY fcr ref escm_fina_cost_regist_index_unique,IDX_COST_REGIST_BATCH_NO,escm_fina_cost_regist_inx1,escm_fina_cost_regist_inx2 escm_fina_cost_regist_index_unique 312 cdc_base.t.ITEM_ID,cdc_base.t.SELF_CODE 1 100 Using index condition; Using where

11 DEPENDENT SUBQUERY oh eq_ref PRIMARY,escm_sto_putin_order_header_inx11 PRIMARY 8 cdc_base.t.STO_PUTIN_ORDER_HEADER_ID 1 100 Using where

10 DEPENDENT SUBQUERY epcm ref escm_prod_customer_material_index_CUSTOMER_MATERIAL_CODE escm_prod_customer_material_index_CUSTOMER_MATERIAL_CODE 303 cdc_base.t.CUSTOMER_ITEM_CODE 1 100 Using where; Using temporary

9 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

9 DEPENDENT SUBQUERY poh eq_ref PRIMARY,escm_sto_putout_order_header_inx5 PRIMARY 8 cdc_base.od.STO_PUTOUT_ORDER_HEADER_ID 1 100 Using where

9 DEPENDENT SUBQUERY sso eq_ref PRIMARY PRIMARY 8 cdc_base.poh.PUTOUT_APPLY_ORDER_ID 1 100

8 DEPENDENT SUBQUERY od eq_ref PRIMARY PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

8 DEPENDENT SUBQUERY sh ref PRIMARY,index_header_id,index_so_no index_so_no 153 cdc_base.od.SO_NO 1 100 Using where; Using index

8 DEPENDENT SUBQUERY sd ref index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sh.SAL_SO_HEADER_ID 3 100 Using where

7 DEPENDENT SUBQUERY od eq_ref PRIMARY PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

7 DEPENDENT SUBQUERY sh ref index_so_no index_so_no 153 cdc_base.od.SO_NO 1 100 Using index condition

6 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

6 DEPENDENT SUBQUERY oh eq_ref PRIMARY,escm_sto_putout_order_header_inx10 PRIMARY 8 cdc_base.od.STO_PUTOUT_ORDER_HEADER_ID 1 100 Using where

6 DEPENDENT SUBQUERY bs eq_ref PRIMARY PRIMARY 8 cdc_base.oh.STOCK_ID 1 100

5 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

5 DEPENDENT SUBQUERY oh eq_ref PRIMARY PRIMARY 8 cdc_base.od.STO_PUTOUT_ORDER_HEADER_ID 1 100 Using where

5 DEPENDENT SUBQUERY so eq_ref PRIMARY,TSP_MD_ORGANIZATIONS_U1 PRIMARY 8 cdc_base.oh.ORGANIZATION_ID 1 100

4 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

4 DEPENDENT SUBQUERY oh eq_ref PRIMARY,escm_sto_putout_order_header_inx5 PRIMARY 8 cdc_base.od.STO_PUTOUT_ORDER_HEADER_ID 1 100 Using where

4 DEPENDENT SUBQUERY so eq_ref PRIMARY,escm_sal_shipping_order_inx2 PRIMARY 8 cdc_base.oh.PUTOUT_APPLY_ORDER_ID 1 100 Using where

4 DEPENDENT SUBQUERY sh eq_ref PRIMARY,index_header_id PRIMARY 8 cdc_base.so.SAL_SO_HEADER_ID 1 100

3 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx7 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where; Using temporary

3 DEPENDENT SUBQUERY d eq_ref PRIMARY PRIMARY 8 cdc_base.od.APPLY_DETAIL_ID 1 100 Using where

3 DEPENDENT SUBQUERY sh ref PRIMARY,escm_sal_delivery_order_uidx1,escm_sal_delivery_order_idx3 escm_sal_delivery_order_uidx1 153 cdc_base.d.DELIVERY_ORDER_NO 1 100 Using index

3 DEPENDENT SUBQUERY sd ref escm_sal_delivery_order_detail_idx escm_sal_delivery_order_detail_idx 9 cdc_base.sh.SAL_DELIVERY_ORDER_ID 3 100 Using where; Distinct

2 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

2 DEPENDENT SUBQUERY oh eq_ref PRIMARY,escm_sto_putout_order_header_inx4 PRIMARY 8 cdc_base.od.STO_PUTOUT_ORDER_HEADER_ID 1 100 Using where

2 DEPENDENT SUBQUERY vc eq_ref PRIMARY PRIMARY 8 cdc_base.oh.CUSTOMER_ID 1 100 Using where

2 DEPENDENT SUBQUERY so eq_ref PRIMARY,TSP_MD_ORGANIZATIONS_U1,ind_sys_organizations_n1 PRIMARY 8 cdc_base.oh.ORGANIZATION_ID 1 100

2 DEPENDENT SUBQUERY dr ref escm_base_supply_demand_rel_inx1,escm_base_supply_demand_rel_inx4 escm_base_supply_demand_rel_inx4 303 cdc_base.vc.COMPANY_CODE 10 100 Using index condition; Using where

1 PRIMARY t ref PITIN_ORDER_HEADER_INDEX,escm_sto_putin_order_detail_inx1,escm_sto_putin_order_detail_DEL_STATUS,escm_sto_putin_order_detail_inx10 PITIN_ORDER_HEADER_INDEX 9 const 1 100 Using where; Using filesort

1 PRIMARY bs eq_ref PRIMARY PRIMARY 8 cdc_base.t.STOCK_ID 1 100 Using where

1 PRIMARY smi eq_ref PRIMARY PRIMARY 8 cdc_base.t.ITEM_ID 1 100 Using where

1 PRIMARY smi2 eq_ref PRIMARY PRIMARY 8 cdc_base.t.PARENT_NO_ITEM_ID 1 100 Using where

1 PRIMARY vc eq_ref PRIMARY PRIMARY 8 cdc_base.t.COMPANY_ID 1 100 Using where

1 PRIMARY ip eq_ref PRIMARY PRIMARY 8 cdc_base.t.STO_SUB_INV_POSITION_ID 1 100 Using where

1 PRIMARY pa eq_ref PRIMARY PRIMARY 8 cdc_base.t.PUR_PRIMARY_PRO_AREA_ID 1 100 Using where

1 PRIMARY vca eq_ref PRIMARY PRIMARY 8 cdc_base.t.VENDOR_ID 1 100 Using where

1 PRIMARY ta eq_ref PRIMARY PRIMARY 8 cdc_base.t.DIS_TRANSPORT_ADDRESS_ID 1 100 Using where

1 PRIMARY bu eq_ref PRIMARY PRIMARY 8 cdc_base.t.BUSINESS_UNIT_ID 1 100 Using where

1 PRIMARY su eq_ref PRIMARY,TSP_MD_USERS_U1 PRIMARY 8 cdc_base.t.PUR_ID 1 100 Using where

1 PRIMARY vcb eq_ref PRIMARY PRIMARY 8 cdc_base.t.SALE_INTENTION 1 100 Using where

1 PRIMARY h const PRIMARY PRIMARY 8 const 1 100

1 PRIMARY mc eq_ref PRIMARY PRIMARY 8 cdc_base.t.STEEL_SPECIES 1 100 Using where

1 PRIMARY cp eq_ref PRIMARY PRIMARY 8 cdc_base.t.VENDOR_PLACE_ID 1 100 Using where

1 PRIMARY vcc eq_ref PRIMARY PRIMARY 8 cdc_base.t.DUTY_UNIT 1 100 Using where

1 PRIMARY pod eq_ref PRIMARY PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

1 PRIMARY rd eq_ref PRIMARY PRIMARY 8 cdc_base.t.RECEIVE_LINE_NUM_ID 1 100 Using where

1 PRIMARY sso ref index_so_no index_so_no 153 cdc_base.t.SO_NO 1 100 Using where

1 PRIMARY sdo eq_ref PRIMARY PRIMARY 8 cdc_base.h.PUTIN_APPLY_ORDER_ID 1 100 Using where

mariadb 10.4.6 库上的执行计划：

IP : 10.18.x.xx

id select_type table type possible_keys key key_len ref rows filtered Extra

1 PRIMARY t ref PITIN_ORDER_HEADER_INDEX,escm_sto_putin_order_detail_inx1,escm_sto_putin_order_detail_DEL_STATUS,escm_sto_putin_order_detail_inx10 PITIN_ORDER_HEADER_INDEX 9 const 18 100 Using where; Using filesort

1 PRIMARY bs eq_ref PRIMARY PRIMARY 8 cdc_base.t.STOCK_ID 1 100 Using where

1 PRIMARY smi eq_ref PRIMARY PRIMARY 8 cdc_base.t.ITEM_ID 1 100 Using where

1 PRIMARY smi2 eq_ref PRIMARY PRIMARY 8 cdc_base.t.PARENT_NO_ITEM_ID 1 100 Using where

1 PRIMARY vc eq_ref PRIMARY PRIMARY 8 cdc_base.t.COMPANY_ID 1 100 Using where

1 PRIMARY ip eq_ref PRIMARY PRIMARY 8 cdc_base.t.STO_SUB_INV_POSITION_ID 1 100 Using where

1 PRIMARY pa eq_ref PRIMARY PRIMARY 8 cdc_base.t.PUR_PRIMARY_PRO_AREA_ID 1 100 Using where

1 PRIMARY vca eq_ref PRIMARY PRIMARY 8 cdc_base.t.VENDOR_ID 1 100 Using where

1 PRIMARY ta eq_ref PRIMARY PRIMARY 8 cdc_base.t.DIS_TRANSPORT_ADDRESS_ID 1 100 Using where

1 PRIMARY bu eq_ref PRIMARY PRIMARY 8 cdc_base.t.BUSINESS_UNIT_ID 1 100 Using where

1 PRIMARY su eq_ref PRIMARY,TSP_MD_USERS_U1 PRIMARY 8 cdc_base.t.PUR_ID 1 100 Using where

1 PRIMARY vcb eq_ref PRIMARY PRIMARY 8 cdc_base.t.SALE_INTENTION 1 100 Using where

1 PRIMARY h const PRIMARY PRIMARY 8 const 1 100

1 PRIMARY mc eq_ref PRIMARY PRIMARY 8 cdc_base.t.STEEL_SPECIES 1 100 Using where

1 PRIMARY cp eq_ref PRIMARY PRIMARY 8 cdc_base.t.VENDOR_PLACE_ID 1 100 Using where

1 PRIMARY vcc eq_ref PRIMARY PRIMARY 8 cdc_base.t.DUTY_UNIT 1 100 Using where

1 PRIMARY pod eq_ref PRIMARY PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

1 PRIMARY rd eq_ref PRIMARY PRIMARY 8 cdc_base.t.RECEIVE_LINE_NUM_ID 1 100 Using where

1 PRIMARY sso ref index_so_no index_so_no 153 cdc_base.t.SO_NO 1 100 Using where

1 PRIMARY sdo eq_ref PRIMARY PRIMARY 8 cdc_base.h.PUTIN_APPLY_ORDER_ID 1 100 Using where

14 DEPENDENT SUBQUERY sso ref PRIMARY,index_header_id,index_so_no,escm_sal_so_header_inx index_so_no 153 cdc_base.t.SO_NO 1 100 Using index condition; Using where

14 DEPENDENT SUBQUERY ssd ref index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sso.SAL_SO_HEADER_ID 4 50 Using where

13 DEPENDENT SUBQUERY sso ref PRIMARY,index_header_id,index_so_no,escm_sal_so_header_inx index_so_no 153 cdc_base.t.SO_NO 1 100 Using index condition; Using where

13 DEPENDENT SUBQUERY ssd ref PRIMARY,index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sso.SAL_SO_HEADER_ID 4 50 Using where

13 DEPENDENT SUBQUERY sss ref SAL_SO_DETAIL_ID SAL_SO_DETAIL_ID 9 cdc_base.ssd.SAL_SO_DETAIL_ID 1 100 Using where

12 DEPENDENT SUBQUERY sso ref PRIMARY,index_header_id,index_so_no,escm_sal_so_header_inx index_so_no 153 cdc_base.t.SO_NO 1 50 Using index condition; Using where

12 DEPENDENT SUBQUERY ssd ref index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 index_header_id 9 cdc_base.sso.SAL_SO_HEADER_ID 4 50 Using where

11 DEPENDENT SUBQUERY oh eq_ref PRIMARY,escm_sto_putin_order_header_inx11 PRIMARY 8 cdc_base.t.STO_PUTIN_ORDER_HEADER_ID 1 100 Using where

11 DEPENDENT SUBQUERY fcr ref escm_fina_cost_regist_index_unique,IDX_COST_REGIST_BATCH_NO,escm_fina_cost_regist_inx1,escm_fina_cost_regist_inx2 escm_fina_cost_regist_index_unique 624 cdc_base.t.ITEM_ID,cdc_base.t.SELF_CODE,cdc_base.oh.STOCK_ID,cdc_base.t.BATCH_NUM 1 100 Using where

10 DEPENDENT SUBQUERY epcm ref escm_prod_customer_material_index_CUSTOMER_MATERIAL_CODE escm_prod_customer_material_index_CUSTOMER_MATERIAL_CODE 303 cdc_base.t.CUSTOMER_ITEM_CODE 1 100 Using where; Using temporary

9 DEPENDENT SUBQUERY sso ALL PRIMARY \N \N \N 163555 100

9 DEPENDENT SUBQUERY poh ref PRIMARY,escm_sto_putout_order_header_inx5 escm_sto_putout_order_header_inx5 9 cdc_base.sso.SAL_SHIPPING_ORDER_ID 2 100 Using index

9 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

8 DEPENDENT SUBQUERY sd ref index_header_id,escm_sal_so_detail_inx,escm_sal_so_detail_inx2 escm_sal_so_detail_inx 6 const 86285 100 Using index condition

8 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_so_no PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

8 DEPENDENT SUBQUERY sh ref PRIMARY,index_header_id,index_so_no index_so_no 153 cdc_base.od.SO_NO 1 100 Using where; Using index

7 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_so_no PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

7 DEPENDENT SUBQUERY sh ref index_so_no index_so_no 153 cdc_base.od.SO_NO 1 100 Using index condition

6 DEPENDENT SUBQUERY bs ALL PRIMARY \N \N \N 6 100

6 DEPENDENT SUBQUERY oh ref PRIMARY,escm_sto_putout_order_header_inx10 escm_sto_putout_order_header_inx10 9 cdc_base.bs.STOCK_ID 111051 100 Using index

6 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

5 DEPENDENT SUBQUERY so index PRIMARY,TSP_MD_ORGANIZATIONS_U1 ind_sys_organizations_n1 123 \N 7 100 Using index

5 DEPENDENT SUBQUERY oh ref PRIMARY,escm_sto_putout_order_detail_org_id escm_sto_putout_order_detail_org_id 9 cdc_base.so.ORGANIZATION_ID 111051 100 Using index

5 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

4 DEPENDENT SUBQUERY sh ALL PRIMARY,index_header_id \N \N \N 21079 100

4 DEPENDENT SUBQUERY so ref PRIMARY,escm_sal_shipping_order_inx2 escm_sal_shipping_order_inx2 9 cdc_base.sh.SAL_SO_HEADER_ID 5 100 Using index

4 DEPENDENT SUBQUERY oh ref PRIMARY,escm_sto_putout_order_header_inx5 escm_sto_putout_order_header_inx5 9 cdc_base.so.SAL_SHIPPING_ORDER_ID 2 100 Using index

4 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where

3 DEPENDENT SUBQUERY sh index PRIMARY,escm_sal_delivery_order_uidx1,escm_sal_delivery_order_idx3 escm_sal_delivery_order_uidx1 153 \N 144202 100 Using where; Using index; Using temporary

3 DEPENDENT SUBQUERY d ref PRIMARY,shipping_order_detail_order_no,shipping_order_detail_row_no shipping_order_detail_order_no 153 cdc_base.sh.DELIVERY_ORDER_NO 2 100 Distinct

3 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx7 PRIMARY 8 cdc_base.t.STO_PUTOUT_ORDER_DETAIL_ID 1 100 Using where; Distinct

3 DEPENDENT SUBQUERY sd ref escm_sal_delivery_order_detail_idx escm_sal_delivery_order_detail_idx 9 cdc_base.sh.SAL_DELIVERY_ORDER_ID 2 100 Using where; Distinct

2 DEPENDENT SUBQUERY so index PRIMARY,TSP_MD_ORGANIZATIONS_U1,ind_sys_organizations_n1 ind_sys_organizations_n1 123 \N 7 100 Using where; Using index

2 DEPENDENT SUBQUERY dr ref escm_base_supply_demand_rel_inx1,escm_base_supply_demand_rel_inx4 escm_base_supply_demand_rel_inx1 9 cdc_base.so.ERP_ORGANIZATION_ID 11 100 Using index condition

2 DEPENDENT SUBQUERY oh ref PRIMARY,escm_sto_putout_order_header_inx4,escm_sto_putout_order_detail_org_id escm_sto_putout_order_detail_org_id 9 cdc_base.so.ORGANIZATION_ID 111051 100 Using where

2 DEPENDENT SUBQUERY vc eq_ref PRIMARY PRIMARY 8 cdc_base.oh.CUSTOMER_ID 1 100 Using where

2 DEPENDENT SUBQUERY od eq_ref PRIMARY,escm_sto_putout_order_detail_inx1

sql 调优处理方式：

主要是根据10.1.18 中子查询的表关联顺序对10.4.6 的执行计划进行调整。

做了straight_join 修改的子查询，避免了6个10万数据量左右的全表扫描，可以很快查出结果。

可以从执行计划中 rows 一列查看子查询中的扫描行数进行确认。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,294评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,493评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,790评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,595评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,718评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,906评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,053评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,797评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,250评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,570评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,711评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,388评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,018评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,796评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,023评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,461评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,595评论 2赞 350

推荐阅读更多精彩内容