Mysql
即使在索引之後，向查詢添加 order by 也會減慢查詢速度
即使在索引列順序之後加入具有大量數據的列也會減慢查詢速度。沒有 order by 查詢在 2 秒內執行。但是按查詢排序需要 145 秒。查詢如下：
SELECT pd.message_id, pd.Receipt_Time_Stamp, pd.shift_id, pd.Tunnel_ID, pd.Package_Number, pd.Package_Read_Status, pd.Iseq_Number, pd.SxS_Status, pd.host_message, pd.package_gap, pd.Parcel_Length, pd.Parcel_Width, pd.Parcel_Height, pd.Image_Files, t.tunnel_name, s.shift_name FROM fm_package_db.dla_package_details pd force index(dl_dla_package_details_receipt_time_stamp_idx) JOIN fm_local_db.as_fm_tunnel_master t force index(dl_as_fm_tunnel_master_tunnel_id_idx) ON pd.Tunnel_ID = t.Tunnel_ID JOIN fm_local_db.as_fm_shift_info s force index(dl_as_fm_shift_info_shift_id_idx) ON s.shift_id = pd.shift_id where pd.message_id in (Select distinct b.Message_ID from dla_more_bar_codes b force index(dl_dla_more_bar_codes_message_id_idx,dl_dla_more_bar_codes_bar_code_number_idx, dl_dla_more_bar_codes_bar_code_idx) JOIN fm_package_db.dla_more_devices d force index(dl_dla_more_devices_message_id_idx,dl_dla_more_devices_device_id_idx) ON b.message_id = d.message_id where (b.bar_code_number = 1 or b.bar_code_number = 2)and bar_code like '%9%' and (d.device_id = 2 or d.device_id = 3 or d.device_id = 8 or d.device_id = 1005 or d.device_id = 1021 or d.device_id = 1049 or d.device_id = 1057 or d.device_id = 1081) ) and pd.receipt_time_stamp between '2015-03-01 00:00:00' AND '2015-03-30 23:59:59' and pd.Message_Type = 'PackageInfo' AND (pd.Tunnel_ID = '1') AND (pd.shift_id = 1 or pd.shift_id = 2 or pd.shift_id = 3) and pd.receipt_time_stamp like '%2015%' and pd.Package_Number like '%5%' and pd.Package_Read_Status like '%R%' and pd.Iseq_Number like '%5%' and pd.host_message like '%5%' and pd.Parcel_Length like '%0%' and pd.Parcel_Width like '%0%' and pd.Parcel_Height like '%0%' and s.shift_name like '%S%' and t.tunnel_name like '%T%' order by pd.receipt_time_stamp asc LIMIT 0,25;解釋如下：
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t ref dl_as_fm_tunnel_master_tunnel_id_idx dl_as_fm_tunnel_master_tunnel_id_idx 8 const 1 Using index condition; Using where; Using temporary; Using filesort 1 SIMPLE pd range dl_dla_package_details_receipt_time_stamp_idx dl_dla_package_details_receipt_time_stamp_idx 5 NULL 1958726 Using index condition; Using where 1 SIMPLE s ref dl_as_fm_shift_info_shift_id_idx dl_as_fm_shift_info_shift_id_idx 8 fm_package_db.pd.shift_id 1 Using where 1 SIMPLE b ref dl_dla_more_bar_codes_message_id_idx,dl_dla_more_bar_codes_bar_code_number_idx dl_dla_more_bar_codes_message_id_idx 8 fm_package_db.pd.Message_ID 1 Using where 1 SIMPLE d ref dl_dla_more_devices_message_id_idx,dl_dla_more_devices_device_id_idx dl_dla_more_devices_message_id_idx 8 fm_package_db.pd.Message_ID 2 Using where; FirstMatch(s)在分析它說發送數據需要更多時間。創建表查詢如下：
CREATE TABLE `dla_package_details` ( `Message_ID` bigint(20) NOT NULL DEFAULT '0', `Receipt_Time_Stamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `Message_Type` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Message_Status` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'P', `Site_Code` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Tunnel_ID` bigint(20) DEFAULT NULL, `shift_id` bigint(20) DEFAULT NULL, `Package_Number` bigint(20) DEFAULT NULL, `Package_Read_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Iseq_Number` bigint(20) DEFAULT NULL, `Host_Message` text CHARACTER SET utf8 COLLATE utf8_bin, `Eseq_Number` bigint(20) DEFAULT NULL, `Bar_Code_1` text CHARACTER SET utf8 COLLATE utf8_bin, `Bar_Code_2` text CHARACTER SET utf8 COLLATE utf8_bin, `Bar_Code_3` text CHARACTER SET utf8 COLLATE utf8_bin, `Bar_Code_4` text CHARACTER SET utf8 COLLATE utf8_bin, `Bar_Code_5` text CHARACTER SET utf8 COLLATE utf8_bin, `Bar_Code_6` text CHARACTER SET utf8 COLLATE utf8_bin, `More_bar_Codes` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'N', `Dev_1_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_2_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_3_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_4_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_5_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_6_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_7_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_8_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_9_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_10_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_11_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_12_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_13_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_14_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_15_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_16_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_17_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_18_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_19_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_20_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_21_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `Dev_22_Details` text CHARACTER SET utf8 COLLATE utf8_bin, `More_Devices` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'N', `Nbr_Short_Parcels` int(11) NOT NULL DEFAULT '0', `Nbr_Short_Gaps` int(11) NOT NULL DEFAULT '0', `Nbr_Lost_Barcodes` int(11) NOT NULL DEFAULT '0', `Trigger_Length` int(11) NOT NULL DEFAULT '0', `Package_Gap` int(11) NOT NULL DEFAULT '0', `Conveyor_Speed` int(11) NOT NULL DEFAULT '0', `SxS_Status` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Parcel_Length` decimal(5,2) DEFAULT NULL, `Parcel_Width` decimal(5,2) DEFAULT NULL, `Parcel_Height` decimal(5,2) DEFAULT NULL, `Parcel_Linear_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `LFT_Dimensions` text CHARACTER SET utf8 COLLATE utf8_bin, `Parcel_Volume` decimal(5,2) DEFAULT NULL, `Parcel_Volume_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Parcel_Weight` decimal(5,2) DEFAULT NULL, `Parcel_Weight_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `LFT_Parcel_Weight` decimal(5,2) DEFAULT NULL, `LFT_Parcel_Weight_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Overlap_Prev` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Overlap_Next` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Image_Files` text CHARACTER SET utf8 COLLATE utf8_bin, `Decode_Info` text CHARACTER SET utf8 COLLATE utf8_bin, `Device_Read_Result` int(11) NOT NULL DEFAULT '0', `unix_receipt_time_stamp` int(11) DEFAULT NULL, `Message_type_id` tinyint(4) DEFAULT NULL, PRIMARY KEY (`Message_ID`), KEY `dl_dla_package_details_message_type_idx` (`Message_Type`), KEY `dl_dla_package_details_shift_id_idx` (`shift_id`), KEY `dl_dla_package_details_package_number_idx` (`Package_Number`), KEY `dl_dla_package_details_package_read_status_idx` (`Package_Read_Status`), KEY `dl_dla_package_details_iseq_number_idx` (`Iseq_Number`), KEY `dl_dla_package_details_host_message_idx` (`Host_Message`(255)), KEY `dl_dla_package_details_package_gap_idx` (`Package_Gap`), KEY `dl_dla_package_details_sxs_status_idx` (`SxS_Status`), KEY `dl_dla_package_details_parcel_length_idx` (`Parcel_Length`), KEY `dl_dla_package_details_parcel_width_idx` (`Parcel_Width`), KEY `dl_dla_package_details_parcel_height_idx` (`Parcel_Height`), KEY `dl_dla_package_details_unix_receipt_time_stamp_idx` (`unix_receipt_time_stamp`), KEY `dl_dla_package_details_tunnel_id_idx` (`Tunnel_ID`), KEY `dl_dla_package_details_receipt_time_stamp_idx` (`Receipt_Time_Stamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;可以做些什麼來優化這個查詢？
在我看來，您正在強制使用太多索引，而不是讓優化器完成它的工作，也許只是將它推向正確的方向。
那些強制索引之一或其他東西使它使用 t 作為基表而不是 pd - 這意味著 pd.receipt_time_stamp 的順序必須由臨時表上的文件排序解決（臨時表中的文本列意味著它必須進入磁碟 -沒有記憶體表 -有關臨時表使用情況的手動資訊）。
您可以嘗試“… STRAIGHT_JOIN fm_local_db.as_fm_tunnel_master t ”使 myslq 使用“正確”基表並按索引排序。
我也會嘗試刪除所有“強制索引”以查看會發生什麼，也許根本不需要直接連接，但這只是我的猜測。