mysql 百万级数据 sql优化

Question

百万级数据，要求3s内出结果，索引都加上了
贴上sql

SELECT
    T.lockbalance_status AS lockbalanceStatus,
    T.order_merinstalltype AS orderMerinstalltype,
    T.order_id AS orderId,
    T.serverUserId,
    date_format( T.order_time, '%Y-%m-%d %H:%i:%s' ) AS orderTime,
    T.order_no AS orderNo,
    T.is_project_order AS isProjectOrder,
    T.STATUS,
    T.user_id AS userId,
    T.insert_user AS userName,
    T.serverUserId AS serverUserName,
    T.organ_name AS organName,
    T.leadingoffice_name AS leadingofficeName,
    T.order_type AS orderType,
    T.is_self_paying AS isSelfPaying,
    T.account_name AS accountName,
    T.account_mobile AS accountMobile,
    T.account_address AS accountAddress,
    T.province_id AS provinceId,
    T.city_id AS cityId,
    T.area_id AS areaId,
    T.install_userid AS installUserName,
    date_format( T.install_time, '%Y-%m-%d %H:%i:%s' ) AS installTime,
    date_format( T.order_completetime, '%Y-%m-%d %H:%i:%s' ) AS completetime,
    T.is_ergent AS isErgent,
    date_format( T.allocationtime, '%Y-%m-%d %H:%i:%s' ) AS allocationtime,
    date_format( T.install_time_again, '%Y-%m-%d %H:%i:%s' ) AS installTimeAgain,
    date_format( T.assign_again_time, '%Y-%m-%d %H:%i:%s' ) AS assignAgainTime,
    T.dealOver AS dealOver,
    date_format( T.alarm_time_again, '%Y-%m-%d %H:%i:%s' ) AS alarmTimeAgain,
    date_format( T.order_takingtime, '%Y-%m-%d %H:%i:%s' ) AS orderTakingtime,
    date_format( T.logistics_time, '%Y-%m-%d %H:%i:%s' ) AS logisticsTime,
    T.is_logistics_time AS isLogisticsTime,
    T.firstperson AS firstPersonId,
    T.firstperson AS firstPersonName,
    T.program_id AS programId,
    T.program_name AS programName,
    T.program_manager AS programManager,
    date_format( T.insert_time, '%Y-%m-%d %H:%i:%s' ) AS programTime,
    date_format( T.p_setProject_time, '%Y-%m-%d' ) AS setProjectTime,
    date_format( T.noSettlementTime, '%Y-%m-%d %H:%i:%s' ) AS noSettlementTime,
    T.installremark AS installRemark,
    T.lockSmithRemark AS lockSmithRemark,
    T.order_remark AS orderRemark,
    T.audit_status AS auditStatus,
CASE

    WHEN T.firstperson = 'cdbcd902dbe411e89c777cd30ae00302' THEN
    0 
    WHEN T.serverUserId = 'cdbcd902dbe411e89c777cd30ae00302' THEN
    1 ELSE 2 
    END AS flag 
FROM
    t_order T 
WHERE
    T.is_project_order = '0' 
    AND T.del_status = '0' 
ORDER BY
    flag,
    T.order_time DESC 
LIMIT 10

截图为原来执行计划

根据各位大神提供的方案，优化了一波，贴上优化后的执行计划

但是依旧达不到速度要求
flag 是业务需要，客户希望与自己有关的数据优先展示
目前执行下来sql需要10s多
求各位大神支招，在线等。

Answer 1

select * from (
SELECT
    T.lockbalance_status AS lockbalanceStatus,
    T.order_merinstalltype AS orderMerinstalltype,
    T.order_id AS orderId,
    T.serverUserId,
    date_format( T.order_time, '%Y-%m-%d %H:%i:%s' ) AS orderTime,
    T.order_no AS orderNo,
    T.is_project_order AS isProjectOrder,
    T.STATUS,
    T.user_id AS userId,
    T.insert_user AS userName,
    T.serverUserId AS serverUserName,
    T.organ_name AS organName,
    T.leadingoffice_name AS leadingofficeName,
    T.order_type AS orderType,
    T.is_self_paying AS isSelfPaying,
    T.account_name AS accountName,
    T.account_mobile AS accountMobile,
    T.account_address AS accountAddress,
    T.province_id AS provinceId,
    T.city_id AS cityId,
    T.area_id AS areaId,
    T.install_userid AS installUserName,
    date_format( T.install_time, '%Y-%m-%d %H:%i:%s' ) AS installTime,
    date_format( T.order_completetime, '%Y-%m-%d %H:%i:%s' ) AS completetime,
    T.is_ergent AS isErgent,
    date_format( T.allocationtime, '%Y-%m-%d %H:%i:%s' ) AS allocationtime,
    date_format( T.install_time_again, '%Y-%m-%d %H:%i:%s' ) AS installTimeAgain,
    date_format( T.assign_again_time, '%Y-%m-%d %H:%i:%s' ) AS assignAgainTime,
    T.dealOver AS dealOver,
    date_format( T.alarm_time_again, '%Y-%m-%d %H:%i:%s' ) AS alarmTimeAgain,
    date_format( T.order_takingtime, '%Y-%m-%d %H:%i:%s' ) AS orderTakingtime,
    date_format( T.logistics_time, '%Y-%m-%d %H:%i:%s' ) AS logisticsTime,
    T.is_logistics_time AS isLogisticsTime,
    T.firstperson AS firstPersonId,
    T.firstperson AS firstPersonName,
    T.program_id AS programId,
    T.program_name AS programName,
    T.program_manager AS programManager,
    date_format( T.insert_time, '%Y-%m-%d %H:%i:%s' ) AS programTime,
    date_format( T.p_setProject_time, '%Y-%m-%d' ) AS setProjectTime,
    date_format( T.noSettlementTime, '%Y-%m-%d %H:%i:%s' ) AS noSettlementTime,
    T.installremark AS installRemark,
    T.lockSmithRemark AS lockSmithRemark,
    T.order_remark AS orderRemark,
    T.audit_status AS auditStatus,
CASE

    WHEN T.firstperson = 'cdbcd902dbe411e89c777cd30ae00302' THEN
    0 
    WHEN T.serverUserId = 'cdbcd902dbe411e89c777cd30ae00302' THEN
    1 ELSE 2 
    END AS flag 
FROM
    t_order T 
WHERE
    T.is_project_order = '0' 
    AND T.del_status = '0' 
LIMIT 10
) AS temp ORDER BY temp.flag ASC,temp.order_time DESC 

应该可以解决，你复制过去执行试试看。