今天遇到一个问题，mysql数据库，在有些场景下，使用自增主键id排序+limit查询时，会很慢。
场景1：表数据50W+，id倒序/升序，limit分页，结果集<=10，耗时 0.9~1.4秒，不分页耗时100~180毫秒.
备注：结果集数量越小，查询速度越慢。
如下SQL
SELECT id, corp_id, org_id, bill_code, bill_maker, product_key_id, latest, reject_and_submit_status, audit_status, sku_id, enable_flag, special_num, special_period, special_flag, batchno_flag, medicine_flag, erp_product_type, zdyh_flag, creator, ts, modify_date, modifier, yn, common_name, product_zjm, product_name, medicine_type, product_type, dosage_form, dosageform_str, specification, unit, unit_str, storage_condition, bar_code, pzwh, pzwhqx, producer, production_place, expire_date, bzcpsms, zlzxbz, mah, yfyl, syz, buyer, sn_flag, sfylqx, input_vat, output_vat, vc_medicine_flag, new_specific_flag, cold_chain_flag, storage_mode, min_temperature, max_temperature, submit_date, storage_location_code, storage_location_name, proof_code, elqxzjyfl, drug_safety_product_id, drug_safety_standard_code, prescription_flag, store_property FROM my_table WHERE org_id=180 AND audit_status IN (3,-2) AND yn = 1 AND ( sku_id LIKE CONCAT("%",‘100001727506‘,"%") OR common_name LIKE CONCAT("%",‘100001727506‘,"%") OR product_zjm LIKE CONCAT("%",‘100001727506‘,"%")) ORDER BY id DESC
场景2：表数据50W+，id倒序，limit分页，结果集>=10000，耗时 30~50毫秒，不分页耗时500~600毫秒.
场景2：表数据50W+，id升序，limit分页，结果集>=10000，耗时 200~300毫秒，不分页耗时500~600毫秒.
备注：结果集数量越大，查询速度越快
SELECT id, corp_id, org_id, bill_code, bill_maker, product_key_id, latest, reject_and_submit_status, audit_status, sku_id, enable_flag, special_num, special_period, special_flag, batchno_flag, medicine_flag, erp_product_type, zdyh_flag, creator, ts, modify_date, modifier, yn, common_name, product_zjm, product_name, medicine_type, product_type, dosage_form, dosageform_str, specification, unit, unit_str, storage_condition, bar_code, pzwh, pzwhqx, producer, production_place, expire_date, bzcpsms, zlzxbz, mah, yfyl, syz, buyer, sn_flag, sfylqx, input_vat, output_vat, vc_medicine_flag, new_specific_flag, cold_chain_flag, storage_mode, min_temperature, max_temperature, submit_date, storage_location_code, storage_location_name, proof_code, elqxzjyfl, drug_safety_product_id, drug_safety_standard_code, prescription_flag, store_property FROM my_table WHERE org_id=180 AND audit_status IN (3,-2) AND yn = 1 AND ( sku_id LIKE CONCAT("%",‘1‘,"%") OR common_name LIKE CONCAT("%",‘1‘,"%") OR product_zjm LIKE CONCAT("%",‘1‘,"%")) ORDER BY id desc limit 20
具体原因：未查明....
结论：不要使用id排序与limit一起使用，如果需要，请做足场景测试，有效利用，避免出现慢SQL