今天遇到一个问题，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