mysql 百万级数据 sql优化

百万级数据，要求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多
求各位大神支招，在线等。

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 

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

看你这是用了filesort，而不是使用排序索引，速度肯定提不起来啊
建议是，新增flag字段，使用你的sql中的case语句给赋值，然后新增索引index_1(is_project_order, del_status, flag, order_time)
百万级别的数据，使用索引搜索，肯定能控制时间在1s之内的。

sql优化需要结合执行计划；单看你当前的这段代码，你可以尝试把查询个字段的处理逻辑提出来，不要写在sql内，尽可能少的逻辑在sql内

你这查询的字段也太多了吧，建议只查询需要的字段即可！

你查过你索引字段的离散度吗？我建议，where后面那两个条件字段，按照其中一个来进行分区，哪个条件最接近你所查出来的数据量，按哪个来分区。然后加不加索引，你先去查一下你要加索引字段的离散度。